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:[email protected]] Sent: Thursday, February 09, 2012 11:32 AM To: [email protected] 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: [email protected] For additional commands, e-mail: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
