You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Nidhi Tuli <nt...@paymentone.com> on 2006/05/15 22:10:43 UTC
Formula trouble -- URGENT Pls help
I am generating an excel sheet for reporting purposes. In this Excel
some of the cells will contain formulas and their value should depend on
the formulas.
I successfully generated cells with formula and their values were
correct when I opened excel for viewing. My trouble starts here. When I
open excel sheet it shows me correct values but when I try to close it ,
Excel prompts me to save the file. This is not acceptable according to
our requirements. So is there a way to get ride of this message and
still keep the formulas in my excel sheet cells?
Looking at this problem I am trying different approaches. I tried
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
evaluator.setCurrentRow(row);
evaluator.evaluateInCell(cell)
thinking that it will evaluate the value of the formula before opening
and the file won't be dirty when it is open.
The above code started giving me RuntimeException. So I had to introduce
some changes
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
evaluator.setCurrentRow(row);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
evaluator.evaluateInCell(cell)
But this change will overwrite the formula and my cells only are numeric
static cells.
Is there a way to get ride of "save" message and still keep the formulas
in my excel sheet cells?
Any pointer or help is greatly appreciated.
Thanks
Nidhi
RE: HSSF has not 'HSSFRowllStyle' to set row style?How to set row sytle?thanks
Posted by Birendar Waldiya <bw...@cisco.com>.
Hi Gurus ,
Can any one give me some pointer , that how POI decides a particular field
is a date field or
A number ??/
Because I am facing a problem of following
While reading a excel file
I have a column in which I give date - initialy it reads as date no issues
now I try to change the date format to some say from say dd-mon-yyyy to
other format dd/mm/yyyy & save it , now this never reads this date as date
but number
Unless I delete the column and re- enter the date
Needed pointers urgently .
Thanks
Regrads
Birendar S Waldiya
---------------------------------------------------------------------
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/
HSSF has not 'HSSFRowllStyle' to set row style?How to set row sytle?thanks
Posted by LiuGuoMing <gm...@sytdc.com>.
Hi,all
HSSFCellStyle style=wb.createCellStyle();
HSSF has not 'HSSFRowllStyle' to set row style?How to set row sytle?thanks
---------------------------------------------------------------------
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/
RE: 'java.lang.OutOfMemoryError: Java heap space'
Posted by Su...@external.mckinsey.com.
Liu,
It would be good to know what your JVM memory settings are
currently. Xmin and XMax settings can be tweaked to increase the memory
available.. try changing that to at least 512MB Max if it is less..
I had the same problem.. only that I was creating excelfiles a bit more
bulkier than yours... I did two things to resolve it.. a. increase the JVM
memory and b. redesign it to reduce the data in excelfiles.
Regards,
Sumit
"Birendar Waldiya" <bw...@cisco.com>
05/16/2006 12:13 AM
Please respond to
"POI Users List" <po...@jakarta.apache.org>
To
"'POI Users List'" <po...@jakarta.apache.org>
cc
Subject
RE: 'java.lang.OutOfMemoryError: Java heap space'
I think this is the problem with POI , ie the large memory maps are beeing
created by POI api
I think you need to limit the size of your documnt .
-----Original Message-----
From: LiuGuoMing [mailto:gmliu@sytdc.com]
Sent: Tuesday, May 16, 2006 7:54 AM
To: POI Users List
Subject: 'java.lang.OutOfMemoryError: Java heap space'
Hi,ALL
I'm currently using POI to generate some fairly large Excel files (30,000+
rows), using the org.apache.poi.hssf.usermodel package.
it occur 'java.lang.OutOfMemoryError: Java heap space' when run it.
my code:
strSQL = "select * from mydatabase";
QueryData qryData = dbcmd.selQuery(strSQL); // note: qryData.size()
>2,0000
if (qryData.size() > 0)
for (int i = 0;i < qryData.size();i++){
row=sheet.createRow((short)currRow);
cell = row.createCell((short)0);
cell.setCellValue(qryData.getRow(i).get("ID"));
cell = row.createCell((short)1);
cell.setCellValue(qryData.getRow(i).get("NAME"));
cell = row.createCell((short)2);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(qryData.getRow(i).get("ADDRESS"));
cell = row.createCell((short)3);
cell.setCellValue(qryData.getRow(i).get("TEL"));
cell = row.createCell((short)4);
cell.setCellValue(qryData.getRow(i).get("ORDERBY"));
cell = row.createCell((short)5);
cell.setCellValue(qryData.getRow(i).get("LINKID"));
cell = row.createCell((short)6);
cell.setCellValue(qryData.getRow(i).get("CSTATUS"));
cell = row.createCell((short)7);
cell.setCellValue(qryData.getRow(i).get("NTATUS"));
cell = row.createCell((short)8);
cell.setCellValue(qryData.getRow(i).get("FLG"));
System.out.println(currRow);
currRow=currRow+1;
}
}
---------------------------------------------------------------------
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/
---------------------------------------------------------------------
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/
+=========================================================+
This message may contain confidential and/or privileged
information. If you are not the addressee or authorized to
receive this for the addressee, you must not use, copy,
disclose or take any action based on this message or any
information herein. If you have received this message in
error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation.
+=========================================================+
RE: 'java.lang.OutOfMemoryError: Java heap space'
Posted by Birendar Waldiya <bw...@cisco.com>.
I think this is the problem with POI , ie the large memory maps are beeing
created by POI api
I think you need to limit the size of your documnt .
-----Original Message-----
From: LiuGuoMing [mailto:gmliu@sytdc.com]
Sent: Tuesday, May 16, 2006 7:54 AM
To: POI Users List
Subject: 'java.lang.OutOfMemoryError: Java heap space'
Hi,ALL
I'm currently using POI to generate some fairly large Excel files (30,000+
rows), using the org.apache.poi.hssf.usermodel package.
it occur 'java.lang.OutOfMemoryError: Java heap space' when run it.
my code:
strSQL = "select * from mydatabase";
QueryData qryData = dbcmd.selQuery(strSQL); // note: qryData.size() >2,0000
if (qryData.size() > 0)
for (int i = 0;i < qryData.size();i++){ row=sheet.createRow((short)currRow);
cell = row.createCell((short)0);
cell.setCellValue(qryData.getRow(i).get("ID"));
cell = row.createCell((short)1);
cell.setCellValue(qryData.getRow(i).get("NAME"));
cell = row.createCell((short)2);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(qryData.getRow(i).get("ADDRESS"));
cell = row.createCell((short)3);
cell.setCellValue(qryData.getRow(i).get("TEL"));
cell = row.createCell((short)4);
cell.setCellValue(qryData.getRow(i).get("ORDERBY"));
cell = row.createCell((short)5);
cell.setCellValue(qryData.getRow(i).get("LINKID"));
cell = row.createCell((short)6);
cell.setCellValue(qryData.getRow(i).get("CSTATUS"));
cell = row.createCell((short)7);
cell.setCellValue(qryData.getRow(i).get("NTATUS"));
cell = row.createCell((short)8);
cell.setCellValue(qryData.getRow(i).get("FLG"));
System.out.println(currRow);
currRow=currRow+1;
}
}
---------------------------------------------------------------------
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/
---------------------------------------------------------------------
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/
'java.lang.OutOfMemoryError: Java heap space'
Posted by LiuGuoMing <gm...@sytdc.com>.
Hi,ALL
I'm currently using POI to generate some fairly large Excel files
(30,000+ rows), using the org.apache.poi.hssf.usermodel package.
it occur 'java.lang.OutOfMemoryError: Java heap space' when run it.
my code:
strSQL = "select * from mydatabase";
QueryData qryData = dbcmd.selQuery(strSQL);
// note: qryData.size() >2,0000
if (qryData.size() > 0)
for (int i = 0;i < qryData.size();i++){
row=sheet.createRow((short)currRow);
cell = row.createCell((short)0);
cell.setCellValue(qryData.getRow(i).get("ID"));
cell = row.createCell((short)1);
cell.setCellValue(qryData.getRow(i).get("NAME"));
cell = row.createCell((short)2);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(qryData.getRow(i).get("ADDRESS"));
cell = row.createCell((short)3);
cell.setCellValue(qryData.getRow(i).get("TEL"));
cell = row.createCell((short)4);
cell.setCellValue(qryData.getRow(i).get("ORDERBY"));
cell = row.createCell((short)5);
cell.setCellValue(qryData.getRow(i).get("LINKID"));
cell = row.createCell((short)6);
cell.setCellValue(qryData.getRow(i).get("CSTATUS"));
cell = row.createCell((short)7);
cell.setCellValue(qryData.getRow(i).get("NTATUS"));
cell = row.createCell((short)8);
cell.setCellValue(qryData.getRow(i).get("FLG"));
System.out.println(currRow);
currRow=currRow+1;
}
}
---------------------------------------------------------------------
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/
Re: Formula trouble -- URGENT Pls help
Posted by Shinya Koizumi <sh...@smartestdesign.com>.
I am not familiar with POI, but can explicitly set the
saved flag to true before closing the document?
The Micorosft Office object gives me an option to change the status of
current
document/sheet. However, I am not sure if this is opened to poi.
I think MS Excel is looking at this flag to see if any modification occurs
for the current sheet and pops up the save dialog.
Hope it helps.
Shinya
----- Original Message -----
From: "Nidhi Tuli" <nt...@paymentone.com>
To: <po...@jakarta.apache.org>
Sent: Monday, May 15, 2006 1:10 PM
Subject: Formula trouble -- URGENT Pls help
I am generating an excel sheet for reporting purposes. In this Excel
some of the cells will contain formulas and their value should depend on
the formulas.
I successfully generated cells with formula and their values were
correct when I opened excel for viewing. My trouble starts here. When I
open excel sheet it shows me correct values but when I try to close it ,
Excel prompts me to save the file. This is not acceptable according to
our requirements. So is there a way to get ride of this message and
still keep the formulas in my excel sheet cells?
Looking at this problem I am trying different approaches. I tried
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
evaluator.setCurrentRow(row);
evaluator.evaluateInCell(cell)
thinking that it will evaluate the value of the formula before opening
and the file won't be dirty when it is open.
The above code started giving me RuntimeException. So I had to introduce
some changes
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
evaluator.setCurrentRow(row);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
evaluator.evaluateInCell(cell)
But this change will overwrite the formula and my cells only are numeric
static cells.
Is there a way to get ride of "save" message and still keep the formulas
in my excel sheet cells?
Any pointer or help is greatly appreciated.
Thanks
Nidhi
---------------------------------------------------------------------
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/
RE: Launch Excel and load memory resident HSSFWorkbook
Posted by Su...@external.mckinsey.com.
ok, that a bit tricky thing !
but once your excel is created, you can still open it by using
Runtime.getRunTime().exec() method.... however, you will have to store the
file first on the users local machine (which you said is not elegant
because of cleanup issues).
http://java.sun.com/j2se/1.4.2/docs/api/java/lang/Runtime.html
I am assuming that in the exec method if you just pass "excel <file
path>", it should equally apply to all versions of Ms-Excel... but that's
just a guess work, you might want to test it first..
"Andrew Shumway" <An...@issinc.com>
05/15/2006 05:36 PM
Please respond to
"POI Users List" <po...@jakarta.apache.org>
To
"'POI Users List'" <po...@jakarta.apache.org>
cc
Subject
RE: Launch Excel and load memory resident HSSFWorkbook
Steaming the HSSFWorkbook out to the web client after changing the mime
type
works for servlet type applications.
It's the Swing client that I'm having difficulty with. Sorry for not
mentioning that in the original post.
--andrew shumway
-----Original Message-----
From: Sumit_Machwe@external.mckinsey.com
[mailto:Sumit_Machwe@external.mckinsey.com]
Sent: Monday, May 15, 2006 3:26 PM
To: POI Users List
Subject: Re: Launch Excel and load memory resident HSSFWorkbook
Andrew,
Following code snippet might be helpful... what we are doing here
is that we are streaming the BLOB object from the database and setting it
in the http response. In your case, you can simply use the HSSFWorkBook's
"write" method along with the POIFSFileSystem to do the streaming.
http://jakarta.apache.org/poi/apidocs/org/apache/poi/hssf/usermodel/HSSFWork
book.html#write(java.io.OutputStream)
remember that in each of these cases, the end user will see a window's
dialogbox asking the user to either save / open / cancel / help options.
If the user opts for "open" then Ms-Excel will be launched and your excel
opened.
{
String mimeType = "";
mimeType = "Application/vnd.ms-excel";
response.setHeader("Content-Disposition","attachment;filename="+fileName+".x
ls");
response.setContentType(mimeType);
servOut = response.getOutputStream();
InputStream in = dataBlob.getBinaryStream();
long len = dataBlob.length();
writeBuffer = dataBlob.getBytes(1,(int)len);
int numRead = 0;
int bufLength = in.read(readBuffer);
while(bufLength != -1)
{
System.arraycopy(readBuffer, 0, writeBuffer, numRead,
bufLength);
servOut.write(writeBuffer, numRead, bufLength);
numRead += bufLength ;
bufLength = in.read(readBuffer);
}
in.close();
servOut.flush();
servOut.close();
}
I hope this is what you are looking for...
Regards,
Sumit Machwe
"Andrew Shumway" <An...@issinc.com>
05/15/2006 04:25 PM
Please respond to
"POI Users List" <po...@jakarta.apache.org>
To
"'POI Users List'" <po...@jakarta.apache.org>
cc
Subject
Launch Excel and load memory resident HSSFWorkbook
Hello
I have an HSSFWorkbook object that is created within my application. I
now
want to launch Excel and display this Workbook so that the user can view,
update and possibly save.
I've been searching thru the FAQ and JavaDocs but I don't see a hook for
this. If I have to I can create a temp file but I'd rather not as it leads
to cleanup issues. Even in this case though I'm not sure how to launch
excel on the client given that different versions install differently etc.
Any ideas?
--andrew shumway
---------------------------------------------------------------------
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/
+=========================================================+
This message may contain confidential and/or privileged
information. If you are not the addressee or authorized to
receive this for the addressee, you must not use, copy,
disclose or take any action based on this message or any
information herein. If you have received this message in
error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation.
+=========================================================+
---------------------------------------------------------------------
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/
+=========================================================+
This message may contain confidential and/or privileged
information. If you are not the addressee or authorized to
receive this for the addressee, you must not use, copy,
disclose or take any action based on this message or any
information herein. If you have received this message in
error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation.
+=========================================================+
RE: Launch Excel and load memory resident HSSFWorkbook
Posted by Andrew Shumway <An...@issinc.com>.
Steaming the HSSFWorkbook out to the web client after changing the mime type
works for servlet type applications.
It's the Swing client that I'm having difficulty with. Sorry for not
mentioning that in the original post.
--andrew shumway
-----Original Message-----
From: Sumit_Machwe@external.mckinsey.com
[mailto:Sumit_Machwe@external.mckinsey.com]
Sent: Monday, May 15, 2006 3:26 PM
To: POI Users List
Subject: Re: Launch Excel and load memory resident HSSFWorkbook
Andrew,
Following code snippet might be helpful... what we are doing here
is that we are streaming the BLOB object from the database and setting it
in the http response. In your case, you can simply use the HSSFWorkBook's
"write" method along with the POIFSFileSystem to do the streaming.
http://jakarta.apache.org/poi/apidocs/org/apache/poi/hssf/usermodel/HSSFWork
book.html#write(java.io.OutputStream)
remember that in each of these cases, the end user will see a window's
dialogbox asking the user to either save / open / cancel / help options.
If the user opts for "open" then Ms-Excel will be launched and your excel
opened.
{
String mimeType = "";
mimeType = "Application/vnd.ms-excel";
response.setHeader("Content-Disposition","attachment;filename="+fileName+".x
ls");
response.setContentType(mimeType);
servOut = response.getOutputStream();
InputStream in = dataBlob.getBinaryStream();
long len = dataBlob.length();
writeBuffer = dataBlob.getBytes(1,(int)len);
int numRead = 0;
int bufLength = in.read(readBuffer);
while(bufLength != -1)
{
System.arraycopy(readBuffer, 0, writeBuffer, numRead,
bufLength);
servOut.write(writeBuffer, numRead, bufLength);
numRead += bufLength ;
bufLength = in.read(readBuffer);
}
in.close();
servOut.flush();
servOut.close();
}
I hope this is what you are looking for...
Regards,
Sumit Machwe
"Andrew Shumway" <An...@issinc.com>
05/15/2006 04:25 PM
Please respond to
"POI Users List" <po...@jakarta.apache.org>
To
"'POI Users List'" <po...@jakarta.apache.org>
cc
Subject
Launch Excel and load memory resident HSSFWorkbook
Hello
I have an HSSFWorkbook object that is created within my application. I
now
want to launch Excel and display this Workbook so that the user can view,
update and possibly save.
I've been searching thru the FAQ and JavaDocs but I don't see a hook for
this. If I have to I can create a temp file but I'd rather not as it leads
to cleanup issues. Even in this case though I'm not sure how to launch
excel on the client given that different versions install differently etc.
Any ideas?
--andrew shumway
---------------------------------------------------------------------
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/
+=========================================================+
This message may contain confidential and/or privileged
information. If you are not the addressee or authorized to
receive this for the addressee, you must not use, copy,
disclose or take any action based on this message or any
information herein. If you have received this message in
error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation.
+=========================================================+
---------------------------------------------------------------------
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/
Re: Launch Excel and load memory resident HSSFWorkbook
Posted by Su...@external.mckinsey.com.
Andrew,
Following code snippet might be helpful... what we are doing here
is that we are streaming the BLOB object from the database and setting it
in the http response. In your case, you can simply use the HSSFWorkBook's
"write" method along with the POIFSFileSystem to do the streaming.
http://jakarta.apache.org/poi/apidocs/org/apache/poi/hssf/usermodel/HSSFWorkbook.html#write(java.io.OutputStream)
remember that in each of these cases, the end user will see a window's
dialogbox asking the user to either save / open / cancel / help options.
If the user opts for "open" then Ms-Excel will be launched and your excel
opened.
{
String mimeType = "";
mimeType = "Application/vnd.ms-excel";
response.setHeader("Content-Disposition","attachment;filename="+fileName+".xls");
response.setContentType(mimeType);
servOut = response.getOutputStream();
InputStream in = dataBlob.getBinaryStream();
long len = dataBlob.length();
writeBuffer = dataBlob.getBytes(1,(int)len);
int numRead = 0;
int bufLength = in.read(readBuffer);
while(bufLength != -1)
{
System.arraycopy(readBuffer, 0, writeBuffer, numRead,
bufLength);
servOut.write(writeBuffer, numRead, bufLength);
numRead += bufLength ;
bufLength = in.read(readBuffer);
}
in.close();
servOut.flush();
servOut.close();
}
I hope this is what you are looking for...
Regards,
Sumit Machwe
"Andrew Shumway" <An...@issinc.com>
05/15/2006 04:25 PM
Please respond to
"POI Users List" <po...@jakarta.apache.org>
To
"'POI Users List'" <po...@jakarta.apache.org>
cc
Subject
Launch Excel and load memory resident HSSFWorkbook
Hello
I have an HSSFWorkbook object that is created within my application. I
now
want to launch Excel and display this Workbook so that the user can view,
update and possibly save.
I've been searching thru the FAQ and JavaDocs but I don't see a hook for
this. If I have to I can create a temp file but I'd rather not as it leads
to cleanup issues. Even in this case though I'm not sure how to launch
excel on the client given that different versions install differently etc.
Any ideas?
--andrew shumway
---------------------------------------------------------------------
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/
+=========================================================+
This message may contain confidential and/or privileged
information. If you are not the addressee or authorized to
receive this for the addressee, you must not use, copy,
disclose or take any action based on this message or any
information herein. If you have received this message in
error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation.
+=========================================================+
Launch Excel and load memory resident HSSFWorkbook
Posted by Andrew Shumway <An...@issinc.com>.
Hello
I have an HSSFWorkbook object that is created within my application. I now
want to launch Excel and display this Workbook so that the user can view,
update and possibly save.
I've been searching thru the FAQ and JavaDocs but I don't see a hook for
this. If I have to I can create a temp file but I'd rather not as it leads
to cleanup issues. Even in this case though I'm not sure how to launch
excel on the client given that different versions install differently etc.
Any ideas?
--andrew shumway
---------------------------------------------------------------------
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/