ASF Bugzilla – Attachment 35385 Details for
Bug 61474
Adjusting of formulas in context of column shifting
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
Remember
[x]
|
Forgot Password
Login:
[x]
[patch]
patch284
0004.patch (text/plain), 20.40 KB, created by
Dragan Jovanović
on 2017-09-28 13:48:14 UTC
(
hide
)
Description:
patch284
Filename:
MIME Type:
Creator:
Dragan Jovanović
Created:
2017-09-28 13:48:14 UTC
Size:
20.40 KB
patch
obsolete
>From 089aa3c86fcc7936ebd10c6679f6b9b38c11f091 Mon Sep 17 00:00:00 2001 >From: =?UTF-8?q?Dragan=20Jovanovi=C4=87?= <drjovanovic@gmail.com> >Date: Wed, 27 Sep 2017 16:59:23 +0200 >Subject: [PATCH 4/5] 660 > >work in progress >--- > .project | 2 +- > .../org/apache/poi/xssf/usermodel/XSSFCell.java | 18 +++ > .../org/apache/poi/xssf/usermodel/XSSFRow.java | 16 +-- > .../org/apache/poi/xssf/usermodel/XSSFSheet.java | 9 +- > .../apache/poi/xssf/usermodel/XSSFVMLDrawing.java | 2 +- > .../xssf/usermodel/helpers/XSSFColumnShifter.java | 96 ++++++++++++--- > .../usermodel/helpers/XSSFColumnShifterTest.java | 137 +++++++++++++++++++++ > 7 files changed, 247 insertions(+), 33 deletions(-) > create mode 100644 src/testcases/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifterTest.java > >diff --git a/.project b/.project >index 274051f..f21cb4b 100644 >--- a/.project >+++ b/.project >@@ -1,6 +1,6 @@ > <?xml version="1.0" encoding="UTF-8"?> > <projectDescription> >- <name>ApachePOI</name> >+ <name>local ApachePOI</name> > <comment></comment> > <projects> > </projects> >diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java >index 7afb389..93c570a 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java >@@ -47,6 +47,7 @@ import org.apache.poi.util.Beta; > import org.apache.poi.util.Internal; > import org.apache.poi.util.LocaleUtil; > import org.apache.poi.util.Removal; >+import org.apache.poi.xssf.model.CalculationChain; > import org.apache.poi.xssf.model.SharedStringsTable; > import org.apache.poi.xssf.model.StylesTable; > import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; >@@ -1327,4 +1328,21 @@ public final class XSSFCell implements Cell { > "You cannot change part of an array."; > notifyArrayFormulaChanging(msg); > } >+ >+ /*** >+ * Moved from XSSFRow.shift(). Not sure what is purpose. >+ */ >+ public void updateCellReferencesForShifting(String msg){ >+ if(isPartOfArrayFormulaGroup()) >+ notifyArrayFormulaChanging(msg); >+ CalculationChain calcChain = getSheet().getWorkbook().getCalculationChain(); >+ int sheetId = (int)getSheet().sheet.getSheetId(); >+ >+ //remove the reference in the calculation chain >+ if(calcChain != null) calcChain.removeItem(sheetId, getReference()); >+ >+ CTCell ctCell = getCTCell(); >+ String r = new CellReference(getRowIndex(), getColumnIndex()).formatAsString(); >+ ctCell.setR(r); >+ } > } >diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java >index 41a6b1e..9919fe5 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java >@@ -575,23 +575,11 @@ public class XSSFRow implements Row, Comparable<XSSFRow> { > */ > protected void shift(int n) { > int rownum = getRowNum() + n; >- CalculationChain calcChain = _sheet.getWorkbook().getCalculationChain(); >- int sheetId = (int)_sheet.sheet.getSheetId(); > String msg = "Row[rownum="+getRowNum()+"] contains cell(s) included in a multi-cell array formula. " + > "You cannot change part of an array."; > for(Cell c : this){ >- XSSFCell cell = (XSSFCell)c; >- if(cell.isPartOfArrayFormulaGroup()){ >- cell.notifyArrayFormulaChanging(msg); >- } >- >- //remove the reference in the calculation chain >- if(calcChain != null) calcChain.removeItem(sheetId, cell.getReference()); >- >- CTCell ctCell = cell.getCTCell(); >- String r = new CellReference(rownum, cell.getColumnIndex()).formatAsString(); >- ctCell.setR(r); >- } >+ ((XSSFCell)c).updateCellReferencesForShifting(msg); >+ } > setRowNum(rownum); > } > >diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java >index 4b4f294..c444469 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java >@@ -88,6 +88,7 @@ import org.apache.poi.xssf.model.CommentsTable; > import org.apache.poi.xssf.usermodel.XSSFPivotTable.PivotTableReferenceConfigurator; > import org.apache.poi.xssf.usermodel.helpers.ColumnHelper; > import org.apache.poi.xssf.usermodel.helpers.XSSFColumnShifter; >+import org.apache.poi.xssf.usermodel.helpers.XSSFFormulaShiftingManager; > import org.apache.poi.xssf.usermodel.helpers.XSSFIgnoredErrorHelper; > import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter; > import org.apache.xmlbeans.XmlCursor; >@@ -2975,13 +2976,15 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { > public void shiftColumns(int startColumn, int endColumn, final int n) { > XSSFVMLDrawing vml = getVMLDrawing(false); > // removeOverwritten(vml, startColumn, endColumn, n); >- //doShifting(vml, startColumn, endColumn, n, false); >+ // doShifting(vml, startColumn, endColumn, n, false); > > FormulaShifter shifter = FormulaShifter.createForItemShift(this, false, startColumn, endColumn, n); > XSSFColumnShifter columnShifter = new XSSFColumnShifter(this, shifter); >- >+ columnShifter.shiftColumns(startColumn, endColumn, n); >+ //XSSFFormulaShiftingManager shiftingManager = new XSSFFormulaShiftingManager(this, shifter); >+ //shiftingManager.updateFormulas(); >+ > /*columnShifter.updateNamedRanges(); >- columnShifter.updateFormulas(); > columnShifter.shiftMergedRegions(startColumn, startColumn, n); > columnShifter.updateConditionalFormatting(); > columnShifter.updateHyperlinks();*/ >diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java >index 7a7f02f..393f146 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java >@@ -265,7 +265,7 @@ public final class XSSFVMLDrawing extends POIXMLDocumentPart { > * > * @return the comment shape or <code>null</code> > */ >- protected CTShape findCommentShape(int row, int col){ >+ public CTShape findCommentShape(int row, int col){ > for(XmlObject itm : _items){ > if(itm instanceof CTShape){ > CTShape sh = (CTShape)itm; >diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifter.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifter.java >index 073a648..fec5455 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifter.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifter.java >@@ -1,13 +1,26 @@ > package org.apache.poi.xssf.usermodel.helpers; > >+import java.util.Comparator; >+import java.util.Iterator; >+import java.util.Map; >+import java.util.SortedMap; >+import java.util.TreeMap; >+ > import org.apache.poi.ss.formula.FormulaShifter; > import org.apache.poi.ss.usermodel.CellType; > import org.apache.poi.ss.usermodel.Row; > import org.apache.poi.ss.usermodel.Sheet; > import org.apache.poi.ss.usermodel.helpers.ColumnShifter; >+import org.apache.poi.ss.util.CellReference; > import org.apache.poi.util.POILogFactory; > import org.apache.poi.util.POILogger; >+import org.apache.poi.xssf.model.CommentsTable; > import org.apache.poi.xssf.usermodel.XSSFCell; >+import org.apache.poi.xssf.usermodel.XSSFComment; >+import org.apache.poi.xssf.usermodel.XSSFRow; >+import org.apache.poi.xssf.usermodel.XSSFVMLDrawing; >+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment; >+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCommentList; > > public class XSSFColumnShifter extends ColumnShifter{ > >@@ -31,6 +44,7 @@ public class XSSFColumnShifter extends ColumnShifter{ > shiftColumnsRight(); > //else if(shiftStep < 0) > //shiftColumnsLeft(); >+ formulaShiftingManager.updateFormulas(); > } > /** > * Inserts shiftStep empty columns at firstShiftColumnIndex-th position, and shifts rest columns to the right >@@ -45,9 +59,6 @@ public class XSSFColumnShifter extends ColumnShifter{ > XSSFCell oldCell = (XSSFCell)row.getCell(columnIndex); > if(oldCell == null) > continue; >- if(oldCell.getCellTypeEnum() == CellType.FORMULA) // recalculate formula >- formulaShiftingManager.updateCellFormula(row, oldCell); >- > if(columnIndex >= firstShiftColumnIndex){ // shift existing cell > org.apache.poi.ss.usermodel.Cell newCell = null; > newCell = row.createCell(columnIndex + shiftStep, oldCell.getCellTypeEnum()); >@@ -59,14 +70,13 @@ public class XSSFColumnShifter extends ColumnShifter{ > } > } > } >- formulaShiftingManager.updateFormulas(); > } >- /*private void shiftColumnsLeft(){ >+ private void shiftColumnsLeft(){ > for(int rowNo = 0; rowNo <= shiftingSheet.getLastRowNum(); rowNo++) > { >- Row row = shiftingSheet.getRow(rowNo); >- for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++){ // process cells backwards, because of shifting >- org.apache.poi.ss.usermodel.Cell oldCell = row.getCell(columnIndex); >+ XSSFRow row = (XSSFRow)shiftingSheet.getRow(rowNo); >+ for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++){ >+ XSSFCell oldCell = (XSSFCell)row.getCell(columnIndex); > if(columnIndex >= firstShiftColumnIndex + shiftStep && columnIndex < row.getLastCellNum() - shiftStep){ // shift existing cell > org.apache.poi.ss.usermodel.Cell newCell = null; > newCell = row.getCell(columnIndex - shiftStep); >@@ -74,8 +84,6 @@ public class XSSFColumnShifter extends ColumnShifter{ > if(newCell != null){ > oldCell.setCellType(newCell.getCellType()); > cloneCellValue(newCell, oldCell); >- if(oldCell.getCellTypeEnum() == CellType.FORMULA) // recalculate formula >- columnFormulaShifter.shiftFormula(oldCell); > } > else { > oldCell.setCellType(CellType.STRING); >@@ -87,8 +95,6 @@ public class XSSFColumnShifter extends ColumnShifter{ > if(newCell != null){ > oldCell.setCellType(newCell.getCellType()); > cloneCellValue(newCell, oldCell); >- if(oldCell.getCellTypeEnum() == CellType.FORMULA) // recalculate formula >- columnFormulaShifter.shiftFormula(oldCell); > } > else { > oldCell.setCellType(CellType.STRING); >@@ -98,8 +104,7 @@ public class XSSFColumnShifter extends ColumnShifter{ > } > } > } >- columnFormulaShifter.processFormulasOnOtherSheets(); >- }*/ >+ } > > public static void cloneCellValue(org.apache.poi.ss.usermodel.Cell oldCell, org.apache.poi.ss.usermodel.Cell newCell) { > switch (oldCell.getCellTypeEnum()) { >@@ -123,4 +128,67 @@ public class XSSFColumnShifter extends ColumnShifter{ > } > } > >+ private void processComments(CommentsTable sheetComments, XSSFVMLDrawing vml, int startRow, int endRow, final int n, boolean copyRowHeight){ >+ SortedMap<XSSFComment, Integer> commentsToShift = new TreeMap<XSSFComment, Integer>(new Comparator<XSSFComment>() { >+ @Override >+ public int compare(XSSFComment o1, XSSFComment o2) { >+ int column1Index = o1.getColumn(); >+ int column2Index = o2.getColumn(); >+ >+ if(column1Index == column2Index) >+ return o1.hashCode() - o2.hashCode(); >+ >+ if(n > 0) // when shifting right, sort higher column-values first >+ return column1Index < column2Index ? 1 : -1; >+ else // sort lower-column values first when shifting left >+ return column1Index > column2Index ? 1 : -1; >+ } >+ }); >+ >+ for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { >+ XSSFRow row = (XSSFRow)it.next(); >+ int rownum = row.getRowNum(); >+ >+ if(sheetComments != null){ >+ // calculate the new rownum >+ int newrownum = shiftedRowNum(startRow, endRow, n, rownum); >+ >+ // is there a change necessary for the current row? >+ if(newrownum != rownum) { >+ CTCommentList lst = sheetComments.getCTComments().getCommentList(); >+ for (CTComment comment : lst.getCommentArray()) { >+ String oldRef = comment.getRef(); >+ CellReference ref = new CellReference(oldRef); >+ >+ // is this comment part of the current row? >+ if(ref.getRow() == rownum) { >+ XSSFComment xssfComment = new XSSFComment(sheetComments, comment, >+ vml == null ? null : vml.findCommentShape(rownum, ref.getCol())); >+ >+ // we should not perform the shifting right here as we would then find >+ // already shifted comments and would shift them again... >+ commentsToShift.put(xssfComment, newrownum); >+ } >+ } >+ } >+ } >+ >+ if(rownum < startRow || rownum > endRow) { >+ continue; >+ } >+ if (!copyRowHeight) { >+ row.setHeight((short)-1); >+ } >+ row.shift(n); >+ } >+ >+ // adjust all the affected comment-structures now >+ // the Map is sorted and thus provides them in the order that we need here, >+ // i.e. from down to up if shifting down, vice-versa otherwise >+ for(Map.Entry<XSSFComment, Integer> entry : commentsToShift.entrySet()) { >+ entry.getKey().setRow(entry.getValue()); >+ } >+ >+ } >+ > } >diff --git a/src/testcases/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifterTest.java b/src/testcases/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifterTest.java >new file mode 100644 >index 0000000..e86cce7 >--- /dev/null >+++ b/src/testcases/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifterTest.java >@@ -0,0 +1,137 @@ >+package org.apache.poi.xssf.usermodel.helpers; >+ >+import static org.junit.Assert.assertEquals; >+ >+import org.apache.poi.ss.usermodel.*; >+import org.apache.poi.xssf.usermodel.*; >+import org.junit.Before; >+import org.junit.Test; >+import org.slf4j.Logger; >+import org.slf4j.LoggerFactory; >+ >+public class XSSFColumnShifterTest { >+ >+ private static Logger log = LoggerFactory.getLogger(XSSFColumnShifterTest.class+"_T"); >+ private XSSFSheet sheet1, sheet2; >+ private Workbook wb07; >+ >+ @Before >+ public void init(){ >+ wb07 = new XSSFWorkbook(); >+ sheet1 = (XSSFSheet)wb07.createSheet("sheet1"); >+ XSSFRow row = sheet1.createRow(0); >+ row.createCell(0, CellType.NUMERIC).setCellValue(0); >+ row.createCell(1, CellType.NUMERIC).setCellValue(1); >+ XSSFCell c1 =row.createCell(2, CellType.NUMERIC); >+ c1.setCellValue(2); >+ c1.setCellComment("this is c1"); >+ >+ row = sheet1.createRow(1); >+ row.createCell(0, CellType.NUMERIC).setCellValue(0.1); >+ row.createCell(1, CellType.NUMERIC).setCellValue(1.1); >+ row.createCell(2, CellType.NUMERIC).setCellValue(2.1); >+ row = sheet1.createRow(2); >+ row.createCell(0, CellType.NUMERIC).setCellValue(0.2); >+ row.createCell(1, CellType.NUMERIC).setCellValue(1.2); >+ row.createCell(2, CellType.NUMERIC).setCellValue(2.2); >+ row = sheet1.createRow(3); >+ row.createCell(0, CellType.FORMULA).setCellFormula("A2*B3"); >+ row.createCell(1, CellType.NUMERIC).setCellValue(1.3); >+ row.createCell(2, CellType.FORMULA).setCellFormula("B1-B3"); >+ row = sheet1.createRow(4); >+ row.createCell(0, CellType.FORMULA).setCellFormula("SUM(C1:C4)"); >+ row.createCell(1, CellType.FORMULA).setCellFormula("SUM(A3:C3)"); >+ row.createCell(2, CellType.FORMULA).setCellFormula("$C1+C$2"); >+ row = sheet1.createRow(5); >+ row.createCell(1, CellType.NUMERIC).setCellValue(1.5); >+/* >+ sheet2 = (XSSFSheet)wb07.createSheet("sheet2"); >+ row = sheet2.createRow(0); >+ row.createCell(0, CellType.NUMERIC).setCellValue(10); >+ row.createCell(1, CellType.NUMERIC).setCellValue(11); >+ row.createCell(2, CellType.FORMULA).setCellFormula("SUM(Sheet1!B3:C3)"); >+ row = sheet2.createRow(1); >+ row.createCell(0, CellType.NUMERIC).setCellValue(21); >+ row.createCell(1, CellType.NUMERIC).setCellValue(22); >+ row.createCell(2, CellType.NUMERIC).setCellValue(23); >+ row = sheet2.createRow(2); >+ row.createCell(0, CellType.FORMULA).setCellFormula("Sheet1!A4+Sheet1!C2+A2"); >+ row.createCell(1, CellType.FORMULA).setCellFormula("SUM(Sheet1!A3:$C3)"); >+ row = sheet2.createRow(3); >+ row.createCell(0, CellType.STRING).setCellValue("dummy"); >+ */ >+ writeSheetToLog(sheet1); >+ } >+ >+ @Test >+ public void testInsertRow() { >+ sheet1.shiftRows(1, sheet1.getLastRowNum(), 1); >+ writeSheetToLog(sheet1); >+ String formulaA4 = sheet1.getRow(4).getCell(0).getCellFormula(); >+ assertEquals("A3*B4", formulaA4); >+ String formulaC4 = sheet1.getRow(4).getCell(2).getCellFormula(); >+ assertEquals("B1-B4", formulaC4); >+ String formulaB5 = sheet1.getRow(5).getCell(1).getCellFormula(); >+ assertEquals("SUM(A4:C4)", formulaB5); >+ String formulaC6 = sheet1.getRow(5).getCell(2).getCellFormula(); >+ assertEquals("$C1+C$3", formulaC6); >+ } >+ >+ @Test >+ public void testInsertOneColumn() { >+ sheet1.shiftColumns(1, 2, 1); >+ writeSheetToLog(sheet1); >+ String formulaA4 = sheet1.getRow(3).getCell(0).getCellFormula(); >+ assertEquals("A2*C3", formulaA4); >+ String formulaC4 = sheet1.getRow(3).getCell(3).getCellFormula(); >+ assertEquals("C1-C3", formulaC4); >+ String formulaB5 = sheet1.getRow(4).getCell(2).getCellFormula(); >+ assertEquals("SUM(A3:D3)", formulaB5); >+ String formulaD5 = sheet1.getRow(4).getCell(3).getCellFormula(); //$C1+C$2 >+ assertEquals("$D1+D$2", formulaD5); >+ >+ String newb5Empty = sheet1.getRow(4).getCell(1).getStringCellValue(); >+ assertEquals(newb5Empty, ""); >+ } >+ >+ @Test >+ public void testInsertTwoColumns(){ >+ sheet1.shiftColumns(1, 2, 2); >+ String formulaA4 = sheet1.getRow(3).getCell(0).getCellFormula(); >+ assertEquals("A2*D3", formulaA4); >+ String formulaD4 = sheet1.getRow(3).getCell(4).getCellFormula(); >+ assertEquals("D1-D3", formulaD4); >+ String formulaD5 = sheet1.getRow(4).getCell(3).getCellFormula(); >+ assertEquals("SUM(A3:E3)", formulaD5); >+ >+ String b5Empty = sheet1.getRow(4).getCell(1).getStringCellValue(); >+ assertEquals(b5Empty, ""); >+ Object c6Null = sheet1.getRow(5).getCell(2); // null cell A5 is shifted for 2 columns, so now c5 should be null >+ assertEquals(c6Null, null); >+ } >+ >+ public static void writeSheetToLog(Sheet sheet){ >+ int rowIndex = sheet.getFirstRowNum(); >+ while(rowIndex <= sheet.getLastRowNum()){ >+ Row row = sheet.getRow(rowIndex); >+ if(row == null) >+ log.trace("null row!"); >+ else log.trace(String.format("%1$12s; %2$12s; %3$12s; %4$12s; %5$12s; %6$12s; %7$12s; %8$12s; %9$12s; %10$12s; %11$12s", >+ row.getCell(0) != null ? row.getCell(0) : "null", >+ row.getCell(1) != null ? row.getCell(1) : "null", >+ row.getCell(2) != null ? row.getCell(2) : "null", >+ row.getCell(3) != null ? row.getCell(3) : "null", >+ row.getCell(4) != null ? row.getCell(4) : "null", >+ row.getCell(5) != null ? row.getCell(5) : "null", >+ row.getCell(6) != null ? row.getCell(6) : "null", >+ row.getCell(7) != null ? row.getCell(7) : "null", >+ row.getCell(8) != null ? row.getCell(8) : "null", >+ row.getCell(9) != null ? row.getCell(9) : "null", >+ row.getCell(10) != null ? row.getCell(10) : "null" >+ )); >+ rowIndex++; >+ } >+ log.trace(""); >+ } >+ >+} >-- >2.13.1.windows.2 >
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
Attachments on
bug 61474
:
35287
|
35294
|
35382
|
35383
|
35384
|
35385
|
35386
|
35417
|
35454