Plutext

I am using xslx4j to create a simple spreadsheet - but when I try to add style information then try to open the file with Excel 2007 it crashes the application!

Has anyone successfully set style information for cells?

If I just add a 'styles' part with an empty StyleSheet (as below) Excel complains but opens the file (after removing the Style part)

Code: Select all

SpreadsheetMLPackage pkg = SpreadsheetMLPackage.createPackage();

      WorksheetPart sheet = pkg.createWorksheetPart(new PartName("/xl/worksheets/sheet1.xml"), "Sheet1", 1);
    SharedStrings sharedStrings = new SharedStrings(new PartName("/xl/sharedStrings.xml"));
    sharedStrings.setJaxbElement( new CTSst() );
    Styles styles = new Styles(new PartName("/xl/styles.xml"));
    CTStylesheet styleSheet = new CTStylesheet();
    styles.setJaxbElement( styleSheet );

addContent(sheet, sharedStrings, styles);

    Parts parts = pkg.getParts();
    Part workBook = parts.get( new PartName("/xl/workbook.xml") );
    workBook.addTargetPart( sharedStrings );
    workBook.addTargetPart( styles );

      SaveToZipFile saver = new SaveToZipFile(pkg);
      saver.save(outputfilepath);

But if I start putting anything into the Stylesheet then Excel crashes:

Code: Select all

SpreadsheetMLPackage pkg = SpreadsheetMLPackage.createPackage();

      WorksheetPart sheet = pkg.createWorksheetPart(new PartName("/xl/worksheets/sheet1.xml"), "Sheet1", 1);
    SharedStrings sharedStrings = new SharedStrings(new PartName("/xl/sharedStrings.xml"));
    sharedStrings.setJaxbElement( new CTSst() );
    Styles styles = new Styles(new PartName("/xl/styles.xml"));
    CTStylesheet styleSheet = new CTStylesheet();
    styleSheet.setCellXfs( new CTCellXfs() );
    styleSheet.getCellXfs().setCount( 1L );
    styleSheet.getCellXfs().getXf().add( new CTXf() );
    styleSheet.setCellStyles( new CTCellStyles() );
    styleSheet.getCellStyles().setCount( 1L );
    styleSheet.getCellStyles().getCellStyle().add( new CTCellStyle() );
    styleSheet.setCellStyleXfs( new CTCellStyleXfs() );
    styleSheet.getCellStyleXfs().setCount( 1L );
    styleSheet.getCellStyleXfs().getXf().add( new CTXf() );   
    styles.setJaxbElement( styleSheet );

      addContent(sheet, sharedStrings, styles);

    Parts parts = pkg.getParts();
    Part workBook = parts.get( new PartName("/xl/workbook.xml") );
    workBook.addTargetPart( sharedStrings );
    workBook.addTargetPart( styles );

      SaveToZipFile saver = new SaveToZipFile(pkg);
      saver.save(outputfilepath);

I expect I'm missing something really obvious but I just can't find it myself. Any help or advice where to look/what to do would be greatly appreciated.

A new xlsx I created in Excel 2007 contains the following styles.xml:



Using the "Good" cell style adds another font, another fill, another cellStyle another cellXfs and another cellStyleXfs:



Check you've got the minimum required content.

You could read an example styles part in from a file to init it; the following snippet which you could adapt is from docx StyleDefinitionsPart:

Code: Select all

         is = org.docx4j.utils.ResourceUtils.getResource(
               "org/docx4j/openpackaging/parts/WordprocessingML/KnownStyles.xml");                  
         
         JAXBContext jc = Context.jc;
         Unmarshaller u = jc.createUnmarshaller();         
         u.setEventHandler(new org.docx4j.jaxb.JaxbValidationEventHandler());

         org.docx4j.wml.Styles styles = (org.docx4j.wml.Styles)u.unmarshal( is );         


Also, if I run the PartsList sample on that xlsx, I get:

Code: Select all

Part /_rels/.rels [org.docx4j.openpackaging.parts.relationships.RelationshipsPart]  containing JaxbElement:org.docx4j.relationships.Relationships
    Part /docProps/app.xml [org.docx4j.openpackaging.parts.DocPropsExtendedPart] http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties containing JaxbElement:org.docx4j.docProps.extended.Properties
    Part /docProps/core.xml [org.docx4j.openpackaging.parts.DocPropsCorePart] http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties containing JaxbElement:org.docx4j.docProps.core.CoreProperties
    Part /xl/workbook.xml [org.docx4j.openpackaging.parts.SpreadsheetML.WorkbookPart] http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument containing JaxbElement:org.xlsx4j.sml.Workbook
        Part /xl/worksheets/sheet3.xml [org.docx4j.openpackaging.parts.SpreadsheetML.WorksheetPart] http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet containing JaxbElement:org.xlsx4j.sml.Worksheet
        Part /xl/worksheets/sheet2.xml [org.docx4j.openpackaging.parts.SpreadsheetML.WorksheetPart] http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet containing JaxbElement:org.xlsx4j.sml.Worksheet
        Part /xl/worksheets/sheet1.xml [org.docx4j.openpackaging.parts.SpreadsheetML.WorksheetPart] http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet containing JaxbElement:org.xlsx4j.sml.Worksheet
        Part /xl/sharedStrings.xml [org.docx4j.openpackaging.parts.SpreadsheetML.SharedStrings] http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings containing JaxbElement:org.xlsx4j.sml.CTSst
        Part /xl/styles.xml [org.docx4j.openpackaging.parts.SpreadsheetML.Styles] http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles containing JaxbElement:org.xlsx4j.sml.CTStylesheet
        Part /xl/theme/theme1.xml [org.docx4j.openpackaging.parts.ThemePart] http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme containing JaxbElement:org.docx4j.dml.Theme


Are you attaching (addTargetPart) your styles part to workbook.xml (consistent with the above structure)?

Yes I was doing the 'addTarget' part.

Your mention of 'minimum required content' sent me off in the right direction.

Seems, that despite the XSD suggesting all stylesheet contents are optional, if you have any contents you need to have at least one Font, Border and Fill.

When I added these Excel happily opened my spreadsheet with wrapped text.

Thanks very much for your help.