public String readCGCell(Row row, int cellIdx, DataDto dto, CellType cellType) { Cell cell = row.getCell(cellIdx); if (null == cell) { log.warn( "Table import. Table " + metaData.getTableName() + " Cell at row: " + (row.getRowNum() + 1) + " col: " + (cellIdx + 1) + " current value: " + dto.getConfidenceGrade() + " cell is null."); return null; } if (cell.getCellType() != Cell.CELL_TYPE_STRING) { log.warn( "Table import. Table " + metaData.getTableName() + " Cell at row: " + (row.getRowNum() + 1) + " col: " + (cellIdx + 1) + " current value: " + dto.getConfidenceGrade() + " CG cell is not of type String."); return null; } RichTextString cg = cell.getRichStringCellValue(); return cg.getString(); }
public String readHeaderCell(Row row, int cellIdx) { Cell cell = row.getCell(cellIdx); if (null == cell) { return null; } if (cell.getCellType() == Cell.CELL_TYPE_STRING || cell.getCellType() == Cell.CELL_TYPE_FORMULA) { try { RichTextString value = cell.getRichStringCellValue(); return value.getString(); } catch (IllegalStateException e) { log.warn( "Table import. Table " + metaData.getTableName() + " Cell at row: " + (row.getRowNum() + 1) + " col: " + (cellIdx + 1) + " cell is not a string. CELL TYPE is " + cell.getCellType() + ". Cannot read cell."); metaData.addWarning( row.getRowNum(), cellIdx, " current value: " + " cell is not string. " + "CELL TYPE is " + cell.getCellType() + ". Cannot read cell."); } } log.warn( "Table import. Table " + metaData.getTableName() + " Header cell at row: " + (row.getRowNum() + 1) + " col: " + (cellIdx + 1) + " cell is is not a string. CELL TYPE is " + cell.getCellType()); return null; }
/** * 过滤 Cell 内容,返回字符串. * * @param c * @return */ private static String filter(Cell c) { int type = c.getCellType(); switch (type) { case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(c.getBooleanCellValue()); case Cell.CELL_TYPE_STRING: { RichTextString text = c.getRichStringCellValue(); return text.getString(); } case Cell.CELL_TYPE_FORMULA: { return c.getCellFormula(); } case Cell.CELL_TYPE_NUMERIC: { return String.valueOf(c.getNumericCellValue()); } } return "no-value"; }
/** * 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; }
public String readDataCell(Row row, int cellIdx, DataDto dto, CellType cellType) { Cell cell = row.getCell(cellIdx); if (null == cell) { log.warn( "Table import. Table " + metaData.getTableName() + " Cell at row: " + (row.getRowNum() + 1) + " col: " + (cellIdx + 1) + " current value: " + dto.getValue() + " cell is null."); return null; } String editedValueString = null; if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC || cell.getCellType() == Cell.CELL_TYPE_FORMULA) { try { double editedValue = cell.getNumericCellValue(); if ("0.00%".equals(cell.getCellStyle().getDataFormatString())) { // Percent editedValue = editedValue * 100; } editedValueString = formatter.formatValueToAtLeastDP(dto, editedValue); } catch (IllegalStateException e) { log.warn( "Table import. Table " + metaData.getTableName() + " Cell at row: " + (row.getRowNum() + 1) + " col: " + (cellIdx + 1) + " current value: " + dto.getValue() + " cell is not numeric. CELL TYPE is " + cell.getCellType() + ". Cannot read cell."); metaData.addWarning( row.getRowNum(), cellIdx, " current value: " + dto.getValue() + " cell is not numeric. CELL TYPE is " + cell.getCellType() + ". Cannot read cell."); } } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { RichTextString editedValue = cell.getRichStringCellValue(); if (editedValue.getString().isEmpty()) { editedValueString = ""; } else { if (isNumberInString(editedValue.getString())) { editedValueString = editedValue.getString(); } else { log.warn( "Table import. Table " + metaData.getTableName() + " Cell at row: " + (row.getRowNum() + 1) + " col: " + (cellIdx + 1) + " current value: " + dto.getValue() + " data '" + editedValue.getString() + "' is not numeric. CELL TYPE is " + cell.getCellType()); } } } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { editedValueString = ""; } else { log.warn( "Table import. Table " + metaData.getTableName() + " Cell at row: " + (row.getRowNum() + 1) + " col: " + (cellIdx + 1) + " current value: " + dto.getValue() + " cell is not numeric. CELL TYPE is " + cell.getCellType()); } return editedValueString; }