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 <[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]
