コード例 #1
0
        private static void CreateBasicFont(Fonts fonts, Color color)
        {
            Font     font1     = new Font();
            FontSize fontSize1 = new FontSize()
            {
                Val = 11D
            };
            FontName fontName1 = new FontName()
            {
                Val = "Calibri"
            };
            FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering()
            {
                Val = 2
            };
            FontScheme fontScheme1 = new FontScheme()
            {
                Val = FontSchemeValues.Minor
            };

            font1.AppendChild(fontSize1);
            font1.AppendChild(color);
            font1.AppendChild(fontName1);
            font1.AppendChild(fontFamilyNumbering1);
            font1.AppendChild(fontScheme1);
            fonts.AppendChild(font1);
        }
コード例 #2
0
ファイル: Exporter.cs プロジェクト: staherianYMCA/test
        static Fonts GenerateFonts()
        {
            var fonts = new Fonts {
                Count = 1U, KnownFonts = true
            };

            var font1     = new Font();
            var fontSize1 = new FontSize {
                Val = 10D
            };
            var color1 = new Color {
                Theme = 1U
            };
            var fontName1 = new FontName {
                Val = "Times New Roman"
            };
            var fontFamilyNumbering1 = new FontFamilyNumbering {
                Val = 1
            };
            var fontCharSet1 = new FontCharSet {
                Val = 204
            };

            font1.Append(fontSize1);
            font1.Append(color1);
            font1.Append(fontName1);
            font1.Append(fontFamilyNumbering1);
            font1.Append(fontCharSet1);

            fonts.Append(font1);

            return(fonts);
        }
コード例 #3
0
        /// <summary>
        ///     Creates a new font and appends it to the workbook's stylesheet
        /// </summary>
        /// <param name="styleSheet">The stylesheet for the current WorkBook</param>
        /// <param name="fontName">The font name.</param>
        /// <param name="fontSize">The font size.</param>
        /// <param name="isBold">Set to true for bold font.</param>
        /// <param name="foreColor">The font color.</param>
        /// <returns>The index of the font.</returns>
        public static UInt32Value CreateFont(
            Stylesheet styleSheet,
            string fontName,
            double?fontSize,
            bool isBold,
            Color foreColor)
        {
            // Fonts fonts = styleSheet.GetFirstChild<Fonts>();

            var font = new Font( );

            if (!string.IsNullOrEmpty(fontName))
            {
                var name = new FontName
                {
                    Val = fontName
                };
                font.Append(name);
            }

            if (fontSize.HasValue)
            {
                var size = new FontSize
                {
                    Val = fontSize.Value
                };
                font.Append(size);
            }

            if (isBold)
            {
                var bold = new Bold( );
                font.Append(bold);
            }

            var color = new DocumentFormat.OpenXml.Spreadsheet.Color
            {
                Rgb = new HexBinaryValue
                {
                    Value =
                        ColorTranslator.ToHtml(
                            Color.FromArgb(
                                foreColor.A,
                                foreColor.R,
                                foreColor.G,
                                foreColor.B)).Replace("#", "")
                }
            };

            font.Append(color);

            styleSheet.Fonts.Append(font);
            UInt32Value result = styleSheet.Fonts.Count;

            styleSheet.Fonts.Count++;
            return(result);
        }
コード例 #4
0
        private static UInt32Value CreateFont(
            Stylesheet styleSheet,
            string fontName,
            double?fontSize,
            bool isBold,
            System.Drawing.Color foreColor)
        {
            DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font();

            if (!string.IsNullOrEmpty(fontName))
            {
                FontName name = new FontName()
                {
                    Val = fontName
                };
                font.Append(name);
            }

            if (fontSize.HasValue)
            {
                DocumentFormat.OpenXml.Spreadsheet.FontSize size = new DocumentFormat.OpenXml.Spreadsheet.FontSize()
                {
                    Val = fontSize.Value
                };
                font.Append(size);
            }

            if (isBold == true)
            {
                Bold bold = new Bold();
                font.Append(bold);
            }

            DocumentFormat.OpenXml.Spreadsheet.Color color = new DocumentFormat.OpenXml.Spreadsheet.Color()
            {
                Rgb = new HexBinaryValue()
                {
                    Value =
                        System.Drawing.ColorTranslator.ToHtml(
                            System.Drawing.Color.FromArgb(
                                foreColor.A,
                                foreColor.R,
                                foreColor.G,
                                foreColor.B)).Replace("#", "")
                }
            };
            font.Append(color);

            styleSheet.Fonts.Append(font);
            UInt32Value result = styleSheet.Fonts.Count;

            styleSheet.Fonts.Count++;
            return(result);
        }
コード例 #5
0
        /// <summary>	Creates the stylesheet. </summary>
        ///
        /// <returns>	The new stylesheet. </returns>
        private static Stylesheet CreateStylesheet()
        {
            Stylesheet stylesheet = new Stylesheet();

            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);

            CellFormat cellformat0 = new CellFormat()
            {
                FontId = 0, FillId = 0, BorderId = 0
            };                                                                                  // Default style : Mandatory | Style ID =0

            CellFormat cellformat1 = new CellFormat()
            {
                FontId = 1
            };
            CellFormats cellformats = new CellFormats();

            cellformats.Append(cellformat0);
            cellformats.Append(cellformat1);

            stylesheet.Append(fonts);
            stylesheet.Append(fills);
            stylesheet.Append(borders);
            stylesheet.Append(cellformats);

            return(stylesheet);
        }
コード例 #6
0
        /// <summary>
        /// Método de agrega una hoja de estilos
        /// </summary>
        /// <param name="spreadsheet">Documento original</param>
        /// <returns></returns>
        private WorkbookStylesPart AddStyleSheet(SpreadsheetDocument spreadsheet)
        {
            WorkbookStylesPart stylesheet         = spreadsheet.WorkbookPart.AddNewPart <WorkbookStylesPart>();
            Stylesheet         workbookstylesheet = new Stylesheet();

            DocumentFormat.OpenXml.Spreadsheet.Font font0 = new DocumentFormat.OpenXml.Spreadsheet.Font();         // Default font
            DocumentFormat.OpenXml.Spreadsheet.Font font1 = new DocumentFormat.OpenXml.Spreadsheet.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()
            {
                FontId = 0, FillId = 0, BorderId = 0
            };                                                                                  // Default style : Mandatory | Style ID =0
            CellFormat cellformat1 = new CellFormat()
            {
                FontId = 1
            };                                                        // Style with Bold text ; Style ID = 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);
            // Finalize
            stylesheet.Stylesheet = workbookstylesheet;
            stylesheet.Stylesheet.Save();
            return(stylesheet);
        }
コード例 #7
0
ファイル: FontWriter.cs プロジェクト: eksotama/fluentxl
        public void Write(Font font)
        {
            if (font == null)
            {
                throw new ArgumentNullException(nameof(font));
            }

            var f = new OpenXml.Font();

            if (font.Size.HasValue)
            {
                f.Append(new OpenXml.FontSize {
                    Val = font.Size.Value
                });
            }

            if (font.Color != null)
            {
                f.Append(font.Color.MapToColor());
            }

            if (!string.IsNullOrEmpty(font.Name))
            {
                f.Append(new OpenXml.FontName {
                    Val = font.Name
                });
            }

            if (font.Bold ?? false)
            {
                f.Append(new OpenXml.Bold());
            }

            if (font.Italic ?? false)
            {
                f.Append(new OpenXml.Italic());
            }

            stylesheet.Fonts.Append(f);
        }
コード例 #8
0
        /// <summary>
        /// Create Font styles
        /// </summary>
        private static void CreateFontStyles(Fonts fts)
        {
            Font     ft  = new Font();
            FontName ftn = new FontName();

            ftn.Val = StringValue.FromString("Calibri");
            FontSize ftsz = new FontSize();

            ftsz.Val    = DoubleValue.FromDouble(11);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);

            ft          = new DocumentFormat.OpenXml.Spreadsheet.Font();
            ftn         = new FontName();
            ftn.Val     = StringValue.FromString("Palatino Linotype");
            ftsz        = new FontSize();
            ftsz.Val    = DoubleValue.FromDouble(18);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);

            fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
        }
コード例 #9
0
        }//экспорт в XLSX

        private void ExportDataSet(DataTable table, string destination)
        {
            using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                //режем тэйбл от мусора
                table.Columns.Remove("Код");
                table.Columns.Remove("ID");

                //создание workbook+sheets
                var workbookPart = workbook.AddWorkbookPart();
                workbook.WorkbookPart.Workbook        = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
                workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

                //собственный формат для даты
                //styles
                WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rId3");
                Stylesheet         stylesheet         = new Stylesheet();
                //  Date Time Display Format when s="1" is applied to cell
                NumberingFormats numberingFormats = new NumberingFormats()
                {
                    Count = (UInt32Value)1U
                };
                NumberingFormat numberingFormat = new NumberingFormat()
                {
                    NumberFormatId = (UInt32Value)164U, FormatCode = "dd.MM.yyyy"
                };
                numberingFormats.Append(numberingFormat);
                // Cell font
                Fonts fonts = new Fonts()
                {
                    Count = (UInt32Value)1U
                };
                DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font();
                FontSize fontSize = new FontSize()
                {
                    Val = 11D
                };
                FontName fontName = new FontName()
                {
                    Val = "Calibri"
                };
                font.Append(fontSize);
                font.Append(fontName);
                fonts.Append(font);
                // empty, but mandatory
                Fills fills = new Fills()
                {
                    Count = (UInt32Value)1U
                };
                Fill fill = new Fill();
                fills.Append(fill);
                Borders borders = new Borders()
                {
                    Count = (UInt32Value)1U
                };
                Border border = new Border();
                borders.Append(border);
                // cellFormat1 for text cell cellFormat2 for Datetime cell
                CellFormats cellFormats = new CellFormats()
                {
                    Count = (UInt32Value)2U
                };
                CellFormat cellFormat1 = new CellFormat()
                {
                    FontId = (UInt32Value)0U
                };
                CellFormat cellFormat2 = new CellFormat()
                {
                    NumberFormatId = (UInt32Value)164U, FontId = (UInt32Value)0U, ApplyNumberFormat = true
                };
                cellFormats.Append(cellFormat1);
                cellFormats.Append(cellFormat2);
                // Save as styles
                stylesheet.Append(numberingFormats);
                stylesheet.Append(fonts);
                stylesheet.Append(fills);
                stylesheet.Append(borders);
                stylesheet.Append(cellFormats);
                workbookStylesPart.Stylesheet = stylesheet;

                {
                    var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
                    var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                    sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

                    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                    string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                    uint sheetId = 1;
                    if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                    {
                        sheetId =
                            sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }

                    DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
                    {
                        Id = relationshipId, SheetId = sheetId, Name = "Отчет"
                    };
                    sheets.Append(sheet);

                    DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                    List <String> columns = new List <string>();

                    //создаем header в xlsx
                    foreach (System.Data.DataColumn column in table.Columns)
                    {
                        columns.Add(column.ColumnName);
                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                        headerRow.AppendChild(cell);
                    }
                    sheetData.AppendChild(headerRow);

                    //Перенос из datatable
                    foreach (System.Data.DataRow dsrow in table.Rows)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                        foreach (String col in columns)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            //проверка типа входных данных
                            Type dtype = dsrow[col].GetType();
                            switch (dtype.Name.ToString())
                            {
                            case "DateTime":
                                DateTime dt = Convert.ToDateTime(dsrow[col].ToString());
                                cell.DataType   = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
                                cell.CellValue  = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dt.ToOADate().ToString());
                                cell.StyleIndex = 1;
                                break;

                            case "String":
                                cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
                                break;

                            case "Int32":
                                cell.DataType  = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
                                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
                                break;

                            default:
                                break;
                            }
                            newRow.AppendChild(cell);
                        }

                        sheetData.AppendChild(newRow);
                    }
                }
            }
        }//вывод dataTable в xlsx с учетом типа данных
コード例 #10
0
ファイル: CustomStylesheet.cs プロジェクト: MiniKeb/OpenXML
 public CustomStylesheet()
 {
     var fonts = new Fonts();
     var font = new DocumentFormat.OpenXml.Spreadsheet.Font();
     var fontName = new FontName { Val = StringValue.FromString("Arial") };
     var fontSize = new FontSize { Val = DoubleValue.FromDouble(11) };
     font.FontName = fontName;
     font.FontSize = fontSize;
     fonts.Append(font);
     //Font Index 1
     font = new DocumentFormat.OpenXml.Spreadsheet.Font();
     fontName = new FontName { Val = StringValue.FromString("Arial") };
     fontSize = new FontSize { Val = DoubleValue.FromDouble(12) };
     font.FontName = fontName;
     font.FontSize = fontSize;
     font.Bold = new Bold();
     fonts.Append(font);
     fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
     var fills = new Fills();
     var fill = new Fill();
     var patternFill = new PatternFill { PatternType = PatternValues.None };
     fill.PatternFill = patternFill;
     fills.Append(fill);
     fill = new Fill();
     patternFill = new PatternFill { PatternType = PatternValues.Gray125 };
     fill.PatternFill = patternFill;
     fills.Append(fill);
     //Fill index  2
     fill = new Fill();
     patternFill = new PatternFill
     {
         PatternType = PatternValues.Solid,
         ForegroundColor = new ForegroundColor()
     };
     patternFill.ForegroundColor =
         TranslateForeground(System.Drawing.Color.LightBlue);
     patternFill.BackgroundColor =
         new BackgroundColor { Rgb = patternFill.ForegroundColor.Rgb };
     fill.PatternFill = patternFill;
     fills.Append(fill);
     //Fill index  3
     fill = new Fill();
     patternFill = new PatternFill
     {
         PatternType = PatternValues.Solid,
         ForegroundColor = new ForegroundColor()
     };
     patternFill.ForegroundColor =
         TranslateForeground(System.Drawing.Color.DodgerBlue);
     patternFill.BackgroundColor =
         new BackgroundColor { Rgb = patternFill.ForegroundColor.Rgb };
     fill.PatternFill = patternFill;
     fills.Append(fill);
     fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
     var borders = new Borders();
     var border = new Border
     {
         LeftBorder = new LeftBorder(),
         RightBorder = new RightBorder(),
         TopBorder = new TopBorder(),
         BottomBorder = new BottomBorder(),
         DiagonalBorder = new DiagonalBorder()
     };
     borders.Append(border);
     //All Boarder Index 1
     border = new Border
     {
         LeftBorder = new LeftBorder { Style = BorderStyleValues.Thin },
         RightBorder = new RightBorder { Style = BorderStyleValues.Thin },
         TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
         BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
         DiagonalBorder = new DiagonalBorder()
     };
     borders.Append(border);
     //Top and Bottom Boarder Index 2
     border = new Border
     {
         LeftBorder = new LeftBorder(),
         RightBorder = new RightBorder(),
         TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
         BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
         DiagonalBorder = new DiagonalBorder()
     };
     borders.Append(border);
     borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
     var cellStyleFormats = new CellStyleFormats();
     var cellFormat = new CellFormat
     {
         NumberFormatId = 0,
         FontId = 0,
         FillId = 0,
         BorderId = 0
     };
     cellStyleFormats.Append(cellFormat);
     cellStyleFormats.Count =
         UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
     uint iExcelIndex = 164;
     var numberingFormats = new NumberingFormats();
     var cellFormats = new CellFormats();
     cellFormat = new CellFormat
     {
         NumberFormatId = 0,
         FontId = 0,
         FillId = 0,
         BorderId = 0,
         FormatId = 0
     };
     cellFormats.Append(cellFormat);
     var nformatDateTime = new NumberingFormat
     {
         NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
         FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
     };
     numberingFormats.Append(nformatDateTime);
     var nformat4Decimal = new NumberingFormat
     {
         NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
         FormatCode = StringValue.FromString("#,##0.0000")
     };
     numberingFormats.Append(nformat4Decimal);
     var nformat2Decimal = new NumberingFormat
     {
         NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
         FormatCode = StringValue.FromString("#,##0.00")
     };
     numberingFormats.Append(nformat2Decimal);
     var nformatForcedText = new NumberingFormat
     {
         NumberFormatId = UInt32Value.FromUInt32(iExcelIndex),
         FormatCode = StringValue.FromString("@")
     };
     numberingFormats.Append(nformatForcedText);
     // index 1
     // Cell Standard Date format
     cellFormat = new CellFormat
     {
         NumberFormatId = 14,
         FontId = 0,
         FillId = 0,
         BorderId = 0,
         FormatId = 0,
         ApplyNumberFormat = BooleanValue.FromBoolean(true)
     };
     cellFormats.Append(cellFormat);
     // Index 2
     // Cell Standard Number format with 2 decimal placing
     cellFormat = new CellFormat
     {
         NumberFormatId = 4,
         FontId = 0,
         FillId = 0,
         BorderId = 0,
         FormatId = 0,
         ApplyNumberFormat = BooleanValue.FromBoolean(true)
     };
     cellFormats.Append(cellFormat);
     // Index 3
     // Cell Date time custom format
     cellFormat = new CellFormat
     {
         NumberFormatId = nformatDateTime.NumberFormatId,
         FontId = 0,
         FillId = 0,
         BorderId = 0,
         FormatId = 0,
         ApplyNumberFormat = BooleanValue.FromBoolean(true)
     };
     cellFormats.Append(cellFormat);
     // Index 4
     // Cell 4 decimal custom format
     cellFormat = new CellFormat
     {
         NumberFormatId = nformat4Decimal.NumberFormatId,
         FontId = 0,
         FillId = 0,
         BorderId = 0,
         FormatId = 0,
         ApplyNumberFormat = BooleanValue.FromBoolean(true)
     };
     cellFormats.Append(cellFormat);
     // Index 5
     // Cell 2 decimal custom format
     cellFormat = new CellFormat
     {
         NumberFormatId = nformat2Decimal.NumberFormatId,
         FontId = 0,
         FillId = 0,
         BorderId = 0,
         FormatId = 0,
         ApplyNumberFormat = BooleanValue.FromBoolean(true)
     };
     cellFormats.Append(cellFormat);
     // Index 6
     // Cell forced number text custom format
     cellFormat = new CellFormat
     {
         NumberFormatId = nformatForcedText.NumberFormatId,
         FontId = 0,
         FillId = 0,
         BorderId = 0,
         FormatId = 0,
         ApplyNumberFormat = BooleanValue.FromBoolean(true)
     };
     cellFormats.Append(cellFormat);
     // Index 7
     // Cell text with font 12
     cellFormat = new CellFormat
     {
         NumberFormatId = nformatForcedText.NumberFormatId,
         FontId = 1,
         FillId = 0,
         BorderId = 0,
         FormatId = 0,
         ApplyNumberFormat = BooleanValue.FromBoolean(true)
     };
     cellFormats.Append(cellFormat);
     // Index 8
     // Cell text
     cellFormat = new CellFormat
     {
         NumberFormatId = nformatForcedText.NumberFormatId,
         FontId = 0,
         FillId = 0,
         BorderId = 1,
         FormatId = 0,
         ApplyNumberFormat = BooleanValue.FromBoolean(true)
     };
     cellFormats.Append(cellFormat);
     // Index 9
     // Coloured 2 decimal cell text
     cellFormat = new CellFormat
     {
         NumberFormatId = nformat2Decimal.NumberFormatId,
         FontId = 0,
         FillId = 2,
         BorderId = 2,
         FormatId = 0,
         ApplyNumberFormat = BooleanValue.FromBoolean(true)
     };
     cellFormats.Append(cellFormat);
     // Index 10
     // Coloured cell text
     cellFormat = new CellFormat
     {
         NumberFormatId = nformatForcedText.NumberFormatId,
         FontId = 0,
         FillId = 2,
         BorderId = 2,
         FormatId = 0,
         ApplyNumberFormat = BooleanValue.FromBoolean(true)
     };
     cellFormats.Append(cellFormat);
     // Index 11
     // Coloured cell text
     cellFormat = new CellFormat
     {
         NumberFormatId = nformatForcedText.NumberFormatId,
         FontId = 1,
         FillId = 3,
         BorderId = 2,
         FormatId = 0,
         ApplyNumberFormat = BooleanValue.FromBoolean(true)
     };
     cellFormats.Append(cellFormat);
     numberingFormats.Count =
         UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);
     cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
     this.Append(numberingFormats);
     this.Append(fonts);
     this.Append(fills);
     this.Append(borders);
     this.Append(cellStyleFormats);
     this.Append(cellFormats);
     var css = new CellStyles();
     var cs = new CellStyle
     {
         Name = StringValue.FromString("Normal"),
         FormatId = 0,
         BuiltinId = 0
     };
     css.Append(cs);
     css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
     this.Append(css);
     var dfs = new DifferentialFormats { Count = 0 };
     this.Append(dfs);
     var tss = new TableStyles
     {
         Count = 0,
         DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
         DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
     };
     this.Append(tss);
 }
コード例 #11
0
        private static Stylesheet CreateStylesheet()
        {
            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
            };

            //Normal Font
            DocumentFormat.OpenXml.Spreadsheet.Font font1 =
                new DocumentFormat.OpenXml.Spreadsheet.Font();
            DocumentFormat.OpenXml.Spreadsheet.FontSize fontSize1 =
                new DocumentFormat.OpenXml.Spreadsheet.FontSize()
            {
                Val = 11D
            };
            DocumentFormat.OpenXml.Spreadsheet.Color color1 =
                new DocumentFormat.OpenXml.Spreadsheet.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);
            fonts1.Append(font1);

            //Bold Font
            DocumentFormat.OpenXml.Spreadsheet.Font bFont =
                new DocumentFormat.OpenXml.Spreadsheet.Font();
            DocumentFormat.OpenXml.Spreadsheet.FontSize bfontSize =
                new DocumentFormat.OpenXml.Spreadsheet.FontSize()
            {
                Val = 11D
            };
            DocumentFormat.OpenXml.Spreadsheet.Color bcolor =
                new DocumentFormat.OpenXml.Spreadsheet.Color()
            {
                Theme = (UInt32Value)1U
            };
            FontName bfontName = new FontName()
            {
                Val = "Calibri"
            };
            FontFamilyNumbering bfontFamilyNumbering =
                new FontFamilyNumbering()
            {
                Val = 2
            };
            FontScheme bfontScheme = new FontScheme()
            {
                Val = FontSchemeValues.Minor
            };
            Bold bFontBold = new Bold();

            bFont.Append(bfontSize);
            bFont.Append(bcolor);
            bFont.Append(bfontName);
            bFont.Append(bfontFamilyNumbering);
            bFont.Append(bfontScheme);
            bFont.Append(bFontBold);

            fonts1.Append(bFont);


            Fills fills1 = new Fills()
            {
                Count = (UInt32Value)6U
            };

            // FillId = 0
            Fill        fill1        = new Fill();
            PatternFill patternFill1 = new PatternFill()
            {
                PatternType = PatternValues.None
            };

            fill1.Append(patternFill1);

            // FillId = 1
            Fill        fill2        = new Fill();
            PatternFill patternFill2 = new PatternFill()
            {
                PatternType = PatternValues.Gray125
            };

            fill2.Append(patternFill2);

            // FillId = 2,RED
            Fill        fill3        = new Fill();
            PatternFill patternFill3 = new PatternFill()
            {
                PatternType = PatternValues.Solid
            };
            ForegroundColor foregroundColor1 = new ForegroundColor()
            {
                Rgb = "5c881a"
            };
            BackgroundColor backgroundColor1 = new BackgroundColor()
            {
                Indexed = (UInt32Value)64U
            };                                                                                       //

            patternFill3.Append(foregroundColor1);
            patternFill3.Append(backgroundColor1);
            fill3.Append(patternFill3);

            // FillId = 3,BLUE
            Fill        fill4        = new Fill();
            PatternFill patternFill4 = new PatternFill()
            {
                PatternType = PatternValues.Solid
            };
            ForegroundColor foregroundColor2 = new ForegroundColor()
            {
                Rgb = "0070c0"
            };
            BackgroundColor backgroundColor2 = new BackgroundColor()
            {
                Indexed = (UInt32Value)64U
            };

            patternFill4.Append(foregroundColor2);
            patternFill4.Append(backgroundColor2);
            fill4.Append(patternFill4);

            // FillId = 4,YELLO
            Fill        fill5        = new Fill();
            PatternFill patternFill5 = new PatternFill()
            {
                PatternType = PatternValues.Solid
            };
            ForegroundColor foregroundColor3 = new ForegroundColor()
            {
                Rgb = "FFFFFF00"
            };
            BackgroundColor backgroundColor3 = new BackgroundColor()
            {
                Indexed = (UInt32Value)64U
            };

            patternFill5.Append(foregroundColor3);
            patternFill5.Append(backgroundColor3);
            fill5.Append(patternFill5);

            // FillId = 5,RED and BOLD Text
            Fill        fill6        = new Fill();
            PatternFill patternFill6 = new PatternFill()
            {
                PatternType = PatternValues.Solid
            };
            ForegroundColor foregroundColor4 = new ForegroundColor()
            {
                Rgb = "5c881a"
            };
            BackgroundColor backgroundColor4 = new BackgroundColor()
            {
                Indexed = (UInt32Value)64U
            };
            Bold bold1 = new Bold();

            patternFill6.Append(bold1);
            patternFill6.Append(foregroundColor4);
            patternFill6.Append(backgroundColor4);
            fill6.Append(patternFill6);


            fills1.Append(fill1);
            fills1.Append(fill2);
            fills1.Append(fill3);
            fills1.Append(fill4);
            fills1.Append(fill5);
            fills1.Append(fill6);

            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)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true
            };
            CellFormat cellFormat4 = new CellFormat()
            {
                NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true
            };
            CellFormat cellFormat5 = new CellFormat()
            {
                NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = 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 = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
            };

            cellStyles1.Append(cellStyle1);
            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);

            stylesheetExtensionList1.Append(stylesheetExtension1);

            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);
        }
コード例 #12
0
        private void ExporttoExcelXML(DataSet ds)
        {
            string strPathReports = GetPathUploadReports();
            string strNamefile    = DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xlsx";
            string strFullPath    = Server.MapPath(strPathReports) + strNamefile;

            var stream   = new MemoryStream();
            var document = SpreadsheetDocument.Create(strFullPath, SpreadsheetDocumentType.Workbook);


            var workbookpart = document.AddWorkbookPart();

            workbookpart.Workbook = new  DocumentFormat.OpenXml.Spreadsheet.Workbook();
            var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();

            Worksheet          ws   = new Worksheet();
            WorkbookStylesPart wbsp = workbookpart.AddNewPart <WorkbookStylesPart>();

            // add styles to sheet
            wbsp.Stylesheet = CreateStylesheet();
            wbsp.Stylesheet.Save();

            var sheetData = new SheetData();

            worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

            var sheets = document.WorkbookPart.Workbook.
                         AppendChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(new DocumentFormat.OpenXml.Spreadsheet.Sheets());

            var sheet = new Sheet()
            {
                Id      = document.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name    = "Resumen General" //data.SheetName ?? "Sheet 1"
            };

            sheets.AppendChild(sheet);



            UInt32 rowIdex = 0;
            var    row     = new Row {
                RowIndex = ++rowIdex
            };

            Fonts fts = new Fonts();

            DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
            Bold     fbld = new Bold();
            FontName ftn  = new FontName();

            ftn.Val = "Calibri";
            DocumentFormat.OpenXml.Spreadsheet.FontSize ftsz = new DocumentFormat.OpenXml.Spreadsheet.FontSize();
            ftsz.Val    = 11;
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            ft.Bold     = fbld;
            fts.Append(ft);
            fts.Count = (uint)fts.ChildElements.Count;
            row.Append(fts);

            sheetData.AppendChild(row);
            row.StyleIndex = (UInt32Value)1U;

            var cellIdex = 0;


            int    intCount = 0;
            string strX     = string.Empty;
            string strY     = string.Empty;

            foreach (System.Data.DataTable table in ds.Tables)
            {
                for (int i = 1; i < table.Columns.Count + 1; i++)
                {
                    //excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName.ToString().ToUpper();

                    Cell cel = CreateTextCellHeader(ColumnLetter(cellIdex++), rowIdex, table.Columns[i - 1].ColumnName.ToString().ToUpper() ?? string.Empty);
                    if (i < 9)
                    {
                        cel.StyleIndex = (UInt32Value)1U;
                    }

                    if (i > 10 && i < 15)
                    {
                        cel.StyleIndex = (UInt32Value)2U;
                    }

                    if (i > 15 && i < 20)
                    {
                        cel.StyleIndex = (UInt32Value)4;
                    }

                    if (i > 20 && i < 25)
                    {
                        cel.StyleIndex = (UInt32Value)5U;
                    }

                    if (i > 30 && i < 35)
                    {
                        cel.StyleIndex = (UInt32Value)6U;
                    }


                    row.AppendChild(cel);

                    //((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, i]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
                    //((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, i]).Font.Bold = true;
                    //((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, i]).Font.Name = "Calibir";
                    //((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, i]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                    //  REQUIERE TC Y TP
                    if ((table.Columns[i - 1].ColumnName.ToString().ToUpper() == "REQUIERE TC Y TP")
                        ||
                        (table.Columns[i - 1].ColumnName.ToString().ToUpper() == "REQUIERE REUBICACIÓN")
                        &&
                        intCount == 0)
                    {
                        intCount = i;
                    }
                    //((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, i]).Style.Name = "Normal";
                }

                for (int j = 0; j < table.Rows.Count; j++)
                {
                    //rowIdex = 1;
                    row = new Row {
                        RowIndex = ++rowIdex
                    };


                    sheetData.AppendChild(row);
                    cellIdex = 0;

                    for (int k = 0; k < table.Columns.Count; k++)
                    {
                        if ((k == 2))
                        {
                            //((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[j + 2, k + 1]).NumberFormat = "#####";
                        }
                        if ((k == 4))
                        {
                            //    ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[j + 2, k + 1]).NumberFormat = "##,###.##";
                        }
                        //excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
                        row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, table.Rows[j].ItemArray[k].ToString() ?? string.Empty));
                    }
                }
            }


            workbookpart.Workbook.Save();
            document.Close();
            string strUrl = "Bajarresumengral.aspx?n=" + strNamefile;

            Response.Redirect(strUrl, true);
        }
コード例 #13
0
        public CustomStylesheet()
        {
            Fonts fts = new Fonts();
            DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
            FontName ftn = new FontName();
            ftn.Val = StringValue.FromString("Calibri");
            FontSize ftsz = new FontSize();
            ftsz.Val = DoubleValue.FromDouble(11);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);

            ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
            ftn = new FontName();
            ftn.Val = StringValue.FromString("Palatino Linotype");
            ftsz = new FontSize();
            ftsz.Val = DoubleValue.FromDouble(18);
            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 = patternFill.ForegroundColor.Rgb;
            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);

            //Boarder Index 1
            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);

            //Boarder Index 2
            border = new Border();
            border.LeftBorder = new LeftBorder();
            border.RightBorder = new RightBorder();
            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 = 0;
            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);

            // index 1
            // Format dd/mm/yyyy
            cf = new CellFormat();
            cf.NumberFormatId = 14;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 2
            // Format #,##0.00
            cf = new CellFormat();
            cf.NumberFormatId = 4;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 3
            cf = new CellFormat();
            cf.NumberFormatId = nfDateTime.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 4
            cf = new CellFormat();
            cf.NumberFormatId = nf4decimal.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 5
            cf = new CellFormat();
            cf.NumberFormatId = nf2decimal.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 6
            cf = new CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 7
            // 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 8
            // 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
            // coloured 2 decimal text
            cf = new CellFormat();
            cf.NumberFormatId = nf2decimal.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 2;
            cf.BorderId = 2;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 10
            // coloured column text
            cf = new CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 2;
            cf.BorderId = 2;
            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);

            this.Append(nfs);
            this.Append(fts);
            this.Append(fills);
            this.Append(borders);
            this.Append(csfs);
            this.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);
            this.Append(css);

            DifferentialFormats dfs = new DifferentialFormats();
            dfs.Count = 0;
            this.Append(dfs);

            TableStyles tss = new TableStyles();
            tss.Count = 0;
            tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
            tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
            this.Append(tss);
        }
コード例 #14
0
        static private void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart1)
        {
            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
            };

            DocumentFormat.OpenXml.Spreadsheet.Font font1 = new DocumentFormat.OpenXml.Spreadsheet.Font();
            FontSize fontSize1 = new FontSize()
            {
                Val = 11D
            };

            DocumentFormat.OpenXml.Spreadsheet.Color color1 = new DocumentFormat.OpenXml.Spreadsheet.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);

            DocumentFormat.OpenXml.Spreadsheet.Font font2 = new DocumentFormat.OpenXml.Spreadsheet.Font();
            Bold     bold1     = new Bold();
            FontSize fontSize2 = new FontSize()
            {
                Val = 11D
            };

            DocumentFormat.OpenXml.Spreadsheet.Color color2 = new DocumentFormat.OpenXml.Spreadsheet.Color()
            {
                Theme = (UInt32Value)1U
            };
            FontName fontName2 = new FontName()
            {
                Val = "Calibri"
            };
            FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
            {
                Val = 2
            };
            FontScheme fontScheme2 = new FontScheme()
            {
                Val = FontSchemeValues.Minor
            };

            font2.Append(bold1);
            font2.Append(fontSize2);
            font2.Append(color2);
            font2.Append(fontName2);
            font2.Append(fontFamilyNumbering2);
            font2.Append(fontScheme2);

            fonts1.Append(font1);
            fonts1.Append(font2);

            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)2U
            };

            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);

            Border border2 = new Border();

            LeftBorder leftBorder2 = new LeftBorder()
            {
                Style = BorderStyleValues.Thin
            };

            DocumentFormat.OpenXml.Spreadsheet.Color color3 = new DocumentFormat.OpenXml.Spreadsheet.Color()
            {
                Indexed = (UInt32Value)64U
            };

            leftBorder2.Append(color3);

            RightBorder rightBorder2 = new RightBorder()
            {
                Style = BorderStyleValues.Thin
            };

            DocumentFormat.OpenXml.Spreadsheet.Color color4 = new DocumentFormat.OpenXml.Spreadsheet.Color()
            {
                Indexed = (UInt32Value)64U
            };

            rightBorder2.Append(color4);

            TopBorder topBorder2 = new TopBorder()
            {
                Style = BorderStyleValues.Thin
            };

            DocumentFormat.OpenXml.Spreadsheet.Color color5 = new DocumentFormat.OpenXml.Spreadsheet.Color()
            {
                Indexed = (UInt32Value)64U
            };

            topBorder2.Append(color5);

            BottomBorder bottomBorder2 = new BottomBorder()
            {
                Style = BorderStyleValues.Thin
            };

            DocumentFormat.OpenXml.Spreadsheet.Color color6 = new DocumentFormat.OpenXml.Spreadsheet.Color()
            {
                Indexed = (UInt32Value)64U
            };

            bottomBorder2.Append(color6);
            DiagonalBorder diagonalBorder2 = new DiagonalBorder();

            border2.Append(leftBorder2);
            border2.Append(rightBorder2);
            border2.Append(topBorder2);
            border2.Append(bottomBorder2);
            border2.Append(diagonalBorder2);

            borders1.Append(border1);
            borders1.Append(border2);

            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)3U
            };
            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)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyBorder = true
            };
            CellFormat cellFormat4 = new CellFormat()
            {
                NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyBorder = true
            };

            cellFormats1.Append(cellFormat2);
            cellFormats1.Append(cellFormat3);
            cellFormats1.Append(cellFormat4);

            CellStyles cellStyles1 = new CellStyles()
            {
                Count = (UInt32Value)1U
            };
            CellStyle cellStyle1 = new CellStyle()
            {
                Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
            };

            cellStyles1.Append(cellStyle1);
            DifferentialFormats differentialFormats1 = new DifferentialFormats()
            {
                Count = (UInt32Value)0U
            };
            TableStyles tableStyles1 = new TableStyles()
            {
                Count = (UInt32Value)0U, 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);

            workbookStylesPart1.Stylesheet = stylesheet1;
        }
コード例 #15
0
        private int? GetStyleIndex(Style style,Row row, ref string value)
        {
            StyleInfo pt = GetStyle(style, row);
            // DocumentFormat.OpenXml.Spreadsheet.Fonts fonts1 = new DocumentFormat.OpenXml.Spreadsheet.Fonts()
            //                                                     { Count = (UInt32Value)1U, KnownFonts = true };
            int? fontId = null;
            int? borderId = null;
            int? cellFormatId = null;

            DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font();

            if (pt.IsFontBold())
                font.Append(new DocumentFormat.OpenXml.Spreadsheet.Bold());
            if (pt.FontStyle == FontStyleEnum.Italic)
                font.Append(new DocumentFormat.OpenXml.Spreadsheet.Italic());

            font.Append(new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = (Double)pt.FontSize });
            font.Append(new DocumentFormat.OpenXml.Spreadsheet.FontName() { Val = pt.FontFamily });
            //font.Append(new DocumentFormat.OpenXml.Spreadsheet.Color()
            //            {  Rgb=GetColor(si.Color) });

            int id = 0;
            foreach (var fo in _styleSheet.Fonts)
            {
                if (fo.OuterXml.Equals(font.OuterXml))
                {
                    fontId = id;
                    break;
                }
                id++;
            }

            if (fontId == null)
            {
                _styleSheet.Fonts.Append(font);
                _styleSheet.Fonts.Count = (uint)_styleSheet.Fonts.ChildElements.Count;
                fontId = _styleSheet.Fonts.ChildElements.Count - 1;
            }

            Border border = new Border();
            if (pt.BStyleLeft != BorderStyleEnum.None)
            {
                border.LeftBorder = new LeftBorder() { Style = GetBorderStyle(pt.BStyleLeft) };
            }
            if (pt.BStyleRight != BorderStyleEnum.None)
            {
                border.RightBorder = new RightBorder() { Style = GetBorderStyle(pt.BStyleRight) };
            }
            if (pt.BStyleTop != BorderStyleEnum.None)
            {
                border.TopBorder = new TopBorder() { Style = GetBorderStyle(pt.BStyleTop) };
            }
            if (pt.BStyleBottom != BorderStyleEnum.None)
            {
                border.BottomBorder = new BottomBorder() { Style = GetBorderStyle(pt.BStyleBottom) };
            }

            id = 0;
            foreach (var bo in _styleSheet.Borders)
            {
                if (bo.OuterXml.Equals(border.OuterXml))
                {
                    borderId = id;
                    break;
                }
                id++;
            }

            if (borderId == null)
            {
                _styleSheet.Borders.Append(border);
                _styleSheet.Borders.Count = (uint)_styleSheet.Borders.ChildElements.Count;
                borderId = _styleSheet.Borders.ChildElements.Count - 1;
            }

            value = NumericValue(value) ?? value;

            CellFormat cf = new CellFormat();
            cf.NumberFormatId = (uint)StyleInfo.GetFormatCode(pt._Format);
            cf.FontId = (uint)fontId;
            cf.FillId = 0;
            cf.BorderId = (uint)borderId;
            // cf.FormatId = 0;

            id = 0;
            foreach (var cef in _styleSheet.CellFormats)
            {
                if (cef.OuterXml.Equals(cf.OuterXml))
                {
                    cellFormatId = id;
                    break;
                }
                id++;
            }

            if (cellFormatId == null)
            {
                _styleSheet.CellFormats.Append(cf);
                _styleSheet.CellFormats.Count = (uint)_styleSheet.CellFormats.ChildElements.Count;
                cellFormatId = _styleSheet.CellFormats.ChildElements.Count - 1;
            }

            return cellFormatId;
        }
コード例 #16
0
        private static Fonts CreateFonts()
        {
            var fts = new Fonts();

            // font 0
            var ft  = new Font();
            var ftn = new FontName {
                Val = StringValue.FromString("Arial")
            };
            var ftsz = new FontSize {
                Val = DoubleValue.FromDouble(11)
            };

            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.AppendChild(ft);

            // font 1
            ft  = new Font();
            ftn = new FontName {
                Val = StringValue.FromString("Arial")
            };
            ftsz = new FontSize {
                Val = DoubleValue.FromDouble(12)
            };
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            ft.Bold     = new Bold();
            fts.AppendChild(ft);

            // font 2
            ft  = new Font();
            ftn = new FontName {
                Val = StringValue.FromString("Arial")
            };
            ftsz = new FontSize {
                Val = DoubleValue.FromDouble(18)
            };
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            ft.Bold     = new Bold();
            fts.AppendChild(ft);

            // font 3
            ft  = new Font();
            ftn = new FontName {
                Val = StringValue.FromString("Arial")
            };
            ftsz = new FontSize {
                Val = DoubleValue.FromDouble(14)
            };
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.AppendChild(ft);

            // font 4
            ft  = new Font();
            ftn = new FontName {
                Val = StringValue.FromString("Arial")
            };
            ftsz = new FontSize {
                Val = DoubleValue.FromDouble(11)
            };
            var fontColor = Color.MediumBlue;

            ft.Color = new DocumentFormat.OpenXml.Spreadsheet.Color()
            {
                Rgb = HexBinaryValueFromColor(fontColor)
            };
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.AppendChild(ft);

            fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
            return(fts);
        }
コード例 #17
0
        static Stylesheet CreateStylesheet()
        {
            Stylesheet ss = new Stylesheet();

            #region fts (fonts)
            Fonts    fts = new Fonts();
            var      ft  = new DocumentFormat.OpenXml.Spreadsheet.Font();
            FontName ftn = new FontName();
            ftn.Val = StringValue.FromString("Arial");
            FontSize ftsz = new FontSize();
            ftsz.Val    = DoubleValue.FromDouble(11);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);

            ft          = new DocumentFormat.OpenXml.Spreadsheet.Font();
            ftn         = new FontName();
            ftn.Val     = StringValue.FromString("Verdana");
            ftsz        = new FontSize();
            ftsz.Val    = DoubleValue.FromDouble(18);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);

            ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
            ft.Append(new Bold());
            ftn         = new FontName();
            ftn.Val     = StringValue.FromString("Arial");
            ftsz        = new FontSize();
            ftsz.Val    = DoubleValue.FromDouble(11);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);

            fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
            #endregion

            #region fills
            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("00efffd9");
            patternFill.BackgroundColor     = new BackgroundColor();
            patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
            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("00f8e8d6");
            patternFill.BackgroundColor     = new BackgroundColor();
            patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
            fill.PatternFill                = patternFill;
            fills.Append(fill);

            fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
            #endregion

            #region borders
            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);
            #endregion

            #region csfs (CellStyleFormats)
            CellStyleFormats csfs = new CellStyleFormats();
            CellFormat       cf   = new CellFormat();
            cf.NumberFormatId = 0;
            cf.FontId         = 0;
            cf.FillId         = 0;
            cf.BorderId       = 0;
            csfs.Append(cf);
            csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count);
            #endregion


            uint        iExcelIndex = 164;
            var         nfs         = new DocumentFormat.OpenXml.Spreadsheet.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);

            var nfDateTime = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
            nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nfDateTime.FormatCode     = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
            nfs.Append(nfDateTime);

            var nfDate = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
            nfDate.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nfDate.FormatCode     = StringValue.FromString("dd/mm/yyyy");
            nfs.Append(nfDate);

            var nf4decimal = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
            nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nf4decimal.FormatCode     = StringValue.FromString("#,##0.0000");
            nfs.Append(nf4decimal);

            // #,##0.00 is also Excel style index 4
            var nf2decimal = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
            nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nf2decimal.FormatCode     = StringValue.FromString("#,##0.00");
            nfs.Append(nf2decimal);

            // @ is also Excel style index 49
            var nfForcedText = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
            nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nfForcedText.FormatCode     = StringValue.FromString("@");
            nfs.Append(nfForcedText);

            // #,##0.00 is also Excel style index 4
            var nf0decimal = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
            nf0decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nf0decimal.FormatCode     = StringValue.FromString("#,##0");
            nfs.Append(nf0decimal);

            // index 1
            cf = new CellFormat();
            cf.NumberFormatId    = nfDate.NumberFormatId;
            cf.FontId            = 0; //Arial 11
            cf.FillId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 2
            cf = new CellFormat();
            cf.NumberFormatId    = nf4decimal.NumberFormatId;
            cf.FontId            = 0; //Arial 11
            cf.FillId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 3
            cf = new CellFormat();
            cf.NumberFormatId    = nf0decimal.NumberFormatId;
            cf.FontId            = 0; //Arial 11
            cf.FillId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 4
            cf = new CellFormat();
            cf.NumberFormatId    = nfForcedText.NumberFormatId;
            cf.FontId            = 0; //Arial 11
            cf.FillId            = 0; //NO Fill
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 5
            cf = new CellFormat();
            cf.NumberFormatId    = nfForcedText.NumberFormatId;
            cf.FontId            = 1; //Verdana 18
            cf.FillId            = 0; //NO Fill
            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            = 2; //Arial 11, Bold
            cf.FillId            = 0; //NO Fill
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 7
            cf = new CellFormat();
            cf.NumberFormatId    = nfForcedText.NumberFormatId;
            cf.FontId            = 0; //Arial 11
            cf.FillId            = 3; //Light Orange
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 8
            // column text
            cf = new CellFormat();
            cf.NumberFormatId    = nfForcedText.NumberFormatId;
            cf.FontId            = 2; //Arial 11, Bold
            cf.FillId            = 2; //Light Green
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 9
            cf = new CellFormat();
            cf.NumberFormatId    = nfForcedText.NumberFormatId;
            cf.FontId            = 0; //Arial 11
            cf.FillId            = 0; //NO Fill
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cf.Append(new Alignment()
            {
                WrapText = true
            });
            cfs.Append(cf);

            // index 10
            cf = new CellFormat();
            cf.NumberFormatId    = nfForcedText.NumberFormatId;
            cf.FontId            = 0; //Arial 11
            cf.FillId            = 3; //Light Orange
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cf.Append(new Alignment()
            {
                WrapText = 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);
        }
コード例 #18
0
        private static Stylesheet CreateStylesheet()
        {
            Stylesheet stylesheet1 = new Stylesheet();

            DocumentFormat.OpenXml.Spreadsheet.Fonts fonts1 = new DocumentFormat.OpenXml.Spreadsheet.Fonts()
            {
                Count = (UInt32Value)1U, KnownFonts = true
            };

            DocumentFormat.OpenXml.Spreadsheet.Font font1 = new DocumentFormat.OpenXml.Spreadsheet.Font();
            FontSize fontSize1 = new FontSize()
            {
                Val = 11
            };
            Color color1 = new Color()
            {
                Theme = (UInt32Value)1U
            };
            FontName fontName1 = new FontName()
            {
                Val = "Calibri"
            };
            FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering()
            {
                Val = 2
            };

            DocumentFormat.OpenXml.Spreadsheet.FontScheme fontScheme1 = new DocumentFormat.OpenXml.Spreadsheet.FontScheme()
            {
                Val = FontSchemeValues.Minor
            };
            font1.Append(fontSize1);
            font1.Append(color1);
            font1.Append(fontName1);
            font1.Append(fontFamilyNumbering1);
            font1.Append(fontScheme1);

            DocumentFormat.OpenXml.Spreadsheet.Font font2 = new DocumentFormat.OpenXml.Spreadsheet.Font();
            FontSize fontSize2 = new FontSize()
            {
                Val = 14
            };
            Color color2 = new Color()
            {
                Rgb = "FF0070C0"
            };
            FontName fontName2 = new FontName()
            {
                Val = "Calibri"
            };
            FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
            {
                Val = 2
            };

            DocumentFormat.OpenXml.Spreadsheet.FontScheme fontScheme2 = new DocumentFormat.OpenXml.Spreadsheet.FontScheme()
            {
                Val = FontSchemeValues.Minor
            };
            font2.Append(fontSize1);
            font1.Append(color2);
            font1.Append(fontName2);
            font1.Append(fontFamilyNumbering2);
            font1.Append(fontScheme2);

            fonts1.Append(font1);
            fonts1.Append(font2);

            CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
            {
                Count = (UInt32Value)1U
            };
            CellFormat cellFormat1 = new CellFormat()
            {
                NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U
            };

            cellStyleFormats1.Append(cellFormat1);

            CellFormats cellFormats1 = new CellFormats()
            {
                Count = (UInt32Value)4U
            };
            CellFormat cellFormat2 = new CellFormat()
            {
                NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U
            };
            CellFormat cellFormat3 = new CellFormat()
            {
                NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U
            };

            cellFormats1.Append(cellFormat2);
            cellFormats1.Append(cellFormat3);

            CellStyles cellStyles1 = new CellStyles()
            {
                Count = (UInt32Value)1U
            };
            CellStyle cellStyle1 = new CellStyle()
            {
                Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
            };

            cellStyles1.Append(cellStyle1);

            stylesheet1.Append(fonts1);
            stylesheet1.Append(cellStyleFormats1);
            stylesheet1.Append(cellFormats1);
            stylesheet1.Append(cellStyles1);
            return(stylesheet1);
        }
コード例 #19
0
        private static MSOpenXML.Stylesheet CreateStylesheet(int pQuantidadeDecimais = 2)
        {
            MSOpenXML.Stylesheet ss = new MSOpenXML.Stylesheet();

            #region Fontes
            MSOpenXML.Fonts    fts = new MSOpenXML.Fonts();
            MSOpenXML.Font     ft  = new MSOpenXML.Font();
            MSOpenXML.FontName ftn = new MSOpenXML.FontName();
            ftn.Val = "Calibri";
            MSOpenXML.FontSize ftsz = new MSOpenXML.FontSize();
            ftsz.Val    = 11;
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);

            ft          = new MSOpenXML.Font();
            ft.Bold     = new MSOpenXML.Bold();
            ftn         = new MSOpenXML.FontName();
            ftn.Val     = "Calibri";
            ftsz        = new MSOpenXML.FontSize();
            ftsz.Val    = 11;
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);

            fts.Count = (uint)fts.ChildElements.Count;
            #endregion

            #region Preenchimento
            MSOpenXML.Fills       fills = new MSOpenXML.Fills();
            MSOpenXML.Fill        fill;
            MSOpenXML.PatternFill patternFill;
            fill                    = new MSOpenXML.Fill();
            patternFill             = new MSOpenXML.PatternFill();
            patternFill.PatternType = MSOpenXML.PatternValues.None;
            fill.PatternFill        = patternFill;
            fills.Append(fill);

            /*fill = new dos.Fill();
             * patternFill = new dos.PatternFill();
             * patternFill.PatternType = dos.PatternValues.Gray125;
             * fill.PatternFill = patternFill;
             * fills.Append(fill);
             *
             * fill = new dos.Fill();
             * patternFill = new dos.PatternFill();
             * patternFill.PatternType = dos.PatternValues.Solid;
             * patternFill.ForegroundColor = new dos.ForegroundColor();
             * patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00ff9728");
             * patternFill.BackgroundColor = new dos.BackgroundColor();
             * patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
             * fill.PatternFill = patternFill;
             * fills.Append(fill);
             */
            fills.Count = (uint)fills.ChildElements.Count;

            #endregion

            #region Bordas
            MSOpenXML.Borders borders = new MSOpenXML.Borders();

            MSOpenXML.Border border = new MSOpenXML.Border();
            border.LeftBorder     = new MSOpenXML.LeftBorder();
            border.RightBorder    = new MSOpenXML.RightBorder();
            border.TopBorder      = new MSOpenXML.TopBorder();
            border.BottomBorder   = new MSOpenXML.BottomBorder();
            border.DiagonalBorder = new MSOpenXML.DiagonalBorder();
            borders.Append(border);

            border                 = new MSOpenXML.Border();
            border.LeftBorder      = new MSOpenXML.LeftBorder();
            border.RightBorder     = new MSOpenXML.RightBorder();
            border.TopBorder       = new MSOpenXML.TopBorder();
            border.TopBorder.Style = MSOpenXML.BorderStyleValues.Thin;
            border.BottomBorder    = new MSOpenXML.BottomBorder();
            border.DiagonalBorder  = new MSOpenXML.DiagonalBorder();
            borders.Append(border);
            borders.Count = (uint)borders.ChildElements.Count;
            #endregion

            MSOpenXML.CellStyleFormats csfs = new MSOpenXML.CellStyleFormats();
            MSOpenXML.CellFormat       cf   = new MSOpenXML.CellFormat();
            cf.NumberFormatId = 0;
            cf.FontId         = 0;
            cf.BorderId       = 0;
            cf.Alignment      = new MSOpenXML.Alignment()
            {
                WrapText = false
            };
            csfs.Append(cf);
            csfs.Count = (uint)csfs.ChildElements.Count;

            uint iExcelIndex = 164;
            MSOpenXML.NumberingFormats nfs = new MSOpenXML.NumberingFormats();
            MSOpenXML.CellFormats      cfs = new MSOpenXML.CellFormats();

            cf = new MSOpenXML.CellFormat();
            cf.NumberFormatId = 0;
            cf.FontId         = 0;
            cf.BorderId       = 0;
            cf.FormatId       = 0;
            cf.Alignment      = new MSOpenXML.Alignment()
            {
                WrapText = false
            };
            cfs.Append(cf);

            MSOpenXML.NumberingFormat nfDateTime = new MSOpenXML.NumberingFormat();
            nfDateTime.NumberFormatId = iExcelIndex++;
            nfDateTime.FormatCode     = "dd/mm/yyyy";
            nfs.Append(nfDateTime);

            MSOpenXML.NumberingFormat nf4decimal = new MSOpenXML.NumberingFormat();
            nf4decimal.NumberFormatId = iExcelIndex++;
            nf4decimal.FormatCode     = "#,##0";
            nfs.Append(nf4decimal);

            // #,##0.00 is also Excel style index 4
            MSOpenXML.NumberingFormat nf2decimal = new MSOpenXML.NumberingFormat();
            nf2decimal.NumberFormatId = iExcelIndex++;
            nf2decimal.FormatCode     = FormatoDecimal(pQuantidadeDecimais);         //"#,##0.00"
            nfs.Append(nf2decimal);

            // @ is also Excel style index 49
            MSOpenXML.NumberingFormat nfForcedText = new MSOpenXML.NumberingFormat();
            nfForcedText.NumberFormatId = iExcelIndex++;
            nfForcedText.FormatCode     = "@";
            nfs.Append(nfForcedText);

            // index 1
            cf = new MSOpenXML.CellFormat();
            cf.NumberFormatId    = nfDateTime.NumberFormatId;
            cf.FontId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = true;
            cf.Alignment         = new MSOpenXML.Alignment()
            {
                WrapText = false
            };
            cfs.Append(cf);

            // index 2
            cf = new MSOpenXML.CellFormat();
            cf.NumberFormatId    = nf4decimal.NumberFormatId;
            cf.FontId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = true;
            cf.Alignment         = new MSOpenXML.Alignment()
            {
                WrapText = false
            };
            cfs.Append(cf);

            // index 3
            cf = new MSOpenXML.CellFormat();
            cf.NumberFormatId    = nf2decimal.NumberFormatId;
            cf.FontId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = true;
            cf.Alignment         = new MSOpenXML.Alignment()
            {
                WrapText = false
            };
            cfs.Append(cf);

            // index 4
            cf = new MSOpenXML.CellFormat();
            cf.NumberFormatId    = nfForcedText.NumberFormatId;
            cf.FontId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = true;
            cf.Alignment         = new MSOpenXML.Alignment()
            {
                WrapText = false
            };
            cfs.Append(cf);

            // index 5
            // Header text
            cf = new MSOpenXML.CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId         = 1;
            cf.BorderId       = 0;
            cf.FormatId       = 0;
            cf.Alignment      = new MSOpenXML.Alignment()
            {
                WrapText = false
            };
            cf.Alignment.Horizontal = MSOpenXML.HorizontalAlignmentValues.Center;
            cf.ApplyNumberFormat    = true;
            cfs.Append(cf);

            // index 6
            // group text
            cf = new MSOpenXML.CellFormat();
            cf.NumberFormatId    = nf2decimal.NumberFormatId;
            cf.FontId            = 1;
            cf.BorderId          = 1;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = true;
            cf.Alignment         = new MSOpenXML.Alignment()
            {
                WrapText = false
            };
            cfs.Append(cf);

            // index 7
            // Total text, ColumnHeader Text
            cf = new MSOpenXML.CellFormat();
            cf.NumberFormatId    = nf2decimal.NumberFormatId;
            cf.FontId            = 1;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = true;
            cf.Alignment         = new MSOpenXML.Alignment()
            {
                WrapText = false
            };
            cfs.Append(cf);

            nfs.Count = (uint)nfs.ChildElements.Count;
            cfs.Count = (uint)cfs.ChildElements.Count;

            ss.Append(nfs);
            ss.Append(fts);
            ss.Append(fills);
            ss.Append(borders);
            ss.Append(csfs);
            ss.Append(cfs);

            MSOpenXML.CellStyles css = new MSOpenXML.CellStyles();
            MSOpenXML.CellStyle  cs  = new MSOpenXML.CellStyle();
            cs.Name      = "Normal";
            cs.FormatId  = 0;
            cs.BuiltinId = 0;
            css.Append(cs);
            css.Count = (uint)css.ChildElements.Count;
            ss.Append(css);

            MSOpenXML.DifferentialFormats dfs = new MSOpenXML.DifferentialFormats();
            dfs.Count = 0;
            ss.Append(dfs);

            MSOpenXML.TableStyles tss = new MSOpenXML.TableStyles();
            tss.Count = 0;
            //tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
            //tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
            ss.Append(tss);

            return(ss);
        }
コード例 #20
0
        public static Stylesheet GenerateStylesheet2()
        {
            Stylesheet ss = new Stylesheet();

            Fonts fts = new Fonts();

            DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
            FontName ftn = new FontName();

            ftn.Val = "Calibri";
            FontSize ftsz = new FontSize();

            ftsz.Val    = 11;
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);
            fts.Count = (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);
            fills.Count = (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);
            borders.Count = (uint)borders.ChildElements.Count;

            CellStyleFormats csfs = new CellStyleFormats();
            CellFormat       cf   = new CellFormat();

            cf.NumberFormatId = 0;
            cf.FontId         = 0;
            cf.FillId         = 0;
            cf.BorderId       = 0;
            csfs.Append(cf);
            csfs.Count = (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 nf;

            nf = new NumberingFormat();
            nf.NumberFormatId = iExcelIndex++;
            nf.FormatCode     = "dd/mm/yyyy hh:mm:ss";
            nfs.Append(nf);
            cf = new CellFormat();
            cf.NumberFormatId    = nf.NumberFormatId;
            cf.FontId            = 0;
            cf.FillId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = true;
            cfs.Append(cf);

            nf = new NumberingFormat();
            nf.NumberFormatId = iExcelIndex++;
            nf.FormatCode     = "#,##0.0000";
            nfs.Append(nf);
            cf = new CellFormat();
            cf.NumberFormatId    = nf.NumberFormatId;
            cf.FontId            = 0;
            cf.FillId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = true;
            cfs.Append(cf);

            // #,##0.00 is also Excel style index 4
            nf = new NumberingFormat();
            nf.NumberFormatId = iExcelIndex++;
            nf.FormatCode     = "#,##0.00";
            nfs.Append(nf);
            cf = new CellFormat();
            cf.NumberFormatId    = nf.NumberFormatId;
            cf.FontId            = 0;
            cf.FillId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = true;
            cfs.Append(cf);

            // @ is also Excel style index 49
            nf = new NumberingFormat();
            nf.NumberFormatId = iExcelIndex++;
            nf.FormatCode     = "@";
            nfs.Append(nf);
            cf = new CellFormat();
            cf.NumberFormatId    = nf.NumberFormatId;
            cf.FontId            = 0;
            cf.FillId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = true;
            cfs.Append(cf);

            nfs.Count = (uint)nfs.ChildElements.Count;
            cfs.Count = (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      = "Normal";
            cs.FormatId  = 0;
            cs.BuiltinId = 0;
            css.Append(cs);
            css.Count = (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 = "TableStyleMedium9";
            tss.DefaultPivotStyle = "PivotStyleLight16";
            ss.Append(tss);

            return(ss);
        }
コード例 #21
0
ファイル: CustomStylesheet.cs プロジェクト: vietthanh30/dcap
        public CustomStylesheet()
        {
            var fonts    = new Fonts();
            var font     = new DocumentFormat.OpenXml.Spreadsheet.Font();
            var fontName = new FontName {
                Val = StringValue.FromString("Arial")
            };
            var fontSize = new FontSize {
                Val = DoubleValue.FromDouble(11)
            };

            font.FontName = fontName;
            font.FontSize = fontSize;
            fonts.Append(font);
            //Font Index 1
            font     = new DocumentFormat.OpenXml.Spreadsheet.Font();
            fontName = new FontName {
                Val = StringValue.FromString("Arial")
            };
            fontSize = new FontSize {
                Val = DoubleValue.FromDouble(12)
            };
            font.FontName = fontName;
            font.FontSize = fontSize;
            font.Bold     = new Bold();
            fonts.Append(font);
            fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
            var fills       = new Fills();
            var fill        = new Fill();
            var patternFill = new PatternFill {
                PatternType = PatternValues.None
            };

            fill.PatternFill = patternFill;
            fills.Append(fill);
            fill        = new Fill();
            patternFill = new PatternFill {
                PatternType = PatternValues.Gray125
            };
            fill.PatternFill = patternFill;
            fills.Append(fill);
            //Fill index  2
            fill        = new Fill();
            patternFill = new PatternFill
            {
                PatternType     = PatternValues.Solid,
                ForegroundColor = new ForegroundColor()
            };
            patternFill.ForegroundColor =
                TranslateForeground(System.Drawing.Color.LightGray);
            patternFill.BackgroundColor =
                new BackgroundColor {
                Rgb = patternFill.ForegroundColor.Rgb
            };
            fill.PatternFill = patternFill;
            fills.Append(fill);
            //Fill index  3
            fill        = new Fill();
            patternFill = new PatternFill
            {
                PatternType     = PatternValues.Solid,
                ForegroundColor = new ForegroundColor()
            };
            patternFill.ForegroundColor =
                TranslateForeground(System.Drawing.Color.LightGray);
            patternFill.BackgroundColor =
                new BackgroundColor {
                Rgb = patternFill.ForegroundColor.Rgb
            };
            fill.PatternFill = patternFill;
            fills.Append(fill);
            fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
            var borders = new Borders();
            var border  = new Border
            {
                LeftBorder = new LeftBorder {
                    Style = BorderStyleValues.Thin
                },
                RightBorder = new RightBorder {
                    Style = BorderStyleValues.Thin
                },
                TopBorder = new TopBorder {
                    Style = BorderStyleValues.Thin
                },
                BottomBorder = new BottomBorder {
                    Style = BorderStyleValues.Thin
                },
                DiagonalBorder = new DiagonalBorder()
            };

            borders.Append(border);
            //All Boarder Index 1
            border = new Border
            {
                LeftBorder = new LeftBorder {
                    Style = BorderStyleValues.Thin
                },
                RightBorder = new RightBorder {
                    Style = BorderStyleValues.Thin
                },
                TopBorder = new TopBorder {
                    Style = BorderStyleValues.Thin
                },
                BottomBorder = new BottomBorder {
                    Style = BorderStyleValues.Thin
                },
                DiagonalBorder = new DiagonalBorder()
            };
            borders.Append(border);
            //All Boarder Index 2
            border = new Border
            {
                LeftBorder = new LeftBorder {
                    Style = BorderStyleValues.Thin
                },
                RightBorder = new RightBorder {
                    Style = BorderStyleValues.Thin
                },
                TopBorder = new TopBorder {
                    Style = BorderStyleValues.Thin
                },
                BottomBorder = new BottomBorder {
                    Style = BorderStyleValues.Thin
                },
                DiagonalBorder = new DiagonalBorder()
            };
            borders.Append(border);
            borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
            var cellStyleFormats = new CellStyleFormats();
            var cellFormat       = new CellFormat
            {
                NumberFormatId = 0,
                FontId         = 0,
                FillId         = 0,
                BorderId       = 0
            };

            cellStyleFormats.Append(cellFormat);
            cellStyleFormats.Count =
                UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
            uint iExcelIndex      = 164;
            var  numberingFormats = new NumberingFormats();
            var  cellFormats      = new CellFormats();

            cellFormat = new CellFormat
            {
                NumberFormatId = 0,
                FontId         = 0,
                FillId         = 0,
                BorderId       = 0,
                FormatId       = 0
            };
            cellFormats.Append(cellFormat);
            var nformatDateTime = new NumberingFormat
            {
                NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
                FormatCode     = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
            };

            numberingFormats.Append(nformatDateTime);
            var nformat4Decimal = new NumberingFormat
            {
                NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
                FormatCode     = StringValue.FromString("#,##0.0000")
            };

            numberingFormats.Append(nformat4Decimal);
            var nformat2Decimal = new NumberingFormat
            {
                NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
                FormatCode     = StringValue.FromString("#,##0.00")
            };

            numberingFormats.Append(nformat2Decimal);
            var nformatForcedText = new NumberingFormat
            {
                NumberFormatId = UInt32Value.FromUInt32(iExcelIndex),
                FormatCode     = StringValue.FromString("@")
            };

            numberingFormats.Append(nformatForcedText);
            // index 1
            // Cell Standard Date format
            cellFormat = new CellFormat
            {
                NumberFormatId    = 14,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 2
            // Cell Standard Number format with 2 decimal placing
            cellFormat = new CellFormat
            {
                NumberFormatId    = 4,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 3
            // Cell Date time custom format
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatDateTime.NumberFormatId,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 4
            // Cell 4 decimal custom format
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformat4Decimal.NumberFormatId,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 5
            // Cell 2 decimal custom format
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformat2Decimal.NumberFormatId,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 6
            // Cell forced number text custom format
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 7
            // Cell text with font 12
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 1,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 8
            // Cell text
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 9
            // Coloured 2 decimal cell text
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformat2Decimal.NumberFormatId,
                FontId            = 0,
                FillId            = 2,
                BorderId          = 2,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 10
            // Coloured cell text
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 0,
                FillId            = 2,
                BorderId          = 2,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 11
            // Coloured cell text
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 1,
                FillId            = 3,
                BorderId          = 2,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            numberingFormats.Count =
                UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);
            cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
            this.Append(numberingFormats);
            this.Append(fonts);
            this.Append(fills);
            this.Append(borders);
            this.Append(cellStyleFormats);
            this.Append(cellFormats);
            var css = new CellStyles();
            var cs  = new CellStyle
            {
                Name      = StringValue.FromString("Normal"),
                FormatId  = 0,
                BuiltinId = 0
            };

            css.Append(cs);
            css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
            this.Append(css);
            var dfs = new DifferentialFormats {
                Count = 0
            };

            this.Append(dfs);
            var tss = new TableStyles
            {
                Count             = 0,
                DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
                DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
            };

            this.Append(tss);
        }
コード例 #22
0
ファイル: OpenXmlWriterHelper.cs プロジェクト: sk00538811/POC
        /// <summary>
        /// create the default excel formats.  These formats are required for the excel in order for it to render
        /// correctly.
        /// </summary>
        /// <returns></returns>
        private Stylesheet CreateDefaultStylesheet()
        {
            Stylesheet ss = new Stylesheet();

            Fonts fts = new Fonts();

            DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
            FontName ftn = new FontName();

            ftn.Val = "Calibri";
            FontSize ftsz = new FontSize();

            ftsz.Val    = 11;
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);
            fts.Count = (uint)fts.ChildElements.Count;

            Fills       fills = new Fills();
            Fill        fill;
            PatternFill patternFill;

            //default fills used by Excel, don't changes these

            fill                    = new Fill();
            patternFill             = new PatternFill();
            patternFill.PatternType = PatternValues.None;
            fill.PatternFill        = patternFill;
            fills.AppendChild(fill);

            fill                    = new Fill();
            patternFill             = new PatternFill();
            patternFill.PatternType = PatternValues.Gray125;
            fill.PatternFill        = patternFill;
            fills.AppendChild(fill);



            fills.Count = (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);
            borders.Count = (uint)borders.ChildElements.Count;

            CellStyleFormats csfs = new CellStyleFormats();
            CellFormat       cf   = new CellFormat();

            cf.NumberFormatId = 0;
            cf.FontId         = 0;
            cf.FillId         = 0;
            cf.BorderId       = 0;
            csfs.Append(cf);
            csfs.Count = (uint)csfs.ChildElements.Count;


            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);



            var nfs = new NumberingFormats();



            nfs.Count = (uint)nfs.ChildElements.Count;
            cfs.Count = (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(
                new CellStyle()
            {
                Name      = "Normal",
                FormatId  = 0,
                BuiltinId = 0,
            }
                );

            css.Count = (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 = "TableStyleMedium9";
            tss.DefaultPivotStyle = "PivotStyleLight16";
            ss.Append(tss);
            return(ss);
        }
コード例 #23
0
ファイル: Utility.cs プロジェクト: kawserkibria/MobileERP
        public static Stylesheet CreateStylesheet()
        {
            var ss = new Stylesheet();

            var fts = new Fonts();
            var ftn = new FontName {
                Val = "Arial"
            };
            var ftsz = new FontSize {
                Val = 11
            };
            var ft = new DocumentFormat.OpenXml.Spreadsheet.Font {
                FontName = ftn, FontSize = ftsz
            };

            fts.Append(ft);
            fts.Count = (uint)fts.ChildElements.Count;


            var fills       = new Fills();
            var fill        = new Fill();
            var patternFill = new PatternFill {
                PatternType = PatternValues.None
            };

            fill.PatternFill = patternFill;
            fills.Append(fill);

            fill        = new Fill();
            patternFill = new PatternFill {
                PatternType = PatternValues.Gray125
            };
            fill.PatternFill = patternFill;
            fills.Append(fill);

            fills.Count = (uint)fills.ChildElements.Count;

            var borders = new Borders();
            var border  = new Border
            {
                LeftBorder     = new LeftBorder(),
                RightBorder    = new RightBorder(),
                TopBorder      = new TopBorder(),
                BottomBorder   = new BottomBorder(),
                DiagonalBorder = new DiagonalBorder()
            };

            borders.Append(border);
            borders.Count = (uint)borders.ChildElements.Count;

            var csfs = new CellStyleFormats();
            var cf   = new CellFormat {
                NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0
            };

            csfs.Append(cf);
            csfs.Count = (uint)csfs.ChildElements.Count;

            // dd/mm/yyyy is also Excel style index 14

            uint iExcelIndex = 164;
            var  nfs         = new NumberingFormats();
            var  cfs         = new CellFormats();

            cf = new CellFormat {
                NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
            };
            cfs.Append(cf);

            var nf = new NumberingFormat {
                NumberFormatId = iExcelIndex, FormatCode = "dd/mm/yyyy hh:mm:ss"
            };

            nfs.Append(nf);

            cf = new CellFormat
            {
                NumberFormatId    = nf.NumberFormatId,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = true
            };
            cfs.Append(cf);


            iExcelIndex = 165;
            nfs         = new NumberingFormats();
            cfs         = new CellFormats();

            cf = new CellFormat {
                NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
            };
            cfs.Append(cf);

            nf = new NumberingFormat {
                NumberFormatId = iExcelIndex, FormatCode = "MMM yyyy"
            };
            nfs.Append(nf);

            cf = new CellFormat
            {
                NumberFormatId    = nf.NumberFormatId,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = true
            };
            cfs.Append(cf);


            iExcelIndex = 170;
            nf          = new NumberingFormat {
                NumberFormatId = iExcelIndex, FormatCode = "#,##0.0000"
            };
            nfs.Append(nf);
            cf = new CellFormat
            {
                NumberFormatId    = nf.NumberFormatId,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = true
            };
            cfs.Append(cf);

            // #,##0.00 is also Excel style index 4
            iExcelIndex = 171;
            nf          = new NumberingFormat {
                NumberFormatId = iExcelIndex, FormatCode = "#,##0.00"
            };
            nfs.Append(nf);
            cf = new CellFormat
            {
                NumberFormatId    = nf.NumberFormatId,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = true
            };
            cfs.Append(cf);

            // @ is also Excel style index 49
            iExcelIndex = 172;
            nf          = new NumberingFormat {
                NumberFormatId = iExcelIndex, FormatCode = "@"
            };
            nfs.Append(nf);
            cf = new CellFormat
            {
                NumberFormatId    = nf.NumberFormatId,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = true
            };
            cfs.Append(cf);

            nfs.Count = (uint)nfs.ChildElements.Count;
            cfs.Count = (uint)cfs.ChildElements.Count;

            ss.Append(nfs);
            ss.Append(fts);
            ss.Append(fills);
            ss.Append(borders);
            ss.Append(csfs);
            ss.Append(cfs);

            var css = new CellStyles();
            var cs  = new CellStyle {
                Name = "Normal", FormatId = 0, BuiltinId = 0
            };

            css.Append(cs);
            css.Count = (uint)css.ChildElements.Count;
            ss.Append(css);

            var dfs = new DifferentialFormats {
                Count = 0
            };

            ss.Append(dfs);

            var tss = new TableStyles
            {
                Count             = 0,
                DefaultTableStyle = "TableStyleMedium9",
                DefaultPivotStyle = "PivotStyleLight16"
            };

            ss.Append(tss);

            return(ss);
        }
コード例 #24
0
ファイル: ExcelManagement.cs プロジェクト: ramirobr/VOCIraq
        /// <summary>
        /// Create an stylesheet to use in excel files
        /// </summary>
        /// <returns></returns>
        private static Stylesheet VocStyleSheet()
        {
            Stylesheet styleSheet = new Stylesheet();

            Fonts fonts = new Fonts();
            // 0 - normal fonts
            DocumentFormat.OpenXml.Spreadsheet.Font myFont = new DocumentFormat.OpenXml.Spreadsheet.Font()
            {
                FontSize = new FontSize() { Val = 11 },
                Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
                FontName = new FontName() { Val = "Calibri" }
            };
            fonts.Append(myFont);

            //1 - font bold
            myFont = new DocumentFormat.OpenXml.Spreadsheet.Font()
            {
                Bold = new Bold(),
                FontSize = new FontSize() { Val = 11 },
                Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
                FontName = new FontName() { Val = "Calibri" }
            };
            fonts.Append(myFont);

            //2 - font title
            myFont = new DocumentFormat.OpenXml.Spreadsheet.Font()
            {
                Bold = new Bold(),
                FontSize = new FontSize() { Val = 20 },
                Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "FFFFFF" } },
                FontName = new FontName() { Val = "Verdana" }
            };
            fonts.Append(myFont);

            //3 - font bold
            myFont = new DocumentFormat.OpenXml.Spreadsheet.Font()
            {
                Bold = new Bold(),
                FontSize = new FontSize() { Val = 16 },
                Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
                FontName = new FontName() { Val = "Calibri" }
            };
            fonts.Append(myFont);

            //4 - small font white
            myFont = new DocumentFormat.OpenXml.Spreadsheet.Font()
            {
                FontSize = new FontSize() { Val = 11 },
                Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "FFFFFF" } },
                FontName = new FontName() { Val = "Calibri" }
            };
            fonts.Append(myFont);

            Fills fills = new Fills();
            //default fill
            Fill fill = new Fill()
            {
                PatternFill = new PatternFill() { PatternType = PatternValues.None }
            };
            fills.Append(fill);
            //default fill
            fill = new Fill()
            {
                PatternFill = new PatternFill() { PatternType = PatternValues.Gray125 }
            };
            fills.Append(fill);
            //title fill
            fill = new Fill()
            {
                PatternFill = new PatternFill()
                {
                    ForegroundColor = new ForegroundColor()
                    {
                        Rgb = new HexBinaryValue() { Value = "499EB1" }
                    },
                    PatternType = PatternValues.Solid
                }
            };
            fills.Append(fill);

            Borders borders = new Borders();
            //normal borders
            Border border = new Border()
            {
                LeftBorder = new LeftBorder(),
                RightBorder = new RightBorder(),
                TopBorder = new TopBorder(),
                BottomBorder = new BottomBorder(),
                DiagonalBorder = new DiagonalBorder()
            };
            borders.Append(border);

            //borders applied
            border = new Border()
            {
                LeftBorder = new LeftBorder() { Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }, Style = BorderStyleValues.Thin },
                RightBorder = new RightBorder() { Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }, Style = BorderStyleValues.Thin },
                TopBorder = new TopBorder() { Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }, Style = BorderStyleValues.Thin },
                BottomBorder = new BottomBorder() { Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }, Style = BorderStyleValues.Thin },
                DiagonalBorder = new DiagonalBorder()
            };
            borders.Append(border);

            CellFormats cellFormats = new CellFormats();
            //0- normal
            CellFormat cellFormat = new CellFormat()
            {
                FontId = 0,
                FillId = 0,
                BorderId = 0,
                ApplyFill = true
            };
            cellFormats.Append(cellFormat);

            //1 - border
            cellFormat = new CellFormat()
            {
                FontId = 0,
                FillId = 0,
                BorderId = 1,
                Alignment = new Alignment() { WrapText = true },
                ApplyFill = true
            };
            cellFormats.Append(cellFormat);

            //2 - border and bold
            cellFormat = new CellFormat()
            {
                FontId = 1,
                FillId = 0,
                BorderId = 1,
                Alignment = new Alignment() { WrapText = true },
                ApplyFill = true
            };
            cellFormats.Append(cellFormat);

            //3 -title
            cellFormat = new CellFormat()
            {
                FontId = 0,
                FillId = 2,
                BorderId = 0,
                ApplyFill = true
            };
            cellFormats.Append(cellFormat);

            //4 - title
            cellFormat = new CellFormat()
            {
                FontId = 2,
                FillId = 2,
                BorderId = 0,
                Alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Center },
                ApplyFill = true
            };
            cellFormats.Append(cellFormat);

            //5 - title
            cellFormat = new CellFormat()
            {
                FontId = 2,
                FillId = 2,
                BorderId = 0,
                Alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.CenterContinuous, Vertical = VerticalAlignmentValues.Center },
                ApplyFill = true
            };
            cellFormats.Append(cellFormat);

            //6 - section title
            cellFormat = new CellFormat()
            {
                FontId = 3,
                FillId = 0,
                BorderId = 1,
                ApplyFill = true
            };
            cellFormats.Append(cellFormat);

            //7 - section title filter values
            cellFormat = new CellFormat()
            {
                FontId = 4,
                FillId = 2,
                BorderId = 0,
                Alignment = new Alignment() { WrapText = false, Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Center },
                ApplyFill = true
            };
            cellFormats.Append(cellFormat);

            styleSheet.Append(fonts);
            styleSheet.Append(fills);
            styleSheet.Append(borders);
            styleSheet.Append(cellFormats);

            return styleSheet;
        }
コード例 #25
0
        public static Stylesheet CreateStylesheet()
        {
            var ss = new Stylesheet();

            var fts = new Fonts();
            var ftn = new FontName { Val = "Arial" };
            var ftsz = new FontSize { Val = 11 };
            var ft = new DocumentFormat.OpenXml.Spreadsheet.Font { FontName = ftn, FontSize = ftsz };
            fts.Append(ft);
            fts.Count = (uint)fts.ChildElements.Count;

            var fills = new Fills();
            var fill = new Fill();
            var patternFill = new PatternFill { PatternType = PatternValues.None };
            fill.PatternFill = patternFill;
            fills.Append(fill);

            fill = new Fill();
            patternFill = new PatternFill { PatternType = PatternValues.Gray125 };
            fill.PatternFill = patternFill;
            fills.Append(fill);

            fills.Count = (uint)fills.ChildElements.Count;

            var borders = new Borders();
            var border = new Border
            {
                LeftBorder = new LeftBorder(),
                RightBorder = new RightBorder(),
                TopBorder = new TopBorder(),
                BottomBorder = new BottomBorder(),
                DiagonalBorder = new DiagonalBorder()
            };
            borders.Append(border);
            borders.Count = (uint)borders.ChildElements.Count;

            var csfs = new CellStyleFormats();
            var cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0 };
            csfs.Append(cf);
            csfs.Count = (uint)csfs.ChildElements.Count;

            // dd/mm/yyyy is also Excel style index 14

            uint iExcelIndex = 164;
            var nfs = new NumberingFormats();
            var cfs = new CellFormats();

            cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 };
            cfs.Append(cf);

            var nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "dd/mm/yyyy hh:mm:ss" };
            nfs.Append(nf);

            cf = new CellFormat
            {
                NumberFormatId = nf.NumberFormatId,
                FontId = 0,
                FillId = 0,
                BorderId = 0,
                FormatId = 0,
                ApplyNumberFormat = true
            };
            cfs.Append(cf);

            iExcelIndex = 165;
            nfs = new NumberingFormats();
            cfs = new CellFormats();

            cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 };
            cfs.Append(cf);

            nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "MMM yyyy" };
            nfs.Append(nf);

            cf = new CellFormat
            {
                NumberFormatId = nf.NumberFormatId,
                FontId = 0,
                FillId = 0,
                BorderId = 0,
                FormatId = 0,
                ApplyNumberFormat = true
            };
            cfs.Append(cf);

            iExcelIndex = 170;
            nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "#,##0.0000" };
            nfs.Append(nf);
            cf = new CellFormat
            {
                NumberFormatId = nf.NumberFormatId,
                FontId = 0,
                FillId = 0,
                BorderId = 0,
                FormatId = 0,
                ApplyNumberFormat = true
            };
            cfs.Append(cf);

            // #,##0.00 is also Excel style index 4
            iExcelIndex = 171;
            nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "#,##0.00" };
            nfs.Append(nf);
            cf = new CellFormat
            {
                NumberFormatId = nf.NumberFormatId,
                FontId = 0,
                FillId = 0,
                BorderId = 0,
                FormatId = 0,
                ApplyNumberFormat = true
            };
            cfs.Append(cf);

            // @ is also Excel style index 49
            iExcelIndex = 172;
            nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "@" };
            nfs.Append(nf);
            cf = new CellFormat
            {
                NumberFormatId = nf.NumberFormatId,
                FontId = 0,
                FillId = 0,
                BorderId = 0,
                FormatId = 0,
                ApplyNumberFormat = true
            };
            cfs.Append(cf);

            nfs.Count = (uint)nfs.ChildElements.Count;
            cfs.Count = (uint)cfs.ChildElements.Count;

            ss.Append(nfs);
            ss.Append(fts);
            ss.Append(fills);
            ss.Append(borders);
            ss.Append(csfs);
            ss.Append(cfs);

            var css = new CellStyles();
            var cs = new CellStyle { Name = "Normal", FormatId = 0, BuiltinId = 0 };
            css.Append(cs);
            css.Count = (uint)css.ChildElements.Count;
            ss.Append(css);

            var dfs = new DifferentialFormats { Count = 0 };
            ss.Append(dfs);

            var tss = new TableStyles
            {
                Count = 0,
                DefaultTableStyle = "TableStyleMedium9",
                DefaultPivotStyle = "PivotStyleLight16"
            };
            ss.Append(tss);

            return ss;
        }
コード例 #26
0
        public override byte[] Render(global::Campus.Report.Base.Report report)
        {
            byte[] buffer;
            using (var outputMemoryStream = new MemoryStream())
            {
                using (var document = SpreadsheetDocument.Create(outputMemoryStream, SpreadsheetDocumentType.Workbook, true))
                {
                    // Add a WorkbookPart to the document.
                    var workbookpart = document.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();

                    // Add a WorksheetPart to the WorkbookPart.
                    var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());

                    // Add Sheets to the Workbook.
                    var sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());

                    // Append a new worksheet and associate it with the workbook.
                    var sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
                    {
                        Id      = document.WorkbookPart.GetIdOfPart(worksheetPart),
                        SheetId = 1,
                        Name    = "Sheet1"
                    };

                    sheets.Append(sheet);

                    // Add a WorkbookStylesPart to the WorkbookPart
                    var stylesPart = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();

                    var shData = worksheetPart.Worksheet.GetFirstChild <SheetData>();



                    stylesPart.Stylesheet = InitializeDefaultStyles(stylesPart.Stylesheet);

                    uint styleId = 2; //обязательно 2 потому что уже додано два стиля по умолчанию с индексами 0 и 1;

                    var styles = report.Select(o => o.Style).ToList();

                    //нельзя перенести в отдельный метод :(
                    foreach (var s in styles)
                    {
                        if (s is TableStyle)
                        {
                            var style = s as TableStyle;

                            var border = new DocumentFormat.OpenXml.Spreadsheet.Border();

                            var leftBorder   = new DocumentFormat.OpenXml.Spreadsheet.LeftBorder();
                            var rightBorder  = new DocumentFormat.OpenXml.Spreadsheet.RightBorder();
                            var topBorder    = new DocumentFormat.OpenXml.Spreadsheet.TopBorder();
                            var bottomBorder = new DocumentFormat.OpenXml.Spreadsheet.BottomBorder();

                            leftBorder.Style   = SetLineType(style.BorderLine);
                            rightBorder.Style  = SetLineType(style.BorderLine);
                            topBorder.Style    = SetLineType(style.BorderLine);
                            bottomBorder.Style = SetLineType(style.BorderLine);

                            leftBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                Rgb = ToHexBinaryValue(style.BorderColor)
                            };
                            rightBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                Rgb = ToHexBinaryValue(style.BorderColor)
                            };
                            topBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                Rgb = ToHexBinaryValue(style.BorderColor)
                            };
                            bottomBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                Rgb = ToHexBinaryValue(style.BorderColor)
                            };

                            border.LeftBorder   = leftBorder;
                            border.RightBorder  = rightBorder;
                            border.TopBorder    = topBorder;
                            border.BottomBorder = bottomBorder;

                            stylesPart.Stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(border);



                            var patternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill
                            {
                                BackgroundColor = new DocumentFormat.OpenXml.Spreadsheet.BackgroundColor {
                                    Rgb = ToHexBinaryValue(style.Background)
                                },
                                ForegroundColor = new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor {
                                    Rgb = ToHexBinaryValue(style.Foreground)
                                },
                                PatternType = SetPatternType(style.PatternType)
                            };

                            var fill = new DocumentFormat.OpenXml.Spreadsheet.Fill {
                                PatternFill = patternFill
                            };

                            stylesPart.Stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(fill);

                            DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font()
                            {
                                FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName {
                                    Val = new StringValue {
                                        Value = s.FontName
                                    }
                                },
                                FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize {
                                    Val = s.FontSize
                                },
                                Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                    Rgb = ToHexBinaryValue(s.FontColor)
                                }
                            };
                            stylesPart.Stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(font);
                        }
                        else
                        {
                            stylesPart.Stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(new DocumentFormat.OpenXml.Spreadsheet.Fill());
                            stylesPart.Stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(new DocumentFormat.OpenXml.Spreadsheet.Border());
                            DocumentFormat.OpenXml.Spreadsheet.Font font;
                            if (s != null)
                            {
                                font = new DocumentFormat.OpenXml.Spreadsheet.Font
                                {
                                    FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName {
                                        Val = new StringValue {
                                            Value = s.FontName
                                        }
                                    },
                                    FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize {
                                        Val = s.FontSize
                                    },
                                    Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                        Rgb = ToHexBinaryValue(s.FontColor)
                                    }
                                };


                                foreach (var item in s.TextStyle)
                                {
                                    switch (item)
                                    {
                                    case TextStyleType.Bold: font.Bold = new DocumentFormat.OpenXml.Spreadsheet.Bold {
                                            Val = true
                                    }; break;

                                    case TextStyleType.Italic: font.Italic = new DocumentFormat.OpenXml.Spreadsheet.Italic {
                                            Val = true
                                    }; break;

                                    case TextStyleType.Underline: font.Underline = new DocumentFormat.OpenXml.Spreadsheet.Underline {
                                            Val = DocumentFormat.OpenXml.Spreadsheet.UnderlineValues.Single
                                    }; break;

                                    case TextStyleType.Normal: break;
                                    }
                                }
                            }
                            else
                            {
                                font = new DocumentFormat.OpenXml.Spreadsheet.Font();
                            }

                            stylesPart.Stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(font);
                        }


                        if (s != null)
                        {
                            var cellFormat = new DocumentFormat.OpenXml.Spreadsheet.CellFormat
                            {
                                BorderId = UInt32Value.ToUInt32(styleId),
                                FillId   = UInt32Value.ToUInt32(styleId),
                                FontId   = UInt32Value.ToUInt32(styleId)
                            };

                            if (s.Aligment != null)
                            {
                                var align = new Alignment()
                                {
                                    TextRotation = new UInt32Value(s.Aligment.Rotation),
                                    Horizontal   = AlignmentMapper.MapHorizontalAligment(s.Aligment.HorizontalAligment),
                                    Vertical     = AlignmentMapper.MapVerticalAligment(s.Aligment.VerticalAligment)
                                };

                                cellFormat.Append(align);
                            }

                            stylesPart.Stylesheet.CellFormats.AppendChild <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(cellFormat);
                        }

                        styleId = styleId + 1;
                    }

                    foreach (var x in report)
                    {
                        if (x is ComplexHeaderCell)
                        {
                            var element = x as ComplexHeaderCell;

                            if (Render(element, styles, shData, worksheetPart, workbookpart))
                            {
                                continue;
                            }
                        }

                        if (x is TextElement)
                        {
                            var element = x as TextElement;

                            if (Render(element, styles, shData, workbookpart))
                            {
                                continue;
                            }
                        }

                        if (x is TableElement)
                        {
                            var element = x as TableElement;
                            if (Render(element, styles, shData, workbookpart))
                            {
                                continue;
                            }
                        }

                        if (x is ImageElement)
                        {
                            var element = x as ImageElement;

                            if (Render(element, styles, shData, workbookpart))
                            {
                                continue;
                            }
                        }
                    }

                    //var dateLine = shData.AppendChild(new Spreadsheet.Row());

                    //dateLine.AppendChild(new Spreadsheet.Cell()
                    //{
                    //    CellValue = new Spreadsheet.CellValue(_report.TimeStamp.Date.ToString()),
                    //    DataType = Spreadsheet.CellValues.String,
                    //    StyleIndex = 0
                    //});

                    workbookpart.Workbook.Save();
                }

                buffer = new byte[outputMemoryStream.Position];
                outputMemoryStream.Position = 0;
                outputMemoryStream.Read(buffer, 0, buffer.Length);
            }
            return(buffer);
        }
コード例 #27
0
        /// <summary>
        /// create the default excel formats.  These formats are required for the excel in order for it to render
        /// correctly.
        /// </summary>
        /// <returns></returns>
        public Stylesheet CreateDefaultStylesheet()
        {
            Stylesheet ss = new Stylesheet();
            Fonts fts = new Fonts();
            DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
            FontName ftn = new FontName();
            ftn.Val = "Calibri";
            FontSize ftsz = new FontSize();
            ftsz.Val = 11;
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);
            fts.Count = (uint)fts.ChildElements.Count;

            Fills fills = new Fills();
            Fill fill;
            PatternFill patternFill;

            //default fills used by Excel, don't changes these

            fill = new Fill();
            patternFill = new PatternFill();
            patternFill.PatternType = PatternValues.None;
            fill.PatternFill = patternFill;
            fills.AppendChild(fill);

            fill = new Fill();
            patternFill = new PatternFill();
            patternFill.PatternType = PatternValues.Gray125;
            fill.PatternFill = patternFill;
            fills.AppendChild(fill);

            fills.Count = (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);
            borders.Count = (uint)borders.ChildElements.Count;

            CellStyleFormats csfs = new CellStyleFormats();
            CellFormat cf = new CellFormat();
            cf.NumberFormatId = 0;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 0;
            csfs.Append(cf);
            csfs.Count = (uint)csfs.ChildElements.Count;

            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);

            var nfs = new NumberingFormats();

            nfs.Count = (uint)nfs.ChildElements.Count;
            cfs.Count = (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(
                new CellStyle()
                {
                    Name = "Normal",
                    FormatId = 0,
                    BuiltinId = 0,
                }
                );

            css.Count = (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 = "TableStyleMedium9";
            tss.DefaultPivotStyle = "PivotStyleLight16";
            ss.Append(tss);
            return ss;
        }
コード例 #28
0
 public void ApplySettings(DocumentFormat.OpenXml.Spreadsheet.Font font, DocumentFormat.OpenXml.Spreadsheet.Fill fill, params ExcelBorder[] borders)
 {
 }
コード例 #29
0
        public CustomStylesheet()
        {
            #region Fonts
            var fonts = new Fonts();
            var color = new Color();
            //Font Index 0
            var font     = new DocumentFormat.OpenXml.Spreadsheet.Font();
            var fontName = new FontName {
                Val = StringValue.FromString("Arial")
            };
            var fontSize = new FontSize {
                Val = DoubleValue.FromDouble(9)
            };
            font.FontName = fontName;
            font.FontSize = fontSize;
            fonts.Append(font);

            //Font Index 1
            font     = new DocumentFormat.OpenXml.Spreadsheet.Font();
            fontName = new FontName {
                Val = StringValue.FromString("Arial")
            };
            fontSize = new FontSize {
                Val = DoubleValue.FromDouble(10)
            };
            font.FontName = fontName;
            font.FontSize = fontSize;
            font.Bold     = new Bold();
            fonts.Append(font);

            //Font Index 2
            font     = new DocumentFormat.OpenXml.Spreadsheet.Font();
            fontName = new FontName {
                Val = StringValue.FromString("Arial")
            };
            fontSize = new FontSize {
                Val = DoubleValue.FromDouble(15)
            };
            font.FontName = fontName;
            font.FontSize = fontSize;
            font.Bold     = new Bold();
            fonts.Append(font);

            //Font Index 3 Posted
            font     = new DocumentFormat.OpenXml.Spreadsheet.Font();
            fontName = new FontName {
                Val = StringValue.FromString("Arial")
            };
            fontSize = new FontSize {
                Val = DoubleValue.FromDouble(9)
            };
            font.FontName = fontName;
            font.FontSize = fontSize;
            color         = new Color()
            {
                Rgb = new HexBinaryValue()
                {
                    Value = "0000FF"
                }
            };
            font.Color = color;
            fonts.Append(font);

            //Font Index 4 InProgress
            font     = new DocumentFormat.OpenXml.Spreadsheet.Font();
            fontName = new FontName {
                Val = StringValue.FromString("Arial")
            };
            fontSize = new FontSize {
                Val = DoubleValue.FromDouble(9)
            };
            font.FontName = fontName;
            font.FontSize = fontSize;
            color         = new Color()
            {
                Rgb = new HexBinaryValue()
                {
                    Value = "FF8B45"
                }
            };
            font.Color = color;
            fonts.Append(font);

            //Font Index 5 Submitted/resubmitted
            font     = new DocumentFormat.OpenXml.Spreadsheet.Font();
            fontName = new FontName {
                Val = StringValue.FromString("Arial")
            };
            fontSize = new FontSize {
                Val = DoubleValue.FromDouble(9)
            };
            font.FontName = fontName;
            font.FontSize = fontSize;
            color         = new Color()
            {
                Rgb = new HexBinaryValue()
                {
                    Value = "FF0000"
                }
            };
            font.Color = color;
            fonts.Append(font);

            //Font Index 6 Accepted
            font     = new DocumentFormat.OpenXml.Spreadsheet.Font();
            fontName = new FontName {
                Val = StringValue.FromString("Arial")
            };
            fontSize = new FontSize {
                Val = DoubleValue.FromDouble(9)
            };
            font.FontName = fontName;
            font.FontSize = fontSize;
            color         = new Color()
            {
                Rgb = new HexBinaryValue()
                {
                    Value = "2C6100"
                }
            };
            font.Color = color;
            fonts.Append(font);

            //Font Index 7 Dashboard
            font     = new DocumentFormat.OpenXml.Spreadsheet.Font();
            fontName = new FontName {
                Val = StringValue.FromString("Calibri")
            };
            fontSize = new FontSize {
                Val = DoubleValue.FromDouble(15)
            };
            font.FontName = fontName;
            font.FontSize = fontSize;
            fonts.Append(font);

            //Font Index 8
            font     = new DocumentFormat.OpenXml.Spreadsheet.Font();
            fontName = new FontName {
                Val = StringValue.FromString("Calibri")
            };
            fontSize = new FontSize {
                Val = DoubleValue.FromDouble(9)
            };
            font.FontName = fontName;
            font.FontSize = fontSize;
            fonts.Append(font);

            //Font Index 9 Header
            font     = new DocumentFormat.OpenXml.Spreadsheet.Font();
            fontName = new FontName {
                Val = StringValue.FromString("Calibri")
            };
            fontSize = new FontSize {
                Val = DoubleValue.FromDouble(18)
            };
            color = new Color()
            {
                Rgb = new HexBinaryValue()
                {
                    Value = "FFFFFF"
                }
            };
            font.Color    = color;
            font.Bold     = new Bold();
            font.FontName = fontName;
            font.FontSize = fontSize;
            fonts.Append(font);

            //Font Index 10 Body Bold
            font     = new DocumentFormat.OpenXml.Spreadsheet.Font();
            fontName = new FontName {
                Val = StringValue.FromString("Calibri")
            };
            fontSize = new FontSize {
                Val = DoubleValue.FromDouble(11)
            };
            font.Bold     = new Bold();
            font.FontName = fontName;
            font.FontSize = fontSize;
            fonts.Append(font);

            //Font Index 11 Body Normal
            font     = new DocumentFormat.OpenXml.Spreadsheet.Font();
            fontName = new FontName {
                Val = StringValue.FromString("Calibri")
            };
            fontSize = new FontSize {
                Val = DoubleValue.FromDouble(11)
            };
            font.FontName = fontName;
            font.FontSize = fontSize;
            fonts.Append(font);

            fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
            #endregion

            #region Fills
            var fills = new Fills();

            //Fill index  0
            var fill        = new Fill();
            var patternFill = new PatternFill {
                PatternType = PatternValues.None
            };
            fill.PatternFill = patternFill;
            fills.Append(fill);

            //Fill index  1
            fill        = new Fill();
            patternFill = new PatternFill {
                PatternType = PatternValues.Gray125
            };
            fill.PatternFill = patternFill;
            fills.Append(fill);

            //Fill index  2
            fill        = new Fill();
            patternFill = new PatternFill {
                PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
            };
            patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.LightBlue);
            patternFill.BackgroundColor = new BackgroundColor {
                Rgb = patternFill.ForegroundColor.Rgb
            };
            fill.PatternFill = patternFill;
            fills.Append(fill);

            //Fill index  3
            fill        = new Fill();
            patternFill = new PatternFill {
                PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
            };
            patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.FromArgb(211, 211, 211));
            patternFill.BackgroundColor = new BackgroundColor {
                Rgb = patternFill.ForegroundColor.Rgb
            };
            fill.PatternFill = patternFill;
            fills.Append(fill);

            //Fill index  4
            fill        = new Fill();
            patternFill = new PatternFill {
                PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
            };
            patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.FromArgb(211, 211, 211));
            patternFill.BackgroundColor = new BackgroundColor()
            {
                Rgb = patternFill.ForegroundColor.Rgb
            };
            fill.PatternFill = patternFill;
            fills.Append(fill);

            //Fill index  5
            fill        = new Fill();
            patternFill = new PatternFill {
                PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
            };
            patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.FromArgb(245, 245, 245));
            patternFill.BackgroundColor = new BackgroundColor {
                Rgb = patternFill.ForegroundColor.Rgb
            };
            fill.PatternFill = patternFill;
            fills.Append(fill);

            //Fill index  6 ForeGround Header Blue
            fill        = new Fill();
            patternFill = new PatternFill {
                PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
            };
            patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.FromArgb(0, 176, 240));
            patternFill.BackgroundColor = new BackgroundColor {
                Rgb = patternFill.ForegroundColor.Rgb
            };

            fill.PatternFill = patternFill;
            fills.Append(fill);

            //Fill index  7 ForeGround Header green
            fill        = new Fill();
            patternFill = new PatternFill {
                PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
            };
            patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.FromArgb(146, 208, 80));
            patternFill.BackgroundColor = new BackgroundColor {
                Rgb = patternFill.ForegroundColor.Rgb
            };
            fill.PatternFill = patternFill;
            fills.Append(fill);

            fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
            #endregion

            #region Borders
            var borders = new Borders();
            //All Boarder Index 0
            var border = new Border
            {
                LeftBorder = new LeftBorder {
                    Style = BorderStyleValues.Thin, Color = new Color()
                    {
                        Indexed = (UInt32Value)64U
                    }
                },
                RightBorder = new RightBorder {
                    Style = BorderStyleValues.Thin, Color = new Color()
                    {
                        Indexed = (UInt32Value)64U
                    }
                },
                TopBorder = new TopBorder {
                    Style = BorderStyleValues.Thin, Color = new Color()
                    {
                        Indexed = (UInt32Value)64U
                    }
                },
                BottomBorder = new BottomBorder {
                    Style = BorderStyleValues.Thin, Color = new Color()
                    {
                        Indexed = (UInt32Value)64U
                    }
                },
                DiagonalBorder = new DiagonalBorder()
            };
            borders.Append(border);

            //All Boarder Index 1
            border = new Border
            {
                LeftBorder = new LeftBorder {
                    Style = BorderStyleValues.Thin, Color = new Color()
                    {
                        Indexed = (UInt32Value)64U
                    }
                },
                RightBorder = new RightBorder {
                    Style = BorderStyleValues.Thin, Color = new Color()
                    {
                        Indexed = (UInt32Value)64U
                    }
                },
                TopBorder = new TopBorder {
                    Style = BorderStyleValues.Thin, Color = new Color()
                    {
                        Indexed = (UInt32Value)64U
                    }
                },
                BottomBorder = new BottomBorder {
                    Style = BorderStyleValues.Thin, Color = new Color()
                    {
                        Indexed = (UInt32Value)64U
                    }
                },
                DiagonalBorder = new DiagonalBorder()
            };
            borders.Append(border);
            //All Boarder Index 2
            border = new Border
            {
                LeftBorder = new LeftBorder {
                    Style = BorderStyleValues.Thin, Color = new Color()
                    {
                        Indexed = (UInt32Value)64U
                    }
                },
                RightBorder = new RightBorder {
                    Style = BorderStyleValues.Thin, Color = new Color()
                    {
                        Indexed = (UInt32Value)64U
                    }
                },
                TopBorder = new TopBorder {
                    Style = BorderStyleValues.Thin, Color = new Color()
                    {
                        Indexed = (UInt32Value)64U
                    }
                },
                BottomBorder = new BottomBorder {
                    Style = BorderStyleValues.Thin, Color = new Color()
                    {
                        Indexed = (UInt32Value)64U
                    }
                },
                DiagonalBorder = new DiagonalBorder()
            };
            borders.Append(border);
            borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
            #endregion

            #region CellStyleFormats
            var cellStyleFormats = new CellStyleFormats();

            var cellFormat = new CellFormat {
                NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 1
            };
            cellStyleFormats.Append(cellFormat);

            cellFormat = new CellFormat {
                NumberFormatId = 0, FontId = 2, FillId = 4, BorderId = 1
            };
            cellStyleFormats.Append(cellFormat);

            cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
            uint iExcelIndex = 164;

            #endregion

            #region NumberFormats
            var numberingFormats = new NumberingFormats();

            var nformatDateTime = new NumberingFormat
            {
                NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
                FormatCode     = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
            };
            numberingFormats.Append(nformatDateTime);
            var nformat4Decimal = new NumberingFormat
            {
                NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
                FormatCode     = StringValue.FromString("#,##0.0000")
            };
            numberingFormats.Append(nformat4Decimal);
            var nformat2Decimal = new NumberingFormat
            {
                NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
                FormatCode     = StringValue.FromString("#,##0.00")
            };
            numberingFormats.Append(nformat2Decimal);
            var nformatForcedText = new NumberingFormat
            {
                NumberFormatId = UInt32Value.FromUInt32(iExcelIndex),
                FormatCode     = StringValue.FromString("@")
            };
            numberingFormats.Append(nformatForcedText);
            #endregion

            #region CellFormats
            var cellFormats = new CellFormats();

            // index 0
            cellFormat = new CellFormat
            {
                NumberFormatId = 0,
                FontId         = 0,
                FillId         = 0,
                FormatId       = 0
            };
            cellFormats.Append(cellFormat);

            // index 1
            // Cell Standard Date format
            cellFormat = new CellFormat
            {
                NumberFormatId    = 14,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormat.AppendChild(new Alignment {
                Vertical = VerticalAlignmentValues.Top
            });
            cellFormats.Append(cellFormat);
            // Index 2
            // Cell Standard Number format with 2 decimal placing
            cellFormat = new CellFormat
            {
                NumberFormatId    = 4,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormat.AppendChild(new Alignment {
                Vertical = VerticalAlignmentValues.Top
            });
            cellFormats.Append(cellFormat);
            // Index 3
            // Cell Date time custom format
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatDateTime.NumberFormatId,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 4
            // Cell 4 decimal custom format
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformat4Decimal.NumberFormatId,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 5
            // Cell 2 decimal custom format
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformat2Decimal.NumberFormatId,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 6
            // Cell forced number text custom format
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 7
            // Cell text with font 12
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 1,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 8
            // Cell text
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 9
            // Coloured 2 decimal cell text
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformat2Decimal.NumberFormatId,
                FontId            = 0,
                FillId            = 2,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 10
            // Coloured cell text
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 0,
                FillId            = 2,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            // Index 11
            // Coloured cell text
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 1,
                FillId            = 3,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);

            // index 12 for insight header
            cellFormat = new CellFormat
            {
                NumberFormatId = nformatForcedText.NumberFormatId,
                FontId         = 2,
                FillId         = 4,
                //BorderId = 1,
                FormatId          = 1,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);

            // index 13
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 0,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormat.AppendChild(new Alignment {
                Vertical = VerticalAlignmentValues.Top
            });
            cellFormats.Append(cellFormat);

            // index 14 alternate for index 1
            // Cell Standard Date format
            cellFormat = new CellFormat
            {
                NumberFormatId    = 14,
                FontId            = 0,
                FillId            = 5,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormat.AppendChild(new Alignment {
                Vertical = VerticalAlignmentValues.Top
            });
            cellFormats.Append(cellFormat);
            // Index 15  alternate for index 2
            // Cell Standard Number format with 2 decimal placing
            cellFormat = new CellFormat
            {
                NumberFormatId    = 4,
                FontId            = 0,
                FillId            = 5,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormat.AppendChild(new Alignment {
                Vertical = VerticalAlignmentValues.Top
            });
            cellFormats.Append(cellFormat);
            // index 16  alternate for index 13
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 0,
                FillId            = 5,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormat.AppendChild(new Alignment {
                Vertical = VerticalAlignmentValues.Top
            });
            cellFormats.Append(cellFormat);
            // Index 17
            // Integer Cell format
            cellFormat = new CellFormat
            {
                FontId            = 0,
                FillId            = 0,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormat.AppendChild(new Alignment {
                Vertical = VerticalAlignmentValues.Top
            });
            cellFormats.Append(cellFormat);
            // Index 18
            // Integer Cell format alternate
            cellFormat = new CellFormat
            {
                FontId            = 0,
                FillId            = 5,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormat.AppendChild(new Alignment {
                Vertical = VerticalAlignmentValues.Top
            });
            cellFormats.Append(cellFormat);

            /*Style index for ITAreaList */
            // index 19  In-Progress
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 4,
                FillId            = 0,//6
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);

            // index 20  Submitted
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 5,
                FillId            = 0,//7
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);

            // index 21  Posted
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 3,
                FillId            = 0,//8
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);

            // index 22  Accepted
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 6,
                FillId            = 0,//9
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);
            /*Style index for ITAreaList */

            /*Style index for Dashboard */
            // Index 23
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 7,
                FillId            = 4,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);

            // Index 24
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 8,
                FillId            = 4,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormat.AppendChild(new Alignment {
                Horizontal = HorizontalAlignmentValues.Right
            });
            cellFormats.Append(cellFormat);
            //cellFormat.ApplyAlignment = new BooleanValue(true);

            // Index 25
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 7,
                FillId            = 0,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);

            // Index 26
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 7,
                FillId            = 0,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true),
            };
            cellFormat.AppendChild(new Alignment {
                Horizontal = HorizontalAlignmentValues.Right
            });
            cellFormats.AppendChild(cellFormat);

            // index 27 for wrapText --AllComments
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 0,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormat.AppendChild(new Alignment {
                WrapText = true
            });
            cellFormats.AppendChild(cellFormat);

            // index 28 for Alternate Wrap --AllComments
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 0,
                FillId            = 5,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormat.AppendChild(new Alignment {
                WrapText = true
            });
            cellFormats.AppendChild(cellFormat);
            //cellFormat.ApplyAlignment = new BooleanValue(true);

            // index 29 for Alternate Wrap --AllComments
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true),
            };
            cellFormat.AppendChild(new Alignment {
                Horizontal = HorizontalAlignmentValues.Right
            });
            cellFormats.AppendChild(cellFormat);

            // Index 30
            // Coloured cell text without border
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 1,
                FillId            = 3,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);

            // Index 31
            // Coloured cell text without border right aligned
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 1,
                FillId            = 3,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true),
                Alignment         = new Alignment {
                    Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
                }
            };
            cellFormats.AppendChild(cellFormat);

            // index 32
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 0,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true),
                Alignment         = new Alignment {
                    Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
                }
            };
            cellFormats.Append(cellFormat);

            // index 33  alternate for index 32
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 0,
                FillId            = 5,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true),
                Alignment         = new Alignment {
                    Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
                }
            };
            cellFormats.Append(cellFormat);

            // index 34  date format 17 = 'mmm-yy'
            cellFormat = new CellFormat
            {
                NumberFormatId    = 17,
                FontId            = 1,
                FillId            = 3,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true),
                Alignment         = new Alignment {
                    Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
                }
            };
            cellFormats.Append(cellFormat);

            // index 35
            // Cell Standard date format 17 = 'mmm-yy'
            cellFormat = new CellFormat
            {
                NumberFormatId    = 17,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true),
                Alignment         = new Alignment {
                    Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
                }
            };
            cellFormats.Append(cellFormat);

            // index 36 alternate for index 35
            // Cell Standard date format 17 = 'mmm-yy'
            cellFormat = new CellFormat
            {
                NumberFormatId    = 17,
                FontId            = 0,
                FillId            = 5,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true),
                Alignment         = new Alignment {
                    Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
                }
            };
            cellFormats.Append(cellFormat);

            // Index 37
            // Coloured cell text for Excel Header
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 2,
                FillId            = 3,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);


            // Index 38
            // Bold text Cell - Left Align
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 1,
                FillId            = 0,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            };
            cellFormats.Append(cellFormat);

            // Index 39
            // Bold text Cell with background - Center Align
            cellFormat = new CellFormat
            {
                NumberFormatId    = nformatForcedText.NumberFormatId,
                FontId            = 1,
                FillId            = 3,
                BorderId          = 2,
                FormatId          = 0,
                ApplyBorder       = true,
                ApplyNumberFormat = BooleanValue.FromBoolean(true),
                Alignment         = new Alignment {
                    Horizontal = HorizontalAlignmentValues.Center
                }
            };
            cellFormats.Append(cellFormat);

            // Index 40
            // Bold text Cell - Right Align
            cellFormat = new CellFormat
            {
                NumberFormatId    = 4,
                FontId            = 1,
                FillId            = 0,
                BorderId          = 1,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true),
                Alignment         = new Alignment {
                    Horizontal = HorizontalAlignmentValues.Right
                }
            };
            cellFormats.Append(cellFormat);

            /* End Style index for Dashboard */

            // Fonts For Tax Savings
            // Index 41 For Header
            cellFormat = new CellFormat
            {
                NumberFormatId    = 0,
                FontId            = 9,
                FillId            = 6,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true),
                Alignment         = new Alignment {
                    Horizontal = HorizontalAlignmentValues.Left
                }
            };
            cellFormats.Append(cellFormat);

            // Index 42 For Header
            cellFormat = new CellFormat
            {
                NumberFormatId    = 0,
                FontId            = 10,
                FillId            = 7,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true),
                Alignment         = new Alignment {
                    Horizontal = HorizontalAlignmentValues.Left
                }
            };
            cellFormats.Append(cellFormat);

            // Index 43 For Body Header Bold
            cellFormat = new CellFormat
            {
                NumberFormatId    = 0,
                FontId            = 10,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true),
                Alignment         = new Alignment {
                    Horizontal = HorizontalAlignmentValues.Center
                }
            };
            cellFormats.Append(cellFormat);

            // Index 44 For Body Header Normal
            cellFormat = new CellFormat
            {
                NumberFormatId    = 0,
                FontId            = 11,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true),
                Alignment         = new Alignment {
                    Horizontal = HorizontalAlignmentValues.Left
                }
            };
            cellFormats.Append(cellFormat);

            // Index 45 For Body Cell Float
            cellFormat = new CellFormat
            {
                NumberFormatId    = 4,
                FontId            = 11,
                FillId            = 0,
                BorderId          = 0,
                FormatId          = 0,
                ApplyNumberFormat = BooleanValue.FromBoolean(true),
                Alignment         = new Alignment {
                    Horizontal = HorizontalAlignmentValues.Right
                }
            };
            cellFormats.Append(cellFormat);

            numberingFormats.Count = UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);
            cellFormats.Count      = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
            #endregion

            this.Append(numberingFormats);
            this.Append(fonts);
            this.Append(fills);
            this.Append(borders);
            this.Append(cellStyleFormats);
            this.Append(cellFormats);

            var css = new CellStyles();
            var cs  = new CellStyle
            {
                Name      = StringValue.FromString("Normal"),
                FormatId  = 0,
                BuiltinId = 0
            };
            css.Append(cs);
            css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);

            this.Append(css);

            var dfs = new DifferentialFormats {
                Count = 0
            };
            this.Append(dfs);
            var tss = new TableStyles
            {
                Count             = 0,
                DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
                DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
            };
            this.Append(tss);
        }
コード例 #30
0
        private Stylesheet createStyleSheet()
        {
            Stylesheet stylesheet = new Stylesheet();

            Fonts fts = new Fonts();

            DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
            FontName ftn = new FontName();

            ftn.Val = StringValue.FromString("Calibri");
            FontSize ftsz = new FontSize();

            ftsz.Val    = DoubleValue.FromDouble(11);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);

            ft          = new DocumentFormat.OpenXml.Spreadsheet.Font();
            ftn         = new FontName();
            ftn.Val     = StringValue.FromString("Palatino Linotype");
            ftsz        = new FontSize();
            ftsz.Val    = DoubleValue.FromDouble(18);
            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 = patternFill.ForegroundColor.Rgb;
            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;


            CellStyleFormats cellStyleFormats = new CellStyleFormats();

            CellFormat cellFormat = new CellFormat();

            cellFormat.NumberFormatId = 0;
            cellFormat.FontId         = 0;
            cellFormat.FillId         = 0;
            cellFormat.BorderId       = 0;
            cellStyleFormats.Append(cellFormat);
            cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);

            uint             iExcelIndex   = 164;
            NumberingFormats numberFormats = new NumberingFormats();
            CellFormats      cellFormats   = new CellFormats();

            cellFormat = new CellFormat();
            cellFormat.NumberFormatId = 0;
            cellFormat.FontId         = 0;
            cellFormat.FillId         = 0;
            cellFormat.BorderId       = 0;
            cellFormat.FormatId       = 0;
            cellFormats.Append(cellFormat);

            NumberingFormat nfDateTime = new NumberingFormat();

            nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nfDateTime.FormatCode     = StringValue.FromString(this.DateTimeFormat);
            numberFormats.Append(nfDateTime);

            NumberingFormat nfDate = new NumberingFormat();

            nfDate.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nfDate.FormatCode     = StringValue.FromString(this.DateFormat);
            numberFormats.Append(nfDate);

            // index 1
            cellFormat = new CellFormat();
            cellFormat.NumberFormatId    = nfDateTime.NumberFormatId;
            cellFormat.FontId            = 0;
            cellFormat.FillId            = 0;
            cellFormat.BorderId          = 0;
            cellFormat.FormatId          = 0;
            cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cellFormats.Append(cellFormat);

            cellFormat = new CellFormat();
            cellFormat.NumberFormatId    = nfDate.NumberFormatId;
            cellFormat.FontId            = 0;
            cellFormat.FillId            = 0;
            cellFormat.BorderId          = 0;
            cellFormat.FormatId          = 0;
            cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cellFormats.Append(cellFormat);

            numberFormats.Count = UInt32Value.FromUInt32((uint)numberFormats.ChildElements.Count);
            cellFormats.Count   = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);

            stylesheet.Append(numberFormats);
            stylesheet.Append(fts);
            stylesheet.Append(fills);
            stylesheet.Append(borders);
            stylesheet.Append(cellStyleFormats);
            stylesheet.Append(cellFormats);

            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);
            stylesheet.Append(css);

            DifferentialFormats dfs = new DifferentialFormats();

            dfs.Count = 0;
            stylesheet.Append(dfs);

            TableStyles tss = new TableStyles();

            tss.Count             = 0;
            tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
            tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
            stylesheet.Append(tss);

            return(stylesheet);
        }
コード例 #31
0
ファイル: BookWriter.cs プロジェクト: robertmaxrees/gif2xlsx
        internal void Save()
        {
            var stylesPart = sd.WorkbookPart.AddNewPart <WorkbookStylesPart>();

            stylesPart.Stylesheet = new Stylesheet();
            CellFormats cellFormats = new CellFormats();

            Fills fills = new Fills();
            // There are two fills that seemingly need to be always in there
            Fill        fill1        = new Fill();
            PatternFill patternFill1 = new PatternFill();

            patternFill1.PatternType = PatternValues.None;
            fill1.Append(patternFill1);
            fills.Append(fill1);
            Fill        fill2        = new Fill();
            PatternFill patternFill2 = new PatternFill();

            patternFill2.PatternType = PatternValues.Gray125;
            fill2.Append(patternFill2);
            fills.Append(fill2);
            // And two XFs
            CellFormat cellFormat1 = new CellFormat();

            cellFormat1.FillId = (UInt32)0;
            cellFormats.Append(cellFormat1);
            CellFormat cellFormat2 = new CellFormat();

            cellFormat2.FillId = (UInt32)1;
            cellFormats.Append(cellFormat2);

            foreach (var pal in palette)
            {
                CellFormat cellFormat = new CellFormat();
                cellFormat.FillId = (UInt32)(pal.Value + 2);
                cellFormats.Append(cellFormat);

                Fill        fill        = new Fill();
                PatternFill patternFill = new PatternFill();
                patternFill.PatternType = PatternValues.Solid;
                ForegroundColor fgc = new ForegroundColor();
                fgc.Rgb = "FF" + ColorTranslator.ToHtml(pal.Key).Replace("#", "");
                patternFill.Append(fgc);
                fill.Append(patternFill);
                fills.Append(fill);
            }

            // Add dummy borders and fonts, which we need for the cellXfs to work
            Borders borders = new Borders();
            Border  border  = new Border();

            borders.Append(border);
            Fonts fonts = new Fonts();

            DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font();
            fonts.Append(font);

            // Add the other things we made to the stylesheet
            stylesPart.Stylesheet.Append(fonts);
            stylesPart.Stylesheet.Append(fills);
            stylesPart.Stylesheet.Append(borders);
            stylesPart.Stylesheet.Append(cellFormats);
            sd.Save();
            sd.Close();
        }
コード例 #32
0
        public CustomStylesheet()
        {
            Fonts fts = new Fonts();

            DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
            FontName ftn = new FontName();

            ftn.Val = StringValue.FromString("Calibri");
            FontSize ftsz = new FontSize();

            ftsz.Val    = DoubleValue.FromDouble(11);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);

            ft          = new DocumentFormat.OpenXml.Spreadsheet.Font();
            ftn         = new FontName();
            ftn.Val     = StringValue.FromString("Palatino Linotype");
            ftsz        = new FontSize();
            ftsz.Val    = DoubleValue.FromDouble(18);
            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 = patternFill.ForegroundColor.Rgb;
            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);

            //Boarder Index 1
            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);


            //Boarder Index 2
            border                    = new Border();
            border.LeftBorder         = new LeftBorder();
            border.RightBorder        = new RightBorder();
            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       = 0;
            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);

            // index 1
            // Format dd/mm/yyyy
            cf = new CellFormat();
            cf.NumberFormatId    = 14;
            cf.FontId            = 0;
            cf.FillId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 2
            // Format #,##0.00
            cf = new CellFormat();
            cf.NumberFormatId    = 4;
            cf.FontId            = 0;
            cf.FillId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 3
            cf = new CellFormat();
            cf.NumberFormatId    = nfDateTime.NumberFormatId;
            cf.FontId            = 0;
            cf.FillId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 4
            cf = new CellFormat();
            cf.NumberFormatId    = nf4decimal.NumberFormatId;
            cf.FontId            = 0;
            cf.FillId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 5
            cf = new CellFormat();
            cf.NumberFormatId    = nf2decimal.NumberFormatId;
            cf.FontId            = 0;
            cf.FillId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 6
            cf = new CellFormat();
            cf.NumberFormatId    = nfForcedText.NumberFormatId;
            cf.FontId            = 0;
            cf.FillId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 7
            // 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 8
            // 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
            // coloured 2 decimal text
            cf = new CellFormat();
            cf.NumberFormatId    = nf2decimal.NumberFormatId;
            cf.FontId            = 0;
            cf.FillId            = 2;
            cf.BorderId          = 2;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 10
            // coloured column text
            cf = new CellFormat();
            cf.NumberFormatId    = nfForcedText.NumberFormatId;
            cf.FontId            = 0;
            cf.FillId            = 2;
            cf.BorderId          = 2;
            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);

            this.Append(nfs);
            this.Append(fts);
            this.Append(fills);
            this.Append(borders);
            this.Append(csfs);
            this.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);
            this.Append(css);

            DifferentialFormats dfs = new DifferentialFormats();

            dfs.Count = 0;
            this.Append(dfs);

            TableStyles tss = new TableStyles();

            tss.Count             = 0;
            tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
            tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
            this.Append(tss);

            return;
        }
コード例 #33
0
        public NewExcel()
        {
            //创建默认字体样式1 Arial 24
            var fonts    = new Fonts();
            var font     = new DocumentFormat.OpenXml.Spreadsheet.Font();
            var fontName = new FontName {
                Val = StringValue.FromString("Arial")
            };
            var fontSize = new FontSize {
                Val = DoubleValue.FromDouble(11)
            };

            font.FontName = fontName;
            font.FontSize = fontSize;
            fonts.Append(font);
            fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
            //创建填充样式1
            var fills       = new Fills();
            var fill        = new Fill();
            var patternFill = new PatternFill {
                PatternType = PatternValues.None
            };

            fill.PatternFill = patternFill;
            fills.Append(fill);
            fill        = new Fill();
            patternFill = new PatternFill {
                PatternType = PatternValues.Gray0625
            };
            fill.PatternFill = patternFill;
            fills.Append(fill);
            fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
            //创建线条样式1
            var borders = new Borders();
            var border  = new Border {
                LeftBorder = new LeftBorder(), RightBorder = new RightBorder(), TopBorder = new TopBorder(), BottomBorder = new BottomBorder(), DiagonalBorder = new DiagonalBorder()
            };

            borders.Append(border);
            borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
            //创建单元格样式
            var cellStyleFormats = new CellStyleFormats();
            var cellFormat       = new CellFormat
            {
                NumberFormatId = 0,
                FontId         = 0,
                FillId         = 0,
                BorderId       = 0,
                FormatId       = 0
            };

            cellStyleFormats.Append(cellFormat);
            cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);

            var cellFormats = new CellFormats();

            cellFormat = new CellFormat
            {
                NumberFormatId = 0,
                FontId         = 0,
                FillId         = 0,
                BorderId       = 0,
                FormatId       = 0
            };
            cellFormats.Append(cellFormat);

            cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
            this.Append(fonts);
            this.Append(fills);
            this.Append(borders);
            this.Append(cellStyleFormats);
            this.Append(cellFormats);
            var css = new CellStyles();
            var cs  = new CellStyle
            {
                Name      = StringValue.FromString("Normal"),
                FormatId  = 0,
                BuiltinId = 0
            };

            css.Append(cs);
            css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
            this.Append(css);
            var dfs = new DifferentialFormats {
                Count = 0
            };

            this.Append(dfs);
            var tss = new TableStyles
            {
                Count             = 0,
                DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
                DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
            };

            this.Append(tss);
        }