List of usage examples for org.apache.poi.ss.usermodel Row getRowNum
int getRowNum();
From source file:com.yqboots.initializer.core.builder.excel.MenuItemSheetBuilder.java
License:Apache License
@Override protected void doBuild(final Path root, final ProjectMetadata metadata, final Sheet sheet) throws IOException { final List<MenuItem> items = new ArrayList<>(); for (Row row : sheet) { // ignore the header if (row.getRowNum() < 1) { continue; }/*from w w w. java 2 s. c o m*/ items.add(getMenuItem(row)); } // generate an XML for the application importing into Database Path targetPath = Paths.get(root + File.separator + properties.getExportRelativePath()); if (!Files.exists(targetPath)) { Files.createDirectories(targetPath); } Path file = Paths.get(targetPath + File.separator + properties.getExportName() + FileType.DOT_XML); try (FileWriter writer = new FileWriter(file.toFile())) { marshaller.marshal(new MenuItems(items), new StreamResult(writer)); } }
From source file:com.yqboots.initializer.core.builder.excel.MessageSheetBuilder.java
License:Apache License
@Override protected void doBuild(final Path root, final ProjectMetadata metadata, final Sheet sheet) throws IOException { Row firstRow = sheet.getRow(0);//www .jav a 2 s . c om List<String> languages = getLanguages(firstRow); Path path = Paths.get(root + File.separator + properties.getExportRelativePath()); for (int i = 1; i <= languages.size(); i++) { String fileName = PREFIX + "_" + languages.get(i - 1) + FileType.DOT_PROPERTIES; try (FileWriter writer = new FileWriter(Paths.get(path + File.separator + fileName).toFile())) { for (Row row : sheet) { if (row.getRowNum() < 1) { continue; } String key = row.getCell(0).getStringCellValue(); String value = row.getCell(i).getStringCellValue(); writer.write(key + "=" + value + "\r\n"); } } } }
From source file:com.yqboots.initializer.core.builder.excel.SystemPropertiesSheetBuilder.java
License:Apache License
@Override protected void doBuild(final Path root, final ProjectMetadata metadata, final Sheet sheet) throws IOException { final List<String> profiles = getProfiles(sheet.getRow(1)); for (int i = 1; i <= profiles.size(); i++) { String profile = profiles.get(i - 1); String fileName = FILE_NAME_PREFIX + FileType.DOT_PROPERTIES; if (!PROFILE_DEFAULT.equals(profile)) { fileName = FILE_NAME_PREFIX + "-" + profile + FileType.DOT_PROPERTIES; }/*from w w w . j a v a 2 s. c o m*/ final Path targetPath = Paths.get(root + File.separator + properties.getExportRelativePath()); if (!Files.exists(targetPath)) { Files.createDirectories(targetPath); } try (final FileWriter writer = new FileWriter( Paths.get(targetPath + File.separator + fileName).toFile())) { for (final Row row : sheet) { // ignore the first and second rows if (row.getRowNum() < 2) { continue; } // set comment final Cell cell = row.getCell(3); if (cell != null) { writer.write("#" + cell.getStringCellValue() + "\r\n"); } final Cell cell0 = row.getCell(0); final Cell celli = row.getCell(i); if (cell0 != null && celli != null) { writer.write(cell0.getStringCellValue() + "=" + celli.getStringCellValue() + "\r\n"); } } } } }
From source file:common.ReadExcelData.java
License:Apache License
public Integer findRow(HSSFSheet sheet, int cellContent) { for (Row row : sheet) { for (Cell cell : row) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { Double d = cell.getNumericCellValue(); if (d.intValue() == cellContent) { return row.getRowNum(); }// w w w . j ava2 s.c om } } } return null; }
From source file:comparararchivos.CompararArchivos.java
/** * @param args the command line arguments *//*w w w .ja v a2s . c om*/ public static void main(String[] args) { // TODO code application logic here File excel1 = null; FileInputStream fl1 = null; XSSFWorkbook book1 = null; File excel2 = null; FileInputStream fl2 = null; XSSFWorkbook book2 = null; try { excel1 = new File("D:\\Users\\jose.gil\\Documents\\Anotaciones\\ProfesorB.xlsx"); excel2 = new File("D:\\Users\\jose.gil\\Documents\\Anotaciones\\ProfesorD.xlsx"); PrintWriter file = new PrintWriter("diferencias.txt", "UTF-8"); fl1 = new FileInputStream(excel1); fl2 = new FileInputStream(excel2); book1 = new XSSFWorkbook(fl1); book2 = new XSSFWorkbook(fl2); XSSFSheet sheet_A = book1.getSheetAt(0); XSSFSheet sheet_B = book2.getSheetAt(0); Iterator<Row> itrA = sheet_A.iterator(); Iterator<Row> itrB = sheet_B.iterator(); int totalDiferencias = 0; int numFila = 2; while (itrA.hasNext() && itrB.hasNext()) { Row rowA = itrA.next(); Row rowB = itrB.next(); if (rowA.getRowNum() == 0) continue; Iterator<Cell> cellitA = rowA.cellIterator(); Iterator<Cell> cellitB = rowB.cellIterator(); Cell celA = cellitA.next(); Cell celB = cellitB.next(); //Se esta en las celdas del numero de Aviso celA = cellitA.next(); celB = cellitB.next(); int numAvisoA = (int) celA.getNumericCellValue(); int numAvisoB = (int) celB.getNumericCellValue(); if (numAvisoA != numAvisoB) { System.out.println("Numero de Aviso: " + numAvisoA); continue; } //Se esta en las celdas de la categoria celA = cellitA.next(); celB = cellitB.next(); String textA = celA.getStringCellValue(); //System.out.println("Categoria A: "+textA); String textB = celB.getStringCellValue(); //System.out.println("Categoria B: "+textB); if (!textA.equals(textB)) { System.out.println("Fila: " + numFila + " Numero de Aviso: " + numAvisoA + " Texto B: " + textA + " - Texto D: " + textB); file.println("Fila: " + numFila + " Numero de Aviso: " + numAvisoA + " Texto B: " + textA + " - Texto D: " + textB); totalDiferencias++; } numFila++; } System.out.println("\nTotal diferencias: " + totalDiferencias); file.println("\nTotal diferencias: " + totalDiferencias); file.close(); } catch (FileNotFoundException fe) { fe.printStackTrace(); } catch (IOException ie) { ie.printStackTrace(); } }
From source file:comparator.Comparator.java
public static void delta_MVC_MTC() throws IOException { //Get the input files //FileInputStream mtcFile = new FileInputStream(new File("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\workingMTC.xlsx")); //FileInputStream mvcFile = new FileInputStream(new File("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\Informal_epSOS-MVC_V1_9.xlsx")); FileInputStream mtcFile = new FileInputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0.xlsx")); FileInputStream mtcFile2 = new FileInputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0.xlsx")); FileInputStream mvcFile = new FileInputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\Informal_epSOS-MVC_V2_0_(DRAFT)_03.xlsx")); //Prepare the output file //Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\delta_Mtc-Mvc.csv"), "UTF-8")); Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\delta_Mtc-Mvc2.1.csv"), "UTF-8")); csvW.write('\ufeff'); csvW.write("Expand Project;"); csvW.write("\n\n"); //Get the workbook instance for XLS file XSSFWorkbook mtcWorkbook = new XSSFWorkbook(mtcFile); XSSFWorkbook mtcWorkbook2 = new XSSFWorkbook(mtcFile2); XSSFWorkbook mvcWorkbook = new XSSFWorkbook(mvcFile); //Output/*from ww w . ja v a 2s. c om*/ csvW.write("One MTC sheet is missing in MVC : VS16_epSOSErrorCodes;"); csvW.write("\n"); csvW.write("********************;"); csvW.write("\n"); csvW.write("Set name;"); csvW.write("\n"); csvW.write("MTC mismatches;List of the codes missing in MVC"); csvW.write("\n"); csvW.write("MVC mismatches;List of the codes missing in MTC"); csvW.write("\n"); csvW.write("********************;"); XSSFSheet mtcSheet; XSSFSheet mtcSheet2; Iterator<Row> mtcRowIterator; Iterator<Row> mtcRowIterator2; Iterator<Row> mvcRowIterator; Iterator<Cell> mtcCellIterator; Iterator<Cell> mvcCellIterator; int mtcCol; int mvcCol; boolean mtcColFound; boolean mvcColFound; ArrayList mtcCodes; ArrayList mvcCodes; ArrayList mtcEnglishNames; ArrayList mvcEnglishNames; ArrayList englishNamesdifferences; Row mtcRow; Row mtcRow2; Row mvcRow; Row mvcRow2; Row newRow; Cell newCell; CellStyle myStyle; String mtcSplit[]; String mvcSplit[]; String mtcSheetName; String mvcSheetName; //Get the sheet from the MTC workbook for (int i = 0; i < mtcWorkbook.getNumberOfSheets(); i++) { mtcSheet = mtcWorkbook.getSheetAt(i); mtcSheet2 = mtcWorkbook2.getSheetAt(i); //Get iterator to all the rows in current MTC sheet mtcRowIterator = mtcSheet.iterator(); mtcRowIterator2 = mtcSheet2.iterator(); //Get the sheet from the MVC workbook for (int j = 0; j < mvcWorkbook.getNumberOfSheets(); j++) { XSSFSheet mvcSheet = mvcWorkbook.getSheetAt(j); //Get iterator to all the rows in current MVC sheet mvcRowIterator = mvcSheet.iterator(); //Get the name of MTC sheet and MVC sheet, compare them if they contain data //MTC data files are called "VSX_sheetName" //MVC data files are called "epSOSsheetName" mtcSplit = mtcSheet.getSheetName().split("_"); mvcSplit = mvcSheet.getSheetName().split("SOS"); mtcSheetName = mtcSplit[mtcSplit.length - 1]; mvcSheetName = mvcSplit[mvcSplit.length - 1]; //And process the file matching or throw out the file that has no equivalent if (mtcSheetName.equals(mvcSheetName)) { mtcCol = 0; mvcCol = 0; mtcColFound = false; mvcColFound = false; mtcCodes = new ArrayList(); mvcCodes = new ArrayList(); mtcEnglishNames = new ArrayList(); mvcEnglishNames = new ArrayList(); englishNamesdifferences = new ArrayList(); //For each row, iterate through each columns //Get iterator to all cells of current row //In MTC while (mtcRowIterator.hasNext()) { mtcRow = mtcRowIterator.next(); mtcRow2 = mtcRow; if (mtcColFound == false) { mtcCellIterator = mtcRow.cellIterator(); while (mtcCellIterator.hasNext()) { Cell mtcCell = mtcCellIterator.next(); if (mtcCell.getCellType() == 1 && (mtcCell.getStringCellValue().equals("Code") || mtcCell.getStringCellValue().equals("epSOS Code"))) { mtcCol = mtcCell.getColumnIndex(); mtcColFound = true; break; } } } else { mtcRow.getCell(mtcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING); mtcRow2.getCell(mtcCol + 1, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); mtcCodes.add(mtcRow.getCell(mtcCol).getStringCellValue().trim()); mtcEnglishNames.add(mtcRow2.getCell(mtcCol + 1).getStringCellValue().trim()); } } //In MVC while (mvcRowIterator.hasNext()) { mvcRow = mvcRowIterator.next(); mvcRow2 = mvcRow; if (mvcColFound == false) { mvcCellIterator = mvcRow.cellIterator(); while (mvcCellIterator.hasNext()) { Cell mvcCell = mvcCellIterator.next(); if (mvcCell.getCellType() == 1 && (mvcCell.getStringCellValue().equals("epSOS Code") || mvcCell.getStringCellValue().equals("Code"))) { mvcCol = mvcCell.getColumnIndex(); mvcColFound = true; break; } } } else { mvcRow.getCell(mvcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING); mvcRow2.getCell(mvcCol + 1, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); mvcCodes.add(mvcRow.getCell(mvcCol).getStringCellValue().trim()); mvcEnglishNames.add(mvcRow2.getCell(mvcCol + 1).getStringCellValue().trim()); } } //Processing colCompare(mtcCodes, mvcCodes, mvcEnglishNames, mtcEnglishNames, englishNamesdifferences); //Output //if((!mtcCodes.isEmpty()) || (!mvcCodes.isEmpty())) {} csvW.write("\n\n"); csvW.write(mtcSheetName + ";"); csvW.write("\n"); csvW.write("MTC mismatches;"); for (int a = 0; a < mtcCodes.size(); a++) { csvW.write(mtcCodes.get(a) + ";"); } csvW.write("\n"); csvW.write("MVC mismatches\n"); for (int b = 0; b < mvcCodes.size(); b++) { csvW.write(mvcCodes.get(b) + ";" + mvcEnglishNames.get(b) + "\n"); } csvW.write("english names differences\n"); if (!englishNamesdifferences.isEmpty()) { csvW.write("code;MTC 2.0;MVC 2.0.1\n"); for (int c = 0; c < englishNamesdifferences.size(); c = c + 3) { csvW.write(englishNamesdifferences.get(c) + ";" + englishNamesdifferences.get(c + 1) + ";" + englishNamesdifferences.get(c + 2) + "\n"); } } /* work on currents MTC2.0 sheet */ mtcColFound = false; mtcCol = 0; List<Integer> delRows = new ArrayList(); //recreate iterator to all the rows in current MTC sheet while (mtcRowIterator2.hasNext()) { mtcRow = mtcRowIterator2.next(); mtcRow2 = mtcRow; if (mtcColFound == false) { mtcCellIterator = mtcRow.cellIterator(); while (mtcCellIterator.hasNext()) { Cell mtcCell = mtcCellIterator.next(); if (mtcCell.getCellType() == 1 && (mtcCell.getStringCellValue().equals("Code") || mtcCell.getStringCellValue().equals("epSOS Code"))) { mtcCol = mtcCell.getColumnIndex(); mtcColFound = true; break; } } } else { mtcRow.getCell(mtcCol, Row.RETURN_NULL_AND_BLANK).setCellType(Cell.CELL_TYPE_STRING); mtcRow2.getCell(mvcCol + 1, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); for (int a = 0; a < mtcCodes.size(); a++) { if (mtcRow.getCell(mtcCol).getStringCellValue().trim().equals(mtcCodes.get(a))) { // delete row corresponding to useless code delRows.add(mtcRow.getRowNum()); break; } } if (!englishNamesdifferences.isEmpty()) { for (int c = 0; c < englishNamesdifferences.size(); c = c + 3) { if (mtcRow2.getCell(mtcCol + 1).getStringCellValue().trim() .equals(englishNamesdifferences.get(c + 1))) { mtcRow2.getCell(mtcCol + 1) .setCellValue(englishNamesdifferences.get(c + 2).toString()); break; } } } } } for (int d = delRows.size() - 1; d >= 0; d--) { mtcSheet2.shiftRows(delRows.get(d) + 1, mtcSheet2.getLastRowNum() + 1, -1); } myStyle = mtcSheet2.getRow(0).getCell(0).getCellStyle(); for (int b = 0; b < mvcCodes.size(); b++) { newRow = mtcSheet2.createRow(mtcSheet2.getLastRowNum() + 1); for (int bb = 0; bb < mtcSheet2.getRow(0).getLastCellNum(); bb++) { newCell = newRow.createCell(bb); newCell.setCellStyle(myStyle); if (bb == mtcCol) { newCell.setCellValue(mvcCodes.get(b).toString()); } else if (bb == mtcCol + 1) { newCell.setCellValue(mvcEnglishNames.get(b).toString()); } } } } } } //close InputStream mtcFile.close(); mtcFile2.close(); mvcFile.close(); //close OutputStream csvW.close(); //Open FileOutputStream to write updates FileOutputStream output_file = new FileOutputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0_new.xlsx")); //write changes mtcWorkbook2.write(output_file); //close the stream output_file.close(); }
From source file:controller.DAOData.java
public Plan readPlan() { ArrayList<Course> courses = new ArrayList<>(); //takes the sheet you ask for XSSFSheet sheet = workbook.getSheet("PLAN"); //for to go over the sheet info - like the rows for (Row row : sheet) { String code = null;/*from w w w . ja v a2s .co m*/ String name = null; double credits = 0; for (Cell cell : row) { if (row.getRowNum() != 0) { switch (cell.getColumnIndex()) { case 0: name = cell.getStringCellValue(); break; case 1: code = cell.getStringCellValue(); break; case 2: credits = cell.getNumericCellValue(); break; } } } if (code != null) { Course course = new Course(code, name, credits); courses.add(course); code = null; name = null; credits = 0; } } Plan plan = new Plan(410, 2010, courses); return plan; }
From source file:controller.DAOData.java
public ArrayList<Employee> readProfessors() { ArrayList<Employee> employees = new ArrayList<>(); //takes the sheet you ask for XSSFSheet sheet = workbook.getSheet("PROFESORES"); //for to go over the sheet info - like the rows for (Row row : sheet) { String id = null;/*from w w w . ja v a 2 s . co m*/ String name = null; String email = null; String phone = null; for (Cell cell : row) { if (row.getRowNum() != 0) { switch (cell.getColumnIndex()) { case 0: id = cell.getStringCellValue(); break; case 1: name = cell.getStringCellValue(); break; case 2: email = cell.getStringCellValue(); break; case 3: phone = cell.getStringCellValue(); break; } } } if (id != null) { employees.add(new Employee(EEmployeeRol.PROFESSOR, id, name, email, phone)); id = null; name = null; email = null; phone = null; } } return employees; }
From source file:controller.DAOData.java
public ArrayList<Object> readGroups() { ArrayList<Object> groups = new ArrayList(); XSSFSheet sheet = workbook.getSheet("OFERTA"); //for to go over the sheet info - like the rows for (Row row : sheet) { String period = null;/*from w w w . j a v a 2 s.c o m*/ Course course = null; int numberGroup = 0; Employee employee = null; ArrayList<Schedule> schedules = new ArrayList(); String scheduleS = null; String classroom = null; for (Cell cell : row) { if (row.getRowNum() != 0) { Schedule schedule = new Schedule(); switch (cell.getColumnIndex()) { case 0: period = cell.getStringCellValue(); break; case 1: course = School.getInstance().selectCourse(cell.getStringCellValue()); break; case 2: numberGroup = (int) cell.getNumericCellValue(); break; case 3: employee = School.getInstance().findEmployee(cell.getStringCellValue()); break; case 4: scheduleS = cell.getStringCellValue(); break; case 5: classroom = cell.getStringCellValue(); break; } } } if (period != null) { schedules = identifySchedules(scheduleS, classroom); groups.add(new Group(numberGroup, period, true, employee, schedules, course)); numberGroup = 0; course = null; period = null; employee = null; schedules = null; } } return groups; }
From source file:controller.DAORequest.java
public ArrayList<Object> readRequests() { ArrayList<Object> requests = new ArrayList(); ArrayList<Resolution> resolutions = readResolutions(); XSSFSheet sheet = workbook.getSheetAt(0); for (Row row : sheet) { System.out.println("1"); Date date = null;//from w w w .j a v a 2s . co m Student affected; String carnet = null; String name = null; String email = null; String celStu = null; Group group; String period = null; String course = null; int numberGroup = 0; EInconsistencie einconsistencie; String inconsistencie = null; String description = null; Person requester; String idReq = null; String nameReq = null; ERequestState reqState; String sreqState = null; int numRes = 0; for (Cell cell : row) { if (row.getRowNum() != 0) { switch (cell.getColumnIndex()) { case 0: date = row.getCell(0).getDateCellValue(); break; case 1: if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) carnet = Integer.toString((int) cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) carnet = cell.getStringCellValue(); break; case 2: //es el nombre del estudiante name = cell.getStringCellValue(); break; case 3: email = cell.getStringCellValue(); break; case 4: if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) celStu = Integer.toString((int) cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) celStu = cell.getStringCellValue(); break; case 5: period = cell.getStringCellValue(); break; case 6: course = cell.getStringCellValue(); break; case 7: numberGroup = (int) cell.getNumericCellValue(); break; case 8: inconsistencie = cell.getStringCellValue(); break; case 9: description = cell.getStringCellValue(); break; case 10: if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) idReq = Integer.toString((int) cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) idReq = cell.getStringCellValue(); break; case 11: nameReq = cell.getStringCellValue(); break; case 12: sreqState = cell.getStringCellValue(); break; case 13: if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) numRes = (int) cell.getNumericCellValue(); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) numRes = Integer.parseInt(cell.getStringCellValue()); break; } } } if (carnet != null) { affected = new Student(carnet, name, email, celStu); requester = new Person(idReq, nameReq, null, null); group = School.getInstance().selectGroup(period, numberGroup, course); einconsistencie = identifyEInconsistencie(inconsistencie); if (sreqState == null) { sreqState = "PENDIENTE"; } reqState = identifyEReqState(sreqState); Request request = new Request(date, description, einconsistencie, reqState, affected, requester, group); request.setRequestState(reqState); requests.add(request); if (numRes != 0) { for (Resolution r : resolutions) { if (r.getId() == numRes) request.setResolution(r); System.out.println("linquea numRes: " + numRes + " con ReqId:" + request.getId()); } } } } return requests; }