View | Details | Raw Unified | Return to bug 61474
Collapse All | Expand All

(-)a/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java (-11 lines)
Lines 105-119 public abstract class RowShifter { Link Here
105
     */
105
     */
106
    public abstract void updateNamedRanges();
106
    public abstract void updateNamedRanges();
107
107
108
    public abstract void updateConditionalFormatting();
109
    
110
    /**
111
     * Shift the Hyperlink anchors (not the hyperlink text, even if the hyperlink
112
     * is of type LINK_DOCUMENT and refers to a cell that was shifted). Hyperlinks
113
     * do not track the content they point to.
114
     *
115
     * @param shifter the formula shifting policy
116
     */
117
    public abstract void updateHyperlinks();
118
119
}
108
}
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (-1 / +1 lines)
Lines 1330-1336 public final class XSSFCell implements Cell { Link Here
1330
    }
1330
    }
1331
    
1331
    
1332
    /***
1332
    /***
1333
     * Moved from XSSFRow.shift(). Not sure what is purpose. 
1333
     * Moved from XSSFRow.shift(). Not sure what is purpose of method. 
1334
     */
1334
     */
1335
    public void updateCellReferencesForShifting(String msg){
1335
    public void updateCellReferencesForShifting(String msg){
1336
        if(isPartOfArrayFormulaGroup())
1336
        if(isPartOfArrayFormulaGroup())
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java (-2 / +2 lines)
Lines 35-41 import org.apache.poi.util.Beta; Link Here
35
import org.apache.poi.util.Internal;
35
import org.apache.poi.util.Internal;
36
import org.apache.poi.xssf.model.CalculationChain;
36
import org.apache.poi.xssf.model.CalculationChain;
37
import org.apache.poi.xssf.model.StylesTable;
37
import org.apache.poi.xssf.model.StylesTable;
38
import org.apache.poi.xssf.usermodel.helpers.XSSFFormulaShiftingManager;
38
import org.apache.poi.xssf.usermodel.helpers.XSSFShiftingManager;
39
import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;
39
import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;
40
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
40
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
41
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;
41
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;
Lines 635-641 public class XSSFRow implements Row, Comparable<XSSFRow> { Link Here
635
            final int destRowNum = getRowNum();
635
            final int destRowNum = getRowNum();
636
            final int rowDifference = destRowNum - srcRowNum;
636
            final int rowDifference = destRowNum - srcRowNum;
637
            final FormulaShifter shifter = FormulaShifter.createForRowCopy(sheetIndex, sheetName, srcRowNum, srcRowNum, rowDifference, SpreadsheetVersion.EXCEL2007);
637
            final FormulaShifter shifter = FormulaShifter.createForRowCopy(sheetIndex, sheetName, srcRowNum, srcRowNum, rowDifference, SpreadsheetVersion.EXCEL2007);
638
            final XSSFFormulaShiftingManager formulaShiftingManager = new XSSFFormulaShiftingManager(_sheet, shifter); 
638
            final XSSFShiftingManager formulaShiftingManager = new XSSFShiftingManager(_sheet, shifter); 
639
            formulaShiftingManager.updateRowFormulas(this);
639
            formulaShiftingManager.updateRowFormulas(this);
640
640
641
            // Copy merged regions that are fully contained on the row
641
            // Copy merged regions that are fully contained on the row
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (-9 / +11 lines)
Lines 88-94 import org.apache.poi.xssf.model.CommentsTable; Link Here
88
import org.apache.poi.xssf.usermodel.XSSFPivotTable.PivotTableReferenceConfigurator;
88
import org.apache.poi.xssf.usermodel.XSSFPivotTable.PivotTableReferenceConfigurator;
89
import org.apache.poi.xssf.usermodel.helpers.ColumnHelper;
89
import org.apache.poi.xssf.usermodel.helpers.ColumnHelper;
90
import org.apache.poi.xssf.usermodel.helpers.XSSFColumnShifter;
90
import org.apache.poi.xssf.usermodel.helpers.XSSFColumnShifter;
91
import org.apache.poi.xssf.usermodel.helpers.XSSFFormulaShiftingManager;
91
import org.apache.poi.xssf.usermodel.helpers.XSSFShiftingManager;
92
import org.apache.poi.xssf.usermodel.helpers.XSSFIgnoredErrorHelper;
92
import org.apache.poi.xssf.usermodel.helpers.XSSFIgnoredErrorHelper;
93
import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;
93
import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;
94
import org.apache.xmlbeans.XmlCursor;
94
import org.apache.xmlbeans.XmlCursor;
Lines 2956-2965 public class XSSFSheet extends POIXMLDocumentPart implements Sheet { Link Here
2956
        XSSFRowShifter rowShifter = new XSSFRowShifter(this, shifter);
2956
        XSSFRowShifter rowShifter = new XSSFRowShifter(this, shifter);
2957
2957
2958
        rowShifter.updateNamedRanges();
2958
        rowShifter.updateNamedRanges();
2959
        rowShifter.updateFormulas();
2960
        rowShifter.shiftMergedRegions(startRow, endRow, n);
2959
        rowShifter.shiftMergedRegions(startRow, endRow, n);
2961
        rowShifter.updateConditionalFormatting();
2960
2962
        rowShifter.updateHyperlinks();
2961
        XSSFShiftingManager shiftingManager = new XSSFShiftingManager(this, shifter);
2962
        shiftingManager.updateFormulas();
2963
        shiftingManager.updateConditionalFormatting();
2964
        shiftingManager.updateHyperlinks();
2963
2965
2964
        //rebuild the _rows map
2966
        //rebuild the _rows map
2965
        Map<Integer, XSSFRow> map = new HashMap<Integer, XSSFRow>();
2967
        Map<Integer, XSSFRow> map = new HashMap<Integer, XSSFRow>();
Lines 2981-2993 public class XSSFSheet extends POIXMLDocumentPart implements Sheet { Link Here
2981
        FormulaShifter shifter = FormulaShifter.createForItemShift(this, false, startColumn, endColumn, n);
2983
        FormulaShifter shifter = FormulaShifter.createForItemShift(this, false, startColumn, endColumn, n);
2982
        XSSFColumnShifter columnShifter = new XSSFColumnShifter(this, shifter);
2984
        XSSFColumnShifter columnShifter = new XSSFColumnShifter(this, shifter);
2983
        columnShifter.shiftColumns(startColumn, endColumn, n);
2985
        columnShifter.shiftColumns(startColumn, endColumn, n);
2984
        //XSSFFormulaShiftingManager shiftingManager = new XSSFFormulaShiftingManager(this, shifter);
2986
        XSSFShiftingManager shiftingManager = new XSSFShiftingManager(this, shifter);
2985
        //shiftingManager.updateFormulas();
2987
        shiftingManager.updateFormulas();
2988
        shiftingManager.updateConditionalFormatting();
2989
        shiftingManager.updateHyperlinks();
2986
        
2990
        
2987
        /*columnShifter.updateNamedRanges();
2991
        /*columnShifter.updateNamedRanges();
2988
        columnShifter.shiftMergedRegions(startColumn, startColumn, n);
2992
        columnShifter.shiftMergedRegions(startColumn, startColumn, n);*/
2989
        columnShifter.updateConditionalFormatting();
2990
        columnShifter.updateHyperlinks();*/
2991
2993
2992
        //rebuild the _rows map
2994
        //rebuild the _rows map
2993
        Map<Integer, XSSFRow> map = new HashMap<Integer, XSSFRow>();
2995
        Map<Integer, XSSFRow> map = new HashMap<Integer, XSSFRow>();
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifter.java (-67 / +4 lines)
Lines 30-40 public class XSSFColumnShifter extends ColumnShifter{ Link Here
30
    private int lastShiftColumnIndex; 
30
    private int lastShiftColumnIndex; 
31
    private int shiftStep;
31
    private int shiftStep;
32
    
32
    
33
    private XSSFFormulaShiftingManager formulaShiftingManager;
33
    private XSSFShiftingManager formulaShiftingManager;
34
34
35
    public XSSFColumnShifter(Sheet sh, FormulaShifter shifter) {
35
    public XSSFColumnShifter(Sheet sh, FormulaShifter shifter) {
36
        super(sh, shifter);
36
        super(sh, shifter);
37
        formulaShiftingManager = new XSSFFormulaShiftingManager(sh, shifter);
37
        formulaShiftingManager = new XSSFShiftingManager(sh, shifter);
38
    }
38
    }
39
39
40
	public void shiftColumns(int firstShiftColumnIndex, int lastShiftColumnIndex, int step){
40
	public void shiftColumns(int firstShiftColumnIndex, int lastShiftColumnIndex, int step){
Lines 42-49 public class XSSFColumnShifter extends ColumnShifter{ Link Here
42
		this.shiftStep = step;
42
		this.shiftStep = step;
43
		if(shiftStep > 0)
43
		if(shiftStep > 0)
44
			shiftColumnsRight();
44
			shiftColumnsRight();
45
		//else if(shiftStep < 0)
45
		else if(shiftStep < 0)
46
			//shiftColumnsLeft();
46
			shiftColumnsLeft();
47
	      formulaShiftingManager.updateFormulas();
47
	      formulaShiftingManager.updateFormulas();
48
	}
48
	}
49
	/**
49
	/**
Lines 128-194 public class XSSFColumnShifter extends ColumnShifter{ Link Here
128
	    }
128
	    }
129
	}
129
	}
130
130
131
    private void processComments(CommentsTable sheetComments, XSSFVMLDrawing vml, int startRow, int endRow, final int n, boolean copyRowHeight){
132
        SortedMap<XSSFComment, Integer> commentsToShift = new TreeMap<XSSFComment, Integer>(new Comparator<XSSFComment>() {
133
            @Override
134
            public int compare(XSSFComment o1, XSSFComment o2) {
135
                int column1Index = o1.getColumn();
136
                int column2Index = o2.getColumn();
137
                
138
                if(column1Index == column2Index) 
139
                    return o1.hashCode() - o2.hashCode();
140
141
                if(n > 0) // when shifting right, sort higher column-values first 
142
                    return column1Index < column2Index ? 1 : -1;
143
                else     // sort lower-column values first when shifting left
144
                    return column1Index > column2Index ? 1 : -1;
145
            }
146
        });
147
        
148
        for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) {
149
            XSSFRow row = (XSSFRow)it.next();
150
            int rownum = row.getRowNum();
151
152
            if(sheetComments != null){
153
                // calculate the new rownum
154
                int newrownum = shiftedRowNum(startRow, endRow, n, rownum);
155
                
156
                // is there a change necessary for the current row?
157
                if(newrownum != rownum) {
158
                    CTCommentList lst = sheetComments.getCTComments().getCommentList();
159
                    for (CTComment comment : lst.getCommentArray()) {
160
                        String oldRef = comment.getRef();
161
                        CellReference ref = new CellReference(oldRef);
162
                        
163
                        // is this comment part of the current row?
164
                        if(ref.getRow() == rownum) {
165
                            XSSFComment xssfComment = new XSSFComment(sheetComments, comment,
166
                                    vml == null ? null : vml.findCommentShape(rownum, ref.getCol()));
167
                            
168
                            // we should not perform the shifting right here as we would then find
169
                            // already shifted comments and would shift them again...
170
                            commentsToShift.put(xssfComment, newrownum);
171
                        }
172
                    }
173
                }
174
            }
175
176
            if(rownum < startRow || rownum > endRow) {
177
                continue;
178
            }
179
            if (!copyRowHeight) {
180
                row.setHeight((short)-1);
181
            }
182
            row.shift(n);
183
        }
184
        
185
        // adjust all the affected comment-structures now
186
        // the Map is sorted and thus provides them in the order that we need here, 
187
        // i.e. from down to up if shifting down, vice-versa otherwise
188
        for(Map.Entry<XSSFComment, Integer> entry : commentsToShift.entrySet()) {
189
            entry.getKey().setRow(entry.getValue());
190
        }
191
        
192
    }
193
194
}
131
}
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFFormulaShiftingManager.java (-130 lines)
Lines 1-130 Link Here
1
package org.apache.poi.xssf.usermodel.helpers;
2
3
import org.apache.poi.ss.formula.FormulaParseException;
4
import org.apache.poi.ss.formula.FormulaParser;
5
import org.apache.poi.ss.formula.FormulaRenderer;
6
import org.apache.poi.ss.formula.FormulaShifter;
7
import org.apache.poi.ss.formula.FormulaType;
8
import org.apache.poi.ss.formula.ptg.Ptg;
9
import org.apache.poi.ss.usermodel.Cell;
10
import org.apache.poi.ss.usermodel.Row;
11
import org.apache.poi.ss.usermodel.Sheet;
12
import org.apache.poi.ss.usermodel.Workbook;
13
import org.apache.poi.util.Internal;
14
import org.apache.poi.util.POILogFactory;
15
import org.apache.poi.util.POILogger;
16
import org.apache.poi.xssf.usermodel.XSSFCell;
17
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
18
import org.apache.poi.xssf.usermodel.XSSFRow;
19
import org.apache.poi.xssf.usermodel.XSSFSheet;
20
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
21
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
22
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
23
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
24
25
public class XSSFFormulaShiftingManager {
26
27
    private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);
28
29
    protected final Sheet shiftingSheet;
30
    protected FormulaShifter shifter;
31
32
    public XSSFFormulaShiftingManager(Sheet shiftingSheet, FormulaShifter shifter){
33
    	this.shiftingSheet = shiftingSheet;
34
        this.shifter = shifter;
35
	}
36
    
37
    public void updateFormulas() {
38
        //update formulas on the parent sheet
39
        updateSheetFormulas(shiftingSheet);
40
41
        //update formulas on other sheets
42
        Workbook wb = shiftingSheet.getWorkbook();
43
        for (Sheet sh : wb) {
44
            if (shiftingSheet == sh) continue;
45
            updateSheetFormulas(sh);
46
        }
47
    }
48
49
    private void updateSheetFormulas(Sheet sh) {
50
        for (Row r : sh) {
51
            XSSFRow row = (XSSFRow) r;
52
            updateRowFormulas(row);
53
        }
54
    }
55
56
    /**
57
     * Update the formulas in specified row using the formula shifting policy specified by shifter
58
     *
59
     * @param row the row to update the formulas on
60
     * @param shifter the formula shifting policy
61
     */
62
    @Internal
63
    public void updateRowFormulas(Row row) {
64
        for (Cell c : row) {
65
           	updateCellFormula(row, (XSSFCell) c);
66
        }
67
    }
68
    
69
    public void updateCellFormula(Row row, XSSFCell cell){
70
        CTCell ctCell = cell.getCTCell();
71
        if (ctCell.isSetF()) {
72
            CTCellFormula f = ctCell.getF();
73
            String formula = f.getStringValue();
74
            if (formula.length() > 0) {
75
                String shiftedFormula = shiftFormula(row, formula);
76
                if (shiftedFormula != null) {
77
                    f.setStringValue(shiftedFormula);
78
                    if(f.getT() == STCellFormulaType.SHARED){
79
                        int si = (int)f.getSi();
80
                        XSSFSheet sheet = (XSSFSheet) row.getSheet();
81
                        CTCellFormula sf = sheet.getSharedFormula(si);
82
                        sf.setStringValue(shiftedFormula);
83
                        updateRefInCTCellFormula(row, sf);
84
                    }
85
                }
86
            }
87
            //Range of cells which the formula applies to.
88
            updateRefInCTCellFormula(row, f);
89
        }
90
    }
91
    private void updateRefInCTCellFormula(Row row, CTCellFormula f) {
92
        if (f.isSetRef()) { //Range of cells which the formula applies to.
93
            String ref = f.getRef();
94
            String shiftedRef = shiftFormula(row, ref);
95
            if (shiftedRef != null) f.setRef(shiftedRef);
96
        }
97
    }
98
99
    /**
100
     * Shift a formula using the supplied FormulaShifter
101
     *
102
     * @param row     the row of the cell this formula belongs to. Used to get a reference to the parent workbook.
103
     * @param formula the formula to shift
104
     * @param shifter the FormulaShifter object that operates on the parsed formula tokens
105
     * @return the shifted formula if the formula was changed,
106
     *         <code>null</code> if the formula wasn't modified
107
     */
108
    private String shiftFormula(Row row, String formula) {
109
        Sheet sheet = row.getSheet();
110
        Workbook wb = sheet.getWorkbook();
111
        int sheetIndex = wb.getSheetIndex(sheet);
112
        final int rowIndex = row.getRowNum();
113
        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);
114
        
115
        try {
116
            Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
117
            String shiftedFmla = null;
118
            if (shifter.adjustFormula(ptgs, sheetIndex)) {
119
                shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
120
            }
121
            return shiftedFmla;
122
        } catch (FormulaParseException fpe) {
123
            // Log, but don't change, rather than breaking
124
            logger.log(POILogger.WARN, "Error shifting formula on row ", row.getRowNum(), fpe);
125
            return formula;
126
        }
127
    }
128
129
    
130
}
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java (-105 / +2 lines)
Lines 59-72 import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType; Link Here
59
public final class XSSFRowShifter extends RowShifter {
59
public final class XSSFRowShifter extends RowShifter {
60
    private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);
60
    private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);
61
61
62
    private XSSFFormulaShiftingManager formulaShiftingManager;
62
    private XSSFShiftingManager formulaShiftingManager;
63
    
63
    
64
    public XSSFRowShifter(XSSFSheet sh) {
64
    public XSSFRowShifter(XSSFSheet sh) {
65
        super(sh);
65
        super(sh);
66
    }
66
    }
67
    public XSSFRowShifter(Sheet sh, FormulaShifter shifter) {
67
    public XSSFRowShifter(Sheet sh, FormulaShifter shifter) {
68
        super(sh, shifter);
68
        super(sh, shifter);
69
        formulaShiftingManager = new XSSFFormulaShiftingManager(sh, shifter);
69
        formulaShiftingManager = new XSSFShiftingManager(sh, shifter);
70
    }
70
    }
71
    
71
    
72
72
Lines 106-212 public final class XSSFRowShifter extends RowShifter { Link Here
106
        }
106
        }
107
    }
107
    }
108
108
109
    public void updateConditionalFormatting() {
110
        XSSFSheet xsheet = (XSSFSheet) sheet;
111
        XSSFWorkbook wb = xsheet.getWorkbook();
112
        int sheetIndex = wb.getSheetIndex(sheet);
113
        final int rowIndex = -1; //don't care, structured references not allowed in conditional formatting
114
115
        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
116
        CTWorksheet ctWorksheet = xsheet.getCTWorksheet();
117
        CTConditionalFormatting[] conditionalFormattingArray = ctWorksheet.getConditionalFormattingArray();
118
        // iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j)
119
        for (int j = conditionalFormattingArray.length - 1; j >= 0; j--) {
120
            CTConditionalFormatting cf = conditionalFormattingArray[j];
121
122
            ArrayList<CellRangeAddress> cellRanges = new ArrayList<CellRangeAddress>();
123
            for (Object stRef : cf.getSqref()) {
124
                String[] regions = stRef.toString().split(" ");
125
                for (String region : regions) {
126
                    cellRanges.add(CellRangeAddress.valueOf(region));
127
                }
128
            }
129
130
            boolean changed = false;
131
            List<CellRangeAddress> temp = new ArrayList<CellRangeAddress>();
132
            for (CellRangeAddress craOld : cellRanges) {
133
                CellRangeAddress craNew = shiftRange(shifter, craOld, sheetIndex);
134
                if (craNew == null) {
135
                    changed = true;
136
                    continue;
137
                }
138
                temp.add(craNew);
139
                if (craNew != craOld) {
140
                    changed = true;
141
                }
142
            }
143
144
            if (changed) {
145
                int nRanges = temp.size();
146
                if (nRanges == 0) {
147
                    ctWorksheet.removeConditionalFormatting(j);
148
                    continue;
149
                }
150
                List<String> refs = new ArrayList<String>();
151
                for(CellRangeAddress a : temp) refs.add(a.formatAsString());
152
                cf.setSqref(refs);
153
            }
154
155
            for(CTCfRule cfRule : cf.getCfRuleArray()){
156
                String[] formulaArray = cfRule.getFormulaArray();
157
                for (int i = 0; i < formulaArray.length; i++) {
158
                    String formula = formulaArray[i];
159
                    Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
160
                    if (shifter.adjustFormula(ptgs, sheetIndex)) {
161
                        String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
162
                        cfRule.setFormulaArray(i, shiftedFmla);
163
                    }
164
                }
165
            }
166
        }
167
    }
168
    
169
    /**
170
     * Shift the Hyperlink anchors (not the hyperlink text, even if the hyperlink
171
     * is of type LINK_DOCUMENT and refers to a cell that was shifted). Hyperlinks
172
     * do not track the content they point to.
173
     *
174
     * @param shifter
175
     */
176
    public void updateHyperlinks() {
177
        int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet);
178
        List<? extends Hyperlink> hyperlinkList = sheet.getHyperlinkList();
179
        
180
        for (Hyperlink hyperlink : hyperlinkList) {
181
            XSSFHyperlink xhyperlink = (XSSFHyperlink) hyperlink;
182
            String cellRef = xhyperlink.getCellRef();
183
            CellRangeAddress cra = CellRangeAddress.valueOf(cellRef);
184
            CellRangeAddress shiftedRange = shiftRange(shifter, cra, sheetIndex);
185
            if (shiftedRange != null && shiftedRange != cra) {
186
                // shiftedRange should not be null. If shiftedRange is null, that means
187
                // that a hyperlink wasn't deleted at the beginning of shiftRows when
188
                // identifying rows that should be removed because they will be overwritten
189
                xhyperlink.setCellReference(shiftedRange.formatAsString());
190
            }
191
        }
192
    }
193
194
    private static CellRangeAddress shiftRange(FormulaShifter shifter, CellRangeAddress cra, int currentExternSheetIx) {
195
        // FormulaShifter works well in terms of Ptgs - so convert CellRangeAddress to AreaPtg (and back) here
196
        AreaPtg aptg = new AreaPtg(cra.getFirstRow(), cra.getLastRow(), cra.getFirstColumn(), cra.getLastColumn(), false, false, false, false);
197
        Ptg[] ptgs = { aptg, };
198
199
        if (!shifter.adjustFormula(ptgs, currentExternSheetIx)) {
200
            return cra;
201
        }
202
        Ptg ptg0 = ptgs[0];
203
        if (ptg0 instanceof AreaPtg) {
204
            AreaPtg bptg = (AreaPtg) ptg0;
205
            return new CellRangeAddress(bptg.getFirstRow(), bptg.getLastRow(), bptg.getFirstColumn(), bptg.getLastColumn());
206
        }
207
        if (ptg0 instanceof AreaErrPtg) {
208
            return null;
209
        }
210
        throw new IllegalStateException("Unexpected shifted ptg class (" + ptg0.getClass().getName() + ")");
211
    }
212
}
109
}
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFShiftingManager.java (-1 / +246 lines)
Line 0 Link Here
0
- 
1
package org.apache.poi.xssf.usermodel.helpers;
2
3
import java.util.ArrayList;
4
import java.util.List;
5
6
import org.apache.poi.ss.formula.FormulaParseException;
7
import org.apache.poi.ss.formula.FormulaParser;
8
import org.apache.poi.ss.formula.FormulaRenderer;
9
import org.apache.poi.ss.formula.FormulaShifter;
10
import org.apache.poi.ss.formula.FormulaType;
11
import org.apache.poi.ss.formula.ptg.AreaErrPtg;
12
import org.apache.poi.ss.formula.ptg.AreaPtg;
13
import org.apache.poi.ss.formula.ptg.Ptg;
14
import org.apache.poi.ss.usermodel.Cell;
15
import org.apache.poi.ss.usermodel.Hyperlink;
16
import org.apache.poi.ss.usermodel.Row;
17
import org.apache.poi.ss.usermodel.Sheet;
18
import org.apache.poi.ss.usermodel.Workbook;
19
import org.apache.poi.ss.util.CellRangeAddress;
20
import org.apache.poi.util.Internal;
21
import org.apache.poi.util.POILogFactory;
22
import org.apache.poi.util.POILogger;
23
import org.apache.poi.xssf.usermodel.XSSFCell;
24
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
25
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
26
import org.apache.poi.xssf.usermodel.XSSFRow;
27
import org.apache.poi.xssf.usermodel.XSSFSheet;
28
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
29
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
30
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
31
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule;
32
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTConditionalFormatting;
33
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
34
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
35
36
public class XSSFShiftingManager {
37
38
    private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);
39
40
    protected final Sheet shiftingSheet;
41
    protected FormulaShifter shifter;
42
43
    public XSSFShiftingManager(Sheet shiftingSheet, FormulaShifter shifter){
44
    	this.shiftingSheet = shiftingSheet;
45
        this.shifter = shifter;
46
	}
47
    
48
    public void updateFormulas() {
49
        //update formulas on the parent sheet
50
        updateSheetFormulas(shiftingSheet);
51
52
        //update formulas on other sheets
53
        Workbook wb = shiftingSheet.getWorkbook();
54
        for (Sheet sh : wb) {
55
            if (shiftingSheet == sh) continue;
56
            updateSheetFormulas(sh);
57
        }
58
    }
59
60
    private void updateSheetFormulas(Sheet sh) {
61
        for (Row r : sh) {
62
            XSSFRow row = (XSSFRow) r;
63
            updateRowFormulas(row);
64
        }
65
    }
66
67
    /**
68
     * Update the formulas in specified row using the formula shifting policy specified by shifter
69
     *
70
     * @param row the row to update the formulas on
71
     * @param shifter the formula shifting policy
72
     */
73
    @Internal
74
    public void updateRowFormulas(Row row) {
75
        for (Cell c : row) {
76
           	updateCellFormula(row, (XSSFCell) c);
77
        }
78
    }
79
    
80
    public void updateCellFormula(Row row, XSSFCell cell){
81
        CTCell ctCell = cell.getCTCell();
82
        if (ctCell.isSetF()) {
83
            CTCellFormula f = ctCell.getF();
84
            String formula = f.getStringValue();
85
            if (formula.length() > 0) {
86
                String shiftedFormula = shiftFormula(row, formula);
87
                if (shiftedFormula != null) {
88
                    f.setStringValue(shiftedFormula);
89
                    if(f.getT() == STCellFormulaType.SHARED){
90
                        int si = (int)f.getSi();
91
                        XSSFSheet sheet = (XSSFSheet) row.getSheet();
92
                        CTCellFormula sf = sheet.getSharedFormula(si);
93
                        sf.setStringValue(shiftedFormula);
94
                        updateRefInCTCellFormula(row, sf);
95
                    }
96
                }
97
            }
98
            //Range of cells which the formula applies to.
99
            updateRefInCTCellFormula(row, f);
100
        }
101
    }
102
    private void updateRefInCTCellFormula(Row row, CTCellFormula f) {
103
        if (f.isSetRef()) { //Range of cells which the formula applies to.
104
            String ref = f.getRef();
105
            String shiftedRef = shiftFormula(row, ref);
106
            if (shiftedRef != null) f.setRef(shiftedRef);
107
        }
108
    }
109
110
    /**
111
     * Shift a formula using the supplied FormulaShifter
112
     *
113
     * @param row     the row of the cell this formula belongs to. Used to get a reference to the parent workbook.
114
     * @param formula the formula to shift
115
     * @param shifter the FormulaShifter object that operates on the parsed formula tokens
116
     * @return the shifted formula if the formula was changed,
117
     *         <code>null</code> if the formula wasn't modified
118
     */
119
    private String shiftFormula(Row row, String formula) {
120
        Sheet sheet = row.getSheet();
121
        Workbook wb = sheet.getWorkbook();
122
        int sheetIndex = wb.getSheetIndex(sheet);
123
        final int rowIndex = row.getRowNum();
124
        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);
125
        
126
        try {
127
            Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
128
            String shiftedFmla = null;
129
            if (shifter.adjustFormula(ptgs, sheetIndex)) {
130
                shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
131
            }
132
            return shiftedFmla;
133
        } catch (FormulaParseException fpe) {
134
            // Log, but don't change, rather than breaking
135
            logger.log(POILogger.WARN, "Error shifting formula on row ", row.getRowNum(), fpe);
136
            return formula;
137
        }
138
    }
139
140
    
141
    public void updateConditionalFormatting() {
142
        XSSFSheet xsheet = (XSSFSheet) shiftingSheet;
143
        XSSFWorkbook wb = xsheet.getWorkbook();
144
        int sheetIndex = wb.getSheetIndex(shiftingSheet);
145
        final int rowIndex = -1; //don't care, structured references not allowed in conditional formatting
146
147
        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
148
        CTWorksheet ctWorksheet = xsheet.getCTWorksheet();
149
        CTConditionalFormatting[] conditionalFormattingArray = ctWorksheet.getConditionalFormattingArray();
150
        // iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j)
151
        for (int j = conditionalFormattingArray.length - 1; j >= 0; j--) {
152
            CTConditionalFormatting cf = conditionalFormattingArray[j];
153
154
            ArrayList<CellRangeAddress> cellRanges = new ArrayList<CellRangeAddress>();
155
            for (Object stRef : cf.getSqref()) {
156
                String[] regions = stRef.toString().split(" ");
157
                for (String region : regions) {
158
                    cellRanges.add(CellRangeAddress.valueOf(region));
159
                }
160
            }
161
162
            boolean changed = false;
163
            List<CellRangeAddress> temp = new ArrayList<CellRangeAddress>();
164
            for (CellRangeAddress craOld : cellRanges) {
165
                CellRangeAddress craNew = shiftRange(shifter, craOld, sheetIndex);
166
                if (craNew == null) {
167
                    changed = true;
168
                    continue;
169
                }
170
                temp.add(craNew);
171
                if (craNew != craOld) {
172
                    changed = true;
173
                }
174
            }
175
176
            if (changed) {
177
                int nRanges = temp.size();
178
                if (nRanges == 0) {
179
                    ctWorksheet.removeConditionalFormatting(j);
180
                    continue;
181
                }
182
                List<String> refs = new ArrayList<String>();
183
                for(CellRangeAddress a : temp) refs.add(a.formatAsString());
184
                cf.setSqref(refs);
185
            }
186
187
            for(CTCfRule cfRule : cf.getCfRuleArray()){
188
                String[] formulaArray = cfRule.getFormulaArray();
189
                for (int i = 0; i < formulaArray.length; i++) {
190
                    String formula = formulaArray[i];
191
                    Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
192
                    if (shifter.adjustFormula(ptgs, sheetIndex)) {
193
                        String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
194
                        cfRule.setFormulaArray(i, shiftedFmla);
195
                    }
196
                }
197
            }
198
        }
199
    }
200
    
201
    private static CellRangeAddress shiftRange(FormulaShifter shifter, CellRangeAddress cra, int currentExternSheetIx) {
202
        // FormulaShifter works well in terms of Ptgs - so convert CellRangeAddress to AreaPtg (and back) here
203
        AreaPtg aptg = new AreaPtg(cra.getFirstRow(), cra.getLastRow(), cra.getFirstColumn(), cra.getLastColumn(), false, false, false, false);
204
        Ptg[] ptgs = { aptg, };
205
206
        if (!shifter.adjustFormula(ptgs, currentExternSheetIx)) {
207
            return cra;
208
        }
209
        Ptg ptg0 = ptgs[0];
210
        if (ptg0 instanceof AreaPtg) {
211
            AreaPtg bptg = (AreaPtg) ptg0;
212
            return new CellRangeAddress(bptg.getFirstRow(), bptg.getLastRow(), bptg.getFirstColumn(), bptg.getLastColumn());
213
        }
214
        if (ptg0 instanceof AreaErrPtg) {
215
            return null;
216
        }
217
        throw new IllegalStateException("Unexpected shifted ptg class (" + ptg0.getClass().getName() + ")");
218
    }
219
    
220
    /**
221
     * Shift the Hyperlink anchors (not the hyperlink text, even if the hyperlink
222
     * is of type LINK_DOCUMENT and refers to a cell that was shifted). Hyperlinks
223
     * do not track the content they point to.
224
     *
225
     * @param shifter
226
     */
227
    public void updateHyperlinks() {
228
        int sheetIndex = shiftingSheet.getWorkbook().getSheetIndex(shiftingSheet);
229
        List<? extends Hyperlink> hyperlinkList = shiftingSheet.getHyperlinkList();
230
        
231
        for (Hyperlink hyperlink : hyperlinkList) {
232
            XSSFHyperlink xhyperlink = (XSSFHyperlink) hyperlink;
233
            String cellRef = xhyperlink.getCellRef();
234
            CellRangeAddress cra = CellRangeAddress.valueOf(cellRef);
235
            CellRangeAddress shiftedRange = shiftRange(shifter, cra, sheetIndex);
236
            if (shiftedRange != null && shiftedRange != cra) {
237
                // shiftedRange should not be null. If shiftedRange is null, that means
238
                // that a hyperlink wasn't deleted at the beginning of shiftRows when
239
                // identifying rows that should be removed because they will be overwritten
240
                xhyperlink.setCellReference(shiftedRange.formatAsString());
241
            }
242
        }
243
    }
244
245
    
246
}

Return to bug 61474