You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Mili Aggarwal, Noida" <mi...@noida.hcltech.com> on 2005/03/16 04:43:27 UTC
RE: shiftRows method does not update cell references in formulae
contained in shifted cells
Hell RS,
I faced the same issue.The formula cell refernces are NOT updated when the
rows are shifted.It was a big headache for a swell and hence I adopted an
alternative algo.
Warm Regards,
Mili Aggarwal
HCL TECHNOLOGIES LTD.
Noida, India.
Tel: +91 (0120) 2516321/328 Extn: 1034
Email: milia@noida.hcltech.com
----------------------------------------------------------------------------
-----------------------------DISCLAIMER-------------------------------------
----------------------------------------------------------------------------
---------
This message and any attachment(s) contained here are information that is
confidential, proprietary to HCL Technologies and its customers. Contents
may be privileged or otherwise protected by law. The information is solely
intended for the individual or the entity it is addressed to. If you are not
the intended recipient of this message, you are not authorized to read,
forward, print, retain, copy or disseminate this message or any part of it.
If you have received this e-mail in error, please notify the sender
immediately by return e-mail and delete it from your computer.
-----Original Message-----
From: R S [mailto:tcat.seq@gmail.com]
Sent: Wednesday, March 16, 2005 1:12 AM
To: poi-user@jakarta.apache.org
Subject: shiftRows method does not update cell references in formulae
contained in shifted cells
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 of Excel
(see
http://mail-archives.eu.apache.org/mod_mbox/jakarta-poi-user/200406.mbox/%3c
1088257363.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) Save the attached excel file in the same location as the compiled class.
3) Run HSSFCreateSheet as follows: java HSSFCreateSheet Test.xls
shiftedTest.xls
4) Open "shiftedExcel.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
Disclaimer:
This message and any attachment(s) contained here are information that is
confidential,proprietary to HCL Technologies and its customers, privileged
or otherwise protected by law.The information is solely intended for the
individual or the entity it is addressed to. If you are not the intended
recipient of this message, you are not authorized to read, forward,
print,retain, copy or disseminate this message or any part of it. If you
have received this e-mail in error, please notify the sender immediately by
return e-mail and delete it from your computer.
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/