You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by jo...@apache.org on 2009/05/18 21:11:47 UTC
svn commit: r776056 [3/4] - in /poi/trunk/src/testcases/org/apache/poi: ddf/
hssf/ hssf/model/ hssf/record/ hssf/record/chart/ hssf/record/formula/eval/
hssf/usermodel/ ss/ ss/formula/ ss/usermodel/ ss/util/ util/
Modified: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java?rev=776056&r1=776055&r2=776056&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java Mon May 18 19:11:45 2009
@@ -1,336 +1,337 @@
-/* ====================================================================
- 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 junit.framework.TestCase;
-import org.apache.poi.ss.ITestDataProvider;
-import org.apache.poi.ss.SpreadsheetVersion;
-import org.apache.poi.ss.util.CellRangeAddress;
-
-/**
- * A base class for bugzilla issues that can be described in terms of common ss interfaces.
- *
- * @author Yegor Kozlov
- */
-public abstract class BaseTestBugzillaIssues extends TestCase {
-
- protected abstract ITestDataProvider getTestDataProvider();
-
- /**
- *
- * Test writing a hyperlink
- * Open resulting sheet in Excel and check that A1 contains a hyperlink
- *
- * Also tests bug 15353 (problems with hyperlinks to Google)
- */
- public void test23094() {
- Workbook wb = getTestDataProvider().createWorkbook();
- Sheet s = wb.createSheet();
- Row r = s.createRow(0);
- r.createCell(0).setCellFormula("HYPERLINK(\"http://jakarta.apache.org\",\"Jakarta\")");
- r.createCell(1).setCellFormula("HYPERLINK(\"http://google.com\",\"Google\")");
-
- wb = getTestDataProvider().writeOutAndReadBack(wb);
- r = wb.getSheetAt(0).getRow(0);
-
- Cell cell_0 = r.getCell(0);
- assertEquals("HYPERLINK(\"http://jakarta.apache.org\",\"Jakarta\")", cell_0.getCellFormula());
- Cell cell_1 = r.getCell(1);
- assertEquals("HYPERLINK(\"http://google.com\",\"Google\")", cell_1.getCellFormula());
- }
-
- /**
- * test writing a file with large number of unique strings,
- * open resulting file in Excel to check results!
- * @param num the number of strings to generate
- */
- public void baseTest15375(int num) {
- Workbook wb = getTestDataProvider().createWorkbook();
- Sheet sheet = wb.createSheet();
- CreationHelper factory = wb.getCreationHelper();
-
- String tmp1 = null;
- String tmp2 = null;
- String tmp3 = null;
-
- for (int i = 0; i < num; i++) {
- tmp1 = "Test1" + i;
- tmp2 = "Test2" + i;
- tmp3 = "Test3" + i;
-
- Row row = sheet.createRow(i);
-
- Cell cell = row.createCell(0);
- cell.setCellValue(factory.createRichTextString(tmp1));
- cell = row.createCell(1);
- cell.setCellValue(factory.createRichTextString(tmp2));
- cell = row.createCell(2);
- cell.setCellValue(factory.createRichTextString(tmp3));
- }
- wb = getTestDataProvider().writeOutAndReadBack(wb);
- for (int i = 0; i < num; i++) {
- tmp1 = "Test1" + i;
- tmp2 = "Test2" + i;
- tmp3 = "Test3" + i;
-
- Row row = sheet.getRow(i);
-
- assertEquals(tmp1, row.getCell(0).getStringCellValue());
- assertEquals(tmp2, row.getCell(1).getStringCellValue());
- assertEquals(tmp3, row.getCell(2).getStringCellValue());
- }
- }
-
- /**
- * Merged regions were being removed from the parent in cloned sheets
- */
- public void test22720() {
- Workbook workBook = getTestDataProvider().createWorkbook();
- workBook.createSheet("TEST");
- Sheet template = workBook.getSheetAt(0);
-
- template.addMergedRegion(new CellRangeAddress(0, 1, 0, 2));
- template.addMergedRegion(new CellRangeAddress(1, 2, 0, 2));
-
- Sheet clone = workBook.cloneSheet(0);
- int originalMerged = template.getNumMergedRegions();
- assertEquals("2 merged regions", 2, originalMerged);
-
- //remove merged regions from clone
- for (int i=template.getNumMergedRegions()-1; i>=0; i--) {
- clone.removeMergedRegion(i);
- }
-
- assertEquals("Original Sheet's Merged Regions were removed", originalMerged, template.getNumMergedRegions());
- //check if template's merged regions are OK
- if (template.getNumMergedRegions()>0) {
- // fetch the first merged region...EXCEPTION OCCURS HERE
- template.getMergedRegion(0);
- }
- //make sure we dont exception
-
- }
-
- public void test28031() {
- Workbook wb = getTestDataProvider().createWorkbook();
- Sheet sheet = wb.createSheet();
- wb.setSheetName(0, "Sheet1");
-
- Row row = sheet.createRow(0);
- Cell cell = row.createCell(0);
- String formulaText =
- "IF(ROUND(A2*B2*C2,2)>ROUND(B2*D2,2),ROUND(A2*B2*C2,2),ROUND(B2*D2,2))";
- cell.setCellFormula(formulaText);
-
- assertEquals(formulaText, cell.getCellFormula());
- wb = getTestDataProvider().writeOutAndReadBack(wb);
- cell = wb.getSheetAt(0).getRow(0).getCell(0);
- assertEquals("IF(ROUND(A2*B2*C2,2)>ROUND(B2*D2,2),ROUND(A2*B2*C2,2),ROUND(B2*D2,2))", cell.getCellFormula());
- }
-
- /**
- * Bug 21334: "File error: data may have been lost" with a file
- * that contains macros and this formula:
- * {=SUM(IF(FREQUENCY(IF(LEN(V4:V220)>0,MATCH(V4:V220,V4:V220,0),""),IF(LEN(V4:V220)>0,MATCH(V4:V220,V4:V220,0),""))>0,1))}
- */
- public void test21334() {
- Workbook wb = getTestDataProvider().createWorkbook();
- Sheet sh = wb.createSheet();
- Cell cell = sh.createRow(0).createCell(0);
- String formula = "SUM(IF(FREQUENCY(IF(LEN(V4:V220)>0,MATCH(V4:V220,V4:V220,0),\"\"),IF(LEN(V4:V220)>0,MATCH(V4:V220,V4:V220,0),\"\"))>0,1))";
- cell.setCellFormula(formula);
-
- Workbook wb_sv = getTestDataProvider().writeOutAndReadBack(wb);
- Cell cell_sv = wb_sv.getSheetAt(0).getRow(0).getCell(0);
- assertEquals(formula, cell_sv.getCellFormula());
- }
-
- /** another test for the number of unique strings issue
- *test opening the resulting file in Excel*/
- public void test22568() {
- int r=2000;int c=3;
-
- Workbook wb = getTestDataProvider().createWorkbook();
- Sheet sheet = wb.createSheet("ExcelTest") ;
-
- int col_cnt=0, rw_cnt=0 ;
-
- col_cnt = c;
- rw_cnt = r;
-
- Row rw ;
- rw = sheet.createRow(0) ;
- //Header row
- for(int j=0; j<col_cnt; j++){
- Cell cell = rw.createCell(j) ;
- cell.setCellValue("Col " + (j+1));
- }
-
- for(int i=1; i<rw_cnt; i++){
- rw = sheet.createRow(i) ;
- for(int j=0; j<col_cnt; j++){
- Cell cell = rw.createCell(j) ;
- cell.setCellValue("Row:" + (i+1) + ",Column:" + (j+1));
- }
- }
-
- sheet.setDefaultColumnWidth(18) ;
-
- wb = getTestDataProvider().writeOutAndReadBack(wb);
- sheet = wb.getSheetAt(0);
- rw = sheet.getRow(0);
- //Header row
- for(int j=0; j<col_cnt; j++){
- Cell cell = rw.getCell(j) ;
- assertEquals("Col " + (j+1), cell.getStringCellValue());
- }
- for(int i=1; i<rw_cnt; i++){
- rw = sheet.getRow(i) ;
- for(int j=0; j<col_cnt; j++){
- Cell cell = rw.getCell(j) ;
- assertEquals("Row:" + (i+1) + ",Column:" + (j+1), cell.getStringCellValue());
- }
- }
- }
-
- /**
- * Bug 42448: Can't parse SUMPRODUCT(A!C7:A!C67, B8:B68) / B69
- */
- public void test42448(){
- Workbook wb = getTestDataProvider().createWorkbook();
- Cell cell = wb.createSheet().createRow(0).createCell(0);
- cell.setCellFormula("SUMPRODUCT(A!C7:A!C67, B8:B68) / B69");
- assertTrue("no errors parsing formula", true);
- }
-
- /**
- * HSSFRichTextString.length() returns negative for really
- * long strings
- */
- public void test46368() {
- Workbook wb = getTestDataProvider().createWorkbook();
- Sheet s = wb.createSheet();
- Row r = s.createRow(0);
- for(int i=0; i<15; i++) {
- int len = 32760 + i;
- Cell c = r.createCell(i);
-
- StringBuffer sb = new StringBuffer();
- for(int j=0; j<len; j++) {
- sb.append("x");
- }
- RichTextString rtr = wb.getCreationHelper().createRichTextString(sb.toString());
- assertEquals(len, rtr.length());
- c.setCellValue(rtr);
- }
-
- // Save and reload
- wb = getTestDataProvider().writeOutAndReadBack(wb);
- s = wb.getSheetAt(0);
- r = s.getRow(0);
- for(int i=0; i<15; i++) {
- int len = 32760 + i;
- Cell c = r.getCell(i);
- assertEquals(len, c.getRichStringCellValue().length());
- }
- }
-
- public void test18800() {
- Workbook book = getTestDataProvider().createWorkbook();
- book.createSheet("TEST");
- Sheet sheet = book.cloneSheet(0);
- book.setSheetName(1,"CLONE");
- sheet.createRow(0).createCell(0).setCellValue("Test");
-
- book = getTestDataProvider().writeOutAndReadBack(book);
- sheet = book.getSheet("CLONE");
- Row row = sheet.getRow(0);
- Cell cell = row.getCell(0);
- assertEquals("Test", cell.getRichStringCellValue().getString());
- }
-
- private static void addNewSheetWithCellsA1toD4(Workbook book, int sheet) {
-
- Sheet sht = book .createSheet("s" + sheet);
- for (int r=0; r < 4; r++) {
-
- Row row = sht.createRow (r);
- for (int c=0; c < 4; c++) {
-
- Cell cel = row.createCell(c);
- cel.setCellValue(sheet*100 + r*10 + c);
- }
- }
- }
-
- public void testBug43093() {
- Workbook xlw = getTestDataProvider().createWorkbook();
-
- addNewSheetWithCellsA1toD4(xlw, 1);
- addNewSheetWithCellsA1toD4(xlw, 2);
- addNewSheetWithCellsA1toD4(xlw, 3);
- addNewSheetWithCellsA1toD4(xlw, 4);
-
- Sheet s2 = xlw.getSheet("s2");
- Row s2r3 = s2.getRow(3);
- Cell s2E4 = s2r3.createCell(4);
- s2E4.setCellFormula("SUM(s3!B2:C3)");
-
- FormulaEvaluator eva = xlw.getCreationHelper().createFormulaEvaluator();
- double d = eva.evaluate(s2E4).getNumberValue();
-
- assertEquals(d, (311+312+321+322), 0.0000001);
- }
-
- public void testMaxFunctionArguments_bug46729(){
- String[] func = {"COUNT", "AVERAGE", "MAX", "MIN", "OR", "SUBTOTAL", "SKEW"};
-
- SpreadsheetVersion ssVersion = getTestDataProvider().getSpreadsheetVersion();
- Workbook wb = getTestDataProvider().createWorkbook();
- Cell cell = wb.createSheet().createRow(0).createCell(0);
-
- String fmla;
- for (String name : func) {
-
- fmla = createFunction(name, 5);
- cell.setCellFormula(fmla);
-
- fmla = createFunction(name, ssVersion.getMaxFunctionArgs());
- cell.setCellFormula(fmla);
-
- try {
- fmla = createFunction(name, ssVersion.getMaxFunctionArgs() + 1);
- cell.setCellFormula(fmla);
- fail("Expected FormulaParseException");
- } catch (RuntimeException e){
- assertTrue(e.getMessage().startsWith("Too many arguments to function '"+name+"'"));
- }
- }
- }
-
- private String createFunction(String name, int maxArgs){
- StringBuffer fmla = new StringBuffer();
- fmla.append(name);
- fmla.append("(");
- for(int i=0; i < maxArgs; i++){
- if(i > 0) fmla.append(',');
- fmla.append("A1");
- }
- fmla.append(")");
- return fmla.toString();
- }
-}
\ No newline at end of file
+/* ====================================================================
+ 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 junit.framework.TestCase;
+import org.apache.poi.ss.ITestDataProvider;
+import org.apache.poi.ss.SpreadsheetVersion;
+import org.apache.poi.ss.util.CellRangeAddress;
+
+/**
+ * A base class for bugzilla issues that can be described in terms of common ss interfaces.
+ *
+ * @author Yegor Kozlov
+ */
+public abstract class BaseTestBugzillaIssues extends TestCase {
+
+ protected abstract ITestDataProvider getTestDataProvider();
+
+ /**
+ *
+ * Test writing a hyperlink
+ * Open resulting sheet in Excel and check that A1 contains a hyperlink
+ *
+ * Also tests bug 15353 (problems with hyperlinks to Google)
+ */
+ public void test23094() {
+ Workbook wb = getTestDataProvider().createWorkbook();
+ Sheet s = wb.createSheet();
+ Row r = s.createRow(0);
+ r.createCell(0).setCellFormula("HYPERLINK(\"http://jakarta.apache.org\",\"Jakarta\")");
+ r.createCell(1).setCellFormula("HYPERLINK(\"http://google.com\",\"Google\")");
+
+ wb = getTestDataProvider().writeOutAndReadBack(wb);
+ r = wb.getSheetAt(0).getRow(0);
+
+ Cell cell_0 = r.getCell(0);
+ assertEquals("HYPERLINK(\"http://jakarta.apache.org\",\"Jakarta\")", cell_0.getCellFormula());
+ Cell cell_1 = r.getCell(1);
+ assertEquals("HYPERLINK(\"http://google.com\",\"Google\")", cell_1.getCellFormula());
+ }
+
+ /**
+ * test writing a file with large number of unique strings,
+ * open resulting file in Excel to check results!
+ * @param num the number of strings to generate
+ */
+ public void baseTest15375(int num) {
+ Workbook wb = getTestDataProvider().createWorkbook();
+ Sheet sheet = wb.createSheet();
+ CreationHelper factory = wb.getCreationHelper();
+
+ String tmp1 = null;
+ String tmp2 = null;
+ String tmp3 = null;
+
+ for (int i = 0; i < num; i++) {
+ tmp1 = "Test1" + i;
+ tmp2 = "Test2" + i;
+ tmp3 = "Test3" + i;
+
+ Row row = sheet.createRow(i);
+
+ Cell cell = row.createCell(0);
+ cell.setCellValue(factory.createRichTextString(tmp1));
+ cell = row.createCell(1);
+ cell.setCellValue(factory.createRichTextString(tmp2));
+ cell = row.createCell(2);
+ cell.setCellValue(factory.createRichTextString(tmp3));
+ }
+ wb = getTestDataProvider().writeOutAndReadBack(wb);
+ for (int i = 0; i < num; i++) {
+ tmp1 = "Test1" + i;
+ tmp2 = "Test2" + i;
+ tmp3 = "Test3" + i;
+
+ Row row = sheet.getRow(i);
+
+ assertEquals(tmp1, row.getCell(0).getStringCellValue());
+ assertEquals(tmp2, row.getCell(1).getStringCellValue());
+ assertEquals(tmp3, row.getCell(2).getStringCellValue());
+ }
+ }
+
+ /**
+ * Merged regions were being removed from the parent in cloned sheets
+ */
+ public void test22720() {
+ Workbook workBook = getTestDataProvider().createWorkbook();
+ workBook.createSheet("TEST");
+ Sheet template = workBook.getSheetAt(0);
+
+ template.addMergedRegion(new CellRangeAddress(0, 1, 0, 2));
+ template.addMergedRegion(new CellRangeAddress(1, 2, 0, 2));
+
+ Sheet clone = workBook.cloneSheet(0);
+ int originalMerged = template.getNumMergedRegions();
+ assertEquals("2 merged regions", 2, originalMerged);
+
+ //remove merged regions from clone
+ for (int i=template.getNumMergedRegions()-1; i>=0; i--) {
+ clone.removeMergedRegion(i);
+ }
+
+ assertEquals("Original Sheet's Merged Regions were removed", originalMerged, template.getNumMergedRegions());
+ //check if template's merged regions are OK
+ if (template.getNumMergedRegions()>0) {
+ // fetch the first merged region...EXCEPTION OCCURS HERE
+ template.getMergedRegion(0);
+ }
+ //make sure we dont exception
+
+ }
+
+ public void test28031() {
+ Workbook wb = getTestDataProvider().createWorkbook();
+ Sheet sheet = wb.createSheet();
+ wb.setSheetName(0, "Sheet1");
+
+ Row row = sheet.createRow(0);
+ Cell cell = row.createCell(0);
+ String formulaText =
+ "IF(ROUND(A2*B2*C2,2)>ROUND(B2*D2,2),ROUND(A2*B2*C2,2),ROUND(B2*D2,2))";
+ cell.setCellFormula(formulaText);
+
+ assertEquals(formulaText, cell.getCellFormula());
+ wb = getTestDataProvider().writeOutAndReadBack(wb);
+ cell = wb.getSheetAt(0).getRow(0).getCell(0);
+ assertEquals("IF(ROUND(A2*B2*C2,2)>ROUND(B2*D2,2),ROUND(A2*B2*C2,2),ROUND(B2*D2,2))", cell.getCellFormula());
+ }
+
+ /**
+ * Bug 21334: "File error: data may have been lost" with a file
+ * that contains macros and this formula:
+ * {=SUM(IF(FREQUENCY(IF(LEN(V4:V220)>0,MATCH(V4:V220,V4:V220,0),""),IF(LEN(V4:V220)>0,MATCH(V4:V220,V4:V220,0),""))>0,1))}
+ */
+ public void test21334() {
+ Workbook wb = getTestDataProvider().createWorkbook();
+ Sheet sh = wb.createSheet();
+ Cell cell = sh.createRow(0).createCell(0);
+ String formula = "SUM(IF(FREQUENCY(IF(LEN(V4:V220)>0,MATCH(V4:V220,V4:V220,0),\"\"),IF(LEN(V4:V220)>0,MATCH(V4:V220,V4:V220,0),\"\"))>0,1))";
+ cell.setCellFormula(formula);
+
+ Workbook wb_sv = getTestDataProvider().writeOutAndReadBack(wb);
+ Cell cell_sv = wb_sv.getSheetAt(0).getRow(0).getCell(0);
+ assertEquals(formula, cell_sv.getCellFormula());
+ }
+
+ /** another test for the number of unique strings issue
+ *test opening the resulting file in Excel*/
+ public void test22568() {
+ int r=2000;int c=3;
+
+ Workbook wb = getTestDataProvider().createWorkbook();
+ Sheet sheet = wb.createSheet("ExcelTest") ;
+
+ int col_cnt=0, rw_cnt=0 ;
+
+ col_cnt = c;
+ rw_cnt = r;
+
+ Row rw ;
+ rw = sheet.createRow(0) ;
+ //Header row
+ for(int j=0; j<col_cnt; j++){
+ Cell cell = rw.createCell(j) ;
+ cell.setCellValue("Col " + (j+1));
+ }
+
+ for(int i=1; i<rw_cnt; i++){
+ rw = sheet.createRow(i) ;
+ for(int j=0; j<col_cnt; j++){
+ Cell cell = rw.createCell(j) ;
+ cell.setCellValue("Row:" + (i+1) + ",Column:" + (j+1));
+ }
+ }
+
+ sheet.setDefaultColumnWidth(18) ;
+
+ wb = getTestDataProvider().writeOutAndReadBack(wb);
+ sheet = wb.getSheetAt(0);
+ rw = sheet.getRow(0);
+ //Header row
+ for(int j=0; j<col_cnt; j++){
+ Cell cell = rw.getCell(j) ;
+ assertEquals("Col " + (j+1), cell.getStringCellValue());
+ }
+ for(int i=1; i<rw_cnt; i++){
+ rw = sheet.getRow(i) ;
+ for(int j=0; j<col_cnt; j++){
+ Cell cell = rw.getCell(j) ;
+ assertEquals("Row:" + (i+1) + ",Column:" + (j+1), cell.getStringCellValue());
+ }
+ }
+ }
+
+ /**
+ * Bug 42448: Can't parse SUMPRODUCT(A!C7:A!C67, B8:B68) / B69
+ */
+ public void test42448(){
+ Workbook wb = getTestDataProvider().createWorkbook();
+ Cell cell = wb.createSheet().createRow(0).createCell(0);
+ cell.setCellFormula("SUMPRODUCT(A!C7:A!C67, B8:B68) / B69");
+ assertTrue("no errors parsing formula", true);
+ }
+
+ /**
+ * HSSFRichTextString.length() returns negative for really
+ * long strings
+ */
+ public void test46368() {
+ Workbook wb = getTestDataProvider().createWorkbook();
+ Sheet s = wb.createSheet();
+ Row r = s.createRow(0);
+ for(int i=0; i<15; i++) {
+ int len = 32760 + i;
+ Cell c = r.createCell(i);
+
+ StringBuffer sb = new StringBuffer();
+ for(int j=0; j<len; j++) {
+ sb.append("x");
+ }
+ RichTextString rtr = wb.getCreationHelper().createRichTextString(sb.toString());
+ assertEquals(len, rtr.length());
+ c.setCellValue(rtr);
+ }
+
+ // Save and reload
+ wb = getTestDataProvider().writeOutAndReadBack(wb);
+ s = wb.getSheetAt(0);
+ r = s.getRow(0);
+ for(int i=0; i<15; i++) {
+ int len = 32760 + i;
+ Cell c = r.getCell(i);
+ assertEquals(len, c.getRichStringCellValue().length());
+ }
+ }
+
+ public void test18800() {
+ Workbook book = getTestDataProvider().createWorkbook();
+ book.createSheet("TEST");
+ Sheet sheet = book.cloneSheet(0);
+ book.setSheetName(1,"CLONE");
+ sheet.createRow(0).createCell(0).setCellValue("Test");
+
+ book = getTestDataProvider().writeOutAndReadBack(book);
+ sheet = book.getSheet("CLONE");
+ Row row = sheet.getRow(0);
+ Cell cell = row.getCell(0);
+ assertEquals("Test", cell.getRichStringCellValue().getString());
+ }
+
+ private static void addNewSheetWithCellsA1toD4(Workbook book, int sheet) {
+
+ Sheet sht = book .createSheet("s" + sheet);
+ for (int r=0; r < 4; r++) {
+
+ Row row = sht.createRow (r);
+ for (int c=0; c < 4; c++) {
+
+ Cell cel = row.createCell(c);
+ cel.setCellValue(sheet*100 + r*10 + c);
+ }
+ }
+ }
+
+ public void testBug43093() {
+ Workbook xlw = getTestDataProvider().createWorkbook();
+
+ addNewSheetWithCellsA1toD4(xlw, 1);
+ addNewSheetWithCellsA1toD4(xlw, 2);
+ addNewSheetWithCellsA1toD4(xlw, 3);
+ addNewSheetWithCellsA1toD4(xlw, 4);
+
+ Sheet s2 = xlw.getSheet("s2");
+ Row s2r3 = s2.getRow(3);
+ Cell s2E4 = s2r3.createCell(4);
+ s2E4.setCellFormula("SUM(s3!B2:C3)");
+
+ FormulaEvaluator eva = xlw.getCreationHelper().createFormulaEvaluator();
+ double d = eva.evaluate(s2E4).getNumberValue();
+
+ assertEquals(d, (311+312+321+322), 0.0000001);
+ }
+
+ public void testMaxFunctionArguments_bug46729(){
+ String[] func = {"COUNT", "AVERAGE", "MAX", "MIN", "OR", "SUBTOTAL", "SKEW"};
+
+ SpreadsheetVersion ssVersion = getTestDataProvider().getSpreadsheetVersion();
+ Workbook wb = getTestDataProvider().createWorkbook();
+ Cell cell = wb.createSheet().createRow(0).createCell(0);
+
+ String fmla;
+ for (String name : func) {
+
+ fmla = createFunction(name, 5);
+ cell.setCellFormula(fmla);
+
+ fmla = createFunction(name, ssVersion.getMaxFunctionArgs());
+ cell.setCellFormula(fmla);
+
+ try {
+ fmla = createFunction(name, ssVersion.getMaxFunctionArgs() + 1);
+ cell.setCellFormula(fmla);
+ fail("Expected FormulaParseException");
+ } catch (RuntimeException e){
+ assertTrue(e.getMessage().startsWith("Too many arguments to function '"+name+"'"));
+ }
+ }
+ }
+
+ private String createFunction(String name, int maxArgs){
+ StringBuffer fmla = new StringBuffer();
+ fmla.append(name);
+ fmla.append("(");
+ for(int i=0; i < maxArgs; i++){
+ if(i > 0) fmla.append(',');
+ fmla.append("A1");
+ }
+ fmla.append(")");
+ return fmla.toString();
+ }
+}
Modified: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestFont.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestFont.java?rev=776056&r1=776055&r2=776056&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestFont.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestFont.java Mon May 18 19:11:45 2009
@@ -1,204 +1,200 @@
-/* ====================================================================
- 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 junit.framework.TestCase;
-import junit.framework.AssertionFailedError;
-import org.apache.poi.ss.ITestDataProvider;
-import org.apache.poi.ss.util.CellRangeAddress;
-
-/**
- * @author Yegor Kozlov
- */
-public abstract class BaseTestFont extends TestCase {
-
- protected abstract ITestDataProvider getTestDataProvider();
-
- public void baseTestDefaultFont(String defaultName, short defaultSize, short defaultColor){
- //get default font and check against default value
- Workbook workbook = getTestDataProvider().createWorkbook();
- Font fontFind=workbook.findFont(Font.BOLDWEIGHT_NORMAL, defaultColor, defaultSize, defaultName, false, false, Font.SS_NONE, Font.U_NONE);
- assertNotNull(fontFind);
-
- //get default font, then change 2 values and check against different values (height changes)
- Font font=workbook.createFont();
- font.setBoldweight(Font.BOLDWEIGHT_BOLD);
- assertEquals(Font.BOLDWEIGHT_BOLD, font.getBoldweight());
- font.setUnderline(Font.U_DOUBLE);
- assertEquals(Font.U_DOUBLE, font.getUnderline());
- font.setFontHeightInPoints((short)15);
- assertEquals(15*20, font.getFontHeight());
- assertEquals(15, font.getFontHeightInPoints());
- fontFind=workbook.findFont(Font.BOLDWEIGHT_BOLD, defaultColor, (short)(15*20), defaultName, false, false, Font.SS_NONE, Font.U_DOUBLE);
- assertNotNull(fontFind);
- }
-
- public void testGetNumberOfFonts(){
- Workbook wb = getTestDataProvider().createWorkbook();
- int num0 = wb.getNumberOfFonts();
-
- Font f1=wb.createFont();
- f1.setBoldweight(Font.BOLDWEIGHT_BOLD);
- short idx1 = f1.getIndex();
- wb.createCellStyle().setFont(f1);
-
- Font f2=wb.createFont();
- f2.setUnderline(Font.U_DOUBLE);
- short idx2 = f2.getIndex();
- wb.createCellStyle().setFont(f2);
-
- Font f3=wb.createFont();
- f3.setFontHeightInPoints((short)23);
- short idx3 = f3.getIndex();
- wb.createCellStyle().setFont(f3);
-
- assertEquals(num0 + 3,wb.getNumberOfFonts());
- assertEquals(Font.BOLDWEIGHT_BOLD,wb.getFontAt(idx1).getBoldweight());
- assertEquals(Font.U_DOUBLE,wb.getFontAt(idx2).getUnderline());
- assertEquals(23,wb.getFontAt(idx3).getFontHeightInPoints());
- }
-
- /**
- * Tests that we can define fonts to a new
- * file, save, load, and still see them
- * @throws Exception
- */
- public void testCreateSave() {
- Workbook wb = getTestDataProvider().createWorkbook();
- Sheet s1 = wb.createSheet();
- Row r1 = s1.createRow(0);
- Cell r1c1 = r1.createCell(0);
- r1c1.setCellValue(2.2);
-
- int num0 = wb.getNumberOfFonts();
-
- Font font=wb.createFont();
- font.setBoldweight(Font.BOLDWEIGHT_BOLD);
- font.setStrikeout(true);
- font.setColor(IndexedColors.YELLOW.getIndex());
- font.setFontName("Courier");
- short font1Idx = font.getIndex();
- wb.createCellStyle().setFont(font);
- assertEquals(num0 + 1, wb.getNumberOfFonts());
-
- CellStyle cellStyleTitle=wb.createCellStyle();
- cellStyleTitle.setFont(font);
- r1c1.setCellStyle(cellStyleTitle);
-
- // Save and re-load
- wb = getTestDataProvider().writeOutAndReadBack(wb);
- s1 = wb.getSheetAt(0);
-
- assertEquals(num0 + 1, wb.getNumberOfFonts());
- short idx = s1.getRow(0).getCell(0).getCellStyle().getFontIndex();
- Font fnt = wb.getFontAt(idx);
- assertNotNull(fnt);
- assertEquals(IndexedColors.YELLOW.getIndex(), fnt.getColor());
- assertEquals("Courier", fnt.getFontName());
-
- // Now add an orphaned one
- Font font2 = wb.createFont();
- font2.setItalic(true);
- font2.setFontHeightInPoints((short)15);
- short font2Idx = font2.getIndex();
- wb.createCellStyle().setFont(font2);
- assertEquals(num0 + 2, wb.getNumberOfFonts());
-
- // Save and re-load
- wb = getTestDataProvider().writeOutAndReadBack(wb);
- s1 = wb.getSheetAt(0);
-
- assertEquals(num0 + 2, wb.getNumberOfFonts());
- assertNotNull(wb.getFontAt(font1Idx));
- assertNotNull(wb.getFontAt(font2Idx));
-
- assertEquals(15, wb.getFontAt(font2Idx).getFontHeightInPoints());
- assertEquals(true, wb.getFontAt(font2Idx).getItalic());
- }
-
-
-
- /**
- * Test that fonts get added properly
- *
- * @see org.apache.poi.hssf.usermodel.TestBugs#test45338()
- */
- public void test45338() {
- Workbook wb = getTestDataProvider().createWorkbook();
- int num0 = wb.getNumberOfFonts();
-
- Sheet s = wb.createSheet();
- s.createRow(0);
- s.createRow(1);
- s.getRow(0).createCell(0);
- s.getRow(1).createCell(0);
-
- //default font
- Font f1 = wb.getFontAt((short)0);
- assertEquals(Font.BOLDWEIGHT_NORMAL, f1.getBoldweight());
-
- // Check that asking for the same font
- // multiple times gives you the same thing.
- // Otherwise, our tests wouldn't work!
- assertSame(wb.getFontAt((short)0), wb.getFontAt((short)0));
-
- // Look for a new font we have
- // yet to add
- assertNull(
- wb.findFont(
- Font.BOLDWEIGHT_BOLD, (short)123, (short)(22*20),
- "Thingy", false, true, (short)2, (byte)2
- )
- );
-
- Font nf = wb.createFont();
- short nfIdx = nf.getIndex();
- assertEquals(num0 + 1, wb.getNumberOfFonts());
-
- assertSame(nf, wb.getFontAt(nfIdx));
-
- nf.setBoldweight(Font.BOLDWEIGHT_BOLD);
- nf.setColor((short)123);
- nf.setFontHeightInPoints((short)22);
- nf.setFontName("Thingy");
- nf.setItalic(false);
- nf.setStrikeout(true);
- nf.setTypeOffset((short)2);
- nf.setUnderline((byte)2);
-
- assertEquals(num0 + 1, wb.getNumberOfFonts());
- assertEquals(nf, wb.getFontAt(nfIdx));
-
- assertEquals(wb.getFontAt(nfIdx), wb.getFontAt(nfIdx));
- assertTrue(wb.getFontAt((short)0) != wb.getFontAt(nfIdx));
-
- // Find it now
- assertNotNull(
- wb.findFont(
- Font.BOLDWEIGHT_BOLD, (short)123, (short)(22*20),
- "Thingy", false, true, (short)2, (byte)2
- )
- );
- assertSame(nf,
- wb.findFont(
- Font.BOLDWEIGHT_BOLD, (short)123, (short)(22*20),
- "Thingy", false, true, (short)2, (byte)2
- )
- );
- }
-
-}
\ No newline at end of file
+/* ====================================================================
+ 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 junit.framework.TestCase;
+
+import org.apache.poi.ss.ITestDataProvider;
+
+/**
+ * @author Yegor Kozlov
+ */
+public abstract class BaseTestFont extends TestCase {
+
+ protected abstract ITestDataProvider getTestDataProvider();
+
+ public void baseTestDefaultFont(String defaultName, short defaultSize, short defaultColor){
+ //get default font and check against default value
+ Workbook workbook = getTestDataProvider().createWorkbook();
+ Font fontFind=workbook.findFont(Font.BOLDWEIGHT_NORMAL, defaultColor, defaultSize, defaultName, false, false, Font.SS_NONE, Font.U_NONE);
+ assertNotNull(fontFind);
+
+ //get default font, then change 2 values and check against different values (height changes)
+ Font font=workbook.createFont();
+ font.setBoldweight(Font.BOLDWEIGHT_BOLD);
+ assertEquals(Font.BOLDWEIGHT_BOLD, font.getBoldweight());
+ font.setUnderline(Font.U_DOUBLE);
+ assertEquals(Font.U_DOUBLE, font.getUnderline());
+ font.setFontHeightInPoints((short)15);
+ assertEquals(15*20, font.getFontHeight());
+ assertEquals(15, font.getFontHeightInPoints());
+ fontFind=workbook.findFont(Font.BOLDWEIGHT_BOLD, defaultColor, (short)(15*20), defaultName, false, false, Font.SS_NONE, Font.U_DOUBLE);
+ assertNotNull(fontFind);
+ }
+
+ public void testGetNumberOfFonts(){
+ Workbook wb = getTestDataProvider().createWorkbook();
+ int num0 = wb.getNumberOfFonts();
+
+ Font f1=wb.createFont();
+ f1.setBoldweight(Font.BOLDWEIGHT_BOLD);
+ short idx1 = f1.getIndex();
+ wb.createCellStyle().setFont(f1);
+
+ Font f2=wb.createFont();
+ f2.setUnderline(Font.U_DOUBLE);
+ short idx2 = f2.getIndex();
+ wb.createCellStyle().setFont(f2);
+
+ Font f3=wb.createFont();
+ f3.setFontHeightInPoints((short)23);
+ short idx3 = f3.getIndex();
+ wb.createCellStyle().setFont(f3);
+
+ assertEquals(num0 + 3,wb.getNumberOfFonts());
+ assertEquals(Font.BOLDWEIGHT_BOLD,wb.getFontAt(idx1).getBoldweight());
+ assertEquals(Font.U_DOUBLE,wb.getFontAt(idx2).getUnderline());
+ assertEquals(23,wb.getFontAt(idx3).getFontHeightInPoints());
+ }
+
+ /**
+ * Tests that we can define fonts to a new
+ * file, save, load, and still see them
+ */
+ public void testCreateSave() {
+ Workbook wb = getTestDataProvider().createWorkbook();
+ Sheet s1 = wb.createSheet();
+ Row r1 = s1.createRow(0);
+ Cell r1c1 = r1.createCell(0);
+ r1c1.setCellValue(2.2);
+
+ int num0 = wb.getNumberOfFonts();
+
+ Font font=wb.createFont();
+ font.setBoldweight(Font.BOLDWEIGHT_BOLD);
+ font.setStrikeout(true);
+ font.setColor(IndexedColors.YELLOW.getIndex());
+ font.setFontName("Courier");
+ short font1Idx = font.getIndex();
+ wb.createCellStyle().setFont(font);
+ assertEquals(num0 + 1, wb.getNumberOfFonts());
+
+ CellStyle cellStyleTitle=wb.createCellStyle();
+ cellStyleTitle.setFont(font);
+ r1c1.setCellStyle(cellStyleTitle);
+
+ // Save and re-load
+ wb = getTestDataProvider().writeOutAndReadBack(wb);
+ s1 = wb.getSheetAt(0);
+
+ assertEquals(num0 + 1, wb.getNumberOfFonts());
+ short idx = s1.getRow(0).getCell(0).getCellStyle().getFontIndex();
+ Font fnt = wb.getFontAt(idx);
+ assertNotNull(fnt);
+ assertEquals(IndexedColors.YELLOW.getIndex(), fnt.getColor());
+ assertEquals("Courier", fnt.getFontName());
+
+ // Now add an orphaned one
+ Font font2 = wb.createFont();
+ font2.setItalic(true);
+ font2.setFontHeightInPoints((short)15);
+ short font2Idx = font2.getIndex();
+ wb.createCellStyle().setFont(font2);
+ assertEquals(num0 + 2, wb.getNumberOfFonts());
+
+ // Save and re-load
+ wb = getTestDataProvider().writeOutAndReadBack(wb);
+ s1 = wb.getSheetAt(0);
+
+ assertEquals(num0 + 2, wb.getNumberOfFonts());
+ assertNotNull(wb.getFontAt(font1Idx));
+ assertNotNull(wb.getFontAt(font2Idx));
+
+ assertEquals(15, wb.getFontAt(font2Idx).getFontHeightInPoints());
+ assertEquals(true, wb.getFontAt(font2Idx).getItalic());
+ }
+
+ /**
+ * Test that fonts get added properly
+ *
+ * @see org.apache.poi.hssf.usermodel.TestBugs#test45338()
+ */
+ public void test45338() {
+ Workbook wb = getTestDataProvider().createWorkbook();
+ int num0 = wb.getNumberOfFonts();
+
+ Sheet s = wb.createSheet();
+ s.createRow(0);
+ s.createRow(1);
+ s.getRow(0).createCell(0);
+ s.getRow(1).createCell(0);
+
+ //default font
+ Font f1 = wb.getFontAt((short)0);
+ assertEquals(Font.BOLDWEIGHT_NORMAL, f1.getBoldweight());
+
+ // Check that asking for the same font
+ // multiple times gives you the same thing.
+ // Otherwise, our tests wouldn't work!
+ assertSame(wb.getFontAt((short)0), wb.getFontAt((short)0));
+
+ // Look for a new font we have
+ // yet to add
+ assertNull(
+ wb.findFont(
+ Font.BOLDWEIGHT_BOLD, (short)123, (short)(22*20),
+ "Thingy", false, true, (short)2, (byte)2
+ )
+ );
+
+ Font nf = wb.createFont();
+ short nfIdx = nf.getIndex();
+ assertEquals(num0 + 1, wb.getNumberOfFonts());
+
+ assertSame(nf, wb.getFontAt(nfIdx));
+
+ nf.setBoldweight(Font.BOLDWEIGHT_BOLD);
+ nf.setColor((short)123);
+ nf.setFontHeightInPoints((short)22);
+ nf.setFontName("Thingy");
+ nf.setItalic(false);
+ nf.setStrikeout(true);
+ nf.setTypeOffset((short)2);
+ nf.setUnderline((byte)2);
+
+ assertEquals(num0 + 1, wb.getNumberOfFonts());
+ assertEquals(nf, wb.getFontAt(nfIdx));
+
+ assertEquals(wb.getFontAt(nfIdx), wb.getFontAt(nfIdx));
+ assertTrue(wb.getFontAt((short)0) != wb.getFontAt(nfIdx));
+
+ // Find it now
+ assertNotNull(
+ wb.findFont(
+ Font.BOLDWEIGHT_BOLD, (short)123, (short)(22*20),
+ "Thingy", false, true, (short)2, (byte)2
+ )
+ );
+ assertSame(nf,
+ wb.findFont(
+ Font.BOLDWEIGHT_BOLD, (short)123, (short)(22*20),
+ "Thingy", false, true, (short)2, (byte)2
+ )
+ );
+ }
+}
Modified: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestPicture.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestPicture.java?rev=776056&r1=776055&r2=776056&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestPicture.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestPicture.java Mon May 18 19:11:45 2009
@@ -1,53 +1,52 @@
-/* ====================================================================
- 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 junit.framework.TestCase;
-import org.apache.poi.ss.ITestDataProvider;
-
-/**
- * @author Yegor Kozlov
- */
-public abstract class BaseTestPicture extends TestCase {
-
- protected abstract ITestDataProvider getTestDataProvider();
-
- public void baseTestResize(ClientAnchor referenceAnchor) {
- Workbook wb = getTestDataProvider().createWorkbook();
- Sheet sh1 = wb.createSheet();
- Drawing p1 = sh1.createDrawingPatriarch();
- CreationHelper factory = wb.getCreationHelper();
-
- byte[] pictureData = getTestDataProvider().getTestDataFileContent("logoKarmokar4.png");
- int idx1 = wb.addPicture( pictureData, Workbook.PICTURE_TYPE_PNG );
- Picture picture = p1.createPicture(factory.createClientAnchor(), idx1);
- picture.resize();
- ClientAnchor anchor1 = picture.getPreferredSize();
-
- //assert against what would BiffViewer print if we insert the image in xls and dump the file
- assertEquals(referenceAnchor.getCol1(), anchor1.getCol1());
- assertEquals(referenceAnchor.getRow1(), anchor1.getRow1());
- assertEquals(referenceAnchor.getCol2(), anchor1.getCol2());
- assertEquals(referenceAnchor.getRow2(), anchor1.getRow2());
- assertEquals(referenceAnchor.getDx1(), anchor1.getDx1());
- assertEquals(referenceAnchor.getDy1(), anchor1.getDy1());
- assertEquals(referenceAnchor.getDx2(), anchor1.getDx2());
- assertEquals(referenceAnchor.getDy2(), anchor1.getDy2());
- }
-
-
-}
\ No newline at end of file
+/* ====================================================================
+ 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 junit.framework.TestCase;
+import org.apache.poi.ss.ITestDataProvider;
+
+/**
+ * @author Yegor Kozlov
+ */
+public abstract class BaseTestPicture extends TestCase {
+
+ protected abstract ITestDataProvider getTestDataProvider();
+
+ public void baseTestResize(ClientAnchor referenceAnchor) {
+ Workbook wb = getTestDataProvider().createWorkbook();
+ Sheet sh1 = wb.createSheet();
+ Drawing p1 = sh1.createDrawingPatriarch();
+ CreationHelper factory = wb.getCreationHelper();
+
+ byte[] pictureData = getTestDataProvider().getTestDataFileContent("logoKarmokar4.png");
+ int idx1 = wb.addPicture( pictureData, Workbook.PICTURE_TYPE_PNG );
+ Picture picture = p1.createPicture(factory.createClientAnchor(), idx1);
+ picture.resize();
+ ClientAnchor anchor1 = picture.getPreferredSize();
+
+ //assert against what would BiffViewer print if we insert the image in xls and dump the file
+ assertEquals(referenceAnchor.getCol1(), anchor1.getCol1());
+ assertEquals(referenceAnchor.getRow1(), anchor1.getRow1());
+ assertEquals(referenceAnchor.getCol2(), anchor1.getCol2());
+ assertEquals(referenceAnchor.getRow2(), anchor1.getRow2());
+ assertEquals(referenceAnchor.getDx1(), anchor1.getDx1());
+ assertEquals(referenceAnchor.getDy1(), anchor1.getDy1());
+ assertEquals(referenceAnchor.getDx2(), anchor1.getDx2());
+ assertEquals(referenceAnchor.getDy2(), anchor1.getDy2());
+ }
+}
Modified: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java?rev=776056&r1=776055&r2=776056&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java Mon May 18 19:11:45 2009
@@ -1,327 +1,327 @@
-/* ====================================================================
- 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 junit.framework.TestCase;
-import org.apache.poi.ss.ITestDataProvider;
-import org.apache.poi.ss.util.CellRangeAddress;
-
-/**
- * Tests row shifting capabilities.
- *
- * @author Shawn Laubach (slaubach at apache dot com)
- * @author Toshiaki Kamoshida (kamoshida.toshiaki at future dot co dot jp)
- */
-public abstract class BaseTestSheetShiftRows extends TestCase {
-
- /**
- * @return an object that provides test data in HSSF / XSSF specific way
- */
- protected abstract ITestDataProvider getTestDataProvider();
-
- /**
- * Tests the shiftRows function. Does three different shifts.
- * After each shift, writes the workbook to file and reads back to
- * check. This ensures that if some changes code that breaks
- * writing or what not, they realize it.
- *
- * @param sampleName the sample file to test against
- */
- public final void baseTestShiftRows(String sampleName){
- // Read initial file in
- Workbook wb = getTestDataProvider().openSampleWorkbook(sampleName);
- Sheet s = wb.getSheetAt( 0 );
-
- // Shift the second row down 1 and write to temp file
- s.shiftRows( 1, 1, 1 );
-
- wb = getTestDataProvider().writeOutAndReadBack(wb);
-
- // Read from temp file and check the number of cells in each
- // row (in original file each row was unique)
- s = wb.getSheetAt( 0 );
-
- assertEquals(s.getRow(0).getPhysicalNumberOfCells(), 1);
- confirmEmptyRow(s, 1);
- assertEquals(s.getRow(2).getPhysicalNumberOfCells(), 2);
- assertEquals(s.getRow(3).getPhysicalNumberOfCells(), 4);
- assertEquals(s.getRow(4).getPhysicalNumberOfCells(), 5);
-
- // Shift rows 1-3 down 3 in the current one. This tests when
- // 1 row is blank. Write to a another temp file
- s.shiftRows( 0, 2, 3 );
- wb = getTestDataProvider().writeOutAndReadBack(wb);
-
- // Read and ensure things are where they should be
- s = wb.getSheetAt(0);
- confirmEmptyRow(s, 0);
- confirmEmptyRow(s, 1);
- confirmEmptyRow(s, 2);
- assertEquals(s.getRow(3).getPhysicalNumberOfCells(), 1);
- confirmEmptyRow(s, 4);
- assertEquals(s.getRow(5).getPhysicalNumberOfCells(), 2);
-
- // Read the first file again
- wb = getTestDataProvider().openSampleWorkbook(sampleName);
- s = wb.getSheetAt( 0 );
-
- // Shift rows 3 and 4 up and write to temp file
- s.shiftRows( 2, 3, -2 );
- wb = getTestDataProvider().writeOutAndReadBack(wb);
- s = wb.getSheetAt( 0 );
- assertEquals(s.getRow(0).getPhysicalNumberOfCells(), 3);
- assertEquals(s.getRow(1).getPhysicalNumberOfCells(), 4);
- confirmEmptyRow(s, 2);
- confirmEmptyRow(s, 3);
- assertEquals(s.getRow(4).getPhysicalNumberOfCells(), 5);
- }
- private static void confirmEmptyRow(Sheet s, int rowIx) {
- Row row = s.getRow(rowIx);
- assertTrue(row == null || row.getPhysicalNumberOfCells() == 0);
- }
-
- /**
- * Tests when rows are null.
- */
- public final void baseTestShiftRow() {
- Workbook b = getTestDataProvider().createWorkbook();
- Sheet s = b.createSheet();
- s.createRow(0).createCell(0).setCellValue("TEST1");
- s.createRow(3).createCell(0).setCellValue("TEST2");
- s.shiftRows(0,4,1);
- }
-
- /**
- * Tests when shifting the first row.
- */
- public final void baseTestActiveCell() {
- Workbook b = getTestDataProvider().createWorkbook();
- Sheet s = b.createSheet();
-
- s.createRow(0).createCell(0).setCellValue("TEST1");
- s.createRow(3).createCell(0).setCellValue("TEST2");
- s.shiftRows(0,4,1);
- }
-
- /**
- * When shifting rows, the page breaks should go with it
- *
- */
- public final void baseTestShiftRowBreaks() {
- Workbook b = getTestDataProvider().createWorkbook();
- Sheet s = b.createSheet();
- Row row = s.createRow(4);
- row.createCell(0).setCellValue("test");
- s.setRowBreak(4);
-
- s.shiftRows(4, 4, 2);
- assertTrue("Row number 6 should have a pagebreak", s.isRowBroken(6));
- }
-
-
- public final void baseTestShiftWithComments(String sampleName) {
- Workbook wb = getTestDataProvider().openSampleWorkbook(sampleName);
-
- Sheet sheet = wb.getSheet("Sheet1");
- assertEquals(3, sheet.getLastRowNum());
-
- // Verify comments are in the position expected
- assertNotNull(sheet.getCellComment(0,0));
- assertNull(sheet.getCellComment(1,0));
- assertNotNull(sheet.getCellComment(2,0));
- assertNotNull(sheet.getCellComment(3,0));
-
- String comment1 = sheet.getCellComment(0,0).getString().getString();
- assertEquals(comment1,"comment top row1 (index0)\n");
- String comment3 = sheet.getCellComment(2,0).getString().getString();
- assertEquals(comment3,"comment top row3 (index2)\n");
- String comment4 = sheet.getCellComment(3,0).getString().getString();
- assertEquals(comment4,"comment top row4 (index3)\n");
-
- // Shifting all but first line down to test comments shifting
- sheet.shiftRows(1, sheet.getLastRowNum(), 1, true, true);
-
- // Test that comments were shifted as expected
- assertEquals(4, sheet.getLastRowNum());
- assertNotNull(sheet.getCellComment(0,0));
- assertNull(sheet.getCellComment(1,0));
- assertNull(sheet.getCellComment(2,0));
- assertNotNull(sheet.getCellComment(3,0));
- assertNotNull(sheet.getCellComment(4,0));
-
- String comment1_shifted = sheet.getCellComment(0,0).getString().getString();
- assertEquals(comment1,comment1_shifted);
- String comment3_shifted = sheet.getCellComment(3,0).getString().getString();
- assertEquals(comment3,comment3_shifted);
- String comment4_shifted = sheet.getCellComment(4,0).getString().getString();
- assertEquals(comment4,comment4_shifted);
-
- // Write out and read back in again
- // Ensure that the changes were persisted
- wb = getTestDataProvider().writeOutAndReadBack(wb);
- sheet = wb.getSheet("Sheet1");
- assertEquals(4, sheet.getLastRowNum());
-
- // Verify comments are in the position expected after the shift
- assertNotNull(sheet.getCellComment(0,0));
- assertNull(sheet.getCellComment(1,0));
- assertNull(sheet.getCellComment(2,0));
- assertNotNull(sheet.getCellComment(3,0));
- assertNotNull(sheet.getCellComment(4,0));
-
- comment1_shifted = sheet.getCellComment(0,0).getString().getString();
- assertEquals(comment1,comment1_shifted);
- comment3_shifted = sheet.getCellComment(3,0).getString().getString();
- assertEquals(comment3,comment3_shifted);
- comment4_shifted = sheet.getCellComment(4,0).getString().getString();
- assertEquals(comment4,comment4_shifted);
- }
-
- public final void baseTestShiftWithNames() {
- Workbook wb = getTestDataProvider().createWorkbook();
- Sheet sheet1 = wb.createSheet("Sheet1");
- Sheet sheet2 = wb.createSheet("Sheet2");
- Row row = sheet1.createRow(0);
- row.createCell(0).setCellValue(1.1);
- row.createCell(1).setCellValue(2.2);
-
- Name name1 = wb.createName();
- name1.setNameName("name1");
- name1.setRefersToFormula("Sheet1!$A$1+Sheet1!$B$1");
-
- Name name2 = wb.createName();
- name2.setNameName("name2");
- name2.setRefersToFormula("Sheet1!$A$1");
-
- //refers to A1 but on Sheet2. Should stay unaffected.
- Name name3 = wb.createName();
- name3.setNameName("name3");
- name3.setRefersToFormula("Sheet2!$A$1");
-
- //The scope of this one is Sheet2. Should stay unaffected.
- Name name4 = wb.createName();
- name4.setNameName("name4");
- name4.setRefersToFormula("A1");
- name4.setSheetIndex(1);
-
- sheet1.shiftRows(0, 1, 2); //shift down the top row on Sheet1.
- name1 = wb.getNameAt(0);
- assertEquals("Sheet1!$A$3+Sheet1!$B$3", name1.getRefersToFormula());
-
- name2 = wb.getNameAt(1);
- assertEquals("Sheet1!$A$3", name2.getRefersToFormula());
-
- //name3 and name4 refer to Sheet2 and should not be affected
- name3 = wb.getNameAt(2);
- assertEquals("Sheet2!$A$1", name3.getRefersToFormula());
-
- name4 = wb.getNameAt(3);
- assertEquals("A1", name4.getRefersToFormula());
- }
-
- public final void baseTestShiftWithMergedRegions() {
- Workbook wb = getTestDataProvider().createWorkbook();
- Sheet sheet = wb.createSheet();
- Row row = sheet.createRow(0);
- row.createCell(0).setCellValue(1.1);
- row.createCell(1).setCellValue(2.2);
- CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
- assertEquals("A1:C1", region.formatAsString());
-
- sheet.addMergedRegion(region);
-
- sheet.shiftRows(0, 1, 2);
- region = sheet.getMergedRegion(0);
- assertEquals("A3:C3", region.formatAsString());
- }
-
- /**
- * See bug #34023
- *
- * @param sampleName the sample file to test against
- */
- public void baseTestShiftWithFormulas(String sampleName) {
- Workbook wb = getTestDataProvider().openSampleWorkbook(sampleName);
-
- Sheet sheet = wb.getSheet("Sheet1");
- assertEquals(20, sheet.getLastRowNum());
-
- confirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
- confirmRow(sheet, 1, 2, 172, 1, "ROW(D2)", "100+B2", "COUNT(D2:E2)");
- confirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");
-
- confirmCell(sheet, 6, 1, 271, "200+B1");
- confirmCell(sheet, 7, 1, 272, "200+B2");
- confirmCell(sheet, 8, 1, 273, "200+B3");
-
- confirmCell(sheet, 14, 0, 0.0, "A12"); // the cell referred to by this formula will be replaced
-
- // -----------
- // Row index 1 -> 11 (row "2" -> row "12")
- sheet.shiftRows(1, 1, 10);
-
- // Now check what sheet looks like after move
-
- // no changes on row "1"
- confirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
-
- // row "2" is now empty
- confirmEmptyRow(sheet, 1);
-
- // Row "2" moved to row "12", and the formula has been updated.
- // note however that the cached formula result (2) has not been updated. (POI differs from Excel here)
- confirmRow(sheet, 11, 2, 172, 1, "ROW(D12)", "100+B12", "COUNT(D12:E12)");
-
- // no changes on row "3"
- confirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");
-
-
- confirmCell(sheet, 14, 0, 0.0, "#REF!");
-
-
- // Formulas on rows that weren't shifted:
- confirmCell(sheet, 6, 1, 271, "200+B1");
- confirmCell(sheet, 7, 1, 272, "200+B12"); // this one moved
- confirmCell(sheet, 8, 1, 273, "200+B3");
-
- // check formulas on other sheets
- Sheet sheet2 = wb.getSheet("Sheet2");
- confirmCell(sheet2, 0, 0, 371, "300+Sheet1!B1");
- confirmCell(sheet2, 1, 0, 372, "300+Sheet1!B12");
- confirmCell(sheet2, 2, 0, 373, "300+Sheet1!B3");
-
- confirmCell(sheet2, 11, 0, 300, "300+Sheet1!#REF!");
-
-
- // Note - named ranges formulas have not been updated
- }
-
- private static void confirmRow(Sheet sheet, int rowIx, double valA, double valB, double valC,
- String formulaA, String formulaB, String formulaC) {
- confirmCell(sheet, rowIx, 4, valA, formulaA);
- confirmCell(sheet, rowIx, 5, valB, formulaB);
- confirmCell(sheet, rowIx, 6, valC, formulaC);
- }
-
- private static void confirmCell(Sheet sheet, int rowIx, int colIx,
- double expectedValue, String expectedFormula) {
- Cell cell = sheet.getRow(rowIx).getCell(colIx);
- assertEquals(expectedValue, cell.getNumericCellValue(), 0.0);
- assertEquals(expectedFormula, cell.getCellFormula());
- }
-}
+/* ====================================================================
+ 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 junit.framework.TestCase;
+import org.apache.poi.ss.ITestDataProvider;
+import org.apache.poi.ss.util.CellRangeAddress;
+
+/**
+ * Tests row shifting capabilities.
+ *
+ * @author Shawn Laubach (slaubach at apache dot com)
+ * @author Toshiaki Kamoshida (kamoshida.toshiaki at future dot co dot jp)
+ */
+public abstract class BaseTestSheetShiftRows extends TestCase {
+
+ /**
+ * @return an object that provides test data in HSSF / XSSF specific way
+ */
+ protected abstract ITestDataProvider getTestDataProvider();
+
+ /**
+ * Tests the shiftRows function. Does three different shifts.
+ * After each shift, writes the workbook to file and reads back to
+ * check. This ensures that if some changes code that breaks
+ * writing or what not, they realize it.
+ *
+ * @param sampleName the sample file to test against
+ */
+ public final void baseTestShiftRows(String sampleName){
+ // Read initial file in
+ Workbook wb = getTestDataProvider().openSampleWorkbook(sampleName);
+ Sheet s = wb.getSheetAt( 0 );
+
+ // Shift the second row down 1 and write to temp file
+ s.shiftRows( 1, 1, 1 );
+
+ wb = getTestDataProvider().writeOutAndReadBack(wb);
+
+ // Read from temp file and check the number of cells in each
+ // row (in original file each row was unique)
+ s = wb.getSheetAt( 0 );
+
+ assertEquals(s.getRow(0).getPhysicalNumberOfCells(), 1);
+ confirmEmptyRow(s, 1);
+ assertEquals(s.getRow(2).getPhysicalNumberOfCells(), 2);
+ assertEquals(s.getRow(3).getPhysicalNumberOfCells(), 4);
+ assertEquals(s.getRow(4).getPhysicalNumberOfCells(), 5);
+
+ // Shift rows 1-3 down 3 in the current one. This tests when
+ // 1 row is blank. Write to a another temp file
+ s.shiftRows( 0, 2, 3 );
+ wb = getTestDataProvider().writeOutAndReadBack(wb);
+
+ // Read and ensure things are where they should be
+ s = wb.getSheetAt(0);
+ confirmEmptyRow(s, 0);
+ confirmEmptyRow(s, 1);
+ confirmEmptyRow(s, 2);
+ assertEquals(s.getRow(3).getPhysicalNumberOfCells(), 1);
+ confirmEmptyRow(s, 4);
+ assertEquals(s.getRow(5).getPhysicalNumberOfCells(), 2);
+
+ // Read the first file again
+ wb = getTestDataProvider().openSampleWorkbook(sampleName);
+ s = wb.getSheetAt( 0 );
+
+ // Shift rows 3 and 4 up and write to temp file
+ s.shiftRows( 2, 3, -2 );
+ wb = getTestDataProvider().writeOutAndReadBack(wb);
+ s = wb.getSheetAt( 0 );
+ assertEquals(s.getRow(0).getPhysicalNumberOfCells(), 3);
+ assertEquals(s.getRow(1).getPhysicalNumberOfCells(), 4);
+ confirmEmptyRow(s, 2);
+ confirmEmptyRow(s, 3);
+ assertEquals(s.getRow(4).getPhysicalNumberOfCells(), 5);
+ }
+ private static void confirmEmptyRow(Sheet s, int rowIx) {
+ Row row = s.getRow(rowIx);
+ assertTrue(row == null || row.getPhysicalNumberOfCells() == 0);
+ }
+
+ /**
+ * Tests when rows are null.
+ */
+ public final void baseTestShiftRow() {
+ Workbook b = getTestDataProvider().createWorkbook();
+ Sheet s = b.createSheet();
+ s.createRow(0).createCell(0).setCellValue("TEST1");
+ s.createRow(3).createCell(0).setCellValue("TEST2");
+ s.shiftRows(0,4,1);
+ }
+
+ /**
+ * Tests when shifting the first row.
+ */
+ public final void baseTestActiveCell() {
+ Workbook b = getTestDataProvider().createWorkbook();
+ Sheet s = b.createSheet();
+
+ s.createRow(0).createCell(0).setCellValue("TEST1");
+ s.createRow(3).createCell(0).setCellValue("TEST2");
+ s.shiftRows(0,4,1);
+ }
+
+ /**
+ * When shifting rows, the page breaks should go with it
+ *
+ */
+ public final void baseTestShiftRowBreaks() {
+ Workbook b = getTestDataProvider().createWorkbook();
+ Sheet s = b.createSheet();
+ Row row = s.createRow(4);
+ row.createCell(0).setCellValue("test");
+ s.setRowBreak(4);
+
+ s.shiftRows(4, 4, 2);
+ assertTrue("Row number 6 should have a pagebreak", s.isRowBroken(6));
+ }
+
+
+ public final void baseTestShiftWithComments(String sampleName) {
+ Workbook wb = getTestDataProvider().openSampleWorkbook(sampleName);
+
+ Sheet sheet = wb.getSheet("Sheet1");
+ assertEquals(3, sheet.getLastRowNum());
+
+ // Verify comments are in the position expected
+ assertNotNull(sheet.getCellComment(0,0));
+ assertNull(sheet.getCellComment(1,0));
+ assertNotNull(sheet.getCellComment(2,0));
+ assertNotNull(sheet.getCellComment(3,0));
+
+ String comment1 = sheet.getCellComment(0,0).getString().getString();
+ assertEquals(comment1,"comment top row1 (index0)\n");
+ String comment3 = sheet.getCellComment(2,0).getString().getString();
+ assertEquals(comment3,"comment top row3 (index2)\n");
+ String comment4 = sheet.getCellComment(3,0).getString().getString();
+ assertEquals(comment4,"comment top row4 (index3)\n");
+
+ // Shifting all but first line down to test comments shifting
+ sheet.shiftRows(1, sheet.getLastRowNum(), 1, true, true);
+
+ // Test that comments were shifted as expected
+ assertEquals(4, sheet.getLastRowNum());
+ assertNotNull(sheet.getCellComment(0,0));
+ assertNull(sheet.getCellComment(1,0));
+ assertNull(sheet.getCellComment(2,0));
+ assertNotNull(sheet.getCellComment(3,0));
+ assertNotNull(sheet.getCellComment(4,0));
+
+ String comment1_shifted = sheet.getCellComment(0,0).getString().getString();
+ assertEquals(comment1,comment1_shifted);
+ String comment3_shifted = sheet.getCellComment(3,0).getString().getString();
+ assertEquals(comment3,comment3_shifted);
+ String comment4_shifted = sheet.getCellComment(4,0).getString().getString();
+ assertEquals(comment4,comment4_shifted);
+
+ // Write out and read back in again
+ // Ensure that the changes were persisted
+ wb = getTestDataProvider().writeOutAndReadBack(wb);
+ sheet = wb.getSheet("Sheet1");
+ assertEquals(4, sheet.getLastRowNum());
+
+ // Verify comments are in the position expected after the shift
+ assertNotNull(sheet.getCellComment(0,0));
+ assertNull(sheet.getCellComment(1,0));
+ assertNull(sheet.getCellComment(2,0));
+ assertNotNull(sheet.getCellComment(3,0));
+ assertNotNull(sheet.getCellComment(4,0));
+
+ comment1_shifted = sheet.getCellComment(0,0).getString().getString();
+ assertEquals(comment1,comment1_shifted);
+ comment3_shifted = sheet.getCellComment(3,0).getString().getString();
+ assertEquals(comment3,comment3_shifted);
+ comment4_shifted = sheet.getCellComment(4,0).getString().getString();
+ assertEquals(comment4,comment4_shifted);
+ }
+
+ public final void baseTestShiftWithNames() {
+ Workbook wb = getTestDataProvider().createWorkbook();
+ Sheet sheet1 = wb.createSheet("Sheet1");
+ wb.createSheet("Sheet2");
+ Row row = sheet1.createRow(0);
+ row.createCell(0).setCellValue(1.1);
+ row.createCell(1).setCellValue(2.2);
+
+ Name name1 = wb.createName();
+ name1.setNameName("name1");
+ name1.setRefersToFormula("Sheet1!$A$1+Sheet1!$B$1");
+
+ Name name2 = wb.createName();
+ name2.setNameName("name2");
+ name2.setRefersToFormula("Sheet1!$A$1");
+
+ //refers to A1 but on Sheet2. Should stay unaffected.
+ Name name3 = wb.createName();
+ name3.setNameName("name3");
+ name3.setRefersToFormula("Sheet2!$A$1");
+
+ //The scope of this one is Sheet2. Should stay unaffected.
+ Name name4 = wb.createName();
+ name4.setNameName("name4");
+ name4.setRefersToFormula("A1");
+ name4.setSheetIndex(1);
+
+ sheet1.shiftRows(0, 1, 2); //shift down the top row on Sheet1.
+ name1 = wb.getNameAt(0);
+ assertEquals("Sheet1!$A$3+Sheet1!$B$3", name1.getRefersToFormula());
+
+ name2 = wb.getNameAt(1);
+ assertEquals("Sheet1!$A$3", name2.getRefersToFormula());
+
+ //name3 and name4 refer to Sheet2 and should not be affected
+ name3 = wb.getNameAt(2);
+ assertEquals("Sheet2!$A$1", name3.getRefersToFormula());
+
+ name4 = wb.getNameAt(3);
+ assertEquals("A1", name4.getRefersToFormula());
+ }
+
+ public final void baseTestShiftWithMergedRegions() {
+ Workbook wb = getTestDataProvider().createWorkbook();
+ Sheet sheet = wb.createSheet();
+ Row row = sheet.createRow(0);
+ row.createCell(0).setCellValue(1.1);
+ row.createCell(1).setCellValue(2.2);
+ CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
+ assertEquals("A1:C1", region.formatAsString());
+
+ sheet.addMergedRegion(region);
+
+ sheet.shiftRows(0, 1, 2);
+ region = sheet.getMergedRegion(0);
+ assertEquals("A3:C3", region.formatAsString());
+ }
+
+ /**
+ * See bug #34023
+ *
+ * @param sampleName the sample file to test against
+ */
+ public void baseTestShiftWithFormulas(String sampleName) {
+ Workbook wb = getTestDataProvider().openSampleWorkbook(sampleName);
+
+ Sheet sheet = wb.getSheet("Sheet1");
+ assertEquals(20, sheet.getLastRowNum());
+
+ confirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
+ confirmRow(sheet, 1, 2, 172, 1, "ROW(D2)", "100+B2", "COUNT(D2:E2)");
+ confirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");
+
+ confirmCell(sheet, 6, 1, 271, "200+B1");
+ confirmCell(sheet, 7, 1, 272, "200+B2");
+ confirmCell(sheet, 8, 1, 273, "200+B3");
+
+ confirmCell(sheet, 14, 0, 0.0, "A12"); // the cell referred to by this formula will be replaced
+
+ // -----------
+ // Row index 1 -> 11 (row "2" -> row "12")
+ sheet.shiftRows(1, 1, 10);
+
+ // Now check what sheet looks like after move
+
+ // no changes on row "1"
+ confirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
+
+ // row "2" is now empty
+ confirmEmptyRow(sheet, 1);
+
+ // Row "2" moved to row "12", and the formula has been updated.
+ // note however that the cached formula result (2) has not been updated. (POI differs from Excel here)
+ confirmRow(sheet, 11, 2, 172, 1, "ROW(D12)", "100+B12", "COUNT(D12:E12)");
+
+ // no changes on row "3"
+ confirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");
+
+
+ confirmCell(sheet, 14, 0, 0.0, "#REF!");
+
+
+ // Formulas on rows that weren't shifted:
+ confirmCell(sheet, 6, 1, 271, "200+B1");
+ confirmCell(sheet, 7, 1, 272, "200+B12"); // this one moved
+ confirmCell(sheet, 8, 1, 273, "200+B3");
+
+ // check formulas on other sheets
+ Sheet sheet2 = wb.getSheet("Sheet2");
+ confirmCell(sheet2, 0, 0, 371, "300+Sheet1!B1");
+ confirmCell(sheet2, 1, 0, 372, "300+Sheet1!B12");
+ confirmCell(sheet2, 2, 0, 373, "300+Sheet1!B3");
+
+ confirmCell(sheet2, 11, 0, 300, "300+Sheet1!#REF!");
+
+
+ // Note - named ranges formulas have not been updated
+ }
+
+ private static void confirmRow(Sheet sheet, int rowIx, double valA, double valB, double valC,
+ String formulaA, String formulaB, String formulaC) {
+ confirmCell(sheet, rowIx, 4, valA, formulaA);
+ confirmCell(sheet, rowIx, 5, valB, formulaB);
+ confirmCell(sheet, rowIx, 6, valC, formulaC);
+ }
+
+ private static void confirmCell(Sheet sheet, int rowIx, int colIx,
+ double expectedValue, String expectedFormula) {
+ Cell cell = sheet.getRow(rowIx).getCell(colIx);
+ assertEquals(expectedValue, cell.getNumericCellValue(), 0.0);
+ assertEquals(expectedFormula, cell.getCellFormula());
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org