CODEWITHSUNDEEP
excelvb.netopenxml-sdk
Jacques Koekemoer
Jacques Koekemoer

Reputation: 1414

What should Excel Styles.xml look like - OpenXML

I am generating a large excel file and I am trying to make the headings of the data bold.

If I comment out all of the CellFormat code and create the spreadsheet then the file creates correctly, but if I don't comment the lines out then excel gives me an error: Repaired Records: Format from /xl/styles.xml. (Obviously I clicked Yes to repair the file first.)

This is what my code looks like:

Public Function Create_Spreadsheet_Stylesheet(ByRef stylePart As WorkbookStylesPart) As WorkbookStylesPart
    Dim font1Id As UInt32Value,
        font2Id As UInt32Value

    Dim font1 As New Font With {
        .FontName = New FontName With {.Val = "arial"},
        .FontSize = New FontSize With {.Val = 9}
    }

    Dim font2 As New Font With {
        .Bold = New Bold,
        .FontName = New FontName With {.Val = "arial"},
        .FontSize = New FontSize With {.Val = 9}
    }

    stylePart.Stylesheet = New Stylesheet
    stylePart.Stylesheet.Fonts = New Fonts

    stylePart.Stylesheet.Fonts.Append(font1)
    font1Id = Convert.ToUInt32(stylePart.Stylesheet.Fonts.ChildElements.Count - 1)

    stylePart.Stylesheet.Fonts.Append(font2)
    font2Id = Convert.ToUInt32(stylePart.Stylesheet.Fonts.ChildElements.Count - 1)

    stylePart.Stylesheet.Save()

    Dim cf1 As New CellFormat() With {
        .FontId = font1Id,
        .FillId = 0,
        .BorderId = 0
    }

    Dim cf2 As New CellFormat() With {
        .FontId = font2Id,
        .FillId = 0,
        .BorderId = 0
    }

    stylePart.Stylesheet.CellFormats = New CellFormats ' I would comment this line out
    stylePart.Stylesheet.CellFormats.Append(cf1) ' And this one
    stylePart.Stylesheet.CellFormats.Append(cf2) ' And this one

    stylePart.Stylesheet.Save()

    Return stylePart
End Function

The styles.xml looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<x:styleSheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
   <x:fonts>
      <x:font>
         <x:sz val="9" />
         <x:name val="arial" />
      </x:font>
      <x:font>
         <x:b />
         <x:sz val="9" />
         <x:name val="arial" />
      </x:font>
   </x:fonts>
   <x:cellXfs>
      <x:xf fontId="0" fillId="0" borderId="0" />
      <x:xf fontId="1" fillId="0" borderId="0" />
   </x:cellXfs>
</x:styleSheet>

What am I doing wrong with the code, or what must I change to get excel to use the cellFormat.

I've looked at a lot of examples on the internet on how to bold a cell and I have been following this tutorial here:

https://social.msdn.microsoft.com/Forums/windows/en-US/4ae9ba85-d5d2-4ce8-a0ba-dece26ed7d2a/open-xml-sdk-for-making-font-bold?forum=oxmlsdk

Upvotes: 0

Views: 1762

Answers (1)

Siarhei Tyshkavets
Siarhei Tyshkavets

Reputation: 392

I think the problem here is that in your cell formats, you're referencing Fill with FillId = 0 and Border with BorderId = 0. Since you've recreated a stylesheet here:

stylePart.Stylesheet = New Stylesheet

no such Fills or Borders are available in your document. You have two solutions for that:

  1. Create a basic fill (white background) and a basic border (no borders) objects and add them to your stylesheet before adding cell formats, just like you're adding fonts.
  2. Try removing references to those Border and Fill from your CellFormat's definitions. Perhaps it will work.

Note that in the example you were following, they were using an existing excel file which likely had certain borders and fills stored in its stylesheet, so they didn't have to do it.

Upvotes: 1

Related Questions