Cannot apply Cell styles in OpenXML spreadsheet

Question

I have a problem with the Cell Style in OpenXml/C# code.

I cannot define a style to a cell. I tried many ways to do this but I was unsuccessful. Can someone help me?

This part of the code, to apply the Cell Style, is:

Stylesheet stylesheet = spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
WorkbookPart workbookPart = spreadsheet.WorkbookPart;
Fonts fonts = stylesheet.Fonts;//new Fonts() { Count = (UInt32Value)1U };
Font font = new Font();
FontSize newFontSize = new FontSize() { Val = fontSize };
Color newFontColor = new Color() { Rgb = new HexBinaryValue(fontColor) };
FontName newFontName = new FontName() { Val = fontName };
FontScheme fontScheme = new FontScheme() { Val = FontSchemeValues.Minor };
font.Append(newFontSize);
font.Append(newFontColor);
font.Append(newFontName);
font.Append(fontScheme);
if (bold == true)
{
    Bold newBold = new Bold();
    font.Append(newBold);
}
if (italic == true)
{
    Italic newItalic = new Italic();
    font.Append(newItalic);
}
if (underline == true)
{
    Underline newUnderline = new Underline();
    font.Append(newUnderline);
}
fonts.Append(font);
Fills fills = stylesheet.Fills;//new Fills() { Count = (UInt32Value)1U };
Fill fill = new Fill(new PatternFill()
{
    PatternType = PatternValues.Solid,
    ForegroundColor = new ForegroundColor() { Rgb = new HexBinaryValue(foregroundColor) }
});
fills.Append(fill);
Borders borders = stylesheet.Borders; //new Borders() { Count = (UInt32Value)1U };
Border border = new Border();
if (borderDefault == true)
{
    BottomBorder bottomBorder = new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin };
    TopBorder topBorder = new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin };
    LeftBorder leftBorder = new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin };
    RightBorder rightBorder = new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin };
    border.Append(leftBorder);
    border.Append(rightBorder);
    border.Append(topBorder);
    border.Append(bottomBorder);
    borders.Append(border);
}
CellStyleFormats cellStyleFormats = stylesheet.CellStyleFormats;
UInt32 fontIndex = fonts.Count;
UInt32 fillIndex = fills.Count;
UInt32 borderIndex = borders.Count;
CellFormat cellFormat = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = fontIndex, FillId = fillIndex, BorderId = borderIndex, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = alignment };
if (alignment == false)
{
    Alignment alignment1 = new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Center };
}
else
{
    Alignment alignment1 = new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center };
}
cellStyleFormats.Append(cellFormat);
UInt32 formatIndex = cellStyleFormats.Count;
CellStyles cellStyles = stylesheet.CellStyles;//new CellStyles() { Count = (UInt32Value)0U };
CellStyle cellStyle = new CellStyle() { Name = "Normal", FormatId = formatIndex, BuiltinId = (UInt32Value)0U };
cellStyles.Append(cellStyle);
//stylesheet.Append(cellStyleFormats);
//stylesheet.Append(fills);
//stylesheet.Append(borders);
//stylesheet.Append(cellFormat);
//stylesheet.Append(cellStyles);
stylesheet.Save();
cell.StyleIndex = formatIndex;
spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();

score 0 · Answer 1 · answered Apr 27 '15 at 19:13

Here is how we did it; maybe this approach will help you. We created an external stylesheet and saved it to the spreadsheet

Create the excel file:

        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(fileStream, SpreadsheetDocumentType.Workbook))
        {
            // Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            WorkbookStylesPart stylesPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorkbookStylesPart>();
            stylesPart.Stylesheet = new Stylesheet();
            stylesPart.Stylesheet.InnerXml = this.GenerateStyleSheet();
            stylesPart.Stylesheet.Save();

            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Add Sheets to the Workbook.
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

            // Append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Result" };
            sheets.Append(sheet);

            workbookpart.Workbook.Save();

            // Close the document.
            spreadsheetDocument.Close();
        }

method to get style sheet:

        public string GenerateStyleSheet()
    {
        if (this.StylesheetFormat == null)
        {
            using (var styleXmlReader = new System.IO.StreamReader(AppDomain.CurrentDomain.BaseDirectory + @"\bin\Resources\ExcelStyleSheet.xml"))
            {
                this.StylesheetFormat = styleXmlReader.ReadToEnd();
            }
        }

        return this.StylesheetFormat;
    }

Style sheet xml:

<x:fonts>
  <x:font>
    <x:sz val="11" />
    <x:color rgb="000000" />
    <x:name val="Calibri" />
  </x:font>
  <x:font>
    <x:b val="1" />
    <x:sz val="11" />
    <x:color rgb="000000" />
    <x:name val="Calibri" />
  </x:font>
  <x:font>
    <x:sz val="11" />
    <x:color rgb="0B0080" />
    <x:name val="Calibri" />
  </x:font>
</x:fonts>
<x:fills>
  <x:fill>
    <x:patternFill patternType="none" />
  </x:fill>
  <x:fill>
    <x:patternFill patternType="solid">
      <x:fgColor rgb="CCCCCC" />
    </x:patternFill>
  </x:fill>
  <x:fill>
    <x:patternFill patternType="solid">
      <x:fgColor rgb="FF9900" />
    </x:patternFill>
  </x:fill>
  <x:fill>
    <x:patternFill patternType="solid">
      <x:fgColor rgb="FFA500" />
    </x:patternFill>
  </x:fill>
  <x:fill>
    <x:patternFill patternType="solid">
      <x:fgColor rgb="D8D8D8" />
    </x:patternFill>
  </x:fill>
</x:fills>
<x:borders>
  <x:border>
    <x:left />
    <x:right />
    <x:top />
    <x:bottom />
    <x:diagonal />
  </x:border>
  <x:border>
    <x:left style="thin">
      <x:color auto="1" />
    </x:left>
    <x:right style="thin">
      <x:color auto="1" />
    </x:right>
    <x:top style="thin">
      <x:color auto="1" />
    </x:top>
    <x:bottom style="thin">
      <x:color auto="1" />
    </x:bottom>
    <x:diagonal />
  </x:border>
</x:borders>
<x:cellXfs>
  <x:xf fontId="0" fillId="0" borderId="0" />
  <x:xf fontId="1" fillId="3" borderId="0" applyFont="0" />
  <x:xf fontId="0" fillId="4" borderId="0" />
  <x:xf fontId="2" fillId="0" borderId="0" />
  <x:xf fontId="2" fillId="4" borderId="0" />
</x:cellXfs>

Cannot apply Cell styles in OpenXML spreadsheet

1 Answers1