Page 1 of 1

help with xlsx creation and row grouping...

PostPosted: Fri Sep 13, 2013 3:43 am
by baven
Hi,
I am new to this forum and the usage of this app. I have attached a sample xlsx file and I am trying to create a xlsx in similar format (row grouping, collapse/expand etc). I used POI XSSFWorkbook, but the memory footprint is high for the real doc creation and I tried to use POI SXSSFWorkbook, but that throws an runtime exception as "not implemented" when we call "sheet.setRowGroupCollapsed(..): method.

sheet.groupRow(1, 9);
sheet.setRowGroupCollapsed(1, true); //this throws a runtime exception in Apache's POI SXSSF impl.

I need a sample code or reference to a sample code for my requirement. Thanks for the help.

Re: help with xlsx creation and row grouping...

PostPosted: Fri Sep 13, 2013 9:31 pm
by jason
If you want to do stuff with xlsx4j, in general you don't use POI methods!

This seems to be the pertinent XML in your sample xlsx:

Syntax: [ Download ] [ Hide ]
Using xml Syntax Highlighting
<worksheet>
  <sheetPr>
    <outlinePr summaryBelow="false"/>
  </sheetPr>
  :
 
  <sheetData>
    :
    <row r="2" hidden="true" outlineLevel="1">
      :
    </row>
    :
    <row r="11" collapsed="true">
      :
    </row>
 
Parsed in 0.001 seconds, using GeSHi 1.0.8.4



You can generate suitable code by submitting your sample xlsx to the docx4j online webapp.

Doing that, generates code like:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting

org.xlsx4j.sml.ObjectFactory smlObjectFactory = new org.xlsx4j.sml.ObjectFactory();

Worksheet worksheet = smlObjectFactory.createWorksheet();
JAXBElement<org.xlsx4j.sml.Worksheet> worksheetWrapped = smlObjectFactory.createWorksheet(worksheet);
    // Create object for sheetPr
    CTSheetPr sheetpr = smlObjectFactory.createCTSheetPr();
    worksheet.setSheetPr(sheetpr);
        // Create object for outlinePr
        CTOutlinePr outlinepr = smlObjectFactory.createCTOutlinePr();
        sheetpr.setOutlinePr(outlinepr);
    // Create object for sheetFormatPr
    CTSheetFormatPr sheetformatpr = smlObjectFactory.createCTSheetFormatPr();
    worksheet.setSheetFormatPr(sheetformatpr);
        sheetformatpr.setBaseColWidth( new Long(8) );
        sheetformatpr.setOutlineLevelRow( new Short(3) );
        sheetformatpr.setOutlineLevelCol( new Short(0) );
    // Create object for sheetData
    SheetData sheetdata = smlObjectFactory.createSheetData();
    worksheet.setSheetData(sheetdata);
        // Create object for row
        Row row = smlObjectFactory.createRow();
        sheetdata.getRow().add( row);
            row.setR( new Long(1) );
            :
            row.setOutlineLevel( new Short(0) );
 
Parsed in 0.017 seconds, using GeSHi 1.0.8.4