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

(-)a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (+3 lines)
Lines 2640-2643 public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { Link Here
2640
        _sheet.setActiveCellRow(row);
2640
        _sheet.setActiveCellRow(row);
2641
        _sheet.setActiveCellCol(col);
2641
        _sheet.setActiveCellCol(col);
2642
    }
2642
    }
2643
2644
    public void shiftColumns(int startColumn, int endColumn, int n){ 
2645
    } 
2643
}
2646
}
(-)a/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java (-1 / +4 lines)
Lines 21-26 import org.apache.poi.hssf.usermodel.HSSFSheet; Link Here
21
import org.apache.poi.ss.formula.FormulaShifter;
21
import org.apache.poi.ss.formula.FormulaShifter;
22
import org.apache.poi.ss.formula.eval.NotImplementedException;
22
import org.apache.poi.ss.formula.eval.NotImplementedException;
23
import org.apache.poi.ss.usermodel.Row;
23
import org.apache.poi.ss.usermodel.Row;
24
import org.apache.poi.ss.usermodel.Sheet;
24
import org.apache.poi.ss.usermodel.helpers.RowShifter;
25
import org.apache.poi.ss.usermodel.helpers.RowShifter;
25
import org.apache.poi.util.Internal;
26
import org.apache.poi.util.Internal;
26
import org.apache.poi.util.NotImplemented;
27
import org.apache.poi.util.NotImplemented;
Lines 38-44 public final class HSSFRowShifter extends RowShifter { Link Here
38
    public HSSFRowShifter(HSSFSheet sh) {
39
    public HSSFRowShifter(HSSFSheet sh) {
39
        super(sh);
40
        super(sh);
40
    }
41
    }
41
42
    public HSSFRowShifter(Sheet sh, FormulaShifter shifter) {
43
        super(sh, shifter);
44
    }
42
    @NotImplemented
45
    @NotImplemented
43
    public void updateNamedRanges(FormulaShifter shifter) {
46
    public void updateNamedRanges(FormulaShifter shifter) {
44
        throw new NotImplementedException("HSSFRowShifter.updateNamedRanges");
47
        throw new NotImplementedException("HSSFRowShifter.updateNamedRanges");
(-)a/src/java/org/apache/poi/ss/formula/FormulaShifter.java (-21 / +130 lines)
Lines 17-22 Link Here
17
17
18
package org.apache.poi.ss.formula;
18
package org.apache.poi.ss.formula;
19
19
20
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
21
import org.apache.poi.hssf.util.CellReference;
20
import org.apache.poi.ss.SpreadsheetVersion;
22
import org.apache.poi.ss.SpreadsheetVersion;
21
import org.apache.poi.ss.formula.ptg.Area2DPtgBase;
23
import org.apache.poi.ss.formula.ptg.Area2DPtgBase;
22
import org.apache.poi.ss.formula.ptg.Area3DPtg;
24
import org.apache.poi.ss.formula.ptg.Area3DPtg;
Lines 33-38 import org.apache.poi.ss.formula.ptg.Ref3DPxg; Link Here
33
import org.apache.poi.ss.formula.ptg.RefErrorPtg;
35
import org.apache.poi.ss.formula.ptg.RefErrorPtg;
34
import org.apache.poi.ss.formula.ptg.RefPtg;
36
import org.apache.poi.ss.formula.ptg.RefPtg;
35
import org.apache.poi.ss.formula.ptg.RefPtgBase;
37
import org.apache.poi.ss.formula.ptg.RefPtgBase;
38
import org.apache.poi.ss.usermodel.Sheet;
39
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
36
40
37
41
38
/**
42
/**
Lines 40-46 import org.apache.poi.ss.formula.ptg.RefPtgBase; Link Here
40
 */
44
 */
41
public final class FormulaShifter {
45
public final class FormulaShifter {
42
46
43
    private static enum ShiftMode {
47
    public static enum ShiftMode {
44
        RowMove,
48
        RowMove,
45
        RowCopy,
49
        RowCopy,
46
        SheetMove,
50
        SheetMove,
Lines 67-72 public final class FormulaShifter { Link Here
67
71
68
    private final ShiftMode _mode;
72
    private final ShiftMode _mode;
69
73
74
	private boolean _rowModeElseColumn; 
75
76
	
70
    /**
77
    /**
71
     * Create an instance for shifting row.
78
     * Create an instance for shifting row.
72
     *
79
     *
Lines 89-94 public final class FormulaShifter { Link Here
89
        _version = version;
96
        _version = version;
90
97
91
        _srcSheetIndex = _dstSheetIndex = -1;
98
        _srcSheetIndex = _dstSheetIndex = -1;
99
        _rowModeElseColumn = true; // default
92
    }
100
    }
93
101
94
    /**
102
    /**
Lines 104-119 public final class FormulaShifter { Link Here
104
        _srcSheetIndex = srcSheetIndex;
112
        _srcSheetIndex = srcSheetIndex;
105
        _dstSheetIndex = dstSheetIndex;
113
        _dstSheetIndex = dstSheetIndex;
106
        _mode = ShiftMode.SheetMove;
114
        _mode = ShiftMode.SheetMove;
115
        _rowModeElseColumn = true; // default
107
    }
116
    }
108
    
117
    
109
    public static FormulaShifter createForRowShift(int externSheetIndex, String sheetName, int firstMovedRowIndex, int lastMovedRowIndex, int numberOfRowsToMove,
118
	public static FormulaShifter createForItemShift(Sheet shiftingSheet, boolean _rowModeElseColumn, int firstShiftItemIndex, int lastShiftItemIndex, int shiftStep){
119
		FormulaShifter instance = new FormulaShifter(shiftingSheet.getWorkbook().getSheetIndex(shiftingSheet), shiftingSheet.getSheetName(), 
120
				firstShiftItemIndex, lastShiftItemIndex, shiftStep, ShiftMode.RowMove, getSpreadsheetVersion(shiftingSheet));
121
		instance._rowModeElseColumn = _rowModeElseColumn;
122
		return instance; 
123
	}
124
	// maybe should be deprecated, and previous one should be used
125
	public static FormulaShifter createForRowShift(int externSheetIndex, String sheetName, int firstMovedRowIndex, int lastMovedRowIndex, int numberOfRowsToMove,
110
            SpreadsheetVersion version) {
126
            SpreadsheetVersion version) {
111
        return new FormulaShifter(externSheetIndex, sheetName, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove, ShiftMode.RowMove, version);
127
		FormulaShifter instance = new FormulaShifter(externSheetIndex, sheetName, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove, ShiftMode.RowMove, version);
128
		return instance;
112
    }
129
    }
113
    
130
    
131
	public static FormulaShifter createForItemCopy(Sheet shiftingSheet, boolean rowModeElseColumn, int firstMovedItemIndex, int lastMovedItemIndex, int shiftStep){
132
		FormulaShifter instance = new FormulaShifter(shiftingSheet.getWorkbook().getSheetIndex(shiftingSheet), shiftingSheet.getSheetName(), 
133
				firstMovedItemIndex, lastMovedItemIndex, shiftStep, ShiftMode.RowCopy, getSpreadsheetVersion(shiftingSheet));
134
    	instance._rowModeElseColumn = rowModeElseColumn;
135
		return instance; 
136
	}
137
	// maybe should be deprecated, and previous one should be used
114
    public static FormulaShifter createForRowCopy(int externSheetIndex, String sheetName, int firstMovedRowIndex, int lastMovedRowIndex, int numberOfRowsToMove,
138
    public static FormulaShifter createForRowCopy(int externSheetIndex, String sheetName, int firstMovedRowIndex, int lastMovedRowIndex, int numberOfRowsToMove,
115
            SpreadsheetVersion version) {
139
            SpreadsheetVersion version) {
116
        return new FormulaShifter(externSheetIndex, sheetName, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove, ShiftMode.RowCopy, version);
140
    	FormulaShifter instance = new FormulaShifter(externSheetIndex, sheetName, firstMovedRowIndex, lastMovedRowIndex, numberOfRowsToMove, ShiftMode.RowCopy, version);
141
    	return instance;
117
    }
142
    }
118
143
119
    public static FormulaShifter createForSheetShift(int srcSheetIndex, int dstSheetIndex) {
144
    public static FormulaShifter createForSheetShift(int srcSheetIndex, int dstSheetIndex) {
Lines 130-152 public final class FormulaShifter { Link Here
130
                "]";
155
                "]";
131
    }
156
    }
132
157
133
    /**
134
     * @param ptgs - if necessary, will get modified by this method
135
     * @param currentExternSheetIx - the extern sheet index of the sheet that contains the formula being adjusted
136
     * @return <code>true</code> if a change was made to the formula tokens
137
     */
138
    public boolean adjustFormula(Ptg[] ptgs, int currentExternSheetIx) {
139
        boolean refsWereChanged = false;
140
        for(int i=0; i<ptgs.length; i++) {
141
            Ptg newPtg = adjustPtg(ptgs[i], currentExternSheetIx);
142
            if (newPtg != null) {
143
                refsWereChanged = true;
144
                ptgs[i] = newPtg;
145
            }
146
        }
147
        return refsWereChanged;
148
    }
149
150
    private Ptg adjustPtg(Ptg ptg, int currentExternSheetIx) {
158
    private Ptg adjustPtg(Ptg ptg, int currentExternSheetIx) {
151
        switch(_mode){
159
        switch(_mode){
152
            case RowMove:
160
            case RowMove:
Lines 548-551 public final class FormulaShifter { Link Here
548
556
549
        throw new IllegalArgumentException("Unexpected ref ptg class (" + ptg.getClass().getName() + ")");
557
        throw new IllegalArgumentException("Unexpected ref ptg class (" + ptg.getClass().getName() + ")");
550
    }
558
    }
559
    
560
    
561
    // ******** logic which processes columns in same way as row ********
562
    
563
564
    /**
565
     * @param ptgs - if necessary, will get modified by this method
566
     * @param currentExternSheetIx - the extern sheet index of the sheet that contains the formula being adjusted
567
     * @return <code>true</code> if a change was made to the formula tokens
568
     */
569
    public boolean adjustFormula(Ptg[] ptgs, int currentExternSheetIx) {
570
        boolean refsWereChanged = false;
571
        for(int i=0; i<ptgs.length; i++) {
572
        	Ptg newPtg;
573
            if(_rowModeElseColumn){
574
            	newPtg = adjustPtg(ptgs[i], currentExternSheetIx);
575
	            if (newPtg != null) {
576
	                refsWereChanged = true;
577
	           		ptgs[i] = newPtg;
578
	            }
579
            }
580
            else {
581
            	Ptg transposedPtg = transpose(ptgs[i]);
582
            	newPtg = adjustPtg(transposedPtg, currentExternSheetIx);
583
                if (newPtg != null) {
584
                    refsWereChanged = true;
585
               		ptgs[i] = transpose(transposedPtg);
586
                }
587
            }
588
        }
589
        return refsWereChanged;
590
    }
591
592
593
    private Ptg transpose(Ptg ptg){
594
            String ptgType = ptg.getClass().getSimpleName();  
595
        	if(ptgType.equals("Ref3DPtg")){  //3D means (sheetNo, col, row) reference, for example Sheet1!B3; xls version  
596
            	int oldColumnIndex = ((Ref3DPtg) ptg).getColumn();
597
            	((Ref3DPtg) ptg).setColumn(((Ref3DPtg) ptg).getRow());
598
            	((Ref3DPtg) ptg).setRow(oldColumnIndex);
599
            	return ptg;
600
        	} else if(ptgType.equals("Ref3DPxg")){  //3D means (sheetNo, col, row) reference, for example Sheet1!B3; xlsx version  
601
    	        int oldColumnIndex = ((Ref3DPxg) ptg).getColumn();
602
            	((Ref3DPxg) ptg).setColumn(((Ref3DPxg) ptg).getRow());
603
            	((Ref3DPxg) ptg).setRow(oldColumnIndex);
604
            	return ptg;
605
        	} else if(ptgType.equals("AreaPtg")){  // region for aggregate function, for example A1:B3 or Sheet1!B3:Sheet1!C3
606
		        	int oldFirstColumnIndex = ((AreaPtg) ptg).getFirstColumn();
607
		        	((AreaPtg) ptg).setFirstColumn(((AreaPtg) ptg).getFirstRow());
608
		        	((AreaPtg) ptg).setFirstRow(oldFirstColumnIndex);
609
		        	int oldLastColumnIndex = ((AreaPtg) ptg).getLastColumn();
610
		        	((AreaPtg) ptg).setLastColumn(((AreaPtg) ptg).getLastRow());
611
		        	((AreaPtg) ptg).setLastRow(oldLastColumnIndex);
612
		        	return ptg;
613
        	}
614
        	 else if(ptgType.equals("Area3DPtg")){ //for example SUM(Sheet1!B3:C3); xls version 
615
		        	int oldFirstColumnIndex = ((Area3DPtg) ptg).getFirstColumn();
616
		        	((Area3DPtg) ptg).setFirstColumn(((Area3DPtg) ptg).getFirstRow());
617
		        	((Area3DPtg) ptg).setFirstRow(oldFirstColumnIndex);
618
		        	int oldLastColumnIndex = ((Area3DPtg) ptg).getLastColumn();
619
		        	((Area3DPtg) ptg).setLastColumn(((Area3DPtg) ptg).getLastRow());
620
		        	((Area3DPtg) ptg).setLastRow(oldLastColumnIndex);
621
		        	return ptg;
622
        	 }
623
        	 else if(ptgType.equals("Area3DPxg")){ //for example SUM(Sheet1!B3:C3); xlsx version 
624
        	         int oldFirstColumnIndex = ((Area3DPxg) ptg).getFirstColumn();
625
		        	((Area3DPxg) ptg).setFirstColumn(((Area3DPxg) ptg).getFirstRow());
626
		        	((Area3DPxg) ptg).setFirstRow(oldFirstColumnIndex);
627
		        	int oldLastColumnIndex = ((Area3DPxg) ptg).getLastColumn();
628
		        	((Area3DPxg) ptg).setLastColumn(((Area3DPxg) ptg).getLastRow());
629
		        	((Area3DPxg) ptg).setLastRow(oldLastColumnIndex);
630
		        	return ptg;
631
        	 }	else if(ptgType.equals("RefPtg")){  // common simple reference, like A2 
632
	                RefPtg transponedCellRefToken = new RefPtg(transpose(ptg.toFormulaString()));
633
	                return transponedCellRefToken;
634
        	 }
635
        	 else // operators like + or SUM, for example
636
        		 return ptg;
637
    }
638
639
    public static String transpose(String cellreference){
640
    	CellReference original = new CellReference(cellreference);
641
    	// transpose, calling public CellReference(int *pRow*, int *pCol*) !!!!
642
    	CellReference transposed = new CellReference(original.getCol(), original.getRow(), original.isColAbsolute(), original.isRowAbsolute());  
643
    	return transposed.formatAsString();
644
    }
645
    
646
	private int getSheetIndex(Sheet sheet){
647
		return sheet.getWorkbook().getSheetIndex(sheet);
648
	}
649
650
	public static SpreadsheetVersion getSpreadsheetVersion(Sheet sheet){
651
		if(sheet.getWorkbook() instanceof XSSFWorkbook)
652
			return SpreadsheetVersion.EXCEL2007;
653
		else if(sheet.getWorkbook() instanceof HSSFWorkbook)
654
			return SpreadsheetVersion.EXCEL97;
655
		else return null;
656
	}
657
	
658
    
659
    
551
}
660
}
(-)a/src/java/org/apache/poi/ss/usermodel/Sheet.java (+1 lines)
Lines 702-707 public interface Sheet extends Iterable<Row> { Link Here
702
     * @param resetOriginalRowHeight whether to set the original row's height to the default
702
     * @param resetOriginalRowHeight whether to set the original row's height to the default
703
     */
703
     */
704
    void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight);
704
    void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight);
705
    void shiftColumns(int startColumn, int endColumn, int n);
705
706
706
    /**
707
    /**
707
     * Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
708
     * Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
(-)a/src/java/org/apache/poi/ss/usermodel/helpers/ColumnShifter.java (+74 lines)
Line 0 Link Here
1
package org.apache.poi.ss.usermodel.helpers;
2
3
import java.util.ArrayList;
4
import java.util.HashSet;
5
import java.util.List;
6
import java.util.Set;
7
8
import org.apache.poi.ss.formula.FormulaShifter;
9
import org.apache.poi.ss.usermodel.Sheet;
10
import org.apache.poi.ss.util.CellRangeAddress;
11
12
public class ColumnShifter {
13
    protected final Sheet shiftingSheet;
14
    protected FormulaShifter shifter;
15
16
    public ColumnShifter(Sheet sheet, FormulaShifter shifter) {
17
    	shiftingSheet = sheet;
18
        this.shifter = shifter;
19
    }
20
21
    
22
    /**
23
     * Shifts, grows, or shrinks the merged regions due to a column shift.
24
     * Merged regions that are completely overlaid by shifting will be deleted.
25
     *
26
     * @param startColumnIndex index of the column to start shifting
27
     * @param endColumnIndex   index of the column to end shifting
28
     * @param n        the number of columns to shift
29
     * @return an array of affected merged regions, doesn't contain deleted ones
30
     */
31
    public List<CellRangeAddress> shiftMergedRegions(int startColumnIndex, int endColumnIndex, int n) {
32
        List<CellRangeAddress> shiftedRegions = new ArrayList<CellRangeAddress>();
33
        Set<Integer> removedIndices = new HashSet<Integer>();
34
        //move merged regions completely if they fall within the new region boundaries when they are shifted
35
        int size = shiftingSheet.getNumMergedRegions();
36
        for (int i = 0; i < size; i++) {
37
            CellRangeAddress merged = shiftingSheet.getMergedRegion(i);
38
39
            // remove merged region that overlaps shifting
40
            if (startColumnIndex + n <= merged.getFirstColumn() && endColumnIndex + n >= merged.getLastColumn()) {
41
                removedIndices.add(i);
42
                continue;
43
            }
44
45
            boolean inStart = (merged.getFirstColumn() >= startColumnIndex || merged.getLastColumn() >= startColumnIndex);
46
            boolean inEnd = (merged.getFirstColumn() <= endColumnIndex || merged.getLastColumn() <= endColumnIndex);
47
48
            //don't check if it's not within the shifted area
49
            if (!inStart || !inEnd) 
50
                continue;
51
52
            //only shift if the region outside the shifted columns is not merged too
53
            if (!merged.containsColumn(startColumnIndex - 1) && !merged.containsColumn(endColumnIndex + 1)) {
54
                merged.setFirstColumn(merged.getFirstColumn() + n);
55
                merged.setLastColumn(merged.getLastColumn() + n);
56
                //have to remove/add it back
57
                shiftedRegions.add(merged);
58
                removedIndices.add(i);
59
            }
60
        }
61
        
62
        if(!removedIndices.isEmpty()) {
63
            shiftingSheet.removeMergedRegions(removedIndices);
64
        }
65
66
        //read so it doesn't get shifted again
67
        for (CellRangeAddress region : shiftedRegions) {
68
            shiftingSheet.addMergedRegion(region);
69
        }
70
        return shiftedRegions;
71
    }
72
    
73
    
74
}
(-)a/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java (-5 / +11 lines)
Lines 36-46 import org.apache.poi.util.Internal; Link Here
36
public abstract class RowShifter {
36
public abstract class RowShifter {
37
    protected final Sheet sheet;
37
    protected final Sheet sheet;
38
38
39
    public RowShifter(Sheet sh) {
39
    protected FormulaShifter shifter; 
40
        sheet = sh;
40
    
41
    }
41
    public RowShifter(Sheet sh) { 
42
42
        sheet = sh; 
43
    /**
43
    } 
44
 
45
    public RowShifter(Sheet sh, FormulaShifter shifter) { 
46
        sheet = sh; 
47
        this.shifter = shifter; 
48
    } 
49
  /**
44
     * Shifts, grows, or shrinks the merged regions due to a row shift.
50
     * Shifts, grows, or shrinks the merged regions due to a row shift.
45
     * Merged regions that are completely overlaid by shifting will be deleted.
51
     * Merged regions that are completely overlaid by shifting will be deleted.
46
     *
52
     *
(-)a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java (+6 lines)
Lines 2115-2118 public class SXSSFSheet implements Sheet Link Here
2115
        color.setIndexed(colorIndex);
2115
        color.setIndexed(colorIndex);
2116
        pr.setTabColor(color);
2116
        pr.setTabColor(color);
2117
    }
2117
    }
2118
    
2119
    @NotImplemented 
2120
    @Override 
2121
    public void shiftColumns(int startColumn, int endColumn, int n){ 
2122
      throw new RuntimeException("NotImplemented"); 
2123
    }
2118
}
2124
}
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (+20 lines)
Lines 53-58 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; Link Here
53
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
53
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
54
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
54
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
55
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellType;
55
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellType;
56
import org.apache.poi.xssf.model.CalculationChain;
56
57
57
/**
58
/**
58
 * High level representation of a cell in a row of a spreadsheet.
59
 * High level representation of a cell in a row of a spreadsheet.
Lines 1311-1314 public final class XSSFCell implements Cell { Link Here
1311
                "You cannot change part of an array.";
1312
                "You cannot change part of an array.";
1312
        notifyArrayFormulaChanging(msg);
1313
        notifyArrayFormulaChanging(msg);
1313
    }
1314
    }
1315
    
1316
    /***
1317
     * Moved from XSSFRow.shift(). Not sure what is purpose. 
1318
     */
1319
    public void updateCellReferencesForShifting(String msg){
1320
        if(isPartOfArrayFormulaGroup())
1321
            notifyArrayFormulaChanging(msg);
1322
        CalculationChain calcChain = getSheet().getWorkbook().getCalculationChain();
1323
        int sheetId = (int)getSheet().sheet.getSheetId();
1324
    
1325
        //remove the reference in the calculation chain
1326
        if(calcChain != null) calcChain.removeItem(sheetId, getReference());
1327
    
1328
        CTCell ctCell = getCTCell();
1329
        String r = new CellReference(getRowIndex(), getColumnIndex()).formatAsString();
1330
        ctCell.setR(r);
1331
    }
1332
        
1314
}
1333
}
1334
    
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java (-17 / +6 lines)
Lines 35-40 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.XSSFShiftingManager;
38
import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;
39
import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;
39
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
40
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
40
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;
41
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;
Lines 572-596 public class XSSFRow implements Row, Comparable<XSSFRow> { Link Here
572
     *
573
     *
573
     * @param n the number of rows to move
574
     * @param n the number of rows to move
574
     */
575
     */
575
    protected void shift(int n) {
576
    public void shift(int n) {
576
        int rownum = getRowNum() + n;
577
        int rownum = getRowNum() + n;
577
        CalculationChain calcChain = _sheet.getWorkbook().getCalculationChain();
578
        int sheetId = (int)_sheet.sheet.getSheetId();
579
        String msg = "Row[rownum="+getRowNum()+"] contains cell(s) included in a multi-cell array formula. " +
578
        String msg = "Row[rownum="+getRowNum()+"] contains cell(s) included in a multi-cell array formula. " +
580
                "You cannot change part of an array.";
579
                "You cannot change part of an array.";
581
        for(Cell c : this){
580
        for(Cell c : this){
582
            XSSFCell cell = (XSSFCell)c;
581
            ((XSSFCell)c).updateCellReferencesForShifting(msg);
583
            if(cell.isPartOfArrayFormulaGroup()){
582
          }
584
                cell.notifyArrayFormulaChanging(msg);
585
            }
586
587
            //remove the reference in the calculation chain
588
            if(calcChain != null) calcChain.removeItem(sheetId, cell.getReference());
589
590
            CTCell ctCell = cell.getCTCell();
591
            String r = new CellReference(rownum, cell.getColumnIndex()).formatAsString();
592
            ctCell.setR(r);
593
        }
594
        setRowNum(rownum);
583
        setRowNum(rownum);
595
    }
584
    }
596
    
585
    
Lines 640-653 public class XSSFRow implements Row, Comparable<XSSFRow> { Link Here
640
                destCell.copyCellFrom(srcCell, policy);
629
                destCell.copyCellFrom(srcCell, policy);
641
            }
630
            }
642
631
643
            final XSSFRowShifter rowShifter = new XSSFRowShifter(_sheet);
644
            final int sheetIndex = _sheet.getWorkbook().getSheetIndex(_sheet);
632
            final int sheetIndex = _sheet.getWorkbook().getSheetIndex(_sheet);
645
            final String sheetName = _sheet.getWorkbook().getSheetName(sheetIndex);
633
            final String sheetName = _sheet.getWorkbook().getSheetName(sheetIndex);
646
            final int srcRowNum = srcRow.getRowNum();
634
            final int srcRowNum = srcRow.getRowNum();
647
            final int destRowNum = getRowNum();
635
            final int destRowNum = getRowNum();
648
            final int rowDifference = destRowNum - srcRowNum;
636
            final int rowDifference = destRowNum - srcRowNum;
649
            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);
650
            rowShifter.updateRowFormulas(this, shifter);
638
            final XSSFShiftingManager formulaShiftingManager = new XSSFShiftingManager(_sheet, shifter); 
639
            formulaShiftingManager.updateRowFormulas(this);
651
640
652
            // Copy merged regions that are fully contained on the row
641
            // Copy merged regions that are fully contained on the row
653
            // FIXME: is this something that rowShifter could be doing?
642
            // FIXME: is this something that rowShifter could be doing?
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (-6 / +66 lines)
Lines 84-91 import org.apache.poi.util.Units; Link Here
84
import org.apache.poi.xssf.model.CommentsTable;
84
import org.apache.poi.xssf.model.CommentsTable;
85
import org.apache.poi.xssf.usermodel.XSSFPivotTable.PivotTableReferenceConfigurator;
85
import org.apache.poi.xssf.usermodel.XSSFPivotTable.PivotTableReferenceConfigurator;
86
import org.apache.poi.xssf.usermodel.helpers.ColumnHelper;
86
import org.apache.poi.xssf.usermodel.helpers.ColumnHelper;
87
import org.apache.poi.xssf.usermodel.helpers.XSSFColumnShifter;
87
import org.apache.poi.xssf.usermodel.helpers.XSSFIgnoredErrorHelper;
88
import org.apache.poi.xssf.usermodel.helpers.XSSFIgnoredErrorHelper;
88
import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;
89
import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;
90
import org.apache.poi.xssf.usermodel.helpers.XSSFShiftingManager;
89
import org.apache.xmlbeans.XmlCursor;
91
import org.apache.xmlbeans.XmlCursor;
90
import org.apache.xmlbeans.XmlException;
92
import org.apache.xmlbeans.XmlException;
91
import org.apache.xmlbeans.XmlObject;
93
import org.apache.xmlbeans.XmlObject;
Lines 2985-2991 public class XSSFSheet extends POIXMLDocumentPart implements Sheet { Link Here
2985
    public void shiftRows(int startRow, int endRow, final int n, boolean copyRowHeight, boolean resetOriginalRowHeight) {
2987
    public void shiftRows(int startRow, int endRow, final int n, boolean copyRowHeight, boolean resetOriginalRowHeight) {
2986
        XSSFVMLDrawing vml = getVMLDrawing(false);
2988
        XSSFVMLDrawing vml = getVMLDrawing(false);
2987
2989
2988
        // first remove all rows which will be overwritten
2990
        removeOverwritten(vml, startRow, endRow, n);
2991
2992
        int sheetIndex = getWorkbook().getSheetIndex(this);
2993
        String sheetName = getWorkbook().getSheetName(sheetIndex);
2994
        FormulaShifter shifter = FormulaShifter.createForRowShift(
2995
                                   sheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL2007);
2996
        XSSFRowShifter rowShifter = new XSSFRowShifter(this, shifter);
2997
        rowShifter.doShiftingAndProcessComments(vml, startRow, endRow, n, copyRowHeight, rowIterator(), sheetComments); 
2998
        rowShifter.shiftMergedRegions(startRow, endRow, n);
2999
3000
        XSSFShiftingManager shiftingManager = new XSSFShiftingManager(this, shifter);
3001
        shiftingManager.updateNamedRanges();
3002
        shiftingManager.updateFormulas();
3003
        shiftingManager.updateConditionalFormatting();
3004
        shiftingManager.updateHyperlinks();
3005
3006
        //rebuild the _rows map
3007
        Map<Integer, XSSFRow> map = new HashMap<Integer, XSSFRow>();
3008
        for(XSSFRow r : _rows.values()) {
3009
            // Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory
3010
            final Integer rownumI = new Integer(r.getRowNum()); // NOSONAR
3011
            map.put(rownumI, r);
3012
        }
3013
        _rows.clear();
3014
        _rows.putAll(map);
3015
    }
3016
    
3017
    /**
3018
     * Shifts columns between startColumn and endColumn n number of columns.
3019
     * If you use a negative number, it will shift columns up.
3020
     * Code ensures that columns don't wrap around
3021
     *
3022
     * @param startRow the column to start shifting
3023
     * @param endRow the column to end shifting
3024
     * @param n the number of columns to shift
3025
     */    @Override
3026
    public void shiftColumns(int startColumn, int endColumn, final int n) {
3027
        XSSFVMLDrawing vml = getVMLDrawing(false);
3028
        
3029
        FormulaShifter shifter = FormulaShifter.createForItemShift(this, false, startColumn, endColumn, n);
3030
        XSSFColumnShifter columnShifter = new XSSFColumnShifter(this, shifter);
3031
        columnShifter.shiftColumns(startColumn, endColumn, n);
3032
        columnShifter.shiftMergedRegions(startColumn, startColumn, n);
3033
        columnShifter.shiftComments(vml, startColumn, endColumn, n, sheetComments);
3034
3035
        XSSFShiftingManager shiftingManager = new XSSFShiftingManager(this, shifter);
3036
        shiftingManager.updateFormulas();
3037
        shiftingManager.updateConditionalFormatting();
3038
        shiftingManager.updateHyperlinks();
3039
        shiftingManager.updateNamedRanges();
3040
3041
        //rebuild the _rows map
3042
        Map<Integer, XSSFRow> map = new HashMap<Integer, XSSFRow>();
3043
        for(XSSFRow r : _rows.values()) {
3044
            final Integer rownumI = new Integer(r.getRowNum()); // NOSONAR
3045
            map.put(rownumI, r);
3046
        }
3047
        _rows.clear();
3048
        _rows.putAll(map);
3049
    }
3050
    
3051
    // remove all rows which will be overwritten
3052
     private void removeOverwritten(XSSFVMLDrawing vml, int startRow, int endRow, final int n){
2989
        for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) {
3053
        for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) {
2990
            XSSFRow row = (XSSFRow)it.next();
3054
            XSSFRow row = (XSSFRow)it.next();
2991
            int rownum = row.getRowNum();
3055
            int rownum = row.getRowNum();
Lines 3087-3096 public class XSSFSheet extends POIXMLDocumentPart implements Sheet { Link Here
3087
                continue;
3151
                continue;
3088
            }
3152
            }
3089
3153
3090
            if (!copyRowHeight) {
3091
                row.setHeight((short)-1);
3092
            }
3093
3094
            row.shift(n);
3154
            row.shift(n);
3095
        }
3155
        }
3096
        
3156
        
Lines 4515-4518 public class XSSFSheet extends POIXMLDocumentPart implements Sheet { Link Here
4515
    }
4575
    }
4516
4576
4517
4577
4518
}
4578
}
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java (-1 / +1 lines)
Lines 264-270 public final class XSSFVMLDrawing extends POIXMLDocumentPart { Link Here
264
     *
264
     *
265
     * @return the comment shape or <code>null</code>
265
     * @return the comment shape or <code>null</code>
266
     */
266
     */
267
    protected CTShape findCommentShape(int row, int col){
267
    public CTShape findCommentShape(int row, int col){
268
        for(XmlObject itm : _items){
268
        for(XmlObject itm : _items){
269
            if(itm instanceof CTShape){
269
            if(itm instanceof CTShape){
270
                CTShape sh = (CTShape)itm;
270
                CTShape sh = (CTShape)itm;
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifter.java (+193 lines)
Line 0 Link Here
1
package org.apache.poi.xssf.usermodel.helpers;
2
3
import java.util.ArrayList;
4
import java.util.Comparator;
5
import java.util.HashSet;
6
import java.util.Iterator;
7
import java.util.List;
8
import java.util.Map;
9
import java.util.Set;
10
import java.util.SortedMap;
11
import java.util.TreeMap;
12
13
import org.apache.poi.ss.ITestDataProvider;
14
import org.apache.poi.ss.formula.FormulaShifter;
15
import org.apache.poi.ss.usermodel.Cell;
16
import org.apache.poi.ss.usermodel.CellType;
17
import org.apache.poi.ss.usermodel.Row;
18
import org.apache.poi.ss.usermodel.Sheet;
19
import org.apache.poi.ss.usermodel.helpers.ColumnShifter;
20
import org.apache.poi.ss.util.CellRangeAddress;
21
import org.apache.poi.ss.util.CellReference;
22
import org.apache.poi.util.POILogFactory;
23
import org.apache.poi.util.POILogger;
24
import org.apache.poi.xssf.model.CommentsTable;
25
import org.apache.poi.xssf.usermodel.XSSFCell;
26
import org.apache.poi.xssf.usermodel.XSSFComment;
27
import org.apache.poi.xssf.usermodel.XSSFRow;
28
import org.apache.poi.xssf.usermodel.XSSFSheet;
29
import org.apache.poi.xssf.usermodel.XSSFVMLDrawing;
30
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment;
31
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCommentList;
32
33
public class XSSFColumnShifter extends ColumnShifter{
34
	
35
    private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);
36
    
37
    private int firstShiftColumnIndex; 
38
    private int lastShiftColumnIndex; 
39
    private int shiftStep;
40
    
41
    private XSSFShiftingManager formulaShiftingManager;
42
43
   
44
    public XSSFColumnShifter(Sheet sh, FormulaShifter shifter) {
45
        super(sh, shifter);
46
        formulaShiftingManager = new XSSFShiftingManager(sh, shifter);
47
    }
48
49
	public void shiftColumns(int firstShiftColumnIndex, int lastShiftColumnIndex, int step){
50
		this.firstShiftColumnIndex = firstShiftColumnIndex;
51
		this.lastShiftColumnIndex = lastShiftColumnIndex;
52
		this.shiftStep = step;
53
		if(shiftStep > 0)
54
			shiftColumnsRight();
55
		else if(shiftStep < 0)
56
			shiftColumnsLeft();
57
//	      formulaShiftingManager.updateFormulas();
58
	}
59
	/**
60
	 * Inserts shiftStep empty columns at firstShiftColumnIndex-th position, and shifts rest columns to the right 
61
	 * (see constructor for parameters)
62
	 */
63
64
	private void shiftColumnsRight(){
65
		for(int rowNo = 0; rowNo <= shiftingSheet.getLastRowNum(); rowNo++)
66
		{	
67
			Row row = shiftingSheet.getRow(rowNo);
68
			if(row == null)
69
				continue;
70
		    for (int columnIndex = lastShiftColumnIndex; columnIndex >= firstShiftColumnIndex; columnIndex--){ // process cells backwards, because of shifting 
71
		    	XSSFCell oldCell = (XSSFCell)row.getCell(columnIndex);
72
		    	Cell newCell = null;
73
		    	if(oldCell == null){
74
		    		newCell = row.getCell(columnIndex + shiftStep);
75
		    		newCell = null;
76
		    		continue;
77
		    	}
78
		    	else {
79
		    		newCell = row.createCell(columnIndex + shiftStep, oldCell.getCellTypeEnum());
80
		    		cloneCellValue(oldCell,newCell);
81
		    		if(columnIndex <= firstShiftColumnIndex + shiftStep - 1){ // clear existing cells on place of insertion
82
		    			oldCell.setCellValue("");
83
		    			oldCell.setCellType(CellType.STRING);
84
		    		}
85
		    	}
86
		    }
87
		}
88
	}
89
	private void shiftColumnsLeft(){
90
		for(int rowNo = 0; rowNo <= shiftingSheet.getLastRowNum(); rowNo++)
91
		{	
92
		    XSSFRow row = (XSSFRow)shiftingSheet.getRow(rowNo);
93
			if(row == null)
94
				continue;
95
		    for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++){ 
96
		        XSSFCell oldCell = (XSSFCell)row.getCell(columnIndex);
97
		    	if(columnIndex >= firstShiftColumnIndex + shiftStep && columnIndex < row.getLastCellNum() - shiftStep){ // shift existing cell 
98
			    	org.apache.poi.ss.usermodel.Cell newCell = null;
99
		    		newCell = row.getCell(columnIndex - shiftStep);
100
		    		if(oldCell != null){
101
			    		if(newCell != null){
102
			    			oldCell.setCellType(newCell.getCellType());
103
			    			cloneCellValue(newCell, oldCell);
104
			    		}
105
			    		else {
106
			    			oldCell.setCellType(CellType.STRING);
107
			    			oldCell.setCellValue("");
108
			    		}
109
		    		}
110
		    		else {
111
		    			oldCell = row.createCell(columnIndex);
112
			    		if(newCell != null){
113
			    			oldCell.setCellType(newCell.getCellType());
114
			    			cloneCellValue(newCell, oldCell);
115
			    		}
116
			    		else {
117
			    			oldCell.setCellType(CellType.STRING);
118
			    			oldCell.setCellValue("");
119
			    		}
120
		    		}
121
		    	}
122
    		}
123
		}
124
	}
125
	
126
    public void shiftComments(XSSFVMLDrawing vml, int startColumnIndex, int endColumnIndex, final int n, CommentsTable sheetComments){
127
        SortedMap<XSSFComment, Integer> commentsToShift = new TreeMap<XSSFComment, Integer>(new Comparator<XSSFComment>() {
128
            @Override
129
            public int compare(XSSFComment o1, XSSFComment o2) {
130
                int column1 = o1.getColumn();
131
                int column2 = o2.getColumn();
132
                
133
                if(column1 == column2) {
134
                    // ordering is not important when column is equal, but don't return zero to still 
135
                    // get multiple comments per column into the map
136
                    return o1.hashCode() - o2.hashCode();
137
                }
138
139
                // when shifting down, sort higher column-values first
140
                if(n > 0) {
141
                    return column1 < column2 ? 1 : -1;
142
                } else {
143
                    // sort lower-column values first when shifting up
144
                    return column1 > column2 ? 1 : -1;
145
                }
146
            }
147
        });
148
        
149
        if(sheetComments != null){
150
            CTCommentList lst = sheetComments.getCTComments().getCommentList();
151
            for (CTComment comment : lst.getCommentArray()) {
152
                String oldRef = comment.getRef();
153
                CellReference ref = new CellReference(oldRef);
154
                
155
                int newColumnIndex = XSSFShiftingManager.shiftedItemIndex(startColumnIndex, endColumnIndex, n, ref.getCol());
156
                
157
                // is there a change necessary for the current row?
158
                if(newColumnIndex != ref.getCol()) {
159
                    XSSFComment xssfComment = new XSSFComment(sheetComments, comment,
160
                            vml == null ? null : vml.findCommentShape(ref.getRow(), ref.getCol()));
161
                    commentsToShift.put(xssfComment, newColumnIndex);
162
                }
163
            }
164
	        for(Map.Entry<XSSFComment, Integer> entry : commentsToShift.entrySet()) 
165
	            entry.getKey().setColumn(entry.getValue());
166
        }
167
    	
168
    }
169
	
170
    public static void cloneCellValue(org.apache.poi.ss.usermodel.Cell oldCell, org.apache.poi.ss.usermodel.Cell newCell) {
171
        newCell.setCellComment(oldCell.getCellComment());
172
        switch (oldCell.getCellTypeEnum()) {
173
	        case STRING:
174
	            newCell.setCellValue(oldCell.getStringCellValue());
175
	            break;
176
	        case NUMERIC:
177
	            newCell.setCellValue(oldCell.getNumericCellValue());
178
	            break;
179
	        case BOOLEAN:
180
	            newCell.setCellValue(oldCell.getBooleanCellValue());
181
	            break;
182
	        case FORMULA:
183
	            newCell.setCellFormula(oldCell.getCellFormula());
184
	            break;
185
	        case ERROR:
186
	            newCell.setCellErrorValue(oldCell.getErrorCellValue());
187
	        case BLANK:
188
	        case _NONE:
189
	            break;
190
	    }
191
	}
192
193
}
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java (-209 / +110 lines)
Lines 18-24 Link Here
18
package org.apache.poi.xssf.usermodel.helpers;
18
package org.apache.poi.xssf.usermodel.helpers;
19
19
20
import java.util.ArrayList;
20
import java.util.ArrayList;
21
import java.util.Comparator;
22
import java.util.Iterator;
21
import java.util.List;
23
import java.util.List;
24
import java.util.Map;
25
import java.util.SortedMap;
26
import java.util.TreeMap;
22
27
23
import org.apache.poi.ss.formula.FormulaParseException;
28
import org.apache.poi.ss.formula.FormulaParseException;
24
import org.apache.poi.ss.formula.FormulaParser;
29
import org.apache.poi.ss.formula.FormulaParser;
Lines 36-53 import org.apache.poi.ss.usermodel.Sheet; Link Here
36
import org.apache.poi.ss.usermodel.Workbook;
41
import org.apache.poi.ss.usermodel.Workbook;
37
import org.apache.poi.ss.usermodel.helpers.RowShifter;
42
import org.apache.poi.ss.usermodel.helpers.RowShifter;
38
import org.apache.poi.ss.util.CellRangeAddress;
43
import org.apache.poi.ss.util.CellRangeAddress;
44
import org.apache.poi.ss.util.CellReference;
39
import org.apache.poi.util.Internal;
45
import org.apache.poi.util.Internal;
40
import org.apache.poi.util.POILogFactory;
46
import org.apache.poi.util.POILogFactory;
41
import org.apache.poi.util.POILogger;
47
import org.apache.poi.util.POILogger;
48
import org.apache.poi.xssf.model.CommentsTable;
42
import org.apache.poi.xssf.usermodel.XSSFCell;
49
import org.apache.poi.xssf.usermodel.XSSFCell;
50
import org.apache.poi.xssf.usermodel.XSSFComment;
43
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
51
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
44
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
52
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
45
import org.apache.poi.xssf.usermodel.XSSFRow;
53
import org.apache.poi.xssf.usermodel.XSSFRow;
46
import org.apache.poi.xssf.usermodel.XSSFSheet;
54
import org.apache.poi.xssf.usermodel.XSSFSheet;
55
import org.apache.poi.xssf.usermodel.XSSFVMLDrawing;
47
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
56
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
48
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
57
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
49
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
58
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
50
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule;
59
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule;
60
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment;
61
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCommentList;
51
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTConditionalFormatting;
62
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTConditionalFormatting;
52
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
63
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
53
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
64
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
Lines 60-68 import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType; Link Here
60
public final class XSSFRowShifter extends RowShifter {
71
public final class XSSFRowShifter extends RowShifter {
61
    private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);
72
    private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);
62
73
74
    private XSSFShiftingManager formulaShiftingManager;
75
    
63
    public XSSFRowShifter(XSSFSheet sh) {
76
    public XSSFRowShifter(XSSFSheet sh) {
64
        super(sh);
77
        super(sh);
65
    }
78
    }
79
    public XSSFRowShifter(Sheet sh, FormulaShifter shifter) {
80
        super(sh, shifter);
81
        formulaShiftingManager = new XSSFShiftingManager(sh, shifter);
82
    }
83
    
84
    // do the actual moving and also adjust comments/rowHeight
85
    // we need to sort it in a way so the shifting does not mess up the structures, 
86
    // i.e. when shifting down, start from down and go up, when shifting up, vice-versa
87
    public void doShiftingAndProcessComments(XSSFVMLDrawing vml, int startRow, int endRow, final int n, 
88
    		boolean copyRowHeight, Iterator<Row> rowIterator, CommentsTable sheetComments){
89
        SortedMap<XSSFComment, Integer> commentsToShift = new TreeMap<XSSFComment, Integer>(new Comparator<XSSFComment>() {
90
            @Override
91
            public int compare(XSSFComment o1, XSSFComment o2) {
92
                int row1 = o1.getRow();
93
                int row2 = o2.getRow();
94
                
95
                if(row1 == row2) {
96
                    // ordering is not important when row is equal, but don't return zero to still 
97
                    // get multiple comments per row into the map
98
                    return o1.hashCode() - o2.hashCode();
99
                }
100
101
                // when shifting down, sort higher row-values first
102
                if(n > 0) {
103
                    return row1 < row2 ? 1 : -1;
104
                } else {
105
                    // sort lower-row values first when shifting up
106
                    return row1 > row2 ? 1 : -1;
107
                }
108
            }
109
        });
110
        
111
        for (Iterator<Row> it = rowIterator; it.hasNext() ; ) {
112
            XSSFRow row = (XSSFRow)it.next();
113
            int rownum = row.getRowNum();
114
115
            if(sheetComments != null){
116
                // calculate the new rownum
117
                int newrownum = XSSFShiftingManager.shiftedItemIndex(startRow, endRow, n, rownum);
118
                
119
                // is there a change necessary for the current row?
120
                if(newrownum != rownum) {
121
                    CTCommentList lst = sheetComments.getCTComments().getCommentList();
122
                    for (CTComment comment : lst.getCommentArray()) {
123
                        String oldRef = comment.getRef();
124
                        CellReference ref = new CellReference(oldRef);
125
                        
126
                        // is this comment part of the current row?
127
                        if(ref.getRow() == rownum) {
128
                            XSSFComment xssfComment = new XSSFComment(sheetComments, comment,
129
                                    vml == null ? null : vml.findCommentShape(rownum, ref.getCol()));
130
                            
131
                            // we should not perform the shifting right here as we would then find
132
                            // already shifted comments and would shift them again...
133
                            commentsToShift.put(xssfComment, newrownum);
134
                        }
135
                    }
136
                }
137
            }
138
139
            if(rownum < startRow || rownum > endRow) {
140
                continue;
141
            }
142
            if (!copyRowHeight) {
143
                row.setHeight((short)-1);
144
            }
145
            row.shift(n);
146
        }
147
        
148
        // adjust all the affected comment-structures now
149
        // the Map is sorted and thus provides them in the order that we need here, 
150
        // i.e. from down to up if shifting down, vice-versa otherwise
151
        for(Map.Entry<XSSFComment, Integer> entry : commentsToShift.entrySet()) {
152
            entry.getKey().setRow(entry.getValue());
153
            int x = 5;
154
        }
155
    	
156
    }
157
    
158
66
    
159
    
67
    /**
160
    /**
68
     * Shift merged regions
161
     * Shift merged regions
Lines 76-300 public final class XSSFRowShifter extends RowShifter { Link Here
76
    public List<CellRangeAddress> shiftMerged(int startRow, int endRow, int n) {
169
    public List<CellRangeAddress> shiftMerged(int startRow, int endRow, int n) {
77
        return shiftMergedRegions(startRow, endRow, n);
170
        return shiftMergedRegions(startRow, endRow, n);
78
    }
171
    }
79
172
    
80
    /**
173
    /**
81
     * Updated named ranges
174
        @deprecated, use FormulaShiftingManager.updateNamedRanges() directly instead
82
     */
175
    */
83
    public void updateNamedRanges(FormulaShifter shifter) {
176
    public void updateNamedRanges(FormulaShifter shifter) {
84
        Workbook wb = sheet.getWorkbook();
177
        formulaShiftingManager.updateNamedRanges();
85
        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);
86
        for (Name name : wb.getAllNames()) {
87
            String formula = name.getRefersToFormula();
88
            int sheetIndex = name.getSheetIndex();
89
            final int rowIndex = -1; //don't care, named ranges are not allowed to include structured references
90
91
            Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.NAMEDRANGE, sheetIndex, rowIndex);
92
            if (shifter.adjustFormula(ptgs, sheetIndex)) {
93
                String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
94
                name.setRefersToFormula(shiftedFmla);
95
            }
96
        }
97
    }
178
    }
98
99
    /**
179
    /**
100
     * Update formulas.
180
        @deprecated, use FormulaShiftingManager.updateFormulas() directly instead
101
     */
181
    */ 
102
    public void updateFormulas(FormulaShifter shifter) {
182
    public void updateFormulas(FormulaShifter shifter) {
103
        //update formulas on the parent sheet
183
        formulaShiftingManager.updateFormulas();
104
        updateSheetFormulas(sheet, shifter);
105
106
        //update formulas on other sheets
107
        Workbook wb = sheet.getWorkbook();
108
        for (Sheet sh : wb) {
109
            if (sheet == sh) continue;
110
            updateSheetFormulas(sh, shifter);
111
        }
112
    }
113
114
    private void updateSheetFormulas(Sheet sh, FormulaShifter shifter) {
115
        for (Row r : sh) {
116
            XSSFRow row = (XSSFRow) r;
117
            updateRowFormulas(row, shifter);
118
        }
119
    }
120
121
    /**
122
     * Update the formulas in specified row using the formula shifting policy specified by shifter
123
     *
124
     * @param row the row to update the formulas on
125
     * @param shifter the formula shifting policy
126
     */
127
    @Internal
128
    public void updateRowFormulas(Row row, FormulaShifter shifter) {
129
        XSSFSheet sheet = (XSSFSheet) row.getSheet();
130
        for (Cell c : row) {
131
            XSSFCell cell = (XSSFCell) c;
132
133
            CTCell ctCell = cell.getCTCell();
134
            if (ctCell.isSetF()) {
135
                CTCellFormula f = ctCell.getF();
136
                String formula = f.getStringValue();
137
                if (formula.length() > 0) {
138
                    String shiftedFormula = shiftFormula(row, formula, shifter);
139
                    if (shiftedFormula != null) {
140
                        f.setStringValue(shiftedFormula);
141
                        if(f.getT() == STCellFormulaType.SHARED){
142
                            int si = (int)f.getSi();
143
                            CTCellFormula sf = sheet.getSharedFormula(si);
144
                            sf.setStringValue(shiftedFormula);
145
                            updateRefInCTCellFormula(row, shifter, sf);
146
                        }
147
                    }
148
149
                }
150
151
                //Range of cells which the formula applies to.
152
                updateRefInCTCellFormula(row, shifter, f);
153
            }
154
155
        }
156
    }
184
    }
157
158
    private void updateRefInCTCellFormula(Row row, FormulaShifter shifter, CTCellFormula f) {
159
        if (f.isSetRef()) { //Range of cells which the formula applies to.
160
            String ref = f.getRef();
161
            String shiftedRef = shiftFormula(row, ref, shifter);
162
            if (shiftedRef != null) f.setRef(shiftedRef);
163
        }
164
    }
165
166
    /**
185
    /**
167
     * Shift a formula using the supplied FormulaShifter
186
        @deprecated, use FormulaShiftingManager.updateConditionalFormatting() directly instead
168
     *
187
     */ 
169
     * @param row     the row of the cell this formula belongs to. Used to get a reference to the parent workbook.
170
     * @param formula the formula to shift
171
     * @param shifter the FormulaShifter object that operates on the parsed formula tokens
172
     * @return the shifted formula if the formula was changed,
173
     *         <code>null</code> if the formula wasn't modified
174
     */
175
    private static String shiftFormula(Row row, String formula, FormulaShifter shifter) {
176
        Sheet sheet = row.getSheet();
177
        Workbook wb = sheet.getWorkbook();
178
        int sheetIndex = wb.getSheetIndex(sheet);
179
        final int rowIndex = row.getRowNum();
180
        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);
181
        
182
        try {
183
            Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
184
            String shiftedFmla = null;
185
            if (shifter.adjustFormula(ptgs, sheetIndex)) {
186
                shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
187
            }
188
            return shiftedFmla;
189
        } catch (FormulaParseException fpe) {
190
            // Log, but don't change, rather than breaking
191
            logger.log(POILogger.WARN, "Error shifting formula on row ", row.getRowNum(), fpe);
192
            return formula;
193
        }
194
    }
195
196
    public void updateConditionalFormatting(FormulaShifter shifter) {
188
    public void updateConditionalFormatting(FormulaShifter shifter) {
197
        XSSFSheet xsheet = (XSSFSheet) sheet;
189
        formulaShiftingManager.updateConditionalFormatting();
198
        XSSFWorkbook wb = xsheet.getWorkbook();
190
    }    
199
        int sheetIndex = wb.getSheetIndex(sheet);
200
        final int rowIndex = -1; //don't care, structured references not allowed in conditional formatting
201
202
        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
203
        CTWorksheet ctWorksheet = xsheet.getCTWorksheet();
204
        CTConditionalFormatting[] conditionalFormattingArray = ctWorksheet.getConditionalFormattingArray();
205
        // iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j)
206
        for (int j = conditionalFormattingArray.length - 1; j >= 0; j--) {
207
            CTConditionalFormatting cf = conditionalFormattingArray[j];
208
209
            ArrayList<CellRangeAddress> cellRanges = new ArrayList<>();
210
            for (Object stRef : cf.getSqref()) {
211
                String[] regions = stRef.toString().split(" ");
212
                for (String region : regions) {
213
                    cellRanges.add(CellRangeAddress.valueOf(region));
214
                }
215
            }
216
217
            boolean changed = false;
218
            List<CellRangeAddress> temp = new ArrayList<>();
219
            for (CellRangeAddress craOld : cellRanges) {
220
                CellRangeAddress craNew = shiftRange(shifter, craOld, sheetIndex);
221
                if (craNew == null) {
222
                    changed = true;
223
                    continue;
224
                }
225
                temp.add(craNew);
226
                if (craNew != craOld) {
227
                    changed = true;
228
                }
229
            }
230
231
            if (changed) {
232
                int nRanges = temp.size();
233
                if (nRanges == 0) {
234
                    ctWorksheet.removeConditionalFormatting(j);
235
                    continue;
236
                }
237
                List<String> refs = new ArrayList<>();
238
                for(CellRangeAddress a : temp) refs.add(a.formatAsString());
239
                cf.setSqref(refs);
240
            }
241
242
            for(CTCfRule cfRule : cf.getCfRuleArray()){
243
                String[] formulaArray = cfRule.getFormulaArray();
244
                for (int i = 0; i < formulaArray.length; i++) {
245
                    String formula = formulaArray[i];
246
                    Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
247
                    if (shifter.adjustFormula(ptgs, sheetIndex)) {
248
                        String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
249
                        cfRule.setFormulaArray(i, shiftedFmla);
250
                    }
251
                }
252
            }
253
        }
254
    }
255
    
256
    /**
191
    /**
257
     * Shift the Hyperlink anchors (not the hyperlink text, even if the hyperlink
192
        @deprecated, use FormulaShiftingManager.updateHyperlinks() directly instead
258
     * is of type LINK_DOCUMENT and refers to a cell that was shifted). Hyperlinks
193
     */ 
259
     * do not track the content they point to.
260
     *
261
     * @param shifter
262
     */
263
    public void updateHyperlinks(FormulaShifter shifter) {
194
    public void updateHyperlinks(FormulaShifter shifter) {
264
        int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet);
195
        formulaShiftingManager.updateHyperlinks();
265
        List<? extends Hyperlink> hyperlinkList = sheet.getHyperlinkList();
266
        
267
        for (Hyperlink hyperlink : hyperlinkList) {
268
            XSSFHyperlink xhyperlink = (XSSFHyperlink) hyperlink;
269
            String cellRef = xhyperlink.getCellRef();
270
            CellRangeAddress cra = CellRangeAddress.valueOf(cellRef);
271
            CellRangeAddress shiftedRange = shiftRange(shifter, cra, sheetIndex);
272
            if (shiftedRange != null && shiftedRange != cra) {
273
                // shiftedRange should not be null. If shiftedRange is null, that means
274
                // that a hyperlink wasn't deleted at the beginning of shiftRows when
275
                // identifying rows that should be removed because they will be overwritten
276
                xhyperlink.setCellReference(shiftedRange.formatAsString());
277
            }
278
        }
279
    }
196
    }
280
197
    public void updateRowFormulas(Row row, FormulaShifter shifter) {
281
    private static CellRangeAddress shiftRange(FormulaShifter shifter, CellRangeAddress cra, int currentExternSheetIx) {
198
        // TODO Auto-generated method stub
282
        // FormulaShifter works well in terms of Ptgs - so convert CellRangeAddress to AreaPtg (and back) here
199
        
283
        AreaPtg aptg = new AreaPtg(cra.getFirstRow(), cra.getLastRow(), cra.getFirstColumn(), cra.getLastColumn(), false, false, false, false);
284
        Ptg[] ptgs = { aptg, };
285
286
        if (!shifter.adjustFormula(ptgs, currentExternSheetIx)) {
287
            return cra;
288
        }
289
        Ptg ptg0 = ptgs[0];
290
        if (ptg0 instanceof AreaPtg) {
291
            AreaPtg bptg = (AreaPtg) ptg0;
292
            return new CellRangeAddress(bptg.getFirstRow(), bptg.getLastRow(), bptg.getFirstColumn(), bptg.getLastColumn());
293
        }
294
        if (ptg0 instanceof AreaErrPtg) {
295
            return null;
296
        }
297
        throw new IllegalStateException("Unexpected shifted ptg class (" + ptg0.getClass().getName() + ")");
298
    }
200
    }
299
300
}
201
}
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFShiftingManager.java (+286 lines)
Line 0 Link Here
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.Name;
17
import org.apache.poi.ss.usermodel.Row;
18
import org.apache.poi.ss.usermodel.Sheet;
19
import org.apache.poi.ss.usermodel.Workbook;
20
import org.apache.poi.ss.util.CellRangeAddress;
21
import org.apache.poi.util.Internal;
22
import org.apache.poi.util.POILogFactory;
23
import org.apache.poi.util.POILogger;
24
import org.apache.poi.xssf.usermodel.XSSFCell;
25
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
26
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
27
import org.apache.poi.xssf.usermodel.XSSFRow;
28
import org.apache.poi.xssf.usermodel.XSSFSheet;
29
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
30
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
31
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
32
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule;
33
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTConditionalFormatting;
34
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
35
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
36
37
public class XSSFShiftingManager {
38
39
    private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);
40
41
    protected final Sheet shiftingSheet;
42
    protected FormulaShifter shifter;
43
44
    public XSSFShiftingManager(Sheet shiftingSheet, FormulaShifter shifter){
45
    	this.shiftingSheet = shiftingSheet;
46
        this.shifter = shifter;
47
	}
48
    
49
    public void updateFormulas() {
50
        //update formulas on the parent sheet
51
        updateSheetFormulas(shiftingSheet);
52
53
        //update formulas on other sheets
54
        Workbook wb = shiftingSheet.getWorkbook();
55
        for (Sheet sh : wb) {
56
            if (shiftingSheet == sh) continue;
57
            updateSheetFormulas(sh);
58
        }
59
    }
60
61
    private void updateSheetFormulas(Sheet sh) {
62
        for (Row r : sh) {
63
            XSSFRow row = (XSSFRow) r;
64
            updateRowFormulas(row);
65
        }
66
    }
67
68
    /**
69
     * Update the formulas in specified row using the formula shifting policy specified by shifter
70
     *
71
     * @param row the row to update the formulas on
72
     * @param shifter the formula shifting policy
73
     */
74
    @Internal
75
    public void updateRowFormulas(Row row) {
76
        for (Cell c : row) {
77
           	updateCellFormula(row, (XSSFCell) c);
78
        }
79
    }
80
    
81
    public void updateCellFormula(Row row, XSSFCell cell){
82
        CTCell ctCell = cell.getCTCell();
83
        if (ctCell.isSetF()) {
84
            CTCellFormula f = ctCell.getF();
85
            String formula = f.getStringValue();
86
            if (formula.length() > 0) {
87
                String shiftedFormula = shiftFormula(row, formula);
88
                if (shiftedFormula != null) {
89
                    f.setStringValue(shiftedFormula);
90
                    if(f.getT() == STCellFormulaType.SHARED){
91
                        int si = (int)f.getSi();
92
                        XSSFSheet sheet = (XSSFSheet) row.getSheet();
93
                        CTCellFormula sf = sheet.getSharedFormula(si);
94
                        sf.setStringValue(shiftedFormula);
95
                        updateRefInCTCellFormula(row, sf);
96
                    }
97
                }
98
            }
99
            //Range of cells which the formula applies to.
100
            updateRefInCTCellFormula(row, f);
101
        }
102
    }
103
    private void updateRefInCTCellFormula(Row row, CTCellFormula f) {
104
        if (f.isSetRef()) { //Range of cells which the formula applies to.
105
            String ref = f.getRef();
106
            String shiftedRef = shiftFormula(row, ref);
107
            if (shiftedRef != null) f.setRef(shiftedRef);
108
        }
109
    }
110
111
    /**
112
     * Shift a formula using the supplied FormulaShifter
113
     *
114
     * @param row     the row of the cell this formula belongs to. Used to get a reference to the parent workbook.
115
     * @param formula the formula to shift
116
     * @param shifter the FormulaShifter object that operates on the parsed formula tokens
117
     * @return the shifted formula if the formula was changed,
118
     *         <code>null</code> if the formula wasn't modified
119
     */
120
    private String shiftFormula(Row row, String formula) {
121
        Sheet sheet = row.getSheet();
122
        Workbook wb = sheet.getWorkbook();
123
        int sheetIndex = wb.getSheetIndex(sheet);
124
        final int rowIndex = row.getRowNum();
125
        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);
126
        
127
        try {
128
            Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
129
            String shiftedFmla = null;
130
            if (shifter.adjustFormula(ptgs, sheetIndex)) {
131
                shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
132
            }
133
            return shiftedFmla;
134
        } catch (FormulaParseException fpe) {
135
            // Log, but don't change, rather than breaking
136
            logger.log(POILogger.WARN, "Error shifting formula on row ", row.getRowNum(), fpe);
137
            return formula;
138
        }
139
    }
140
141
    
142
    public void updateConditionalFormatting() {
143
        XSSFSheet xsheet = (XSSFSheet) shiftingSheet;
144
        XSSFWorkbook wb = xsheet.getWorkbook();
145
        int sheetIndex = wb.getSheetIndex(shiftingSheet);
146
        final int rowIndex = -1; //don't care, structured references not allowed in conditional formatting
147
148
        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
149
        CTWorksheet ctWorksheet = xsheet.getCTWorksheet();
150
        CTConditionalFormatting[] conditionalFormattingArray = ctWorksheet.getConditionalFormattingArray();
151
        // iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j)
152
        for (int j = conditionalFormattingArray.length - 1; j >= 0; j--) {
153
            CTConditionalFormatting cf = conditionalFormattingArray[j];
154
155
            ArrayList<CellRangeAddress> cellRanges = new ArrayList<CellRangeAddress>();
156
            for (Object stRef : cf.getSqref()) {
157
                String[] regions = stRef.toString().split(" ");
158
                for (String region : regions) {
159
                    cellRanges.add(CellRangeAddress.valueOf(region));
160
                }
161
            }
162
163
            boolean changed = false;
164
            List<CellRangeAddress> temp = new ArrayList<CellRangeAddress>();
165
            for (CellRangeAddress craOld : cellRanges) {
166
                CellRangeAddress craNew = shiftRange(shifter, craOld, sheetIndex);
167
                if (craNew == null) {
168
                    changed = true;
169
                    continue;
170
                }
171
                temp.add(craNew);
172
                if (craNew != craOld) {
173
                    changed = true;
174
                }
175
            }
176
177
            if (changed) {
178
                int nRanges = temp.size();
179
                if (nRanges == 0) {
180
                    ctWorksheet.removeConditionalFormatting(j);
181
                    continue;
182
                }
183
                List<String> refs = new ArrayList<String>();
184
                for(CellRangeAddress a : temp) refs.add(a.formatAsString());
185
                cf.setSqref(refs);
186
            }
187
188
            for(CTCfRule cfRule : cf.getCfRuleArray()){
189
                String[] formulaArray = cfRule.getFormulaArray();
190
                for (int i = 0; i < formulaArray.length; i++) {
191
                    String formula = formulaArray[i];
192
                    Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
193
                    if (shifter.adjustFormula(ptgs, sheetIndex)) {
194
                        String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
195
                        cfRule.setFormulaArray(i, shiftedFmla);
196
                    }
197
                }
198
            }
199
        }
200
    }
201
    
202
    private static CellRangeAddress shiftRange(FormulaShifter shifter, CellRangeAddress cra, int currentExternSheetIx) {
203
        // FormulaShifter works well in terms of Ptgs - so convert CellRangeAddress to AreaPtg (and back) here
204
        AreaPtg aptg = new AreaPtg(cra.getFirstRow(), cra.getLastRow(), cra.getFirstColumn(), cra.getLastColumn(), false, false, false, false);
205
        Ptg[] ptgs = { aptg, };
206
207
        if (!shifter.adjustFormula(ptgs, currentExternSheetIx)) {
208
            return cra;
209
        }
210
        Ptg ptg0 = ptgs[0];
211
        if (ptg0 instanceof AreaPtg) {
212
            AreaPtg bptg = (AreaPtg) ptg0;
213
            return new CellRangeAddress(bptg.getFirstRow(), bptg.getLastRow(), bptg.getFirstColumn(), bptg.getLastColumn());
214
        }
215
        if (ptg0 instanceof AreaErrPtg) {
216
            return null;
217
        }
218
        throw new IllegalStateException("Unexpected shifted ptg class (" + ptg0.getClass().getName() + ")");
219
    }
220
    
221
    /**
222
     * Shift the Hyperlink anchors (not the hyperlink text, even if the hyperlink
223
     * is of type LINK_DOCUMENT and refers to a cell that was shifted). Hyperlinks
224
     * do not track the content they point to.
225
     *
226
     * @param shifter
227
     */
228
    public void updateHyperlinks() {
229
        int sheetIndex = shiftingSheet.getWorkbook().getSheetIndex(shiftingSheet);
230
        List<? extends Hyperlink> hyperlinkList = shiftingSheet.getHyperlinkList();
231
        
232
        for (Hyperlink hyperlink : hyperlinkList) {
233
            XSSFHyperlink xhyperlink = (XSSFHyperlink) hyperlink;
234
            String cellRef = xhyperlink.getCellRef();
235
            CellRangeAddress cra = CellRangeAddress.valueOf(cellRef);
236
            CellRangeAddress shiftedRange = shiftRange(shifter, cra, sheetIndex);
237
            if (shiftedRange != null && shiftedRange != cra) {
238
                // shiftedRange should not be null. If shiftedRange is null, that means
239
                // that a hyperlink wasn't deleted at the beginning of shiftRows when
240
                // identifying rows that should be removed because they will be overwritten
241
                xhyperlink.setCellReference(shiftedRange.formatAsString());
242
            }
243
        }
244
    }
245
246
    public void updateNamedRanges() {
247
        Workbook wb = shiftingSheet.getWorkbook();
248
        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);
249
        for (Name name : wb.getAllNames()) {
250
            String formula = name.getRefersToFormula();
251
            int sheetIndex = name.getSheetIndex();
252
            final int rowIndex = -1; //don't care, named ranges are not allowed to include structured references
253
254
            Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.NAMEDRANGE, sheetIndex, rowIndex);
255
            if (shifter.adjustFormula(ptgs, sheetIndex)) {
256
                String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
257
                name.setRefersToFormula(shiftedFmla);
258
            }
259
        }
260
    }
261
262
    public static int shiftedItemIndex(int startShiftingIndex, int endShiftingIndex, int shiftingStep, int originalItemIndex) {
263
        // no change if before any affected item
264
        if(originalItemIndex < startShiftingIndex && (shiftingStep > 0 || (startShiftingIndex - originalItemIndex) > shiftingStep)) {
265
            return originalItemIndex;
266
        }
267
        // no change if after any affected item
268
        if(originalItemIndex > endShiftingIndex && (shiftingStep < 0 || (originalItemIndex - endShiftingIndex) > shiftingStep)) {
269
            return originalItemIndex;
270
        }
271
        // item before and things are moved up
272
        if(originalItemIndex < startShiftingIndex) {
273
            // item is moved down by the shifting
274
            return originalItemIndex + (endShiftingIndex - startShiftingIndex);
275
        }
276
        // item is after and things are moved down
277
        if(originalItemIndex > endShiftingIndex) {
278
            // item is moved up by the shifting
279
            return originalItemIndex - (endShiftingIndex - startShiftingIndex);
280
        }
281
        // item is part of the shifted block
282
        return originalItemIndex + shiftingStep;
283
    }
284
285
286
}
(-)a/src/testcases/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifterTest.java (-1 / +384 lines)
Line 0 Link Here
0
- 
1
package org.apache.poi.xssf.usermodel.helpers;
2
3
import static org.apache.poi.POITestCase.skipTest;
4
import static org.apache.poi.POITestCase.testPassesNow;
5
import static org.junit.Assert.assertEquals;
6
import static org.junit.Assert.assertNotNull;
7
import static org.junit.Assert.assertNull;
8
import static org.junit.Assert.assertTrue;
9
import static org.junit.Assert.fail;
10
import static org.junit.Assume.assumeTrue;
11
12
import java.io.IOException;
13
import java.util.ArrayList;
14
import java.util.List;
15
16
import org.apache.poi.common.usermodel.HyperlinkType;
17
import org.apache.poi.ss.ITestDataProvider;
18
import org.apache.poi.ss.usermodel.*;
19
import org.apache.poi.ss.util.CellAddress;
20
import org.apache.poi.ss.util.CellRangeAddress;
21
import org.apache.poi.ss.util.CellUtil;
22
import org.apache.poi.xssf.XSSFITestDataProvider;
23
import org.apache.poi.xssf.XSSFTestDataSamples;
24
import org.apache.poi.xssf.usermodel.*;
25
import org.junit.Before;
26
import org.junit.Ignore;
27
import org.junit.Test;
28
import org.slf4j.Logger;
29
import org.slf4j.LoggerFactory;
30
31
public class XSSFColumnShifterTest {
32
33
	//private static Logger log = LoggerFactory.getLogger(XSSFColumnShifterTest.class + "_T");
34
	private XSSFSheet sheet1, sheet2;
35
	private Workbook wb07;
36
37
    protected final ITestDataProvider _testDataProvider;
38
39
	public XSSFColumnShifterTest(){
40
        _testDataProvider = XSSFITestDataProvider.instance; 
41
    }
42
43
	@Before
44
	public void init() {
45
		wb07 = new XSSFWorkbook();
46
		sheet1 = (XSSFSheet) wb07.createSheet("sheet1");
47
		XSSFRow row = sheet1.createRow(0);
48
		row.createCell(0, CellType.NUMERIC).setCellValue(0);
49
		row.createCell(1, CellType.NUMERIC).setCellValue(1);
50
		XSSFCell c1 = row.createCell(2, CellType.NUMERIC);
51
		c1.setCellValue(2);
52
53
		row = sheet1.createRow(1);
54
		row.createCell(0, CellType.NUMERIC).setCellValue(0.1);
55
		row.createCell(1, CellType.NUMERIC).setCellValue(1.1);
56
		row.createCell(2, CellType.NUMERIC).setCellValue(2.1);
57
		row = sheet1.createRow(2);
58
		row.createCell(0, CellType.NUMERIC).setCellValue(0.2);
59
		row.createCell(1, CellType.NUMERIC).setCellValue(1.2);
60
		row.createCell(2, CellType.NUMERIC).setCellValue(2.2);
61
		row = sheet1.createRow(3);
62
		row.createCell(0, CellType.FORMULA).setCellFormula("A2*B3");
63
		row.createCell(1, CellType.NUMERIC).setCellValue(1.3);
64
		row.createCell(2, CellType.FORMULA).setCellFormula("B1-B3");
65
		row = sheet1.createRow(4);
66
		row.createCell(0, CellType.FORMULA).setCellFormula("SUM(C1:C4)");
67
		row.createCell(1, CellType.FORMULA).setCellFormula("SUM(A3:C3)");
68
		row.createCell(2, CellType.FORMULA).setCellFormula("$C1+C$2");
69
		row = sheet1.createRow(5);
70
		row.createCell(1, CellType.NUMERIC).setCellValue(1.5);
71
		/*
72
		 * sheet2 = (XSSFSheet)wb07.createSheet("sheet2"); row =
73
		 * sheet2.createRow(0); row.createCell(0,
74
		 * CellType.NUMERIC).setCellValue(10); row.createCell(1,
75
		 * CellType.NUMERIC).setCellValue(11); row.createCell(2,
76
		 * CellType.FORMULA).setCellFormula("SUM(Sheet1!B3:C3)"); row =
77
		 * sheet2.createRow(1); row.createCell(0,
78
		 * CellType.NUMERIC).setCellValue(21); row.createCell(1,
79
		 * CellType.NUMERIC).setCellValue(22); row.createCell(2,
80
		 * CellType.NUMERIC).setCellValue(23); row = sheet2.createRow(2);
81
		 * row.createCell(0,
82
		 * CellType.FORMULA).setCellFormula("Sheet1!A4+Sheet1!C2+A2");
83
		 * row.createCell(1,
84
		 * CellType.FORMULA).setCellFormula("SUM(Sheet1!A3:$C3)"); row =
85
		 * sheet2.createRow(3); row.createCell(0,
86
		 * CellType.STRING).setCellValue("dummy");
87
		 */
88
		// writeSheetToLog(sheet1);
89
	}
90
91
	@Test
92
	public void testInsertOneColumn() {
93
		sheet1.shiftColumns(1, 2, 1);
94
		writeSheetToLog(sheet1);
95
		String formulaA4 = sheet1.getRow(3).getCell(0).getCellFormula();
96
		assertEquals("A2*C3", formulaA4);
97
		String formulaC4 = sheet1.getRow(3).getCell(3).getCellFormula();
98
		assertEquals("C1-C3", formulaC4);
99
		String formulaB5 = sheet1.getRow(4).getCell(2).getCellFormula();
100
		assertEquals("SUM(A3:D3)", formulaB5);
101
		String formulaD5 = sheet1.getRow(4).getCell(3).getCellFormula(); // $C1+C$2
102
		assertEquals("$D1+D$2", formulaD5);
103
104
		String newb5Empty = sheet1.getRow(4).getCell(1).getStringCellValue();
105
		assertEquals(newb5Empty, "");
106
	}
107
108
	@Test
109
	public void testInsertTwoColumns() {
110
		sheet1.shiftColumns(1, 2, 2);
111
		String formulaA4 = sheet1.getRow(3).getCell(0).getCellFormula();
112
		assertEquals("A2*D3", formulaA4);
113
		String formulaD4 = sheet1.getRow(3).getCell(4).getCellFormula();
114
		assertEquals("D1-D3", formulaD4);
115
		String formulaD5 = sheet1.getRow(4).getCell(3).getCellFormula();
116
		assertEquals("SUM(A3:E3)", formulaD5);
117
118
		String b5Empty = sheet1.getRow(4).getCell(1).getStringCellValue();
119
		assertEquals(b5Empty, "");
120
		Object c6Null = sheet1.getRow(5).getCell(2); // null cell A5 is shifted
121
														// for 2 columns, so now
122
														// c5 should be null
123
		assertEquals(c6Null, null);
124
	}
125
126
	public static void writeSheetToLog(Sheet sheet) {
127
		int rowIndex = sheet.getFirstRowNum();/*
128
		while (rowIndex <= sheet.getLastRowNum()) {
129
			Row row = sheet.getRow(rowIndex);
130
			if (row == null)
131
				;//log.trace("null row!");
132
			else
133
				log.trace(String.format(
134
						"%1$12s; %2$12s; %3$12s; %4$12s; %5$12s; %6$12s; %7$12s; %8$12s; %9$12s; %10$12s; %11$12s",
135
						row.getCell(0) != null ? row.getCell(0).getCellComment() : "null",
136
						row.getCell(1) != null ? row.getCell(1).getCellComment() : "null",
137
						row.getCell(2) != null ? row.getCell(2).getCellComment() : "null",
138
						row.getCell(3) != null ? row.getCell(3).getCellComment() : "null",
139
						row.getCell(4) != null ? row.getCell(4).getCellComment() : "null",
140
						row.getCell(5) != null ? row.getCell(5).getCellComment() : "null",
141
						row.getCell(6) != null ? row.getCell(6).getCellComment() : "null",
142
						row.getCell(7) != null ? row.getCell(7).getCellComment() : "null",
143
						row.getCell(8) != null ? row.getCell(8).getCellComment() : "null",
144
						row.getCell(9) != null ? row.getCell(9).getCellComment() : "null",
145
						row.getCell(10) != null ? row.getCell(10).getCellComment() : "null"));
146
			rowIndex++;
147
		}
148
		log.trace("");*/
149
	}
150
151
	@Test
152
	public void testShiftHyperlinks() throws IOException {
153
		Workbook wb = _testDataProvider.createWorkbook();
154
		Sheet sheet = wb.createSheet("test");
155
		Row row = sheet.createRow(0);
156
157
		// How to create hyperlinks
158
		// https://poi.apache.org/spreadsheet/quick-guide.html#Hyperlinks
159
		CreationHelper helper = wb.getCreationHelper();
160
		CellStyle hlinkStyle = wb.createCellStyle();
161
		Font hlinkFont = wb.createFont();
162
		hlinkFont.setUnderline(Font.U_SINGLE);
163
		hlinkFont.setColor(IndexedColors.BLUE.getIndex());
164
		hlinkStyle.setFont(hlinkFont);
165
166
		// 3D relative document link
167
		// CellAddress=A1, shifted to A4
168
		Cell cell = row.createCell(0);
169
		cell.setCellStyle(hlinkStyle);
170
		createHyperlink(helper, cell, HyperlinkType.DOCUMENT, "test!E1");
171
172
		// URL
173
		cell = row.createCell(1);
174
		// CellAddress=B1, shifted to B4
175
		cell.setCellStyle(hlinkStyle);
176
		createHyperlink(helper, cell, HyperlinkType.URL, "http://poi.apache.org/");
177
178
		// row0 will be shifted on top of row1, so this URL should be removed
179
		// from the workbook
180
		Row overwrittenRow = sheet.createRow(3);
181
		cell = overwrittenRow.createCell(2);
182
		// CellAddress=C4, will be overwritten (deleted)
183
		cell.setCellStyle(hlinkStyle);
184
		createHyperlink(helper, cell, HyperlinkType.EMAIL, "mailto:poi@apache.org");
185
186
		Row unaffectedRow = sheet.createRow(20);
187
		cell = unaffectedRow.createCell(3);
188
		// CellAddress=D21, will be unaffected
189
		cell.setCellStyle(hlinkStyle);
190
		createHyperlink(helper, cell, HyperlinkType.FILE, "54524.xlsx");
191
192
		cell = wb.createSheet("other").createRow(0).createCell(0);
193
		// CellAddress=Other!A1, will be unaffected
194
		cell.setCellStyle(hlinkStyle);
195
		createHyperlink(helper, cell, HyperlinkType.URL, "http://apache.org/");
196
197
		int startRow = 0;
198
		int endRow = 4;
199
		int n = 3;
200
		writeSheetToLog(sheet);
201
		sheet.shiftColumns(startRow, endRow, n);
202
		writeSheetToLog(sheet);
203
204
		Workbook read = _testDataProvider.writeOutAndReadBack(wb);
205
		wb.close();
206
207
		Sheet sh = read.getSheet("test");
208
209
		Row shiftedRow = sh.getRow(0);
210
211
		// document link anchored on a shifted cell should be moved
212
		// Note that hyperlinks do not track what they point to, so this
213
		// hyperlink should still refer to test!E1
214
		verifyHyperlink(shiftedRow.getCell(3), HyperlinkType.DOCUMENT, "test!E1");
215
216
		// URL, EMAIL, and FILE links anchored on a shifted cell should be moved
217
		verifyHyperlink(shiftedRow.getCell(4), HyperlinkType.URL, "http://poi.apache.org/");
218
219
		// Make sure hyperlinks were moved and not copied
220
		assertNull("Document hyperlink should be moved, not copied", sh.getHyperlink(0, 0));
221
		assertNull("URL hyperlink should be moved, not copied", sh.getHyperlink(1, 0));
222
223
		assertEquals(4, sh.getHyperlinkList().size());
224
		read.close();
225
	}
226
227
	private void createHyperlink(CreationHelper helper, Cell cell, HyperlinkType linkType, String ref) {
228
		cell.setCellValue(ref);
229
		Hyperlink link = helper.createHyperlink(linkType);
230
		link.setAddress(ref);
231
		cell.setHyperlink(link);
232
	}
233
234
	private void verifyHyperlink(Cell cell, HyperlinkType linkType, String ref) {
235
		assertTrue(cellHasHyperlink(cell));
236
		Hyperlink link = cell.getHyperlink();
237
		assertEquals(linkType, link.getTypeEnum());
238
		assertEquals(ref, link.getAddress());
239
	}
240
241
	private boolean cellHasHyperlink(Cell cell) {
242
		return (cell != null) && (cell.getHyperlink() != null);
243
	}
244
245
    @Test
246
    public void shiftMergedColumnsToMergedColumnsRight() throws IOException {
247
        Workbook wb = _testDataProvider.createWorkbook();
248
        Sheet sheet = wb.createSheet("test");
249
250
        // populate sheet cells
251
        populateSheetCells(sheet);
252
        writeSheetToLog(sheet);
253
        CellRangeAddress A1_A5 = new CellRangeAddress(0, 4, 0, 0);
254
        CellRangeAddress B1_B3 = new CellRangeAddress(0, 2, 1, 1);
255
256
        sheet.addMergedRegion(B1_B3);
257
        sheet.addMergedRegion(A1_A5);
258
259
        // A1:A5 should be moved to B1:B5
260
        // B1:B3 will be removed
261
        sheet.shiftColumns(0, 0, 1);
262
        writeSheetToLog(sheet);
263
        
264
        assertEquals(1, sheet.getNumMergedRegions());
265
        assertEquals(CellRangeAddress.valueOf("B1:B5"), sheet.getMergedRegion(0));
266
267
        wb.close();
268
    }
269
    @Test
270
    public void shiftMergedColumnsToMergedColumnsLeft() throws IOException {
271
        Workbook wb = _testDataProvider.createWorkbook();
272
        Sheet sheet = wb.createSheet("test");
273
        populateSheetCells(sheet);
274
275
        CellRangeAddress A1_A5 = new CellRangeAddress(0, 4, 0, 0);
276
        CellRangeAddress B1_B3 = new CellRangeAddress(0, 2, 1, 1);
277
278
        sheet.addMergedRegion(A1_A5);
279
        sheet.addMergedRegion(B1_B3);
280
281
        // A1:E1 should be removed
282
        // B1:B3 will be A1:A3
283
        sheet.shiftColumns(1, 5, -1);
284
285
        assertEquals(1, sheet.getNumMergedRegions());
286
        assertEquals(CellRangeAddress.valueOf("A1:A3"), sheet.getMergedRegion(0));
287
288
        wb.close();
289
    }
290
291
    private void populateSheetCells(Sheet sheet) {
292
        // populate sheet cells
293
        for (int i = 0; i < 2; i++) {
294
            Row row = sheet.createRow(i);
295
            for (int j = 0; j < 5; j++) {
296
                Cell cell = row.createCell(j);
297
                cell.setCellValue(i + "x" + j);
298
            }
299
        }
300
    }
301
302
    @Test
303
    public final void testShiftWithMergedRegions() throws IOException {
304
        Workbook wb = _testDataProvider.createWorkbook();
305
        Sheet sheet = wb.createSheet();
306
        Row row = sheet.createRow(0);
307
        row.createCell(0).setCellValue(1.1);
308
        row = sheet.createRow(1);
309
        row.createCell(0).setCellValue(2.2);
310
        CellRangeAddress region = new CellRangeAddress(0, 2, 0, 0);
311
        assertEquals("A1:A3", region.formatAsString());
312
313
        sheet.addMergedRegion(region);
314
315
        sheet.shiftColumns(0, 1, 2);
316
        region = sheet.getMergedRegion(0);
317
        assertEquals("C1:C3", region.formatAsString());
318
        wb.close();
319
    }
320
321
	@Test
322
	public void testCommentsShifting() throws IOException {
323
		Workbook wb = XSSFTestDataSamples.openSampleWorkbook("56017.xlsx");
324
325
		Sheet sheet = wb.getSheetAt(0);
326
		Comment comment = sheet.getCellComment(new CellAddress(0, 0));
327
		assertNotNull(comment);
328
		assertEquals("Amdocs", comment.getAuthor());
329
		assertEquals("Amdocs:\ntest\n", comment.getString().getString());
330
331
		sheet.shiftColumns(0, 1, 1);
332
333
		// comment in column 0 is gone
334
		comment = sheet.getCellComment(new CellAddress(0, 0));
335
		assertNull(comment);
336
337
		// comment is column in column 1
338
		comment = sheet.getCellComment(new CellAddress(0, 1));
339
		assertNotNull(comment);
340
		assertEquals("Amdocs", comment.getAuthor());
341
		assertEquals("Amdocs:\ntest\n", comment.getString().getString());
342
343
		Workbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(wb);
344
		wb.close();
345
		assertNotNull(wbBack);
346
347
		Sheet sheetBack = wbBack.getSheetAt(0);
348
349
		// comment in column 0 is gone
350
		comment = sheetBack.getCellComment(new CellAddress(0, 0));
351
		assertNull(comment);
352
353
		// comment is now in column 1
354
		comment = sheetBack.getCellComment(new CellAddress(0, 1));
355
		assertNotNull(comment);
356
		assertEquals("Amdocs", comment.getAuthor());
357
		assertEquals("Amdocs:\ntest\n", comment.getString().getString());
358
		wbBack.close();
359
	}
360
	
361
	// transposed version of TestXSSFSheetShiftRows.testBug54524()
362
    @Test
363
    public void testBug54524() throws IOException {
364
        Workbook wb = _testDataProvider.createWorkbook();
365
        Sheet sheet = wb.createSheet();
366
        Row firstRow = sheet.createRow(0); 
367
        firstRow.createCell(0).setCellValue("");
368
        firstRow.createCell(1).setCellValue(1);
369
        firstRow.createCell(2).setCellValue(2);
370
        firstRow.createCell(3).setCellFormula("SUM(B1:C1)");
371
        firstRow.createCell(4).setCellValue("X");
372
        
373
        sheet.shiftColumns(3, 5, -1);
374
375
        Cell cell = CellUtil.getCell(sheet.getRow(0), 1);
376
        assertEquals(1.0, cell.getNumericCellValue(), 0);
377
        cell = CellUtil.getCell(sheet.getRow(0), 2);
378
        assertEquals("SUM(B1:B1)", cell.getCellFormula());
379
        cell = CellUtil.getCell(sheet.getRow(0), 3);
380
        assertEquals("X", cell.getStringCellValue());
381
        wb.close();
382
    }
383
384
}

Return to bug 61474