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;
}
