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 Object objectFrom(final HSSFWorkbook workbook, final Cell cell) { Object cellValue = null; if (cell.getCellType() == Cell.CELL_TYPE_STRING) { cellValue = cell.getRichStringCellValue().getString(); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { cellValue = getNumericCellValue(cell); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { cellValue = cell.getBooleanCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { cellValue = evaluateCellFormula(workbook, cell); } return cellValue; }
public void parseData( final Sheet sheet, final int startRow, final int endColumn, final List<String> propertyNames, final Class clazzOfTestCase) { int rowCounter = startRow; while (!isBlank(sheet, rowCounter, endColumn)) { Row row = sheet.getRow(rowCounter - 1); for (int i = 0; i < endColumn; i++) { Cell cell = row.getCell(i); if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { try { Method method = clazzOfTestCase.getMethod("set" + propertyNames.get(i), Object.class); } catch (NoSuchMethodException e) { e .printStackTrace(); // To change body of catch statement use File | Settings | File // Templates. } } } } }
public void testModifyArrayCells_mergeCells() { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); assertEquals(0, sheet.getNumMergedRegions()); // single-cell array formulas behave just like normal cells CellRange<? extends Cell> srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5")); Cell scell = srange.getTopLeftCell(); sheet.addMergedRegion(CellRangeAddress.valueOf("B5:C6")); // we are still an array formula assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType()); assertTrue(scell.isPartOfArrayFormulaGroup()); assertEquals(1, sheet.getNumMergedRegions()); // we cannot merge cells included in an array formula CellRange<? extends Cell> mrange = sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3")); CellRangeAddress cra = CellRangeAddress.valueOf("C1:C3"); try { sheet.addMergedRegion(cra); fail("expected exception"); } catch (IllegalStateException e) { String msg = "The range " + cra.formatAsString() + " intersects with a multi-cell array formula. You cannot merge cells of an array."; assertEquals(msg, e.getMessage()); } // the number of merged regions remains the same assertEquals(1, sheet.getNumMergedRegions()); }
/** Set multi-cell array formula */ public final void testSetArrayFormula_multiCell() { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); // multi-cell formula // rows 3-5 don't exist yet assertNull(sheet.getRow(3)); assertNull(sheet.getRow(4)); assertNull(sheet.getRow(5)); CellRangeAddress range = CellRangeAddress.valueOf("C4:C6"); Cell[] cells = sheet.setArrayFormula("SUM(A1:A3*B1:B3)", range).getFlattenedCells(); assertEquals(3, cells.length); // sheet.setArrayFormula creates rows and cells for the designated range assertSame(cells[0], sheet.getRow(3).getCell(2)); assertSame(cells[1], sheet.getRow(4).getCell(2)); assertSame(cells[2], sheet.getRow(5).getCell(2)); for (Cell acell : cells) { assertTrue(acell.isPartOfArrayFormulaGroup()); assertEquals(Cell.CELL_TYPE_FORMULA, acell.getCellType()); assertEquals("SUM(A1:A3*B1:B3)", acell.getCellFormula()); // retrieve the range and check it is the same assertEquals(range.formatAsString(), acell.getArrayFormulaRange().formatAsString()); } }
/** create and remove array formulas */ public final void testRemoveArrayFormula() { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); CellRangeAddress range = new CellRangeAddress(3, 5, 2, 2); assertEquals("C4:C6", range.formatAsString()); CellRange<?> cr = sheet.setArrayFormula("SUM(A1:A3*B1:B3)", range); assertEquals(3, cr.size()); // remove the formula cells in C4:C6 CellRange<?> dcells = sheet.removeArrayFormula(cr.getTopLeftCell()); // removeArrayFormula should return the same cells as setArrayFormula assertTrue(Arrays.equals(cr.getFlattenedCells(), dcells.getFlattenedCells())); for (Cell acell : cr) { assertFalse(acell.isPartOfArrayFormulaGroup()); assertEquals(Cell.CELL_TYPE_BLANK, acell.getCellType()); } // cells C4:C6 are not included in array formula, // invocation of sheet.removeArrayFormula on any of them throws IllegalArgumentException for (Cell acell : cr) { try { sheet.removeArrayFormula(acell); fail("expected exception"); } catch (IllegalArgumentException e) { String ref = new CellReference(acell).formatAsString(); assertEquals("Cell " + ref + " is not part of an array formula.", e.getMessage()); } } }
private static void verifyNumericCell(Cell cell, String cell_description) throws ParseException { verifyCellNotEmpty(cell, cell_description); if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) { throw new ParseException( cell_description + " in row " + cell.getRow().getRowNum() + " is not numeric type!", 0); } }
public void testModifyArrayCells_removeRow() { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); // single-cell array formulas behave just like normal cells CellRangeAddress cra = CellRangeAddress.valueOf("B5"); CellRange<? extends Cell> srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", cra); Cell scell = srange.getTopLeftCell(); assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType()); Row srow = scell.getRow(); assertSame(srow, sheet.getRow(cra.getFirstRow())); sheet.removeRow(srow); assertNull(sheet.getRow(cra.getFirstRow())); // re-create the removed row and cell scell = sheet.createRow(cra.getFirstRow()).createCell(cra.getFirstColumn()); assertEquals(Cell.CELL_TYPE_BLANK, scell.getCellType()); assertFalse(scell.isPartOfArrayFormulaGroup()); // we cannot remove rows with cells included in a multi-cell array formula CellRange<? extends Cell> mrange = sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3")); for (Cell mcell : mrange) { int columnIndex = mcell.getColumnIndex(); Row mrow = mcell.getRow(); try { sheet.removeRow(mrow); fail("expected exception"); } catch (IllegalStateException e) { String msg = "Row[rownum=" + mrow.getRowNum() + "] contains cell(s) included in a multi-cell array formula. You cannot change part of an array."; assertEquals(msg, e.getMessage()); } // a failed invocation of Row.removeCell leaves the row // in the state that it was in prior to the invocation assertSame(mrow, sheet.getRow(mrow.getRowNum())); assertSame(mcell, mrow.getCell(columnIndex)); assertTrue(mcell.isPartOfArrayFormulaGroup()); assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType()); } }
public void testModifyArrayCells_setCellFormula() { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); CellRange<? extends Cell> srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5")); Cell scell = srange.getTopLeftCell(); assertEquals("SUM(A4:A6,B4:B6)", scell.getCellFormula()); assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType()); assertTrue(scell.isPartOfArrayFormulaGroup()); scell.setCellFormula("SUM(A4,A6)"); // we are now a normal formula cell assertEquals("SUM(A4,A6)", scell.getCellFormula()); assertFalse(scell.isPartOfArrayFormulaGroup()); assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType()); // check that setting formula result works assertEquals(0.0, scell.getNumericCellValue()); scell.setCellValue(33.0); assertEquals(33.0, scell.getNumericCellValue()); // multi-cell array formula CellRange<? extends Cell> mrange = sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3")); for (Cell mcell : mrange) { // we cannot set individual formulas for cells included in an array formula try { assertEquals("A1:A3*B1:B3", mcell.getCellFormula()); mcell.setCellFormula("A1+A2"); 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.setCellFormula leaves the cell // in the state that it was in prior to the invocation assertEquals("A1:A3*B1:B3", mcell.getCellFormula()); assertTrue(mcell.isPartOfArrayFormulaGroup()); } }
private int firstEmptyCellPosition(final Row cells) { int columnCount = 0; for (Cell cell : cells) { if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { break; } columnCount++; } return columnCount; }
/** * Returns a 2D double array representing the excel data. * * <p>In the case of an error, null is returned. In this case, call {@link #getErrMsg()} to see * the error message. * * @return the excel file data. */ public List<Vector> getData() { if (sheet == null) { return null; } int size = sheet.getLastRowNum() + 1; List<List<Double>> cols = new ArrayList<>(numField); for (int i = 0; i < numField; i++) { cols.add(new ArrayList<Double>(size)); } for (int i = 0; i < size; i++) { Row r = sheet.getRow(i); if (r == null) { msg = String.format("Error: cannot read row %d", i + 1); return null; } for (int j = 0; j < numField; j++) { Cell c = r.getCell(j); if (c == null || (c.getCellType() != Cell.CELL_TYPE_NUMERIC && c.getCellType() != Cell.CELL_TYPE_FORMULA)) { msg = String.format("Error: cannot read row %d cell %d", i, j); if (j == 0) { return parse(cols); } return null; } try { cols.get(j).add(c.getNumericCellValue()); } catch (Exception ex) { msg = String.format( "Error: cannot read row %d cell %d\nMessage: %s", i, j, ex.getMessage()); return null; } } } return parse(cols); }
// TODO should we have this stuff in the FormulaEvaluator? private void evaluateWorkbook(Workbook workbook) { FormulaEvaluator eval = workbook.getCreationHelper().createFormulaEvaluator(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); for (Row r : sheet) { for (Cell c : r) { if (c.getCellType() == Cell.CELL_TYPE_FORMULA) { eval.evaluateFormulaCell(c); } } } } }
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()); } }
/** Test that we can set pre-calculated formula result for array formulas */ public void testModifyArrayCells_setFormulaResult() { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); // single-cell array formula 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.setCellValue(1.1); assertEquals(1.1, scell.getNumericCellValue()); // multi-cell array formula CellRange<? extends Cell> mrange = sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3")); for (Cell mcell : mrange) { assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType()); assertEquals(0.0, mcell.getNumericCellValue()); double fmlaResult = 1.2; mcell.setCellValue(fmlaResult); assertEquals(fmlaResult, mcell.getNumericCellValue()); } }
public boolean isBlank(final Sheet sheet, final int rowPos, final int endColumn) { Row row = sheet.getRow(rowPos - 1); if (row == null) { return true; } for (int i = 0; i < endColumn; i++) { Cell cell = row.getCell(i); if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { return false; } } return true; }
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; } } } }
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; }
/** * 获得指定位置的值,返回object,可为数值,字符串,布尔类型,null类型 * * @param column * @return */ public Object getCellValueObject(int rowNum, int column) { // 定义返回的数组 Object tempObject = null; row = sheet.getRow(rowNum); cell = row.getCell(column); // 判断值类型 switch (cell.getCellType()) { // 字符串类型 case Cell.CELL_TYPE_STRING: tempObject = cell.getRichStringCellValue().getString(); // System.out.println(cell.getRichStringCellValue().getString()); break; // 数值类型 case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { tempObject = cell.getDateCellValue(); // System.out.println(cell.getDateCellValue()); } else { tempObject = cell.getNumericCellValue(); // System.out.println(cell.getNumericCellValue()); } break; // 布尔类型 case Cell.CELL_TYPE_BOOLEAN: tempObject = cell.getBooleanCellValue(); // System.out.println(cell.getBooleanCellValue()); break; // 数学公式类型 case Cell.CELL_TYPE_FORMULA: tempObject = cell.getCellFormula(); // System.out.println(cell.getCellFormula()); break; default: System.out.println(); } return tempObject; }
private static void verifyCellNotEmpty(Cell cell, String cell_description) throws ParseException { if (cell.getCellType() == Cell.CELL_TYPE_BLANK) throw new ParseException( cell_description + " in row " + cell.getRow().getRowNum() + " is blank!", 0); }
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; }
private String getGroupParam(Row row) { Cell cell = row.getCell(1); return cell != null && cell.getCellType() == 1 ? cell.getStringCellValue().replace("_", "").toUpperCase() : ""; }
private String parse(final Cell cell) { if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) { return formatter.formatCellValue(cell); } return formatter.formatCellValue(cell, evaluator); }
/** * Compute width of a single cell * * @param cell the cell whose width is to be calculated * @param defaultCharWidth the width of a single character * @param formatter formatter used to prepare the text to be measured * @param useMergedCells whether to use merged cells * @return the width in pixels */ public static double getCellWidth( Cell cell, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells) { Sheet sheet = cell.getSheet(); Workbook wb = sheet.getWorkbook(); Row row = cell.getRow(); int column = cell.getColumnIndex(); int colspan = 1; for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress region = sheet.getMergedRegion(i); if (containsCell(region, row.getRowNum(), column)) { if (!useMergedCells) { // If we're not using merged cells, skip this one and move on to the next. return -1; } cell = row.getCell(region.getFirstColumn()); colspan = 1 + region.getLastColumn() - region.getFirstColumn(); } } CellStyle style = cell.getCellStyle(); int cellType = cell.getCellType(); // for formula cells we compute the cell width for the cached formula result if (cellType == Cell.CELL_TYPE_FORMULA) cellType = cell.getCachedFormulaResultType(); Font font = wb.getFontAt(style.getFontIndex()); AttributedString str; TextLayout layout; double width = -1; if (cellType == Cell.CELL_TYPE_STRING) { RichTextString rt = cell.getRichStringCellValue(); String[] lines = rt.getString().split("\\n"); for (int i = 0; i < lines.length; i++) { String txt = lines[i] + defaultChar; str = new AttributedString(txt); copyAttributes(font, str, 0, txt.length()); if (rt.numFormattingRuns() > 0) { // TODO: support rich text fragments } layout = new TextLayout(str.getIterator(), fontRenderContext); if (style.getRotation() != 0) { /* * Transform the text using a scale so that it's height is increased by a multiple of the leading, * and then rotate the text before computing the bounds. The scale results in some whitespace around * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but * is added by the standard Excel autosize. */ AffineTransform trans = new AffineTransform(); trans.concatenate( AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0)); trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple)); width = Math.max( width, ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention()); } else { width = Math.max( width, ((layout.getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention()); } } } else { String sval = null; if (cellType == Cell.CELL_TYPE_NUMERIC) { // Try to get it formatted to look the same as excel try { sval = formatter.formatCellValue(cell, dummyEvaluator); } catch (Exception e) { sval = String.valueOf(cell.getNumericCellValue()); } } else if (cellType == Cell.CELL_TYPE_BOOLEAN) { sval = String.valueOf(cell.getBooleanCellValue()).toUpperCase(); } if (sval != null) { String txt = sval + defaultChar; str = new AttributedString(txt); copyAttributes(font, str, 0, txt.length()); layout = new TextLayout(str.getIterator(), fontRenderContext); if (style.getRotation() != 0) { /* * Transform the text using a scale so that it's height is increased by a multiple of the leading, * and then rotate the text before computing the bounds. The scale results in some whitespace around * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but * is added by the standard Excel autosize. */ AffineTransform trans = new AffineTransform(); trans.concatenate( AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0)); trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple)); width = Math.max( width, ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention()); } else { width = Math.max( width, ((layout.getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention()); } } } return width; }
/** @see se.idega.idegaweb.commune.school.report.business.ReportModel#calculate() */ protected float calculate(Cell cell) throws RemoteException { float value = 0f; int schoolId = -1; if (cell.getRowParameter() != null) { schoolId = ((Integer) cell.getRowParameter()).intValue(); } String schoolYearName = (String) cell.getColumnParameter(); if (cell.getColumnMethod() == COLUMN_METHOD_SCHOOL_YEAR) { switch (cell.getRowMethod()) { case ROW_METHOD_SCHOOL: value = getPrivateSchoolOCCPlacementCount(schoolId, schoolYearName); break; case ROW_METHOD_SUM: int rowIndex = cell.getRow() - 1; if (rowIndex < 0) { break; } Cell c = getCell(rowIndex, cell.getColumn()); while (rowIndex >= 0 && c.getCellType() == Cell.CELLTYPE_NORMAL) { value += c.getFloatValue(); rowIndex--; if (rowIndex >= 0) { c = getCell(rowIndex, cell.getColumn()); } } break; case ROW_METHOD_TOTAL: rowIndex = cell.getRow() - 1; while (rowIndex >= 0) { c = getCell(rowIndex, cell.getColumn()); if (c.getCellType() == Cell.CELLTYPE_NORMAL) { value += c.getFloatValue(); } rowIndex--; } break; } } else { switch (cell.getColumnMethod()) { case COLUMN_METHOD_SUM_1_3: for (int i = 1; i < 4; i++) { value += getCell(cell.getRow(), i).getFloatValue(); } break; case COLUMN_METHOD_SUM_4_6: for (int i = 5; i < 8; i++) { value += getCell(cell.getRow(), i).getFloatValue(); } break; case COLUMN_METHOD_SUM_7_9: for (int i = 9; i < 12; i++) { value += getCell(cell.getRow(), i).getFloatValue(); } break; case COLUMN_METHOD_TOTAL_1_9: value = getCell(cell.getRow(), 4).getFloatValue() + getCell(cell.getRow(), 8).getFloatValue() + getCell(cell.getRow(), 12).getFloatValue(); break; case COLUMN_METHOD_TOTAL_F_9: value = getCell(cell.getRow(), 13).getFloatValue() + getCell(cell.getRow(), 0).getFloatValue(); break; } } return value; }
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 boolean isEmpty(final Row row) { Cell firstCell = row.getCell(0); boolean rowIsEmpty = (firstCell == null) || (firstCell.getCellType() == Cell.CELL_TYPE_BLANK); return rowIsEmpty; }