List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet rowIterator
@Override @SuppressWarnings("unchecked") public Iterator<Row> rowIterator()
From source file:org.protempa.test.XlsxDataProvider.java
License:Apache License
/** * Parse the list of ICD9 Diagnostic codes present in the workbook. * * @return A list of {@link Icd9Diagnosis} objects. *///from ww w .ja v a 2 s.c o m private List<Icd9Diagnosis> readIcd9Diagnoses() { XSSFSheet sheet = this.workbook.getSheet("eICD9D"); List<Icd9Diagnosis> result = new ArrayList<>(); Iterator<Row> rows = sheet.rowIterator(); rows.next(); // skip header row while (rows.hasNext()) { Row row = rows.next(); Icd9Diagnosis diagnosis = new Icd9Diagnosis(); diagnosis.setId(XlsxDataProvider.readStringValue(row.getCell(0))); diagnosis.setEncounterId(XlsxDataProvider.readLongValue(row.getCell(1))); diagnosis.setTimestamp(XlsxDataProvider.readDateValue(row.getCell(2))); diagnosis.setEntityId(XlsxDataProvider.readStringValue(row.getCell(3))); diagnosis.setCreateDate(XlsxDataProvider.readDateValue(row.getCell(4))); diagnosis.setUpdateDate(XlsxDataProvider.readDateValue(row.getCell(5))); diagnosis.setDeleteDate(XlsxDataProvider.readDateValue(row.getCell(6))); result.add(diagnosis); } return result; }
From source file:org.protempa.test.XlsxDataProvider.java
License:Apache License
/** * Parse the list of ICD9 Procedure codes present in the workbook. * * @return A list of {@link Icd9Procedure} objects. *//*w w w . j av a 2 s . co m*/ private List<Icd9Procedure> readIcd9Procedures() { XSSFSheet sheet = this.workbook.getSheet("eICD9P"); List<Icd9Procedure> result = new ArrayList<>(); Iterator<Row> rows = sheet.rowIterator(); rows.next(); // skip header row while (rows.hasNext()) { Row row = rows.next(); Icd9Procedure procedure = new Icd9Procedure(); procedure.setId(XlsxDataProvider.readStringValue(row.getCell(0))); procedure.setEncounterId(XlsxDataProvider.readLongValue(row.getCell(1))); procedure.setTimestamp(XlsxDataProvider.readDateValue(row.getCell(2))); procedure.setEntityId(XlsxDataProvider.readStringValue(row.getCell(3))); procedure.setCreateDate(XlsxDataProvider.readDateValue(row.getCell(4))); procedure.setUpdateDate(XlsxDataProvider.readDateValue(row.getCell(5))); procedure.setDeleteDate(XlsxDataProvider.readDateValue(row.getCell(6))); result.add(procedure); } return result; }
From source file:org.protempa.test.XlsxDataProvider.java
License:Apache License
/** * Parse the list of medications present in the workbook. * * @return A list of {@link Medication} objects. *//* w w w . j a va2 s.c o m*/ private List<Medication> readMedications() { XSSFSheet sheet = this.workbook.getSheet("eMEDS"); List<Medication> result = new ArrayList<>(); Iterator<Row> rows = sheet.rowIterator(); rows.next(); // skip header row while (rows.hasNext()) { Row row = rows.next(); Medication medication = new Medication(); medication.setId(XlsxDataProvider.readStringValue(row.getCell(0))); medication.setEncounterId(XlsxDataProvider.readLongValue(row.getCell(1))); medication.setTimestamp(XlsxDataProvider.readDateValue(row.getCell(2))); medication.setEntityId(XlsxDataProvider.readStringValue(row.getCell(3))); medication.setCreateDate(XlsxDataProvider.readDateValue(row.getCell(4))); medication.setUpdateDate(XlsxDataProvider.readDateValue(row.getCell(5))); medication.setDeleteDate(XlsxDataProvider.readDateValue(row.getCell(6))); result.add(medication); } return result; }
From source file:org.protempa.test.XlsxDataProvider.java
License:Apache License
/** * Parse the list of labs present in the workbook's "eLABS" worksheet. * * @return A list of {@link Lab} objects. *//*ww w.j a v a 2 s . c om*/ private List<Lab> readLabs() { XSSFSheet sheet = this.workbook.getSheet("eLABS"); List<Lab> result = new ArrayList<>(); Iterator<Row> rows = sheet.rowIterator(); rows.next(); // skip header row while (rows.hasNext()) { Row row = rows.next(); Lab lab = new Lab(); lab.setId(XlsxDataProvider.readStringValue(row.getCell(0))); lab.setEncounterId(XlsxDataProvider.readLongValue(row.getCell(1))); lab.setTimestamp(XlsxDataProvider.readDateValue(row.getCell(2))); lab.setEntityId(XlsxDataProvider.readStringValue(row.getCell(3))); lab.setResultAsStr(XlsxDataProvider.readStringValue(row.getCell(4))); lab.setResultAsNum(XlsxDataProvider.readDoubleValue(row.getCell(5))); lab.setUnits(XlsxDataProvider.readStringValue(row.getCell(6))); lab.setFlag(XlsxDataProvider.readStringValue(row.getCell(7))); lab.setCreateDate(XlsxDataProvider.readDateValue(row.getCell(8))); lab.setUpdateDate(XlsxDataProvider.readDateValue(row.getCell(9))); lab.setDeleteDate(XlsxDataProvider.readDateValue(row.getCell(10))); result.add(lab); } return result; }
From source file:org.protempa.test.XlsxDataProvider.java
License:Apache License
/** * Parse the list of vitals present in the workbook's "eVITALS" worksheet. * * @return A list of {@link Vital} objects. *//*from w w w .ja va 2 s .co m*/ private List<Vital> readVitals() { XSSFSheet sheet = this.workbook.getSheet("eVITALS"); List<Vital> result = new ArrayList<>(); Iterator<Row> rows = sheet.rowIterator(); rows.next(); // skip header row while (rows.hasNext()) { Row row = rows.next(); Vital vital = new Vital(); vital.setId(readStringValue(row.getCell(0))); vital.setEncounterId(readLongValue(row.getCell(1))); vital.setTimestamp(readDateValue(row.getCell(2))); vital.setEntityId(readStringValue(row.getCell(3))); vital.setResultAsStr(readStringValue(row.getCell(4))); vital.setResultAsNum(readDoubleValue(row.getCell(5))); vital.setUnits(readStringValue(row.getCell(6))); vital.setFlag(readStringValue(row.getCell(7))); vital.setCreateDate(readDateValue(row.getCell(8))); vital.setUpdateDate(readDateValue(row.getCell(9))); vital.setDeleteDate(readDateValue(row.getCell(10))); result.add(vital); } return result; }
From source file:org.sakaiproject.tool.gradebook.ui.SpreadsheetUploadBean.java
License:Educational Community License
/** * Parse newer OOXML Excel Spreadsheets// w ww .j a va2 s . c om * @param inputStreams * @return * @throws IOException */ private List<String> excelOOXMLToArray(InputStream inputStreams) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(inputStreams); //Convert an Excel OOXML (.xslx) file to csv XSSFSheet sheet = wb.getSheetAt(0); List<String> array = new ArrayList<String>(); Iterator it = sheet.rowIterator(); while (it.hasNext()) { XSSFRow row = (XSSFRow) it.next(); String rowAsString = fromXSSFRowtoCSV(row); if (rowAsString.replaceAll(",", "").replaceAll("\"", "").equals("")) { continue; } array.add(fromXSSFRowtoCSV(row)); } return array; }
From source file:org.syrahtest.core.data.xls.ExcelReader.java
License:Open Source License
public static void readXLSXFile() throws IOException { InputStream ExcelFileToRead = new FileInputStream("C:/Test.xlsx"); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFWorkbook test = new XSSFWorkbook(); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row;/* w w w. j a v a 2 s . c o m*/ XSSFCell cell; Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { cell = (XSSFCell) cells.next(); if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { System.out.print(cell.getStringCellValue() + " "); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { System.out.print(cell.getNumericCellValue() + " "); } else { //U Can Handel Boolean, Formula, Errors } } System.out.println(); } }
From source file:org.wise.portal.presentation.web.controllers.run.MergeSpreadsheetsController.java
License:Open Source License
@RequestMapping(method = RequestMethod.POST) protected ModelAndView onSubmit(@RequestParam("uploadFile") MultipartFile uploadFile, @RequestParam("mergeColumnTitle") String mergeColumnTitle, HttpServletResponse response) throws Exception { // TODO: this line is saving uploadFile to home directory. Can we do without saving to home directory? File file = multipartToFile(uploadFile); String mergedResultFileName = "merged_" + file.getName(); FileInputStream fis = new FileInputStream(file); // Finds the workbook instance of XLSX file XSSFWorkbook workbook = new XSSFWorkbook(fis); DataFormatter objDefaultFormat = new DataFormatter(); FormulaEvaluator objFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook); // number of sheets in the workbook int numberOfSheets = workbook.getNumberOfSheets(); // contains all values of the merge column across all sheets ArrayList<String> mergeColumnValues = new ArrayList<String>(); // maps mergeColumn value to a Map<SheetIndex, ArrayList<Row>> HashMap<String, HashMap<Integer, ArrayList<Row>>> mergeColumnValueToSheetRows = new HashMap<String, HashMap<Integer, ArrayList<Row>>>(); // maps sheet index to the headers in that sheet HashMap<Integer, ArrayList<String>> sheetIndexToSheetColumnHeaders = new HashMap<Integer, ArrayList<String>>(); // how many copies of headers need to be created for each sheet HashMap<Integer, Integer> sheetIndexToMaxSheetRowCount = new HashMap<Integer, Integer>(); // loop through the sheets in the workbook and populate the variables for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) { XSSFSheet sheet = workbook.getSheetAt(sheetIndex); int mergeColumnIndex = -1; // index of the merge column in this sheet int rowIteratorIndex = 0; // index of current row iteration // collect all of the merge column rows in each sheet Iterator<Row> rowIterator = sheet.rowIterator(); int maxSheetRowCountForCurrentSheet = 0; while (rowIterator.hasNext()) { Row row = (Row) rowIterator.next(); if (rowIteratorIndex == 0) { // for the very first row in this sheet, go through all the cells in the top row and add to sheetColumnHeaders // and add it to sheetIndexToSheetColumnHeaders ArrayList<String> sheetColumnHeaders = new ArrayList<String>(); int rowCellIteratorIndex = 0; Iterator<Cell> topRowCellIterator = row.cellIterator(); while (topRowCellIterator.hasNext()) { Cell topRowCell = topRowCellIterator.next(); String topRowCellString = topRowCell.toString(); if (!topRowCellString.isEmpty()) { sheetColumnHeaders.add(topRowCellString); }// w w w . ja va 2 s. c o m if (!topRowCellString.isEmpty() && topRowCellString.equals(mergeColumnTitle)) { // this is the mergeColumn. Remember the column index if (mergeColumnIndex == -1) { mergeColumnIndex = rowCellIteratorIndex; } else { // there are multiple mergeColumnTitles in this sheet. Let the user know and exit ModelAndView mav = new ModelAndView("/admin/run/mergespreadsheets"); mav.addObject("errorMsg", "You have multiple columns titled \"" + mergeColumnTitle + "\" in worksheet #" + (sheetIndex + 1) + ". You can have only one merge column per worksheet. Please fix and try again."); return mav; } } rowCellIteratorIndex++; } sheetIndexToSheetColumnHeaders.put(sheetIndex, sheetColumnHeaders); } else { // for rows that are not the top row (header) // 1. get all the mergeColumnValues // 2. populate mergeColumnValueToSheetRows // 3. calculate sheetIndexToMaxSheetRowCount Cell mergeColumnValueCell = row.getCell(mergeColumnIndex); if (mergeColumnValueCell != null && !mergeColumnValueCell.toString().isEmpty()) { objFormulaEvaluator.evaluate(mergeColumnValueCell); String mergeColumnValueString = objDefaultFormat.formatCellValue(mergeColumnValueCell, objFormulaEvaluator); HashMap<Integer, ArrayList<Row>> sheetIndexToSheetRows = mergeColumnValueToSheetRows .get(mergeColumnValueString); if (sheetIndexToSheetRows == null) { sheetIndexToSheetRows = new HashMap<Integer, ArrayList<Row>>(); mergeColumnValueToSheetRows.put(mergeColumnValueString, sheetIndexToSheetRows); } ArrayList<Row> sheetRows = sheetIndexToSheetRows.get(sheetIndex); if (sheetRows == null) { sheetRows = new ArrayList<>(); sheetIndexToSheetRows.put(sheetIndex, sheetRows); } sheetRows.add(row); if (sheetRows.size() > maxSheetRowCountForCurrentSheet) { maxSheetRowCountForCurrentSheet = sheetRows.size(); } Iterator<Cell> rowCellIterator = row.cellIterator(); int rowCellIteratorIndex = 0; while (rowCellIterator.hasNext()) { Cell rowCell = rowCellIterator.next(); if (rowCellIteratorIndex == mergeColumnIndex) { // this is a merge column cell, so add its value to mergeColumnValues if (!rowCell.toString().isEmpty()) { objFormulaEvaluator.evaluate(rowCell); String rowCellValueString = objDefaultFormat.formatCellValue(rowCell, objFormulaEvaluator); if (!mergeColumnValues.contains(rowCellValueString)) { mergeColumnValues.add(rowCellValueString); } } } rowCellIteratorIndex++; } } } rowIteratorIndex++; } sheetIndexToMaxSheetRowCount.put(sheetIndex, maxSheetRowCountForCurrentSheet); } // Now we are ready to make the merge sheet. We will be writing one row at a time. Workbook wb = new XSSFWorkbook(); // new output workbook Sheet mergedSheet = wb.createSheet("merged"); // output merged result in "merged" sheet // make the header row Row headerRow = mergedSheet.createRow(0); // (0,0) will be the merge cell header. Column 0 will contain mergeColumnValues. Cell mergeColumnHeaderCell = headerRow.createCell(0); mergeColumnHeaderCell.setCellValue(mergeColumnTitle); // current column index "cursor" where we will be writing to int cellIndexWithoutMergeColumn = 1; // make the header row for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) { Integer maxSheetRowCount = sheetIndexToMaxSheetRowCount.get(sheetIndex); ArrayList<String> sheetColumnHeaders = sheetIndexToSheetColumnHeaders.get(sheetIndex); XSSFSheet sheet = workbook.getSheetAt(sheetIndex); String sheetName = sheet.getSheetName(); for (int i = 0; i < maxSheetRowCount; i++) { for (int sheetColumnHeaderIndex = 0; sheetColumnHeaderIndex < sheetColumnHeaders .size(); sheetColumnHeaderIndex++) { String sheetColumnHeader = sheetColumnHeaders.get(sheetColumnHeaderIndex); if (!sheetColumnHeader.isEmpty() && !sheetColumnHeader.equals(mergeColumnTitle)) { String newSheetColumnHeader = sheetColumnHeader + " ( " + sheetName + " " + (i + 1) + " ) "; Cell headerCell = headerRow.createCell(cellIndexWithoutMergeColumn); headerCell.setCellValue(newSheetColumnHeader); cellIndexWithoutMergeColumn++; } } } } // now make all the non-header rows for (int mergeColumnValueIndex = 0; mergeColumnValueIndex < mergeColumnValues .size(); mergeColumnValueIndex++) { String mergeColumnValue = mergeColumnValues.get(mergeColumnValueIndex); HashMap<Integer, ArrayList<Row>> mergeColumnValueSheetRow = mergeColumnValueToSheetRows .get(mergeColumnValue); if (mergeColumnValueSheetRow == null) { System.out.println("Null mergeColumnValueSheetRow, continuing. mergeColumnValueIndex: " + mergeColumnValueIndex + " mergeColumnValue: " + mergeColumnValue); continue; } Row row = mergedSheet.createRow(mergeColumnValueIndex + 1); // + 1 is to account for the header row; // reset current cursor as we make each row cellIndexWithoutMergeColumn = 0; // first column will be the merge column value Cell mergeColumnCell = row.createCell(0); mergeColumnCell.setCellValue(mergeColumnValue); cellIndexWithoutMergeColumn++; for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) { ArrayList<Row> sheetRows = mergeColumnValueSheetRow.get(sheetIndex); int currentSheetSheetRowIndex = 0; ArrayList<String> sheetHeaders = sheetIndexToSheetColumnHeaders.get(sheetIndex); if (sheetRows != null) { for (int sheetRowIndex = 0; sheetRowIndex < sheetRows.size(); sheetRowIndex++) { Row sheetRow = sheetRows.get(sheetRowIndex); for (int sheetHeaderIndex = 0; sheetHeaderIndex < sheetHeaders.size(); sheetHeaderIndex++) { String sheetHeader = sheetHeaders.get(sheetHeaderIndex); if (!sheetHeader.equals(mergeColumnTitle)) { Cell cell = sheetRow.getCell(sheetHeaderIndex); Cell exportCell = row.createCell(cellIndexWithoutMergeColumn); objFormulaEvaluator.evaluate(cell); String cellString = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator); exportCell.setCellValue(cellString); cellIndexWithoutMergeColumn++; } } currentSheetSheetRowIndex++; } } // some columns do not have any values to populate, so populate them with empty cells Integer maxSheetRowCount = sheetIndexToMaxSheetRowCount.get(sheetIndex); while (currentSheetSheetRowIndex < maxSheetRowCount) { for (int i = 0; i < sheetHeaders.size(); i++) { String sheetHeader = sheetHeaders.get(i); if (!sheetHeader.isEmpty() && !sheetHeader.equals(mergeColumnTitle)) { Cell exportCell = row.createCell(cellIndexWithoutMergeColumn); exportCell.setCellValue(""); cellIndexWithoutMergeColumn++; } } currentSheetSheetRowIndex++; } } } // write to response output response.setHeader("Content-Disposition", "attachment; filename=\"" + mergedResultFileName + "\""); ServletOutputStream outputStream = response.getOutputStream(); wb.write(outputStream); fis.close(); return null; }
From source file:parser.CloudDSFParser.java
License:Apache License
/** * Retrieves influencing relations between decisions. All decisions (influencing, affecting, * binding) are parsed and stored as basic influencing ones for the cloudDSF. * //from w w w. java 2 s.c om * @return */ private void setInfluencingRelations() { XSSFSheet sheet = workbook.getSheet("Decision Level"); // Column B has name of start Decision int startDecisionColumn = 1; // Row 1 has names of endDecision Row endDecisionRow = sheet.getRow(1); // Iterate over all rows starting at 3 Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { XSSFRow row = (XSSFRow) rows.next(); // Iterate over cells Iterator<Cell> cells = row.cellIterator(); while (cells.hasNext()) { XSSFCell cell = (XSSFCell) cells.next(); String relationName = cell.getStringCellValue(); if (relationName.equals("Influencing") || relationName.equals("Affecting") || relationName.equals("Binding")) { // if type of relationship matches predefined values get names of the two participating // decisions String startDecision = row.getCell(startDecisionColumn).getStringCellValue(); String endDecision = endDecisionRow.getCell(cell.getColumnIndex()).getStringCellValue(); // add new decision relation cdsf.setLegacyDecisionRelation(startDecision, endDecision); } } } }
From source file:parser.CloudDSFParser.java
License:Apache License
/** * Retrieves influencing relations between tasks and decisions. *///from w w w. j ava 2 s . c om private void setInfluencingTasks() { XSSFSheet sheet = workbook.getSheet("Task Level"); // Column A has name of start Task int startTaskColumn = 0; // Row 1 has names of endDecision Row endDecisionRow = sheet.getRow(1); // Iterate over all rows Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { XSSFRow row = (XSSFRow) rows.next(); Iterator<Cell> cells = row.cellIterator(); while (cells.hasNext()) { XSSFCell cell = (XSSFCell) cells.next(); if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { // Depending on the relation type source and target are set // accordingly String relationName = cell.getStringCellValue(); String sourceDesc = row.getCell(startTaskColumn).getStringCellValue(); String targetDesc = endDecisionRow.getCell(cell.getColumnIndex()).getStringCellValue(); switch (relationName) { case "Affecting": cdsf.setTaskRelation(sourceDesc, targetDesc, "oneWay"); break; case "Both": cdsf.setTaskRelation(sourceDesc, targetDesc, "twoWay"); break; case "Affected": cdsf.setTaskRelation(sourceDesc, targetDesc, "backwards"); break; // no default } } } } }