none
Create excel format using OpoenXML

    Question

  • We need to create format styles using OpenXML SDK. This index may be later attached to cells, columns or rows. We need to create a format programatically. Please help.
    Tuesday, March 12, 2013 12:35 PM

Answers

  • using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml;
    using X14 = DocumentFormat.OpenXml.Office2010.Excel;
    
    namespace GeneratedCode
    {
        public class GeneratedClass
        {
            // Adds child parts and generates content of the specified part.
            public void CreateWorkbookStylesPart(WorkbookStylesPart part)
            {
                GeneratePartContent(part);
    
            }
    
            // Generates content of part.
            private void GeneratePartContent(WorkbookStylesPart part)
            {
                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)2U, 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 = 24D };
                Color color2 = new Color(){ Rgb = "FFFF0000" };
                FontName fontName2 = new FontName(){ Val = "Aharoni" };
                FontCharSet fontCharSet1 = new FontCharSet(){ Val = 177 };
    
                font2.Append(fontSize2);
                font2.Append(color2);
                font2.Append(fontName2);
                font2.Append(fontCharSet1);
    
                fonts1.Append(font1);
                fonts1.Append(font2);
    
                Fills fills1 = new Fills(){ Count = (UInt32Value)3U };
    
                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);
    
                Fill fill3 = new Fill();
    
                PatternFill patternFill3 = new PatternFill(){ PatternType = PatternValues.Solid };
                ForegroundColor foregroundColor1 = new ForegroundColor(){ Rgb = "FFFFFF00" };
                BackgroundColor backgroundColor1 = new BackgroundColor(){ Indexed = (UInt32Value)64U };
    
                patternFill3.Append(foregroundColor1);
                patternFill3.Append(backgroundColor1);
    
                fill3.Append(patternFill3);
    
                fills1.Append(fill1);
                fills1.Append(fill2);
                fills1.Append(fill3);
    
                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)2U };
                CellFormat cellFormat1 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
                CellFormat cellFormat2 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U };
    
                cellStyleFormats1.Append(cellFormat1);
                cellStyleFormats1.Append(cellFormat2);
    
                CellFormats cellFormats1 = new CellFormats(){ Count = (UInt32Value)2U };
                CellFormat cellFormat3 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
                CellFormat cellFormat4 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U };
    
                cellFormats1.Append(cellFormat3);
                cellFormats1.Append(cellFormat4);
    
                CellStyles cellStyles1 = new CellStyles(){ Count = (UInt32Value)2U };
                CellStyle cellStyle1 = new CellStyle(){ Name = "MyStyle", FormatId = (UInt32Value)1U };
                CellStyle cellStyle2 = new CellStyle(){ Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
    
                cellStyles1.Append(cellStyle1);
                cellStyles1.Append(cellStyle2);
                DifferentialFormats differentialFormats1 = new DifferentialFormats(){ Count = (UInt32Value)0U };
                TableStyles tableStyles1 = new TableStyles(){ Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };
    
                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");
    
                OpenXmlUnknownElement openXmlUnknownElement1 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<x15:timelineStyles defaultTimelineStyle=\"TimeSlicerStyleLight1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");
    
                stylesheetExtension2.Append(openXmlUnknownElement1);
    
                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);
    
                part.Stylesheet = stylesheet1;
            }
    
    
        }
    }
    


    Regards,
    Bubu
    http://zsvipullo.blogspot.it

    Please mark my answer if it helped you, I would greatly appreciate it.

    Tuesday, March 12, 2013 2:13 PM

All replies

  • using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml;
    using X14 = DocumentFormat.OpenXml.Office2010.Excel;
    
    namespace GeneratedCode
    {
        public class GeneratedClass
        {
            // Adds child parts and generates content of the specified part.
            public void CreateWorkbookStylesPart(WorkbookStylesPart part)
            {
                GeneratePartContent(part);
    
            }
    
            // Generates content of part.
            private void GeneratePartContent(WorkbookStylesPart part)
            {
                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)2U, 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 = 24D };
                Color color2 = new Color(){ Rgb = "FFFF0000" };
                FontName fontName2 = new FontName(){ Val = "Aharoni" };
                FontCharSet fontCharSet1 = new FontCharSet(){ Val = 177 };
    
                font2.Append(fontSize2);
                font2.Append(color2);
                font2.Append(fontName2);
                font2.Append(fontCharSet1);
    
                fonts1.Append(font1);
                fonts1.Append(font2);
    
                Fills fills1 = new Fills(){ Count = (UInt32Value)3U };
    
                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);
    
                Fill fill3 = new Fill();
    
                PatternFill patternFill3 = new PatternFill(){ PatternType = PatternValues.Solid };
                ForegroundColor foregroundColor1 = new ForegroundColor(){ Rgb = "FFFFFF00" };
                BackgroundColor backgroundColor1 = new BackgroundColor(){ Indexed = (UInt32Value)64U };
    
                patternFill3.Append(foregroundColor1);
                patternFill3.Append(backgroundColor1);
    
                fill3.Append(patternFill3);
    
                fills1.Append(fill1);
                fills1.Append(fill2);
                fills1.Append(fill3);
    
                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)2U };
                CellFormat cellFormat1 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
                CellFormat cellFormat2 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U };
    
                cellStyleFormats1.Append(cellFormat1);
                cellStyleFormats1.Append(cellFormat2);
    
                CellFormats cellFormats1 = new CellFormats(){ Count = (UInt32Value)2U };
                CellFormat cellFormat3 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
                CellFormat cellFormat4 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U };
    
                cellFormats1.Append(cellFormat3);
                cellFormats1.Append(cellFormat4);
    
                CellStyles cellStyles1 = new CellStyles(){ Count = (UInt32Value)2U };
                CellStyle cellStyle1 = new CellStyle(){ Name = "MyStyle", FormatId = (UInt32Value)1U };
                CellStyle cellStyle2 = new CellStyle(){ Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
    
                cellStyles1.Append(cellStyle1);
                cellStyles1.Append(cellStyle2);
                DifferentialFormats differentialFormats1 = new DifferentialFormats(){ Count = (UInt32Value)0U };
                TableStyles tableStyles1 = new TableStyles(){ Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };
    
                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");
    
                OpenXmlUnknownElement openXmlUnknownElement1 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<x15:timelineStyles defaultTimelineStyle=\"TimeSlicerStyleLight1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");
    
                stylesheetExtension2.Append(openXmlUnknownElement1);
    
                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);
    
                part.Stylesheet = stylesheet1;
            }
    
    
        }
    }
    


    Regards,
    Bubu
    http://zsvipullo.blogspot.it

    Please mark my answer if it helped you, I would greatly appreciate it.

    Tuesday, March 12, 2013 2:13 PM
  • Create using OpenXMLSDKProductivityTool

    http://www.microsoft.com/en-us/download/details.aspx?id=5124


    Regards,
    Bubu
    http://zsvipullo.blogspot.it

    Please mark my answer if it helped you, I would greatly appreciate it.

    Tuesday, March 12, 2013 2:14 PM