CODEWITHSUNDEEP
c#excelopenxml
Kliver Max
Kliver Max

Reputation: 5299

How to create a Cell Format correctly?

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?

Upvotes: 5

Views: 10366

Answers (1)

Kavindu Dodanduwa
Kavindu Dodanduwa

Reputation: 13059

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.

Upvotes: 7

Related Questions