How to create a Cell Format correctly?

Question

I have a two Cell Formats:

 var stylesPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorkbookStylesPart>(); stylesPart.Stylesheet = new Stylesheet(); // blank font list stylesPart.Stylesheet.Fonts = new Fonts(); stylesPart.Stylesheet.Fonts.Count = 2; stylesPart.Stylesheet.Fonts.AppendChild(new Font(new Bold(), new FontSize() {Val = 14})); stylesPart.Stylesheet.Fonts.AppendChild(new Font(new FontSize() {Val = 12})); // cell format list stylesPart.Stylesheet.CellFormats = new CellFormats(); stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 0, ApplyFont = true }); stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 1, FontId = 1, ApplyFont = true }); stylesPart.Stylesheet.CellFormats.Count = 2; stylesPart.Stylesheet.Save();

And when i use any of them to create my Excel document i get a message when tried to open document that document have an error in /xl/styles.xml (Styles);

Whats can be wrong?

FIL · Accepted Answer · 2018-09-21 10:23:53Z

In a excel sheet you need to follow a specific order for create a style sheet. What you are not doing is to follow this order [As I provided in code example]. Easiest way to learn how actually style sheet is structured use Open XMl Productivity tool. You can analyse any Excel file for it's contents and also verify formatting too. [A good tutorial].

As a support here I have provided a code for a basic style sheet for a workbook. This is the correct order you should have. [Here I have provided code for 2 styles, style index 0 the default and 1 a blot text with alignment.]

WorkbookStylesPart stylesheet = spreadsheet.WorkbookPart .AddNewPart<WorkbookStylesPart>(); Stylesheet workbookstylesheet = new Stylesheet(); // <Fonts> Font font0 = new Font(); // Default font Font font1 = new Font(); // Bold font Bold bold = new Bold(); font1.Append(bold); Fonts fonts = new Fonts(); // <APENDING Fonts> fonts.Append(font0); fonts.Append(font1); // <Fills> Fill fill0 = new Fill(); // Default fill Fills fills = new Fills(); // <APENDING Fills> fills.Append(fill0); // <Borders> Border border0 = new Border(); // Defualt border Borders borders = new Borders(); // <APENDING Borders> borders.Append(border0); // <CellFormats> CellFormat cellformat0 = new CellFormat() { FormatId = 0, FillId = 0, BorderId = 0 }; Alignment alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }; CellFormat cellformat1 = new CellFormat(alignment) { FontId = 1 }; // <APENDING CellFormats> CellFormats cellformats = new CellFormats(); cellformats.Append(cellformat0); cellformats.Append(cellformat1); // Append FONTS, FILLS , BORDERS & CellFormats to stylesheet <Preserve the ORDER> workbookstylesheet.Append(fonts); workbookstylesheet.Append(fills); workbookstylesheet.Append(borders); workbookstylesheet.Append(cellformats); stylesheet.Stylesheet = workbookstylesheet; stylesheet.Stylesheet.Save();

Note - In your specific case you have omitted much needed Fills and Borders.

Thanks for example. I thought that i can create a style with only Font() or Fill() etc.
The tools sdk is the greatest thing I have ever used. You have just saved me from scrounging the web for examples. OMG. I wish I could up your vote 10 X

Collectives™ on Stack Overflow

How to create a Cell Format correctly?

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related