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 <[email protected]> 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: [email protected] > For additional commands, e-mail: [email protected] > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
