You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by madhup1727 <ma...@gmail.com> on 2011/09/03 10:15:19 UTC
Re: problem opening excel 2007 created using SXSSF
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 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