poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Yegor Kozlov <yegor.koz...@dinom.ru>
Subject Re: Using SXSSF to write excel gives an error while opening the file
Date Thu, 09 Feb 2012 19:05:41 GMT
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 <ajit_wadatkar@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


Mime
View raw message