[github-81] Formula adjusting in context of column shifting. Thanks to Dragan Jovanović. This closes #81

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1822639 13f79535-47bb-0310-9956-ffa450edef68

4 files changed, 94 insertions, 6 deletions

diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java b/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java
index c0eb9bd20a..3a33a75cdd 100644
--- a/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java
+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java
@@ -24,11 +24,13 @@ import org.apache.poi.hssf.record.CellValueRecordInterface;
 import org.apache.poi.hssf.record.ExtendedFormatRecord;
 import org.apache.poi.hssf.record.RowRecord;
 import org.apache.poi.ss.SpreadsheetVersion;
+import org.apache.poi.ss.formula.eval.NotImplementedException;
 import org.apache.poi.ss.usermodel.Cell;
 import org.apache.poi.ss.usermodel.CellStyle;
 import org.apache.poi.ss.usermodel.CellType;
 import org.apache.poi.ss.usermodel.Row;
 import org.apache.poi.util.Configurator;
+import org.apache.poi.util.NotImplemented;
 
 /**
  * High level representation of a row of a spreadsheet.
@@ -715,4 +717,58 @@ public final class HSSFRow implements Row, Comparable<HSSFRow> {
     public int hashCode() {
         return row.hashCode();
     }
+    
+    /**
+     * Shifts column range [firstShiftColumnIndex-lastShiftColumnIndex] step places to the right.
+     * @param startColumn the column to start shifting
+     * @param endColumn the column to end shifting
+     * @param step length of the shifting step
+     */
+    @Override
+    public void shiftCellsRight(int firstShiftColumnIndex, int lastShiftColumnIndex, int step){
+        if(step < 0)
+            throw new IllegalArgumentException("Shifting step may not be negative ");
+        if(firstShiftColumnIndex > lastShiftColumnIndex)
+            throw new IllegalArgumentException(String.format("Incorrect shifting range : %d-%d", firstShiftColumnIndex, lastShiftColumnIndex));
+        if(lastShiftColumnIndex + step + 1> cells.length)
+            extend(lastShiftColumnIndex + step + 1);
+        for (int columnIndex = lastShiftColumnIndex; columnIndex >= firstShiftColumnIndex; columnIndex--){ // process cells backwards, because of shifting 
+            HSSFCell cell = getCell(columnIndex);
+            cells[columnIndex+step] = null;
+            if(cell != null)
+                moveCell(cell, (short)(columnIndex+step));
+        }
+        for (int columnIndex = firstShiftColumnIndex; columnIndex <= firstShiftColumnIndex+step-1; columnIndex++)
+            cells[columnIndex] = null;
+    }
+    private void extend(int newLenght){
+        HSSFCell[] temp = cells.clone();
+        cells = new HSSFCell[newLenght];
+        System.arraycopy(temp, 0, cells, 0, temp.length);
+    }
+    /**
+     * Shifts column range [firstShiftColumnIndex-lastShiftColumnIndex] step places to the left.
+     * @param startColumn the column to start shifting
+     * @param endColumn the column to end shifting
+     * @param step length of the shifting step
+     */
+    @Override
+    public void shiftCellsLeft(int firstShiftColumnIndex, int lastShiftColumnIndex, int step){
+        if(step < 0)
+            throw new IllegalArgumentException("Shifting step may not be negative ");
+        if(firstShiftColumnIndex > lastShiftColumnIndex)
+            throw new IllegalArgumentException(String.format("Incorrect shifting range : %d-%d", firstShiftColumnIndex, lastShiftColumnIndex));
+        if(firstShiftColumnIndex - step < 0) 
+            throw new IllegalStateException("Column index less than zero : " + (Integer.valueOf(firstShiftColumnIndex + step)).toString());
+        for (int columnIndex = firstShiftColumnIndex; columnIndex <= lastShiftColumnIndex; columnIndex++){ 
+            HSSFCell cell = getCell(columnIndex);
+            if(cell != null){
+                cells[columnIndex-step] = null;
+                moveCell(cell, (short)(columnIndex-step));
+            }
+            else cells[columnIndex-step] = null;
+        }
+        for (int columnIndex = lastShiftColumnIndex-step+1; columnIndex <= lastShiftColumnIndex; columnIndex++)
+            cells[columnIndex] = null;
+    }
 }
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
index 162c42b0a5..de3b05c1e5 100644
--- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
@@ -50,6 +50,8 @@ import org.apache.poi.hssf.record.aggregates.DataValidityTable;
 import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
 import org.apache.poi.hssf.record.aggregates.RecordAggregate.RecordVisitor;
 import org.apache.poi.hssf.record.aggregates.WorksheetProtectionBlock;
+import org.apache.poi.hssf.usermodel.helpers.HSSFColumnShifter;
+
 import org.apache.poi.hssf.usermodel.helpers.HSSFRowShifter;
 import org.apache.poi.ss.SpreadsheetVersion;
 import org.apache.poi.ss.formula.FormulaShifter;
@@ -73,6 +75,7 @@ import org.apache.poi.ss.util.CellReference;
 import org.apache.poi.ss.util.PaneInformation;
 import org.apache.poi.ss.util.SSCellRange;
 import org.apache.poi.ss.util.SheetUtil;
+import org.apache.poi.util.Beta;
 import org.apache.poi.util.Configurator;
 import org.apache.poi.util.POILogFactory;
 import org.apache.poi.util.POILogger;
@@ -1647,16 +1650,18 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet {
         // Re-compute the first and last rows of the sheet as needed
         recomputeFirstAndLastRowsForRowShift(startRow, endRow, n);
 
+        int sheetIndex = _workbook.getSheetIndex(this);
+        short externSheetIndex = _book.checkExternSheet(sheetIndex);
+        String sheetName = _workbook.getSheetName(sheetIndex);
+        FormulaShifter formulaShifter = FormulaShifter.createForRowShift(
+                externSheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL97);
         // Update formulas that refer to rows that have been moved
-        updateFormulasForRowShift(startRow, endRow, n);
+        updateFormulasForShift(formulaShifter);
     }
 
-    private void updateFormulasForRowShift(int startRow, int endRow, int n) {
+    private void updateFormulasForShift(FormulaShifter formulaShifter) {
         int sheetIndex = _workbook.getSheetIndex(this);
-        String sheetName = _workbook.getSheetName(sheetIndex);
         short externSheetIndex = _book.checkExternSheet(sheetIndex);
-        FormulaShifter formulaShifter = FormulaShifter.createForRowShift(
-                         externSheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL97);
         // update formulas on this sheet that point to rows which have been moved
         _sheet.updateFormulasAfterCellShift(formulaShifter, externSheetIndex);
 
@@ -1737,6 +1742,31 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet {
             }
         }
     }
+    
+    /**
+     * Shifts columns in range [startColumn, endColumn] for n places to the right.
+     * For n < 0, it will shift columns left.
+     * Additionally adjusts formulas.
+     * Probably should also process other features (hyperlinks, comments...) in the way analog to shiftRows method 
+     * @param startRow               the row to start shifting
+     * @param endRow                 the row to end shifting
+     * @param n                      the number of rows to shift
+     */
+
+    @Beta
+    @Override
+    public void shiftColumns(int startColumn, int endColumn, int n){ 
+        HSSFColumnShifter columnShifter = new HSSFColumnShifter(this); 
+        columnShifter.shiftColumns(startColumn, endColumn, n); 
+        
+        int sheetIndex = _workbook.getSheetIndex(this);
+        short externSheetIndex = _book.checkExternSheet(sheetIndex);
+        String sheetName = _workbook.getSheetName(sheetIndex);
+        FormulaShifter formulaShifter = FormulaShifter.createForColumnShift(
+                externSheetIndex, sheetName, startColumn, endColumn, n, SpreadsheetVersion.EXCEL97);
+        updateFormulasForShift(formulaShifter); 
+        // add logic for hyperlinks etc, like in shiftRows() 
+    } 
 
     protected void insertChartRecords(List<Record> records) {
         int window2Loc = _sheet.findFirstRecordLocBySid(WindowTwoRecord.sid);
diff --git a/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFColumnShifter.java b/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFColumnShifter.java
index 2e9af38f2d..d6b84d6588 100644
--- a/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFColumnShifter.java
+++ b/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFColumnShifter.java
@@ -65,4 +65,4 @@ public final class HSSFColumnShifter extends ColumnShifter {
         throw new NotImplementedException("updateHyperlinks");
     }
 
-}
+}
\ No newline at end of file
diff --git a/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java b/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java
index 31f8834b62..f63f22ee08 100644
--- a/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java
+++ b/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java
@@ -20,6 +20,8 @@ package org.apache.poi.hssf.usermodel.helpers;
 import org.apache.poi.hssf.usermodel.HSSFSheet;
 import org.apache.poi.ss.formula.FormulaShifter;
 import org.apache.poi.ss.formula.eval.NotImplementedException;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
 import org.apache.poi.ss.usermodel.helpers.RowShifter;
 import org.apache.poi.util.NotImplemented;
 import org.apache.poi.util.POILogFactory;