CODEWITHSUNDEEP
asp.netexcelvb.netopenxml
Sandeep Rao
Sandeep Rao

Reputation: 1757

Unable to use multiple fonts with OpenXMl Cell styling

I have two different fonts defined in the style sheet but If I use the second style with StyleIndex=1 . I am unable to open the generated spread sheet. Any help would be greatly appreciated

My code

    Private Function GenerateStyleSheet() As Stylesheet

        Dim ss As Stylesheet = New Stylesheet()
        Dim fonts1 As Fonts = New Fonts()

        Dim f1 As Font = New Font()
        Dim f1Size As FontSize = New FontSize()
        f1Size.Val = 11D

        f1.Append(f1Size)

        Dim f2 As Font = New Font()
        Dim b2 As Bold = New Bold()
        Dim f2Size As FontSize = New FontSize()
        f2Size.Val = 11D
        f2.Append(b2)
        f2.Append(f2Size)

        fonts1.Append(f1)

        fonts1.Append(f2)
        fonts1.Count = fonts1.ChildElements.Count
        ss.Append(fonts1)
        Return ss

    End Function





Function getBoldTextCell(ByVal cell As String, ByRef row As Row, ByVal val As String) As Row
    Dim refCell As Cell = Nothing
    Dim newCell As New Cell()
    newCell.StyleIndex = 1 // 0 works 
    newCell.CellReference = cell
    row.InsertBefore(newCell, refCell)
    newCell.CellValue = New CellValue(val)
    newCell.DataType = New EnumValue(Of CellValues)(CellValues.String)


    Return (row)
End Function

XML Code:

<x:row xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:c r="A1" s="0" t="str">
    <x:v>Request #</x:v>
  </x:c>
  <x:c r="B1" t="str">
    <x:v>1</x:v>
  </x:c>
</x:row>

Style Code:

<x:fonts count="2" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:font>
    <x:sz val="11" />
  </x:font>
  <x:font>
    <x:b />
    <x:sz val="11" />
  </x:font>
</x:fonts>

Upvotes: 2

Views: 1657

Answers (1)

Kavindu Dodanduwa
Kavindu Dodanduwa

Reputation: 13059

You cannot define styles like that . You need to create a Style with Fonts , Fills , Borders and create cellformats from defined Fonts , Fills , Borders as given below.

    // <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 }; 
    CellFormat cellformat1 = new CellFormat(new Alignment() { Horizontal  HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }) { 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);

And later when defining cells , add style refference as 

    cell.StyleIndex=0 ; // Default style
    cell1.StyleIndex=1 ; // Our defined style 1

Upvotes: 2

Related Questions