public void baseBuiltinFormats() {
Workbook wb = getTestDataProvider().createWorkbook();
DataFormat df = wb.createDataFormat();
Map<Integer, String> formats = BuiltinFormats.getBuiltinFormats();
for (int idx : formats.keySet()) {
String fmt = formats.get(new Integer(idx));
assertEquals(idx, df.getFormat(fmt));
}
// default format for new cells is General
Sheet sheet = wb.createSheet();
Cell cell = sheet.createRow(0).createCell(0);
assertEquals(0, cell.getCellStyle().getDataFormat());
assertEquals("General", cell.getCellStyle().getDataFormatString());
// create a custom data format
String customFmt = "#0.00 AM/PM";
// check it is not in built-in formats
assertEquals(-1, BuiltinFormats.getBuiltinFormat(customFmt));
int customIdx = df.getFormat(customFmt);
// The first user-defined format starts at 164.
assertTrue(customIdx >= BuiltinFormats.FIRST_USER_DEFINED_FORMAT_INDEX);
// read and verify the string representation
assertEquals(customFmt, df.getFormat((short) customIdx));
}
protected org.zkoss.poi.ss.usermodel.Font getPoiFontFromRichText(
Workbook book, Cell cell, RichTextString rstr, int run) {
if (run < 0) return null; // ZSS-1138
org.zkoss.poi.ss.usermodel.Font font =
rstr instanceof HSSFRichTextString
? book.getFontAt(((HSSFRichTextString) rstr).getFontOfFormattingRun(run))
: ((XSSFRichTextString) rstr).getFontOfFormattingRun((XSSFWorkbook) book, run);
if (font == null) {
CellStyle style = cell.getCellStyle();
short fontIndex = style != null ? style.getFontIndex() : (short) 0;
return book.getFontAt(fontIndex);
}
return font;
}
/**
* 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;
}
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;
}
