Bug 34023

I'm facing an issue with the shiftRows(...) method while trying to insert rows 
into an existing excel
spreadsheet. The following program reads an excel spreadsheet (which has cells 
with formulae in it) from 
the command line, inserts 10 rows and outputs the modified spreadsheet to the 
name specified in the
command line.

The issue I'm having is that if the original row (row that will be shifted 
during the "shiftRows" method)
had a cell that contained a formula that referenced other cells, the cell 
references do not change after 
"shiftRows" has been called. This behavior is different when you manually shift 
rows using MS Excel or
Open Office. When you do it manually, the cell references are updated in the 
formula as the row is being
shifted.

I know the developers of POI don't expect POI to be a replacement to Excel (see 
http://mail-archives.eu.apache.org/mod_mbox/jakarta-poi-user/200406.mbox/%
3c1088257363.889.1.camel@IT-avik.in.itellix.net%3e),
but if there is a shiftRows method I think it should duplicate the 
functionality. Am I missing something here?

I haven't looked at the source code to understand POI since I've just started 
using POI. Lame excuse, I know :)
But if someone could confirm they are seeing the same behavior and whether the 
existing implementation/functionality of
shiftRows is the correct functionality, I'd really appreciate it.

I browsed through the user list archives, but could not find any indication 
that someone had faced this
problem. Maybe I missed something.

Environment:
Windows XP
JDK 1.4.1_01
poi-2.5.1-final-20040804.jar

Tested on:
MS Excel 2002 SP3
Open Office 1.1.4

To run this example:

1) Compile HSSFCreateSheet.java (javac HSSFCreateSheet.java). Make sure you 
have 
   poi-2.5.1-final-20040804.jar in your classpath
2) Create an empty excel file in the same location as the compiled class. Add a 
value 0 to cell B14 and 100 to cell C14. Add the formula =B14/C14 to cell B17. 
Save the excel file Test.xls.
3) Run HSSFCreateSheet as follows: java HSSFCreateSheet Test.xls shiftedTest.xls
4) Open "shiftedTest.xls". And check the formulae in the shifted row. The cell 
references in the 
   formula didn't change during the shift.


import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class HSSFCreateSheet 
{
	private String fileName = null;
	
	private HSSFWorkbook hssfWb = null;
	
	public static void main(String [] args)
	{
		try
		{
			HSSFCreateSheet hssf = new HSSFCreateSheet(args[0]);
			HSSFWorkbook wb = hssf.hssfWb;
			HSSFSheet sheet = wb.getSheetAt(0);

            // insert 10 rows starting at row 9
			sheet.shiftRows(8, sheet.getLastRowNum(), 10, true, 
true);

			FileOutputStream fos = new FileOutputStream(args[1]);
			wb.write(fos);
			
			fos.close();
		}
		catch (Exception e)
		{
			System.out.println("Exception encountered..." + 
e.getMessage());
			e.printStackTrace();
		}
    }

	public HSSFCreateSheet(String inputExcelFileName) throws 
FileNotFoundException, IOException 
	{
		this.fileName = inputExcelFileName;
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream
(fileName));
		hssfWb = new HSSFWorkbook(fs);
	}

}


Thanks much,
RAS