none
StyleSheet - Adding new Font for Excel RRS feed

  • Question

  • This is my first time using OpenXml and I am having problems with creating StyleSheet.  I copied a routine from the web for creating a stylesheet file and I am trying to edit it by adding another Font but it appears that the program can not find the new font.  What am I doing wrong?
    private Stylesheet CreateStylesheet()
        {
            Stylesheet ss = new Stylesheet();
            Fonts fts = new Fonts();
            Font ft = new Font();
            FontName ftn = new FontName();
            FontSize ftsz = new FontSize();
            ftn.Val = StringValue.FromString("Calibri");        
            ftsz.Val = DoubleValue.FromDouble(11);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);
            ft = new Font();
            ftn = new FontName();
            ftsz = new FontSize();
            ftn.Val = StringValue.FromString("Palatino Linotype");        
            ftsz.Val = DoubleValue.FromDouble(18);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);
            //new font added
            ft = new Font();
            ftn = new FontName();
            ftsz = new FontSize();
            ftn.Val = StringValue.FromString("Arial");        
            ftsz.Val = DoubleValue.FromDouble(12);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);
            fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
            Fills fills = new Fills();
            Fill fill;
            PatternFill patternFill;
            fill = new Fill();
            patternFill = new PatternFill();
            patternFill.PatternType = PatternValues.None;
            fill.PatternFill = patternFill;
            fills.Append(fill);
            fill = new Fill();
            patternFill = new PatternFill();
            patternFill.PatternType = PatternValues.Gray125;
            fill.PatternFill = patternFill;
            fills.Append(fill);
            fill = new Fill();
            patternFill = new PatternFill();
            patternFill.PatternType = PatternValues.Solid;
            //patternFill.ForegroundColor = new ForegroundColor();
            //patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00ff9728");
            patternFill.BackgroundColor = new BackgroundColor();
            patternFill.BackgroundColor.Rgb = HexBinaryValue.FromString("00ff9728");
            fill.PatternFill = patternFill;
            fills.Append(fill);
            fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
            Borders borders = new Borders();
            Border border = new Border();
            border.LeftBorder = new LeftBorder();
            border.RightBorder = new RightBorder();
            border.TopBorder = new TopBorder();
            border.BottomBorder = new BottomBorder();
            border.DiagonalBorder = new DiagonalBorder();
            borders.Append(border);
            border = new Border();
            border.LeftBorder = new LeftBorder();
            border.LeftBorder.Style = BorderStyleValues.Thin;
            border.RightBorder = new RightBorder();
            border.RightBorder.Style = BorderStyleValues.Thin;
            border.TopBorder = new TopBorder();
            border.TopBorder.Style = BorderStyleValues.Thin;
            border.BottomBorder = new BottomBorder();
            border.BottomBorder.Style = BorderStyleValues.Thin;
            border.DiagonalBorder = new DiagonalBorder();
            borders.Append(border);
            borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
            CellStyleFormats csfs = new CellStyleFormats();
            CellFormat cf = new CellFormat();
            cf.NumberFormatId = 0;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 1;
            csfs.Append(cf);
            csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count);
            uint iExcelIndex = 164;
            NumberingFormats nfs = new NumberingFormats();
            CellFormats cfs = new CellFormats();
            cf = new CellFormat();
            cf.NumberFormatId = 0;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cfs.Append(cf);
            NumberingFormat nfDateTime = new NumberingFormat();
            nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
            nfs.Append(nfDateTime);
            NumberingFormat nf4decimal = new NumberingFormat();
            nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nf4decimal.FormatCode = StringValue.FromString("#,##0.0000");
            nfs.Append(nf4decimal);
            // #,##0.00 is also Excel style index 4
            NumberingFormat nf2decimal = new NumberingFormat();
            nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nf2decimal.FormatCode = StringValue.FromString("#,##0.00");
            nfs.Append(nf2decimal);
            // @ is also Excel style index 49
            NumberingFormat nfForcedText = new NumberingFormat();
            nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nfForcedText.FormatCode = StringValue.FromString("@");
            nfs.Append(nfForcedText);
            //Alignment
            Alignment align = new Alignment() { Horizontal = HorizontalAlignmentValues.General, Vertical = VerticalAlignmentValues.Center };
            // index 1
            cf = new CellFormat();
            cf.NumberFormatId = nfDateTime.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 1;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            // index 2
            cf = new CellFormat();
            cf.NumberFormatId = nf4decimal.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 1;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            // index 3
            cf = new CellFormat();
            cf.NumberFormatId = nf2decimal.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 1;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            // index 4
            cf = new CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 1;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            // index 5
            // Header text
            cf = new CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId = 1;
            cf.FillId = 0;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            // index 6
            // column text
            cf = new CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId = 1;
            cf.FillId = 1;
            cf.BorderId = 1;
            cf.FormatId = 1;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            // index 7
            // coloured 2 decimal text
            cf = new CellFormat();
            cf.NumberFormatId = nf2decimal.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 1;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            cf.Append(align);
            // index 8
            // coloured column text
            cf = new CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 1;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            //// index 9          new index added
            // coloured column text
            cf = new CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId = 3;
            cf.FillId = 0;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
            cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);
            ss.Append(nfs);
            ss.Append(fts);
            ss.Append(fills);
            ss.Append(borders);
            ss.Append(csfs);
            ss.Append(cfs);
            CellStyles css = new CellStyles();
            CellStyle cs = new CellStyle();
            cs.Name = StringValue.FromString("Normal");
            cs.FormatId = 0;
            cs.BuiltinId = 0;
            css.Append(cs);
            css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
            ss.Append(css);
            DifferentialFormats dfs = new DifferentialFormats();
            dfs.Count = 0;
            ss.Append(dfs);
            TableStyles tss = new TableStyles();
            tss.Count = 0;
            tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
            tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
            ss.Append(tss);
            return ss;
        }

    Wednesday, September 25, 2013 2:57 PM

Answers

  • I hope this is what you are looking for:

     Stylesheet ss = new Stylesheet();
            Fonts fts = new Fonts();
            Font ft = new Font();
            FontName ftn = new FontName();
            FontSize ftsz = new FontSize();
            ftn.Val = StringValue.FromString("Calibri");        
            ftsz.Val = DoubleValue.FromDouble(11);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);
            ft = new Font();
            ftn = new FontName();
            ftsz = new FontSize();
            ftn.Val = StringValue.FromString("Palatino Linotype");        
            ftsz.Val = DoubleValue.FromDouble(18);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);
            //new font added
            ft = new Font();
            ftn = new FontName();
            ftsz = new FontSize();
            ftn.Val = StringValue.FromString("Arial");        
            ftsz.Val = DoubleValue.FromDouble(12);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);
            fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
     //// index 9          new index added
            // coloured column text
            cf = new CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId = 2;
            cf.FillId = 4;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
           
    Cell newCell = new Cell() { CellReference = cellReference };
               
                if (Session["WriteFlag"].ToString() == "Headings")
                { newCell.StyleIndex = 9;  }
               
                row.InsertBefore(newCell, refCell);
                worksheet.Save();
                return newCell;

    • Marked as answer by Angel1953 Thursday, November 3, 2016 1:05 PM
    Friday, October 18, 2013 2:11 PM

All replies

  • << I am trying to edit it by adding another Font but it appears that the program can not find the new font>>

    In what way does the problem manifest itself? Are you getting an error message? Please provide more detail.

    Also, please show us the code that YOU are using to add the font.

    Note, please, that the font must be written exactly as the system "sees" it. This includes any spaces, capitalization, etc. And I have come across fonts that display a different name in Word than is actually used in the system. So you might want to create a very small, simple workbook that uses this font then open the workbook's Zip package and see what Excel has stored in the style sheet. You can also use the Open XML Productivity Tool for this.


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, October 2, 2013 2:48 PM
    Moderator
  • Only get an error when trying to open excel file:

    "Excel found unreadable content in qqqq.xlsx."

    Thursday, October 17, 2013 7:48 PM
  • I'm still missing information from you:

    "Also, please show us the code that YOU are using to add the font."


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, October 18, 2013 6:08 AM
    Moderator
  • I hope this is what you are looking for:

     Stylesheet ss = new Stylesheet();
            Fonts fts = new Fonts();
            Font ft = new Font();
            FontName ftn = new FontName();
            FontSize ftsz = new FontSize();
            ftn.Val = StringValue.FromString("Calibri");        
            ftsz.Val = DoubleValue.FromDouble(11);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);
            ft = new Font();
            ftn = new FontName();
            ftsz = new FontSize();
            ftn.Val = StringValue.FromString("Palatino Linotype");        
            ftsz.Val = DoubleValue.FromDouble(18);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);
            //new font added
            ft = new Font();
            ftn = new FontName();
            ftsz = new FontSize();
            ftn.Val = StringValue.FromString("Arial");        
            ftsz.Val = DoubleValue.FromDouble(12);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);
            fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
     //// index 9          new index added
            // coloured column text
            cf = new CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId = 2;
            cf.FillId = 4;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
           
    Cell newCell = new Cell() { CellReference = cellReference };
               
                if (Session["WriteFlag"].ToString() == "Headings")
                { newCell.StyleIndex = 9;  }
               
                row.InsertBefore(newCell, refCell);
                worksheet.Save();
                return newCell;

    • Marked as answer by Angel1953 Thursday, November 3, 2016 1:05 PM
    Friday, October 18, 2013 2:11 PM