private static void verifyCategoryCell(Cell cell) throws ParseException { verifyStringCell(cell, "category cell"); Category cat = Category.getCategory(cell.getStringCellValue()); if (cat == Category.UNKNOWN) throw new ParseException( "Unknown category: " + cell.getStringCellValue() + "in row " + cell.getRow().getRowNum(), 0); }
private static void verifySpeakerCell(Cell cell) throws ParseException { verifyStringCell(cell, "speaker cell"); Speaker speaker = Speaker.getSpeaker(cell.getStringCellValue()); if (speaker == Speaker.UNKNOWN) { throw new ParseException( "Unknown speaker: " + cell.getStringCellValue() + "in row " + cell.getRow().getRowNum(), 0); } }
public Object getDataFromCell(final Cell cell) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return cell.getNumericCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { return cell.getStringCellValue(); } return null; }
private static InterviewInstance getInstance(Row row) throws ParseException { // extract the cells needed from this row Cell interview_id_cell = row.getCell(0, Row.CREATE_NULL_AS_BLANK); Cell line_cell = row.getCell(1, Row.CREATE_NULL_AS_BLANK); Cell speaker_cell = row.getCell(2, Row.CREATE_NULL_AS_BLANK); Cell comment_cell = row.getCell(3, Row.CREATE_NULL_AS_BLANK); ArrayList<Cell> category_cells = new ArrayList<>(5); for (int i = 4; i <= 8; ++i) { Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK); if (cell.getCellType() != Cell.CELL_TYPE_BLANK) category_cells.add(cell); } Cell sentiment_cell = row.getCell(9, Row.CREATE_NULL_AS_BLANK); // verify that all the required information is present verifyNumericCell(interview_id_cell, "Interview ID field"); verifyNumericCell(line_cell, "Line field"); verifySpeakerCell(speaker_cell); verifyCellNotEmpty(comment_cell, "Comment cell"); if (category_cells.isEmpty()) throw new ParseException("No category labels given for samples in row " + row.getRowNum(), 0); Iterator<Cell> row_iter = category_cells.iterator(); ArrayList<Category> categories = new ArrayList<>(); while (row_iter.hasNext()) { Cell category_cell = row_iter.next(); verifyCategoryCell(category_cell); categories.add(Category.getCategory(category_cell.getStringCellValue())); } // convert the cell into an interviewInstance InterviewInstance instance = new InterviewInstance( (int) interview_id_cell.getNumericCellValue(), (int) line_cell.getNumericCellValue(), Speaker.getSpeaker(speaker_cell.getStringCellValue()), comment_cell.getStringCellValue(), categories); return instance; }
public List<String> getHeaderNames(final Sheet sheet, final int headerPos, final int endColumn) { Row row = sheet.getRow(headerPos - 1); final List<String> headerNames = new ArrayList<String>(); for (int i = 0; i < endColumn; i++) { Cell cell = row.getCell(i); if (cell != null) { String header = cell.getStringCellValue(); header = header.replaceAll(" ", ""); headerNames.add(header); } } return headerNames; }
private void processRow(Row row) { int fc = row.getFirstCellNum(); if (fc == 0) { Cell cell = row.getCell(0); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { columnCount = columnCount < row.getLastCellNum() ? row.getLastCellNum() : columnCount; String rowType = cell.getStringCellValue().toUpperCase(); switch (rowType) { case "TITLE": { title.add(row); } break; case "GROUPH": { String fName = getGroupParam(row); if (!fName.isEmpty()) { detail.addGroup(true, row, fName); } } break; case "DETAIL1": { detail.add(row); } break; case "GROUPF": { String fName = getGroupParam(row); if (!fName.isEmpty()) { detail.addGroup(false, row, fName); } } break; case "SUMMARY": { summary.add(row); } break; } } } }
public void testModifyArrayCells_setCellType() { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); // single-cell array formulas behave just like normal cells - // changing cell type removes the array formula and associated cached result CellRange<? extends Cell> srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5")); Cell scell = srange.getTopLeftCell(); assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType()); assertEquals(0.0, scell.getNumericCellValue()); scell.setCellType(Cell.CELL_TYPE_STRING); assertEquals(Cell.CELL_TYPE_STRING, scell.getCellType()); scell.setCellValue("string cell"); assertEquals("string cell", scell.getStringCellValue()); // once you create a multi-cell array formula, you cannot change the type of its cells CellRange<? extends Cell> mrange = sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3")); for (Cell mcell : mrange) { try { assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType()); mcell.setCellType(Cell.CELL_TYPE_NUMERIC); fail("expected exception"); } catch (IllegalStateException e) { CellReference ref = new CellReference(mcell); String msg = "Cell " + ref.formatAsString() + " is part of a multi-cell array formula. You cannot change part of an array."; assertEquals(msg, e.getMessage()); } // a failed invocation of Cell.setCellType leaves the cell // in the state that it was in prior to the invocation assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType()); assertTrue(mcell.isPartOfArrayFormulaGroup()); } }
public static List<List<List<String>>> readExcel(File file, Rule rule) { int start = rule.getStart(); int end = rule.getEnd(); List<List<List<String>>> result = Lists.newArrayList(); Workbook wb; try { wb = WorkbookFactory.create(file); } catch (Exception e) { throw new ExcelException(e); } for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); List<List<String>> sheetList = Lists.newArrayList(); int rows = sheet.getLastRowNum(); if (start <= sheet.getFirstRowNum()) { start = sheet.getFirstRowNum(); } if (end >= rows) { end = rows; } else if (end <= 0) { end = rows + end; } for (int rowIndex = start; rowIndex <= end; rowIndex++) { Row row = sheet.getRow(rowIndex); List<String> columns = Lists.newArrayList(); int cellNum = row.getLastCellNum(); System.out.println(row.getLastCellNum()); System.out.println(row.getPhysicalNumberOfCells()); for (int cellIndex = row.getFirstCellNum(); cellIndex < cellNum; cellIndex++) { Cell cell = row.getCell(cellIndex); int cellType = cell.getCellType(); String column = ""; switch (cellType) { case Cell.CELL_TYPE_NUMERIC: // DecimalFormat format = new DecimalFormat(); // format.setGroupingUsed(false); column = String.valueOf(cell.getDateCellValue()); break; case Cell.CELL_TYPE_STRING: column = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: column = cell.getBooleanCellValue() + ""; break; case Cell.CELL_TYPE_FORMULA: column = cell.getCellFormula(); break; case Cell.CELL_TYPE_ERROR: case Cell.CELL_TYPE_BLANK: column = " "; break; default: } columns.add(column.trim()); } List<Boolean> rowFilterFlagList = Lists.newArrayList(); List<RowFilter> rowFilterList = Lists.newArrayList(); for (int k = 0; k < rowFilterList.size(); k++) { RowFilter rowFilter = rowFilterList.get(k); rowFilterFlagList.add(rowFilter.doFilter(rowIndex, columns)); } if (!rowFilterFlagList.contains(false)) { sheetList.add(columns); } } result.add(sheetList); } return result; }
public void readProcessSpreadSheet() { processSpreadSheet = System.getProperty("processSpreadSheet"); System.out.println("processSpreadSheet: " + processSpreadSheet); if (processCodesList.size() == 0) { if (processSpreadSheet != null && !processSpreadSheet.equals("")) { Workbook wb1 = null; try { wb1 = new XSSFWorkbook(processSpreadSheet); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } Sheet sheet = wb1.getSheetAt(0); Row row; Cell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int tmp = 0; // This trick ensures that we get the data properly even if it // doesn't start from first few rows for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); // out.println("tmp value"+tmp); if (tmp > cols) { cols = tmp; } } } ProcessDefinition tempProcessCode; for (int r1 = 0; r1 < rows; r1++) { tempProcessCode = new ProcessDefinition(); row = sheet.getRow(r1); if (row != null) { if (row.getCell(0) != null) { for (int counter = 0; counter < cols; counter++) { cell = row.getCell((short) counter); // cell = row.getCell(1); if (counter == 0) { if (cell != null) { tempProcessCode.setProcessName(cell.getStringCellValue()); } else { tempProcessCode.setProcessName(""); } } else if (counter == 1) { if (cell != null) { tempProcessCode.setProcessCode(cell.getStringCellValue()); } else { tempProcessCode.setProcessCode(""); } } else if (counter == 2) { if (cell != null) { tempProcessCode.setIaeaCode(cell.getStringCellValue()); } else { tempProcessCode.setIaeaCode(""); } } else if (counter == 3) { if (cell != null) { tempProcessCode.setProcessDescription(cell.getStringCellValue()); // System.out.println(tempProcessCode.getProcessDescription()); } else { tempProcessCode.setProcessDescription(""); // System.out.println("Process Description EMpty"); } } } } } else { rows++; } processCodesList.add(tempProcessCode); } } } System.out.println(processCodesList.size()); }
protected SCell importCell(Cell poiCell, int row, SSheet sheet) { SCell cell = sheet.getCell(row, poiCell.getColumnIndex()); cell.setCellStyle(importCellStyle(poiCell.getCellStyle())); switch (poiCell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: cell.setNumberValue(poiCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: RichTextString poiRichTextString = poiCell.getRichStringCellValue(); if (poiRichTextString != null && poiRichTextString.numFormattingRuns() > 0) { SRichText richText = cell.setupRichTextValue(); importRichText(poiCell, poiRichTextString, richText); } else { cell.setStringValue(poiCell.getStringCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: cell.setBooleanValue(poiCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: cell.setFormulaValue(poiCell.getCellFormula()); // ZSS-873 if (isImportCache() && !poiCell.isCalcOnLoad() && !mustCalc(cell)) { ValueEval val = null; switch (poiCell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_NUMERIC: val = new NumberEval(poiCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: RichTextString poiRichTextString0 = poiCell.getRichStringCellValue(); if (poiRichTextString0 != null && poiRichTextString0.numFormattingRuns() > 0) { SRichText richText = new RichTextImpl(); importRichText(poiCell, poiRichTextString0, richText); val = new StringEval(richText.getText()); } else { val = new StringEval(poiCell.getStringCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: val = BoolEval.valueOf(poiCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: val = ErrorEval.valueOf(poiCell.getErrorCellValue()); break; case Cell.CELL_TYPE_BLANK: default: // do nothing } if (val != null) { ((AbstractCellAdv) cell).setFormulaResultValue(val); } } break; case Cell.CELL_TYPE_ERROR: cell.setErrorValue(PoiEnumConversion.toErrorCode(poiCell.getErrorCellValue())); break; case Cell.CELL_TYPE_BLANK: // do nothing because spreadsheet model auto creates blank cells default: // TODO log: leave an unknown cell type as a blank cell. break; } Hyperlink poiHyperlink = poiCell.getHyperlink(); if (poiHyperlink != null) { String addr = poiHyperlink.getAddress(); String label = poiHyperlink.getLabel(); SHyperlink hyperlink = cell.setupHyperlink( PoiEnumConversion.toHyperlinkType(poiHyperlink.getType()), addr == null ? "" : addr, label == null ? "" : label); cell.setHyperlink(hyperlink); } Comment poiComment = poiCell.getCellComment(); if (poiComment != null) { SComment comment = cell.setupComment(); comment.setAuthor(poiComment.getAuthor()); comment.setVisible(poiComment.isVisible()); RichTextString poiRichTextString = poiComment.getString(); if (poiRichTextString != null && poiRichTextString.numFormattingRuns() > 0) { importRichText(poiCell, poiComment.getString(), comment.setupRichText()); } else { comment.setText(poiComment.toString()); } } return cell; }
private String getGroupParam(Row row) { Cell cell = row.getCell(1); return cell != null && cell.getCellType() == 1 ? cell.getStringCellValue().replace("_", "").toUpperCase() : ""; }