You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by ajitw <aj...@yahoo.com> on 2012/02/09 17:31:31 UTC
Using SXSSF to write excel gives an error while opening the file
Hello,
We used the hssf code earlier to write an .xls file. This started with a xlt
template (has validations in columns and headers which we wanted to use) and
outputed an .xls file.
Now we are trying to upgrade to export the .xlsx file. As our data could be
large we are trying to use SXSSFWorkbook with window size as 100.
We start with an existing template which is an .xlsx file. Create a
SXSSFWorkbook from this and then write data to this. After the job
completes, when we try to open the excel file and it gives an error saying
"Excel found unreadable content in ...xlsx. Do you want to recover the
contents of this workbook?.."
When you click Yes for the option box it repairs the excel and opens it but
the existing sheets from the template have data missing.
I wrote a test class to simulate the issue we are having :
I start with a blank workbook as a template, then create a SXXSFWorkbook and
create sheets and add data and the output an xlsx. Get the same error when
opening this.
This is the sample code:
import java.io.*;
import org.apache.poi.openxml4j.opc.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.*;
import org.apache.poi.xssf.usermodel.*;
public class TestWriter
{
public static void main(String args[])
throws Throwable
{
TestWriter wr = new TestWriter();
wr.testSmoke( );
}
public void testSmoke( ) throws Throwable {
InputStream excelInput = null;
File file = new File("C:/Temp/"+FILE_NAME);
excelInput = new FileInputStream( file );
OPCPackage pkg = OPCPackage.open( excelInput );
final XSSFWorkbook workbook = new XSSFWorkbook( pkg );
m_workbook = new SXSSFWorkbook( workbook, 100 );
String[ ] lines = { "Line201", "Line202", "Line203", "Line204",
"Line205", "Line206", "Line207", "Line208", "Line209", "Line210", "Line211",
"Line212", "Line213", "Line214", "Line215", "Line228", "Line229", "Line230",
"Line231", "Line232", "Line233", "Line235", "Line236", "Line237", "Line238"
};
String[ ] items = {
"28527RB","29520","29522","29586","29620#CA","29675","29675PW4","29685","29685#CA","33287","33462","34274","34300","34301","34301PW2#CA","34306","34478PW2#CA","34500","36620","3920","3920E","3920VW5","3925","39520","39520#CA","39521","39578","39580","39590","39590PW2#CA","39591","3975","3979","3980","3982","3982PW1","3984","3984VW5","3994","3994#CA","45220","45221","45280","45284","45285","45287","4535","4595","47623A","49520","49576","49585","522","52393","52400","52400#GV","52400PW4","526","52618","52618PW2","52638","52638#GV","527","528","529","532X","5335","5395","5395VW1#CA","55175CPW3","55187CPW3","55187CPW3#GV","55200CPW3","55206CPW3","55212CPW3","552A","5535","553X","55437PW2","55443PW2","555S","5578","557S","5584","5595","560","563","563VW1","56418","56418#GV","56425","56425PW2","565","566","56650","56650#CA","56650PW2","567","568","570","572","5735","575","5760","580","580#GV","581","590A","592A","592AVW3","593","593A","594#CA","59425","594A","594A#CA","594AVW3","595PW4","596","596#CA","598","612","621","623","632","6320","633","6379","6386","6389","639","6420","643","64450","64450#CA","64452A","6461A","6466","64700","64700#CA","65200","65225","65237A","65237PW3","653","653#GV","6535","6535PW3","6535PW3#CA","6535PW4VW4#CA","6535W","6536","65390","655","65500","65500PW1","65500PW2","6574#CA","6575","6576","6576#CA","6580","6580#CA","661","66212","66225","663","663#GV","66462","665","66520","665A","672","677","681","681A","685","687","72187CPW3VW2","72188CPW3VW2","72200C","72200CPW3VW2","72200CPW3VW2#CA","72212CPW3","72212CPW3VW2","72218CPW3VW2","72487PW2VW7","740","742","744PW4VW1","745A","748S","749","749A","750A","752","752#CA","753A","755#CA","757#CA","758","759","766","78215C","78250AC","78250C","78537","78551","9380","A-JHM318410#CA","A-JHM522610","H414210","H414210#CA","H414235","H414242","H414242#CA","H414249","HH506310","HH814510","HL-64450#CA","HL-A-JHM318448#CA","HL-A-JHM522649","HL-JM718149#CA","HM212010","HM212011","HM212011#CA","HM212044","HM212047","HM212047#CA","HM212049","HM212049#CA","HM218210","HM218238","HM218248","HM218248PW4#CA","HM220110#CA","HM220149","HM220149#CA","HM516410","HM516414AXVW1","HM516442","HM516448","HM516449","HM516449A","HM518410","HM518445","HM617010","HM617049","HM807010","HM807010PW2","HM807040PW3","HM807046PW3","HM807049","HM807049APW3","HM807049PW3","HM813810PW2","HM813811","HM813840PW3#CA","HM813841PW3","HM813844PW3","HM813849PW3","HM911210","JH211710","JH211710PK","JH211749","JH217210","JH217210#CA","JH217249","JH217249#CA","JH217249PK","JH307710","JH307749","JH415610","JH415647","JH415647#CA","JHM318410","JHM318410#CA","JHM318448#CA","JHM522610","JHM522610#GV","JHM522649","JHM720210","JHM720249","JHM720249#CA","JLM714110","JLM714110#CA","JLM714110PK","JLM714149","JLM714149#CA","JM511910","JM511910PK","JM511945","JM511946","JM511946#CA","JM511946PK","JM612910#CA","JM716610","JM716610#CA","JM716649","JM716649VW1#CA","JM716649VW2#CA","JM718110","JM718110#CA","JM718110PK","JM718149","JM718149#CA","JM718149PK","JM719113","JM719113#CA","JM719149#CA","JM719149PK","JM720210","JM720249","JM720249#CA","JM822010","JM822010#CA","JM822049","JM822049PK","L610510","LM613449","LM814810","LM814849","NA56425SWCB125","NA593CB125","XLH414210","XLH414235","XLHM518411PW2","XLHM518411WPW2","XLHM518437PW3"
};
String[ ] columns = { "Column1", "Column2", "Column3", "Column4",
"Column5" };
int rownum = 0;
final int MAX_ROWS = 1048576;
Sheet sh = m_workbook.createSheet( "MyTab" );
System.out.println( "Starting writing XLSX file..." );
// Write header
Row row = sh.createRow( ++rownum );
for( int column = 0; column < columns.length; column++ ) {
Cell cell = row.createCell( column );
cell.setCellValue( columns[ column ] );
}
for( String line : lines ) {
for( String itemA : items ) {
for( String itemB : items ) {
if( ! itemA.equals( itemB ) ) {
row = sh.createRow( ++rownum );
int cellId = 0;
Cell lineCell = row.createCell( cellId++ );
lineCell.setCellValue( line );
Cell itemACell = row.createCell( cellId++ );
itemACell.setCellValue( itemA );
Cell itemBCell = row.createCell( cellId++ );
itemBCell.setCellValue( itemB );
Cell setupCostCell = row.createCell( cellId++ );
setupCostCell.setCellValue( 1.0 );
Cell setupTimeCell = row.createCell( cellId );
setupTimeCell.setCellValue( 1.0 );
if( rownum == MAX_ROWS ) {
sh = m_workbook.createSheet("MyTab"+ m_idx );
m_idx++;
rownum = 0;
row = sh.createRow( rownum++ );
for( int column = 0; column < columns.length;
column++ ) {
Cell cell = row.createCell( column );
cell.setCellValue( columns[ column ] );
}
}
}
}
}
}
FileOutputStream out = new FileOutputStream(
"c:/temp/Output_MyTab_blank.xlsx" );
m_workbook.write(out);
out.close();
System.out.println( "Done!" );
}
public static final String FILE_NAME = "MyTab_blank.xlsx";
private SXSSFWorkbook m_workbook;
private int m_idx = 1;
}
Also attaching the input template referred in the code:
http://apache-poi.1045710.n5.nabble.com/file/n5469995/MyTab_blank.xlsx
MyTab_blank.xlsx
The output file was too large to attach.
Also in the actual code we are using getSheet instead of createSheet, but
while testing found even createSheet gives the same error while opening the
file.
Was wondering if anyone knew about this issue or how to correct it.
Thanks
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Using-SXSSF-to-write-excel-gives-an-error-while-opening-the-file-tp5469995p5469995.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: Using SXSSF to write excel gives an error while opening the file
Posted by wl <l_...@nec.cn>.
Send email to below url. Hope this help:)
user-unsubscribe@poi.apache.org
-----Original Message-----
From: Me Myself and I [mailto:stargate7thsymbol@live.co.uk]
Sent: Friday, February 10, 2012 8:38 AM
To: user@poi.apache.org
Subject: RE: Using SXSSF to write excel gives an error while opening the
file
Can someone send me the url for unsubscribe on this POI email list?
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Using SXSSF to write excel gives an error while opening the file
Posted by Me Myself and I <st...@live.co.uk>.
Can someone send me the url for unsubscribe on this POI email list?
RE: Using SXSSF to write excel gives an error while opening the file
Posted by Matt Rogghe <mr...@blazent.com>.
Try reducing the variable MAX_ROWS by 1. I believe that starting at 0 and incrementing up to that will give you one more row than Excel 2007+ supports.
-----Original Message-----
From: ajitw [mailto:ajit_wadatkar@yahoo.com]
Sent: Thursday, February 09, 2012 11:32 AM
To: user@poi.apache.org
Subject: Using SXSSF to write excel gives an error while opening the file
Hello,
We used the hssf code earlier to write an .xls file. This started with a xlt
template (has validations in columns and headers which we wanted to use) and
outputed an .xls file.
Now we are trying to upgrade to export the .xlsx file. As our data could be
large we are trying to use SXSSFWorkbook with window size as 100.
We start with an existing template which is an .xlsx file. Create a
SXSSFWorkbook from this and then write data to this. After the job
completes, when we try to open the excel file and it gives an error saying
"Excel found unreadable content in ...xlsx. Do you want to recover the
contents of this workbook?.."
When you click Yes for the option box it repairs the excel and opens it but
the existing sheets from the template have data missing.
I wrote a test class to simulate the issue we are having :
I start with a blank workbook as a template, then create a SXXSFWorkbook and
create sheets and add data and the output an xlsx. Get the same error when
opening this.
This is the sample code:
import java.io.*;
import org.apache.poi.openxml4j.opc.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.*;
import org.apache.poi.xssf.usermodel.*;
public class TestWriter
{
public static void main(String args[])
throws Throwable
{
TestWriter wr = new TestWriter();
wr.testSmoke( );
}
public void testSmoke( ) throws Throwable {
InputStream excelInput = null;
File file = new File("C:/Temp/"+FILE_NAME);
excelInput = new FileInputStream( file );
OPCPackage pkg = OPCPackage.open( excelInput );
final XSSFWorkbook workbook = new XSSFWorkbook( pkg );
m_workbook = new SXSSFWorkbook( workbook, 100 );
String[ ] lines = { "Line201", "Line202", "Line203", "Line204",
"Line205", "Line206", "Line207", "Line208", "Line209", "Line210", "Line211",
"Line212", "Line213", "Line214", "Line215", "Line228", "Line229", "Line230",
"Line231", "Line232", "Line233", "Line235", "Line236", "Line237", "Line238"
};
String[ ] items = {
"28527RB","29520","29522","29586","29620#CA","29675","29675PW4","29685","29685#CA","33287","33462","34274","34300","34301","34301PW2#CA","34306","34478PW2#CA","34500","36620","3920","3920E","3920VW5","3925","39520","39520#CA","39521","39578","39580","39590","39590PW2#CA","39591","3975","3979","3980","3982","3982PW1","3984","3984VW5","3994","3994#CA","45220","45221","45280","45284","45285","45287","4535","4595","47623A","49520","49576","49585","522","52393","52400","52400#GV","52400PW4","526","52618","52618PW2","52638","52638#GV","527","528","529","532X","5335","5395","5395VW1#CA","55175CPW3","55187CPW3","55187CPW3#GV","55200CPW3","55206CPW3","55212CPW3","552A","5535","553X","55437PW2","55443PW2","555S","5578","557S","5584","5595","560","563","563VW1","56418","56418#GV","56425","56425PW2","565","566","56650","56650#CA","56650PW2","567","568","570","572","5735","575","5760","580","580#GV","581","590A","592A","592AVW3","593","593A","594#CA","59425","594A","594A#CA","594AVW3","595PW4","596","596#CA","598","612","621","623","632","6320","633","6379","6386","6389","639","6420","643","64450","64450#CA","64452A","6461A","6466","64700","64700#CA","65200","65225","65237A","65237PW3","653","653#GV","6535","6535PW3","6535PW3#CA","6535PW4VW4#CA","6535W","6536","65390","655","65500","65500PW1","65500PW2","6574#CA","6575","6576","6576#CA","6580","6580#CA","661","66212","66225","663","663#GV","66462","665","66520","665A","672","677","681","681A","685","687","72187CPW3VW2","72188CPW3VW2","72200C","72200CPW3VW2","72200CPW3VW2#CA","72212CPW3","72212CPW3VW2","72218CPW3VW2","72487PW2VW7","740","742","744PW4VW1","745A","748S","749","749A","750A","752","752#CA","753A","755#CA","757#CA","758","759","766","78215C","78250AC","78250C","78537","78551","9380","A-JHM318410#CA","A-JHM522610","H414210","H414210#CA","H414235","H414242","H414242#CA","H414249","HH506310","HH814510","HL-64450#CA","HL-A-JHM318448#CA","HL-A-JHM522649","HL-JM718149#CA","HM212010","HM212011","HM212011#CA","HM212044","HM212047","HM212047#CA","HM212049","HM212049#CA","HM218210","HM218238","HM218248","HM218248PW4#CA","HM220110#CA","HM220149","HM220149#CA","HM516410","HM516414AXVW1","HM516442","HM516448","HM516449","HM516449A","HM518410","HM518445","HM617010","HM617049","HM807010","HM807010PW2","HM807040PW3","HM807046PW3","HM807049","HM807049APW3","HM807049PW3","HM813810PW2","HM813811","HM813840PW3#CA","HM813841PW3","HM813844PW3","HM813849PW3","HM911210","JH211710","JH211710PK","JH211749","JH217210","JH217210#CA","JH217249","JH217249#CA","JH217249PK","JH307710","JH307749","JH415610","JH415647","JH415647#CA","JHM318410","JHM318410#CA","JHM318448#CA","JHM522610","JHM522610#GV","JHM522649","JHM720210","JHM720249","JHM720249#CA","JLM714110","JLM714110#CA","JLM714110PK","JLM714149","JLM714149#CA","JM511910","JM511910PK","JM511945","JM511946","JM511946#CA","JM511946PK","JM612910#CA","JM716610","JM716610#CA","JM716649","JM716649VW1#CA","JM716649VW2#CA","JM718110","JM718110#CA","JM718110PK","JM718149","JM718149#CA","JM718149PK","JM719113","JM719113#CA","JM719149#CA","JM719149PK","JM720210","JM720249","JM720249#CA","JM822010","JM822010#CA","JM822049","JM822049PK","L610510","LM613449","LM814810","LM814849","NA56425SWCB125","NA593CB125","XLH414210","XLH414235","XLHM518411PW2","XLHM518411WPW2","XLHM518437PW3"
};
String[ ] columns = { "Column1", "Column2", "Column3", "Column4",
"Column5" };
int rownum = 0;
final int MAX_ROWS = 1048576;
Sheet sh = m_workbook.createSheet( "MyTab" );
System.out.println( "Starting writing XLSX file..." );
// Write header
Row row = sh.createRow( ++rownum );
for( int column = 0; column < columns.length; column++ ) {
Cell cell = row.createCell( column );
cell.setCellValue( columns[ column ] );
}
for( String line : lines ) {
for( String itemA : items ) {
for( String itemB : items ) {
if( ! itemA.equals( itemB ) ) {
row = sh.createRow( ++rownum );
int cellId = 0;
Cell lineCell = row.createCell( cellId++ );
lineCell.setCellValue( line );
Cell itemACell = row.createCell( cellId++ );
itemACell.setCellValue( itemA );
Cell itemBCell = row.createCell( cellId++ );
itemBCell.setCellValue( itemB );
Cell setupCostCell = row.createCell( cellId++ );
setupCostCell.setCellValue( 1.0 );
Cell setupTimeCell = row.createCell( cellId );
setupTimeCell.setCellValue( 1.0 );
if( rownum == MAX_ROWS ) {
sh = m_workbook.createSheet("MyTab"+ m_idx );
m_idx++;
rownum = 0;
row = sh.createRow( rownum++ );
for( int column = 0; column < columns.length;
column++ ) {
Cell cell = row.createCell( column );
cell.setCellValue( columns[ column ] );
}
}
}
}
}
}
FileOutputStream out = new FileOutputStream(
"c:/temp/Output_MyTab_blank.xlsx" );
m_workbook.write(out);
out.close();
System.out.println( "Done!" );
}
public static final String FILE_NAME = "MyTab_blank.xlsx";
private SXSSFWorkbook m_workbook;
private int m_idx = 1;
}
Also attaching the input template referred in the code:
http://apache-poi.1045710.n5.nabble.com/file/n5469995/MyTab_blank.xlsx
MyTab_blank.xlsx
The output file was too large to attach.
Also in the actual code we are using getSheet instead of createSheet, but
while testing found even createSheet gives the same error while opening the
file.
Was wondering if anyone knew about this issue or how to correct it.
Thanks
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Using-SXSSF-to-write-excel-gives-an-error-while-opening-the-file-tp5469995p5469995.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: Using SXSSF to write excel gives an error while opening the file
Posted by Yegor Kozlov <ye...@dinom.ru>.
The problem is that SXSSF does not validate row numbers and lets you
create rows past the limit.
The maximum number of rows in SpreadsheetML is 1048576, the rowIndex
argument in sheet.createRow(int rowIndex) is 0-based which means that
the allowable range is (0...1048575)
Change MAX_ROWS from 1048576 to 1048575 and you will be good.
or better take the limit from the SpreadsheetVersion enum :
int MAX_ROWS = SpreadsheetVersion.EXCEL2007.getLastRowIndex();
I tweaked SXSSF in r1243232 to validate row and column indexes. Now an
attempt to call sheet.createRow(1048576) will throw
IllegalArgumentException, same behavior as in XSSF.
Yegor
On Thu, Feb 9, 2012 at 8:31 PM, ajitw <aj...@yahoo.com> wrote:
> Hello,
> We used the hssf code earlier to write an .xls file. This started with a xlt
> template (has validations in columns and headers which we wanted to use) and
> outputed an .xls file.
>
> Now we are trying to upgrade to export the .xlsx file. As our data could be
> large we are trying to use SXSSFWorkbook with window size as 100.
> We start with an existing template which is an .xlsx file. Create a
> SXSSFWorkbook from this and then write data to this. After the job
> completes, when we try to open the excel file and it gives an error saying
> "Excel found unreadable content in ...xlsx. Do you want to recover the
> contents of this workbook?.."
> When you click Yes for the option box it repairs the excel and opens it but
> the existing sheets from the template have data missing.
>
> I wrote a test class to simulate the issue we are having :
>
> I start with a blank workbook as a template, then create a SXXSFWorkbook and
> create sheets and add data and the output an xlsx. Get the same error when
> opening this.
>
> This is the sample code:
>
>
> import java.io.*;
>
> import org.apache.poi.openxml4j.opc.*;
> import org.apache.poi.ss.usermodel.*;
> import org.apache.poi.xssf.streaming.*;
> import org.apache.poi.xssf.usermodel.*;
>
> public class TestWriter
> {
>
> public static void main(String args[])
> throws Throwable
> {
> TestWriter wr = new TestWriter();
> wr.testSmoke( );
> }
>
>
> public void testSmoke( ) throws Throwable {
> InputStream excelInput = null;
>
> File file = new File("C:/Temp/"+FILE_NAME);
> excelInput = new FileInputStream( file );
> OPCPackage pkg = OPCPackage.open( excelInput );
> final XSSFWorkbook workbook = new XSSFWorkbook( pkg );
>
> m_workbook = new SXSSFWorkbook( workbook, 100 );
>
>
> String[ ] lines = { "Line201", "Line202", "Line203", "Line204",
> "Line205", "Line206", "Line207", "Line208", "Line209", "Line210", "Line211",
> "Line212", "Line213", "Line214", "Line215", "Line228", "Line229", "Line230",
> "Line231", "Line232", "Line233", "Line235", "Line236", "Line237", "Line238"
> };
> String[ ] items = {
> "28527RB","29520","29522","29586","29620#CA","29675","29675PW4","29685","29685#CA","33287","33462","34274","34300","34301","34301PW2#CA","34306","34478PW2#CA","34500","36620","3920","3920E","3920VW5","3925","39520","39520#CA","39521","39578","39580","39590","39590PW2#CA","39591","3975","3979","3980","3982","3982PW1","3984","3984VW5","3994","3994#CA","45220","45221","45280","45284","45285","45287","4535","4595","47623A","49520","49576","49585","522","52393","52400","52400#GV","52400PW4","526","52618","52618PW2","52638","52638#GV","527","528","529","532X","5335","5395","5395VW1#CA","55175CPW3","55187CPW3","55187CPW3#GV","55200CPW3","55206CPW3","55212CPW3","552A","5535","553X","55437PW2","55443PW2","555S","5578","557S","5584","5595","560","563","563VW1","56418","56418#GV","56425","56425PW2","565","566","56650","56650#CA","56650PW2","567","568","570","572","5735","575","5760","580","580#GV","581","590A","592A","592AVW3","593","593A","594#CA","59425","594A","594A#CA","594AVW3","595PW4","596","596#CA","598","612","621","623","632","6320","633","6379","6386","6389","639","6420","643","64450","64450#CA","64452A","6461A","6466","64700","64700#CA","65200","65225","65237A","65237PW3","653","653#GV","6535","6535PW3","6535PW3#CA","6535PW4VW4#CA","6535W","6536","65390","655","65500","65500PW1","65500PW2","6574#CA","6575","6576","6576#CA","6580","6580#CA","661","66212","66225","663","663#GV","66462","665","66520","665A","672","677","681","681A","685","687","72187CPW3VW2","72188CPW3VW2","72200C","72200CPW3VW2","72200CPW3VW2#CA","72212CPW3","72212CPW3VW2","72218CPW3VW2","72487PW2VW7","740","742","744PW4VW1","745A","748S","749","749A","750A","752","752#CA","753A","755#CA","757#CA","758","759","766","78215C","78250AC","78250C","78537","78551","9380","A-JHM318410#CA","A-JHM522610","H414210","H414210#CA","H414235","H414242","H414242#CA","H414249","HH506310","HH814510","HL-64450#CA","HL-A-JHM318448#CA","HL-A-JHM522649","HL-JM718149#CA","HM212010","HM212011","HM212011#CA","HM212044","HM212047","HM212047#CA","HM212049","HM212049#CA","HM218210","HM218238","HM218248","HM218248PW4#CA","HM220110#CA","HM220149","HM220149#CA","HM516410","HM516414AXVW1","HM516442","HM516448","HM516449","HM516449A","HM518410","HM518445","HM617010","HM617049","HM807010","HM807010PW2","HM807040PW3","HM807046PW3","HM807049","HM807049APW3","HM807049PW3","HM813810PW2","HM813811","HM813840PW3#CA","HM813841PW3","HM813844PW3","HM813849PW3","HM911210","JH211710","JH211710PK","JH211749","JH217210","JH217210#CA","JH217249","JH217249#CA","JH217249PK","JH307710","JH307749","JH415610","JH415647","JH415647#CA","JHM318410","JHM318410#CA","JHM318448#CA","JHM522610","JHM522610#GV","JHM522649","JHM720210","JHM720249","JHM720249#CA","JLM714110","JLM714110#CA","JLM714110PK","JLM714149","JLM714149#CA","JM511910","JM511910PK","JM511945","JM511946","JM511946#CA","JM511946PK","JM612910#CA","JM716610","JM716610#CA","JM716649","JM716649VW1#CA","JM716649VW2#CA","JM718110","JM718110#CA","JM718110PK","JM718149","JM718149#CA","JM718149PK","JM719113","JM719113#CA","JM719149#CA","JM719149PK","JM720210","JM720249","JM720249#CA","JM822010","JM822010#CA","JM822049","JM822049PK","L610510","LM613449","LM814810","LM814849","NA56425SWCB125","NA593CB125","XLH414210","XLH414235","XLHM518411PW2","XLHM518411WPW2","XLHM518437PW3"
> };
> String[ ] columns = { "Column1", "Column2", "Column3", "Column4",
> "Column5" };
>
> int rownum = 0;
> final int MAX_ROWS = 1048576;
>
> Sheet sh = m_workbook.createSheet( "MyTab" );
>
> System.out.println( "Starting writing XLSX file..." );
>
> // Write header
> Row row = sh.createRow( ++rownum );
> for( int column = 0; column < columns.length; column++ ) {
> Cell cell = row.createCell( column );
> cell.setCellValue( columns[ column ] );
> }
>
> for( String line : lines ) {
> for( String itemA : items ) {
> for( String itemB : items ) {
> if( ! itemA.equals( itemB ) ) {
>
> row = sh.createRow( ++rownum );
> int cellId = 0;
>
> Cell lineCell = row.createCell( cellId++ );
> lineCell.setCellValue( line );
>
> Cell itemACell = row.createCell( cellId++ );
> itemACell.setCellValue( itemA );
>
> Cell itemBCell = row.createCell( cellId++ );
> itemBCell.setCellValue( itemB );
>
> Cell setupCostCell = row.createCell( cellId++ );
> setupCostCell.setCellValue( 1.0 );
>
> Cell setupTimeCell = row.createCell( cellId );
> setupTimeCell.setCellValue( 1.0 );
>
> if( rownum == MAX_ROWS ) {
> sh = m_workbook.createSheet("MyTab"+ m_idx );
> m_idx++;
> rownum = 0;
>
> row = sh.createRow( rownum++ );
> for( int column = 0; column < columns.length;
> column++ ) {
> Cell cell = row.createCell( column );
> cell.setCellValue( columns[ column ] );
> }
> }
> }
> }
> }
> }
>
> FileOutputStream out = new FileOutputStream(
> "c:/temp/Output_MyTab_blank.xlsx" );
> m_workbook.write(out);
> out.close();
>
> System.out.println( "Done!" );
> }
>
>
> public static final String FILE_NAME = "MyTab_blank.xlsx";
>
> private SXSSFWorkbook m_workbook;
>
> private int m_idx = 1;
> }
>
>
> Also attaching the input template referred in the code:
> http://apache-poi.1045710.n5.nabble.com/file/n5469995/MyTab_blank.xlsx
> MyTab_blank.xlsx
>
> The output file was too large to attach.
> Also in the actual code we are using getSheet instead of createSheet, but
> while testing found even createSheet gives the same error while opening the
> file.
>
> Was wondering if anyone knew about this issue or how to correct it.
>
> Thanks
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Using-SXSSF-to-write-excel-gives-an-error-while-opening-the-file-tp5469995p5469995.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: Using SXSSF to write excel gives an error while opening the
file
Posted by ajitw <aj...@yahoo.com>.
Yes then the output file opens up fine without giving any error and the excel
contains all the data.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Using-SXSSF-to-write-excel-gives-an-error-while-opening-the-file-tp5469995p5470470.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: Using SXSSF to write excel gives an error while opening the file
Posted by Yegor Kozlov <ye...@dinom.ru>.
what happens if you use the default constructor and start the
workbook from blank:
m_workbook = new SXSSFWorkbook( 100 );
Is the output readable ?
Yegor
On Thu, Feb 9, 2012 at 8:31 PM, ajitw <aj...@yahoo.com> wrote:
> Hello,
> We used the hssf code earlier to write an .xls file. This started with a xlt
> template (has validations in columns and headers which we wanted to use) and
> outputed an .xls file.
>
> Now we are trying to upgrade to export the .xlsx file. As our data could be
> large we are trying to use SXSSFWorkbook with window size as 100.
> We start with an existing template which is an .xlsx file. Create a
> SXSSFWorkbook from this and then write data to this. After the job
> completes, when we try to open the excel file and it gives an error saying
> "Excel found unreadable content in ...xlsx. Do you want to recover the
> contents of this workbook?.."
> When you click Yes for the option box it repairs the excel and opens it but
> the existing sheets from the template have data missing.
>
> I wrote a test class to simulate the issue we are having :
>
> I start with a blank workbook as a template, then create a SXXSFWorkbook and
> create sheets and add data and the output an xlsx. Get the same error when
> opening this.
>
> This is the sample code:
>
>
> import java.io.*;
>
> import org.apache.poi.openxml4j.opc.*;
> import org.apache.poi.ss.usermodel.*;
> import org.apache.poi.xssf.streaming.*;
> import org.apache.poi.xssf.usermodel.*;
>
> public class TestWriter
> {
>
> public static void main(String args[])
> throws Throwable
> {
> TestWriter wr = new TestWriter();
> wr.testSmoke( );
> }
>
>
> public void testSmoke( ) throws Throwable {
> InputStream excelInput = null;
>
> File file = new File("C:/Temp/"+FILE_NAME);
> excelInput = new FileInputStream( file );
> OPCPackage pkg = OPCPackage.open( excelInput );
> final XSSFWorkbook workbook = new XSSFWorkbook( pkg );
>
> m_workbook = new SXSSFWorkbook( workbook, 100 );
>
>
> String[ ] lines = { "Line201", "Line202", "Line203", "Line204",
> "Line205", "Line206", "Line207", "Line208", "Line209", "Line210", "Line211",
> "Line212", "Line213", "Line214", "Line215", "Line228", "Line229", "Line230",
> "Line231", "Line232", "Line233", "Line235", "Line236", "Line237", "Line238"
> };
> String[ ] items = {
> "28527RB","29520","29522","29586","29620#CA","29675","29675PW4","29685","29685#CA","33287","33462","34274","34300","34301","34301PW2#CA","34306","34478PW2#CA","34500","36620","3920","3920E","3920VW5","3925","39520","39520#CA","39521","39578","39580","39590","39590PW2#CA","39591","3975","3979","3980","3982","3982PW1","3984","3984VW5","3994","3994#CA","45220","45221","45280","45284","45285","45287","4535","4595","47623A","49520","49576","49585","522","52393","52400","52400#GV","52400PW4","526","52618","52618PW2","52638","52638#GV","527","528","529","532X","5335","5395","5395VW1#CA","55175CPW3","55187CPW3","55187CPW3#GV","55200CPW3","55206CPW3","55212CPW3","552A","5535","553X","55437PW2","55443PW2","555S","5578","557S","5584","5595","560","563","563VW1","56418","56418#GV","56425","56425PW2","565","566","56650","56650#CA","56650PW2","567","568","570","572","5735","575","5760","580","580#GV","581","590A","592A","592AVW3","593","593A","594#CA","59425","594A","594A#CA","594AVW3","595PW4","596","596#CA","598","612","621","623","632","6320","633","6379","6386","6389","639","6420","643","64450","64450#CA","64452A","6461A","6466","64700","64700#CA","65200","65225","65237A","65237PW3","653","653#GV","6535","6535PW3","6535PW3#CA","6535PW4VW4#CA","6535W","6536","65390","655","65500","65500PW1","65500PW2","6574#CA","6575","6576","6576#CA","6580","6580#CA","661","66212","66225","663","663#GV","66462","665","66520","665A","672","677","681","681A","685","687","72187CPW3VW2","72188CPW3VW2","72200C","72200CPW3VW2","72200CPW3VW2#CA","72212CPW3","72212CPW3VW2","72218CPW3VW2","72487PW2VW7","740","742","744PW4VW1","745A","748S","749","749A","750A","752","752#CA","753A","755#CA","757#CA","758","759","766","78215C","78250AC","78250C","78537","78551","9380","A-JHM318410#CA","A-JHM522610","H414210","H414210#CA","H414235","H414242","H414242#CA","H414249","HH506310","HH814510","HL-64450#CA","HL-A-JHM318448#CA","HL-A-JHM522649","HL-JM718149#CA","HM212010","HM212011","HM212011#CA","HM212044","HM212047","HM212047#CA","HM212049","HM212049#CA","HM218210","HM218238","HM218248","HM218248PW4#CA","HM220110#CA","HM220149","HM220149#CA","HM516410","HM516414AXVW1","HM516442","HM516448","HM516449","HM516449A","HM518410","HM518445","HM617010","HM617049","HM807010","HM807010PW2","HM807040PW3","HM807046PW3","HM807049","HM807049APW3","HM807049PW3","HM813810PW2","HM813811","HM813840PW3#CA","HM813841PW3","HM813844PW3","HM813849PW3","HM911210","JH211710","JH211710PK","JH211749","JH217210","JH217210#CA","JH217249","JH217249#CA","JH217249PK","JH307710","JH307749","JH415610","JH415647","JH415647#CA","JHM318410","JHM318410#CA","JHM318448#CA","JHM522610","JHM522610#GV","JHM522649","JHM720210","JHM720249","JHM720249#CA","JLM714110","JLM714110#CA","JLM714110PK","JLM714149","JLM714149#CA","JM511910","JM511910PK","JM511945","JM511946","JM511946#CA","JM511946PK","JM612910#CA","JM716610","JM716610#CA","JM716649","JM716649VW1#CA","JM716649VW2#CA","JM718110","JM718110#CA","JM718110PK","JM718149","JM718149#CA","JM718149PK","JM719113","JM719113#CA","JM719149#CA","JM719149PK","JM720210","JM720249","JM720249#CA","JM822010","JM822010#CA","JM822049","JM822049PK","L610510","LM613449","LM814810","LM814849","NA56425SWCB125","NA593CB125","XLH414210","XLH414235","XLHM518411PW2","XLHM518411WPW2","XLHM518437PW3"
> };
> String[ ] columns = { "Column1", "Column2", "Column3", "Column4",
> "Column5" };
>
> int rownum = 0;
> final int MAX_ROWS = 1048576;
>
> Sheet sh = m_workbook.createSheet( "MyTab" );
>
> System.out.println( "Starting writing XLSX file..." );
>
> // Write header
> Row row = sh.createRow( ++rownum );
> for( int column = 0; column < columns.length; column++ ) {
> Cell cell = row.createCell( column );
> cell.setCellValue( columns[ column ] );
> }
>
> for( String line : lines ) {
> for( String itemA : items ) {
> for( String itemB : items ) {
> if( ! itemA.equals( itemB ) ) {
>
> row = sh.createRow( ++rownum );
> int cellId = 0;
>
> Cell lineCell = row.createCell( cellId++ );
> lineCell.setCellValue( line );
>
> Cell itemACell = row.createCell( cellId++ );
> itemACell.setCellValue( itemA );
>
> Cell itemBCell = row.createCell( cellId++ );
> itemBCell.setCellValue( itemB );
>
> Cell setupCostCell = row.createCell( cellId++ );
> setupCostCell.setCellValue( 1.0 );
>
> Cell setupTimeCell = row.createCell( cellId );
> setupTimeCell.setCellValue( 1.0 );
>
> if( rownum == MAX_ROWS ) {
> sh = m_workbook.createSheet("MyTab"+ m_idx );
> m_idx++;
> rownum = 0;
>
> row = sh.createRow( rownum++ );
> for( int column = 0; column < columns.length;
> column++ ) {
> Cell cell = row.createCell( column );
> cell.setCellValue( columns[ column ] );
> }
> }
> }
> }
> }
> }
>
> FileOutputStream out = new FileOutputStream(
> "c:/temp/Output_MyTab_blank.xlsx" );
> m_workbook.write(out);
> out.close();
>
> System.out.println( "Done!" );
> }
>
>
> public static final String FILE_NAME = "MyTab_blank.xlsx";
>
> private SXSSFWorkbook m_workbook;
>
> private int m_idx = 1;
> }
>
>
> Also attaching the input template referred in the code:
> http://apache-poi.1045710.n5.nabble.com/file/n5469995/MyTab_blank.xlsx
> MyTab_blank.xlsx
>
> The output file was too large to attach.
> Also in the actual code we are using getSheet instead of createSheet, but
> while testing found even createSheet gives the same error while opening the
> file.
>
> Was wondering if anyone knew about this issue or how to correct it.
>
> Thanks
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Using-SXSSF-to-write-excel-gives-an-error-while-opening-the-file-tp5469995p5469995.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