DO NOT REPLY [Bug 34023] New: - shiftRows method does not update cell references in formula contained in shifted cells

[bu...@apache.org]

DO NOT REPLY TO THIS EMAIL, BUT PLEASE POST YOUR BUG�
RELATED COMMENTS THROUGH THE WEB INTERFACE AVAILABLE AT
<http://issues.apache.org/bugzilla/show_bug.cgi?id=34023>.
ANY REPLY MADE TO THIS MESSAGE WILL NOT BE COLLECTED AND�
INSERTED IN THE BUG DATABASE.

http://issues.apache.org/bugzilla/show_bug.cgi?id=34023

           Summary: shiftRows method does not update cell references in
                    formula contained in shifted cells
           Product: POI
           Version: 2.5
          Platform: PC
        OS/Version: Windows XP
            Status: NEW
          Severity: normal
          Priority: P2
         Component: HSSF
        AssignedTo: poi-dev@jakarta.apache.org
        ReportedBy: tcat.seq@gmail.com


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

-- 
Configure bugmail: http://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
Mailing List:    http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta POI Project: http://jakarta.apache.org/poi/