none
Open XML Spreadsheet style error RRS feed

  • Question

  • I've used the Open XML Format SDK 2.0 to create a simple excel document. Opening the document in Excel first requires Excel to open it. I've narrowed this down to the CellFormats within the Stylesheet. The contents of which are below:

    <x:styleSheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
     
    <x:numFmts count="4">
       
    <x:numFmt numFmtId="170" formatCode="_-[$GBP]\ * #,##0.00_-;\-[$GBP]\ * #,##0.00_-;_-[$GBP]\ * &quot;-&quot;??_-;_-@_-" />
       
    <x:numFmt numFmtId="171" formatCode="_-[$EUR]\ * #,##0.00_-;\-[$EUR]\ * #,##0.00_-;_-[$EUR]\ * &quot;-&quot;??_-;_-@_-" />
       
    <x:numFmt numFmtId="172" formatCode="_-[$SEK]\ * #,##0.00_-;\-[$SEK]\ * #,##0.00_-;_-[$SEK]\ * &quot;-&quot;??_-;_-@_-" />
       
    <x:numFmt numFmtId="173" formatCode="_-[$NOK]\ * #,##0.00_-;\-[$NOK]\ * #,##0.00_-;_-[$NOK]\ * &quot;-&quot;??_-;_-@_-" />
     
    </x:numFmts>
     
    <x:cellXfs count="5">
       
    <x:xf />
       
    <x:xf numFmtId="170" applyNumberFormat="1" />
       
    <x:xf numFmtId="171" applyNumberFormat="1" />
       
    <x:xf numFmtId="172" applyNumberFormat="1" />
       
    <x:xf numFmtId="173" applyNumberFormat="1" />
     
    </x:cellXfs>
    </x:styleSheet>

    Once Excel has repaired the file it looks like I expect it. However in order to make it look like expected I had to add the empty cell format as the first child. If one of the proper styles were used as the first child, once Excel had repaired the file that style would have been removed.

    Any pointers in the right direction would be greatly appreciated.

    Friday, March 27, 2009 11:13 AM

Answers

  • Finally fix the problem.

    I started out with the code auto generated by the Document Reflector tool and edited to bring it inline with mine until it started erroring. The problem it seems was that the stylesheet required a general font, fill and boarder definition. For anyone that is interest the above code now does work with the additional lines added before the cell formats are declared:

    // General definitions
    stylesheet.Fonts = new Fonts(new Font(new FontSize() { Val = 11D }, new Color() { Theme = (UInt32Value)1U }, new FontName() { Val = "Calibri" }, new FontFamily() { Val = 2 }, new FontScheme() { Val = FontSchemeValues.Minor })) { Count = (UInt32Value)1U };
    stylesheet.Fills = new Fills(new Fill(new PatternFill() { PatternType = PatternValues.None })) { Count = (UInt32Value)2U };
    stylesheet.Borders = new Borders(new Border(new LeftBorder(), new RightBorder(), new TopBorder(), new BottomBorder(), new DiagonalBorder())) { Count = (UInt32Value)1U };
    


    goodol - Thank-you very much for you help, it's been greatly appresiated.
    • Marked as answer by Whisky Fudge Tuesday, April 14, 2009 8:49 AM
    Tuesday, April 14, 2009 8:47 AM

All replies

  • I guess it's Excel app's behavior by default, though it's wired... In this case, you have to add the general cell format to avoid this issue. 
    Wednesday, April 1, 2009 10:02 AM
  • By general cell format I'm assuming your referring to the empty cell format definition I make? In which case this doesn't actually fix the problem completely, Excel still has to repair the file before it can open it.

    Without the empty cell format definition, the file had to be repaired and then anything being styled by the first style definition lost its styling. I found that by adding the empty cell style definition, Excel still had to repair the document but at least all the cell's I had set a cell style on, were retained their styling.

    I hope this makes sense, as I don't believe I'm explaining myself very well.

    Wednesday, April 1, 2009 10:12 AM
  • yes, "general" means empty cell format.

    I create a spreadsheet document with three columns with custom cell formats.

    Below is the SpreadsheetML in styles part:
    <numFmts count="3">        <br/>        <numFmt numFmtId="165" formatCode="[$-409]d\-mmm\-yyyy;@"/><br/>        <numFmt numFmtId="166" formatCode="&quot;$&quot;#,##0.000"/><br/>        <numFmt numFmtId="167" formatCode="0.00000;[Red]0.00000"/><br/></numFmts><br/>
    
    <cellXfs count="4"> <br/>        <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>       <br/>        <xf numFmtId="165" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/><br/>        <xf numFmtId="166" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/><br/>        <xf numFmtId="167" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/><br/></cellXfs><br/>
    
    

    and the SpreadsheetML in sheet part:

    <cols><br/>        <col min="1" max="1" width="9.5703125" style="3" bestFit="1" customWidth="1"/><br/>        <col min="2" max="2" width="10.85546875" style="1" bestFit="1" customWidth="1"/><br/>        <col min="3" max="3" width="9.140625" style="2"/><br/></cols><br/><br/>
    
    
    the first cellformat in cellformats is the empty one.  when i open the spreasheet by app, there's no reqair requirement.
    Thursday, April 2, 2009 5:34 AM
  • Thank-you for your help, and apologies about the delay in getting back to you, but still no joy!

    I've started from scratch making the simplest app I could. This still throws up the same repair requirement in Excel. The code is as follows:

    static void Main(string[] args)
    {
    	using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(@"C:\Users\WhiskyFudge\Desktop\test.xlsx", SpreadsheetDocumentType.Workbook))
    	{
    		// Add workbook
    		WorkbookPart workbookPart = spreadSheet.AddWorkbookPart();
    		workbookPart.Workbook = new Workbook(new Sheets());
    		workbookPart.Workbook.Save();
    
    		#region Worksheet
    		// Add a new worksheet part to the workbook.
    		WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    		worksheetPart.Worksheet = new Worksheet(new SheetData());
    		worksheetPart.Worksheet.Save();
    
    		Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
    		string relationshipId = workbookPart.GetIdOfPart(worksheetPart);
    
    		// Get a unique ID for the new sheet.
    		uint sheetId = 1;
    		if (sheets.Elements<Sheet>().Count() > 0)
    		{
    			sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
    		}
    
    		string sheetName = "Sheet" + sheetId;
    
    		// Append the new worksheet and associate it with the workbook.
    		Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
    		sheets.Append(sheet);
    		workbookPart.Workbook.Save();
    		SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
    		#endregion
    
    		#region StyleSheet
    		WorkbookStylesPart workbookStyle = workbookPart.AddNewPart<WorkbookStylesPart>();
    		Stylesheet stylesheet = new Stylesheet();
    
    		// Add number formats
    		NumberFormats numberFormats = new NumberFormats();
    		numberFormats.AppendChild<NumberFormat>(new NumberFormat() { NumberFormatId = 165, FormatCode = @"0.00" });
    		numberFormats.Count = 1;
    		stylesheet.NumberFormats = numberFormats;
    
    		// add cell formats
    		CellFormats cellFormats = new CellFormats();
    
    		CellFormat cellFormat = new CellFormat();
    		cellFormat.NumberFormatId = (UInt32Value)0;
    		cellFormat.FontId = (UInt32Value)0;
    		cellFormat.FillId = (UInt32Value)0;
    		cellFormat.BorderId = (UInt32Value)0;
    		cellFormat.FormatId = (UInt32Value)0;
    		cellFormats.AppendChild<CellFormat>(cellFormat);
    
    		cellFormat = new CellFormat();
    		cellFormat.NumberFormatId = (UInt32Value)0;
    		cellFormat.FontId = (UInt32Value)0;
    		cellFormat.FillId = (UInt32Value)0;
    		cellFormat.BorderId = (UInt32Value)0;
    		cellFormat.FormatId = (UInt32Value)0;
    		cellFormat.NumberFormatId = 165;
    		cellFormat.ApplyNumberFormat = true;
    		cellFormats.AppendChild<CellFormat>(cellFormat);
    
    		cellFormats.Count = 2;
    		stylesheet.CellFormats = cellFormats;
    
    		stylesheet.Save(workbookStyle);
    		#endregion
    
    		worksheetPart.Worksheet.Save();
    	}
    }
    
    which produces the following ML:
    <x:styleSheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main  <x:numFmts count="1">
        <x:numFmt numFmtId="165" formatCode="0.00" />
      </x:numFmts>
      <x:cellXfs count="2">
        <x:xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" />
        <x:xf numFmtId="165" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1" />
      </x:cellXfs>
    </x:styleSheet>
    ">
    
    Any further help would be greatly appreciated. I feel I've spent way too long on this problem which is no doubt just a simple coding error on my part. If it helps I could send the c# or xlsx files used.
    Sunday, April 12, 2009 9:16 AM
  • If you are not sure which rules that your documents have violated, a fast way to check what Excel has changed before it could open the document is to use the OpenXmlDiff tool. The tool could compare your before and after documents and show what are the differences.
    Monday, April 13, 2009 6:54 AM
  • I've already attempted this idea, unfortunately Excel makes a large number of changes to the style sheet and document in the whole. Thus making it very hard (so much so I can't tell) what change has actually fixed the document error.

    Monday, April 13, 2009 7:09 AM
  • the above SpreadsheetML is right. the problem should be somewhere else.

    Tuesday, April 14, 2009 3:17 AM
  • Finally fix the problem.

    I started out with the code auto generated by the Document Reflector tool and edited to bring it inline with mine until it started erroring. The problem it seems was that the stylesheet required a general font, fill and boarder definition. For anyone that is interest the above code now does work with the additional lines added before the cell formats are declared:

    // General definitions
    stylesheet.Fonts = new Fonts(new Font(new FontSize() { Val = 11D }, new Color() { Theme = (UInt32Value)1U }, new FontName() { Val = "Calibri" }, new FontFamily() { Val = 2 }, new FontScheme() { Val = FontSchemeValues.Minor })) { Count = (UInt32Value)1U };
    stylesheet.Fills = new Fills(new Fill(new PatternFill() { PatternType = PatternValues.None })) { Count = (UInt32Value)2U };
    stylesheet.Borders = new Borders(new Border(new LeftBorder(), new RightBorder(), new TopBorder(), new BottomBorder(), new DiagonalBorder())) { Count = (UInt32Value)1U };
    


    goodol - Thank-you very much for you help, it's been greatly appresiated.
    • Marked as answer by Whisky Fudge Tuesday, April 14, 2009 8:49 AM
    Tuesday, April 14, 2009 8:47 AM
  • Thanks for sharing:)
    Tuesday, April 14, 2009 9:04 AM
  • Thanks, just what i needed, more than 10 years later :D

    Monday, November 25, 2019 10:41 AM