Attachment #3377 for bug #13332

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




                    <li><link href="#DataFormats">Create user defined data formats.</link></li>
                    <li><link href="#PrintArea">Set print area for a sheet.</link></li>
                    <li><link href="#FooterPageNumbers">Set page numbers on the footer of a sheet.</link></li>
                    <li><link href="#ShiftRows">Shift rows.</link></li>
                </ul>
            </section>
            <section title="Features">

    fileOut.close();
                    </source>
                </section>

                <anchor id="ShiftRows"/>
                <section title="Shift rows up or down on a sheet">
                    <source>
    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();
                    </source>
                </section>                
            </section>
        </section>
    </body>




     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);
    }
}




        sheet.removeRow(row);
    }

    public void testCloneSheet() {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Test Clone");
        HSSFRow row = sheet.createRow((short) 0);
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("clone_test"); 
        HSSFSheet cloned = workbook.cloneSheet(0);
  
        //Check for a good clone
        assertEquals(cloned.getRow((short)0).getCell((short)0).getStringCellValue(), "clone_test");
        
        //Check that the cells are not somehow linked
        cell.setCellValue("Difference Check");
        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);
    }
}

Return to bug 13332

Lines 829-832 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (+47 lines)
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	}

Lines 199-218 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java (-16 / +97 lines)
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	}

Lines 32-37 Link Here

(-)src/documentation/xdocs/hssf/quick-guide.xml (+18 lines)
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>