none
How to format Excel cells programmatically using C#? RRS feed

  • Question

  • 1. How to format the font to BOLD.  In the cold below, I used f.FontId = 1, but it didn't work.

    2. How to add color to the font?

    3. How to set the column width with auto max?

    4. With f.NumberFormatId = 4, the output is #,##0.00 which is correct. But how can I get 4 decimals, like #,##0.0000?

    var excelStyleSheet = new ExcelStylesheet();
    
                    excelStyleSheet.AddCellFormat("Numeric", (f) =>
                    {
                        f.NumberFormatId = 4; // #,##0.00
                        f.Alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Center };
                        f.ApplyNumberFormat = BooleanValue.FromBoolean(true);
                    });
    
                    excelStyleSheet.AddCellFormat("Header", (f) =>
                    {
                        f.FontId = 1;
                        f.Alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center };
                        f.ApplyFont = BooleanValue.FromBoolean(true);
                    });


    • Edited by danyeungw Wednesday, September 18, 2013 2:21 PM mis-spelled
    Tuesday, September 17, 2013 6:25 PM

Answers

  • Hi,

    As far as I know, if we want to add format in a new workbook, we need to create related xml.

    For example: if we need to set bold for A1, we need to add a new element named font into /xl/styles.xml/x:stylesheet/x:fonts.

    Since you post a part code of your project and I cannot work fine (unknown class such as AddPartXml/ excelStylesheet), so I wrote a demo for your reference.

    The new workbook contain four specified cells as you mentioned:

    A1 output format: #,##0.00

    B1 Bold

    C1 red font color

    D1 set specified width

    Here is the code below (Excel 2013):

    using X14 = DocumentFormat.OpenXml.Office2010.Excel;
    using X15 = DocumentFormat.OpenXml.Office2013.Excel;
    static void Main(string[] args)
    {
    NewWorkbook();
    }
    public static void NewWorkbook()
            {
                using (SpreadsheetDocument document = SpreadsheetDocument.Create("E:/testStyle.xlsx", SpreadsheetDocumentType.Workbook))
                {
                    WorkbookPart workbookPart = document.AddWorkbookPart();
    
                    WorkbookStylesPart wbsp = workbookPart.AddNewPart<WorkbookStylesPart>();
                    SharedStringItem sharedStringItem = new SharedStringItem();
    
                    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                    string relId = workbookPart.GetIdOfPart(worksheetPart);
    
                    Workbook workbook = new Workbook();
                    FileVersion fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
                    Worksheet worksheet = new Worksheet();
                    SheetData sheetData = new SheetData();
    
                    Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
                    Cell cell1 = new Cell() { CellReference = "A1", StyleIndex = 1 };
                    Cell cell2 = new Cell() { CellReference = "B1", StyleIndex = 2 };
                    Cell cell3 = new Cell() { CellReference = "C1", StyleIndex = 3 };
    
                    row1.Append(cell1);
                    row1.Append(cell2);
                    row1.Append(cell3);
                    sheetData.Append(row1);
                    Columns columns1 = new Columns();
                    Column column1 = new Column() { Min = 4, Max = 4, Width = 37.28515625D, CustomWidth = true };
                    columns1.Append(column1);
                    worksheet.Append(columns1);
    
                    worksheet.Append(sheetData);
    
                    wbsp.Stylesheet = GenerateStylesheet();
    
                    worksheetPart.Worksheet = worksheet;
                    worksheetPart.Worksheet.Save();
    
                    Sheets sheets = new Sheets();
                    Sheet sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };
                    sheets.Append(sheet);
    
                    workbook.Append(fileVersion);
                    workbook.Append(sheets);
    
                    document.WorkbookPart.Workbook = workbook;
                    document.WorkbookPart.Workbook.Save();
                    document.Close();
                }
            }
    
            public static Stylesheet GenerateStylesheet()
            {
                Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
                stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
                stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
    
                Fonts fonts1 = new Fonts() { Count = (UInt32Value)3U, KnownFonts = true };
    
                Font font1 = new Font();
                FontSize fontSize1 = new FontSize() { Val = 11D };
                Color color1 = new Color() { Theme = (UInt32Value)1U };
                FontName fontName1 = new FontName() { Val = "Calibri" };
                FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
                FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };
    
                font1.Append(fontSize1);
                font1.Append(color1);
                font1.Append(fontName1);
                font1.Append(fontFamilyNumbering1);
                font1.Append(fontScheme1);
    
                Font font2 = new Font();
                FontSize fontSize2 = new FontSize() { Val = 11D };
                Color color2 = new Color() { Rgb = "FFFF0000" };
                FontName fontName2 = new FontName() { Val = "Calibri" };
                FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 };
                FontScheme fontScheme2 = new FontScheme() { Val = FontSchemeValues.Minor };
    
                font2.Append(fontSize2);
                font2.Append(color2);
                font2.Append(fontName2);
                font2.Append(fontFamilyNumbering2);
                font2.Append(fontScheme2);
    
                Font font3 = new Font();
                Bold bold1 = new Bold();
                FontSize fontSize3 = new FontSize() { Val = 11D };
                Color color3 = new Color() { Theme = (UInt32Value)1U };
                FontName fontName3 = new FontName() { Val = "Calibri" };
                FontFamilyNumbering fontFamilyNumbering3 = new FontFamilyNumbering() { Val = 2 };
                FontScheme fontScheme3 = new FontScheme() { Val = FontSchemeValues.Minor };
    
                font3.Append(bold1);
                font3.Append(fontSize3);
                font3.Append(color3);
                font3.Append(fontName3);
                font3.Append(fontFamilyNumbering3);
                font3.Append(fontScheme3);
    
                fonts1.Append(font1);
                fonts1.Append(font2);
                fonts1.Append(font3);
    
                Fills fills1 = new Fills() { Count = (UInt32Value)2U };
    
                Fill fill1 = new Fill();
                PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
    
                fill1.Append(patternFill1);
    
                Fill fill2 = new Fill();
                PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
    
                fill2.Append(patternFill2);
    
                fills1.Append(fill1);
                fills1.Append(fill2);
    
                Borders borders1 = new Borders() { Count = (UInt32Value)1U };
    
                Border border1 = new Border();
                LeftBorder leftBorder1 = new LeftBorder();
                RightBorder rightBorder1 = new RightBorder();
                TopBorder topBorder1 = new TopBorder();
                BottomBorder bottomBorder1 = new BottomBorder();
                DiagonalBorder diagonalBorder1 = new DiagonalBorder();
    
                border1.Append(leftBorder1);
                border1.Append(rightBorder1);
                border1.Append(topBorder1);
                border1.Append(bottomBorder1);
                border1.Append(diagonalBorder1);
    
                borders1.Append(border1);
    
                CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
                CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
    
                cellStyleFormats1.Append(cellFormat1);
    
                CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)4U };
                CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
                CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)2U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true };
                CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true };
                CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true };
    
                cellFormats1.Append(cellFormat2);
                cellFormats1.Append(cellFormat3);
                cellFormats1.Append(cellFormat4);
                cellFormats1.Append(cellFormat5);
    
                CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
                CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = 0, BuiltinId = 0 };
    
                cellStyles1.Append(cellStyle1);
                DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = 0 };
                TableStyles tableStyles1 = new TableStyles() { Count = 0, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" };
    
                StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
    
                StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
                stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
                X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" };
    
                stylesheetExtension1.Append(slicerStyles1);
    
                StylesheetExtension stylesheetExtension2 = new StylesheetExtension() { Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };
                stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
                X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles() { DefaultTimelineStyle = "TimeSlicerStyleLight1" };
    
                stylesheetExtension2.Append(timelineStyles1);
    
                stylesheetExtensionList1.Append(stylesheetExtension1);
                stylesheetExtensionList1.Append(stylesheetExtension2);
    
                stylesheet1.Append(fonts1);
                stylesheet1.Append(fills1);
                stylesheet1.Append(borders1);
                stylesheet1.Append(cellStyleFormats1);
                stylesheet1.Append(cellFormats1);
                stylesheet1.Append(cellStyles1);
                stylesheet1.Append(differentialFormats1);
                stylesheet1.Append(tableStyles1);
                stylesheet1.Append(stylesheetExtensionList1);
                return stylesheet1;
            }
    
    In addition, we can use OpenXMLSDKTool to view all xml files and get reflected code. So if we want to create a specified workbook, we can try to create a similar one and open by OpenXMLSDKTool to view the structure and reflected code.

    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Friday, September 20, 2013 1:42 AM
    Moderator

All replies

  • Hi,

    To my understand ,you want to set font style in sheet using OpenXml,but I'm confused about

    ExcelStylesheet. I cant find in Document.OpenXml.Spreadsheet namespace, if you want set

    format of sheet.this sample should be help you .

    new Stylesheet( new Fonts( new DocumentFormat.OpenXml.Spreadsheet.Font( new FontSize() { Val = 10D }, new DocumentFormat.OpenXml.Spreadsheet.Color() { Theme = (UInt32Value)1U }, new FontName() { Val = "Arial" }, new Bold (), new FontFamilyNumbering() { Val = 2 })));


    Regard,
    • Edited by Mercop002 Thursday, September 19, 2013 6:18 AM
    Thursday, September 19, 2013 6:16 AM
  • Hi,

    As far as I know, if we want to add format in a new workbook, we need to create related xml.

    For example: if we need to set bold for A1, we need to add a new element named font into /xl/styles.xml/x:stylesheet/x:fonts.

    Since you post a part code of your project and I cannot work fine (unknown class such as AddPartXml/ excelStylesheet), so I wrote a demo for your reference.

    The new workbook contain four specified cells as you mentioned:

    A1 output format: #,##0.00

    B1 Bold

    C1 red font color

    D1 set specified width

    Here is the code below (Excel 2013):

    using X14 = DocumentFormat.OpenXml.Office2010.Excel;
    using X15 = DocumentFormat.OpenXml.Office2013.Excel;
    static void Main(string[] args)
    {
    NewWorkbook();
    }
    public static void NewWorkbook()
            {
                using (SpreadsheetDocument document = SpreadsheetDocument.Create("E:/testStyle.xlsx", SpreadsheetDocumentType.Workbook))
                {
                    WorkbookPart workbookPart = document.AddWorkbookPart();
    
                    WorkbookStylesPart wbsp = workbookPart.AddNewPart<WorkbookStylesPart>();
                    SharedStringItem sharedStringItem = new SharedStringItem();
    
                    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                    string relId = workbookPart.GetIdOfPart(worksheetPart);
    
                    Workbook workbook = new Workbook();
                    FileVersion fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
                    Worksheet worksheet = new Worksheet();
                    SheetData sheetData = new SheetData();
    
                    Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
                    Cell cell1 = new Cell() { CellReference = "A1", StyleIndex = 1 };
                    Cell cell2 = new Cell() { CellReference = "B1", StyleIndex = 2 };
                    Cell cell3 = new Cell() { CellReference = "C1", StyleIndex = 3 };
    
                    row1.Append(cell1);
                    row1.Append(cell2);
                    row1.Append(cell3);
                    sheetData.Append(row1);
                    Columns columns1 = new Columns();
                    Column column1 = new Column() { Min = 4, Max = 4, Width = 37.28515625D, CustomWidth = true };
                    columns1.Append(column1);
                    worksheet.Append(columns1);
    
                    worksheet.Append(sheetData);
    
                    wbsp.Stylesheet = GenerateStylesheet();
    
                    worksheetPart.Worksheet = worksheet;
                    worksheetPart.Worksheet.Save();
    
                    Sheets sheets = new Sheets();
                    Sheet sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };
                    sheets.Append(sheet);
    
                    workbook.Append(fileVersion);
                    workbook.Append(sheets);
    
                    document.WorkbookPart.Workbook = workbook;
                    document.WorkbookPart.Workbook.Save();
                    document.Close();
                }
            }
    
            public static Stylesheet GenerateStylesheet()
            {
                Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
                stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
                stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
    
                Fonts fonts1 = new Fonts() { Count = (UInt32Value)3U, KnownFonts = true };
    
                Font font1 = new Font();
                FontSize fontSize1 = new FontSize() { Val = 11D };
                Color color1 = new Color() { Theme = (UInt32Value)1U };
                FontName fontName1 = new FontName() { Val = "Calibri" };
                FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
                FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };
    
                font1.Append(fontSize1);
                font1.Append(color1);
                font1.Append(fontName1);
                font1.Append(fontFamilyNumbering1);
                font1.Append(fontScheme1);
    
                Font font2 = new Font();
                FontSize fontSize2 = new FontSize() { Val = 11D };
                Color color2 = new Color() { Rgb = "FFFF0000" };
                FontName fontName2 = new FontName() { Val = "Calibri" };
                FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 };
                FontScheme fontScheme2 = new FontScheme() { Val = FontSchemeValues.Minor };
    
                font2.Append(fontSize2);
                font2.Append(color2);
                font2.Append(fontName2);
                font2.Append(fontFamilyNumbering2);
                font2.Append(fontScheme2);
    
                Font font3 = new Font();
                Bold bold1 = new Bold();
                FontSize fontSize3 = new FontSize() { Val = 11D };
                Color color3 = new Color() { Theme = (UInt32Value)1U };
                FontName fontName3 = new FontName() { Val = "Calibri" };
                FontFamilyNumbering fontFamilyNumbering3 = new FontFamilyNumbering() { Val = 2 };
                FontScheme fontScheme3 = new FontScheme() { Val = FontSchemeValues.Minor };
    
                font3.Append(bold1);
                font3.Append(fontSize3);
                font3.Append(color3);
                font3.Append(fontName3);
                font3.Append(fontFamilyNumbering3);
                font3.Append(fontScheme3);
    
                fonts1.Append(font1);
                fonts1.Append(font2);
                fonts1.Append(font3);
    
                Fills fills1 = new Fills() { Count = (UInt32Value)2U };
    
                Fill fill1 = new Fill();
                PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
    
                fill1.Append(patternFill1);
    
                Fill fill2 = new Fill();
                PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
    
                fill2.Append(patternFill2);
    
                fills1.Append(fill1);
                fills1.Append(fill2);
    
                Borders borders1 = new Borders() { Count = (UInt32Value)1U };
    
                Border border1 = new Border();
                LeftBorder leftBorder1 = new LeftBorder();
                RightBorder rightBorder1 = new RightBorder();
                TopBorder topBorder1 = new TopBorder();
                BottomBorder bottomBorder1 = new BottomBorder();
                DiagonalBorder diagonalBorder1 = new DiagonalBorder();
    
                border1.Append(leftBorder1);
                border1.Append(rightBorder1);
                border1.Append(topBorder1);
                border1.Append(bottomBorder1);
                border1.Append(diagonalBorder1);
    
                borders1.Append(border1);
    
                CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
                CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
    
                cellStyleFormats1.Append(cellFormat1);
    
                CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)4U };
                CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
                CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)2U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true };
                CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true };
                CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true };
    
                cellFormats1.Append(cellFormat2);
                cellFormats1.Append(cellFormat3);
                cellFormats1.Append(cellFormat4);
                cellFormats1.Append(cellFormat5);
    
                CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
                CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = 0, BuiltinId = 0 };
    
                cellStyles1.Append(cellStyle1);
                DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = 0 };
                TableStyles tableStyles1 = new TableStyles() { Count = 0, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" };
    
                StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
    
                StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
                stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
                X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" };
    
                stylesheetExtension1.Append(slicerStyles1);
    
                StylesheetExtension stylesheetExtension2 = new StylesheetExtension() { Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };
                stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
                X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles() { DefaultTimelineStyle = "TimeSlicerStyleLight1" };
    
                stylesheetExtension2.Append(timelineStyles1);
    
                stylesheetExtensionList1.Append(stylesheetExtension1);
                stylesheetExtensionList1.Append(stylesheetExtension2);
    
                stylesheet1.Append(fonts1);
                stylesheet1.Append(fills1);
                stylesheet1.Append(borders1);
                stylesheet1.Append(cellStyleFormats1);
                stylesheet1.Append(cellFormats1);
                stylesheet1.Append(cellStyles1);
                stylesheet1.Append(differentialFormats1);
                stylesheet1.Append(tableStyles1);
                stylesheet1.Append(stylesheetExtensionList1);
                return stylesheet1;
            }
    
    In addition, we can use OpenXMLSDKTool to view all xml files and get reflected code. So if we want to create a specified workbook, we can try to create a similar one and open by OpenXMLSDKTool to view the structure and reflected code.

    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Friday, September 20, 2013 1:42 AM
    Moderator