Attachment #35384 for bug #61474

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

Lines 1718-1723 public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { Link Here

(-)a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (+3 lines)
1718	_workbook.getWorkbook().updateNamesAfterCellShift(shifter);	1718	_workbook.getWorkbook().updateNamesAfterCellShift(shifter);
1719	}	1719	}
1720		1720
		1721	public void shiftColumns(int startColumn, int endColumn, int n){
		1722
		1723	}
1721	protected void insertChartRecords(List<Record> records) {	1724	protected void insertChartRecords(List<Record> records) {
1722	int window2Loc = _sheet.findFirstRecordLocBySid(WindowTwoRecord.sid);	1725	int window2Loc = _sheet.findFirstRecordLocBySid(WindowTwoRecord.sid);
1723	_sheet.getRecords().addAll(window2Loc, records);	1726	_sheet.getRecords().addAll(window2Loc, records);




    }

    @NotImplemented
    public void updateFormulas() {
        throw new NotImplementedException("updateFormulas");
    }

    @Internal
    @NotImplemented
    public void updateRowFormulas(Row row) {
        throw new NotImplementedException("updateRowFormulas");
    }

    @NotImplemented
    public void updateConditionalFormatting() {
        throw new NotImplementedException("updateConditionalFormatting");
    }

Lines 692-697 public interface Sheet extends Iterable<Row> { Link Here

(-)a/src/java/org/apache/poi/ss/usermodel/Sheet.java (+1 lines)
692	* @param resetOriginalRowHeight whether to set the original row's height to the default	692	* @param resetOriginalRowHeight whether to set the original row's height to the default
693	*/	693	*/
694	void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight);	694	void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight);
		695	void shiftColumns(int startColumn, int endColumn, int n);
695		696
696	/**	697	/**
697	* Creates a split (freezepane). Any existing freezepane or split pane is overwritten.	698	* Creates a split (freezepane). Any existing freezepane or split pane is overwritten.




package org.apache.poi.ss.usermodel.helpers;

import org.apache.poi.ss.formula.FormulaShifter;
import org.apache.poi.ss.usermodel.Sheet;

public class ColumnShifter {
    protected final Sheet shiftingSheet;
    protected FormulaShifter shifter;

    public ColumnShifter(Sheet sheet, FormulaShifter shifter) {
    	shiftingSheet = sheet;
        this.shifter = shifter;
    }

    
}




     */
    public abstract void updateNamedRanges();

    /**
     * Update formulas.
     */
    public abstract void updateFormulas();

    /**
     * Update the formulas in specified row using the formula shifting policy specified by shifter
     *
     * @param row the row to update the formulas on
     * @param shifter the formula shifting policy
     */
    @Internal
    public abstract void updateRowFormulas(Row row);

    public abstract void updateConditionalFormatting();
    
    /**

Lines 1000-1005 public class SXSSFSheet implements Sheet Link Here

(-)a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java (+5 lines)
1000	throw new RuntimeException("NotImplemented");	1000	throw new RuntimeException("NotImplemented");
1001	}	1001	}
1002		1002
		1003	@NotImplemented
		1004	@Override
		1005	public void shiftColumns(int startColumn, int endColumn, int n){
		1006	throw new RuntimeException("NotImplemented");
		1007	}
1003	/**	1008	/**
1004	* Creates a split (freezepane). Any existing freezepane or split pane is overwritten.	1009	* Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
1005	* @param colSplit Horizonatal position of split.	1010	* @param colSplit Horizonatal position of split.




import org.apache.poi.xssf.model.CommentsTable;
import org.apache.poi.xssf.usermodel.XSSFPivotTable.PivotTableReferenceConfigurator;
import org.apache.poi.xssf.usermodel.helpers.ColumnHelper;
import org.apache.poi.xssf.usermodel.helpers.XSSFColumnShifter;
import org.apache.poi.xssf.usermodel.helpers.XSSFIgnoredErrorHelper;
import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;
import org.apache.xmlbeans.XmlCursor;

        _rows.putAll(map);
    }
    
    @Override
    public void shiftColumns(int startColumn, int endColumn, final int n) {
    	XSSFVMLDrawing vml = getVMLDrawing(false);
       // removeOverwritten(vml, startColumn, endColumn, n);
        //doShifting(vml, startColumn, endColumn, n, false); 
        
        FormulaShifter shifter = FormulaShifter.createForItemShift(this, false, startColumn, endColumn, n);
        XSSFColumnShifter columnShifter = new XSSFColumnShifter(this, shifter);

        /*columnShifter.updateNamedRanges();
        columnShifter.updateFormulas();
        columnShifter.shiftMergedRegions(startColumn, startColumn, n);
        columnShifter.updateConditionalFormatting();
        columnShifter.updateHyperlinks();*/

        //rebuild the _rows map
        Map<Integer, XSSFRow> map = new HashMap<Integer, XSSFRow>();
        for(XSSFRow r : _rows.values()) {
            // Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory
            final Integer rownumI = new Integer(r.getRowNum()); // NOSONAR
            map.put(rownumI, r);
        }
        _rows.clear();
        _rows.putAll(map);
    }
    
    // remove all rows which will be overwritten
    private void removeOverwritten(XSSFVMLDrawing vml, int startRow, int endRow, final int n){
        for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) {




package org.apache.poi.xssf.usermodel.helpers;

import org.apache.poi.ss.formula.FormulaShifter;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.helpers.ColumnShifter;
import org.apache.poi.util.POILogFactory;
import org.apache.poi.util.POILogger;
import org.apache.poi.xssf.usermodel.XSSFCell;

public class XSSFColumnShifter extends ColumnShifter{
	
    private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);
    
    private int firstShiftColumnIndex; 
    private int lastShiftColumnIndex; 
    private int shiftStep;
    
    private XSSFFormulaShiftingManager formulaShiftingManager;

    public XSSFColumnShifter(Sheet sh, FormulaShifter shifter) {
        super(sh, shifter);
        formulaShiftingManager = new XSSFFormulaShiftingManager(sh, shifter);
    }

	public void shiftColumns(int firstShiftColumnIndex, int lastShiftColumnIndex, int step){
		this.firstShiftColumnIndex = firstShiftColumnIndex;
		this.shiftStep = step;
		if(shiftStep > 0)
			shiftColumnsRight();
		//else if(shiftStep < 0)
			//shiftColumnsLeft();
	}
	/**
	 * Inserts shiftStep empty columns at firstShiftColumnIndex-th position, and shifts rest columns to the right 
	 * (see constructor for parameters)
	 */

	private void shiftColumnsRight(){
		for(int rowNo = 0; rowNo <= shiftingSheet.getLastRowNum(); rowNo++)
		{	
			Row row = shiftingSheet.getRow(rowNo);
		    for (int columnIndex = row.getLastCellNum()-1; columnIndex >= 0 ; columnIndex--){ // process cells backwards, because of shifting 
		    	XSSFCell oldCell = (XSSFCell)row.getCell(columnIndex);
		    	if(oldCell == null)
		    		continue;
	    		if(oldCell.getCellTypeEnum() == CellType.FORMULA) // recalculate formula
	    			formulaShiftingManager.updateCellFormula(row, oldCell);

	    		if(columnIndex >= firstShiftColumnIndex){ // shift existing cell 
			    	org.apache.poi.ss.usermodel.Cell newCell = null;
		    		newCell = row.createCell(columnIndex + shiftStep, oldCell.getCellTypeEnum());
		    		cloneCellValue(oldCell,newCell);
		    		if(columnIndex <= firstShiftColumnIndex + shiftStep - 1){ // clear existing cells on place of insertion
		    			oldCell.setCellValue("");
		    			oldCell.setCellType(CellType.STRING);
		    		}
		    	}
    		}
		}
		formulaShiftingManager.updateFormulas();
	}
	/*private void shiftColumnsLeft(){
		for(int rowNo = 0; rowNo <= shiftingSheet.getLastRowNum(); rowNo++)
		{	
			Row row = shiftingSheet.getRow(rowNo);
		    for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++){ // process cells backwards, because of shifting 
		    	org.apache.poi.ss.usermodel.Cell oldCell = row.getCell(columnIndex);
		    	if(columnIndex >= firstShiftColumnIndex + shiftStep && columnIndex < row.getLastCellNum() - shiftStep){ // shift existing cell 
			    	org.apache.poi.ss.usermodel.Cell newCell = null;
		    		newCell = row.getCell(columnIndex - shiftStep);
		    		if(oldCell != null){
			    		if(newCell != null){
			    			oldCell.setCellType(newCell.getCellType());
			    			cloneCellValue(newCell, oldCell);
				    		if(oldCell.getCellTypeEnum() == CellType.FORMULA) // recalculate formula
				    			columnFormulaShifter.shiftFormula(oldCell);
			    		}
			    		else {
			    			oldCell.setCellType(CellType.STRING);
			    			oldCell.setCellValue("");
			    		}
		    		}
		    		else {
		    			oldCell = row.createCell(columnIndex);
			    		if(newCell != null){
			    			oldCell.setCellType(newCell.getCellType());
			    			cloneCellValue(newCell, oldCell);
				    		if(oldCell.getCellTypeEnum() == CellType.FORMULA) // recalculate formula
				    			columnFormulaShifter.shiftFormula(oldCell);
			    		}
			    		else {
			    			oldCell.setCellType(CellType.STRING);
			    			oldCell.setCellValue("");
			    		}
		    		}
		    	}
    		}
		}
		columnFormulaShifter.processFormulasOnOtherSheets();
	}*/
	
    public static void cloneCellValue(org.apache.poi.ss.usermodel.Cell oldCell, org.apache.poi.ss.usermodel.Cell newCell) {
	    switch (oldCell.getCellTypeEnum()) {
	        case STRING:
	            newCell.setCellValue(oldCell.getStringCellValue());
	            break;
	        case NUMERIC:
	            newCell.setCellValue(oldCell.getNumericCellValue());
	            break;
	        case BOOLEAN:
	            newCell.setCellValue(oldCell.getBooleanCellValue());
	            break;
	        case FORMULA:
	            newCell.setCellFormula(oldCell.getCellFormula());
	            break;
	        case ERROR:
	            newCell.setCellErrorValue(oldCell.getErrorCellValue());
	        case BLANK:
	        case _NONE:
	            break;
	    }
	}

}




package org.apache.poi.xssf.usermodel.helpers;

import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaRenderer;
import org.apache.poi.ss.formula.FormulaShifter;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.Internal;
import org.apache.poi.util.POILogFactory;
import org.apache.poi.util.POILogger;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;

public class XSSFFormulaShiftingManager {

    private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);

    protected final Sheet shiftingSheet;
    protected FormulaShifter shifter;

    public XSSFFormulaShiftingManager(Sheet shiftingSheet, FormulaShifter shifter){
    	this.shiftingSheet = shiftingSheet;
        this.shifter = shifter;
	}
    
    public void updateFormulas() {
        //update formulas on the parent sheet
        updateSheetFormulas(shiftingSheet);

        //update formulas on other sheets
        Workbook wb = shiftingSheet.getWorkbook();
        for (Sheet sh : wb) {
            if (shiftingSheet == sh) continue;
            updateSheetFormulas(sh);
        }
    }

    private void updateSheetFormulas(Sheet sh) {
        for (Row r : sh) {
            XSSFRow row = (XSSFRow) r;
            updateRowFormulas(row);
        }
    }

    /**
     * Update the formulas in specified row using the formula shifting policy specified by shifter
     *
     * @param row the row to update the formulas on
     * @param shifter the formula shifting policy
     */
    @Internal
    public void updateRowFormulas(Row row) {
        for (Cell c : row) {
           	updateCellFormula(row, (XSSFCell) c);
        }
    }
    
    public void updateCellFormula(Row row, XSSFCell cell){
        CTCell ctCell = cell.getCTCell();
        if (ctCell.isSetF()) {
            CTCellFormula f = ctCell.getF();
            String formula = f.getStringValue();
            if (formula.length() > 0) {
                String shiftedFormula = shiftFormula(row, formula);
                if (shiftedFormula != null) {
                    f.setStringValue(shiftedFormula);
                    if(f.getT() == STCellFormulaType.SHARED){
                        int si = (int)f.getSi();
                        XSSFSheet sheet = (XSSFSheet) row.getSheet();
                        CTCellFormula sf = sheet.getSharedFormula(si);
                        sf.setStringValue(shiftedFormula);
                        updateRefInCTCellFormula(row, sf);
                    }
                }
            }
            //Range of cells which the formula applies to.
            updateRefInCTCellFormula(row, f);
        }
    }
    private void updateRefInCTCellFormula(Row row, CTCellFormula f) {
        if (f.isSetRef()) { //Range of cells which the formula applies to.
            String ref = f.getRef();
            String shiftedRef = shiftFormula(row, ref);
            if (shiftedRef != null) f.setRef(shiftedRef);
        }
    }

    /**
     * Shift a formula using the supplied FormulaShifter
     *
     * @param row     the row of the cell this formula belongs to. Used to get a reference to the parent workbook.
     * @param formula the formula to shift
     * @param shifter the FormulaShifter object that operates on the parsed formula tokens
     * @return the shifted formula if the formula was changed,
     *         <code>null</code> if the formula wasn't modified
     */
    private String shiftFormula(Row row, String formula) {
        Sheet sheet = row.getSheet();
        Workbook wb = sheet.getWorkbook();
        int sheetIndex = wb.getSheetIndex(sheet);
        final int rowIndex = row.getRowNum();
        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);
        
        try {
            Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
            String shiftedFmla = null;
            if (shifter.adjustFormula(ptgs, sheetIndex)) {
                shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
            }
            return shiftedFmla;
        } catch (FormulaParseException fpe) {
            // Log, but don't change, rather than breaking
            logger.log(POILogger.WARN, "Error shifting formula on row ", row.getRowNum(), fpe);
            return formula;
        }
    }

    
}

Lines 59-72 import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType; Link Here

(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java (-100 / +8 lines)
59	public final class XSSFRowShifter extends RowShifter {	59	public final class XSSFRowShifter extends RowShifter {
60	private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);	60	private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);
61		61
		62	private XSSFFormulaShiftingManager formulaShiftingManager;
		63
62	public XSSFRowShifter(XSSFSheet sh) {	64	public XSSFRowShifter(XSSFSheet sh) {
63	super(sh);	65	super(sh);
64	}	66	}
65	public XSSFRowShifter(Sheet sh, FormulaShifter shifter) {	67	public XSSFRowShifter(Sheet sh, FormulaShifter shifter) {
66	super(sh, shifter);	68	super(sh, shifter);
		69	formulaShiftingManager = new XSSFFormulaShiftingManager(sh, shifter);
67	}	70	}
68		71
69		72
		73	public void updateFormulas(){
		74	formulaShiftingManager.updateFormulas();
		75	}
		76
70	/**	77	/**
71	* Shift merged regions	78	* Shift merged regions
72	*	79	*
Lines 99-201 public final class XSSFRowShifter extends RowShifter { Link Here
99	}	106	}
100	}	107	}
101		108
102	/**
103	* Update formulas.
104	*/
105	public void updateFormulas() {
106	//update formulas on the parent sheet
107	updateSheetFormulas(sheet);
108
109	//update formulas on other sheets
110	Workbook wb = sheet.getWorkbook();
111	for (Sheet sh : wb) {
112	if (sheet == sh) continue;
113	updateSheetFormulas(sh);
114	}
115	}
116
117	private void updateSheetFormulas(Sheet sh) {
118	for (Row r : sh) {
119	XSSFRow row = (XSSFRow) r;
120	updateRowFormulas(row);
121	}
122	}
123
124	/**
125	* Update the formulas in specified row using the formula shifting policy specified by shifter
126	*
127	* @param row the row to update the formulas on
128	* @param shifter the formula shifting policy
129	*/
130	@Internal
131	public void updateRowFormulas(Row row) {
132	XSSFSheet sheet = (XSSFSheet) row.getSheet();
133	for (Cell c : row) {
134	XSSFCell cell = (XSSFCell) c;
135
136	CTCell ctCell = cell.getCTCell();
137	if (ctCell.isSetF()) {
138	CTCellFormula f = ctCell.getF();
139	String formula = f.getStringValue();
140	if (formula.length() > 0) {
141	String shiftedFormula = shiftFormula(row, formula, shifter);
142	if (shiftedFormula != null) {
143	f.setStringValue(shiftedFormula);
144	if(f.getT() == STCellFormulaType.SHARED){
145	int si = (int)f.getSi();
146	CTCellFormula sf = sheet.getSharedFormula(si);
147	sf.setStringValue(shiftedFormula);
148	updateRefInCTCellFormula(row, sf);
149	}
150	}
151
152	}
153
154	//Range of cells which the formula applies to.
155	updateRefInCTCellFormula(row, f);
156	}
157
158	}
159	}
160
161	private void updateRefInCTCellFormula(Row row, CTCellFormula f) {
162	if (f.isSetRef()) { //Range of cells which the formula applies to.
163	String ref = f.getRef();
164	String shiftedRef = shiftFormula(row, ref, shifter);
165	if (shiftedRef != null) f.setRef(shiftedRef);
166	}
167	}
168
169	/**
170	* Shift a formula using the supplied FormulaShifter
171	*
172	* @param row the row of the cell this formula belongs to. Used to get a reference to the parent workbook.
173	* @param formula the formula to shift
174	* @param shifter the FormulaShifter object that operates on the parsed formula tokens
175	* @return the shifted formula if the formula was changed,
176	* <code>null</code> if the formula wasn't modified
177	*/
178	private static String shiftFormula(Row row, String formula, FormulaShifter shifter) {
179	Sheet sheet = row.getSheet();
180	Workbook wb = sheet.getWorkbook();
181	int sheetIndex = wb.getSheetIndex(sheet);
182	final int rowIndex = row.getRowNum();
183	XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);
184
185	try {
186	Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
187	String shiftedFmla = null;
188	if (shifter.adjustFormula(ptgs, sheetIndex)) {
189	shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
190	}
191	return shiftedFmla;
192	} catch (FormulaParseException fpe) {
193	// Log, but don't change, rather than breaking
194	logger.log(POILogger.WARN, "Error shifting formula on row ", row.getRowNum(), fpe);
195	return formula;
196	}
197	}
198
199	public void updateConditionalFormatting() {	109	public void updateConditionalFormatting() {
200	XSSFSheet xsheet = (XSSFSheet) sheet;	110	XSSFSheet xsheet = (XSSFSheet) sheet;
201	XSSFWorkbook wb = xsheet.getWorkbook();	111	XSSFWorkbook wb = xsheet.getWorkbook();
Lines 299-303 public final class XSSFRowShifter extends RowShifter { Link Here
299	}	209	}
300	throw new IllegalStateException("Unexpected shifted ptg class (" + ptg0.getClass().getName() + ")");	210	throw new IllegalStateException("Unexpected shifted ptg class (" + ptg0.getClass().getName() + ")");
301	}	211	}
302
303	}	212	}
304	-

Return to bug 61474

Line 0 Link Here

(-)a/src/java/org/apache/poi/ss/usermodel/helpers/ColumnShifter.java (+16 lines)
	1	package org.apache.poi.ss.usermodel.helpers;
	2
	3	import org.apache.poi.ss.formula.FormulaShifter;
	4	import org.apache.poi.ss.usermodel.Sheet;
	5
	6	public class ColumnShifter {
	7	protected final Sheet shiftingSheet;
	8	protected FormulaShifter shifter;
	9
	10	public ColumnShifter(Sheet sheet, FormulaShifter shifter) {
	11	shiftingSheet = sheet;
	12	this.shifter = shifter;
	13	}
	14
	15
	16	}

Lines 105-124 public abstract class RowShifter { Link Here

(-)a/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java (-14 lines)
105	*/	105	*/
106	public abstract void updateNamedRanges();	106	public abstract void updateNamedRanges();
107		107
108	/**
109	* Update formulas.
110	*/
111	public abstract void updateFormulas();
112
113	/**
114	* Update the formulas in specified row using the formula shifting policy specified by shifter
115	*
116	* @param row the row to update the formulas on
117	* @param shifter the formula shifting policy
118	*/
119	@Internal
120	public abstract void updateRowFormulas(Row row);
121
122	public abstract void updateConditionalFormatting();	108	public abstract void updateConditionalFormatting();
123		109
124	/**	110	/**

Lines 35-40 import org.apache.poi.util.Beta; Link Here

(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java (-2 / +3 lines)
35	import org.apache.poi.util.Internal;	35	import org.apache.poi.util.Internal;
36	import org.apache.poi.xssf.model.CalculationChain;	36	import org.apache.poi.xssf.model.CalculationChain;
37	import org.apache.poi.xssf.model.StylesTable;	37	import org.apache.poi.xssf.model.StylesTable;
		38	import org.apache.poi.xssf.usermodel.helpers.XSSFFormulaShiftingManager;
38	import org.apache.poi.xssf.usermodel.helpers.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 646-653 public class XSSFRow implements Row, Comparable<XSSFRow> { Link Here
646	final int destRowNum = getRowNum();	647	final int destRowNum = getRowNum();
647	final int rowDifference = destRowNum - srcRowNum;	648	final int rowDifference = destRowNum - srcRowNum;
648	final FormulaShifter shifter = FormulaShifter.createForRowCopy(sheetIndex, sheetName, srcRowNum, srcRowNum, rowDifference, SpreadsheetVersion.EXCEL2007);	649	final FormulaShifter shifter = FormulaShifter.createForRowCopy(sheetIndex, sheetName, srcRowNum, srcRowNum, rowDifference, SpreadsheetVersion.EXCEL2007);
649	final XSSFRowShifter rowShifter = new XSSFRowShifter(_sheet, shifter);	650	final XSSFFormulaShiftingManager formulaShiftingManager = new XSSFFormulaShiftingManager(_sheet, shifter);
650	rowShifter.updateRowFormulas(this);	651	formulaShiftingManager.updateRowFormulas(this);
651		652
652	// Copy merged regions that are fully contained on the row	653	// Copy merged regions that are fully contained on the row
653	// FIXME: is this something that rowShifter could be doing?	654	// FIXME: is this something that rowShifter could be doing?

Lines 87-92 import org.apache.poi.util.Units; Link Here

(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (+27 lines)
87	import org.apache.poi.xssf.model.CommentsTable;	87	import org.apache.poi.xssf.model.CommentsTable;
88	import org.apache.poi.xssf.usermodel.XSSFPivotTable.PivotTableReferenceConfigurator;	88	import org.apache.poi.xssf.usermodel.XSSFPivotTable.PivotTableReferenceConfigurator;
89	import org.apache.poi.xssf.usermodel.helpers.ColumnHelper;	89	import org.apache.poi.xssf.usermodel.helpers.ColumnHelper;
		90	import org.apache.poi.xssf.usermodel.helpers.XSSFColumnShifter;
90	import org.apache.poi.xssf.usermodel.helpers.XSSFIgnoredErrorHelper;	91	import org.apache.poi.xssf.usermodel.helpers.XSSFIgnoredErrorHelper;
91	import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;	92	import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;
92	import org.apache.xmlbeans.XmlCursor;	93	import org.apache.xmlbeans.XmlCursor;
Lines 2970-2975 public class XSSFSheet extends POIXMLDocumentPart implements Sheet { Link Here
2970	_rows.putAll(map);	2971	_rows.putAll(map);
2971	}	2972	}
2972		2973
		2974	@Override
		2975	public void shiftColumns(int startColumn, int endColumn, final int n) {
		2976	XSSFVMLDrawing vml = getVMLDrawing(false);
		2977	// removeOverwritten(vml, startColumn, endColumn, n);
		2978	//doShifting(vml, startColumn, endColumn, n, false);
		2979
		2980	FormulaShifter shifter = FormulaShifter.createForItemShift(this, false, startColumn, endColumn, n);
		2981	XSSFColumnShifter columnShifter = new XSSFColumnShifter(this, shifter);
		2982
		2983	/*columnShifter.updateNamedRanges();
		2984	columnShifter.updateFormulas();
		2985	columnShifter.shiftMergedRegions(startColumn, startColumn, n);
		2986	columnShifter.updateConditionalFormatting();
		2987	columnShifter.updateHyperlinks();*/
		2988
		2989	//rebuild the _rows map
		2990	Map<Integer, XSSFRow> map = new HashMap<Integer, XSSFRow>();
		2991	for(XSSFRow r : _rows.values()) {
		2992	// Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory
		2993	final Integer rownumI = new Integer(r.getRowNum()); // NOSONAR
		2994	map.put(rownumI, r);
		2995	}
		2996	_rows.clear();
		2997	_rows.putAll(map);
		2998	}
		2999
2973	// remove all rows which will be overwritten	3000	// remove all rows which will be overwritten
2974	private void removeOverwritten(XSSFVMLDrawing vml, int startRow, int endRow, final int n){	3001	private void removeOverwritten(XSSFVMLDrawing vml, int startRow, int endRow, final int n){
2975	for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) {	3002	for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) {

Line 0 Link Here

(-)a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFColumnShifter.java (+126 lines)
		1	package org.apache.poi.xssf.usermodel.helpers;
		2
		3	import org.apache.poi.ss.formula.FormulaShifter;
		4	import org.apache.poi.ss.usermodel.CellType;
		5	import org.apache.poi.ss.usermodel.Row;
		6	import org.apache.poi.ss.usermodel.Sheet;
		7	import org.apache.poi.ss.usermodel.helpers.ColumnShifter;
		8	import org.apache.poi.util.POILogFactory;
		9	import org.apache.poi.util.POILogger;
		10	import org.apache.poi.xssf.usermodel.XSSFCell;
		11
		12	public class XSSFColumnShifter extends ColumnShifter{
		13
		14	private static final POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);
		15
		16	private int firstShiftColumnIndex;
		17	private int lastShiftColumnIndex;
		18	private int shiftStep;
		19
		20	private XSSFFormulaShiftingManager formulaShiftingManager;
		21
		22	public XSSFColumnShifter(Sheet sh, FormulaShifter shifter) {
		23	super(sh, shifter);
		24	formulaShiftingManager = new XSSFFormulaShiftingManager(sh, shifter);
		25	}
		26
		27	public void shiftColumns(int firstShiftColumnIndex, int lastShiftColumnIndex, int step){
		28	this.firstShiftColumnIndex = firstShiftColumnIndex;
		29	this.shiftStep = step;
		30	if(shiftStep > 0)
		31	shiftColumnsRight();
		32	//else if(shiftStep < 0)
		33	//shiftColumnsLeft();
		34	}
		35	/**
		36	* Inserts shiftStep empty columns at firstShiftColumnIndex-th position, and shifts rest columns to the right
		37	* (see constructor for parameters)
		38	*/
		39
		40	private void shiftColumnsRight(){
		41	for(int rowNo = 0; rowNo <= shiftingSheet.getLastRowNum(); rowNo++)
		42	{
		43	Row row = shiftingSheet.getRow(rowNo);
		44	for (int columnIndex = row.getLastCellNum()-1; columnIndex >= 0 ; columnIndex--){ // process cells backwards, because of shifting
		45	XSSFCell oldCell = (XSSFCell)row.getCell(columnIndex);
		46	if(oldCell == null)
		47	continue;
		48	if(oldCell.getCellTypeEnum() == CellType.FORMULA) // recalculate formula
		49	formulaShiftingManager.updateCellFormula(row, oldCell);
		50
		51	if(columnIndex >= firstShiftColumnIndex){ // shift existing cell
		52	org.apache.poi.ss.usermodel.Cell newCell = null;
		53	newCell = row.createCell(columnIndex + shiftStep, oldCell.getCellTypeEnum());
		54	cloneCellValue(oldCell,newCell);
		55	if(columnIndex <= firstShiftColumnIndex + shiftStep - 1){ // clear existing cells on place of insertion
		56	oldCell.setCellValue("");
		57	oldCell.setCellType(CellType.STRING);
		58	}
		59	}
		60	}
		61	}
		62	formulaShiftingManager.updateFormulas();
		63	}
		64	/*private void shiftColumnsLeft(){
65	for(int rowNo = 0; rowNo <= shiftingSheet.getLastRowNum(); rowNo++)
66	{
67	Row row = shiftingSheet.getRow(rowNo);
68	for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++){ // process cells backwards, because of shifting
69	org.apache.poi.ss.usermodel.Cell oldCell = row.getCell(columnIndex);
70	if(columnIndex >= firstShiftColumnIndex + shiftStep && columnIndex < row.getLastCellNum() - shiftStep){ // shift existing cell
71	org.apache.poi.ss.usermodel.Cell newCell = null;
72	newCell = row.getCell(columnIndex - shiftStep);
73	if(oldCell != null){
74	if(newCell != null){
75	oldCell.setCellType(newCell.getCellType());
76	cloneCellValue(newCell, oldCell);
77	if(oldCell.getCellTypeEnum() == CellType.FORMULA) // recalculate formula
78	columnFormulaShifter.shiftFormula(oldCell);
79	}
80	else {
81	oldCell.setCellType(CellType.STRING);
82	oldCell.setCellValue("");
83	}
84	}
85	else {
86	oldCell = row.createCell(columnIndex);
87	if(newCell != null){
88	oldCell.setCellType(newCell.getCellType());
89	cloneCellValue(newCell, oldCell);
90	if(oldCell.getCellTypeEnum() == CellType.FORMULA) // recalculate formula
91	columnFormulaShifter.shiftFormula(oldCell);
92	}
93	else {
94	oldCell.setCellType(CellType.STRING);
95	oldCell.setCellValue("");
96	}
97	}
98	}
99	}
100	}
101	columnFormulaShifter.processFormulasOnOtherSheets();
102	}*/
103
104	public static void cloneCellValue(org.apache.poi.ss.usermodel.Cell oldCell, org.apache.poi.ss.usermodel.Cell newCell) {
105	switch (oldCell.getCellTypeEnum()) {
106	case STRING:
107	newCell.setCellValue(oldCell.getStringCellValue());
108	break;
109	case NUMERIC:
110	newCell.setCellValue(oldCell.getNumericCellValue());
111	break;
112	case BOOLEAN:
113	newCell.setCellValue(oldCell.getBooleanCellValue());
114	break;
115	case FORMULA:
116	newCell.setCellFormula(oldCell.getCellFormula());
117	break;
118	case ERROR:
119	newCell.setCellErrorValue(oldCell.getErrorCellValue());
120	case BLANK:
121	case _NONE:
122	break;
123	}
124	}
125
126	}