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

(-)a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (-1 / +1 lines)
Lines 950-956 public class HSSFCell implements Cell { Link Here
950
     * @return CellValueRecordInterface representing the cell via the low level api.
950
     * @return CellValueRecordInterface representing the cell via the low level api.
951
     */
951
     */
952
952
953
    protected CellValueRecordInterface getCellValueRecord()
953
    public CellValueRecordInterface getCellValueRecord()
954
    {
954
    {
955
        return _record;
955
        return _record;
956
    }
956
    }
(-)a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (-6 / +18 lines)
Lines 50-55 import org.apache.poi.hssf.record.aggregates.DataValidityTable; Link Here
50
import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
50
import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
51
import org.apache.poi.hssf.record.aggregates.RecordAggregate.RecordVisitor;
51
import org.apache.poi.hssf.record.aggregates.RecordAggregate.RecordVisitor;
52
import org.apache.poi.hssf.record.aggregates.WorksheetProtectionBlock;
52
import org.apache.poi.hssf.record.aggregates.WorksheetProtectionBlock;
53
import org.apache.poi.hssf.usermodel.helpers.HSSFColumnShifter;
53
import org.apache.poi.hssf.usermodel.helpers.HSSFRowShifter;
54
import org.apache.poi.hssf.usermodel.helpers.HSSFRowShifter;
54
import org.apache.poi.ss.SpreadsheetVersion;
55
import org.apache.poi.ss.SpreadsheetVersion;
55
import org.apache.poi.ss.formula.FormulaShifter;
56
import org.apache.poi.ss.formula.FormulaShifter;
Lines 76-81 import org.apache.poi.ss.util.SheetUtil; Link Here
76
import org.apache.poi.util.Configurator;
77
import org.apache.poi.util.Configurator;
77
import org.apache.poi.util.POILogFactory;
78
import org.apache.poi.util.POILogFactory;
78
import org.apache.poi.util.POILogger;
79
import org.apache.poi.util.POILogger;
80
import org.apache.poi.xssf.usermodel.helpers.XSSFColumnShifter;
79
81
80
/**
82
/**
81
 * High level representation of a worksheet.
83
 * High level representation of a worksheet.
Lines 1697-1711 public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { Link Here
1697
            }
1699
            }
1698
        }
1700
        }
1699
1701
1702
        shiftFormulas(startRow, endRow, n, true);
1703
    }
1704
    private void shiftFormulas(int startRow, int endRow, int n, boolean forRowElseColumnShift){
1700
        // Update any formulas on this sheet that point to
1705
        // Update any formulas on this sheet that point to
1701
        //  rows which have been moved
1706
        //  rows/columns which have been moved
1702
        int sheetIndex = _workbook.getSheetIndex(this);
1707
        int sheetIndex = _workbook.getSheetIndex(this);
1703
        String sheetName = _workbook.getSheetName(sheetIndex);
1704
        short externSheetIndex = _book.checkExternSheet(sheetIndex);
1708
        short externSheetIndex = _book.checkExternSheet(sheetIndex);
1705
        FormulaShifter shifter = FormulaShifter.createForRowShift(
1709
        FormulaShifter shifter = FormulaShifter.createForItemShift(this, forRowElseColumnShift, startRow, endRow, n);
1706
                         externSheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL97);
1710
                         //externSheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL97);
1707
        _sheet.updateFormulasAfterCellShift(shifter, externSheetIndex);
1711
        _sheet.updateFormulasAfterCellShift(shifter, externSheetIndex);
1708
1712
    	
1709
        int nSheets = _workbook.getNumberOfSheets();
1713
        int nSheets = _workbook.getNumberOfSheets();
1710
        for (int i = 0; i < nSheets; i++) {
1714
        for (int i = 0; i < nSheets; i++) {
1711
            InternalSheet otherSheet = _workbook.getSheetAt(i).getSheet();
1715
            InternalSheet otherSheet = _workbook.getSheetAt(i).getSheet();
Lines 1717-1723 public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { Link Here
1717
        }
1721
        }
1718
        _workbook.getWorkbook().updateNamesAfterCellShift(shifter);
1722
        _workbook.getWorkbook().updateNamesAfterCellShift(shifter);
1719
    }
1723
    }
1720
1724
    
1721
    protected void insertChartRecords(List<Record> records) {
1725
    protected void insertChartRecords(List<Record> records) {
1722
        int window2Loc = _sheet.findFirstRecordLocBySid(WindowTwoRecord.sid);
1726
        int window2Loc = _sheet.findFirstRecordLocBySid(WindowTwoRecord.sid);
1723
        _sheet.getRecords().addAll(window2Loc, records);
1727
        _sheet.getRecords().addAll(window2Loc, records);
Lines 2640-2643 public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { Link Here
2640
        _sheet.setActiveCellRow(row);
2644
        _sheet.setActiveCellRow(row);
2641
        _sheet.setActiveCellCol(col);
2645
        _sheet.setActiveCellCol(col);
2642
    }
2646
    }
2647
2648
    public void shiftColumns(int startColumn, int endColumn, int n){ 
2649
        FormulaShifter shifter = FormulaShifter.createForItemShift(this, false, startColumn, endColumn, n);
2650
        HSSFColumnShifter columnShifter = new HSSFColumnShifter(this, shifter);
2651
        columnShifter.shiftColumns(startColumn, endColumn, n);
2652
        shiftFormulas(startColumn, endColumn, n, false);
2653
        // add logic for hyperlinks etc, like in shiftRows()
2654
    } 
2643
}
2655
}
(-)a/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFColumnShifter.java (+102 lines)
Line 0 Link Here
1
package org.apache.poi.hssf.usermodel.helpers;
2
3
import org.apache.poi.hssf.usermodel.HSSFCell;
4
import org.apache.poi.hssf.usermodel.HSSFRow;
5
import org.apache.poi.ss.formula.FormulaShifter;
6
import org.apache.poi.ss.usermodel.Cell;
7
import org.apache.poi.ss.usermodel.CellType;
8
import org.apache.poi.ss.usermodel.Row;
9
import org.apache.poi.ss.usermodel.Sheet;
10
import org.apache.poi.ss.usermodel.helpers.ColumnShifter;
11
import org.apache.poi.util.POILogFactory;
12
import org.apache.poi.util.POILogger;
13
import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;
14
15
public class HSSFColumnShifter extends ColumnShifter{
16
    private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);
17
    
18
    private int firstShiftColumnIndex; 
19
    private int lastShiftColumnIndex; 
20
    private int shiftStep;
21
    
22
    public HSSFColumnShifter(Sheet sh, FormulaShifter shifter) {
23
        super(sh, shifter);
24
    }
25
26
    public void shiftColumns(int firstShiftColumnIndex, int lastShiftColumnIndex, int step){
27
        this.firstShiftColumnIndex = firstShiftColumnIndex;
28
        this.lastShiftColumnIndex = lastShiftColumnIndex;
29
        this.shiftStep = step;
30
        if(shiftStep > 0)
31
            shiftColumnsRight();
32
        else if(shiftStep < 0)
33
            shiftColumnsLeft();
34
//        formulaShiftingManager.updateFormulas();
35
    }
36
    /**
37
     * Inserts shiftStep empty columns at firstShiftColumnIndex-th position, and shifts rest columns to the right 
38
     * (see constructor for parameters)
39
     */
40
41
    private void shiftColumnsRight(){
42
        for(int rowNo = 0; rowNo <= shiftingSheet.getLastRowNum(); rowNo++)
43
        {   
44
            Row row = shiftingSheet.getRow(rowNo);
45
            if(row == null)
46
                continue;
47
            for (int columnIndex = lastShiftColumnIndex; columnIndex >= firstShiftColumnIndex; columnIndex--){ // process cells backwards, because of shifting 
48
                HSSFCell oldCell = (HSSFCell)row.getCell(columnIndex);
49
                Cell newCell = null;
50
                if(oldCell == null){
51
                    newCell = row.getCell(columnIndex + shiftStep);
52
                    newCell = null;
53
                    continue;
54
                }
55
                else {
56
                    newCell = row.createCell(columnIndex + shiftStep, oldCell.getCellType());
57
                    cloneCellValue(oldCell,newCell);
58
                    if(columnIndex <= firstShiftColumnIndex + shiftStep - 1){ // clear existing cells on place of insertion
59
                        oldCell.setCellValue("");
60
                        oldCell.setCellType(CellType.STRING);
61
                    }
62
                }
63
            }
64
        }
65
    }
66
    private void shiftColumnsLeft(){
67
        for(int rowNo = 0; rowNo <= shiftingSheet.getLastRowNum(); rowNo++)
68
        {   
69
            HSSFRow row = (HSSFRow)shiftingSheet.getRow(rowNo);
70
            if(row == null)
71
                continue;
72
            for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++){ 
73
                HSSFCell oldCell = row.getCell(columnIndex);
74
                if(columnIndex >= firstShiftColumnIndex + shiftStep && columnIndex < row.getLastCellNum() - shiftStep){ // shift existing cell 
75
                    org.apache.poi.ss.usermodel.Cell newCell = null;
76
                    newCell = row.getCell(columnIndex - shiftStep);
77
                    if(oldCell != null){
78
                        if(newCell != null){
79
                            oldCell.setCellType(newCell.getCellType());
80
                            cloneCellValue(newCell, oldCell);
81
                        }
82
                        else {
83
                            oldCell.setCellType(CellType.STRING);
84
                            oldCell.setCellValue("");
85
                        }
86
                    }
87
                    else {
88
                        oldCell = row.createCell(columnIndex);
89
                        if(newCell != null){
90
                            oldCell.setCellType(newCell.getCellType());
91
                            cloneCellValue(newCell, oldCell);
92
                        }
93
                        else {
94
                            oldCell.setCellType(CellType.STRING);
95
                            oldCell.setCellValue("");
96
                        }
97
                    }
98
                }
99
            }
100
        }
101
    }
102
}
(-)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 (+97 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
    public static void cloneCellValue(org.apache.poi.ss.usermodel.Cell oldCell, org.apache.poi.ss.usermodel.Cell newCell) {
74
        newCell.setCellComment(oldCell.getCellComment());
75
        switch (oldCell.getCellType()) {
76
            case STRING:
77
                newCell.setCellValue(oldCell.getStringCellValue());
78
                break;
79
            case NUMERIC:
80
                newCell.setCellValue(oldCell.getNumericCellValue());
81
                break;
82
            case BOOLEAN:
83
                newCell.setCellValue(oldCell.getBooleanCellValue());
84
                break;
85
            case FORMULA:
86
                newCell.setCellFormula(oldCell.getCellFormula());
87
                break;
88
            case ERROR:
89
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
90
            case BLANK:
91
            case _NONE:
92
                break;
93
        }
94
    }
95
96
    
97
}
(-)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 / +26 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 206-211 public class XSSFRow implements Row, Comparable<XSSFRow> { Link Here
206
     * @return XSSFCell a high level representation of the created cell.
207
     * @return XSSFCell a high level representation of the created cell.
207
     * @throws IllegalArgumentException if the specified cell type is invalid, columnIndex < 0
208
     * @throws IllegalArgumentException if the specified cell type is invalid, columnIndex < 0
208
     *   or greater than 16384, the maximum number of columns supported by the SpreadsheetML format (.xlsx)
209
     *   or greater than 16384, the maximum number of columns supported by the SpreadsheetML format (.xlsx)
210
     * @see CellType#BLANK
211
     * @see CellType#BOOLEAN
212
     * @see CellType#ERROR
213
     * @see CellType#FORMULA
214
     * @see CellType#NUMERIC
215
     * @see CellType#STRING
216
     * @deprecated POI 3.15 beta 3. Use {@link #createCell(int, CellType)} instead.
217
     */
218
    //@Override
219
    public XSSFCell createCell(int columnIndex, int type) {
220
        return createCell(columnIndex, CellType.forInt(type));
221
    }
222
    /**
223
     * Use this to create new cells within the row and return it.
224
     *
225
     * @param columnIndex - the column number this cell represents
226
     * @param type - the cell's data type
227
     * @return XSSFCell a high level representation of the created cell.
228
     * @throws IllegalArgumentException if the specified cell type is invalid, columnIndex < 0
229
     *   or greater than 16384, the maximum number of columns supported by the SpreadsheetML format (.xlsx)
209
     */
230
     */
210
    @Override
231
    @Override
211
    public XSSFCell createCell(int columnIndex, CellType type) {
232
    public XSSFCell createCell(int columnIndex, CellType type) {
Lines 552-576 public class XSSFRow implements Row, Comparable<XSSFRow> { Link Here
552
     *
573
     *
553
     * @param n the number of rows to move
574
     * @param n the number of rows to move
554
     */
575
     */
555
    protected void shift(int n) {
576
    public void shift(int n) {
556
        int rownum = getRowNum() + n;
577
        int rownum = getRowNum() + n;
557
        CalculationChain calcChain = _sheet.getWorkbook().getCalculationChain();
558
        int sheetId = (int)_sheet.sheet.getSheetId();
559
        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. " +
560
                "You cannot change part of an array.";
579
                "You cannot change part of an array.";
561
        for(Cell c : this){
580
        for(Cell c : this){
562
            XSSFCell cell = (XSSFCell)c;
581
            ((XSSFCell)c).updateCellReferencesForShifting(msg);
563
            if(cell.isPartOfArrayFormulaGroup()){
582
          }
564
                cell.notifyArrayFormulaChanging(msg);
565
            }
566
567
            //remove the reference in the calculation chain
568
            if(calcChain != null) calcChain.removeItem(sheetId, cell.getReference());
569
570
            CTCell ctCell = cell.getCTCell();
571
            String r = new CellReference(rownum, cell.getColumnIndex()).formatAsString();
572
            ctCell.setR(r);
573
        }
574
        setRowNum(rownum);
583
        setRowNum(rownum);
575
    }
584
    }
576
    
585
    
Lines 620-633 public class XSSFRow implements Row, Comparable<XSSFRow> { Link Here
620
                destCell.copyCellFrom(srcCell, policy);
629
                destCell.copyCellFrom(srcCell, policy);
621
            }
630
            }
622
631
623
            final XSSFRowShifter rowShifter = new XSSFRowShifter(_sheet);
624
            final int sheetIndex = _sheet.getWorkbook().getSheetIndex(_sheet);
632
            final int sheetIndex = _sheet.getWorkbook().getSheetIndex(_sheet);
625
            final String sheetName = _sheet.getWorkbook().getSheetName(sheetIndex);
633
            final String sheetName = _sheet.getWorkbook().getSheetName(sheetIndex);
626
            final int srcRowNum = srcRow.getRowNum();
634
            final int srcRowNum = srcRow.getRowNum();
627
            final int destRowNum = getRowNum();
635
            final int destRowNum = getRowNum();
628
            final int rowDifference = destRowNum - srcRowNum;
636
            final int rowDifference = destRowNum - srcRowNum;
629
            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);
630
            rowShifter.updateRowFormulas(this, shifter);
638
            final XSSFShiftingManager formulaShiftingManager = new XSSFShiftingManager(_sheet, shifter); 
639
            formulaShiftingManager.updateRowFormulas(this);
631
640
632
            // Copy merged regions that are fully contained on the row
641
            // Copy merged regions that are fully contained on the row
633
            // 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 (-124 / +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
        int sheetIndex = getWorkbook().getSheetIndex(this);
2991
        String sheetName = getWorkbook().getSheetName(sheetIndex);
2992
        FormulaShifter shifter = FormulaShifter.createForRowShift(
2993
                                   sheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL2007);
2994
        removeOverwritten(vml, startRow, endRow, n);
2995
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 3028-3155 public class XSSFSheet extends POIXMLDocumentPart implements Sheet { Link Here
3028
                }
3092
                }
3029
            }
3093
            }
3030
        }
3094
        }
3031
3032
        // then do the actual moving and also adjust comments/rowHeight
3033
        // we need to sort it in a way so the shifting does not mess up the structures, 
3034
        // i.e. when shifting down, start from down and go up, when shifting up, vice-versa
3035
        SortedMap<XSSFComment, Integer> commentsToShift = new TreeMap<>(new Comparator<XSSFComment>() {
3036
            @Override
3037
            public int compare(XSSFComment o1, XSSFComment o2) {
3038
                int row1 = o1.getRow();
3039
                int row2 = o2.getRow();
3040
3041
                if (row1 == row2) {
3042
                    // ordering is not important when row is equal, but don't return zero to still 
3043
                    // get multiple comments per row into the map
3044
                    return o1.hashCode() - o2.hashCode();
3045
                }
3046
3047
                // when shifting down, sort higher row-values first
3048
                if (n > 0) {
3049
                    return row1 < row2 ? 1 : -1;
3050
                } else {
3051
                    // sort lower-row values first when shifting up
3052
                    return row1 > row2 ? 1 : -1;
3053
                }
3054
            }
3055
        });
3056
3057
        
3058
        for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) {
3059
            XSSFRow row = (XSSFRow)it.next();
3060
            int rownum = row.getRowNum();
3061
3062
            if(sheetComments != null){
3063
                // calculate the new rownum
3064
                int newrownum = shiftedRowNum(startRow, endRow, n, rownum);
3065
                
3066
                // is there a change necessary for the current row?
3067
                if(newrownum != rownum) {
3068
                    CTCommentList lst = sheetComments.getCTComments().getCommentList();
3069
                    for (CTComment comment : lst.getCommentArray()) {
3070
                        String oldRef = comment.getRef();
3071
                        CellReference ref = new CellReference(oldRef);
3072
                        
3073
                        // is this comment part of the current row?
3074
                        if(ref.getRow() == rownum) {
3075
                            XSSFComment xssfComment = new XSSFComment(sheetComments, comment,
3076
                                    vml == null ? null : vml.findCommentShape(rownum, ref.getCol()));
3077
                            
3078
                            // we should not perform the shifting right here as we would then find
3079
                            // already shifted comments and would shift them again...
3080
                            commentsToShift.put(xssfComment, newrownum);
3081
                        }
3082
                    }
3083
                }
3084
            }
3085
3086
            if(rownum < startRow || rownum > endRow) {
3087
                continue;
3088
            }
3089
3090
            if (!copyRowHeight) {
3091
                row.setHeight((short)-1);
3092
            }
3093
3094
            row.shift(n);
3095
        }
3096
        
3097
        // adjust all the affected comment-structures now
3098
        // the Map is sorted and thus provides them in the order that we need here, 
3099
        // i.e. from down to up if shifting down, vice-versa otherwise
3100
        for(Map.Entry<XSSFComment, Integer> entry : commentsToShift.entrySet()) {
3101
            entry.getKey().setRow(entry.getValue());
3102
        }
3103
        
3104
        XSSFRowShifter rowShifter = new XSSFRowShifter(this);
3105
3106
        int sheetIndex = getWorkbook().getSheetIndex(this);
3107
        String sheetName = getWorkbook().getSheetName(sheetIndex);
3108
        FormulaShifter shifter = FormulaShifter.createForRowShift(
3109
                                   sheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL2007);
3110
3111
        rowShifter.updateNamedRanges(shifter);
3112
        rowShifter.updateFormulas(shifter);
3113
        rowShifter.shiftMergedRegions(startRow, endRow, n);
3114
        rowShifter.updateConditionalFormatting(shifter);
3115
        rowShifter.updateHyperlinks(shifter);
3116
3117
        //rebuild the _rows map
3118
        Map<Integer, XSSFRow> map = new HashMap<>();
3119
        for(XSSFRow r : _rows.values()) {
3120
            // Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory
3121
            final Integer rownumI = Integer.valueOf(r.getRowNum()); // NOSONAR
3122
            map.put(rownumI, r);
3123
        }
3124
        _rows.clear();
3125
        _rows.putAll(map);
3126
    }
3127
3128
    private int shiftedRowNum(int startRow, int endRow, int n, int rownum) {
3129
        // no change if before any affected row
3130
        if(rownum < startRow && (n > 0 || (startRow - rownum) > n)) {
3131
            return rownum;
3132
        }
3133
        
3134
        // no change if after any affected row
3135
        if(rownum > endRow && (n < 0 || (rownum - endRow) > n)) {
3136
            return rownum;
3137
        }
3138
        
3139
        // row before and things are moved up
3140
        if(rownum < startRow) {
3141
            // row is moved down by the shifting
3142
            return rownum + (endRow - startRow);
3143
        }
3144
        
3145
        // row is after and things are moved down
3146
        if(rownum > endRow) {
3147
            // row is moved up by the shifting
3148
            return rownum - (endRow - startRow);
3149
        }
3150
        
3151
        // row is part of the shifted block
3152
        return rownum + n;
3153
    }
3095
    }
3154
3096
3155
    /**
3097
    /**
Lines 4515-4518 public class XSSFSheet extends POIXMLDocumentPart implements Sheet { Link Here
4515
    }
4457
    }
4516
4458
4517
4459
4518
}
4460
}
(-)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 (+170 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
}
(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java (-225 / +116 lines)
Lines 17-56 Link Here
17
17
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.Comparator;
21
import java.util.Iterator;
21
import java.util.List;
22
import java.util.List;
23
import java.util.Map;
24
import java.util.SortedMap;
25
import java.util.TreeMap;
22
26
23
import org.apache.poi.ss.formula.FormulaParseException;
24
import org.apache.poi.ss.formula.FormulaParser;
25
import org.apache.poi.ss.formula.FormulaRenderer;
26
import org.apache.poi.ss.formula.FormulaShifter;
27
import org.apache.poi.ss.formula.FormulaShifter;
27
import org.apache.poi.ss.formula.FormulaType;
28
import org.apache.poi.ss.formula.ptg.AreaErrPtg;
29
import org.apache.poi.ss.formula.ptg.AreaPtg;
30
import org.apache.poi.ss.formula.ptg.Ptg;
31
import org.apache.poi.ss.usermodel.Cell;
32
import org.apache.poi.ss.usermodel.Hyperlink;
33
import org.apache.poi.ss.usermodel.Name;
34
import org.apache.poi.ss.usermodel.Row;
28
import org.apache.poi.ss.usermodel.Row;
35
import org.apache.poi.ss.usermodel.Sheet;
29
import org.apache.poi.ss.usermodel.Sheet;
36
import org.apache.poi.ss.usermodel.Workbook;
37
import org.apache.poi.ss.usermodel.helpers.RowShifter;
30
import org.apache.poi.ss.usermodel.helpers.RowShifter;
38
import org.apache.poi.ss.util.CellRangeAddress;
31
import org.apache.poi.ss.util.CellRangeAddress;
39
import org.apache.poi.util.Internal;
32
import org.apache.poi.ss.util.CellReference;
40
import org.apache.poi.util.POILogFactory;
33
import org.apache.poi.util.POILogFactory;
41
import org.apache.poi.util.POILogger;
34
import org.apache.poi.util.POILogger;
42
import org.apache.poi.xssf.usermodel.XSSFCell;
35
import org.apache.poi.xssf.model.CommentsTable;
43
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
36
import org.apache.poi.xssf.usermodel.XSSFComment;
44
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
45
import org.apache.poi.xssf.usermodel.XSSFRow;
37
import org.apache.poi.xssf.usermodel.XSSFRow;
46
import org.apache.poi.xssf.usermodel.XSSFSheet;
38
import org.apache.poi.xssf.usermodel.XSSFSheet;
47
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
39
import org.apache.poi.xssf.usermodel.XSSFVMLDrawing;
48
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
40
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment;
49
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
41
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCommentList;
50
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule;
51
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTConditionalFormatting;
52
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
53
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
54
42
55
/**
43
/**
56
 * Helper for shifting rows up or down
44
 * Helper for shifting rows up or down
Lines 60-287 import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType; Link Here
60
public final class XSSFRowShifter extends RowShifter {
48
public final class XSSFRowShifter extends RowShifter {
61
    private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);
49
    private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);
62
50
51
    private XSSFShiftingManager formulaShiftingManager;
52
    
63
    public XSSFRowShifter(XSSFSheet sh) {
53
    public XSSFRowShifter(XSSFSheet sh) {
64
        super(sh);
54
        super(sh);
65
    }
55
    }
66
56
    public XSSFRowShifter(Sheet sh, FormulaShifter shifter) {
67
    /**
57
        super(sh, shifter);
68
     * Updated named ranges
58
        formulaShiftingManager = new XSSFShiftingManager(sh, shifter);
69
     */
70
    public void updateNamedRanges(FormulaShifter shifter) {
71
        Workbook wb = sheet.getWorkbook();
72
        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);
73
        for (Name name : wb.getAllNames()) {
74
            String formula = name.getRefersToFormula();
75
            int sheetIndex = name.getSheetIndex();
76
            final int rowIndex = -1; //don't care, named ranges are not allowed to include structured references
77
78
            Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.NAMEDRANGE, sheetIndex, rowIndex);
79
            if (shifter.adjustFormula(ptgs, sheetIndex)) {
80
                String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
81
                name.setRefersToFormula(shiftedFmla);
82
            }
83
        }
84
    }
85
86
    /**
87
     * Update formulas.
88
     */
89
    public void updateFormulas(FormulaShifter shifter) {
90
        //update formulas on the parent sheet
91
        updateSheetFormulas(sheet, shifter);
92
93
        //update formulas on other sheets
94
        Workbook wb = sheet.getWorkbook();
95
        for (Sheet sh : wb) {
96
            if (sheet == sh) continue;
97
            updateSheetFormulas(sh, shifter);
98
        }
99
    }
100
101
    private void updateSheetFormulas(Sheet sh, FormulaShifter shifter) {
102
        for (Row r : sh) {
103
            XSSFRow row = (XSSFRow) r;
104
            updateRowFormulas(row, shifter);
105
        }
106
    }
59
    }
60
    
61
    // do the actual moving and also adjust comments/rowHeight
62
    // we need to sort it in a way so the shifting does not mess up the structures, 
63
    // i.e. when shifting down, start from down and go up, when shifting up, vice-versa
64
    public void doShiftingAndProcessComments(XSSFVMLDrawing vml, int startRow, int endRow, final int n, 
65
    		boolean copyRowHeight, Iterator<Row> rowIterator, CommentsTable sheetComments){
66
        SortedMap<XSSFComment, Integer> commentsToShift = new TreeMap<XSSFComment, Integer>(new Comparator<XSSFComment>() {
67
            @Override
68
            public int compare(XSSFComment o1, XSSFComment o2) {
69
                int row1 = o1.getRow();
70
                int row2 = o2.getRow();
71
                
72
                if(row1 == row2) {
73
                    // ordering is not important when row is equal, but don't return zero to still 
74
                    // get multiple comments per row into the map
75
                    return o1.hashCode() - o2.hashCode();
76
                }
107
77
108
    /**
78
                // when shifting down, sort higher row-values first
109
     * Update the formulas in specified row using the formula shifting policy specified by shifter
79
                if(n > 0) {
110
     *
80
                    return row1 < row2 ? 1 : -1;
111
     * @param row the row to update the formulas on
81
                } else {
112
     * @param shifter the formula shifting policy
82
                    // sort lower-row values first when shifting up
113
     */
83
                    return row1 > row2 ? 1 : -1;
114
    @Internal
84
                }
115
    public void updateRowFormulas(Row row, FormulaShifter shifter) {
85
            }
116
        XSSFSheet sheet = (XSSFSheet) row.getSheet();
86
        });
117
        for (Cell c : row) {
87
        
118
            XSSFCell cell = (XSSFCell) c;
88
        for (Iterator<Row> it = rowIterator; it.hasNext() ; ) {
119
89
            XSSFRow row = (XSSFRow)it.next();
120
            CTCell ctCell = cell.getCTCell();
90
            int rownum = row.getRowNum();
121
            if (ctCell.isSetF()) {
91
122
                CTCellFormula f = ctCell.getF();
92
            if(sheetComments != null){
123
                String formula = f.getStringValue();
93
                // calculate the new rownum
124
                if (formula.length() > 0) {
94
                int newrownum = XSSFShiftingManager.shiftedItemIndex(startRow, endRow, n, rownum);
125
                    String shiftedFormula = shiftFormula(row, formula, shifter);
95
                
126
                    if (shiftedFormula != null) {
96
                // is there a change necessary for the current row?
127
                        f.setStringValue(shiftedFormula);
97
                if(newrownum != rownum) {
128
                        if(f.getT() == STCellFormulaType.SHARED){
98
                    CTCommentList lst = sheetComments.getCTComments().getCommentList();
129
                            int si = (int)f.getSi();
99
                    for (CTComment comment : lst.getCommentArray()) {
130
                            CTCellFormula sf = sheet.getSharedFormula(si);
100
                        String oldRef = comment.getRef();
131
                            sf.setStringValue(shiftedFormula);
101
                        CellReference ref = new CellReference(oldRef);
132
                            updateRefInCTCellFormula(row, shifter, sf);
102
                        
103
                        // is this comment part of the current row?
104
                        if(ref.getRow() == rownum) {
105
                            XSSFComment xssfComment = new XSSFComment(sheetComments, comment,
106
                                    vml == null ? null : vml.findCommentShape(rownum, ref.getCol()));
107
                            
108
                            // we should not perform the shifting right here as we would then find
109
                            // already shifted comments and would shift them again...
110
                            commentsToShift.put(xssfComment, newrownum);
133
                        }
111
                        }
134
                    }
112
                    }
135
136
                }
113
                }
137
138
                //Range of cells which the formula applies to.
139
                updateRefInCTCellFormula(row, shifter, f);
140
            }
114
            }
141
115
116
            if(rownum < startRow || rownum > endRow) {
117
                continue;
118
            }
119
            if (!copyRowHeight) {
120
                row.setHeight((short)-1);
121
            }
122
            row.shift(n);
142
        }
123
        }
143
    }
124
        
144
125
        // adjust all the affected comment-structures now
145
    private void updateRefInCTCellFormula(Row row, FormulaShifter shifter, CTCellFormula f) {
126
        // the Map is sorted and thus provides them in the order that we need here, 
146
        if (f.isSetRef()) { //Range of cells which the formula applies to.
127
        // i.e. from down to up if shifting down, vice-versa otherwise
147
            String ref = f.getRef();
128
        for(Map.Entry<XSSFComment, Integer> entry : commentsToShift.entrySet()) {
148
            String shiftedRef = shiftFormula(row, ref, shifter);
129
            entry.getKey().setRow(entry.getValue());
149
            if (shiftedRef != null) f.setRef(shiftedRef);
130
            int x = 5;
150
        }
131
        }
132
    	
151
    }
133
    }
134
    
152
135
136
    
153
    /**
137
    /**
154
     * Shift a formula using the supplied FormulaShifter
138
     * Shift merged regions
155
     *
139
     * 
156
     * @param row     the row of the cell this formula belongs to. Used to get a reference to the parent workbook.
140
     * @param startRow the row to start shifting
157
     * @param formula the formula to shift
141
     * @param endRow   the row to end shifting
158
     * @param shifter the FormulaShifter object that operates on the parsed formula tokens
142
     * @param n        the number of rows to shift
159
     * @return the shifted formula if the formula was changed,
143
     * @return an array of merged cell regions
160
     *         <code>null</code> if the formula wasn't modified
144
     * @deprecated POI 3.15 beta 2. Use {@link #shiftMergedRegions(int, int, int)} instead.
161
     */
145
     */
162
    private static String shiftFormula(Row row, String formula, FormulaShifter shifter) {
146
    public List<CellRangeAddress> shiftMerged(int startRow, int endRow, int n) {
163
        Sheet sheet = row.getSheet();
147
        return shiftMergedRegions(startRow, endRow, n);
164
        Workbook wb = sheet.getWorkbook();
165
        int sheetIndex = wb.getSheetIndex(sheet);
166
        final int rowIndex = row.getRowNum();
167
        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);
168
        
169
        try {
170
            Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
171
            String shiftedFmla = null;
172
            if (shifter.adjustFormula(ptgs, sheetIndex)) {
173
                shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
174
            }
175
            return shiftedFmla;
176
        } catch (FormulaParseException fpe) {
177
            // Log, but don't change, rather than breaking
178
            logger.log(POILogger.WARN, "Error shifting formula on row ", row.getRowNum(), fpe);
179
            return formula;
180
        }
181
    }
182
183
    public void updateConditionalFormatting(FormulaShifter shifter) {
184
        XSSFSheet xsheet = (XSSFSheet) sheet;
185
        XSSFWorkbook wb = xsheet.getWorkbook();
186
        int sheetIndex = wb.getSheetIndex(sheet);
187
        final int rowIndex = -1; //don't care, structured references not allowed in conditional formatting
188
189
        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
190
        CTWorksheet ctWorksheet = xsheet.getCTWorksheet();
191
        CTConditionalFormatting[] conditionalFormattingArray = ctWorksheet.getConditionalFormattingArray();
192
        // iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j)
193
        for (int j = conditionalFormattingArray.length - 1; j >= 0; j--) {
194
            CTConditionalFormatting cf = conditionalFormattingArray[j];
195
196
            ArrayList<CellRangeAddress> cellRanges = new ArrayList<>();
197
            for (Object stRef : cf.getSqref()) {
198
                String[] regions = stRef.toString().split(" ");
199
                for (String region : regions) {
200
                    cellRanges.add(CellRangeAddress.valueOf(region));
201
                }
202
            }
203
204
            boolean changed = false;
205
            List<CellRangeAddress> temp = new ArrayList<>();
206
            for (CellRangeAddress craOld : cellRanges) {
207
                CellRangeAddress craNew = shiftRange(shifter, craOld, sheetIndex);
208
                if (craNew == null) {
209
                    changed = true;
210
                    continue;
211
                }
212
                temp.add(craNew);
213
                if (craNew != craOld) {
214
                    changed = true;
215
                }
216
            }
217
218
            if (changed) {
219
                int nRanges = temp.size();
220
                if (nRanges == 0) {
221
                    ctWorksheet.removeConditionalFormatting(j);
222
                    continue;
223
                }
224
                List<String> refs = new ArrayList<>();
225
                for(CellRangeAddress a : temp) refs.add(a.formatAsString());
226
                cf.setSqref(refs);
227
            }
228
229
            for(CTCfRule cfRule : cf.getCfRuleArray()){
230
                String[] formulaArray = cfRule.getFormulaArray();
231
                for (int i = 0; i < formulaArray.length; i++) {
232
                    String formula = formulaArray[i];
233
                    Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
234
                    if (shifter.adjustFormula(ptgs, sheetIndex)) {
235
                        String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
236
                        cfRule.setFormulaArray(i, shiftedFmla);
237
                    }
238
                }
239
            }
240
        }
241
    }
148
    }
242
    
149
    
243
    /**
150
    /**
244
     * Shift the Hyperlink anchors (not the hyperlink text, even if the hyperlink
151
        @deprecated, use FormulaShiftingManager.updateNamedRanges() directly instead
245
     * is of type LINK_DOCUMENT and refers to a cell that was shifted). Hyperlinks
152
    */
246
     * do not track the content they point to.
153
    public void updateNamedRanges(FormulaShifter shifter) {
247
     *
154
        formulaShiftingManager.updateNamedRanges();
248
     * @param shifter
155
    }
249
     */
156
    /**
157
        @deprecated, use FormulaShiftingManager.updateFormulas() directly instead
158
    */ 
159
    public void updateFormulas(FormulaShifter shifter) {
160
        formulaShiftingManager.updateFormulas();
161
    }
162
    /**
163
        @deprecated, use FormulaShiftingManager.updateConditionalFormatting() directly instead
164
     */ 
165
    public void updateConditionalFormatting(FormulaShifter shifter) {
166
        formulaShiftingManager.updateConditionalFormatting();
167
    }    
168
    /**
169
        @deprecated, use FormulaShiftingManager.updateHyperlinks() directly instead
170
     */ 
250
    public void updateHyperlinks(FormulaShifter shifter) {
171
    public void updateHyperlinks(FormulaShifter shifter) {
251
        int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet);
172
        formulaShiftingManager.updateHyperlinks();
252
        List<? extends Hyperlink> hyperlinkList = sheet.getHyperlinkList();
253
        
254
        for (Hyperlink hyperlink : hyperlinkList) {
255
            XSSFHyperlink xhyperlink = (XSSFHyperlink) hyperlink;
256
            String cellRef = xhyperlink.getCellRef();
257
            CellRangeAddress cra = CellRangeAddress.valueOf(cellRef);
258
            CellRangeAddress shiftedRange = shiftRange(shifter, cra, sheetIndex);
259
            if (shiftedRange != null && shiftedRange != cra) {
260
                // shiftedRange should not be null. If shiftedRange is null, that means
261
                // that a hyperlink wasn't deleted at the beginning of shiftRows when
262
                // identifying rows that should be removed because they will be overwritten
263
                xhyperlink.setCellReference(shiftedRange.formatAsString());
264
            }
265
        }
266
    }
173
    }
267
174
    public void updateRowFormulas(Row row, FormulaShifter shifter) {
268
    private static CellRangeAddress shiftRange(FormulaShifter shifter, CellRangeAddress cra, int currentExternSheetIx) {
175
        // TODO Auto-generated method stub
269
        // FormulaShifter works well in terms of Ptgs - so convert CellRangeAddress to AreaPtg (and back) here
176
        
270
        AreaPtg aptg = new AreaPtg(cra.getFirstRow(), cra.getLastRow(), cra.getFirstColumn(), cra.getLastColumn(), false, false, false, false);
271
        Ptg[] ptgs = { aptg, };
272
273
        if (!shifter.adjustFormula(ptgs, currentExternSheetIx)) {
274
            return cra;
275
        }
276
        Ptg ptg0 = ptgs[0];
277
        if (ptg0 instanceof AreaPtg) {
278
            AreaPtg bptg = (AreaPtg) ptg0;
279
            return new CellRangeAddress(bptg.getFirstRow(), bptg.getLastRow(), bptg.getFirstColumn(), bptg.getLastColumn());
280
        }
281
        if (ptg0 instanceof AreaErrPtg) {
282
            return null;
283
        }
284
        throw new IllegalStateException("Unexpected shifted ptg class (" + ptg0.getClass().getName() + ")");
285
    }
177
    }
286
287
}
178
}
(-)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 / +373 lines)
Line 0 Link Here
0
- 
1
package org.apache.poi.xssf.usermodel.helpers;
2
3
import static org.junit.Assert.assertEquals;
4
import static org.junit.Assert.assertNotNull;
5
import static org.junit.Assert.assertNull;
6
import static org.junit.Assert.assertTrue;
7
import java.io.IOException;
8
import org.apache.poi.common.usermodel.HyperlinkType;
9
import org.apache.poi.ss.ITestDataProvider;
10
import org.apache.poi.ss.usermodel.*;
11
import org.apache.poi.ss.util.CellAddress;
12
import org.apache.poi.ss.util.CellRangeAddress;
13
import org.apache.poi.ss.util.CellUtil;
14
import org.apache.poi.xssf.XSSFITestDataProvider;
15
import org.apache.poi.xssf.XSSFTestDataSamples;
16
import org.apache.poi.xssf.usermodel.*;
17
import org.junit.Before;
18
import org.junit.Test;
19
20
public class XSSFColumnShifterTest {
21
22
	//private static Logger log = LoggerFactory.getLogger(XSSFColumnShifterTest.class + "_T");
23
	private XSSFSheet sheet1, sheet2;
24
	private Workbook wb07;
25
26
    protected final ITestDataProvider _testDataProvider;
27
28
	public XSSFColumnShifterTest(){
29
        _testDataProvider = XSSFITestDataProvider.instance; 
30
    }
31
32
	@Before
33
	public void init() {
34
		wb07 = new XSSFWorkbook();
35
		sheet1 = (XSSFSheet) wb07.createSheet("sheet1");
36
		XSSFRow row = sheet1.createRow(0);
37
		row.createCell(0, CellType.NUMERIC).setCellValue(0);
38
		row.createCell(1, CellType.NUMERIC).setCellValue(1);
39
		XSSFCell c1 = row.createCell(2, CellType.NUMERIC);
40
		c1.setCellValue(2);
41
42
		row = sheet1.createRow(1);
43
		row.createCell(0, CellType.NUMERIC).setCellValue(0.1);
44
		row.createCell(1, CellType.NUMERIC).setCellValue(1.1);
45
		row.createCell(2, CellType.NUMERIC).setCellValue(2.1);
46
		row = sheet1.createRow(2);
47
		row.createCell(0, CellType.NUMERIC).setCellValue(0.2);
48
		row.createCell(1, CellType.NUMERIC).setCellValue(1.2);
49
		row.createCell(2, CellType.NUMERIC).setCellValue(2.2);
50
		row = sheet1.createRow(3);
51
		row.createCell(0, CellType.FORMULA).setCellFormula("A2*B3");
52
		row.createCell(1, CellType.NUMERIC).setCellValue(1.3);
53
		row.createCell(2, CellType.FORMULA).setCellFormula("B1-B3");
54
		row = sheet1.createRow(4);
55
		row.createCell(0, CellType.FORMULA).setCellFormula("SUM(C1:C4)");
56
		row.createCell(1, CellType.FORMULA).setCellFormula("SUM(A3:C3)");
57
		row.createCell(2, CellType.FORMULA).setCellFormula("$C1+C$2");
58
		row = sheet1.createRow(5);
59
		row.createCell(1, CellType.NUMERIC).setCellValue(1.5);
60
		/*
61
		 * sheet2 = (XSSFSheet)wb07.createSheet("sheet2"); row =
62
		 * sheet2.createRow(0); row.createCell(0,
63
		 * CellType.NUMERIC).setCellValue(10); row.createCell(1,
64
		 * CellType.NUMERIC).setCellValue(11); row.createCell(2,
65
		 * CellType.FORMULA).setCellFormula("SUM(Sheet1!B3:C3)"); row =
66
		 * sheet2.createRow(1); row.createCell(0,
67
		 * CellType.NUMERIC).setCellValue(21); row.createCell(1,
68
		 * CellType.NUMERIC).setCellValue(22); row.createCell(2,
69
		 * CellType.NUMERIC).setCellValue(23); row = sheet2.createRow(2);
70
		 * row.createCell(0,
71
		 * CellType.FORMULA).setCellFormula("Sheet1!A4+Sheet1!C2+A2");
72
		 * row.createCell(1,
73
		 * CellType.FORMULA).setCellFormula("SUM(Sheet1!A3:$C3)"); row =
74
		 * sheet2.createRow(3); row.createCell(0,
75
		 * CellType.STRING).setCellValue("dummy");
76
		 */
77
		// writeSheetToLog(sheet1);
78
	}
79
80
	@Test
81
	public void testInsertOneColumn() {
82
		sheet1.shiftColumns(1, 2, 1);
83
		writeSheetToLog(sheet1);
84
		String formulaA4 = sheet1.getRow(3).getCell(0).getCellFormula();
85
		assertEquals("A2*C3", formulaA4);
86
		String formulaC4 = sheet1.getRow(3).getCell(3).getCellFormula();
87
		assertEquals("C1-C3", formulaC4);
88
		String formulaB5 = sheet1.getRow(4).getCell(2).getCellFormula();
89
		assertEquals("SUM(A3:D3)", formulaB5);
90
		String formulaD5 = sheet1.getRow(4).getCell(3).getCellFormula(); // $C1+C$2
91
		assertEquals("$D1+D$2", formulaD5);
92
93
		String newb5Empty = sheet1.getRow(4).getCell(1).getStringCellValue();
94
		assertEquals(newb5Empty, "");
95
	}
96
97
	@Test
98
	public void testInsertTwoColumns() {
99
		sheet1.shiftColumns(1, 2, 2);
100
		String formulaA4 = sheet1.getRow(3).getCell(0).getCellFormula();
101
		assertEquals("A2*D3", formulaA4);
102
		String formulaD4 = sheet1.getRow(3).getCell(4).getCellFormula();
103
		assertEquals("D1-D3", formulaD4);
104
		String formulaD5 = sheet1.getRow(4).getCell(3).getCellFormula();
105
		assertEquals("SUM(A3:E3)", formulaD5);
106
107
		String b5Empty = sheet1.getRow(4).getCell(1).getStringCellValue();
108
		assertEquals(b5Empty, "");
109
		Object c6Null = sheet1.getRow(5).getCell(2); // null cell A5 is shifted
110
														// for 2 columns, so now
111
														// c5 should be null
112
		assertEquals(c6Null, null);
113
	}
114
115
	public static void writeSheetToLog(Sheet sheet) {
116
		int rowIndex = sheet.getFirstRowNum();/*
117
		while (rowIndex <= sheet.getLastRowNum()) {
118
			Row row = sheet.getRow(rowIndex);
119
			if (row == null)
120
				;//log.trace("null row!");
121
			else
122
				log.trace(String.format(
123
						"%1$12s; %2$12s; %3$12s; %4$12s; %5$12s; %6$12s; %7$12s; %8$12s; %9$12s; %10$12s; %11$12s",
124
						row.getCell(0) != null ? row.getCell(0).getCellComment() : "null",
125
						row.getCell(1) != null ? row.getCell(1).getCellComment() : "null",
126
						row.getCell(2) != null ? row.getCell(2).getCellComment() : "null",
127
						row.getCell(3) != null ? row.getCell(3).getCellComment() : "null",
128
						row.getCell(4) != null ? row.getCell(4).getCellComment() : "null",
129
						row.getCell(5) != null ? row.getCell(5).getCellComment() : "null",
130
						row.getCell(6) != null ? row.getCell(6).getCellComment() : "null",
131
						row.getCell(7) != null ? row.getCell(7).getCellComment() : "null",
132
						row.getCell(8) != null ? row.getCell(8).getCellComment() : "null",
133
						row.getCell(9) != null ? row.getCell(9).getCellComment() : "null",
134
						row.getCell(10) != null ? row.getCell(10).getCellComment() : "null"));
135
			rowIndex++;
136
		}
137
		log.trace("");*/
138
	}
139
140
	@Test
141
	public void testShiftHyperlinks() throws IOException {
142
		Workbook wb = _testDataProvider.createWorkbook();
143
		Sheet sheet = wb.createSheet("test");
144
		Row row = sheet.createRow(0);
145
146
		// How to create hyperlinks
147
		// https://poi.apache.org/spreadsheet/quick-guide.html#Hyperlinks
148
		CreationHelper helper = wb.getCreationHelper();
149
		CellStyle hlinkStyle = wb.createCellStyle();
150
		Font hlinkFont = wb.createFont();
151
		hlinkFont.setUnderline(Font.U_SINGLE);
152
		hlinkFont.setColor(IndexedColors.BLUE.getIndex());
153
		hlinkStyle.setFont(hlinkFont);
154
155
		// 3D relative document link
156
		// CellAddress=A1, shifted to A4
157
		Cell cell = row.createCell(0);
158
		cell.setCellStyle(hlinkStyle);
159
		createHyperlink(helper, cell, HyperlinkType.DOCUMENT, "test!E1");
160
161
		// URL
162
		cell = row.createCell(1);
163
		// CellAddress=B1, shifted to B4
164
		cell.setCellStyle(hlinkStyle);
165
		createHyperlink(helper, cell, HyperlinkType.URL, "http://poi.apache.org/");
166
167
		// row0 will be shifted on top of row1, so this URL should be removed
168
		// from the workbook
169
		Row overwrittenRow = sheet.createRow(3);
170
		cell = overwrittenRow.createCell(2);
171
		// CellAddress=C4, will be overwritten (deleted)
172
		cell.setCellStyle(hlinkStyle);
173
		createHyperlink(helper, cell, HyperlinkType.EMAIL, "mailto:poi@apache.org");
174
175
		Row unaffectedRow = sheet.createRow(20);
176
		cell = unaffectedRow.createCell(3);
177
		// CellAddress=D21, will be unaffected
178
		cell.setCellStyle(hlinkStyle);
179
		createHyperlink(helper, cell, HyperlinkType.FILE, "54524.xlsx");
180
181
		cell = wb.createSheet("other").createRow(0).createCell(0);
182
		// CellAddress=Other!A1, will be unaffected
183
		cell.setCellStyle(hlinkStyle);
184
		createHyperlink(helper, cell, HyperlinkType.URL, "http://apache.org/");
185
186
		int startRow = 0;
187
		int endRow = 4;
188
		int n = 3;
189
		writeSheetToLog(sheet);
190
		sheet.shiftColumns(startRow, endRow, n);
191
		writeSheetToLog(sheet);
192
193
		Workbook read = _testDataProvider.writeOutAndReadBack(wb);
194
		wb.close();
195
196
		Sheet sh = read.getSheet("test");
197
198
		Row shiftedRow = sh.getRow(0);
199
200
		// document link anchored on a shifted cell should be moved
201
		// Note that hyperlinks do not track what they point to, so this
202
		// hyperlink should still refer to test!E1
203
		verifyHyperlink(shiftedRow.getCell(3), HyperlinkType.DOCUMENT, "test!E1");
204
205
		// URL, EMAIL, and FILE links anchored on a shifted cell should be moved
206
		verifyHyperlink(shiftedRow.getCell(4), HyperlinkType.URL, "http://poi.apache.org/");
207
208
		// Make sure hyperlinks were moved and not copied
209
		assertNull("Document hyperlink should be moved, not copied", sh.getHyperlink(0, 0));
210
		assertNull("URL hyperlink should be moved, not copied", sh.getHyperlink(1, 0));
211
212
		assertEquals(4, sh.getHyperlinkList().size());
213
		read.close();
214
	}
215
216
	private void createHyperlink(CreationHelper helper, Cell cell, HyperlinkType linkType, String ref) {
217
		cell.setCellValue(ref);
218
		Hyperlink link = helper.createHyperlink(linkType);
219
		link.setAddress(ref);
220
		cell.setHyperlink(link);
221
	}
222
223
	private void verifyHyperlink(Cell cell, HyperlinkType linkType, String ref) {
224
		assertTrue(cellHasHyperlink(cell));
225
		Hyperlink link = cell.getHyperlink();
226
		assertEquals(linkType, link.getTypeEnum());
227
		assertEquals(ref, link.getAddress());
228
	}
229
230
	private boolean cellHasHyperlink(Cell cell) {
231
		return (cell != null) && (cell.getHyperlink() != null);
232
	}
233
234
    @Test
235
    public void shiftMergedColumnsToMergedColumnsRight() throws IOException {
236
        Workbook wb = _testDataProvider.createWorkbook();
237
        Sheet sheet = wb.createSheet("test");
238
239
        // populate sheet cells
240
        populateSheetCells(sheet);
241
        writeSheetToLog(sheet);
242
        CellRangeAddress A1_A5 = new CellRangeAddress(0, 4, 0, 0);
243
        CellRangeAddress B1_B3 = new CellRangeAddress(0, 2, 1, 1);
244
245
        sheet.addMergedRegion(B1_B3);
246
        sheet.addMergedRegion(A1_A5);
247
248
        // A1:A5 should be moved to B1:B5
249
        // B1:B3 will be removed
250
        sheet.shiftColumns(0, 0, 1);
251
        writeSheetToLog(sheet);
252
        
253
        assertEquals(1, sheet.getNumMergedRegions());
254
        assertEquals(CellRangeAddress.valueOf("B1:B5"), sheet.getMergedRegion(0));
255
256
        wb.close();
257
    }
258
    @Test
259
    public void shiftMergedColumnsToMergedColumnsLeft() throws IOException {
260
        Workbook wb = _testDataProvider.createWorkbook();
261
        Sheet sheet = wb.createSheet("test");
262
        populateSheetCells(sheet);
263
264
        CellRangeAddress A1_A5 = new CellRangeAddress(0, 4, 0, 0);
265
        CellRangeAddress B1_B3 = new CellRangeAddress(0, 2, 1, 1);
266
267
        sheet.addMergedRegion(A1_A5);
268
        sheet.addMergedRegion(B1_B3);
269
270
        // A1:E1 should be removed
271
        // B1:B3 will be A1:A3
272
        sheet.shiftColumns(1, 5, -1);
273
274
        assertEquals(1, sheet.getNumMergedRegions());
275
        assertEquals(CellRangeAddress.valueOf("A1:A3"), sheet.getMergedRegion(0));
276
277
        wb.close();
278
    }
279
280
    private void populateSheetCells(Sheet sheet) {
281
        // populate sheet cells
282
        for (int i = 0; i < 2; i++) {
283
            Row row = sheet.createRow(i);
284
            for (int j = 0; j < 5; j++) {
285
                Cell cell = row.createCell(j);
286
                cell.setCellValue(i + "x" + j);
287
            }
288
        }
289
    }
290
291
    @Test
292
    public final void testShiftWithMergedRegions() throws IOException {
293
        Workbook wb = _testDataProvider.createWorkbook();
294
        Sheet sheet = wb.createSheet();
295
        Row row = sheet.createRow(0);
296
        row.createCell(0).setCellValue(1.1);
297
        row = sheet.createRow(1);
298
        row.createCell(0).setCellValue(2.2);
299
        CellRangeAddress region = new CellRangeAddress(0, 2, 0, 0);
300
        assertEquals("A1:A3", region.formatAsString());
301
302
        sheet.addMergedRegion(region);
303
304
        sheet.shiftColumns(0, 1, 2);
305
        region = sheet.getMergedRegion(0);
306
        assertEquals("C1:C3", region.formatAsString());
307
        wb.close();
308
    }
309
310
	@Test
311
	public void testCommentsShifting() throws IOException {
312
		Workbook wb = XSSFTestDataSamples.openSampleWorkbook("56017.xlsx");
313
314
		Sheet sheet = wb.getSheetAt(0);
315
		Comment comment = sheet.getCellComment(new CellAddress(0, 0));
316
		assertNotNull(comment);
317
		assertEquals("Amdocs", comment.getAuthor());
318
		assertEquals("Amdocs:\ntest\n", comment.getString().getString());
319
320
		sheet.shiftColumns(0, 1, 1);
321
322
		// comment in column 0 is gone
323
		comment = sheet.getCellComment(new CellAddress(0, 0));
324
		assertNull(comment);
325
326
		// comment is column in column 1
327
		comment = sheet.getCellComment(new CellAddress(0, 1));
328
		assertNotNull(comment);
329
		assertEquals("Amdocs", comment.getAuthor());
330
		assertEquals("Amdocs:\ntest\n", comment.getString().getString());
331
332
		Workbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(wb);
333
		wb.close();
334
		assertNotNull(wbBack);
335
336
		Sheet sheetBack = wbBack.getSheetAt(0);
337
338
		// comment in column 0 is gone
339
		comment = sheetBack.getCellComment(new CellAddress(0, 0));
340
		assertNull(comment);
341
342
		// comment is now in column 1
343
		comment = sheetBack.getCellComment(new CellAddress(0, 1));
344
		assertNotNull(comment);
345
		assertEquals("Amdocs", comment.getAuthor());
346
		assertEquals("Amdocs:\ntest\n", comment.getString().getString());
347
		wbBack.close();
348
	}
349
	
350
	// transposed version of TestXSSFSheetShiftRows.testBug54524()
351
    @Test
352
    public void testBug54524() throws IOException {
353
        Workbook wb = _testDataProvider.createWorkbook();
354
        Sheet sheet = wb.createSheet();
355
        Row firstRow = sheet.createRow(0); 
356
        firstRow.createCell(0).setCellValue("");
357
        firstRow.createCell(1).setCellValue(1);
358
        firstRow.createCell(2).setCellValue(2);
359
        firstRow.createCell(3).setCellFormula("SUM(B1:C1)");
360
        firstRow.createCell(4).setCellValue("X");
361
        
362
        sheet.shiftColumns(3, 5, -1);
363
364
        Cell cell = CellUtil.getCell(sheet.getRow(0), 1);
365
        assertEquals(1.0, cell.getNumericCellValue(), 0);
366
        cell = CellUtil.getCell(sheet.getRow(0), 2);
367
        assertEquals("SUM(B1:B1)", cell.getCellFormula());
368
        cell = CellUtil.getCell(sheet.getRow(0), 3);
369
        assertEquals("X", cell.getStringCellValue());
370
        wb.close();
371
    }
372
373
}

Return to bug 61474