You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2009/02/15 21:47:37 UTC
svn commit: r744750 - in /poi/trunk/src: ooxml/java/org/apache/poi/
ooxml/java/org/apache/poi/xssf/usermodel/
ooxml/testcases/org/apache/poi/xssf/usermodel/
testcases/org/apache/poi/hssf/data/
testcases/org/apache/poi/hssf/usermodel/ testcases/org/apac...
Author: yegor
Date: Sun Feb 15 20:47:36 2009
New Revision: 744750
URL: http://svn.apache.org/viewvc?rev=744750&view=rev
Log:
refactored XSSFSheet.shiftRows to use FormulaShifter, use a common test superclass for both hssf and xssf
Added:
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestSheetShiftRows.java (with props)
poi/trunk/src/testcases/org/apache/poi/hssf/data/ForShifting.xlsx (with props)
poi/trunk/src/testcases/org/apache/poi/hssf/data/SimpleMultiCell.xlsx (with props)
poi/trunk/src/testcases/org/apache/poi/hssf/data/comments.xlsx (with props)
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java (with props)
Removed:
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/BaseTestSheetShiftRows.java
Modified:
poi/trunk/src/ooxml/java/org/apache/poi/POIXMLDocumentPart.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java
Modified: poi/trunk/src/ooxml/java/org/apache/poi/POIXMLDocumentPart.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/POIXMLDocumentPart.java?rev=744750&r1=744749&r2=744750&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/POIXMLDocumentPart.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/POIXMLDocumentPart.java Sun Feb 15 20:47:36 2009
@@ -128,6 +128,15 @@
}
/**
+ * Remove the specified part in this package.
+ */
+ public final void removeRelation(POIXMLDocumentPart part){
+ getPackagePart().removeRelationship(part.getPackageRelationship().getId());
+ getPackagePart().getPackage().removePart(part.getPackagePart());
+ relations.remove(part);
+ }
+
+ /**
* Returns the parent POIXMLDocumentPart. All parts except root have not-null parent.
*
* @return the parent POIXMLDocumentPart or <code>null</code> for the root element.
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java?rev=744750&r1=744749&r2=744750&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java Sun Feb 15 20:47:36 2009
@@ -27,7 +27,10 @@
import org.apache.poi.ss.formula.FormulaRenderer;
import org.apache.poi.xssf.model.CalculationChain;
import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.hssf.record.formula.FormulaShifter;
import org.apache.poi.hssf.record.SharedFormulaRecord;
+import org.apache.poi.util.POILogger;
+import org.apache.poi.util.POILogFactory;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
@@ -36,6 +39,7 @@
* High level representation of a row of a spreadsheet.
*/
public class XSSFRow implements Row, Comparable<XSSFRow> {
+ private static final POILogger logger = POILogFactory.getLogger(XSSFRow.class);
private static final String FILE_FORMAT_NAME = "BIFF12";
/**
@@ -406,34 +410,45 @@
* @param n the number of rows to move
*/
protected void shift(int n) {
- XSSFSheet sheet = getSheet();
- CalculationChain calcChain = sheet.getWorkbook().getCalculationChain();
int rownum = getRowNum() + n;
+ CalculationChain calcChain = sheet.getWorkbook().getCalculationChain();
+ int sheetId = (int)sheet.sheet.getSheetId();
for(Cell c : this){
XSSFCell cell = (XSSFCell)c;
//remove the reference in the calculation chain
- if(calcChain != null) calcChain.removeItem((int)sheet.sheet.getSheetId(), cell.getReference());
+ if(calcChain != null) calcChain.removeItem(sheetId, cell.getReference());
CTCell ctCell = cell.getCTCell();
String r = new CellReference(rownum, cell.getColumnIndex()).formatAsString();
ctCell.setR(r);
+ }
+ setRowNum(rownum);
+ }
+
+ protected void updateFormulasAfterCellShift(FormulaShifter shifter) {
+ for(Cell c : this){
+ XSSFCell cell = (XSSFCell)c;
+ CTCell ctCell = cell.getCTCell();
if(ctCell.isSetF()){
CTCellFormula f = ctCell.getF();
- String fmla = f.getStringValue();
- if(fmla.length() > 0) {
- String shiftedFmla = shiftFormula(fmla, n);
- f.setStringValue(shiftedFmla);
+ String formula = f.getStringValue();
+ if(formula.length() > 0) {
+ String shiftedFormula = shiftFormula(formula, shifter);
+ if (shiftedFormula != null) {
+ f.setStringValue(shiftedFormula);
+ }
}
+
if(f.isSetRef()){ //Range of cells which the formula applies to.
String ref = f.getRef();
- String shiftedRef = shiftFormula(ref, n);
- f.setRef(shiftedRef);
+ String shiftedRef = shiftFormula(ref, shifter);
+ if(shiftedRef != null) f.setRef(shiftedRef);
}
}
+
}
- setRowNum(rownum);
}
/**
@@ -443,17 +458,21 @@
* </p>
*
* @param formula the formula to shift
- * @param n the number of rows to shift
- * @return the shifted formula
+ * @param shifter the FormulaShifter object that operates on the parsed formula tokens
+ * @return the shifted formula if the formula was changed,
+ * <code>null</code> if the formula wasn't modified
*/
- private String shiftFormula(String formula, int n){
+ private String shiftFormula(String formula, FormulaShifter shifter){
XSSFSheet sheet = getSheet();
XSSFWorkbook wb = sheet.getWorkbook();
int sheetIndex = wb.getSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex);
- Ptg[] fmla = SharedFormulaRecord.convertSharedFormulas(ptgs, n, 0);
- return FormulaRenderer.toFormulaString(fpb, fmla);
+ String shiftedFmla = null;
+ if (shifter.adjustFormula(ptgs, sheetIndex)) {
+ shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
+ }
+ return shiftedFmla;
}
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java?rev=744750&r1=744749&r2=744750&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java Sun Feb 15 20:47:36 2009
@@ -25,6 +25,7 @@
import org.apache.poi.hssf.util.PaneInformation;
import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.hssf.record.formula.FormulaShifter;
import org.apache.poi.hssf.record.SharedFormulaRecord;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
@@ -60,7 +61,7 @@
* </p>
*/
public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
- private static POILogger logger = POILogFactory.getLogger(XSSFSheet.class);
+ private static final POILogger logger = POILogFactory.getLogger(XSSFSheet.class);
/**
* Column width measured as the number of characters of the maximum digit width of the
@@ -1442,6 +1443,7 @@
public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight) {
for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) {
XSSFRow row = (XSSFRow)it.next();
+ int rownum = row.getRowNum();
if (!copyRowHeight) {
row.setHeight((short)-1);
@@ -1456,11 +1458,39 @@
else if (row.getRowNum() >= startRow && row.getRowNum() <= endRow) {
row.shift(n);
}
+
+ if(sheetComments != null){
+ //TODO shift Note's anchor in the associated /xl/drawing/vmlDrawings#.vml
+ CTCommentList lst = sheetComments.getCTComments().getCommentList();
+ for (CTComment comment : lst.getCommentArray()) {
+ CellReference ref = new CellReference(comment.getRef());
+ if(ref.getRow() == rownum){
+ ref = new CellReference(rownum + n, ref.getCol());
+ comment.setRef(ref.formatAsString());
+ }
+ }
+ }
}
//rebuild the rows map
+ int sheetIndex = getWorkbook().getSheetIndex(this);
+ FormulaShifter shifter = FormulaShifter.createForRowShift(sheetIndex, startRow, endRow, n);
TreeMap<Integer, Row> map = new TreeMap<Integer, Row>();
- for(Row r : this) map.put(r.getRowNum(), r);
+ for(Row r : this) {
+ XSSFRow row = (XSSFRow)r;
+ row.updateFormulasAfterCellShift(shifter);
+ map.put(r.getRowNum(), r);
+ }
rows = map;
+
+ //update formulas on other sheets
+ for(XSSFSheet sheet : getWorkbook()) {
+ if (sheet == this) continue;
+ for(Row r : sheet) {
+ XSSFRow row = (XSSFRow)r;
+ row.updateFormulasAfterCellShift(shifter);
+ }
+ }
+
}
/**
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java?rev=744750&r1=744749&r2=744750&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java Sun Feb 15 20:47:36 2009
@@ -1068,12 +1068,22 @@
workbook.unsetDefinedNames();
}
}
+ }
+ private void saveCalculationChain(){
+ if(calcChain != null){
+ int count = calcChain.getCTCalcChain().getCArray().length;
+ if(count == 0){
+ removeRelation(calcChain);
+ calcChain = null;
+ }
+ }
}
@Override
protected void commit() throws IOException {
saveNamedRanges();
+ saveCalculationChain();
XmlOptions xmlOptions = new XmlOptions(DEFAULT_XML_OPTIONS);
xmlOptions.setSaveSyntheticDocumentElement(new QName(CTWorkbook.type.getName().getNamespaceURI(), "workbook"));
Added: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestSheetShiftRows.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestSheetShiftRows.java?rev=744750&view=auto
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestSheetShiftRows.java (added)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestSheetShiftRows.java Sun Feb 15 20:47:36 2009
@@ -0,0 +1,68 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+package org.apache.poi.xssf.usermodel;
+
+import org.apache.poi.ss.usermodel.BaseTestSheetShiftRows;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.xssf.XSSFTestDataSamples;
+
+/**
+ * @author Yegor Kozlov
+ */
+public class TestSheetShiftRows extends BaseTestSheetShiftRows {
+
+ @Override
+ protected Workbook openSampleWorkbook(String sampleFileName) {
+ return XSSFTestDataSamples.openSampleWorkbook(sampleFileName);
+ }
+
+ @Override
+ protected Workbook writeOutAndReadBack(Workbook wb) {
+ return XSSFTestDataSamples.writeOutAndReadBack(wb);
+ }
+
+ @Override
+ protected Workbook createWorkbook() {
+ return new XSSFWorkbook();
+ }
+
+ public void testShiftRows() {
+ baseTestShiftRows("SimpleMultiCell.xlsx");
+ }
+
+ public void testShiftRow() {
+ baseTestShiftRow();
+ }
+
+ public void testShiftRow0() {
+ baseTestShiftRow0();
+ }
+
+ //TODO support shifting of page breaks
+ public void $testShiftRowBreaks() {
+ baseTestShiftRowBreaks();
+ }
+
+ //TODO support shifting of comments.
+ public void $testShiftWithComments() {
+ baseTestShiftWithComments("comments.xlsx");
+ }
+
+ public void testShiftWithFormulas() {
+ baseTestShiftWithFormulas("ForShifting.xlsx");
+ }
+}
Propchange: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestSheetShiftRows.java
------------------------------------------------------------------------------
svn:executable = *
Added: poi/trunk/src/testcases/org/apache/poi/hssf/data/ForShifting.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/data/ForShifting.xlsx?rev=744750&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/data/ForShifting.xlsx
------------------------------------------------------------------------------
svn:executable = *
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/data/ForShifting.xlsx
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
Added: poi/trunk/src/testcases/org/apache/poi/hssf/data/SimpleMultiCell.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/data/SimpleMultiCell.xlsx?rev=744750&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/data/SimpleMultiCell.xlsx
------------------------------------------------------------------------------
svn:executable = *
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/data/SimpleMultiCell.xlsx
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
Added: poi/trunk/src/testcases/org/apache/poi/hssf/data/comments.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/data/comments.xlsx?rev=744750&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/data/comments.xlsx
------------------------------------------------------------------------------
svn:executable = *
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/data/comments.xlsx
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java?rev=744750&r1=744749&r2=744750&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java Sun Feb 15 20:47:36 2009
@@ -19,6 +19,7 @@
import org.apache.poi.hssf.HSSFTestDataSamples;
import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.ss.usermodel.BaseTestSheetShiftRows;
/**
* Tests row shifting capabilities.
@@ -31,7 +32,7 @@
@Override
protected Workbook openSampleWorkbook(String sampleFileName) {
- return HSSFTestDataSamples.openSampleWorkbook(sampleFileName + ".xls");
+ return HSSFTestDataSamples.openSampleWorkbook(sampleFileName);
}
@Override
@@ -46,4 +47,29 @@
protected Workbook createWorkbook() {
return new HSSFWorkbook();
}
+
+ public void testShiftRows() {
+ baseTestShiftRows("SimpleMultiCell.xls");
+ }
+
+ public void testShiftRow() {
+ baseTestShiftRow();
+ }
+
+ public void testShiftRow0() {
+ baseTestShiftRow0();
+ }
+
+ public void testShiftRowBreaks() {
+ baseTestShiftRowBreaks();
+ }
+
+ public void testShiftWithComments() {
+ baseTestShiftWithComments("comments.xls");
+ }
+
+ public void testShiftWithFormulas() {
+ baseTestShiftWithFormulas("ForShifting.xls");
+ }
+
}
Added: 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=744750&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java Sun Feb 15 20:47:36 2009
@@ -0,0 +1,276 @@
+/* ====================================================================
+ 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;
+
+/**
+ * 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 {
+ /**
+ * Override to provide HSSF / XSSF specific way for re-serialising a workbook
+ */
+ protected abstract Workbook writeOutAndReadBack(Workbook wb);
+ /**
+ * Override to provide way of loading HSSF / XSSF sample workbooks
+ * @param sampleFileName without the ".xls" or ".xlsx" suffix
+ */
+ protected abstract Workbook openSampleWorkbook(String sampleFileName);
+ /**
+ * Override to provide way of creating HSSF / XSSF workbooks
+ */
+ protected abstract Workbook createWorkbook();
+
+ /**
+ * 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.
+ *
+ * @author Shawn Laubach (slaubach at apache dot org)
+ */
+ public final void baseTestShiftRows(String sampleName){
+ // Read initial file in
+ Workbook wb = openSampleWorkbook(sampleName);
+ Sheet s = wb.getSheetAt( 0 );
+
+ // Shift the second row down 1 and write to temp file
+ s.shiftRows( 1, 1, 1 );
+
+ wb = 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 = 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 = openSampleWorkbook(sampleName);
+ s = wb.getSheetAt( 0 );
+
+ // Shift rows 3 and 4 up and write to temp file
+ s.shiftRows( 2, 3, -2 );
+ wb = 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.
+ *
+ * @author Toshiaki Kamoshida (kamoshida.toshiaki at future dot co dot jp)
+ */
+ public final void baseTestShiftRow() {
+ Workbook b = 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.
+ *
+ * @author Toshiaki Kamoshida (kamoshida.toshiaki at future dot co dot jp)
+ */
+ public final void baseTestShiftRow0() {
+ Workbook b = 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 = 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 = 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 = 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);
+ }
+
+ /**
+ * See bug #34023
+ */
+ public void baseTestShiftWithFormulas(String sampleName) {
+ Workbook wb = 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());
+ }
+}
Propchange: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java
------------------------------------------------------------------------------
svn:executable = *
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org