From 3bd494cc622425844dfce0105017b5e415d0152a Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Sun, 1 Mar 2009 16:24:40 +0000 Subject: [PATCH] support built-in data formats in XSSFDataFormat git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@749054 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/changes.xml | 1 + src/documentation/content/xdocs/status.xml | 1 + .../poi/hssf/usermodel/HSSFDataFormat.java | 124 +--- .../poi/ss/usermodel/BuiltinFormats.java | 133 +++++ .../apache/poi/xssf/model/StylesTable.java | 3 +- .../poi/xssf/usermodel/XSSFCellStyle.java | 3 +- .../poi/xssf/usermodel/XSSFDataFormat.java | 24 +- .../xssf/usermodel/AllXSSFUsermodelTests.java | 2 +- .../poi/xssf/usermodel/TestXSSFCell.java | 546 ------------------ .../xssf/usermodel/TestXSSFDataFormat.java | 53 ++ .../hssf/usermodel/TestHSSFDataFormat.java | 40 ++ .../poi/ss/usermodel/BaseTestDataFormat.java | 63 ++ 12 files changed, 342 insertions(+), 651 deletions(-) create mode 100755 src/java/org/apache/poi/ss/usermodel/BuiltinFormats.java delete mode 100644 src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java create mode 100644 src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFDataFormat.java create mode 100755 src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormat.java create mode 100755 src/testcases/org/apache/poi/ss/usermodel/BaseTestDataFormat.java diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index ac90a3b436..395df7134f 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -37,6 +37,7 @@ + 46772 - support built-in data formats in XSSFDataFormat 46719 - fixed XSSFSheet.shiftRows to correctly preserve row heights 46715 - preserve custom column widths across re-serialization of XSSFWorkbook 46703 - added setDisplayZeros / isDisplayZeros to common interface org.apache.poi.ss.usermodel.Sheet diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 4a5dd69249..b22fd9d385 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ + 46772 - support built-in data formats in XSSFDataFormat 46719 - fixed XSSFSheet.shiftRows to correctly preserve row heights 46715 - preserve custom column widths across re-serialization of XSSFWorkbook 46703 - added setDisplayZeros / isDisplayZeros to common interface org.apache.poi.ss.usermodel.Sheet diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFDataFormat.java b/src/java/org/apache/poi/hssf/usermodel/HSSFDataFormat.java index fb7ac49f39..dd66438a28 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFDataFormat.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFDataFormat.java @@ -23,14 +23,12 @@ */ package org.apache.poi.hssf.usermodel; -import java.util.Iterator; -import java.util.List; -import java.util.ListIterator; -import java.util.Vector; +import java.util.*; import org.apache.poi.hssf.model.Workbook; import org.apache.poi.hssf.record.FormatRecord; import org.apache.poi.ss.usermodel.DataFormat; +import org.apache.poi.ss.usermodel.BuiltinFormats; /** * Utility to identify builtin formats. Now can handle user defined data formats also. The following is a list of the formats as @@ -115,61 +113,10 @@ public class HSSFDataFormat implements DataFormat private static synchronized List createBuiltinFormats() { List builtinFormats = new Vector(); - builtinFormats.add( 0, "General" ); - builtinFormats.add( 1, "0" ); - builtinFormats.add( 2, "0.00" ); - builtinFormats.add( 3, "#,##0" ); - builtinFormats.add( 4, "#,##0.00" ); - builtinFormats.add( 5, "($#,##0_);($#,##0)" ); - builtinFormats.add( 6, "($#,##0_);[Red]($#,##0)" ); - builtinFormats.add( 7, "($#,##0.00);($#,##0.00)" ); - builtinFormats.add( 8, "($#,##0.00_);[Red]($#,##0.00)" ); - builtinFormats.add( 9, "0%" ); - builtinFormats.add( 0xa, "0.00%" ); - builtinFormats.add( 0xb, "0.00E+00" ); - builtinFormats.add( 0xc, "# ?/?" ); - builtinFormats.add( 0xd, "# ??/??" ); - builtinFormats.add( 0xe, "m/d/yy" ); - builtinFormats.add( 0xf, "d-mmm-yy" ); - builtinFormats.add( 0x10, "d-mmm" ); - builtinFormats.add( 0x11, "mmm-yy" ); - builtinFormats.add( 0x12, "h:mm AM/PM" ); - builtinFormats.add( 0x13, "h:mm:ss AM/PM" ); - builtinFormats.add( 0x14, "h:mm" ); - builtinFormats.add( 0x15, "h:mm:ss" ); - builtinFormats.add( 0x16, "m/d/yy h:mm" ); - - // 0x17 - 0x24 reserved for international and undocumented - builtinFormats.add( 0x17, "0x17" ); - builtinFormats.add( 0x18, "0x18" ); - builtinFormats.add( 0x19, "0x19" ); - builtinFormats.add( 0x1a, "0x1a" ); - builtinFormats.add( 0x1b, "0x1b" ); - builtinFormats.add( 0x1c, "0x1c" ); - builtinFormats.add( 0x1d, "0x1d" ); - builtinFormats.add( 0x1e, "0x1e" ); - builtinFormats.add( 0x1f, "0x1f" ); - builtinFormats.add( 0x20, "0x20" ); - builtinFormats.add( 0x21, "0x21" ); - builtinFormats.add( 0x22, "0x22" ); - builtinFormats.add( 0x23, "0x23" ); - builtinFormats.add( 0x24, "0x24" ); - - // 0x17 - 0x24 reserved for international and undocumented - builtinFormats.add( 0x25, "(#,##0_);(#,##0)" ); - builtinFormats.add( 0x26, "(#,##0_);[Red](#,##0)" ); - builtinFormats.add( 0x27, "(#,##0.00_);(#,##0.00)" ); - builtinFormats.add( 0x28, "(#,##0.00_);[Red](#,##0.00)" ); - builtinFormats.add( 0x29, "_(*#,##0_);_(*(#,##0);_(* \"-\"_);_(@_)" ); - builtinFormats.add( 0x2a, "_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)" ); - builtinFormats.add( 0x2b, "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)" ); - builtinFormats.add( 0x2c, - "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)" ); - builtinFormats.add( 0x2d, "mm:ss" ); - builtinFormats.add( 0x2e, "[h]:mm:ss" ); - builtinFormats.add( 0x2f, "mm:ss.0" ); - builtinFormats.add( 0x30, "##0.0E+0" ); - builtinFormats.add( 0x31, "@" ); + Map formats = BuiltinFormats.getBuiltinFormats(); + for(int key : formats.keySet()) { + builtinFormats.add(key, formats.get(key)); + } return builtinFormats; } @@ -187,22 +134,7 @@ public class HSSFDataFormat implements DataFormat public static short getBuiltinFormat( String format ) { - if (format.toUpperCase().equals("TEXT")) - format = "@"; - - short retval = -1; - - for (short k = 0; k <= 0x31; k++) - { - String nformat = (String) builtinFormats.get( k ); - - if ( ( nformat != null ) && nformat.equals( format ) ) - { - retval = k; - break; - } - } - return retval; + return (short)BuiltinFormats.getBuiltinFormat(format); } /** @@ -212,41 +144,36 @@ public class HSSFDataFormat implements DataFormat * @param format string matching a built in format * @return index of format. */ - public short getFormat( String format ) - { + public short getFormat(String format) { ListIterator i; int ind; - if (format.toUpperCase().equals("TEXT")) - format = "@"; + if (format.toUpperCase().equals("TEXT")) + format = "@"; - if ( !movedBuiltins ) - { + if (!movedBuiltins) { i = builtinFormats.listIterator(); - while ( i.hasNext() ) - { + while (i.hasNext()) { ind = i.nextIndex(); - if ( formats.size() < ind + 1 ) - { - formats.setSize( ind + 1 ); - } - - formats.set( ind, i.next() ); + if (formats.size() < ind + 1) { + formats.setSize(ind + 1); + } + + formats.set(ind, i.next()); } movedBuiltins = true; } i = formats.listIterator(); - while ( i.hasNext() ) - { + while (i.hasNext()) { ind = i.nextIndex(); - if ( format.equals( i.next() ) ) + if (format.equals(i.next())) return (short) ind; } - ind = workbook.getFormat( format, true ); - if ( formats.size() <= ind ) - formats.setSize( ind + 1 ); - formats.set( ind, format ); + ind = workbook.getFormat(format, true); + if (formats.size() <= ind) + formats.setSize(ind + 1); + formats.set(ind, format); return (short) ind; } @@ -271,12 +198,11 @@ public class HSSFDataFormat implements DataFormat * get the format string that matches the given format index * @param index of a built in format * @return string represented at index of format or null if there is not a builtin format at that index - * @throws ArrayOutOfBoundsException when the index exceeds the number of builtin formats. */ public static String getBuiltinFormat( short index ) { - return (String) builtinFormats.get( index ); + return BuiltinFormats.getBuiltinFormat(index); } /** @@ -286,6 +212,6 @@ public class HSSFDataFormat implements DataFormat public static int getNumberOfBuiltinBuiltinFormats() { - return builtinFormats.size(); + return BuiltinFormats.getBuiltinFormats().size(); } } diff --git a/src/java/org/apache/poi/ss/usermodel/BuiltinFormats.java b/src/java/org/apache/poi/ss/usermodel/BuiltinFormats.java new file mode 100755 index 0000000000..f07fd96b6a --- /dev/null +++ b/src/java/org/apache/poi/ss/usermodel/BuiltinFormats.java @@ -0,0 +1,133 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ +package org.apache.poi.ss.usermodel; + +import java.util.Map; +import java.util.LinkedHashMap; + +/** + * Utility to identify builtin formats + * + * @author Yegor Kozlov + */ +public class BuiltinFormats { + /** + * The first user-defined format starts at 164. + */ + public static final int FIRST_USER_DEFINED_FORMAT_INDEX = 164; + + private final static Map formats; + + static { + formats = new LinkedHashMap(); + formats.put( 0, "General" ); + formats.put( 1, "0" ); + formats.put( 2, "0.00" ); + formats.put( 3, "#,##0" ); + formats.put( 4, "#,##0.00" ); + formats.put( 5, "($#,##0_);($#,##0)" ); + formats.put( 6, "($#,##0_);[Red]($#,##0)" ); + formats.put( 7, "($#,##0.00);($#,##0.00)" ); + formats.put( 8, "($#,##0.00_);[Red]($#,##0.00)" ); + formats.put( 9, "0%" ); + formats.put( 0xa, "0.00%" ); + formats.put( 0xb, "0.00E+00" ); + formats.put( 0xc, "# ?/?" ); + formats.put( 0xd, "# ??/??" ); + formats.put( 0xe, "m/d/yy" ); + formats.put( 0xf, "d-mmm-yy" ); + formats.put( 0x10, "d-mmm" ); + formats.put( 0x11, "mmm-yy" ); + formats.put( 0x12, "h:mm AM/PM" ); + formats.put( 0x13, "h:mm:ss AM/PM" ); + formats.put( 0x14, "h:mm" ); + formats.put( 0x15, "h:mm:ss" ); + formats.put( 0x16, "m/d/yy h:mm" ); + + // 0x17 - 0x24 reserved for international and undocumented + formats.put( 0x17, "0x17" ); + formats.put( 0x18, "0x18" ); + formats.put( 0x19, "0x19" ); + formats.put( 0x1a, "0x1a" ); + formats.put( 0x1b, "0x1b" ); + formats.put( 0x1c, "0x1c" ); + formats.put( 0x1d, "0x1d" ); + formats.put( 0x1e, "0x1e" ); + formats.put( 0x1f, "0x1f" ); + formats.put( 0x20, "0x20" ); + formats.put( 0x21, "0x21" ); + formats.put( 0x22, "0x22" ); + formats.put( 0x23, "0x23" ); + formats.put( 0x24, "0x24" ); + + // 0x17 - 0x24 reserved for international and undocumented + formats.put( 0x25, "(#,##0_);(#,##0)" ); + formats.put( 0x26, "(#,##0_);[Red](#,##0)" ); + formats.put( 0x27, "(#,##0.00_);(#,##0.00)" ); + formats.put( 0x28, "(#,##0.00_);[Red](#,##0.00)" ); + formats.put( 0x29, "_(*#,##0_);_(*(#,##0);_(* \"-\"_);_(@_)" ); + formats.put( 0x2a, "_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)" ); + formats.put( 0x2b, "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)" ); + formats.put( 0x2c, + "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)" ); + formats.put( 0x2d, "mm:ss" ); + formats.put( 0x2e, "[h]:mm:ss" ); + formats.put( 0x2f, "mm:ss.0" ); + formats.put( 0x30, "##0.0E+0" ); + formats.put( 0x31, "@" ); + } + + /** + * Get the index-formatString map with built-in data formats + * + * @return built-in data formats + */ + public static Map getBuiltinFormats(){ + return formats; + } + + /** + * Get the format string that matches the given format index + * + * @param index of a built in format + * @return string represented at index of format or null if there is not a builtin format at that index + */ + public static String getBuiltinFormat(int index){ + return formats.get(index); + } + + /** + * Get the format index that matches the given format string + *

+ * Automatically converts "text" to excel's format string to represent text. + *

+ * @param fmt string matching a built-in format + * @return index of format or -1 if undefined. + */ + public static int getBuiltinFormat(String fmt){ + int idx = -1; + if (fmt.toUpperCase().equals("TEXT")) fmt = "@"; + + for(int key : formats.keySet()) { + if(fmt.equals(formats.get(key))) { + idx = key; + break; + } + } + return idx; + } +} diff --git a/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java b/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java index 8355f4f9ba..33da9e4ea6 100644 --- a/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java +++ b/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java @@ -25,6 +25,7 @@ import java.util.Map.Entry; import org.apache.poi.ss.usermodel.FontFamily; import org.apache.poi.ss.usermodel.FontScheme; +import org.apache.poi.ss.usermodel.BuiltinFormats; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder; @@ -70,7 +71,7 @@ public class StylesTable extends POIXMLDocumentPart { /** * The first style id available for use as a custom style */ - public static final int FIRST_CUSTOM_STYLE_ID = 165; + public static final int FIRST_CUSTOM_STYLE_ID = BuiltinFormats.FIRST_USER_DEFINED_FORMAT_INDEX + 1; private StyleSheetDocument doc; diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java index 8a6ca929c7..fcc87f8cb7 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java @@ -342,7 +342,8 @@ public class XSSFCellStyle implements CellStyle { * @return the number format string */ public String getDataFormatString() { - return stylesSource.getNumberFormatAt(getDataFormat()); + int idx = getDataFormat(); + return new XSSFDataFormat(stylesSource).getFormat((short)idx); } /** diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataFormat.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataFormat.java index ec11cf436b..cb9b5b396e 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataFormat.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataFormat.java @@ -17,11 +17,12 @@ package org.apache.poi.xssf.usermodel; import org.apache.poi.ss.usermodel.DataFormat; +import org.apache.poi.ss.usermodel.BuiltinFormats; import org.apache.poi.xssf.model.StylesTable; /** * Handles data formats for XSSF. - * TODO Figure out if there are build in formats too + * */ public class XSSFDataFormat implements DataFormat { private StylesTable stylesSource; @@ -30,11 +31,28 @@ public class XSSFDataFormat implements DataFormat { this.stylesSource = stylesSource; } + /** + * Get the format index that matches the given format + * string, creating a new format entry if required. + * Aliases text to the proper format as required. + * + * @param format string matching a built in format + * @return index of format. + */ public short getFormat(String format) { - return (short)stylesSource.putNumberFormat(format); + int idx = BuiltinFormats.getBuiltinFormat(format); + if(idx == -1) idx = stylesSource.putNumberFormat(format); + return (short)idx; } + /** + * get the format string that matches the given format index + * @param index of a format + * @return string represented at index of format or null if there is not a format at that index + */ public String getFormat(short index) { - return stylesSource.getNumberFormatAt(index); + String fmt = BuiltinFormats.getBuiltinFormat(index); + if(fmt == null) fmt = stylesSource.getNumberFormatAt(index); + return fmt; } } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/AllXSSFUsermodelTests.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/AllXSSFUsermodelTests.java index 39d621865e..1f5b31a486 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/AllXSSFUsermodelTests.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/AllXSSFUsermodelTests.java @@ -38,7 +38,7 @@ public final class AllXSSFUsermodelTests { result.addTestSuite(TestFormulaEvaluatorOnXSSF.class); result.addTestSuite(TestSheetHiding.class); result.addTestSuite(TestXSSFBugs.class); - result.addTestSuite(TestXSSFCell.class); + result.addTestSuite(TestXSSFDataFormat.class); result.addTestSuite(TestXSSFCellStyle.class); result.addTestSuite(TestXSSFComment.class); result.addTestSuite(TestXSSFDialogSheet.class); diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java deleted file mode 100644 index 54f23f8d4b..0000000000 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java +++ /dev/null @@ -1,546 +0,0 @@ -/* ==================================================================== - Licensed to the Apache Software Foundation (ASF) under one or more - contributor license agreements. See the NOTICE file distributed with - this work for additional information regarding copyright ownership. - The ASF licenses this file to You under the Apache License, Version 2.0 - (the "License"); you may not use this file except in compliance with - the License. You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. -==================================================================== */ - -package org.apache.poi.xssf.usermodel; - -import java.util.Calendar; -import java.util.Date; - -import junit.framework.TestCase; - -import org.apache.poi.hssf.usermodel.HSSFCell; -import org.apache.poi.hssf.usermodel.HSSFRichTextString; -import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.ss.usermodel.*; -import org.apache.poi.xssf.XSSFTestDataSamples; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellType; - -/** - * Tests for {@link XSSFCell} - * - */ -public final class TestXSSFCell extends TestCase { - - private static final String TEST_C10_AUTHOR = "test C10 author"; - - /** - * Test setting and getting boolean values. - */ - public void testSetGetBoolean() throws Exception { - XSSFRow row = createParentObjects(); - XSSFCell cell = row.createCell(0); - //for blank cells getBooleanCellValue returns false - assertFalse(cell.getBooleanCellValue()); - - cell.setCellValue(true); - assertEquals(Cell.CELL_TYPE_BOOLEAN, cell.getCellType()); - assertTrue(cell.getBooleanCellValue()); - cell.setCellValue(false); - assertFalse(cell.getBooleanCellValue()); - - cell.setCellType(Cell.CELL_TYPE_NUMERIC); - try { - cell.getBooleanCellValue(); - fail("Exception expected"); - } catch (IllegalStateException e) { - // success - assertEquals("Cannot get a boolean value from a numeric cell", e.getMessage()); - } - - cell.setCellValue("1"); - assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType()); - try { - cell.getBooleanCellValue(); - fail("Exception expected"); - } catch (IllegalStateException e) { - // success - assertEquals("Cannot get a boolean value from a text cell", e.getMessage()); - } - - //reverted to a blank cell - cell.setCellType(Cell.CELL_TYPE_BLANK); - assertFalse(cell.getBooleanCellValue()); - - - } - - /** - * Test setting and getting numeric values. - */ - public void testSetGetNumeric() throws Exception { - XSSFRow row = createParentObjects(); - XSSFCell cell = row.createCell(0); - assertEquals(0.0, cell.getNumericCellValue()); - - cell.setCellValue(10.0); - assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCellType()); - assertEquals(10.0, cell.getNumericCellValue()); - cell.setCellValue(-23.76); - assertEquals(-23.76, cell.getNumericCellValue()); - - cell.setCellValue("string"); - try { - cell.getNumericCellValue(); - fail("Exception expected"); - } catch (IllegalStateException e) { - // success - assertEquals("Cannot get a numeric value from a text cell", e.getMessage()); - } - - cell.setCellValue(true); - try { - cell.getNumericCellValue(); - fail("Exception expected"); - } catch (IllegalStateException e) { - // success - assertEquals("Cannot get a numeric value from a boolean cell", e.getMessage()); - } - - //reverted to a blank cell - cell.setCellType(Cell.CELL_TYPE_BLANK); - assertEquals(0.0, cell.getNumericCellValue()); - - //setting numeric value for a formula cell does not change the cell type - XSSFCell fcell = row.createCell(1); - fcell.setCellFormula("SUM(C4:E4)"); - assertEquals(Cell.CELL_TYPE_FORMULA, fcell.getCellType()); - fcell.setCellValue(36.6); - assertEquals(Cell.CELL_TYPE_FORMULA, fcell.getCellType()); - assertEquals(36.6, fcell.getNumericCellValue()); - - //the said above is true for error cells - fcell.setCellType(Cell.CELL_TYPE_ERROR); - assertEquals(36.6, fcell.getNumericCellValue()); - fcell.setCellValue(16.6); - assertEquals(Cell.CELL_TYPE_FORMULA, fcell.getCellType()); - assertEquals(16.6, fcell.getNumericCellValue()); - } - - /** - * Test setting and getting date values. - */ - public void testSetGetDate() throws Exception { - XSSFRow row = createParentObjects(); - XSSFCell cell = row.createCell(0); - Date now = new Date(); - cell.setCellValue(now); - assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCellType()); - assertEquals(now, cell.getDateCellValue()); - - // Test case for 1904 hack - Calendar cal = Calendar.getInstance(); - cal.set(1903, 1, 8); - Date before1904 = cal.getTime(); - cell.setCellValue(before1904); - assertEquals(before1904, cell.getDateCellValue()); - - cell.setCellType(Cell.CELL_TYPE_BOOLEAN); - try { - cell.getDateCellValue(); - fail("Exception expected"); - } catch (IllegalStateException e) { - // success - assertEquals("Cannot get a numeric value from a boolean cell", e.getMessage()); - } - - cell.setCellValue(cal); - assertEquals(before1904,cell.getDateCellValue()); - - } - - /** - * Test setting and getting date values. - */ - public void testSetGetType() throws Exception { - XSSFRow row = createParentObjects(); - XSSFCell cell = row.createCell(0); - cell.setCellType(Cell.CELL_TYPE_BLANK); - assertEquals(Cell.CELL_TYPE_BLANK, cell.getCellType()); - cell.setCellType(Cell.CELL_TYPE_STRING); - assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType()); - cell.setCellType(Cell.CELL_TYPE_FORMULA); - assertEquals(Cell.CELL_TYPE_FORMULA, cell.getCellType()); - cell.setCellFormula(null); - - //number cell w/o value is treated as a Blank cell - cell.setCellType(Cell.CELL_TYPE_NUMERIC); - assertFalse(cell.getCTCell().isSetV()); - assertEquals(Cell.CELL_TYPE_BLANK, cell.getCellType()); - - //normal number cells have set values - cell.setCellType(Cell.CELL_TYPE_NUMERIC); - cell.getCTCell().setV("0"); - assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCellType()); - - cell.setCellType(Cell.CELL_TYPE_BOOLEAN); - assertEquals(Cell.CELL_TYPE_BOOLEAN, cell.getCellType()); - } - - public void testSetGetError() throws Exception { - XSSFRow row = createParentObjects(); - XSSFCell cell = row.createCell(0); - - cell.setCellErrorValue((byte)0); - assertEquals(Cell.CELL_TYPE_ERROR, cell.getCellType()); - assertEquals((byte)0, cell.getErrorCellValue()); - - //YK setting numeric value of a error cell does not change the cell type - cell.setCellValue(2.2); - assertEquals(Cell.CELL_TYPE_ERROR, cell.getCellType()); - - cell.setCellErrorValue(FormulaError.NAME); - assertEquals(Cell.CELL_TYPE_ERROR, cell.getCellType()); - assertEquals(FormulaError.NAME.getCode(), cell.getErrorCellValue()); - assertEquals(FormulaError.NAME.getString(), cell.getErrorCellString()); - } - - public void testSetGetFormula() throws Exception { - XSSFRow row = createParentObjects(); - XSSFCell cell = row.createCell(0); - String formula = "SQRT(C2^2+D2^2)"; - - cell.setCellFormula(formula); - assertEquals(Cell.CELL_TYPE_FORMULA, cell.getCellType()); - assertEquals(formula, cell.getCellFormula()); - - assertEquals(0.0, cell.getNumericCellValue()); - - cell.setCellValue(44.5); //set precalculated value - assertEquals(Cell.CELL_TYPE_FORMULA, cell.getCellType()); - assertEquals(44.5, cell.getNumericCellValue()); - - cell.setCellValue(""); //set precalculated value - } - - public void testSetGetStringInline() throws Exception { - CTCell rawCell = CTCell.Factory.newInstance(); - XSSFRow row = createParentObjects(); - XSSFCell cell = new XSSFCell(row, rawCell); - - // Default is shared string mode, so have to do this explicitly - rawCell.setT(STCellType.INLINE_STR); - - assertEquals(STCellType.INT_INLINE_STR, rawCell.getT().intValue()); - assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType()); - assertEquals("", cell.getRichStringCellValue().getString()); - - cell.setCellValue(new XSSFRichTextString("Foo")); - assertEquals(STCellType.INT_INLINE_STR, rawCell.getT().intValue()); - assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType()); - assertEquals("Foo", cell.getRichStringCellValue().getString()); - - // To number and back to string, stops being inline - cell.setCellValue(1.4); - cell.setCellValue(new XSSFRichTextString("Foo2")); - assertEquals(STCellType.INT_S, rawCell.getT().intValue()); - assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType()); - assertEquals("Foo2", cell.getRichStringCellValue().getString()); - } - - public void testSetGetStringShared() { - XSSFRow row = createParentObjects(); - XSSFCell cell = row.createCell(0); - //we return empty string for blank cells - assertEquals("", cell.getStringCellValue()); - - cell.setCellValue(new XSSFRichTextString("test")); - assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType()); - assertEquals("test", cell.getRichStringCellValue().getString()); - - cell.setCellValue(new XSSFRichTextString("Foo")); - assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType()); - assertEquals("Foo", cell.getRichStringCellValue().getString()); - - cell.setCellValue((String)null); - assertEquals(Cell.CELL_TYPE_BLANK, cell.getCellType()); - - XSSFCell fcell = row.createCell(1); - fcell.setCellFormula("SUM(C4:E4)"); - assertEquals(Cell.CELL_TYPE_FORMULA, fcell.getCellType()); - fcell.setCellValue("36.6"); - assertEquals(Cell.CELL_TYPE_FORMULA, fcell.getCellType()); - assertEquals("36.6", fcell.getStringCellValue()); - - } - - /** - * Test that empty cells (no v element) return default values. - */ - public void testGetEmptyCellValue() { - XSSFRow row = createParentObjects(); - XSSFCell cell = row.createCell(0); - cell.setCellType(Cell.CELL_TYPE_BOOLEAN); - assertFalse(cell.getBooleanCellValue()); - cell.setCellType(Cell.CELL_TYPE_NUMERIC); - assertEquals(0.0, cell.getNumericCellValue() ); - assertNull(cell.getDateCellValue()); - cell.setCellType(Cell.CELL_TYPE_ERROR); - assertEquals(0, cell.getErrorCellValue()); - cell.setCellType(Cell.CELL_TYPE_STRING); - assertEquals("", cell.getRichStringCellValue().getString()); - } - - public void testParseCellNum() { - assertEquals(0, XSSFCell.parseCellNum("A1")); - assertEquals(1, XSSFCell.parseCellNum("B1")); - assertEquals(1, XSSFCell.parseCellNum("B2")); - assertEquals(26, XSSFCell.parseCellNum("AA1")); - assertEquals(255, XSSFCell.parseCellNum("IV1")); - assertEquals(255, XSSFCell.parseCellNum("IV32768")); - } - - public void testSetCellReference() { - XSSFWorkbook wb = new XSSFWorkbook(); - XSSFSheet sheet = wb.createSheet(); - XSSFRow row = sheet.createRow(0); - XSSFCell cell = row.createCell(0); - assertEquals("A1", cell.getCTCell().getR()); - - row = sheet.createRow(100); - cell = row.createCell(100); - assertEquals("CW101", cell.getCTCell().getR()); - - row = sheet.createRow(XSSFRow.MAX_ROW_NUMBER-1); - cell = row.createCell(100); - assertEquals("CW1048576", cell.getCTCell().getR()); - - row = sheet.createRow(XSSFRow.MAX_ROW_NUMBER-1); - cell = row.createCell(XSSFCell.MAX_COLUMN_NUMBER-1); - assertEquals("XFD1048576", cell.getCTCell().getR()); - - try { - sheet.createRow(XSSFRow.MAX_ROW_NUMBER); - fail("expecting exception when rownum >= XSSFRow.MAX_ROW_NUMBER"); - } catch(IllegalArgumentException e){ - ; - } - - row = sheet.createRow(100); - try { - row.createCell(XSSFCell.MAX_COLUMN_NUMBER); - fail("expecting exception when columnIndex >= XSSFCell.MAX_COLUMN_NUMBER"); - } catch(IllegalArgumentException e){ - ; - } - } - - public void testGetCellComment() { - XSSFWorkbook workbook = new XSSFWorkbook(); - XSSFSheet sheet = workbook.createSheet(); - assertNotNull(sheet); - - XSSFComment comment = sheet.createComment(); - comment.setAuthor(TEST_C10_AUTHOR); - sheet.setCellComment("C10", comment); - - // Create C10 cell - Row row = sheet.createRow(9); - row.createCell(2); - row.createCell(3); - - assertNotNull(sheet.getRow(9).getCell((short)2)); - assertNotNull(sheet.getRow(9).getCell((short)2).getCellComment()); - assertEquals(TEST_C10_AUTHOR, sheet.getRow(9).getCell((short)2).getCellComment().getAuthor()); - assertNull(sheet.getRow(9).getCell((short)3).getCellComment()); - } - - public void testSetCellComment() { - XSSFWorkbook workbook = new XSSFWorkbook(); - XSSFSheet sheet = workbook.createSheet(); - assertNotNull(sheet); - - XSSFComment comment = sheet.createComment(); - comment.setAuthor(TEST_C10_AUTHOR); - - // Create C10 cell - XSSFRow row = sheet.createRow(9); - XSSFCell cell = row.createCell(2); - row.createCell(3); - - // Set a comment for C10 cell - cell.setCellComment(comment); - - CTCell ctCell = cell.getCTCell(); - assertNotNull(ctCell); - assertEquals("C10", ctCell.getR()); - assertEquals(TEST_C10_AUTHOR, comment.getAuthor()); - } - - public void testSetAsActiveCell() { - XSSFWorkbook workbook = new XSSFWorkbook(); - XSSFSheet sheet = workbook.createSheet(); - Cell cell = sheet.createRow(0).createCell((short)0); - cell.setAsActiveCell(); - - assertEquals("A1", sheet.getCTWorksheet().getSheetViews().getSheetViewArray(0).getSelectionArray(0).getActiveCell()); - } - - - /** - * Tests that cell formatting stuff works as expected - */ - public void testCellFormatting() { - Workbook workbook = new XSSFWorkbook(); - Sheet sheet = workbook.createSheet(); - CreationHelper creationHelper = workbook.getCreationHelper(); - - CellStyle cs = workbook.createCellStyle(); - assertNotNull(cs); - assertTrue(cs.getIndex() > 0); - - assertNotNull(creationHelper); - assertNotNull(creationHelper.createDataFormat()); - - cs.setDataFormat( - creationHelper.createDataFormat().getFormat("yyyy/mm/dd") - ); - Cell cell = sheet.createRow(0).createCell((short)0); - assertNotNull(cell.getCellStyle()); - assertEquals(0, cell.getCellStyle().getIndex()); - cell.setCellValue(new Date(654321)); - - cell.setCellStyle(cs); - - assertEquals(new Date(654321), cell.getDateCellValue()); - assertNotNull(cell.getCellStyle()); - assertEquals("yyyy/mm/dd", cell.getCellStyle().getDataFormatString()); - - - // Save, re-load, and test again - Workbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(workbook); - Cell c2 = wb2.getSheetAt(0).getRow(0).getCell(0); - assertEquals(new Date(654321), c2.getDateCellValue()); - assertEquals("yyyy/mm/dd", c2.getCellStyle().getDataFormatString()); - } - - private static XSSFRow createParentObjects() { - XSSFWorkbook wb = new XSSFWorkbook(); - XSSFSheet sheet = wb.createSheet(); - XSSFRow row = sheet.createRow(0); - return row; - } - - /** - * Test to ensure we can only assign cell styles that belong - * to our workbook, and not those from other workbooks. - */ - public void testCellStyleWorkbookMatch() { - XSSFWorkbook wbA = new XSSFWorkbook(); - XSSFWorkbook wbB = new XSSFWorkbook(); - - XSSFCellStyle styA = wbA.createCellStyle(); - XSSFCellStyle styB = wbB.createCellStyle(); - - styA.verifyBelongsToStylesSource(wbA.getStylesSource()); - styB.verifyBelongsToStylesSource(wbB.getStylesSource()); - try { - styA.verifyBelongsToStylesSource(wbB.getStylesSource()); - fail(); - } catch(IllegalArgumentException e) {} - try { - styB.verifyBelongsToStylesSource(wbA.getStylesSource()); - fail(); - } catch(IllegalArgumentException e) {} - - Cell cellA = wbA.createSheet().createRow(0).createCell((short)0); - Cell cellB = wbB.createSheet().createRow(0).createCell((short)0); - - cellA.setCellStyle(styA); - cellB.setCellStyle(styB); - try { - cellA.setCellStyle(styB); - fail(); - } catch(IllegalArgumentException e) {} - try { - cellB.setCellStyle(styA); - fail(); - } catch(IllegalArgumentException e) {} - } - - - public void testHSSFXSSFToString(){ - Workbook xwb = new XSSFWorkbook(); - Sheet xsheet = xwb.createSheet(); - XSSFCell xcell = (XSSFCell) xsheet.createRow(0).createCell((short)0); - - Workbook hwb=new HSSFWorkbook(); - Sheet hsheet=hwb.createSheet(); - HSSFCell hcell = (HSSFCell) hsheet.createRow(0).createCell((short)0); - - //BLANK - assertEquals(hcell.toString(),xcell.toString()); - //BOOLEAN - xcell.setCellValue(true); - xcell.setCellType(Cell.CELL_TYPE_BOOLEAN); - hcell.setCellValue(true); - hcell.setCellType(Cell.CELL_TYPE_BOOLEAN); - assertEquals(hcell.toString(),xcell.toString()); - - //NUMERIC - - xcell.setCellValue(1234); - xcell.setCellType(Cell.CELL_TYPE_NUMERIC); - hcell.setCellValue(1234); - hcell.setCellType(Cell.CELL_TYPE_NUMERIC); - assertEquals(hcell.toString(),xcell.toString()); - - //DATE ******************** - - Calendar cal = Calendar.getInstance(); - cal.set(1903, 1, 8); - xcell.setCellValue(cal.getTime()); - CellStyle xstyle=xwb.createCellStyle(); - DataFormat format = xwb.createDataFormat(); - xstyle.setDataFormat(format.getFormat("YYYY-MM-DD")); - xcell.setCellStyle(xstyle); - - hcell.setCellValue(cal.getTime()); - CellStyle hstyle=hwb.createCellStyle(); - DataFormat hformat = hwb.createDataFormat(); - hstyle.setDataFormat(hformat.getFormat("YYYY-MM-DD")); - hcell.setCellStyle(hstyle); - - assertEquals(hcell.toString(),xcell.toString()); - - - //STRING - xcell.setCellValue(new XSSFRichTextString("text string")); - xcell.setCellType(Cell.CELL_TYPE_STRING); - hcell.setCellValue(new HSSFRichTextString("text string")); - hcell.setCellType(Cell.CELL_TYPE_STRING); - assertEquals(hcell.toString(),xcell.toString()); - - //ERROR - xcell.setCellErrorValue(FormulaError.VALUE); - xcell.setCellType(Cell.CELL_TYPE_ERROR); - - hcell.setCellErrorValue((byte)0); - hcell.setCellType(Cell.CELL_TYPE_ERROR); - - assertEquals(hcell.toString(),xcell.toString()); - - //FORMULA - xcell.setCellFormula("A1+B2"); - hcell.setCellValue("A1+B2"); - assertEquals(hcell.toString(),xcell.toString()); - - } - - -} diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFDataFormat.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFDataFormat.java new file mode 100644 index 0000000000..5a8b0fb3ab --- /dev/null +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFDataFormat.java @@ -0,0 +1,53 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.xssf.usermodel; + +import java.util.Calendar; +import java.util.Date; +import java.util.Map; + +import junit.framework.TestCase; + +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFRichTextString; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.ss.usermodel.*; +import org.apache.poi.ss.ITestDataProvider; +import org.apache.poi.xssf.XSSFTestDataSamples; +import org.apache.poi.xssf.XSSFITestDataProvider; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellType; + +/** + * Tests for {@link XSSFDataFormat} + * + */ +public final class TestXSSFDataFormat extends BaseTestDataFormat { + + @Override + protected ITestDataProvider getTestDataProvider(){ + return XSSFITestDataProvider.getInstance(); + } + /** + * Test setting and getting boolean values. + */ + public void testBuiltinFormats() throws Exception { + baseBuiltinFormats(); + } +} diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormat.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormat.java new file mode 100755 index 0000000000..431fa2d7d5 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormat.java @@ -0,0 +1,40 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.hssf.usermodel; + +import org.apache.poi.hssf.HSSFITestDataProvider; +import org.apache.poi.ss.usermodel.*; +import org.apache.poi.ss.ITestDataProvider; + +/** + * Tests for {@link HSSFDataFormat} + * + */ +public final class TestHSSFDataFormat extends BaseTestDataFormat { + + @Override + protected ITestDataProvider getTestDataProvider(){ + return HSSFITestDataProvider.getInstance(); + } + /** + * Test setting and getting boolean values. + */ + public void testBuiltinFormats() throws Exception { + baseBuiltinFormats(); + } +} \ No newline at end of file diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestDataFormat.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestDataFormat.java new file mode 100755 index 0000000000..a65c73a051 --- /dev/null +++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestDataFormat.java @@ -0,0 +1,63 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.ss.usermodel; + +import java.util.Map; + +import junit.framework.TestCase; +import org.apache.poi.ss.ITestDataProvider; + +/** + * Tests for {@link org.apache.poi.xssf.usermodel.XSSFDataFormat} + * + */ +public abstract class BaseTestDataFormat extends TestCase { + + /** + * @return an object that provides test data in HSSF / XSSF specific way + */ + protected abstract ITestDataProvider getTestDataProvider(); + + public void baseBuiltinFormats() throws Exception { + Workbook wb = getTestDataProvider().createWorkbook(); + + DataFormat df = wb.createDataFormat(); + + Map formats = BuiltinFormats.getBuiltinFormats(); + for (int idx : formats.keySet()) { + String fmt = formats.get(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)); + } +} \ No newline at end of file -- 2.39.5