diff options
| author | Yegor Kozlov <yegor@apache.org> | 2008-01-04 14:19:14 +0000 |
|---|---|---|
| committer | Yegor Kozlov <yegor@apache.org> | 2008-01-04 14:19:14 +0000 |
| commit | ce32b7c036323c2b722ee129ac8a428abd4fe5ce (patch) | |
| tree | 9f1274454491f23d795137e6e6afcd7887f98f6e /src/testcases/org | |
| parent | 329d1ae3307d1beccb50410dc960badd42ca5df0 (diff) | |
| download | poi-REL_3_0_2_BETA2.tar.gz poi-REL_3_0_2_BETA2.zip | |
merged TRUNK changes r608809 into REL_3_0_2_BETA2REL_3_0_2_BETA2
git-svn-id: https://svn.apache.org/repos/asf/poi/tags/REL_3_0_2_BETA2@608846 13f79535-47bb-0310-9956-ffa450edef68
Diffstat (limited to 'src/testcases/org')
21 files changed, 1298 insertions, 68 deletions
diff --git a/src/testcases/org/apache/poi/hssf/data/1900DateWindowing.xls b/src/testcases/org/apache/poi/hssf/data/1900DateWindowing.xls Binary files differindex e6d966456e..94fe5c1bec 100644 --- a/src/testcases/org/apache/poi/hssf/data/1900DateWindowing.xls +++ b/src/testcases/org/apache/poi/hssf/data/1900DateWindowing.xls diff --git a/src/testcases/org/apache/poi/hssf/data/1904DateWindowing.xls b/src/testcases/org/apache/poi/hssf/data/1904DateWindowing.xls Binary files differindex 142d3148f3..8c0dba1d7a 100644 --- a/src/testcases/org/apache/poi/hssf/data/1904DateWindowing.xls +++ b/src/testcases/org/apache/poi/hssf/data/1904DateWindowing.xls diff --git a/src/testcases/org/apache/poi/hssf/data/AverageTaxRates.xlsx b/src/testcases/org/apache/poi/hssf/data/AverageTaxRates.xlsx Binary files differnew file mode 100644 index 0000000000..fee8059e2b --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/data/AverageTaxRates.xlsx diff --git a/src/testcases/org/apache/poi/hssf/data/SimpleWithColours.xls b/src/testcases/org/apache/poi/hssf/data/SimpleWithColours.xls Binary files differnew file mode 100755 index 0000000000..ab3cdecf0b --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/data/SimpleWithColours.xls diff --git a/src/testcases/org/apache/poi/hssf/data/StringContinueRecords.xls b/src/testcases/org/apache/poi/hssf/data/StringContinueRecords.xls Binary files differnew file mode 100644 index 0000000000..f2ada9eb28 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/data/StringContinueRecords.xls diff --git a/src/testcases/org/apache/poi/hssf/data/Tables.xlsx b/src/testcases/org/apache/poi/hssf/data/Tables.xlsx Binary files differnew file mode 100644 index 0000000000..705e0ae373 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/data/Tables.xlsx diff --git a/src/testcases/org/apache/poi/hssf/data/TestDataValidation.xls b/src/testcases/org/apache/poi/hssf/data/TestDataValidation.xls Binary files differnew file mode 100644 index 0000000000..6b71a77f2d --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/data/TestDataValidation.xls diff --git a/src/testcases/org/apache/poi/hssf/data/TwoSheetsNoneHidden.xls b/src/testcases/org/apache/poi/hssf/data/TwoSheetsNoneHidden.xls Binary files differnew file mode 100644 index 0000000000..969f01408e --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/data/TwoSheetsNoneHidden.xls diff --git a/src/testcases/org/apache/poi/hssf/data/TwoSheetsOneHidden.xls b/src/testcases/org/apache/poi/hssf/data/TwoSheetsOneHidden.xls Binary files differnew file mode 100644 index 0000000000..940ffc0582 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/data/TwoSheetsOneHidden.xls diff --git a/src/testcases/org/apache/poi/hssf/data/sample-beta.xlsx b/src/testcases/org/apache/poi/hssf/data/sample-beta.xlsx Binary files differnew file mode 100644 index 0000000000..2eb36ee2a0 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/data/sample-beta.xlsx diff --git a/src/testcases/org/apache/poi/hssf/data/sample.xlsx b/src/testcases/org/apache/poi/hssf/data/sample.xlsx Binary files differindex 2eb36ee2a0..a275cf417e 100644 --- a/src/testcases/org/apache/poi/hssf/data/sample.xlsx +++ b/src/testcases/org/apache/poi/hssf/data/sample.xlsx diff --git a/src/testcases/org/apache/poi/hssf/data/templateExcelWithAutofilter.xls b/src/testcases/org/apache/poi/hssf/data/templateExcelWithAutofilter.xls Binary files differnew file mode 100644 index 0000000000..5813fde385 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/data/templateExcelWithAutofilter.xls diff --git a/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java b/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java index 027495a1b0..0aef5c765a 100644 --- a/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java +++ b/src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java @@ -68,6 +68,19 @@ public class TestExcelExtractor extends TestCase { ); } + public void testwithContinueRecords() throws Exception { + String path = System.getProperty("HSSF.testdata.path"); + FileInputStream fin = new FileInputStream(path + File.separator + "StringContinueRecords.xls"); + + ExcelExtractor extractor = new ExcelExtractor(new POIFSFileSystem(fin)); + + extractor.getText(); + + // Has masses of text + // Until we fixed bug #41064, this would've + // failed by now + assertTrue(extractor.getText().length() > 40960); + } public void testStringConcat() throws Exception { String path = System.getProperty("HSSF.testdata.path"); diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java b/src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java new file mode 100644 index 0000000000..f970ff26f1 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java @@ -0,0 +1,910 @@ +/* ==================================================================== + Copyright 2002-2004 Apache Software Foundation + + Licensed 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 junit.framework.TestCase; + +import org.apache.poi.hssf.util.*; + +import java.io.*; +import java.util.*; +import java.text.SimpleDateFormat; + +/** + * <p>Title: TestDataValidation</p> + * <p>Description: Class for testing Excel's data validation mechanism + * Second test : + * - + * </p> + * @author Dragos Buleandra ( dragos.buleandra@trade2b.ro ) + */ +public class TestDataValidation extends TestCase +{ + public TestDataValidation(String name) + { + super(name); + } + + protected void setUp() + { + String filename = System.getProperty("HSSF.testdata.path"); + if (filename == null) + { + System.setProperty("HSSF.testdata.path", "src/testcases/org/apache/poi/hssf/data"); + } + } + + public void testDataValidation() throws Exception + { + System.out.println("\nTest no. 2 - Test Excel's Data validation mechanism"); + String resultFile = System.getProperty("HSSF.testdata.path")+"/TestDataValidation.xls"; + HSSFWorkbook wb = new HSSFWorkbook(); + + HSSFCellStyle style_1 = this.createStyle( wb, HSSFCellStyle.ALIGN_LEFT ); + HSSFCellStyle style_2 = this.createStyle( wb, HSSFCellStyle.ALIGN_CENTER ); + HSSFCellStyle style_3 = this.createStyle( wb, HSSFCellStyle.ALIGN_CENTER, HSSFColor.GREY_25_PERCENT.index, true ); + HSSFCellStyle style_4 = this.createHeaderStyle(wb); + HSSFDataValidation data_validation = null; + + //data validation's number types + System.out.print(" Create sheet for Data Validation's number types ... "); + HSSFSheet fSheet = wb.createSheet("Number types"); + + //"Whole number" validation type + this.createDVTypeRow( wb, 0, style_3, "Whole number"); + this.createHeaderRow( wb, 0, style_4 ); + + short start_row = (short)fSheet.getPhysicalNumberOfRows(); + data_validation = new HSSFDataValidation((short)(start_row),(short)0,(short)(start_row),(short)0); + data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_INTEGER); + data_validation.setOperator(HSSFDataValidation.OPERATOR_BETWEEN); + data_validation.setFirstFormula("2"); + data_validation.setSecondFormula("6"); + data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); + data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Between 2 and 6 ", true, true, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + + data_validation.setFirstRow((short)(start_row+1)); + data_validation.setLastRow((short)(start_row+1)); + data_validation.setEmptyCellAllowed(false); + data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_BETWEEN); + data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_INFO); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Not between 2 and 6 ", false, true, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type = INFO" ); + + data_validation.setFirstRow((short)(start_row+2)); + data_validation.setLastRow((short)(start_row+2)); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(false); + data_validation.setFirstFormula("3"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_EQUAL); + data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Equal to 3", false, false, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type = WARNING" ); + + data_validation.setFirstRow((short)(start_row+3)); + data_validation.setLastRow((short)(start_row+3)); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(false); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula("3"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_EQUAL); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Not equal to 3", false, false, false ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + data_validation.setFirstRow((short)(start_row+4)); + data_validation.setLastRow((short)(start_row+4)); + data_validation.setEmptyCellAllowed(true); + data_validation.setShowPromptBox(false); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula("3"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_THAN); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than 3", true, false, false ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + data_validation.setFirstRow((short)(start_row+5)); + data_validation.setLastRow((short)(start_row+5)); + data_validation.setEmptyCellAllowed(true); + data_validation.setShowPromptBox(true); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula("3"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_THAN); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than 3", true, true, false ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + data_validation.setFirstRow((short)(start_row+6)); + data_validation.setLastRow((short)(start_row+6)); + data_validation.setEmptyCellAllowed(true); + data_validation.setShowPromptBox(false); + data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_STOP); + data_validation.setShowErrorBox(true); + data_validation.setFirstFormula("4"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_OR_EQUAL); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than or equal to 4", true, false, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + + data_validation.setFirstRow((short)(start_row+7)); + data_validation.setLastRow((short)(start_row+7)); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(true); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula("4"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_OR_EQUAL); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than or equal to 4", false, true, false ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + //"Decimal" validation type + this.createDVTypeRow( wb, 0, style_3, "Decimal"); + this.createHeaderRow( wb, 0, style_4 ); + + start_row += (short)(8+4); + data_validation = new HSSFDataValidation((short)(start_row),(short)0,(short)(start_row),(short)0); + data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_DECIMAL); + data_validation.setOperator(HSSFDataValidation.OPERATOR_BETWEEN); + data_validation.setFirstFormula("2"); + data_validation.setSecondFormula("6"); + data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); + data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Between 2 and 6 ", true, true, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + + data_validation.setFirstRow((short)(start_row+1)); + data_validation.setLastRow((short)(start_row+1)); + data_validation.setEmptyCellAllowed(false); + data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_BETWEEN); + data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_INFO); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Not between 2 and 6 ", false, true, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type = INFO" ); + + data_validation.setFirstRow((short)(start_row+2)); + data_validation.setLastRow((short)(start_row+2)); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(false); + data_validation.setFirstFormula("3"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_EQUAL); + data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Equal to 3", false, false, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type = WARNING" ); + + data_validation.setFirstRow((short)(start_row+3)); + data_validation.setLastRow((short)(start_row+3)); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(false); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula("3"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_EQUAL); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Not equal to 3", false, false, false ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + data_validation.setFirstRow((short)(start_row+4)); + data_validation.setLastRow((short)(start_row+4)); + data_validation.setEmptyCellAllowed(true); + data_validation.setShowPromptBox(false); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula("3"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_THAN); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than 3", true, false, false ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + data_validation.setFirstRow((short)(start_row+5)); + data_validation.setLastRow((short)(start_row+5)); + data_validation.setEmptyCellAllowed(true); + data_validation.setShowPromptBox(true); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula("3"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_THAN); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than 3", true, true, false ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + data_validation.setFirstRow((short)(start_row+6)); + data_validation.setLastRow((short)(start_row+6)); + data_validation.setEmptyCellAllowed(true); + data_validation.setShowPromptBox(false); + data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_STOP); + data_validation.setShowErrorBox(true); + data_validation.setFirstFormula("4"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_OR_EQUAL); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than or equal to 4", true, false, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + + data_validation.setFirstRow((short)(start_row+7)); + data_validation.setLastRow((short)(start_row+7)); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(true); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula("4"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_OR_EQUAL); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than or equal to 4", false, true, false ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + System.out.println("done !"); + + //"List" Data Validation type + /** @todo List*/ + System.out.print(" Create sheet for 'List' Data Validation type ... "); + fSheet = wb.createSheet("Lists"); + + this.createDVTypeRow( wb, 1, style_3, "Explicit lists - list items are explicitly provided"); + this.createDVDeescriptionRow( wb, 1, style_3, "Disadvantage - sum of item's length should be less than 255 characters"); + this.createHeaderRow( wb, 1, style_4 ); + + start_row = (short)fSheet.getPhysicalNumberOfRows(); + data_validation = new HSSFDataValidation((short)(start_row),(short)0,(short)(start_row),(short)0); + data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST); + data_validation.setFirstFormula("1+2+3"); + data_validation.setSecondFormula(null); + data_validation.setSurppressDropDownArrow(false); + data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); + data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "POIFS,HSSF,HWPF,HPSF", true, true, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type=STOP ; In-cell dropdown=yes" ); + + data_validation = new HSSFDataValidation((short)(start_row+1),(short)0,(short)(start_row+1),(short)0); + data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST); + data_validation.setFirstFormula("4+5+6+7"); + data_validation.setSecondFormula(null); + data_validation.setSurppressDropDownArrow(false); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(false); + data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "POIFS,HSSF,HWPF,HPSF", false, false, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type=STOP ; In-cell dropdown=yes" ); + + data_validation = new HSSFDataValidation((short)(start_row+2),(short)0,(short)(start_row+2),(short)0); + data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST); + data_validation.setFirstFormula("7+21"); + data_validation.setSecondFormula(null); + data_validation.setSurppressDropDownArrow(true); + data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); + data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "POIFS,HSSF,HWPF,HPSF", true, true, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type=STOP ; In-cell dropdown=no" ); + + data_validation = new HSSFDataValidation((short)(start_row+3),(short)0,(short)(start_row+3),(short)0); + data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST); + data_validation.setFirstFormula("8/2"); + data_validation.setSecondFormula(null); + data_validation.setSurppressDropDownArrow(true); + data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(false); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "POIFS,HSSF,HWPF,HPSF", false, false, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type=STOP ; In-cell dropdown=no" ); + + this.createDVTypeRow( wb, 1, style_3, "Reference lists - list items are taken from others cells"); + this.createDVDeescriptionRow( wb, 1, style_3, "Advantage - no restriction regarding the sum of item's length"); + this.createHeaderRow( wb, 1, style_4 ); + + start_row += (short)(4+5); + String cellStrValue = "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "+ + "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "+ + "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "+ + "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "; + + String strFormula = "$A$100:$A$120"; + data_validation = new HSSFDataValidation((short)(start_row),(short)0,(short)(start_row),(short)0); + data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST); + data_validation.setFirstFormula(strFormula); + data_validation.setSecondFormula(null); + data_validation.setSurppressDropDownArrow(false); + data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); + data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, strFormula, true, true, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type=STOP ; In-cell dropdown=yes" ); + + data_validation = new HSSFDataValidation((short)(start_row+1),(short)0,(short)(start_row+1),(short)0); + data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST); + data_validation.setFirstFormula(strFormula); + data_validation.setSecondFormula(null); + data_validation.setSurppressDropDownArrow(false); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(false); + data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, strFormula, false, false, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type=STOP ; In-cell dropdown=yes" ); + + data_validation = new HSSFDataValidation((short)(start_row+2),(short)0,(short)(start_row+2),(short)0); + data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST); + data_validation.setFirstFormula(strFormula); + data_validation.setSecondFormula(null); + data_validation.setSurppressDropDownArrow(true); + data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); + data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, strFormula, true, true, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type=STOP ; In-cell dropdown=no" ); + + data_validation = new HSSFDataValidation((short)(start_row+3),(short)0,(short)(start_row+3),(short)0); + data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST); + data_validation.setFirstFormula(strFormula); + data_validation.setSecondFormula(null); + data_validation.setSurppressDropDownArrow(true); + data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(false); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, strFormula, false, false, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type=STOP ; In-cell dropdown=no" ); + + for (int i=100; i<=120; i++) + { + HSSFRow currRow = fSheet.createRow(i); + currRow.createCell((short)0).setCellValue(cellStrValue); +// currRow.hide( true ); + } + + System.out.println("done !"); + + //Date/Time Validation type + System.out.print(" Create sheet for 'Date' and 'Time' Data Validation types ... "); + fSheet = wb.createSheet("Date_Time"); + SimpleDateFormat df = new SimpleDateFormat("m/d/yyyy"); + HSSFDataFormat dataFormat = wb.createDataFormat(); + short fmtDate = dataFormat.getFormat("m/d/yyyy"); + short fmtTime = dataFormat.getFormat("h:mm"); + HSSFCellStyle cellStyle_data = wb.createCellStyle(); + cellStyle_data.setDataFormat(fmtDate); + HSSFCellStyle cellStyle_time = wb.createCellStyle(); + cellStyle_time.setDataFormat(fmtTime); + + this.createDVTypeRow( wb, 2, style_3, "Date ( cells are already formated as date - m/d/yyyy)"); + this.createHeaderRow( wb, 2, style_4 ); + + start_row = (short)fSheet.getPhysicalNumberOfRows(); + data_validation = new HSSFDataValidation((short)(start_row),(short)0,(short)(start_row),(short)0); + data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_DATE); + data_validation.setOperator(HSSFDataValidation.OPERATOR_BETWEEN); + + data_validation.setFirstFormula( String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("1/2/2004"))) ); + data_validation.setSecondFormula( String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("1/6/2004"))) ); + + data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); + data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Between 1/2/2004 and 1/6/2004 ", true, true, true ); + this.setCellFormat( fSheet, cellStyle_data ); + this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + + data_validation.setFirstRow((short)(start_row+1)); + data_validation.setLastRow((short)(start_row+1)); + data_validation.setEmptyCellAllowed(false); + data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_BETWEEN); + data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_INFO); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Not between 1/2/2004 and 1/6/2004 ", false, true, true ); + this.setCellFormat( fSheet, cellStyle_data ); + this.writeOtherSettings( fSheet, style_1, "Error box type = INFO" ); + + data_validation.setFirstRow((short)(start_row+2)); + data_validation.setLastRow((short)(start_row+2)); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(false); + data_validation.setFirstFormula(String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("3/2/2004")))); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_EQUAL); + data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Equal to 3/2/2004", false, false, true ); + this.setCellFormat( fSheet, cellStyle_data ); + this.writeOtherSettings( fSheet, style_1, "Error box type = WARNING" ); + + data_validation.setFirstRow((short)(start_row+3)); + data_validation.setLastRow((short)(start_row+3)); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(false); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula(String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("3/2/2004")))); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_EQUAL); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Not equal to 3/2/2004", false, false, false ); + this.setCellFormat( fSheet, cellStyle_data ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + data_validation.setFirstRow((short)(start_row+4)); + data_validation.setLastRow((short)(start_row+4)); + data_validation.setEmptyCellAllowed(true); + data_validation.setShowPromptBox(false); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula(String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("3/2/2004")))); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_THAN); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than 3/2/2004", true, false, false ); + this.setCellFormat( fSheet, cellStyle_data ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + data_validation.setFirstRow((short)(start_row+5)); + data_validation.setLastRow((short)(start_row+5)); + data_validation.setEmptyCellAllowed(true); + data_validation.setShowPromptBox(true); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula(String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("3/2/2004")))); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_THAN); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than 3/2/2004", true, true, false ); + this.setCellFormat( fSheet, cellStyle_data ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + data_validation.setFirstRow((short)(start_row+6)); + data_validation.setLastRow((short)(start_row+6)); + data_validation.setEmptyCellAllowed(true); + data_validation.setShowPromptBox(false); + data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_STOP); + data_validation.setShowErrorBox(true); + data_validation.setFirstFormula(String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("3/2/2004")))); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_OR_EQUAL); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than or equal to 3/2/2004", true, false, true ); + this.setCellFormat( fSheet, cellStyle_data ); + this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + + data_validation.setFirstRow((short)(start_row+7)); + data_validation.setLastRow((short)(start_row+7)); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(true); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula(String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("3/4/2004")))); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_OR_EQUAL); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than or equal to 3/4/2004", false, true, false ); + this.setCellFormat( fSheet, cellStyle_data ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + //"Time" validation type + this.createDVTypeRow( wb, 2, style_3, "Time ( cells are already formated as time - h:mm)"); + this.createHeaderRow( wb, 2, style_4 ); + + df = new SimpleDateFormat("hh:mm"); + + start_row += (short)(8+4); + data_validation = new HSSFDataValidation((short)(start_row),(short)0,(short)(start_row),(short)0); + data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_TIME); + data_validation.setOperator(HSSFDataValidation.OPERATOR_BETWEEN); + data_validation.setFirstFormula(String.valueOf(HSSFDateUtil.getExcelDate(df.parse("12:00")))); + data_validation.setSecondFormula(String.valueOf(HSSFDateUtil.getExcelDate(df.parse("16:00")))); + data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); + data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Between 12:00 and 16:00 ", true, true, true ); + this.setCellFormat( fSheet, cellStyle_time ); + this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + + data_validation.setFirstRow((short)(start_row+1)); + data_validation.setLastRow((short)(start_row+1)); + data_validation.setEmptyCellAllowed(false); + data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_BETWEEN); + data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_INFO); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Not between 12:00 and 16:00 ", false, true, true ); + this.setCellFormat( fSheet, cellStyle_time ); + this.writeOtherSettings( fSheet, style_1, "Error box type = INFO" ); + + data_validation.setFirstRow((short)(start_row+2)); + data_validation.setLastRow((short)(start_row+2)); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(false); + data_validation.setFirstFormula(String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("13:35")))); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_EQUAL); + data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Equal to 13:35", false, false, true ); + this.setCellFormat( fSheet, cellStyle_time ); + this.writeOtherSettings( fSheet, style_1, "Error box type = WARNING" ); + + data_validation.setFirstRow((short)(start_row+3)); + data_validation.setLastRow((short)(start_row+3)); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(false); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula(String.valueOf(HSSFDateUtil.getExcelDate(df.parse("13:35")))); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_EQUAL); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Not equal to 13:35", false, false, false ); + this.setCellFormat( fSheet, cellStyle_time ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + data_validation.setFirstRow((short)(start_row+4)); + data_validation.setLastRow((short)(start_row+4)); + data_validation.setEmptyCellAllowed(true); + data_validation.setShowPromptBox(false); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula(String.valueOf(HSSFDateUtil.getExcelDate(df.parse("12:00")))); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_THAN); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than 12:00", true, false, false ); + this.setCellFormat( fSheet, cellStyle_time ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + data_validation.setFirstRow((short)(start_row+5)); + data_validation.setLastRow((short)(start_row+5)); + data_validation.setEmptyCellAllowed(true); + data_validation.setShowPromptBox(true); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula(String.valueOf(HSSFDateUtil.getExcelDate(df.parse("12:00")))); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_THAN); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than 12:00", true, true, false ); + this.setCellFormat( fSheet, cellStyle_time ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + data_validation.setFirstRow((short)(start_row+6)); + data_validation.setLastRow((short)(start_row+6)); + data_validation.setEmptyCellAllowed(true); + data_validation.setShowPromptBox(false); + data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_STOP); + data_validation.setShowErrorBox(true); + data_validation.setFirstFormula(String.valueOf(HSSFDateUtil.getExcelDate(df.parse("14:00")))); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_OR_EQUAL); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than or equal to 14:00", true, false, true ); + this.setCellFormat( fSheet, cellStyle_time ); + this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + + data_validation.setFirstRow((short)(start_row+7)); + data_validation.setLastRow((short)(start_row+7)); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(true); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula(String.valueOf(HSSFDateUtil.getExcelDate(df.parse("14:00")))); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_OR_EQUAL); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than or equal to 14:00", false, true, false ); + this.setCellFormat( fSheet, cellStyle_time ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + System.out.println("done !"); + + //"Text length" validation type + System.out.print(" Create sheet for 'Text length' Data Validation type... "); + fSheet = wb.createSheet("Text length"); + this.createHeaderRow( wb, 3, style_4 ); + + data_validation = new HSSFDataValidation((short)1,(short)0,(short)1,(short)0); + data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_TEXT_LENGTH); + data_validation.setOperator(HSSFDataValidation.OPERATOR_BETWEEN); + data_validation.setFirstFormula("2"); + data_validation.setSecondFormula("6"); + data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); + data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Between 2 and 6 ", true, true, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + + data_validation.setFirstRow((short)2); + data_validation.setLastRow((short)2); + data_validation.setEmptyCellAllowed(false); + data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_BETWEEN); + data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_INFO); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Not between 2 and 6 ", false, true, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type = INFO" ); + + data_validation.setFirstRow((short)3); + data_validation.setLastRow((short)3); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(false); + data_validation.setFirstFormula("3"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_EQUAL); + data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Equal to 3", false, false, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type = WARNING" ); + + data_validation.setFirstRow((short)4); + data_validation.setLastRow((short)4); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(false); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula("3"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_EQUAL); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Not equal to 3", false, false, false ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + data_validation.setFirstRow((short)5); + data_validation.setLastRow((short)5); + data_validation.setEmptyCellAllowed(true); + data_validation.setShowPromptBox(false); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula("3"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_THAN); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than 3", true, false, false ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + data_validation.setFirstRow((short)6); + data_validation.setLastRow((short)6); + data_validation.setEmptyCellAllowed(true); + data_validation.setShowPromptBox(true); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula("3"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_THAN); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than 3", true, true, false ); + this.writeOtherSettings( fSheet, style_1, "-" ); + + data_validation.setFirstRow((short)7); + data_validation.setLastRow((short)7); + data_validation.setEmptyCellAllowed(true); + data_validation.setShowPromptBox(false); + data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_STOP); + data_validation.setShowErrorBox(true); + data_validation.setFirstFormula("4"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_OR_EQUAL); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than or equal to 4", true, false, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + + data_validation.setFirstRow((short)8); + data_validation.setLastRow((short)8); + data_validation.setEmptyCellAllowed(false); + data_validation.setShowPromptBox(true); + data_validation.setShowErrorBox(false); + data_validation.setFirstFormula("4"); + data_validation.setSecondFormula(null); + data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_OR_EQUAL); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than or equal to 4", false, true, false ); + this.writeOtherSettings( fSheet, style_1, "-" ); + System.out.println("done !"); + + //Custom Validation type + System.out.print(" Create sheet for 'Custom' Data Validation type ... "); + fSheet = wb.createSheet("Custom"); + this.createHeaderRow( wb, 4, style_4 ); + + data_validation = new HSSFDataValidation((short)1,(short)0,(short)1,(short)0); + data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_FORMULA); + data_validation.setFirstFormula("ISNUMBER($A2)"); + data_validation.setSecondFormula(null); + data_validation.setShowPromptBox(true); + data_validation.setShowErrorBox(true); + data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); + data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "ISNUMBER(A2)", true, true, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + + data_validation = new HSSFDataValidation((short)2,(short)0,(short)2,(short)0); + data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_FORMULA); + data_validation.setFirstFormula("IF(SUM(A2:A3)=5,TRUE,FALSE)"); + data_validation.setSecondFormula(null); + data_validation.setShowPromptBox(false); + data_validation.setShowErrorBox(true); + data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING); + data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); + data_validation.setEmptyCellAllowed(false); + fSheet.addValidationData(data_validation); + this.writeDataValidationSettings( fSheet, style_1, style_2, "IF(SUM(A2:A3)=5,TRUE,FALSE)", false, false, true ); + this.writeOtherSettings( fSheet, style_1, "Error box type = WARNING" ); + + System.out.println("done !"); + + //so , everything it's ok for now ; it remains for you to open the file + System.out.println("\n Everything it's ok since we've got so far -:) !\n"+ + " In order to complete the test , it remains for you to open the file \n"+ + " and see if there are four sheets , as described !"); + System.out.println(" File was saved in \""+resultFile+"\""); + + FileOutputStream fileOut = new FileOutputStream(resultFile); + wb.write(fileOut); + fileOut.close(); + } + + private void createDVTypeRow( HSSFWorkbook wb, int sheetNo , HSSFCellStyle cellStyle, String strTypeDescription) + { + HSSFSheet sheet = wb.getSheetAt(sheetNo); + HSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows()); + row = sheet.createRow(sheet.getPhysicalNumberOfRows()); + sheet.addMergedRegion(new Region((short)(sheet.getPhysicalNumberOfRows()-1),(short)0,(short)(sheet.getPhysicalNumberOfRows()-1),(short)5)); + HSSFCell cell = row.createCell((short)0); + cell.setCellValue(strTypeDescription); + cell.setCellStyle(cellStyle); + row = sheet.createRow(sheet.getPhysicalNumberOfRows()); + } + + private void createDVDeescriptionRow( HSSFWorkbook wb, int sheetNo , HSSFCellStyle cellStyle, String strTypeDescription ) + { + HSSFSheet sheet = wb.getSheetAt(sheetNo); + HSSFRow row = sheet.getRow(sheet.getPhysicalNumberOfRows()-1); + sheet.addMergedRegion(new Region((short)(sheet.getPhysicalNumberOfRows()-1),(short)0,(short)(sheet.getPhysicalNumberOfRows()-1),(short)5)); + HSSFCell cell = row.createCell((short)0); + cell.setCellValue(strTypeDescription); + cell.setCellStyle(cellStyle); + row = sheet.createRow(sheet.getPhysicalNumberOfRows()); + } + + private void createHeaderRow( HSSFWorkbook wb, int sheetNo , HSSFCellStyle cellStyle ) + { + HSSFSheet sheet = wb.getSheetAt(sheetNo); + HSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows()); + row.setHeight((short)400); + for ( int i=0; i<6; i++ ) + { + row.createCell((short)i).setCellStyle( cellStyle ); + if ( i==2 || i==3 || i==4 ) + { + sheet.setColumnWidth( (short) i, (short) 3500); + } + else if ( i== 5) + { + sheet.setColumnWidth( (short) i, (short) 10000); + } + else + { + sheet.setColumnWidth( (short) i, (short) 8000); + } + } + HSSFCell cell = row.getCell((short)0); + cell.setCellValue("Data validation cells"); + cell = row.getCell((short)1); + cell.setCellValue("Condition"); + cell = row.getCell((short)2); + cell.setCellValue("Allow blank"); + cell = row.getCell((short)3); + cell.setCellValue("Prompt box"); + cell = row.getCell((short)4); + cell.setCellValue("Error box"); + cell = row.getCell((short)5); + cell.setCellValue("Other settings"); + } + + private HSSFCellStyle createHeaderStyle(HSSFWorkbook wb) + { + HSSFFont font = wb.createFont(); + font.setColor( HSSFColor.WHITE.index ); + font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); + + HSSFCellStyle cellStyle = wb.createCellStyle(); + cellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index); + cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); + cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); + cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); + cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); + cellStyle.setLeftBorderColor(HSSFColor.WHITE.index); + cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); + cellStyle.setTopBorderColor(HSSFColor.WHITE.index); + cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); + cellStyle.setRightBorderColor(HSSFColor.WHITE.index); + cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); + cellStyle.setBottomBorderColor(HSSFColor.WHITE.index); + cellStyle.setFont(font); + return cellStyle; + } + + private HSSFCellStyle createStyle( HSSFWorkbook wb, short h_align, short color, boolean bold ) + { + HSSFFont font = wb.createFont(); + if ( bold ) + { + font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); + } + + HSSFCellStyle cellStyle = wb.createCellStyle(); + cellStyle.setFont(font); + cellStyle.setFillForegroundColor(color); + cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); + cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); + cellStyle.setAlignment(h_align); + cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); + cellStyle.setLeftBorderColor(HSSFColor.BLACK.index); + cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); + cellStyle.setTopBorderColor(HSSFColor.BLACK.index); + cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); + cellStyle.setRightBorderColor(HSSFColor.BLACK.index); + cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); + cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); + + return cellStyle; + } + + private HSSFCellStyle createStyle( HSSFWorkbook wb, short h_align ) + { + return this.createStyle(wb, h_align, HSSFColor.WHITE.index, false); + } + + private void writeDataValidationSettings( HSSFSheet sheet, HSSFCellStyle style_1, HSSFCellStyle style_2, String strCondition, boolean allowEmpty, boolean inputBox, boolean errorBox ) + { + HSSFRow row = sheet.createRow( sheet.getPhysicalNumberOfRows() ); + //condition's string + HSSFCell cell = row.createCell((short)1); + cell.setCellStyle(style_1); + cell.setCellValue(strCondition); + //allow empty cells + cell = row.createCell((short)2); + cell.setCellStyle(style_2); + cell.setCellValue( ((allowEmpty) ? "yes" : "no") ); + //show input box + cell = row.createCell((short)3); + cell.setCellStyle(style_2); + cell.setCellValue( ((inputBox) ? "yes" : "no") ); + //show error box + cell = row.createCell((short)4); + cell.setCellStyle(style_2); + cell.setCellValue( ((errorBox) ? "yes" : "no") ); + } + + private void setCellFormat( HSSFSheet sheet, HSSFCellStyle cell_style ) + { + HSSFRow row = sheet.getRow( sheet.getPhysicalNumberOfRows() -1 ); + HSSFCell cell = row.createCell((short)0); + cell.setCellStyle(cell_style); + } + + private void writeOtherSettings( HSSFSheet sheet, HSSFCellStyle style, String strStettings ) + { + HSSFRow row = sheet.getRow( sheet.getPhysicalNumberOfRows() -1 ); + HSSFCell cell = row.createCell((short)5); + cell.setCellStyle(style); + cell.setCellValue(strStettings); + } + + public static void main(String[] args) + { + junit.textui.TestRunner.run(TestDataValidation.class); + } +} diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java index 5742660260..ee3cace263 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFCell.java @@ -44,6 +44,7 @@ import java.util.GregorianCalendar; * paticular datatypes, etc. * @author Andrew C. Oliver (andy at superlinksoftware dot com) * @author Dan Sherman (dsherman at isisph.com) + * @author Alex Jacoby (ajacoby at gmail.com) */ public class TestHSSFCell @@ -107,42 +108,106 @@ extends TestCase { } /** - * Checks that the recognition of files using 1904 date windowing - * is working properly. Conversion of the date is also an issue, - * but there's a separate unit test for that. - */ - public void testDateWindowing() throws Exception { - GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000 - Date date = cal.getTime(); - String path = System.getProperty("HSSF.testdata.path"); - - // first check a file with 1900 Date Windowing - String filename = path + "/1900DateWindowing.xls"; - FileInputStream stream = new FileInputStream(filename); - POIFSFileSystem fs = new POIFSFileSystem(stream); - HSSFWorkbook workbook = new HSSFWorkbook(fs); - HSSFSheet sheet = workbook.getSheetAt(0); - - assertEquals("Date from file using 1900 Date Windowing", - date.getTime(), - sheet.getRow(0).getCell((short)0) - .getDateCellValue().getTime()); - stream.close(); - - // now check a file with 1904 Date Windowing - filename = path + "/1904DateWindowing.xls"; - stream = new FileInputStream(filename); - fs = new POIFSFileSystem(stream); - workbook = new HSSFWorkbook(fs); - sheet = workbook.getSheetAt(0); - - assertEquals("Date from file using 1904 Date Windowing", - date.getTime(), - sheet.getRow(0).getCell((short)0) - .getDateCellValue().getTime()); - stream.close(); - } - + * Checks that the recognition of files using 1904 date windowing + * is working properly. Conversion of the date is also an issue, + * but there's a separate unit test for that. + */ + public void testDateWindowingRead() throws Exception { + GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000 + Date date = cal.getTime(); + String path = System.getProperty("HSSF.testdata.path"); + + // first check a file with 1900 Date Windowing + String filename = path + "/1900DateWindowing.xls"; + FileInputStream stream = new FileInputStream(filename); + POIFSFileSystem fs = new POIFSFileSystem(stream); + HSSFWorkbook workbook = new HSSFWorkbook(fs); + HSSFSheet sheet = workbook.getSheetAt(0); + + assertEquals("Date from file using 1900 Date Windowing", + date.getTime(), + sheet.getRow(0).getCell((short)0) + .getDateCellValue().getTime()); + stream.close(); + + // now check a file with 1904 Date Windowing + filename = path + "/1904DateWindowing.xls"; + stream = new FileInputStream(filename); + fs = new POIFSFileSystem(stream); + workbook = new HSSFWorkbook(fs); + sheet = workbook.getSheetAt(0); + + assertEquals("Date from file using 1904 Date Windowing", + date.getTime(), + sheet.getRow(0).getCell((short)0) + .getDateCellValue().getTime()); + stream.close(); + } + + /** + * Checks that dates are properly written to both types of files: + * those with 1900 and 1904 date windowing. Note that if the + * previous test ({@link #testDateWindowingRead}) fails, the + * results of this test are meaningless. + */ + public void testDateWindowingWrite() throws Exception { + GregorianCalendar cal = new GregorianCalendar(2000,0,1); // Jan. 1, 2000 + Date date = cal.getTime(); + String path = System.getProperty("HSSF.testdata.path"); + + // first check a file with 1900 Date Windowing + String filename = path + "/1900DateWindowing.xls"; + writeCell(filename, 0, (short) 1, date); + assertEquals("Date from file using 1900 Date Windowing", + date.getTime(), + readCell(filename, 0, (short) 1).getTime()); + + // now check a file with 1904 Date Windowing + filename = path + "/1904DateWindowing.xls"; + writeCell(filename, 0, (short) 1, date); + assertEquals("Date from file using 1900 Date Windowing", + date.getTime(), + readCell(filename, 0, (short) 1).getTime()); + } + + /** + * Sets cell value and writes file. + */ + private void writeCell(String filename, + int rowIdx, short colIdx, Date date) throws Exception { + FileInputStream stream = new FileInputStream(filename); + POIFSFileSystem fs = new POIFSFileSystem(stream); + HSSFWorkbook workbook = new HSSFWorkbook(fs); + HSSFSheet sheet = workbook.getSheetAt(0); + HSSFRow row = sheet.getRow(rowIdx); + HSSFCell cell = row.getCell(colIdx); + + if (cell == null) { + cell = row.createCell(colIdx); + } + cell.setCellValue(date); + + // Write the file + stream.close(); + FileOutputStream oStream = new FileOutputStream(filename); + workbook.write(oStream); + oStream.close(); + } + + /** + * Reads cell value from file. + */ + private Date readCell(String filename, + int rowIdx, short colIdx) throws Exception { + FileInputStream stream = new FileInputStream(filename); + POIFSFileSystem fs = new POIFSFileSystem(stream); + HSSFWorkbook workbook = new HSSFWorkbook(fs); + HSSFSheet sheet = workbook.getSheetAt(0); + HSSFRow row = sheet.getRow(rowIdx); + HSSFCell cell = row.getCell(colIdx); + return cell.getDateCellValue(); + } + /** * Tests that the active cell can be correctly read and set */ diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFClientAnchor.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFClientAnchor.java index 10c4e390cb..655f78eddb 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFClientAnchor.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFClientAnchor.java @@ -85,4 +85,23 @@ public class TestHSSFClientAnchor extends TestCase assertEquals(anchor[i].getRow2(), record.getRow2()); } } + + public void testAnchorHeightInPoints(){ + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet(); + + HSSFClientAnchor[] anchor = { + new HSSFClientAnchor( 0 , 0, 0 , 0 ,(short)0, 1,(short)1, 3), + new HSSFClientAnchor( 0 , 254 , 0 , 126 ,(short)0, 1,(short)1, 3), + new HSSFClientAnchor( 0 , 128 , 0 , 128 ,(short)0, 1,(short)1, 3), + new HSSFClientAnchor( 0 , 0 , 0 , 128 ,(short)0, 1,(short)1, 3), + }; + float[] ref = {24.0f, 18.0f, 24.0f, 30.0f}; + for (int i = 0; i < anchor.length; i++) { + float height = anchor[i].getAnchorHeightInPoints(sheet); + assertEquals(ref[i], height, 0); + } + + } + } diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java index f8aeb8517e..6b37f749ee 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java @@ -36,6 +36,8 @@ import org.apache.poi.poifs.filesystem.POIFSFileSystem; * * @author Dan Sherman (dsherman at isisph.com) * @author Hack Kampbjorn (hak at 2mba.dk) + * @author Pavel Krupets (pkrupets at palmtreebusiness dot com) + * @author Alex Jacoby (ajacoby at gmail.com) * @version %I%, %G% */ @@ -47,7 +49,9 @@ public class TestHSSFDateUtil public static final int CALENDAR_FEBRUARY = 1; public static final int CALENDAR_MARCH = 2; public static final int CALENDAR_APRIL = 3; - + public static final int CALENDAR_JULY = 6; + public static final int CALENDAR_OCTOBER = 9; + public TestHSSFDateUtil(String s) { super(s); @@ -67,10 +71,10 @@ public class TestHSSFDateUtil GregorianCalendar date = new GregorianCalendar(2002, 0, 1, hour, 1, 1); double excelDate = - HSSFDateUtil.getExcelDate(date.getTime()); + HSSFDateUtil.getExcelDate(date.getTime(), false); assertEquals("Checking hour = " + hour, date.getTime().getTime(), - HSSFDateUtil.getJavaDate(excelDate).getTime()); + HSSFDateUtil.getJavaDate(excelDate, false).getTime()); } // check 1900 and 1904 date windowing conversions @@ -99,7 +103,7 @@ public class TestHSSFDateUtil public void testExcelConversionOnDSTStart() { TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen"); TimeZone.setDefault(cet); - Calendar cal = new GregorianCalendar(2004, Calendar.MARCH, 28); + Calendar cal = new GregorianCalendar(2004, CALENDAR_MARCH, 28); for (int hour = 0; hour < 24; hour++) { // Skip 02:00 CET as that is the Daylight change time @@ -110,7 +114,7 @@ public class TestHSSFDateUtil cal.set(Calendar.HOUR_OF_DAY, hour); Date javaDate = cal.getTime(); - double excelDate = HSSFDateUtil.getExcelDate(javaDate); + double excelDate = HSSFDateUtil.getExcelDate(javaDate, false); double difference = excelDate - Math.floor(excelDate); int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60; assertEquals("Checking " + hour + " hour on Daylight Saving Time start date", @@ -118,7 +122,7 @@ public class TestHSSFDateUtil differenceInHours); assertEquals("Checking " + hour + " hour on Daylight Saving Time start date", javaDate.getTime(), - HSSFDateUtil.getJavaDate(excelDate).getTime()); + HSSFDateUtil.getJavaDate(excelDate, false).getTime()); } } @@ -129,8 +133,8 @@ public class TestHSSFDateUtil public void testJavaConversionOnDSTStart() { TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen"); TimeZone.setDefault(cet); - Calendar cal = new GregorianCalendar(2004, Calendar.MARCH, 28); - double excelDate = HSSFDateUtil.getExcelDate(cal.getTime()); + Calendar cal = new GregorianCalendar(2004, CALENDAR_MARCH, 28); + double excelDate = HSSFDateUtil.getExcelDate(cal.getTime(), false); double oneHour = 1.0 / 24; double oneMinute = oneHour / 60; for (int hour = 0; hour < 24; hour++, excelDate += oneHour) { @@ -142,10 +146,10 @@ public class TestHSSFDateUtil } cal.set(Calendar.HOUR_OF_DAY, hour); - Date javaDate = HSSFDateUtil.getJavaDate(excelDate); + Date javaDate = HSSFDateUtil.getJavaDate(excelDate, false); assertEquals("Checking " + hour + " hours on Daylight Saving Time start date", excelDate, - HSSFDateUtil.getExcelDate(javaDate), oneMinute); + HSSFDateUtil.getExcelDate(javaDate, false), oneMinute); } } @@ -156,11 +160,11 @@ public class TestHSSFDateUtil public void testExcelConversionOnDSTEnd() { TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen"); TimeZone.setDefault(cet); - Calendar cal = new GregorianCalendar(2004, Calendar.OCTOBER, 31); + Calendar cal = new GregorianCalendar(2004, CALENDAR_OCTOBER, 31); for (int hour = 0; hour < 24; hour++) { cal.set(Calendar.HOUR_OF_DAY, hour); Date javaDate = cal.getTime(); - double excelDate = HSSFDateUtil.getExcelDate(javaDate); + double excelDate = HSSFDateUtil.getExcelDate(javaDate, false); double difference = excelDate - Math.floor(excelDate); int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60; assertEquals("Checking " + hour + " hour on Daylight Saving Time end date", @@ -168,7 +172,7 @@ public class TestHSSFDateUtil differenceInHours); assertEquals("Checking " + hour + " hour on Daylight Saving Time start date", javaDate.getTime(), - HSSFDateUtil.getJavaDate(excelDate).getTime()); + HSSFDateUtil.getJavaDate(excelDate, false).getTime()); } } @@ -179,16 +183,16 @@ public class TestHSSFDateUtil public void testJavaConversionOnDSTEnd() { TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen"); TimeZone.setDefault(cet); - Calendar cal = new GregorianCalendar(2004, Calendar.OCTOBER, 31); - double excelDate = HSSFDateUtil.getExcelDate(cal.getTime()); + Calendar cal = new GregorianCalendar(2004, CALENDAR_OCTOBER, 31); + double excelDate = HSSFDateUtil.getExcelDate(cal.getTime(), false); double oneHour = 1.0 / 24; double oneMinute = oneHour / 60; for (int hour = 0; hour < 24; hour++, excelDate += oneHour) { cal.set(Calendar.HOUR_OF_DAY, hour); - Date javaDate = HSSFDateUtil.getJavaDate(excelDate); + Date javaDate = HSSFDateUtil.getJavaDate(excelDate, false); assertEquals("Checking " + hour + " hours on Daylight Saving Time start date", excelDate, - HSSFDateUtil.getExcelDate(javaDate), oneMinute); + HSSFDateUtil.getExcelDate(javaDate, false), oneMinute); } } @@ -315,25 +319,38 @@ public class TestHSSFDateUtil } public void testDateBug_2Excel() { - assertEquals(59.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_FEBRUARY, 28)), 0.00001); - assertEquals(61.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_MARCH, 1)), 0.00001); + assertEquals(59.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_FEBRUARY, 28), false), 0.00001); + assertEquals(61.0, HSSFDateUtil.getExcelDate(createDate(1900, CALENDAR_MARCH, 1), false), 0.00001); - assertEquals(37315.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_FEBRUARY, 28)), 0.00001); - assertEquals(37316.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_MARCH, 1)), 0.00001); - assertEquals(37257.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_JANUARY, 1)), 0.00001); - assertEquals(38074.00, HSSFDateUtil.getExcelDate(createDate(2004, CALENDAR_MARCH, 28)), 0.00001); + assertEquals(37315.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_FEBRUARY, 28), false), 0.00001); + assertEquals(37316.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_MARCH, 1), false), 0.00001); + assertEquals(37257.00, HSSFDateUtil.getExcelDate(createDate(2002, CALENDAR_JANUARY, 1), false), 0.00001); + assertEquals(38074.00, HSSFDateUtil.getExcelDate(createDate(2004, CALENDAR_MARCH, 28), false), 0.00001); } public void testDateBug_2Java() { - assertEquals(createDate(1900, Calendar.FEBRUARY, 28), HSSFDateUtil.getJavaDate(59.0)); - assertEquals(createDate(1900, Calendar.MARCH, 1), HSSFDateUtil.getJavaDate(61.0)); + assertEquals(createDate(1900, CALENDAR_FEBRUARY, 28), HSSFDateUtil.getJavaDate(59.0, false)); + assertEquals(createDate(1900, CALENDAR_MARCH, 1), HSSFDateUtil.getJavaDate(61.0, false)); - assertEquals(createDate(2002, Calendar.FEBRUARY, 28), HSSFDateUtil.getJavaDate(37315.00)); - assertEquals(createDate(2002, Calendar.MARCH, 1), HSSFDateUtil.getJavaDate(37316.00)); - assertEquals(createDate(2002, Calendar.JANUARY, 1), HSSFDateUtil.getJavaDate(37257.00)); - assertEquals(createDate(2004, Calendar.MARCH, 28), HSSFDateUtil.getJavaDate(38074.00)); + assertEquals(createDate(2002, CALENDAR_FEBRUARY, 28), HSSFDateUtil.getJavaDate(37315.00, false)); + assertEquals(createDate(2002, CALENDAR_MARCH, 1), HSSFDateUtil.getJavaDate(37316.00, false)); + assertEquals(createDate(2002, CALENDAR_JANUARY, 1), HSSFDateUtil.getJavaDate(37257.00, false)); + assertEquals(createDate(2004, CALENDAR_MARCH, 28), HSSFDateUtil.getJavaDate(38074.00, false)); } - + + public void testDate1904() { + assertEquals(createDate(1904, CALENDAR_JANUARY, 2), HSSFDateUtil.getJavaDate(1.0, true)); + assertEquals(createDate(1904, CALENDAR_JANUARY, 1), HSSFDateUtil.getJavaDate(0.0, true)); + assertEquals(0.0, HSSFDateUtil.getExcelDate(createDate(1904, CALENDAR_JANUARY, 1), true), 0.00001); + assertEquals(1.0, HSSFDateUtil.getExcelDate(createDate(1904, CALENDAR_JANUARY, 2), true), 0.00001); + + assertEquals(createDate(1998, CALENDAR_JULY, 5), HSSFDateUtil.getJavaDate(35981, false)); + assertEquals(createDate(1998, CALENDAR_JULY, 5), HSSFDateUtil.getJavaDate(34519, true)); + + assertEquals(35981.0, HSSFDateUtil.getExcelDate(createDate(1998, CALENDAR_JULY, 5), false), 0.00001); + assertEquals(34519.0, HSSFDateUtil.getExcelDate(createDate(1998, CALENDAR_JULY, 5), true), 0.00001); + } + private Date createDate(int year, int month, int day) { Calendar c = new GregorianCalendar(); c.set(year, month, day, 0, 0, 0); @@ -341,10 +358,21 @@ public class TestHSSFDateUtil return c.getTime(); } + /** + * Check if HSSFDateUtil.getAbsoluteDay works as advertised. + */ + public void testAbsoluteDay() { + // 1 Jan 1900 is 1 day after 31 Dec 1899 + GregorianCalendar calendar = new GregorianCalendar(1900, 0, 1); + assertEquals("Checking absolute day (1 Jan 1900)", 1, HSSFDateUtil.absoluteDay(calendar, false)); + // 1 Jan 1901 is 366 days after 31 Dec 1899 + calendar = new GregorianCalendar(1901, 0, 1); + assertEquals("Checking absolute day (1 Jan 1901)", 366, HSSFDateUtil.absoluteDay(calendar, false)); + } + public static void main(String [] args) { System.out .println("Testing org.apache.poi.hssf.usermodel.TestHSSFDateUtil"); junit.textui.TestRunner.run(TestHSSFDateUtil.class); } } - diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFPalette.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFPalette.java index 8504b8dd7e..c5674b9e76 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFPalette.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFPalette.java @@ -96,6 +96,71 @@ public class TestHSSFPalette extends TestCase } /** + * Uses the palette from cell stylings + */ + public void testPaletteFromCellColours() throws Exception { + String dir = System.getProperty("HSSF.testdata.path"); + File sample = new File(dir + "/SimpleWithColours.xls"); + assertTrue("SimpleWithColours.xls exists and is readable", sample.canRead()); + FileInputStream fis = new FileInputStream(sample); + HSSFWorkbook book = new HSSFWorkbook(fis); + fis.close(); + + HSSFPalette p = book.getCustomPalette(); + + HSSFCell cellA = book.getSheetAt(0).getRow(0).getCell((short)0); + HSSFCell cellB = book.getSheetAt(0).getRow(1).getCell((short)0); + HSSFCell cellC = book.getSheetAt(0).getRow(2).getCell((short)0); + HSSFCell cellD = book.getSheetAt(0).getRow(3).getCell((short)0); + HSSFCell cellE = book.getSheetAt(0).getRow(4).getCell((short)0); + + // Plain + assertEquals("I'm plain", cellA.getStringCellValue()); + assertEquals(64, cellA.getCellStyle().getFillForegroundColor()); + assertEquals(64, cellA.getCellStyle().getFillBackgroundColor()); + assertEquals(HSSFFont.COLOR_NORMAL, cellA.getCellStyle().getFont(book).getColor()); + assertEquals(0, cellA.getCellStyle().getFillPattern()); + assertEquals("0:0:0", p.getColor((short)64).getHexString()); + assertEquals(null, p.getColor((short)32767)); + + // Red + assertEquals("I'm red", cellB.getStringCellValue()); + assertEquals(64, cellB.getCellStyle().getFillForegroundColor()); + assertEquals(64, cellB.getCellStyle().getFillBackgroundColor()); + assertEquals(10, cellB.getCellStyle().getFont(book).getColor()); + assertEquals(0, cellB.getCellStyle().getFillPattern()); + assertEquals("0:0:0", p.getColor((short)64).getHexString()); + assertEquals("FFFF:0:0", p.getColor((short)10).getHexString()); + + // Red + green bg + assertEquals("I'm red with a green bg", cellC.getStringCellValue()); + assertEquals(11, cellC.getCellStyle().getFillForegroundColor()); + assertEquals(64, cellC.getCellStyle().getFillBackgroundColor()); + assertEquals(10, cellC.getCellStyle().getFont(book).getColor()); + assertEquals(1, cellC.getCellStyle().getFillPattern()); + assertEquals("0:FFFF:0", p.getColor((short)11).getHexString()); + assertEquals("FFFF:0:0", p.getColor((short)10).getHexString()); + + // Pink with yellow + assertEquals("I'm pink with a yellow pattern (none)", cellD.getStringCellValue()); + assertEquals(13, cellD.getCellStyle().getFillForegroundColor()); + assertEquals(64, cellD.getCellStyle().getFillBackgroundColor()); + assertEquals(14, cellD.getCellStyle().getFont(book).getColor()); + assertEquals(0, cellD.getCellStyle().getFillPattern()); + assertEquals("FFFF:FFFF:0", p.getColor((short)13).getHexString()); + assertEquals("FFFF:0:FFFF", p.getColor((short)14).getHexString()); + + // Pink with yellow - full + assertEquals("I'm pink with a yellow pattern (full)", cellE.getStringCellValue()); + assertEquals(13, cellE.getCellStyle().getFillForegroundColor()); + assertEquals(64, cellE.getCellStyle().getFillBackgroundColor()); + assertEquals(14, cellE.getCellStyle().getFont(book).getColor()); + assertEquals(0, cellE.getCellStyle().getFillPattern()); + assertEquals("FFFF:FFFF:0", p.getColor((short)13).getHexString()); + assertEquals("FFFF:0:FFFF", p.getColor((short)14).getHexString()); + } + + /** * Verifies that the generated gnumeric-format string values match the * hardcoded values in the HSSFColor default color palette */ diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestReadWriteChart.java b/src/testcases/org/apache/poi/hssf/usermodel/TestReadWriteChart.java index f53b2f88da..7f2819433b 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestReadWriteChart.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestReadWriteChart.java @@ -63,7 +63,7 @@ public class TestReadWriteChart //System.out.println("first assertion for date"); assertEquals(new GregorianCalendar(2000, 0, 1, 10, 51, 2).getTime(), HSSFDateUtil - .getJavaDate(firstCell.getNumericCellValue())); + .getJavaDate(firstCell.getNumericCellValue(), false)); HSSFRow row = sheet.createRow(( short ) 15); HSSFCell cell = row.createCell(( short ) 1); diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestSheetHiding.java b/src/testcases/org/apache/poi/hssf/usermodel/TestSheetHiding.java new file mode 100644 index 0000000000..9628ab23e4 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestSheetHiding.java @@ -0,0 +1,105 @@ +/* +* 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 java.io.ByteArrayInputStream; +import java.io.ByteArrayOutputStream; +import java.io.FileInputStream; +import java.io.FileNotFoundException; + +import org.apache.poi.poifs.filesystem.POIFSFileSystem; + +import junit.framework.TestCase; + +/** + * Tests for how HSSFWorkbook behaves with XLS files + * with a WORKBOOK directory entry (instead of the more + * usual, Workbook) + */ +public class TestSheetHiding extends TestCase { + private String dirPath; + private String xlsHidden = "TwoSheetsOneHidden.xls"; + private String xlsShown = "TwoSheetsNoneHidden.xls"; + + protected void setUp() throws Exception { + super.setUp(); + + dirPath = System.getProperty("HSSF.testdata.path"); + } + + /** + * Test that we get the right number of sheets, + * with the right text on them, no matter what + * the hidden flags are + */ + public void testTextSheets() throws Exception { + FileInputStream isH = new FileInputStream(dirPath + "/" + xlsHidden); + POIFSFileSystem fsH = new POIFSFileSystem(isH); + + FileInputStream isU = new FileInputStream(dirPath + "/" + xlsShown); + POIFSFileSystem fsU = new POIFSFileSystem(isU); + + HSSFWorkbook wbH = new HSSFWorkbook(fsH); + HSSFWorkbook wbU = new HSSFWorkbook(fsU); + + // Both should have two sheets + assertEquals(2, wbH.sheets.size()); + assertEquals(2, wbU.sheets.size()); + + // All sheets should have one row + assertEquals(0, wbH.getSheetAt(0).getLastRowNum()); + assertEquals(0, wbH.getSheetAt(1).getLastRowNum()); + assertEquals(0, wbU.getSheetAt(0).getLastRowNum()); + assertEquals(0, wbU.getSheetAt(1).getLastRowNum()); + + // All rows should have one column + assertEquals(1, wbH.getSheetAt(0).getRow(0).getLastCellNum()); + assertEquals(1, wbH.getSheetAt(1).getRow(0).getLastCellNum()); + assertEquals(1, wbU.getSheetAt(0).getRow(0).getLastCellNum()); + assertEquals(1, wbU.getSheetAt(1).getRow(0).getLastCellNum()); + + // Text should be sheet based + assertEquals("Sheet1A1", wbH.getSheetAt(0).getRow(0).getCell((short)0).getStringCellValue()); + assertEquals("Sheet2A1", wbH.getSheetAt(1).getRow(0).getCell((short)0).getStringCellValue()); + assertEquals("Sheet1A1", wbU.getSheetAt(0).getRow(0).getCell((short)0).getStringCellValue()); + assertEquals("Sheet2A1", wbU.getSheetAt(1).getRow(0).getCell((short)0).getStringCellValue()); + } + + /** + * Check that we can get and set the hidden flags + * as expected + */ + public void testHideUnHideFlags() throws Exception { + // TODO + } + + /** + * Turn the sheet with none hidden into the one with + * one hidden + */ + public void testHide() throws Exception { + // TODO + } + + /** + * Turn the sheet with one hidden into the one with + * none hidden + */ + public void testUnHide() throws Exception { + // TODO + } +} diff --git a/src/testcases/org/apache/poi/poifs/filesystem/TestEmptyDocument.java b/src/testcases/org/apache/poi/poifs/filesystem/TestEmptyDocument.java index a56a1d2a3a..4f67f98767 100644 --- a/src/testcases/org/apache/poi/poifs/filesystem/TestEmptyDocument.java +++ b/src/testcases/org/apache/poi/poifs/filesystem/TestEmptyDocument.java @@ -23,6 +23,7 @@ import java.io.ByteArrayOutputStream; import junit.framework.TestCase; +import org.apache.poi.util.IOUtils; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.poifs.filesystem.POIFSWriterEvent; import org.apache.poi.poifs.filesystem.POIFSWriterListener; @@ -140,4 +141,28 @@ public class TestEmptyDocument extends TestCase { fs.writeFilesystem(out); new POIFSFileSystem(new ByteArrayInputStream(out.toByteArray())); } + + public void testEmptyDocumentBug11744() throws Exception { + byte[] testData = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 }; + + POIFSFileSystem fs = new POIFSFileSystem(); + fs.createDocument(new ByteArrayInputStream(new byte[0]), "Empty"); + fs.createDocument(new ByteArrayInputStream(testData), "NotEmpty"); + ByteArrayOutputStream out = new ByteArrayOutputStream(); + fs.writeFilesystem(out); + out.toByteArray(); + + // This line caused the error. + fs = new POIFSFileSystem(new ByteArrayInputStream(out.toByteArray())); + + DocumentEntry entry = (DocumentEntry) fs.getRoot().getEntry("Empty"); + assertEquals("Expected zero size", 0, entry.getSize()); + assertEquals("Expected zero read from stream", 0, + IOUtils.toByteArray(new DocumentInputStream(entry)).length); + + entry = (DocumentEntry) fs.getRoot().getEntry("NotEmpty"); + assertEquals("Expected size was wrong", testData.length, entry.getSize()); + assertEquals("Expected different data read from stream", testData, + IOUtils.toByteArray(new DocumentInputStream(entry))); + } } |