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

(-)src/documentation/xdocs/hssf/quick-guide.xml (+18 lines)
Lines 32-37 Link Here
32
                    <li><link href="#DataFormats">Create user defined data formats.</link></li>
32
                    <li><link href="#DataFormats">Create user defined data formats.</link></li>
33
                    <li><link href="#PrintArea">Set print area for a sheet.</link></li>
33
                    <li><link href="#PrintArea">Set print area for a sheet.</link></li>
34
                    <li><link href="#FooterPageNumbers">Set page numbers on the footer of a sheet.</link></li>
34
                    <li><link href="#FooterPageNumbers">Set page numbers on the footer of a sheet.</link></li>
35
                    <li><link href="#ShiftRows">Shift rows.</link></li>
35
                </ul>
36
                </ul>
36
            </section>
37
            </section>
37
            <section title="Features">
38
            <section title="Features">
Lines 446-451 Link Here
446
    fileOut.close();
447
    fileOut.close();
447
                    </source>
448
                    </source>
448
                </section>
449
                </section>
450
451
                <anchor id="ShiftRows"/>
452
                <section title="Shift rows up or down on a sheet">
453
                    <source>
454
    HSSFWorkbook wb = new HSSFWorkbook();
455
    HSSFSheet sheet = wb.createSheet("row sheet");
456
457
    // Create various cells and rows for spreadsheet.
458
459
    // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)
460
    sheet.shiftRows(5, 10, -5);
461
462
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
463
    wb.write(fileOut);
464
    fileOut.close();
465
                    </source>
466
                </section>                
449
            </section>
467
            </section>
450
        </section>
468
        </section>
451
    </body>
469
    </body>
(-)src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (+47 lines)
Lines 829-832 Link Here
829
     public void setMargin(short margin, double size) {
829
     public void setMargin(short margin, double size) {
830
       getSheet().setMargin(margin, size);
830
       getSheet().setMargin(margin, size);
831
      }
831
      }
832
833
    /**
834
     * Shifts rows between startRow and endRow n number of rows.
835
     * If you use a negative number, it will shift rows up.
836
     * Code ensures that rows don't wrap around
837
     *
838
     * @param startRow the row to start shifting
839
     * @param endRow the row to end shifting
840
     * @param n the number of rows to shift
841
     */
842
    public void shiftRows(int startRow, int endRow, int n) {
843
	int s, e, inc;
844
	if (n < 0) {
845
	    s = startRow;
846
	    e = endRow;
847
	    inc = 1;
848
	} else {
849
	    s = endRow;
850
	    e = startRow;
851
	    inc = -1;
852
	}
853
	for (int rowNum = s; rowNum >= startRow && rowNum <= endRow && rowNum >= 0 && rowNum < 65536; rowNum+=inc) {
854
	    HSSFRow row = getRow(rowNum);
855
	    HSSFRow row2Replace = getRow(rowNum + n);
856
	    if (row2Replace == null) 
857
		row2Replace = createRow(rowNum + n);
858
859
	    HSSFCell cell;
860
	    for (short col = row2Replace.getFirstCellNum(); col <= row2Replace.getLastCellNum(); col++) {
861
		cell = row2Replace.getCell(col);
862
		if (cell != null)
863
		    row2Replace.removeCell(cell);
864
	    }
865
	    for (short col = row.getFirstCellNum(); col <= row.getLastCellNum(); col++) {
866
		cell = row.getCell(col);
867
		if (cell != null) {
868
		    row.removeCell(cell);
869
		    CellValueRecordInterface cellRecord = cell.getCellValueRecord();
870
		    cellRecord.setRow(rowNum + n);
871
		    row2Replace.createCellFromRecord(cellRecord);
872
		    sheet.addValueRecord(rowNum + n, cellRecord);
873
		}
874
	    }
875
	}
876
	if (endRow == lastrow || endRow + n > lastrow) lastrow = Math.min(endRow + n, 65535);
877
	if (startRow == firstrow || startRow + n < firstrow) firstrow = Math.max(startRow + n, 0);
878
    }
832
}
879
}
(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java (-16 / +97 lines)
Lines 199-218 Link Here
199
        sheet.removeRow(row);
199
        sheet.removeRow(row);
200
    }
200
    }
201
201
202
    public void testCloneSheet() {
202
    public void testCloneSheet() {
203
        HSSFWorkbook workbook = new HSSFWorkbook();
203
        HSSFWorkbook workbook = new HSSFWorkbook();
204
        HSSFSheet sheet = workbook.createSheet("Test Clone");
204
        HSSFSheet sheet = workbook.createSheet("Test Clone");
205
        HSSFRow row = sheet.createRow((short) 0);
205
        HSSFRow row = sheet.createRow((short) 0);
206
        HSSFCell cell = row.createCell((short) 0);
206
        HSSFCell cell = row.createCell((short) 0);
207
        cell.setCellValue("clone_test"); 
207
        cell.setCellValue("clone_test"); 
208
        HSSFSheet cloned = workbook.cloneSheet(0);
208
        HSSFSheet cloned = workbook.cloneSheet(0);
209
  
209
  
210
        //Check for a good clone
210
        //Check for a good clone
211
        assertEquals(cloned.getRow((short)0).getCell((short)0).getStringCellValue(), "clone_test");
211
        assertEquals(cloned.getRow((short)0).getCell((short)0).getStringCellValue(), "clone_test");
212
        
212
        
213
        //Check that the cells are not somehow linked
213
        //Check that the cells are not somehow linked
214
        cell.setCellValue("Difference Check");
214
        cell.setCellValue("Difference Check");
215
        assertEquals(cloned.getRow((short)0).getCell((short)0).getStringCellValue(), "clone_test");
215
        assertEquals(cloned.getRow((short)0).getCell((short)0).getStringCellValue(), "clone_test");
216
    }
216
    }
217
217
218
    /**
219
     * Tests the shiftRows function.  Does three different shifts.
220
     * After each shift, writes the workbook to file and reads back to
221
     * check.  This ensures that if some changes code that breaks
222
     * writing or what not, they realize it.
223
     *
224
     * Shawn Laubach (slaubach at apache dot org)
225
     */
226
    public void testShiftRows() throws Exception {
227
	// Read initial file in
228
	String filename = System.getProperty("HSSF.testdata.path");
229
        filename = filename + "/SimpleMultiCell.xls";
230
        FileInputStream fin = new FileInputStream(filename);
231
        HSSFWorkbook wb = new HSSFWorkbook(fin);
232
	fin.close();
233
	HSSFSheet s = wb.getSheetAt(0);
234
235
	// Shift the second row down 1 and write to temp file
236
	s.shiftRows(1, 1, 1);
237
	File tempFile = File.createTempFile("shift", "test.xls");
238
        FileOutputStream fout = new FileOutputStream(tempFile);
239
	wb.write(fout);
240
	fout.close();
241
242
	// Read from temp file and check the number of cells in each
243
	// row (in original file each row was unique)
244
	fin = new FileInputStream(tempFile);
245
        wb = new HSSFWorkbook(fin);
246
	fin.close();
247
	s = wb.getSheetAt(0);
248
	
249
	assertEquals(s.getRow(0).getPhysicalNumberOfCells(), 1);
250
	assertTrue(s.getRow(1) == null || s.getRow(1).getPhysicalNumberOfCells() == 0);
251
	assertEquals(s.getRow(2).getPhysicalNumberOfCells(), 2);
252
	assertEquals(s.getRow(3).getPhysicalNumberOfCells(), 4);
253
	assertEquals(s.getRow(4).getPhysicalNumberOfCells(), 5);
254
255
	// Shift rows 1-3 down 3 in the current one.  This tests when
256
	// 1 row is blank.  Write to a another temp file
257
	s.shiftRows(0, 2, 3);
258
	tempFile = File.createTempFile("shift", "test.xls");
259
        fout = new FileOutputStream(tempFile);
260
	wb.write(fout);
261
	fout.close();
262
263
	// Read and ensure things are where they should be
264
	fin = new FileInputStream(tempFile);
265
        wb = new HSSFWorkbook(fin);
266
	fin.close();
267
	s = wb.getSheetAt(0);
268
	assertTrue(s.getRow(0) == null || s.getRow(0).getPhysicalNumberOfCells() == 0);
269
	assertTrue(s.getRow(1) == null || s.getRow(1).getPhysicalNumberOfCells() == 0);
270
	assertTrue(s.getRow(2) == null || s.getRow(2).getPhysicalNumberOfCells() == 0);
271
	assertEquals(s.getRow(3).getPhysicalNumberOfCells(), 1);
272
	assertTrue(s.getRow(4) == null || s.getRow(4).getPhysicalNumberOfCells() == 0);
273
	assertEquals(s.getRow(5).getPhysicalNumberOfCells(), 2);
274
	
275
	// Read the first file again
276
	fin = new FileInputStream(filename);
277
        wb = new HSSFWorkbook(fin);
278
	fin.close();
279
	s = wb.getSheetAt(0);
280
281
	// Shift rows 3 and 4 up and write to temp file
282
	s.shiftRows(2, 3, -2);
283
	tempFile = File.createTempFile("shift", "test.xls");
284
        fout = new FileOutputStream(tempFile);
285
	wb.write(fout);
286
	fout.close();
287
288
	// Read file and test
289
	fin = new FileInputStream(tempFile);
290
        wb = new HSSFWorkbook(fin);
291
	fin.close();
292
	s = wb.getSheetAt(0);
293
	assertEquals(s.getRow(0).getPhysicalNumberOfCells(), 3);
294
	assertEquals(s.getRow(1).getPhysicalNumberOfCells(), 4);
295
	assertTrue(s.getRow(2) == null || s.getRow(2).getPhysicalNumberOfCells() == 0);
296
	assertTrue(s.getRow(3) == null || s.getRow(3).getPhysicalNumberOfCells() == 0);
297
	assertEquals(s.getRow(4).getPhysicalNumberOfCells(), 5);
298
    }
218
}
299
}

Return to bug 13332