From 57a02664576278cc67fc60f465eafb80f5213ddf Mon Sep 17 00:00:00 2001 From: Shawn Laubach Date: Mon, 7 Oct 2002 16:26:24 +0000 Subject: [PATCH] Added ability to shift rows in a sheet. PR: Obtained from: Submitted by: Reviewed by: git-svn-id: https://svn.apache.org/repos/asf/jakarta/poi/trunk@352881 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/xdocs/hssf/quick-guide.xml | 18 ++++ .../apache/poi/hssf/usermodel/HSSFSheet.java | 47 ++++++++++ .../apache/poi/hssf/data/SimpleMultiCell.xls | Bin 0 -> 13824 bytes .../poi/hssf/usermodel/TestHSSFSheet.java | 81 ++++++++++++++++++ .../poi/hssf/usermodel/TestWorkbook.java | 1 - 5 files changed, 146 insertions(+), 1 deletion(-) create mode 100644 src/testcases/org/apache/poi/hssf/data/SimpleMultiCell.xls diff --git a/src/documentation/xdocs/hssf/quick-guide.xml b/src/documentation/xdocs/hssf/quick-guide.xml index 1743de9e9f..335b78aa8b 100644 --- a/src/documentation/xdocs/hssf/quick-guide.xml +++ b/src/documentation/xdocs/hssf/quick-guide.xml @@ -32,6 +32,7 @@
  • Create user defined data formats.
  • Set print area for a sheet.
  • Set page numbers on the footer of a sheet.
    • +
  • Shift rows.
    • @@ -446,6 +447,23 @@ fileOut.close();
    + + +
    + + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet("row sheet"); + + // Create various cells and rows for spreadsheet. + + // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5) + sheet.shiftRows(5, 10, -5); + + FileOutputStream fileOut = new FileOutputStream("workbook.xls"); + wb.write(fileOut); + fileOut.close(); + +
    diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java index 031fabea9d..a5bb0cede8 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java @@ -829,4 +829,51 @@ public class HSSFSheet public void setMargin(short margin, double size) { getSheet().setMargin(margin, size); } + + /** + * Shifts rows between startRow and endRow n number of rows. + * If you use a negative number, it will shift rows up. + * Code ensures that rows don't wrap around + * + * @param startRow the row to start shifting + * @param endRow the row to end shifting + * @param n the number of rows to shift + */ + public void shiftRows(int startRow, int endRow, int n) { + int s, e, inc; + if (n < 0) { + s = startRow; + e = endRow; + inc = 1; + } else { + s = endRow; + e = startRow; + inc = -1; + } + for (int rowNum = s; rowNum >= startRow && rowNum <= endRow && rowNum >= 0 && rowNum < 65536; rowNum+=inc) { + HSSFRow row = getRow(rowNum); + HSSFRow row2Replace = getRow(rowNum + n); + if (row2Replace == null) + row2Replace = createRow(rowNum + n); + + HSSFCell cell; + for (short col = row2Replace.getFirstCellNum(); col <= row2Replace.getLastCellNum(); col++) { + cell = row2Replace.getCell(col); + if (cell != null) + row2Replace.removeCell(cell); + } + for (short col = row.getFirstCellNum(); col <= row.getLastCellNum(); col++) { + cell = row.getCell(col); + if (cell != null) { + row.removeCell(cell); + CellValueRecordInterface cellRecord = cell.getCellValueRecord(); + cellRecord.setRow(rowNum + n); + row2Replace.createCellFromRecord(cellRecord); + sheet.addValueRecord(rowNum + n, cellRecord); + } + } + } + if (endRow == lastrow || endRow + n > lastrow) lastrow = Math.min(endRow + n, 65535); + if (startRow == firstrow || startRow + n < firstrow) firstrow = Math.max(startRow + n, 0); + } } diff --git a/src/testcases/org/apache/poi/hssf/data/SimpleMultiCell.xls b/src/testcases/org/apache/poi/hssf/data/SimpleMultiCell.xls new file mode 100644 index 0000000000000000000000000000000000000000..3c8c8b0b00df5b20611c80b3283d3f57f6c53ff9 GIT binary patch literal 13824 zcmeHOU1(fI6#nl1BpaJ1n;&DWX}E5p&2C~$6R^e7?p3U`VlkvhBStoxG$k=U9;;U^H1q(Inch240{V^NTLa3IR<(!#0 zb7sz&@6Me&GxzSd-_$M5eX{8yO2RJqv7D_!MSVb6!eU9qAr97g%hTOvE zTIVN~Y{e#gMg8onCuO)Z1g%Gq#srd>!uplA23iorT|lHg($>}1){}_t80zpcL($g2 zy}TD&iprlI7(4h}u~Gz;*qm4D?kR4Ed1-13LWq=Vm9&;q-G&%?frPjcvpvsp*`W>< z3`KY31d#_K^gGevBxt1@QT|x9uwKkAtQ510YQ)inhl`E5Jt(%Pt`(S!$MZ%XLMN|M z-l$jqx?Qibn$Fi;LZe>oKeM*RE?clgE(1a3e|SZ!%VlJ)q+d=ht*)ehJIgKD(!E^p z@a%MP_^VVNEhFzLBafAlSN%(V$`?R5QmAycU_GSIj&6U+|lTydN; z&^tl#vVzm%*x1Zgk<3*VNP0zbCR8BlJAnYll7&SpL;{8q(KeogT=n&!&2N$OZ+r-*(YC(iWDUEH{|1!h9Srn zG+4C|ncQ8aGV#Au=3hZ3J{0P`;uG{h9DpnFc|HZCb;-dI9&#+qqgi_+`LIeGl{Y&+ zmbLN;0Giu@YYm*jDGZnb3i>__dC_fZjD7{9WA>Oxa7;q#6a$_Ms3~Eb;f#)e)7&PnT zg?wfa1~)HPOu~)BFkE#DVQ`&RzK$>ySTRY1fI+|@U=T0}+&luyT#N;EOrc=|YLo@d7+0n5n^#RuNmuC-uX*n## zJ-h7R<-NV^`=z{&vApw_eZKe2sO$(2_1JwmTBLDyZ literal 0 HcmV?d00001 diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java index 5ce12b4618..3bad3aea2a 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java @@ -215,4 +215,85 @@ public class TestHSSFSheet assertEquals(cloned.getRow((short)0).getCell((short)0).getStringCellValue(), "clone_test"); } + /** + * Tests the shiftRows function. Does three different shifts. + * After each shift, writes the workbook to file and reads back to + * check. This ensures that if some changes code that breaks + * writing or what not, they realize it. + * + * Shawn Laubach (slaubach at apache dot org) + */ + public void testShiftRows() throws Exception { + // Read initial file in + String filename = System.getProperty("HSSF.testdata.path"); + filename = filename + "/SimpleMultiCell.xls"; + FileInputStream fin = new FileInputStream(filename); + HSSFWorkbook wb = new HSSFWorkbook(fin); + fin.close(); + HSSFSheet s = wb.getSheetAt(0); + + // Shift the second row down 1 and write to temp file + s.shiftRows(1, 1, 1); + File tempFile = File.createTempFile("shift", "test.xls"); + FileOutputStream fout = new FileOutputStream(tempFile); + wb.write(fout); + fout.close(); + + // Read from temp file and check the number of cells in each + // row (in original file each row was unique) + fin = new FileInputStream(tempFile); + wb = new HSSFWorkbook(fin); + fin.close(); + s = wb.getSheetAt(0); + + assertEquals(s.getRow(0).getPhysicalNumberOfCells(), 1); + assertTrue(s.getRow(1) == null || s.getRow(1).getPhysicalNumberOfCells() == 0); + assertEquals(s.getRow(2).getPhysicalNumberOfCells(), 2); + assertEquals(s.getRow(3).getPhysicalNumberOfCells(), 4); + assertEquals(s.getRow(4).getPhysicalNumberOfCells(), 5); + + // Shift rows 1-3 down 3 in the current one. This tests when + // 1 row is blank. Write to a another temp file + s.shiftRows(0, 2, 3); + tempFile = File.createTempFile("shift", "test.xls"); + fout = new FileOutputStream(tempFile); + wb.write(fout); + fout.close(); + + // Read and ensure things are where they should be + fin = new FileInputStream(tempFile); + wb = new HSSFWorkbook(fin); + fin.close(); + s = wb.getSheetAt(0); + assertTrue(s.getRow(0) == null || s.getRow(0).getPhysicalNumberOfCells() == 0); + assertTrue(s.getRow(1) == null || s.getRow(1).getPhysicalNumberOfCells() == 0); + assertTrue(s.getRow(2) == null || s.getRow(2).getPhysicalNumberOfCells() == 0); + assertEquals(s.getRow(3).getPhysicalNumberOfCells(), 1); + assertTrue(s.getRow(4) == null || s.getRow(4).getPhysicalNumberOfCells() == 0); + assertEquals(s.getRow(5).getPhysicalNumberOfCells(), 2); + + // Read the first file again + fin = new FileInputStream(filename); + wb = new HSSFWorkbook(fin); + fin.close(); + s = wb.getSheetAt(0); + + // Shift rows 3 and 4 up and write to temp file + s.shiftRows(2, 3, -2); + tempFile = File.createTempFile("shift", "test.xls"); + fout = new FileOutputStream(tempFile); + wb.write(fout); + fout.close(); + + // Read file and test + fin = new FileInputStream(tempFile); + wb = new HSSFWorkbook(fin); + fin.close(); + s = wb.getSheetAt(0); + assertEquals(s.getRow(0).getPhysicalNumberOfCells(), 3); + assertEquals(s.getRow(1).getPhysicalNumberOfCells(), 4); + assertTrue(s.getRow(2) == null || s.getRow(2).getPhysicalNumberOfCells() == 0); + assertTrue(s.getRow(3) == null || s.getRow(3).getPhysicalNumberOfCells() == 0); + assertEquals(s.getRow(4).getPhysicalNumberOfCells(), 5); + } } diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestWorkbook.java b/src/testcases/org/apache/poi/hssf/usermodel/TestWorkbook.java index 841a542d0b..845e49b44b 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestWorkbook.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestWorkbook.java @@ -272,7 +272,6 @@ public class TestWorkbook { File file = File.createTempFile("testWriteDataFormat", ".xls"); - System.err.println(file); FileOutputStream out = new FileOutputStream(file); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); -- 2.39.5