You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Shreyaswini <sh...@gmail.com> on 2011/08/16 22:14:11 UTC
problem opening excel 2007 created using SXSSF
hi,
I have used SXSSF to create the excel with 2 sheets in it. For excel with 1
sheet, it opens up properly without any problem. But with two sheets & with
large data (45k+ rows & 100+ columns) i get the following pop up while
opening the excel : "Excel found unreadable content in 'doc1.xlsx'.Do you
want to recover the contents of this workbook? if you trust the source of
this workbook,click Yes." if i click yes, the data is present in the sheets
but all the styles applied to the workbook is lost.
SXSSFWorkbook workbook = new SXSSFWorkbook(DEFAULT_WINDOW_SIZE);
buildExcelDocument(model, workbook, request, response);
// Set the content type.
response.setContentType(getContentType());
// Flush byte array to servlet output stream.
ServletOutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
protected void buildExcelDocument(Map<String, Object> model,
SXSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse
response) throws Exception {
String excelSheetName = CommonUtil.getExcelNameForXssf(entityType,
entityName, Date);
response.reset();
response.setHeader("Content-Disposition", excelSheetName);
createSheet1(model, workbook);
createSheet2(model, workbook);
response.flushBuffer();
}
Please help me to resolve this issue.
Regards,
Shreya
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/problem-opening-excel-2007-created-using-SXSSF-tp4705686p4705686.html
Sent from the POI - User mailing list archive at Nabble.com.
Re: problem opening excel 2007 created using SXSSF
Posted by ricuelo <ri...@gmail.com>.
Hi,
I also was getting a corrupted excel, but i discovered that it was happening
because i was using an Outputstream instead of the FileOutputStream
**What i was doing:
OutputStream out=response.getOutputStream();
**What i Should
FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
For some reasson if we use a SXSSFWorkbook i cannot be open with
OutputStream.
Hope it helps
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/problem-opening-excel-2007-created-using-SXSSF-tp4705693p4876184.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: problem opening excel 2007 created using SXSSF
Posted by ricuelo <ri...@gmail.com>.
Alex
it finally worked out!
i had to put :
response.setHeader("Content-Disposition","attachment;filename=\"whatever.xlsx\"");
instead of
response.setHeader("Content-Disposition","attachment;filename=\"whatever.xlsx\"");
therefore the code you posted works if we put xlsx, just because SXSSF works
with xlsx
regards
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/problem-opening-excel-2007-created-using-SXSSF-tp4705693p4879802.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: problem opening excel 2007 created using SXSSF
Posted by Lokesh Puri <lp...@sapient.com>.
Hi Yegor,
I have done all the steps mentioned forum post but I am still getting the
same "unreadable content" Dialog Box. I am using POI-3.8 and have Excel 2007
installed on my machine. My code works fine and xlsx opens without issues if
I write to FileOutputStream as mentioned in one of the posts earlier while
if I write to response.getOutputStream(), I get "unreadable content" dialog
box. Please find my code snippet below, I am not doing anything fancy just
writing 70k lines to xlsx.
My Controller Code
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition","attachment;filename=" + fileName);
service.createPOCDoc(response.getOutputStream());
My Service Code
public void createPOCDoc(OutputStream output) throws IOException{
Workbook wb = new XSSFWorkbook();
createDataSheet(wb.createSheet("data"), 70000);
wb.write(output);
output.flush();
output.close();
}
private void createDataSheet(Sheet sheet, int max_rows) {
for (int i = 0; i < max_rows; i++) {
Row row = sheet.createRow(i);
Cell cell = row.createCell(0);
cell.setCellValue("row number - " + i);
}
System.out.println("added " + max_rows);
}
Yegor Kozlov-4 wrote
>
> Your servlet must return "application/vnd.ms-excel" for .xls and
> "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
> for .xlsx files. Please ensure you set it correctly.
>
> Yegor
>
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/problem-opening-excel-2007-created-using-SXSSF-tp4705693p4879718.html
>
>
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/problem-opening-excel-2007-created-using-SXSSF-tp4705693p5710150.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: problem opening excel 2007 created using SXSSF
Posted by Yegor Kozlov <ye...@dinom.ru>.
Your servlet must return "application/vnd.ms-excel" for .xls and
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
for .xlsx files. Please ensure you set it correctly.
Yegor
On Fri, Oct 7, 2011 at 3:13 PM, ricuelo <ri...@gmail.com> wrote:
>
>
> Yes i agree, it has nothing to do with SXSSF, it has for more to do with
> response.setContentType() . I am reading in some posts that there can
> problems oepning excel because POI doesn't support for office 2007 .xslx.
>
> So now the topic has changed.
>
> thank u m8!
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/problem-opening-excel-2007-created-using-SXSSF-tp4705693p4879718.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: problem opening excel 2007 created using SXSSF
Posted by ricuelo <ri...@gmail.com>.
Yes i agree, it has nothing to do with SXSSF, it has for more to do with
response.setContentType() . I am reading in some posts that there can
problems oepning excel because POI doesn't support for office 2007 .xslx.
So now the topic has changed.
thank u m8!
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/problem-opening-excel-2007-created-using-SXSSF-tp4705693p4879718.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: problem opening excel 2007 created using SXSSF
Posted by Alex Geller <ag...@4js.com>.
Hi,
just to be clear. This means that the problem has nothing to do with the
SXSSF implementation. Even if you had returned a file you had previously
created with MS-Excel it probably would have failed. The problem lies in the
format of the file. For reasons I don't know, Excel can apparently open
"xls", but not "xlsx" documents directly returned in an HTTP request. The
problem may be in your servlet code, the browser or in Excel. The
documentation for the ServletResponse.getOutputStream() states that the data
is not encoded ("The servlet container does not encode the binary data"). I
don't know if this means that you need to encode the data but the problem is
likely in this area. How about using "wget" to look at the result of the
request?
Regards,
Alex
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/problem-opening-excel-2007-created-using-SXSSF-tp4705693p4879626.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: problem opening excel 2007 created using SXSSF
Posted by ricuelo <ri...@gmail.com>.
Hi!
First at all, thank you very much!
It didn´t work, I've ran the code as you indicate, and as well as it was
heappenning to me, by the time trying to use the writer method of the
outputstream linked to the HttpServletResponse, i get an excel corrupted.
However the excel saved to disk can be opened correctly if we open it buy
ourselves.
It didn´t happen with HSSF, doing:
OutputStream out=response.getOutputStream();
out.write(FileInputStream) or out.write(workbook)
taht used to work.
but with SXSSF, the only thing posible i managed to do is saving the file to
disk.
I wish it could be opened directly. Anyways i thing i will save it as ia ma
doing now, and after that i will open it as possible.
again, thank you for you interest ;)
Ricuelo
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/problem-opening-excel-2007-created-using-SXSSF-tp4705693p4879570.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: problem opening excel 2007 created using SXSSF
Posted by Alex Geller <ag...@4js.com>.
Hi,
what happens if you return the contents of the file (which is OK as you say)
directly in the response as in the following code?
protected void service(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment;filename=\"whatever.xls\"");
OutputStream out=response.getOutputStream();
Workbook wb = new SXSSFWorkbook(-1);
Sheet sh = wb.createSheet();
for(int rownum = 0; rownum < 10000; rownum++){
Row row = sh.createRow(rownum);
for(int cellnum = 0; cellnum < 10; cellnum++){
Cell cell = row.createCell(cellnum);
cell.setCellValue("dummy text");
}
if(rownum % 100 == 0) {
((SXSSFSheet)sh).flushRows(100);
}
}
FileOutputStream fout = new
FileOutputStream("c:/whatever.xlsx");
wb.write(fout);
fout.close();
//copy workbook file data to out
FileInputStream fin=new FileInputStream("c:/whatever.xlsx")
byte[] chunk = new byte[1024];
int count;
while ((count = fin.read(chunk)) >=0 ) {
out.write(chunk,0,count);
}
out.close();
}
Can you test this? If this works then there may be a problem in SXSSF but I
suspect that this isn't going to work either.
Regards,
Alex
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/problem-opening-excel-2007-created-using-SXSSF-tp4705693p4879149.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: problem opening excel 2007 created using SXSSF
Posted by ricuelo <ri...@gmail.com>.
Hi Yegor!
I' ve trought make working out this method (wich is similar to one of the
given examples we all know),
but the slightly diference is that i use OutputStream instead of
FileInputStream, and that's what make the incoming excel to be corrupted.
this is the worng code:
protected void service(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment;filename=\"whatever.xls\"");
OutputStream out=response.getOutputStream();
Workbook wb = new SXSSFWorkbook(-1);
Sheet sh = wb.createSheet();
for(int rownum = 0; rownum < 10000; rownum++){
Row row = sh.createRow(rownum);
for(int cellnum = 0; cellnum < 10; cellnum++){
Cell cell = row.createCell(cellnum);
cell.setCellValue("dummy text");
}
if(rownum % 100 == 0) {
((SXSSFSheet)sh).flushRows(100);
}
}
wb.write(out);
out.close();
}
and this is the right code , these are the changes that i did in order to
work it out. And it worked out! :)
protected void service(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment;filename=\"whatever.xls\"");
Workbook wb = new SXSSFWorkbook(-1);
Sheet sh = wb.createSheet();
for(int rownum = 0; rownum < 10000; rownum++){
Row row = sh.createRow(rownum);
for(int cellnum = 0; cellnum < 10; cellnum++){
Cell cell = row.createCell(cellnum);
cell.setCellValue("dummy text");
}
if(rownum % 100 == 0) {
((SXSSFSheet)sh).flushRows(100);
}
}
FileOutputStream out = new FileOutputStream("c:/whatever.xlsx");
wb.write(out);
out.close();
}
that means that with SXSSF we cannot work with outputstream and response to
show the excel. The problem is that in this way the file is saved to disk.
then.
My final is question:
Should i try to read this xlsx after saving? or try to make work the
outputStream with response.
moreover, is there some way to read the xlsx i've saved to disk
Thanks in advance ;)
Ricuelo
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/problem-opening-excel-2007-created-using-SXSSF-tp4705693p4876799.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: problem opening excel 2007 created using SXSSF
Posted by Yegor Kozlov <ye...@dinom.ru>.
it is hard ti tell what is wrong without a sample code. Can you write
a small isolated program that demonstrates the problem? Please post
the sample code so that we can debug and help you.
Yegor
On Sat, Sep 3, 2011 at 12:15 PM, madhup1727 <ma...@gmail.com> wrote:
> Shreyashwini/Yegor.
>
> I am facing the same problem and looks like the styles get corrupted for
> some reason.
> Total number of rows in excel is approx. 44800 rows.
> POI version 3.8-beta4-20110826
> Error message is
> *Repaired Part: /xl/styles.xml part with XML error. (Styles) Load error.
> Line 2, column 11471400.
> Repaired Part: /xl/worksheets/sheet1.xml part with XML error. Load error.
> Line 80222, column 35.
> Removed Feature: Format from /xl/styles.xml part (Styles)
> Removed Feature: Font from /xl/styles.xml part (Styles)
> Removed Records: Cell information from /xl/worksheets/sheet1.xml part
> Repaired Records: Cell information from /xl/worksheets/sheet1.xml part*
>
> Were you able to resolve this issue ? If yes what was the resolution ?
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/problem-opening-excel-2007-created-using-SXSSF-tp4705693p4765026.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: problem opening excel 2007 created using SXSSF
Posted by madhup1727 <ma...@gmail.com>.
Shreyashwini/Yegor.
I am facing the same problem and looks like the styles get corrupted for
some reason.
Total number of rows in excel is approx. 44800 rows.
POI version 3.8-beta4-20110826
Error message is
*Repaired Part: /xl/styles.xml part with XML error. (Styles) Load error.
Line 2, column 11471400.
Repaired Part: /xl/worksheets/sheet1.xml part with XML error. Load error.
Line 80222, column 35.
Removed Feature: Format from /xl/styles.xml part (Styles)
Removed Feature: Font from /xl/styles.xml part (Styles)
Removed Records: Cell information from /xl/worksheets/sheet1.xml part
Repaired Records: Cell information from /xl/worksheets/sheet1.xml part*
Were you able to resolve this issue ? If yes what was the resolution ?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/problem-opening-excel-2007-created-using-SXSSF-tp4705693p4765026.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: problem opening excel 2007 created using SXSSF
Posted by Shreyaswini <sh...@gmail.com>.
Hi,
i am currently using the latest jar of the version 3.8-beta4-20110817 from
the trunk.
I get the following message in the pop up when i click on "Yes" of the
error.
Repaired Part: /xl/worksheets/sheet2.xml part with XML error. Load error.
Line 76442, column 193.
Removed Feature: Format from /xl/styles.xml part (Styles)
Removed Records: Cell information from /xl/worksheets/sheet2.xml part
Repaired Records: Cell information from /xl/worksheets/sheet1.xml part
PFA the code & the sheets: The workbook with 1 sheet is working fine. The
workbook with 2 sheets is giving issues only for large number of rows. It
works fine for 2000-4000 rows.
http://apache-poi.1045710.n5.nabble.com/file/n4709949/Files_to_be_sent.zip
Files_to_be_sent.zip
http://apache-poi.1045710.n5.nabble.com/file/n4709949/Desktop.zip
Desktop.zip
Please help me solve this issue.
Thanks,
Shreya
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/problem-opening-excel-2007-created-using-SXSSF-tp4705693p4709949.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: problem opening excel 2007 created using SXSSF
Posted by Shreyaswini <sh...@gmail.com>.
I have used poi-3.8-beta4-20110811.jar from the trunk. Should i take the
latest after this version?
Regards,
Shreya
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/problem-opening-excel-2007-created-using-SXSSF-tp4705693p4708188.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: problem opening excel 2007 created using SXSSF
Posted by Yegor Kozlov <ye...@dinom.ru>.
Which version of POI? Please try with the latest build from trunk,
there have been updates to SXSSF lately.
If the problem is still there, we need more info to debug it:
1. post full code that we can use to reproduce the problem.
2. attach two files: one generated by SXSSF and the other 'fixed' by Excel.
P.S. please don't cross-post to both poi-user and poi-dev. poi-user is
the right place to ask questions. When the problem is detected and a
Bugzilla ticker is created, you can track further progress in the
poi-dev list.
Yegor
On Wed, Aug 17, 2011 at 12:14 AM, Shreyaswini <sh...@gmail.com> wrote:
> hi,
>
> I have used SXSSF to create the excel with 2 sheets in it. For excel with 1
> sheet, it opens up properly without any problem. But with two sheets & with
> large data (45k+ rows & 100+ columns) i get the following pop up while
> opening the excel : "Excel found unreadable content in 'doc1.xlsx'.Do you
> want to recover the contents of this workbook? if you trust the source of
> this workbook,click Yes." if i click yes, the data is present in the sheets
> but all the styles applied to the workbook is lost.
>
> SXSSFWorkbook workbook = new SXSSFWorkbook(DEFAULT_WINDOW_SIZE);
> buildExcelDocument(model, workbook, request, response);
> // Set the content type.
> response.setContentType(getContentType());
> // Flush byte array to servlet output stream.
> ServletOutputStream out = response.getOutputStream();
> workbook.write(out);
> out.flush();
>
>
> protected void buildExcelDocument(Map<String, Object> model,
> SXSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse
> response) throws Exception {
>
> String excelSheetName = CommonUtil.getExcelNameForXssf(entityType,
> entityName, Date);
> response.reset();
> response.setHeader("Content-Disposition", excelSheetName);
>
> createSheet1(model, workbook);
>
> createSheet2(model, workbook);
>
> response.flushBuffer();
> }
>
>
> Please help me to resolve this issue.
>
> Regards,
> Shreya
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/problem-opening-excel-2007-created-using-SXSSF-tp4705686p4705686.html
> Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org