You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by fa...@apache.org on 2018/01/30 13:16:50 UTC
svn commit: r1822639 - in /poi/trunk/src: java/org/apache/poi/hssf/usermodel/
java/org/apache/poi/hssf/usermodel/helpers/ java/org/apache/poi/ss/formula/
java/org/apache/poi/ss/usermodel/ java/org/apache/poi/ss/usermodel/helpers/
ooxml/java/org/apache/...
Author: fanningpj
Date: Tue Jan 30 13:16:49 2018
New Revision: 1822639
URL: http://svn.apache.org/viewvc?rev=1822639&view=rev
Log:
[github-81] Formula adjusting in context of column shifting. Thanks to Dragan Jovanović. This closes #81
Added:
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftColumns.java (with props)
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/helpers/TestXSSFColumnShifting.java (with props)
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFColumnShifting.java (with props)
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheetShiftColumns.java (with props)
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestColumnShifting.java (with props)
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftColumns.java (with props)
Modified:
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFColumnShifter.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java
poi/trunk/src/java/org/apache/poi/ss/formula/FormulaShifter.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/Row.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/helpers/ColumnShifter.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFRow.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFRow.java
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestRow.java
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java Tue Jan 30 13:16:49 2018
@@ -24,11 +24,13 @@ import org.apache.poi.hssf.record.CellVa
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 Ro
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;
+ }
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java Tue Jan 30 13:16:49 2018
@@ -50,6 +50,8 @@ import org.apache.poi.hssf.record.aggreg
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.CellRefere
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
// 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
}
}
}
+
+ /**
+ * 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);
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFColumnShifter.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFColumnShifter.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFColumnShifter.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFColumnShifter.java Tue Jan 30 13:16:49 2018
@@ -65,4 +65,4 @@ public final class HSSFColumnShifter ext
throw new NotImplementedException("updateHyperlinks");
}
-}
+}
\ No newline at end of file
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/helpers/HSSFRowShifter.java Tue Jan 30 13:16:49 2018
@@ -20,6 +20,8 @@ package org.apache.poi.hssf.usermodel.he
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;
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/FormulaShifter.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/FormulaShifter.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/FormulaShifter.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/FormulaShifter.java Tue Jan 30 13:16:49 2018
@@ -5,9 +5,7 @@
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
-
http://www.apache.org/licenses/LICENSE-2.0
-
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
@@ -838,4 +836,4 @@ public final class FormulaShifter {
throw new IllegalArgumentException("Unexpected ref ptg class (" + ptg.getClass().getName() + ")");
}
-}
+}
\ No newline at end of file
Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/Row.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/Row.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/Row.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/Row.java Tue Jan 30 13:16:49 2018
@@ -234,4 +234,7 @@ public interface Row extends Iterable<Ce
* you take it out of them.
*/
public int getOutlineLevel();
+
+ public void shiftCellsRight(int firstShiftColumnIndex, int lastShiftColumnIndex, int step);
+ public void shiftCellsLeft(int firstShiftColumnIndex, int lastShiftColumnIndex, int step);
}
Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java Tue Jan 30 13:16:49 2018
@@ -704,6 +704,17 @@ public interface Sheet extends Iterable<
void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight);
/**
+ * Shifts columns between startColumn and endColumn, n number of columns.
+ * If you use a negative number, it will shift columns left.
+ * Code ensures that columns don't wrap around
+ *
+ * @param startColumn the column to start shifting
+ * @param endColumn the column to end shifting
+ * @param n the number of columns to shift
+ */
+ void shiftColumns(int startColumn, int endColumn, int n);
+
+ /**
* Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
* <p>
* If both colSplit and rowSplit are zero then the existing freeze pane is removed
Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/helpers/ColumnShifter.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/helpers/ColumnShifter.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/helpers/ColumnShifter.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/helpers/ColumnShifter.java Tue Jan 30 13:16:49 2018
@@ -22,6 +22,7 @@ import java.util.HashSet;
import java.util.List;
import java.util.Set;
+import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Beta;
@@ -119,4 +120,18 @@ public abstract class ColumnShifter exte
// if the merged-region and the overwritten area intersect, we need to remove it
return merged.intersects(overwrite);
}
+
+ public void shiftColumns(int firstShiftColumnIndex, int lastShiftColumnIndex, int step){
+ if(step > 0){
+ for (Row row : sheet)
+ if(row != null)
+ row.shiftCellsRight(firstShiftColumnIndex, lastShiftColumnIndex, step);
+ }
+ else if(step < 0){
+ for (Row row : sheet)
+ if(row != null)
+ row.shiftCellsLeft(firstShiftColumnIndex, lastShiftColumnIndex, -step);
+ }
+ //else step == 0 => nothing to shift
+ }
}
Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/helpers/RowShifter.java Tue Jan 30 13:16:49 2018
@@ -35,11 +35,11 @@ import org.apache.poi.ss.util.CellRangeA
public abstract class RowShifter extends BaseRowColShifter {
protected final Sheet sheet;
- public RowShifter(Sheet sh) {
- sheet = sh;
- }
-
- /**
+ public RowShifter(Sheet sh) {
+ sheet = sh;
+ }
+
+ /**
* Shifts, grows, or shrinks the merged regions due to a row shift.
* Merged regions that are completely overlaid by shifting will be deleted.
*
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFRow.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFRow.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFRow.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFRow.java Tue Jan 30 13:16:49 2018
@@ -24,12 +24,14 @@ import java.util.SortedMap;
import java.util.TreeMap;
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.ss.usermodel.Sheet;
import org.apache.poi.util.Internal;
+import org.apache.poi.util.NotImplemented;
/**
* Streaming version of XSSFRow implementing the "BigGridDemo" strategy.
@@ -545,6 +547,16 @@ public class SXSSFRow implements Row, Co
return _cells.hashCode();
}
+ @Override
+ @NotImplemented
+ public void shiftCellsRight(int firstShiftColumnIndex, int lastShiftColumnIndex, int step){
+ throw new NotImplementedException("shiftCellsRight");
+ }
+ @Override
+ @NotImplemented
+ public void shiftCellsLeft(int firstShiftColumnIndex, int lastShiftColumnIndex, int step){
+ throw new NotImplementedException("shiftCellsLeft");
+ }
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java Tue Jan 30 13:16:49 2018
@@ -2119,4 +2119,10 @@ public class SXSSFSheet implements Sheet
color.setIndexed(colorIndex);
pr.setTabColor(color);
}
+
+ @NotImplemented
+ @Override
+ public void shiftColumns(int startColumn, int endColumn, int n){
+ throw new UnsupportedOperationException("NotImplemented");
+ }
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java Tue Jan 30 13:16:49 2018
@@ -49,6 +49,7 @@ import org.apache.poi.util.LocaleUtil;
import org.apache.poi.util.Removal;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
+import org.apache.poi.xssf.model.CalculationChain;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
@@ -1324,4 +1325,22 @@ public final class XSSFCell implements C
"You cannot change part of an array.";
notifyArrayFormulaChanging(msg);
}
+
+
+ //Moved from XSSFRow.shift(). Not sure what is purpose.
+ public void updateCellReferencesForShifting(String msg){
+ if(isPartOfArrayFormulaGroup())
+ notifyArrayFormulaChanging(msg);
+ CalculationChain calcChain = getSheet().getWorkbook().getCalculationChain();
+ int sheetId = (int)getSheet().sheet.getSheetId();
+
+ //remove the reference in the calculation chain
+ if(calcChain != null) calcChain.removeItem(sheetId, getReference());
+
+ CTCell ctCell = getCTCell();
+ String r = new CellReference(getRowIndex(), getColumnIndex()).formatAsString();
+ ctCell.setR(r);
+ }
+
}
+
\ No newline at end of file
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java Tue Jan 30 13:16:49 2018
@@ -30,10 +30,8 @@ import org.apache.poi.ss.usermodel.CellS
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
-import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.Beta;
import org.apache.poi.util.Internal;
-import org.apache.poi.xssf.model.CalculationChain;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
@@ -227,7 +225,6 @@ public class XSSFRow implements Row, Com
_cells.put(colI, xcell);
return xcell;
}
-
/**
* Returns the cell at the given (0 based) index,
* with the {@link org.apache.poi.ss.usermodel.Row.MissingCellPolicy} from the parent Workbook.
@@ -554,23 +551,11 @@ public class XSSFRow implements Row, Com
*/
protected void shift(int n) {
int rownum = getRowNum() + n;
- CalculationChain calcChain = _sheet.getWorkbook().getCalculationChain();
- int sheetId = (int)_sheet.sheet.getSheetId();
String msg = "Row[rownum="+getRowNum()+"] contains cell(s) included in a multi-cell array formula. " +
"You cannot change part of an array.";
for(Cell c : this){
- XSSFCell cell = (XSSFCell)c;
- if(cell.isPartOfArrayFormulaGroup()){
- cell.notifyArrayFormulaChanging(msg);
- }
-
- //remove the reference in the calculation chain
- if(calcChain != null) calcChain.removeItem(sheetId, cell.getReference());
-
- CTCell ctCell = cell.getCTCell();
- String r = new CellReference(rownum, cell.getColumnIndex()).formatAsString();
- ctCell.setR(r);
- }
+ ((XSSFCell)c).updateCellReferencesForShifting(msg);
+ }
setRowNum(rownum);
}
@@ -620,13 +605,14 @@ public class XSSFRow implements Row, Com
destCell.copyCellFrom(srcCell, policy);
}
- final XSSFRowShifter rowShifter = new XSSFRowShifter(_sheet);
final int sheetIndex = _sheet.getWorkbook().getSheetIndex(_sheet);
final String sheetName = _sheet.getWorkbook().getSheetName(sheetIndex);
final int srcRowNum = srcRow.getRowNum();
final int destRowNum = getRowNum();
final int rowDifference = destRowNum - srcRowNum;
+
final FormulaShifter formulaShifter = FormulaShifter.createForRowCopy(sheetIndex, sheetName, srcRowNum, srcRowNum, rowDifference, SpreadsheetVersion.EXCEL2007);
+ final XSSFRowShifter rowShifter = new XSSFRowShifter(_sheet);
rowShifter.updateRowFormulas(this, formulaShifter);
// Copy merged regions that are fully contained on the row
@@ -652,4 +638,68 @@ public class XSSFRow implements Row, Com
public int getOutlineLevel() {
return _row.getOutlineLevel();
}
+
+ /**
+ * 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));
+ for (int columnIndex = lastShiftColumnIndex; columnIndex >= firstShiftColumnIndex; columnIndex--){ // process cells backwards, because of shifting
+ shiftCell(columnIndex, step);
+ }
+ for (int columnIndex = firstShiftColumnIndex; columnIndex <= firstShiftColumnIndex+step-1; columnIndex++)
+ {
+ _cells.remove(columnIndex);
+ XSSFCell targetCell = getCell(columnIndex);
+ if(targetCell != null)
+ targetCell.getCTCell().set(CTCell.Factory.newInstance());
+ }
+ }
+ /**
+ * 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++){
+ shiftCell(columnIndex, -step);
+ }
+ for (int columnIndex = lastShiftColumnIndex-step+1; columnIndex <= lastShiftColumnIndex; columnIndex++){
+ _cells.remove(columnIndex);
+ XSSFCell targetCell = getCell(columnIndex);
+ if(targetCell != null)
+ targetCell.getCTCell().set(CTCell.Factory.newInstance());
+ }
+ }
+ private void shiftCell(int columnIndex, int step/*pass negative value for left shift*/){
+ if(columnIndex + step < 0) // only for shifting left
+ throw new IllegalStateException("Column index less than zero : " + (Integer.valueOf(columnIndex + step)).toString());
+
+ XSSFCell currentCell = getCell(columnIndex);
+ if(currentCell != null){
+ currentCell.setCellNum(columnIndex+step);
+ _cells.put(columnIndex+step, currentCell);
+ }
+ else {
+ _cells.remove(columnIndex+step);
+ XSSFCell targetCell = getCell(columnIndex+step);
+ if(targetCell != null)
+ targetCell.getCTCell().set(CTCell.Factory.newInstance());
+ }
+ }
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java Tue Jan 30 13:16:49 2018
@@ -84,6 +84,7 @@ import org.apache.poi.util.Units;
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;
@@ -2985,7 +2986,65 @@ public class XSSFSheet extends POIXMLDoc
public void shiftRows(int startRow, int endRow, final int n, boolean copyRowHeight, boolean resetOriginalRowHeight) {
XSSFVMLDrawing vml = getVMLDrawing(false);
- // first remove all rows which will be overwritten
+ int sheetIndex = getWorkbook().getSheetIndex(this);
+ String sheetName = getWorkbook().getSheetName(sheetIndex);
+ FormulaShifter formulaShifter = FormulaShifter.createForRowShift(
+ sheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL2007);
+ removeOverwritten(vml, startRow, endRow, n);
+ shiftCommentsAndRows(vml, startRow, endRow, n);
+
+ XSSFRowShifter rowShifter = new XSSFRowShifter(this);
+ rowShifter.shiftMergedRegions(startRow, endRow, n);
+ rowShifter.updateNamedRanges(formulaShifter);
+ rowShifter.updateFormulas(formulaShifter);
+ rowShifter.updateConditionalFormatting(formulaShifter);
+ rowShifter.updateHyperlinks(formulaShifter);
+
+ //rebuild the _rows map
+ Map<Integer, XSSFRow> map = new HashMap<>();
+ 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);
+ }
+
+ /**
+ * Shifts columns between startColumn and endColumn n number of columns.
+ * If you use a negative number, it will shift columns left.
+ * Code ensures that columns don't wrap around
+ *
+ * @param startColumn the column to start shifting
+ * @param endColumn the column to end shifting
+ * @param n length of the shifting step
+ */
+ @Override
+ public void shiftColumns(int startColumn, int endColumn, final int n) {
+ XSSFVMLDrawing vml = getVMLDrawing(false);
+ shiftCommentsForColumns(vml, startColumn, endColumn, n);
+ FormulaShifter formulaShifter = FormulaShifter.createForColumnShift(this.getWorkbook().getSheetIndex(this), this.getSheetName(), startColumn, endColumn, n, SpreadsheetVersion.EXCEL2007);
+ XSSFColumnShifter columnShifter = new XSSFColumnShifter(this);
+ columnShifter.shiftColumns(startColumn, endColumn, n);
+ columnShifter.shiftMergedRegions(startColumn, startColumn, n);
+ columnShifter.updateFormulas(formulaShifter);
+ columnShifter.updateConditionalFormatting(formulaShifter);
+ columnShifter.updateHyperlinks(formulaShifter);
+ columnShifter.updateNamedRanges(formulaShifter);
+
+ //rebuild the _rows map
+ Map<Integer, XSSFRow> map = new HashMap<>();
+ for(XSSFRow r : _rows.values()) {
+ 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() ; ) {
XSSFRow row = (XSSFRow)it.next();
int rownum = row.getRowNum();
@@ -3029,16 +3088,123 @@ public class XSSFSheet extends POIXMLDoc
}
}
+ }
+
+ private void shiftCommentsAndRows(XSSFVMLDrawing vml, int startRow, int endRow, final int n){
+ // then do the actual moving and also adjust comments/rowHeight
+ // we need to sort it in a way so the shifting does not mess up the structures,
+ // i.e. when shifting down, start from down and go up, when shifting up, vice-versa
+ SortedMap<XSSFComment, Integer> commentsToShift = new TreeMap<>(new Comparator<XSSFComment>() {
+ @Override
+ public int compare(XSSFComment o1, XSSFComment o2) {
+ int row1 = o1.getRow();
+ int row2 = o2.getRow();
+
+ if (row1 == row2) {
+ // ordering is not important when row is equal, but don't return zero to still
+ // get multiple comments per row into the map
+ return o1.hashCode() - o2.hashCode();
+ }
+
+ // when shifting down, sort higher row-values first
+ if (n > 0) {
+ return row1 < row2 ? 1 : -1;
+ } else {
+ // sort lower-row values first when shifting up
+ return row1 > row2 ? 1 : -1;
+ }
+ }
+ });
+
+
+ for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) {
+ XSSFRow row = (XSSFRow)it.next();
+ int rownum = row.getRowNum();
+
+ if(sheetComments != null){
+ // calculate the new rownum
+ int newrownum = shiftedRowNum(startRow, endRow, n, rownum);
+
+ // is there a change necessary for the current row?
+ if(newrownum != rownum) {
+ CTCommentList lst = sheetComments.getCTComments().getCommentList();
+ for (CTComment comment : lst.getCommentArray()) {
+ String oldRef = comment.getRef();
+ CellReference ref = new CellReference(oldRef);
+
+ // is this comment part of the current row?
+ if(ref.getRow() == rownum) {
+ XSSFComment xssfComment = new XSSFComment(sheetComments, comment,
+ vml == null ? null : vml.findCommentShape(rownum, ref.getCol()));
+
+ // we should not perform the shifting right here as we would then find
+ // already shifted comments and would shift them again...
+ commentsToShift.put(xssfComment, newrownum);
+ }
+ }
+ }
+ }
+
+ if(rownum < startRow || rownum > endRow) {
+ continue;
+ }
+ row.shift(n);
+ }
+ // adjust all the affected comment-structures now
+ // the Map is sorted and thus provides them in the order that we need here,
+ // i.e. from down to up if shifting down, vice-versa otherwise
+ for(Map.Entry<XSSFComment, Integer> entry : commentsToShift.entrySet()) {
+ entry.getKey().setRow(entry.getValue());
+ }
+
+ //rebuild the _rows map
+ Map<Integer, XSSFRow> map = new HashMap<>();
+ for(XSSFRow r : _rows.values()) {
+ // Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory
+ final Integer rownumI = Integer.valueOf(r.getRowNum()); // NOSONAR
+ map.put(rownumI, r);
+ }
+ _rows.clear();
+ _rows.putAll(map);
+
+ }
+ private int shiftedRowNum(int startRow, int endRow, int n, int rownum) {
+ // no change if before any affected row
+ if(rownum < startRow && (n > 0 || (startRow - rownum) > n)) {
+ return rownum;
+ }
+
+ // no change if after any affected row
+ if(rownum > endRow && (n < 0 || (rownum - endRow) > n)) {
+ return rownum;
+ }
+
+ // row before and things are moved up
+ if(rownum < startRow) {
+ // row is moved down by the shifting
+ return rownum + (endRow - startRow);
+ }
+
+ // row is after and things are moved down
+ if(rownum > endRow) {
+ // row is moved up by the shifting
+ return rownum - (endRow - startRow);
+ }
+
+ // row is part of the shifted block
+ return rownum + n;
+ }
+ private void shiftCommentsForColumns(XSSFVMLDrawing vml, int startColumnIndex, int endColumnIndex, final int n){
// then do the actual moving and also adjust comments/rowHeight
// we need to sort it in a way so the shifting does not mess up the structures,
// i.e. when shifting down, start from down and go up, when shifting up, vice-versa
SortedMap<XSSFComment, Integer> commentsToShift = new TreeMap<>(new Comparator<XSSFComment>() {
@Override
public int compare(XSSFComment o1, XSSFComment o2) {
- int row1 = o1.getRow();
- int row2 = o2.getRow();
+ int column1 = o1.getColumn();
+ int column2 = o2.getColumn();
- if (row1 == row2) {
+ if (column1 == column2) {
// ordering is not important when row is equal, but don't return zero to still
// get multiple comments per row into the map
return o1.hashCode() - o2.hashCode();
@@ -3046,74 +3212,37 @@ public class XSSFSheet extends POIXMLDoc
// when shifting down, sort higher row-values first
if (n > 0) {
- return row1 < row2 ? 1 : -1;
+ return column1 < column2 ? 1 : -1;
} else {
// sort lower-row values first when shifting up
- return row1 > row2 ? 1 : -1;
+ return column1 > column2 ? 1 : -1;
}
}
});
- for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) {
- XSSFRow row = (XSSFRow)it.next();
- int rownum = row.getRowNum();
-
- if(sheetComments != null){
- // calculate the new rownum
- int newrownum = shiftedRowNum(startRow, endRow, n, rownum);
+ if(sheetComments != null){
+ CTCommentList lst = sheetComments.getCTComments().getCommentList();
+ for (CTComment comment : lst.getCommentArray()) {
+ String oldRef = comment.getRef();
+ CellReference ref = new CellReference(oldRef);
- // is there a change necessary for the current row?
- if(newrownum != rownum) {
- CTCommentList lst = sheetComments.getCTComments().getCommentList();
- for (CTComment comment : lst.getCommentArray()) {
- String oldRef = comment.getRef();
- CellReference ref = new CellReference(oldRef);
-
- // is this comment part of the current row?
- if(ref.getRow() == rownum) {
- XSSFComment xssfComment = new XSSFComment(sheetComments, comment,
- vml == null ? null : vml.findCommentShape(rownum, ref.getCol()));
-
- // we should not perform the shifting right here as we would then find
- // already shifted comments and would shift them again...
- commentsToShift.put(xssfComment, newrownum);
- }
- }
+ int columnIndex =ref.getCol();
+ int newColumnIndex = shiftedRowNum(startColumnIndex, endColumnIndex, n, columnIndex);
+ if(newColumnIndex != columnIndex){
+ XSSFComment xssfComment = new XSSFComment(sheetComments, comment,
+ vml == null ? null : vml.findCommentShape(ref.getRow(), columnIndex));
+ commentsToShift.put(xssfComment, newColumnIndex);
}
}
-
- if(rownum < startRow || rownum > endRow) {
- continue;
- }
-
- if (!copyRowHeight) {
- row.setHeight((short)-1);
- }
-
- row.shift(n);
}
-
// adjust all the affected comment-structures now
// the Map is sorted and thus provides them in the order that we need here,
// i.e. from down to up if shifting down, vice-versa otherwise
for(Map.Entry<XSSFComment, Integer> entry : commentsToShift.entrySet()) {
- entry.getKey().setRow(entry.getValue());
+ entry.getKey().setColumn(entry.getValue());
}
- XSSFRowShifter rowShifter = new XSSFRowShifter(this);
-
- int sheetIndex = getWorkbook().getSheetIndex(this);
- String sheetName = getWorkbook().getSheetName(sheetIndex);
- FormulaShifter shifter = FormulaShifter.createForRowShift(
- sheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL2007);
-
- rowShifter.updateNamedRanges(shifter);
- rowShifter.updateFormulas(shifter);
- rowShifter.shiftMergedRegions(startRow, endRow, n);
- rowShifter.updateConditionalFormatting(shifter);
- rowShifter.updateHyperlinks(shifter);
-
//rebuild the _rows map
Map<Integer, XSSFRow> map = new HashMap<>();
for(XSSFRow r : _rows.values()) {
@@ -3123,33 +3252,7 @@ public class XSSFSheet extends POIXMLDoc
}
_rows.clear();
_rows.putAll(map);
- }
-
- private int shiftedRowNum(int startRow, int endRow, int n, int rownum) {
- // no change if before any affected row
- if(rownum < startRow && (n > 0 || (startRow - rownum) > n)) {
- return rownum;
- }
- // no change if after any affected row
- if(rownum > endRow && (n < 0 || (rownum - endRow) > n)) {
- return rownum;
- }
-
- // row before and things are moved up
- if(rownum < startRow) {
- // row is moved down by the shifting
- return rownum + (endRow - startRow);
- }
-
- // row is after and things are moved down
- if(rownum > endRow) {
- // row is moved up by the shifting
- return rownum - (endRow - startRow);
- }
-
- // row is part of the shifted block
- return rownum + n;
}
/**
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFVMLDrawing.java Tue Jan 30 13:16:49 2018
@@ -264,7 +264,7 @@ public final class XSSFVMLDrawing extend
*
* @return the comment shape or <code>null</code>
*/
- protected CTShape findCommentShape(int row, int col){
+ public CTShape findCommentShape(int row, int col){
for(XmlObject itm : _items){
if(itm instanceof CTShape){
CTShape sh = (CTShape)itm;
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java Tue Jan 30 13:16:49 2018
@@ -36,7 +36,6 @@ public final class XSSFRowShifter extend
public XSSFRowShifter(XSSFSheet sh) {
super(sh);
}
-
@Override
public void updateNamedRanges(FormulaShifter formulaShifter) {
XSSFRowColShifter.updateNamedRanges(sheet, formulaShifter);
@@ -46,7 +45,7 @@ public final class XSSFRowShifter extend
public void updateFormulas(FormulaShifter formulaShifter) {
XSSFRowColShifter.updateFormulas(sheet, formulaShifter);
}
-
+
/**
* Update the formulas in specified row using the formula shifting policy specified by shifter
*
Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFRow.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFRow.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFRow.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFRow.java Tue Jan 30 13:16:49 2018
@@ -22,6 +22,8 @@ package org.apache.poi.xssf.streaming;
import org.apache.poi.ss.usermodel.BaseTestXRow;
import org.apache.poi.xssf.SXSSFITestDataProvider;
import org.junit.After;
+import org.junit.Ignore;
+import org.junit.Test;
/**
* Tests for XSSFRow
@@ -38,4 +40,16 @@ public final class TestSXSSFRow extends
((SXSSFITestDataProvider) _testDataProvider).cleanup();
}
+ @Override
+ @Ignore ("see <https://bz.apache.org/bugzilla/show_bug.cgi?id=62030#c1>") @Test
+ public void testCellShiftingRight(){
+ // Remove when SXSSFRow.shiftCellsRight() is implemented.
+ }
+ @Override
+ @Ignore ("see <https://bz.apache.org/bugzilla/show_bug.cgi?id=62030#c1>") @Test
+ public void testCellShiftingLeft(){
+ // Remove when SXSSFRow.shiftCellsLeft() is implemented.
+ }
+
+
}
Added: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftColumns.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftColumns.java?rev=1822639&view=auto
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftColumns.java (added)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftColumns.java Tue Jan 30 13:16:49 2018
@@ -0,0 +1,24 @@
+package org.apache.poi.xssf.usermodel;
+
+import java.io.IOException;
+
+import org.apache.poi.ss.usermodel.BaseTestSheetShiftColumns;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.xssf.XSSFITestDataProvider;
+import org.apache.poi.xssf.XSSFTestDataSamples;
+
+public class TestXSSFSheetShiftColumns extends BaseTestSheetShiftColumns {
+ public TestXSSFSheetShiftColumns(){
+ super();
+ workbook = new XSSFWorkbook();
+ _testDataProvider = XSSFITestDataProvider.instance;
+ }
+
+ protected Workbook openWorkbook(String spreadsheetFileName) throws IOException{
+ return XSSFTestDataSamples.openSampleWorkbook(spreadsheetFileName);
+ }
+ protected Workbook getReadBackWorkbook(Workbook wb){
+ return XSSFTestDataSamples.writeOutAndReadBack(wb);
+ }
+
+}
Propchange: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftColumns.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/helpers/TestXSSFColumnShifting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/helpers/TestXSSFColumnShifting.java?rev=1822639&view=auto
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/helpers/TestXSSFColumnShifting.java (added)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/helpers/TestXSSFColumnShifting.java Tue Jan 30 13:16:49 2018
@@ -0,0 +1,18 @@
+package org.apache.poi.xssf.usermodel.helpers;
+
+import org.apache.poi.ss.usermodel.BaseTestColumnShifting;
+import org.apache.poi.xssf.usermodel.XSSFSheet;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+
+public class TestXSSFColumnShifting extends BaseTestColumnShifting{
+ public TestXSSFColumnShifting(){
+ super();
+ wb = new XSSFWorkbook();
+ }
+ @Override
+ protected void initColumnShifter(){
+ columnShifter = new XSSFColumnShifter((XSSFSheet)sheet1);
+ }
+
+
+}
Propchange: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/helpers/TestXSSFColumnShifting.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFColumnShifting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFColumnShifting.java?rev=1822639&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFColumnShifting.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFColumnShifting.java Tue Jan 30 13:16:49 2018
@@ -0,0 +1,16 @@
+package org.apache.poi.hssf.usermodel;
+
+import org.apache.poi.hssf.usermodel.helpers.HSSFColumnShifter;
+import org.apache.poi.ss.usermodel.BaseTestColumnShifting;
+
+public class TestHSSFColumnShifting extends BaseTestColumnShifting {
+ public TestHSSFColumnShifting(){
+ super();
+ wb = new HSSFWorkbook();
+ }
+ @Override
+ protected void initColumnShifter(){
+ columnShifter = new HSSFColumnShifter((HSSFSheet)sheet1);
+ }
+
+}
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFColumnShifting.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheetShiftColumns.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheetShiftColumns.java?rev=1822639&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheetShiftColumns.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheetShiftColumns.java Tue Jan 30 13:16:49 2018
@@ -0,0 +1,69 @@
+package org.apache.poi.hssf.usermodel;
+
+import java.io.IOException;
+
+import org.apache.poi.hssf.HSSFITestDataProvider;
+import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.ss.usermodel.BaseTestSheetShiftColumns;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.junit.Ignore;
+import org.junit.Test;
+
+public class TestHSSFSheetShiftColumns extends BaseTestSheetShiftColumns {
+ public TestHSSFSheetShiftColumns(){
+ super();
+ workbook = new HSSFWorkbook();
+ _testDataProvider = HSSFITestDataProvider.instance;
+ }
+
+ protected Workbook openWorkbook(String spreadsheetFileName)
+ throws IOException {
+ return HSSFTestDataSamples.openSampleWorkbook(spreadsheetFileName);
+ }
+
+ protected Workbook getReadBackWorkbook(Workbook wb) throws IOException {
+ return HSSFTestDataSamples.writeOutAndReadBack((HSSFWorkbook)wb);
+ }
+
+ @Override
+ @Ignore("see <https://bz.apache.org/bugzilla/show_bug.cgi?id=62030>")
+ @Test
+ public void shiftMergedColumnsToMergedColumnsLeft() throws IOException {
+ // This override is used only in order to test failing for HSSF. Please remove method after code is fixed on hssf,
+ // so that original method from BaseTestSheetShiftColumns can be executed.
+ }
+ @Override
+ @Ignore("see <https://bz.apache.org/bugzilla/show_bug.cgi?id=62030>")
+ @Test
+ public void shiftMergedColumnsToMergedColumnsRight() throws IOException {
+ // This override is used only in order to test failing for HSSF. Please remove method after code is fixed on hssf,
+ // so that original method from BaseTestSheetShiftColumns can be executed.
+ }
+ @Override
+ @Ignore("see <https://bz.apache.org/bugzilla/show_bug.cgi?id=62030>")
+ @Test
+ public void testBug54524() throws IOException {
+ // This override is used only in order to test failing for HSSF. Please remove method after code is fixed on hssf,
+ // so that original method from BaseTestSheetShiftColumns can be executed.
+ }
+ @Override
+ @Ignore("see <https://bz.apache.org/bugzilla/show_bug.cgi?id=62030>")
+ @Test
+ public void testCommentsShifting() throws IOException {
+ // This override is used only in order to test failing for HSSF. Please remove method after code is fixed on hssf,
+ // so that original method from BaseTestSheetShiftColumns can be executed.
+ }
+ @Override
+ @Ignore("see <https://bz.apache.org/bugzilla/show_bug.cgi?id=62030>")
+ @Test
+ public void testShiftWithMergedRegions() throws IOException {
+ // This override is used only in order to test failing for HSSF. Please remove method after code is fixed on hssf,
+ // so that original method from BaseTestSheetShiftColumns can be executed.
+ // After removing, you can re-add 'final' keyword to specification of original method.
+ }
+
+ @Override
+ @Ignore("see <https://bz.apache.org/bugzilla/show_bug.cgi?id=62030>")
+ @Test
+ public void testShiftHyperlinks() throws IOException {}
+}
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheetShiftColumns.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestColumnShifting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestColumnShifting.java?rev=1822639&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestColumnShifting.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestColumnShifting.java Tue Jan 30 13:16:49 2018
@@ -0,0 +1,69 @@
+package org.apache.poi.ss.usermodel;
+
+import org.junit.Before;
+import org.junit.Test;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertNull;
+import static org.junit.Assert.assertTrue;
+
+import org.apache.poi.ss.usermodel.helpers.ColumnShifter;
+
+public class BaseTestColumnShifting {
+ protected Workbook wb;
+ protected Sheet sheet1;
+ protected ColumnShifter columnShifter;
+
+ @Before
+ public void init() {
+ int rowIndex = 0;
+ sheet1 = wb.createSheet("sheet1");
+ Row row = sheet1.createRow(rowIndex++);
+ row.createCell(0, CellType.NUMERIC).setCellValue(0);
+ row.createCell(3, CellType.NUMERIC).setCellValue(3);
+ row.createCell(4, CellType.NUMERIC).setCellValue(4);
+
+ row = sheet1.createRow(rowIndex++);
+ row.createCell(0, CellType.NUMERIC).setCellValue(0.1);
+ row.createCell(1, CellType.NUMERIC).setCellValue(1.1);
+ row.createCell(2, CellType.NUMERIC).setCellValue(2.1);
+ row.createCell(3, CellType.NUMERIC).setCellValue(3.1);
+ row.createCell(4, CellType.NUMERIC).setCellValue(4.1);
+ row.createCell(5, CellType.NUMERIC).setCellValue(5.1);
+ row.createCell(6, CellType.NUMERIC).setCellValue(6.1);
+ row.createCell(7, CellType.NUMERIC).setCellValue(7.1);
+ row = sheet1.createRow(rowIndex++);
+ row.createCell(3, CellType.NUMERIC).setCellValue(3.2);
+ row.createCell(5, CellType.NUMERIC).setCellValue(5.2);
+ row.createCell(7, CellType.NUMERIC).setCellValue(7.2);
+
+ initColumnShifter();
+ }
+ protected void initColumnShifter(){
+
+ }
+
+ @Test
+ public void testShift3ColumnsRight() {
+ columnShifter.shiftColumns(1, 2, 3);
+
+ Cell cell = sheet1.getRow(0).getCell(4);
+ assertNull(cell);
+ cell = sheet1.getRow(1).getCell(4);
+ assertEquals(1.1, cell.getNumericCellValue(), 0.01);
+ cell = sheet1.getRow(1).getCell(5);
+ assertEquals(2.1, cell.getNumericCellValue(), 0.01);
+ cell = sheet1.getRow(2).getCell(4);
+ assertNull(cell);
+ }
+
+ @Test
+ public void testShiftLeft() {
+ try {
+ columnShifter.shiftColumns(1, 2, -3);
+ assertTrue("Shift to negative indices should throw exception", false);
+ }
+ catch(IllegalStateException e){
+ assertTrue(true);
+ }
+ }
+}
Propchange: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestColumnShifting.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestRow.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestRow.java?rev=1822639&r1=1822638&r2=1822639&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestRow.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestRow.java Tue Jan 30 13:16:49 2018
@@ -466,4 +466,69 @@ public abstract class BaseTestRow {
wb2.close();
}
+
+ @Test
+ public void testCellShiftingRight() {
+ Workbook wb = _testDataProvider.createWorkbook();
+ Sheet sheet = wb.createSheet("sheet1");
+ Row row = sheet.createRow(0);
+ row.createCell(0, CellType.NUMERIC).setCellValue(0);
+ row.createCell(1, CellType.NUMERIC).setCellValue(1);
+ row.createCell(2, CellType.NUMERIC).setCellValue(2);//C
+ row.createCell(3, CellType.NUMERIC).setCellValue(3);//D
+ row.createCell(4, CellType.NUMERIC).setCellValue(4);//E
+ row.createCell(5, CellType.NUMERIC).setCellValue(5);//F
+ row.createCell(6, CellType.NUMERIC).setCellValue(6);//G
+ try {
+ row.shiftCellsLeft(6, 4, 2); // range [6-4] is illegal
+ fail("expected shiftLeft to fail");
+ } catch (IllegalArgumentException e){
+ row.shiftCellsRight(2, 4, 1);
+ //should be [0.0, 1.0, null, 2.0, 3.0, 4.0, 6.0, null]
+
+ Cell h1 = row.getCell(7);
+ assertNull(h1);
+ Cell g1 = row.getCell(6);
+ assertEquals(6, g1.getNumericCellValue(), 0.01);
+ Cell f1 = row.getCell(5);
+ assertEquals(4, f1.getNumericCellValue(), 0.01);
+ Cell e1 = row.getCell(4);
+ assertEquals(3, e1.getNumericCellValue(), 0.01);
+ Cell d1 = row.getCell(3);
+ assertEquals(2, d1.getNumericCellValue(), 0.01);
+ Cell c1 = row.getCell(2);
+ assertNull(c1);
+ }
+ }
+ @Test
+ public void testCellShiftingLeft() {
+ Workbook wb = _testDataProvider.createWorkbook();
+ Sheet sheet = wb.createSheet("sheet1");
+ Row row = sheet.createRow(0);
+ row.createCell(0, CellType.NUMERIC).setCellValue(0);
+ row.createCell(1, CellType.NUMERIC).setCellValue(1);
+ row.createCell(2, CellType.NUMERIC).setCellValue(2);//C
+ row.createCell(3, CellType.NUMERIC).setCellValue(3);//D
+ row.createCell(4, CellType.NUMERIC).setCellValue(4);//E
+ row.createCell(5, CellType.NUMERIC).setCellValue(5);//F
+ row.createCell(6, CellType.NUMERIC).setCellValue(6);//G
+ try {
+ row.shiftCellsLeft(4, 6, -2); // step = -1 is illegal
+ fail("expected shiftLeft to fail");
+ } catch (IllegalArgumentException e){
+ row.shiftCellsLeft(4, 6, 2);
+ //should be [0.0, 1.0, 4.0, 5.0, 6.0, null, null, null]
+
+ Cell b1 = row.getCell(1);
+ assertEquals(1, b1.getNumericCellValue(), 0.01);
+ Cell c1 = row.getCell(2);
+ assertEquals(4, c1.getNumericCellValue(), 0.01);
+ Cell d1 = row.getCell(3);
+ assertEquals(5, d1.getNumericCellValue(), 0.01);
+ Cell e1 = row.getCell(4);
+ assertEquals(6, e1.getNumericCellValue(), 0.01);
+ Cell f1 = row.getCell(5);
+ assertNull(f1);
+ }
+ }
}
Added: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftColumns.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftColumns.java?rev=1822639&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftColumns.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftColumns.java Tue Jan 30 13:16:49 2018
@@ -0,0 +1,388 @@
+package org.apache.poi.ss.usermodel;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertNotNull;
+import static org.junit.Assert.assertNull;
+import static org.junit.Assert.assertTrue;
+
+import java.io.IOException;
+
+import org.apache.poi.common.usermodel.HyperlinkType;
+import org.apache.poi.ss.ITestDataProvider;
+import org.apache.poi.ss.util.CellAddress;
+import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.ss.util.CellUtil;
+import org.junit.Before;
+import org.junit.Test;
+
+public abstract class BaseTestSheetShiftColumns {
+ protected Sheet sheet1;
+ protected Sheet sheet2;
+ protected Workbook workbook;
+
+ protected ITestDataProvider _testDataProvider;
+
+ public BaseTestSheetShiftColumns(){
+ }
+
+ @Before
+ public void init() {
+ int rowIndex = 0;
+ sheet1 = workbook.createSheet("sheet1");
+ Row row = sheet1.createRow(rowIndex++);
+ row.createCell(0, CellType.NUMERIC).setCellValue(0);
+ row.createCell(1, CellType.NUMERIC).setCellValue(1);
+ row.createCell(2, CellType.NUMERIC).setCellValue(2);
+
+ row = sheet1.createRow(rowIndex++);
+ row.createCell(0, CellType.NUMERIC).setCellValue(0.1);
+ row.createCell(1, CellType.NUMERIC).setCellValue(1.1);
+ row.createCell(2, CellType.NUMERIC).setCellValue(2.1);
+ row = sheet1.createRow(rowIndex++);
+ row.createCell(0, CellType.NUMERIC).setCellValue(0.2);
+ row.createCell(1, CellType.NUMERIC).setCellValue(1.2);
+ row.createCell(2, CellType.NUMERIC).setCellValue(2.2);
+ row = sheet1.createRow(rowIndex++);
+ row.createCell(0, CellType.FORMULA).setCellFormula("A2*B3");
+ row.createCell(1, CellType.NUMERIC).setCellValue(1.3);
+ row.createCell(2, CellType.FORMULA).setCellFormula("B1-B3");
+ row = sheet1.createRow(rowIndex++);
+ row.createCell(0, CellType.FORMULA).setCellFormula("SUM(C1:C4)");
+ row.createCell(1, CellType.FORMULA).setCellFormula("SUM(A3:C3)");
+ row.createCell(2, CellType.FORMULA).setCellFormula("$C1+C$2");
+ row = sheet1.createRow(rowIndex++);
+ row.createCell(1, CellType.NUMERIC).setCellValue(1.5);
+ row = sheet1.createRow(rowIndex);
+ row.createCell(1, CellType.BOOLEAN).setCellValue(false);
+ Cell textCell = row.createCell(2, CellType.STRING);
+ textCell.setCellValue("TEXT");
+ textCell.setCellStyle(newCenterBottomStyle());
+
+ sheet2 = workbook.createSheet("sheet2");
+ row = sheet2.createRow(0); row.createCell(0, CellType.NUMERIC).setCellValue(10);
+ row.createCell(1, CellType.NUMERIC).setCellValue(11);
+ row.createCell(2, CellType.FORMULA).setCellFormula("SUM(sheet1!B3:C3)");
+ row = sheet2.createRow(1);
+ row.createCell(0, CellType.NUMERIC).setCellValue(21);
+ row.createCell(1, CellType.NUMERIC).setCellValue(22);
+ row.createCell(2, CellType.NUMERIC).setCellValue(23);
+ row = sheet2.createRow(2);
+ row.createCell(0, CellType.FORMULA).setCellFormula("sheet1!A4+sheet1!C2+A2");
+ row.createCell(1, CellType.FORMULA).setCellFormula("SUM(sheet1!A3:$C3)");
+ row = sheet2.createRow(3);
+ row.createCell(0, CellType.STRING).setCellValue("dummy");
+ }
+
+ private CellStyle newCenterBottomStyle(){
+ CellStyle style = workbook.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.BOTTOM);
+ return style;
+ }
+
+ @Test
+ public void testShiftOneColumnRight() {
+ sheet1.shiftColumns(1, 2, 1);
+ double c1Value = sheet1.getRow(0).getCell(2).getNumericCellValue();
+ assertEquals(1d, c1Value, 0.01);
+ String formulaA4 = sheet1.getRow(3).getCell(0).getCellFormula();
+ assertEquals("A2*C3", formulaA4);
+ String formulaC4 = sheet1.getRow(3).getCell(3).getCellFormula();
+ assertEquals("C1-C3", formulaC4);
+ String formulaB5 = sheet1.getRow(4).getCell(2).getCellFormula();
+ assertEquals("SUM(A3:D3)", formulaB5);
+ String formulaD5 = sheet1.getRow(4).getCell(3).getCellFormula(); // $C1+C$2
+ assertEquals("$D1+D$2", formulaD5);
+
+ Cell newb5Null = sheet1.getRow(4).getCell(1);
+ assertEquals(newb5Null, null);
+ boolean logicalValue = sheet1.getRow(6).getCell(2).getBooleanCellValue();
+ assertEquals(logicalValue, false);
+ Cell textCell = sheet1.getRow(6).getCell(3);
+ assertEquals(textCell.getStringCellValue(), "TEXT");
+ assertEquals(textCell.getCellStyle().getAlignment(), HorizontalAlignment.CENTER);
+
+ // other sheet
+ String formulaC1 = sheet2.getRow(0).getCell(2).getCellFormula(); // SUM(sheet1!B3:C3)
+ assertEquals("SUM(sheet1!C3:D3)", formulaC1);
+ String formulaA3 = sheet2.getRow(2).getCell(0).getCellFormula(); // sheet1!A4+sheet1!C2+A2
+ assertEquals("sheet1!A4+sheet1!D2+A2", formulaA3);
+ }
+
+ @Test
+ public void testShiftTwoColumnsRight() {
+ sheet1.shiftColumns(1, 2, 2);
+ String formulaA4 = sheet1.getRow(3).getCell(0).getCellFormula();
+ assertEquals("A2*D3", formulaA4);
+ String formulaD4 = sheet1.getRow(3).getCell(4).getCellFormula();
+ assertEquals("D1-D3", formulaD4);
+ String formulaD5 = sheet1.getRow(4).getCell(3).getCellFormula();
+ assertEquals("SUM(A3:E3)", formulaD5);
+
+ Cell b5Null = sheet1.getRow(4).getCell(1);
+ assertEquals(b5Null, null);
+ Object c6Null = sheet1.getRow(5).getCell(2); // null cell A5 is shifted
+ // for 2 columns, so now
+ // c5 should be null
+ assertEquals(c6Null, null);
+ }
+
+ @Test
+ public void testShiftOneColumnLeft() {
+ sheet1.shiftColumns(1, 2, -1);
+
+ String formulaA5 = sheet1.getRow(4).getCell(0).getCellFormula();
+ assertEquals("SUM(A3:B3)", formulaA5);
+ String formulaB4 = sheet1.getRow(3).getCell(1).getCellFormula();
+ assertEquals("A1-A3", formulaB4);
+ String formulaB5 = sheet1.getRow(4).getCell(1).getCellFormula();
+ assertEquals("$B1+B$2", formulaB5);
+ Cell newb6Null = sheet1.getRow(5).getCell(1);
+ assertEquals(newb6Null, null);
+ }
+
+ @Test(expected = IllegalStateException.class)
+ public void testShiftTwoColumnsLeft() {
+ sheet1.shiftColumns(1, 2, -2);
+ }
+
+ @Test
+ public void testShiftHyperlinks() throws IOException {
+ Workbook wb = _testDataProvider.createWorkbook();
+ Sheet sheet = wb.createSheet("test");
+ Row row = sheet.createRow(0);
+
+ // How to create hyperlinks
+ // https://poi.apache.org/spreadsheet/quick-guide.html#Hyperlinks
+ CreationHelper helper = wb.getCreationHelper();
+ CellStyle hlinkStyle = wb.createCellStyle();
+ Font hlinkFont = wb.createFont();
+ hlinkFont.setUnderline(Font.U_SINGLE);
+ hlinkFont.setColor(IndexedColors.BLUE.getIndex());
+ hlinkStyle.setFont(hlinkFont);
+
+ // 3D relative document link
+ // CellAddress=A1, shifted to A4
+ Cell cell = row.createCell(0);
+ cell.setCellStyle(hlinkStyle);
+ createHyperlink(helper, cell, HyperlinkType.DOCUMENT, "test!E1");
+
+ // URL
+ cell = row.createCell(1);
+ // CellAddress=B1, shifted to B4
+ cell.setCellStyle(hlinkStyle);
+ createHyperlink(helper, cell, HyperlinkType.URL, "http://poi.apache.org/");
+
+ // row0 will be shifted on top of row1, so this URL should be removed
+ // from the workbook
+ Row overwrittenRow = sheet.createRow(3);
+ cell = overwrittenRow.createCell(2);
+ // CellAddress=C4, will be overwritten (deleted)
+ cell.setCellStyle(hlinkStyle);
+ createHyperlink(helper, cell, HyperlinkType.EMAIL, "mailto:poi@apache.org");
+
+ Row unaffectedRow = sheet.createRow(20);
+ cell = unaffectedRow.createCell(3);
+ // CellAddress=D21, will be unaffected
+ cell.setCellStyle(hlinkStyle);
+ createHyperlink(helper, cell, HyperlinkType.FILE, "54524.xlsx");
+
+ cell = wb.createSheet("other").createRow(0).createCell(0);
+ // CellAddress=Other!A1, will be unaffected
+ cell.setCellStyle(hlinkStyle);
+ createHyperlink(helper, cell, HyperlinkType.URL, "http://apache.org/");
+
+ int startRow = 0;
+ int endRow = 4;
+ int n = 3;
+ sheet.shiftColumns(startRow, endRow, n);
+
+ Workbook read = _testDataProvider.writeOutAndReadBack(wb);
+ wb.close();
+
+ Sheet sh = read.getSheet("test");
+
+ Row shiftedRow = sh.getRow(0);
+
+ // document link anchored on a shifted cell should be moved
+ // Note that hyperlinks do not track what they point to, so this
+ // hyperlink should still refer to test!E1
+ verifyHyperlink(shiftedRow.getCell(3), HyperlinkType.DOCUMENT, "test!E1");
+
+ // URL, EMAIL, and FILE links anchored on a shifted cell should be moved
+ verifyHyperlink(shiftedRow.getCell(4), HyperlinkType.URL, "http://poi.apache.org/");
+
+ // Make sure hyperlinks were moved and not copied
+ assertNull("Document hyperlink should be moved, not copied", sh.getHyperlink(0, 0));
+ assertNull("URL hyperlink should be moved, not copied", sh.getHyperlink(1, 0));
+
+ assertEquals(4, sh.getHyperlinkList().size());
+ read.close();
+ }
+
+ private void createHyperlink(CreationHelper helper, Cell cell, HyperlinkType linkType, String ref) {
+ cell.setCellValue(ref);
+ Hyperlink link = helper.createHyperlink(linkType);
+ link.setAddress(ref);
+ cell.setHyperlink(link);
+ }
+
+ private void verifyHyperlink(Cell cell, HyperlinkType linkType, String ref) {
+ assertTrue(cellHasHyperlink(cell));
+ if (cell != null) {
+ Hyperlink link = cell.getHyperlink();
+ assertEquals(linkType, link.getType());
+ assertEquals(ref, link.getAddress());
+ }
+ }
+
+ private boolean cellHasHyperlink(Cell cell) {
+ return (cell != null) && (cell.getHyperlink() != null);
+ }
+
+ @Test
+ public void shiftMergedColumnsToMergedColumnsRight() throws IOException {
+ Workbook wb = _testDataProvider.createWorkbook();
+ Sheet sheet = wb.createSheet("test");
+
+ // populate sheet cells
+ populateSheetCells(sheet);
+ CellRangeAddress A1_A5 = new CellRangeAddress(0, 4, 0, 0); // NOSONAR, it's more readable this way
+ CellRangeAddress B1_B3 = new CellRangeAddress(0, 2, 1, 1); // NOSONAR, it's more readable this way
+
+ sheet.addMergedRegion(B1_B3);
+ sheet.addMergedRegion(A1_A5);
+
+ // A1:A5 should be moved to B1:B5
+ // B1:B3 will be removed
+ sheet.shiftColumns(0, 0, 1);
+
+ assertEquals(1, sheet.getNumMergedRegions());
+ assertEquals(CellRangeAddress.valueOf("B1:B5"), sheet.getMergedRegion(0));
+
+ wb.close();
+ }
+
+ @Test
+ public void shiftMergedColumnsToMergedColumnsLeft() throws IOException {
+ Workbook wb = _testDataProvider.createWorkbook();
+ Sheet sheet = wb.createSheet("test");
+ populateSheetCells(sheet);
+
+ CellRangeAddress A1_A5 = new CellRangeAddress(0, 4, 0, 0); // NOSONAR, it's more readable this way
+ CellRangeAddress B1_B3 = new CellRangeAddress(0, 2, 1, 1); // NOSONAR, it's more readable this way
+
+ sheet.addMergedRegion(A1_A5);
+ sheet.addMergedRegion(B1_B3);
+
+ // A1:E1 should be removed
+ // B1:B3 will be A1:A3
+ sheet.shiftColumns(1, 5, -1);
+
+ assertEquals(1, sheet.getNumMergedRegions());
+ assertEquals(CellRangeAddress.valueOf("A1:A3"), sheet.getMergedRegion(0));
+
+ wb.close();
+ }
+
+ private void populateSheetCells(Sheet sheet) {
+ // populate sheet cells
+ for (int i = 0; i < 2; i++) {
+ Row row = sheet.createRow(i);
+ for (int j = 0; j < 5; j++) {
+ Cell cell = row.createCell(j);
+ cell.setCellValue(i + "x" + j);
+ }
+ }
+ }
+
+ @Test
+ public void testShiftWithMergedRegions() throws IOException {
+ Workbook wb = _testDataProvider.createWorkbook();
+ Sheet sheet = wb.createSheet();
+ Row row = sheet.createRow(0);
+ row.createCell(0).setCellValue(1.1);
+ row = sheet.createRow(1);
+ row.createCell(0).setCellValue(2.2);
+ CellRangeAddress region = new CellRangeAddress(0, 2, 0, 0);
+ assertEquals("A1:A3", region.formatAsString());
+
+ sheet.addMergedRegion(region);
+
+ sheet.shiftColumns(0, 1, 2);
+ region = sheet.getMergedRegion(0);
+ assertEquals("C1:C3", region.formatAsString());
+ wb.close();
+ }
+
+ protected abstract Workbook openWorkbook(String spreadsheetFileName) throws IOException;
+ protected abstract Workbook getReadBackWorkbook(Workbook wb) throws IOException;
+
+ protected static final String AMDOCS = "Amdocs";
+ protected static final String AMDOCS_TEST = "Amdocs:\ntest\n";
+
+ @Test
+ public void testCommentsShifting() throws IOException {
+ Workbook inputWb = openWorkbook("56017.xlsx");
+
+ Sheet sheet = inputWb.getSheetAt(0);
+ Comment comment = sheet.getCellComment(new CellAddress(0, 0));
+ assertNotNull(comment);
+ assertEquals(AMDOCS, comment.getAuthor());
+ assertEquals(AMDOCS_TEST, comment.getString().getString());
+
+ sheet.shiftColumns(0, 1, 1);
+
+ // comment in column 0 is gone
+ comment = sheet.getCellComment(new CellAddress(0, 0));
+ assertNull(comment);
+
+ // comment is column in column 1
+ comment = sheet.getCellComment(new CellAddress(0, 1));
+ assertNotNull(comment);
+ assertEquals(AMDOCS, comment.getAuthor());
+ assertEquals(AMDOCS_TEST, comment.getString().getString());
+
+ Workbook wbBack = getReadBackWorkbook(inputWb);
+ inputWb.close();
+ assertNotNull(wbBack);
+
+ Sheet sheetBack = wbBack.getSheetAt(0);
+
+ // comment in column 0 is gone
+ comment = sheetBack.getCellComment(new CellAddress(0, 0));
+ assertNull(comment);
+
+ // comment is now in column 1
+ comment = sheetBack.getCellComment(new CellAddress(0, 1));
+ assertNotNull(comment);
+ assertEquals(AMDOCS, comment.getAuthor());
+ assertEquals(AMDOCS_TEST, comment.getString().getString());
+ wbBack.close();
+ }
+
+ // transposed version of TestXSSFSheetShiftRows.testBug54524()
+ @Test
+ public void testBug54524() throws IOException {
+ Workbook wb = _testDataProvider.createWorkbook();
+ Sheet sheet = wb.createSheet();
+ Row firstRow = sheet.createRow(0);
+ firstRow.createCell(0).setCellValue("");
+ firstRow.createCell(1).setCellValue(1);
+ firstRow.createCell(2).setCellValue(2);
+ firstRow.createCell(3).setCellFormula("SUM(B1:C1)");
+ firstRow.createCell(4).setCellValue("X");
+
+ sheet.shiftColumns(3, 5, -1);
+
+ Cell cell = CellUtil.getCell(sheet.getRow(0), 1);
+ assertEquals(1.0, cell.getNumericCellValue(), 0);
+ cell = CellUtil.getCell(sheet.getRow(0), 2);
+ assertEquals("SUM(B1:B1)", cell.getCellFormula());
+ cell = CellUtil.getCell(sheet.getRow(0), 3);
+ assertEquals("X", cell.getStringCellValue());
+ wb.close();
+ }
+
+
+}
Propchange: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftColumns.java
------------------------------------------------------------------------------
svn:eol-style = native
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org