From 2ea123586aabe249b3a853ea2e7747b86cd28761 Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Thu, 19 Feb 2009 17:29:55 +0000 Subject: [PATCH] fixed autoSizeColumn() to use cached formula values when processing formula cells, see bug #46736 git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@745937 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/changes.xml | 1 + src/documentation/content/xdocs/status.xml | 1 + .../apache/poi/hssf/usermodel/HSSFSheet.java | 12 ++++++---- .../org/apache/poi/ss/usermodel/Cell.java | 8 +++++++ .../apache/poi/xssf/usermodel/XSSFCell.java | 23 ++++++++++++++++++- .../xssf/usermodel/helpers/ColumnHelper.java | 11 +++++---- 6 files changed, 47 insertions(+), 9 deletions(-) diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index c59adc2e72..9389059f55 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -37,6 +37,7 @@ + fixed Sheet.autoSizeColumn() to use cached formula values when processing formula cells Fixed formula parser to handle names with backslashes 46660 - added Workbook getHidden() and setHidden(boolean) 46693 - Fixed bugs serialization bugs in records: CHARTFORMAT, SHTPROPS, SXVD and SXVDEX diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 386c5f998f..989c45d384 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ + fixed Sheet.autoSizeColumn() to use cached formula values when processing formula cells Fixed formula parser to handle names with backslashes 46660 - added Workbook getHidden() and setHidden(boolean) 46693 - Fixed bugs serialization bugs in records: CHARTFORMAT, SHTPROPS, SXVD and SXVDEX diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java index 86646286f0..86336034df 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java @@ -1699,9 +1699,12 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { } HSSFCellStyle style = cell.getCellStyle(); + int cellType = cell.getCellType(); + if(cellType == HSSFCell.CELL_TYPE_FORMULA) cellType = cell.getCachedFormulaResultType(); + HSSFFont font = wb.getFontAt(style.getFontIndex()); - if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { + if (cellType == HSSFCell.CELL_TYPE_STRING) { HSSFRichTextString rt = cell.getRichStringCellValue(); String[] lines = rt.getString().split("\\n"); for (int i = 0; i < lines.length; i++) { @@ -1739,8 +1742,9 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { } } else { String sval = null; - if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { - String format = style.getDataFormatString().replaceAll("\"", ""); + if (cellType == HSSFCell.CELL_TYPE_NUMERIC) { + String dfmt = style.getDataFormatString(); + String format = dfmt == null ? null : dfmt.replaceAll("\"", ""); double value = cell.getNumericCellValue(); try { NumberFormat fmt; @@ -1754,7 +1758,7 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { } catch (Exception e) { sval = "" + value; } - } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { + } else if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) { sval = String.valueOf(cell.getBooleanCellValue()); } if(sval != null) { diff --git a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java index 8816025a4f..aa1e8c3955 100644 --- a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java +++ b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Cell.java @@ -131,6 +131,14 @@ public interface Cell { */ int getCellType(); + /** + * Only valid for formula cells + * @return one of ({@link #CELL_TYPE_NUMERIC}, {@link #CELL_TYPE_STRING}, + * {@link #CELL_TYPE_BOOLEAN}, {@link #CELL_TYPE_ERROR}) depending + * on the cached value of the formula + */ + int getCachedFormulaResultType(); + /** * Set a numeric value for the cell * diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java index 26fb2dc8a5..433b2c650c 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java @@ -466,7 +466,28 @@ public final class XSSFCell implements Cell { return CELL_TYPE_FORMULA; } - switch (this.cell.getT().intValue()) { + return getBaseCellType(); + } + + /** + * Only valid for formula cells + * @return one of ({@link #CELL_TYPE_NUMERIC}, {@link #CELL_TYPE_STRING}, + * {@link #CELL_TYPE_BOOLEAN}, {@link #CELL_TYPE_ERROR}) depending + * on the cached value of the formula + */ + public int getCachedFormulaResultType() { + if (cell.getF() == null) { + throw new IllegalStateException("Only formula cells have cached results"); + } + + return getBaseCellType(); + } + + /** + * Detect cell type based on the "t" attribute of the CTCell bean + */ + private int getBaseCellType() { + switch (cell.getT().intValue()) { case STCellType.INT_B: return CELL_TYPE_BOOLEAN; case STCellType.INT_N: diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/ColumnHelper.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/ColumnHelper.java index c796776f47..30da5efd42 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/ColumnHelper.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/ColumnHelper.java @@ -346,9 +346,11 @@ public class ColumnHelper { } XSSFCellStyle style = cell.getCellStyle(); + int cellType = cell.getCellType(); + if(cellType == XSSFCell.CELL_TYPE_FORMULA) cellType = cell.getCachedFormulaResultType(); XSSFFont font = wb.getFontAt(style.getFontIndex()); - if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { + if (cellType == XSSFCell.CELL_TYPE_STRING) { XSSFRichTextString rt = cell.getRichStringCellValue(); String[] lines = rt.getString().split("\\n"); for (int i = 0; i < lines.length; i++) { @@ -388,8 +390,9 @@ public class ColumnHelper { } } else { String sval = null; - if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { - String format = style.getDataFormatString().replaceAll("\"", ""); + if (cellType == XSSFCell.CELL_TYPE_NUMERIC) { + String dfmt = style.getDataFormatString(); + String format = dfmt == null ? null : dfmt.replaceAll("\"", ""); double value = cell.getNumericCellValue(); try { NumberFormat fmt; @@ -403,7 +406,7 @@ public class ColumnHelper { } catch (Exception e) { sval = "" + value; } - } else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) { + } else if (cellType == XSSFCell.CELL_TYPE_BOOLEAN) { sval = String.valueOf(cell.getBooleanCellValue()); } if(sval != null) { -- 2.39.5