ASF Bugzilla – Attachment 35383 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]
patch282
0002.patch (text/plain), 15.46 KB, created by
Dragan Jovanović
on 2017-09-28 13:46:38 UTC
(
hide
)
Description:
patch282
Filename:
MIME Type:
Creator:
Dragan Jovanović
Created:
2017-09-28 13:46:38 UTC
Size:
15.46 KB
patch
obsolete
>From 0b1a207f3764657afde1c20fb3d3f42bf339b421 Mon Sep 17 00:00:00 2001 >From: zmau <drjovanovic@gmail.com> >Date: Mon, 25 Sep 2017 16:02:02 +0200 >Subject: [PATCH 2/5] 660: Add column shifting functionality to Apache POI > project > >Refactored Sheet.shiftRows() a bit, in order to make it more readable. >Refactored RowShifter, XSSFRowShifter and HSSFRowShifter classes, made shifter an attribute of them. >--- > .../poi/hssf/usermodel/helpers/HSSFRowShifter.java | 14 +++-- > .../poi/ss/usermodel/helpers/RowShifter.java | 16 ++++-- > .../org/apache/poi/xssf/usermodel/XSSFRow.java | 4 +- > .../org/apache/poi/xssf/usermodel/XSSFSheet.java | 67 ++++++++++++---------- > .../poi/xssf/usermodel/helpers/XSSFRowShifter.java | 29 +++++----- > 5 files changed, 74 insertions(+), 56 deletions(-) > >diff --git a/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java b/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java >index 88a6f9b..0c4f78b 100644 >--- a/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java >+++ b/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java >@@ -21,6 +21,7 @@ import org.apache.poi.hssf.usermodel.HSSFSheet; > import org.apache.poi.ss.formula.FormulaShifter; > import org.apache.poi.ss.formula.eval.NotImplementedException; > import org.apache.poi.ss.usermodel.Row; >+import org.apache.poi.ss.usermodel.Sheet; > import org.apache.poi.ss.usermodel.helpers.RowShifter; > import org.apache.poi.util.Internal; > import org.apache.poi.util.NotImplemented; >@@ -38,30 +39,33 @@ public final class HSSFRowShifter extends RowShifter { > public HSSFRowShifter(HSSFSheet sh) { > super(sh); > } >+ public HSSFRowShifter(Sheet sh, FormulaShifter shifter) { >+ super(sh, shifter); >+ } > > @NotImplemented >- public void updateNamedRanges(FormulaShifter shifter) { >+ public void updateNamedRanges() { > throw new NotImplementedException("HSSFRowShifter.updateNamedRanges"); > } > > @NotImplemented >- public void updateFormulas(FormulaShifter shifter) { >+ public void updateFormulas() { > throw new NotImplementedException("updateFormulas"); > } > > @Internal > @NotImplemented >- public void updateRowFormulas(Row row, FormulaShifter shifter) { >+ public void updateRowFormulas(Row row) { > throw new NotImplementedException("updateRowFormulas"); > } > > @NotImplemented >- public void updateConditionalFormatting(FormulaShifter shifter) { >+ public void updateConditionalFormatting() { > throw new NotImplementedException("updateConditionalFormatting"); > } > > @NotImplemented >- public void updateHyperlinks(FormulaShifter shifter) { >+ public void updateHyperlinks() { > throw new NotImplementedException("updateHyperlinks"); > } > >diff --git a/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java b/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java >index 680abff..5d815e6 100644 >--- a/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java >+++ b/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java >@@ -37,11 +37,17 @@ import org.apache.poi.util.POILogger; > */ > public abstract class RowShifter { > protected final Sheet sheet; >+ protected FormulaShifter shifter; > > public RowShifter(Sheet sh) { > sheet = sh; > } > >+ public RowShifter(Sheet sh, FormulaShifter shifter) { >+ sheet = sh; >+ this.shifter = shifter; >+ } >+ > /** > * Shifts, grows, or shrinks the merged regions due to a row shift. > * Merged regions that are completely overlaid by shifting will be deleted. >@@ -97,12 +103,12 @@ public abstract class RowShifter { > /** > * Updated named ranges > */ >- public abstract void updateNamedRanges(FormulaShifter shifter); >+ public abstract void updateNamedRanges(); > > /** > * Update formulas. > */ >- public abstract void updateFormulas(FormulaShifter shifter); >+ public abstract void updateFormulas(); > > /** > * Update the formulas in specified row using the formula shifting policy specified by shifter >@@ -111,9 +117,9 @@ public abstract class RowShifter { > * @param shifter the formula shifting policy > */ > @Internal >- public abstract void updateRowFormulas(Row row, FormulaShifter shifter); >+ public abstract void updateRowFormulas(Row row); > >- public abstract void updateConditionalFormatting(FormulaShifter shifter); >+ public abstract void updateConditionalFormatting(); > > /** > * Shift the Hyperlink anchors (not the hyperlink text, even if the hyperlink >@@ -122,6 +128,6 @@ public abstract class RowShifter { > * > * @param shifter the formula shifting policy > */ >- public abstract void updateHyperlinks(FormulaShifter shifter); >+ public abstract void updateHyperlinks(); > > } >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 35ed242..d79172d 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java >@@ -640,14 +640,14 @@ public class XSSFRow implements Row, Comparable<XSSFRow> { > destCell.copyCellFrom(srcCell, policy); > } > >- final XSSFRowShifter rowShifter = new XSSFRowShifter(_sheet); > final int sheetIndex = _sheet.getWorkbook().getSheetIndex(_sheet); > final String sheetName = _sheet.getWorkbook().getSheetName(sheetIndex); > final int srcRowNum = srcRow.getRowNum(); > final int destRowNum = getRowNum(); > final int rowDifference = destRowNum - srcRowNum; > final FormulaShifter shifter = FormulaShifter.createForRowCopy(sheetIndex, sheetName, srcRowNum, srcRowNum, rowDifference, SpreadsheetVersion.EXCEL2007); >- rowShifter.updateRowFormulas(this, shifter); >+ final XSSFRowShifter rowShifter = new XSSFRowShifter(_sheet, shifter); >+ rowShifter.updateRowFormulas(this); > > // Copy merged regions that are fully contained on the row > // FIXME: is this something that rowShifter could be doing? >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 b5b4e42..2348f4e 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java >@@ -2943,8 +2943,35 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { > @Override > public void shiftRows(int startRow, int endRow, final int n, boolean copyRowHeight, boolean resetOriginalRowHeight) { > XSSFVMLDrawing vml = getVMLDrawing(false); >+ removeOverwritten(vml, startRow, endRow, n); >+ doShifting(vml, startRow, endRow, n, copyRowHeight); >+ >+ >+ int sheetIndex = getWorkbook().getSheetIndex(this); >+ String sheetName = getWorkbook().getSheetName(sheetIndex); >+ FormulaShifter shifter = FormulaShifter.createForRowShift( >+ sheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL2007); >+ XSSFRowShifter rowShifter = new XSSFRowShifter(this, shifter); > >- // first remove all rows which will be overwritten >+ rowShifter.updateNamedRanges(); >+ rowShifter.updateFormulas(); >+ rowShifter.shiftMergedRegions(startRow, endRow, n); >+ rowShifter.updateConditionalFormatting(); >+ rowShifter.updateHyperlinks(); >+ >+ //rebuild the _rows map >+ Map<Integer, XSSFRow> map = new HashMap<Integer, XSSFRow>(); >+ for(XSSFRow r : _rows.values()) { >+ // Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory >+ final Integer rownumI = new Integer(r.getRowNum()); // NOSONAR >+ map.put(rownumI, r); >+ } >+ _rows.clear(); >+ _rows.putAll(map); >+ } >+ >+ // remove all rows which will be overwritten >+ private void removeOverwritten(XSSFVMLDrawing vml, int startRow, int endRow, final int n){ > for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { > XSSFRow row = (XSSFRow)it.next(); > int rownum = row.getRowNum(); >@@ -2987,10 +3014,12 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { > } > } > } >- >- // then do the actual moving and also adjust comments/rowHeight >- // we need to sort it in a way so the shifting does not mess up the structures, >- // i.e. when shifting down, start from down and go up, when shifting up, vice-versa >+ } >+ >+ // do the actual moving and also adjust comments/rowHeight >+ // we need to sort it in a way so the shifting does not mess up the structures, >+ // i.e. when shifting down, start from down and go up, when shifting up, vice-versa >+ private void doShifting(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) { >@@ -3012,7 +3041,6 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { > } > } > }); >- > > for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { > XSSFRow row = (XSSFRow)it.next(); >@@ -3045,11 +3073,9 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { > if(rownum < startRow || rownum > endRow) { > continue; > } >- > if (!copyRowHeight) { > row.setHeight((short)-1); > } >- > row.shift(n); > } > >@@ -3059,30 +3085,9 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { > for(Map.Entry<XSSFComment, Integer> entry : commentsToShift.entrySet()) { > entry.getKey().setRow(entry.getValue()); > } >- >- XSSFRowShifter rowShifter = new XSSFRowShifter(this); >- >- int sheetIndex = getWorkbook().getSheetIndex(this); >- String sheetName = getWorkbook().getSheetName(sheetIndex); >- FormulaShifter shifter = FormulaShifter.createForRowShift( >- sheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL2007); >- >- rowShifter.updateNamedRanges(shifter); >- rowShifter.updateFormulas(shifter); >- rowShifter.shiftMergedRegions(startRow, endRow, n); >- rowShifter.updateConditionalFormatting(shifter); >- rowShifter.updateHyperlinks(shifter); >- >- //rebuild the _rows map >- Map<Integer, XSSFRow> map = new HashMap<Integer, XSSFRow>(); >- for(XSSFRow r : _rows.values()) { >- // Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory >- final Integer rownumI = new Integer(r.getRowNum()); // NOSONAR >- map.put(rownumI, r); >- } >- _rows.clear(); >- _rows.putAll(map); >+ > } >+ > > private int shiftedRowNum(int startRow, int endRow, int n, int rownum) { > // no change if before any affected row >diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java >index 46f0b89..f9ad224 100644 >--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java >+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java >@@ -19,7 +19,6 @@ package org.apache.poi.xssf.usermodel.helpers; > > import java.util.ArrayList; > import java.util.List; >- > import org.apache.poi.ss.formula.FormulaParseException; > import org.apache.poi.ss.formula.FormulaParser; > import org.apache.poi.ss.formula.FormulaRenderer; >@@ -63,6 +62,10 @@ public final class XSSFRowShifter extends RowShifter { > public XSSFRowShifter(XSSFSheet sh) { > super(sh); > } >+ public XSSFRowShifter(Sheet sh, FormulaShifter shifter) { >+ super(sh, shifter); >+ } >+ > > /** > * Shift merged regions >@@ -80,7 +83,7 @@ public final class XSSFRowShifter extends RowShifter { > /** > * Updated named ranges > */ >- public void updateNamedRanges(FormulaShifter shifter) { >+ public void updateNamedRanges() { > Workbook wb = sheet.getWorkbook(); > XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb); > for (Name name : wb.getAllNames()) { >@@ -99,22 +102,22 @@ public final class XSSFRowShifter extends RowShifter { > /** > * Update formulas. > */ >- public void updateFormulas(FormulaShifter shifter) { >+ public void updateFormulas() { > //update formulas on the parent sheet >- updateSheetFormulas(sheet, shifter); >+ updateSheetFormulas(sheet); > > //update formulas on other sheets > Workbook wb = sheet.getWorkbook(); > for (Sheet sh : wb) { > if (sheet == sh) continue; >- updateSheetFormulas(sh, shifter); >+ updateSheetFormulas(sh); > } > } > >- private void updateSheetFormulas(Sheet sh, FormulaShifter shifter) { >+ private void updateSheetFormulas(Sheet sh) { > for (Row r : sh) { > XSSFRow row = (XSSFRow) r; >- updateRowFormulas(row, shifter); >+ updateRowFormulas(row); > } > } > >@@ -125,7 +128,7 @@ public final class XSSFRowShifter extends RowShifter { > * @param shifter the formula shifting policy > */ > @Internal >- public void updateRowFormulas(Row row, FormulaShifter shifter) { >+ public void updateRowFormulas(Row row) { > XSSFSheet sheet = (XSSFSheet) row.getSheet(); > for (Cell c : row) { > XSSFCell cell = (XSSFCell) c; >@@ -142,20 +145,20 @@ public final class XSSFRowShifter extends RowShifter { > int si = (int)f.getSi(); > CTCellFormula sf = sheet.getSharedFormula(si); > sf.setStringValue(shiftedFormula); >- updateRefInCTCellFormula(row, shifter, sf); >+ updateRefInCTCellFormula(row, sf); > } > } > > } > > //Range of cells which the formula applies to. >- updateRefInCTCellFormula(row, shifter, f); >+ updateRefInCTCellFormula(row, f); > } > > } > } > >- private void updateRefInCTCellFormula(Row row, FormulaShifter shifter, CTCellFormula f) { >+ private void updateRefInCTCellFormula(Row row, CTCellFormula f) { > if (f.isSetRef()) { //Range of cells which the formula applies to. > String ref = f.getRef(); > String shiftedRef = shiftFormula(row, ref, shifter); >@@ -193,7 +196,7 @@ public final class XSSFRowShifter extends RowShifter { > } > } > >- public void updateConditionalFormatting(FormulaShifter shifter) { >+ public void updateConditionalFormatting() { > XSSFSheet xsheet = (XSSFSheet) sheet; > XSSFWorkbook wb = xsheet.getWorkbook(); > int sheetIndex = wb.getSheetIndex(sheet); >@@ -260,7 +263,7 @@ public final class XSSFRowShifter extends RowShifter { > * > * @param shifter > */ >- public void updateHyperlinks(FormulaShifter shifter) { >+ public void updateHyperlinks() { > int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet); > List<? extends Hyperlink> hyperlinkList = sheet.getHyperlinkList(); > >-- >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