static Dictionary <int, CellFormat> ReadStyleSheet(Spreadsheet document, Package.SpreadsheetDocument importDocument)
        {
            Dictionary <int, CellFormat> cellFormats = new Dictionary <int, CellFormat>();

            var importedStyleSheet = new Excel.Stylesheet();

            if (importDocument.WorkbookPart.WorkbookStylesPart != null)
            {
                importedStyleSheet.Load(importDocument.WorkbookPart.WorkbookStylesPart);
            }

            int index = 0;

            foreach (Excel.CellFormat item in importedStyleSheet.CellFormats)
            {
                if (item.ApplyFill != null && item.ApplyFill.Value && item.FillId != null)
                {
                    int fillID = (int)(uint)item.FillId.Value;
                    var fill   = (Excel.Fill)importedStyleSheet.Fills.ElementAt(fillID);

                    if (fill.PatternFill.ForegroundColor != null)
                    {
                        string fillColor = fill.PatternFill.ForegroundColor.Rgb;
                        cellFormats.Add(index, document.CellFormats().CellFormat(fillColor));
                    }
                }
                index++;
            }
            return(cellFormats);
        }
示例#2
0
        static void SaveDocument(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Spreadsheet document)
        {
            var exportedWorkbook = new Excel.Workbook();

            exportedWorkbookPart.Workbook = exportedWorkbook;

            Excel.Sheets exportedSheets = new Excel.Sheets();
            exportedWorkbook.AppendChild(exportedSheets);

            uint sheetId = 1;

            foreach (var sheet in document.Sheets)
            {
                SaveSheet(exportedWorkbookPart, styleSheet, cellFormatList, exportedSheets, sheet, sheetId++);
            }
        }
示例#3
0
        static void SaveRow(Excel.SheetData exportedSheetData, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Row row)
        {
            Excel.Row exportedRow = new Excel.Row()
            {
                RowIndex = RowIndex(row), Hidden = row._hidden
            };
            if (row._hidden)
            {
                exportedRow.Hidden = true;
            }
            exportedSheetData.Append(exportedRow);

            foreach (var cell in row._cells.OrderBy(r => r.Column._index))
            {
                SaveCell(exportedRow, styleSheet, cellFormatList, cell);
            }
        }
示例#4
0
        public static void Write(Spreadsheet document, string path)
        {
            var exportedDocument     = Package.SpreadsheetDocument.Create(path, OpenXml.SpreadsheetDocumentType.Workbook);
            var exportedWorkbookPart = exportedDocument.AddWorkbookPart();

            Dictionary <CellFormat, uint> cellFormatList = null;

            Excel.Stylesheet exportedStyleSheet = SaveStyleSheet(exportedWorkbookPart, ref cellFormatList, document);

            SaveDocument(exportedWorkbookPart, exportedStyleSheet, cellFormatList, document);

/*
 *          var exportedSharedStringTablePart = exportedWorkbookPart.AddNewPart<Package.SharedStringTablePart>();
 *          exportedSharedStringTablePart.SharedStringTable = SaveSharedStringTable();
 *          exportedSharedStringTablePart.SharedStringTable.Save();
 */

            exportedWorkbookPart.Workbook.Save();
            exportedDocument.Close();
        }
示例#5
0
        public void Write(Stylesheet element)
        {
            if (element == null)
            {
                throw new ArgumentNullException(nameof(element));
            }

            // create stylesheet
            var stylesheet = new OpenXml.Stylesheet();

            Write(stylesheet, element.Fonts);
            Write(stylesheet, element.Fills);
            Write(stylesheet, element.Borders);
            Write(stylesheet, element.NumberFormats);
            Write(stylesheet, element.CellFormats);

            // write to part
            workbookStylesPart.Stylesheet = stylesheet;
            stylesheet.Save();
        }
示例#6
0
/*
 *      static Excel.SharedStringTable SaveSharedStringTable()
 *      {
 *          var exportedSharedStringTable = new Excel.SharedStringTable();
 *
 *          return exportedSharedStringTable;
 *      }
 */

        static void SaveSheet(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Excel.Sheets exportedSheets, Sheet sheet, uint sheetId)
        {
            var    exportedWorksheetPart = exportedWorkbookPart.AddNewPart <Package.WorksheetPart>();
            string relId = exportedWorkbookPart.GetIdOfPart(exportedWorksheetPart);

            var exportedWorksheet = new Excel.Worksheet();

            exportedWorksheetPart.Worksheet = exportedWorksheet;

            var exportedColumns = new Excel.Columns();

            exportedWorksheet.Append(exportedColumns);

            var exportedSheetData = new Excel.SheetData();

            exportedWorksheet.Append(exportedSheetData);

            var exportedSheet = new Excel.Sheet()
            {
                Name = sheet.Name, Id = relId, SheetId = sheetId
            };

            if (sheet.Hidden)
            {
                exportedSheet.State = Excel.SheetStateValues.Hidden;
            }
            exportedSheets.Append(exportedSheet);

            foreach (var column in sheet.Columns.OrderBy(r => r.Index))
            {
                SaveColumn(exportedColumns, column);
            }

            foreach (var row in sheet.Rows.OrderBy(r => r.Index))
            {
                SaveRow(exportedSheetData, styleSheet, cellFormatList, row);
            }

            exportedWorksheetPart.Worksheet.Save();
        }
示例#7
0
        static void SaveCell(Excel.Row exportedRow, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Cell cell)
        {
            Excel.Cell exportedCell = new Excel.Cell()
            {
                CellReference = CellIndex(cell.Column, cell.Row)
            };
            exportedCell.DataType = new OpenXml.EnumValue <Excel.CellValues>(Excel.CellValues.String);
            switch (cell.Type)
            {
            case ExcelValueType.String:
                exportedCell.CellValue = new Excel.CellValue(cell.StringValue);
                exportedCell.DataType  = new OpenXml.EnumValue <Excel.CellValues>(Excel.CellValues.String);
                break;

            case ExcelValueType.Number:
                exportedCell.CellValue = new Excel.CellValue(cell.NumberValue.ToString());
                exportedCell.DataType  = new OpenXml.EnumValue <Excel.CellValues>(Excel.CellValues.Number);
                break;

            case ExcelValueType.Boolean:
                exportedCell.CellValue = new Excel.CellValue((cell.BooleanValue ? 1 : 0).ToString());
                exportedCell.DataType  = new OpenXml.EnumValue <Excel.CellValues>(Excel.CellValues.Boolean);
                break;

            case ExcelValueType.Null:
                break;

            default:
                throw new Exception("Unsupported type");
            }

            if (cell.CellFormat != null)
            {
                exportedCell.StyleIndex = cellFormatList[cell.CellFormat];
            }

            exportedRow.Append(exportedCell);
        }
示例#8
0
        private static Stylesheet InitializeDefaultStyles(Stylesheet stylesheet)
        {
            //!!!!!!!!!!!!!!!!!!!!!!!!!
            //Просьба не переделывать этот метод, это связано с непонятным багом в библиотеке Office XML SDK
            //!!!!!!!!!!!!!!!!!!!!!!!!!
            stylesheet         = new DocumentFormat.OpenXml.Spreadsheet.Stylesheet();
            stylesheet.Borders = new DocumentFormat.OpenXml.Spreadsheet.Borders();
            stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(new DocumentFormat.OpenXml.Spreadsheet.Border());
            stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(new DocumentFormat.OpenXml.Spreadsheet.Border());

            stylesheet.Fills = new DocumentFormat.OpenXml.Spreadsheet.Fills();
            stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(new DocumentFormat.OpenXml.Spreadsheet.Fill());
            stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(new DocumentFormat.OpenXml.Spreadsheet.Fill());

            stylesheet.Fonts = new DocumentFormat.OpenXml.Spreadsheet.Fonts();
            stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(new DocumentFormat.OpenXml.Spreadsheet.Font());
            stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(new DocumentFormat.OpenXml.Spreadsheet.Font());

            stylesheet.CellFormats = new DocumentFormat.OpenXml.Spreadsheet.CellFormats();
            stylesheet.CellFormats.AppendChild <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(new DocumentFormat.OpenXml.Spreadsheet.CellFormat());
            stylesheet.CellFormats.AppendChild <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(new DocumentFormat.OpenXml.Spreadsheet.CellFormat());

            return(stylesheet);
        }
示例#9
0
        void Write(OpenXml.Stylesheet stylesheet, CountedCollection <NumberFormat> numberFormats)
        {
            IWriter <CountedCollection <NumberFormat> > numberFormatWriter = new NumberFormatWriter(stylesheet);

            numberFormatWriter.Write(numberFormats);
        }
示例#10
0
        void Write(OpenXml.Stylesheet stylesheet, CountedCollection <CellFormat> cellFormats)
        {
            IWriter <CountedCollection <CellFormat> > cellFormatWriter = new CellFormatWriter(stylesheet);

            cellFormatWriter.Write(cellFormats);
        }
示例#11
0
        void Write(OpenXml.Stylesheet stylesheet, CountedCollection <Border> borders)
        {
            IWriter <CountedCollection <Border> > borderWriter = new BorderWriter(stylesheet);

            borderWriter.Write(borders);
        }
示例#12
0
        void Write(OpenXml.Stylesheet stylesheet, CountedCollection <Fill> fills)
        {
            IWriter <CountedCollection <Fill> > fillWriter = new FillWriter(stylesheet);

            fillWriter.Write(fills);
        }
示例#13
0
        void Write(OpenXml.Stylesheet stylesheet, CountedCollection <Font> fonts)
        {
            IWriter <CountedCollection <Font> > fontWriter = new FontWriter(stylesheet);

            fontWriter.Write(fonts);
        }
示例#14
0
        private static void GerarExcelOpXml(System.Data.DataTable dt, string pathExcelFile, string sheetName,
                                            string ColunaDefault, int rowDefault, int larguraColuna)
        {
            var styleIndexDefault     = 0;
            var styleIndexDefaultLine = 2;

            DocumentFormat.OpenXml.UInt32Value styleIndex;

            using (OpenXmlPackaging.SpreadsheetDocument myDoc =
                       OpenXmlPackaging.SpreadsheetDocument.Open(pathExcelFile, true))
            {
                OpenXmlPackaging.WorksheetPart worksheetPart = GetWorksheetPartByName(myDoc, sheetName);

                OpenXmlSpread.Cell cellDefault =
                    GetCell(worksheetPart.Worksheet, ColunaDefault, Convert.ToUInt32(rowDefault));
                styleIndex        = cellDefault.StyleIndex;
                styleIndexDefault = Convert.ToInt32(styleIndex.Value.ToString());

                OpenXmlSpread.Cell cellDefaultline =
                    GetCell(worksheetPart.Worksheet, ColunaDefault, Convert.ToUInt32(rowDefault + 1));
                styleIndex            = cellDefaultline.StyleIndex;
                styleIndexDefaultLine = Convert.ToInt32(styleIndex.Value.ToString());
            }

            using (OpenXmlPackaging.SpreadsheetDocument myDoc =
                       OpenXmlPackaging.SpreadsheetDocument.Open(pathExcelFile, true))
            {
                OpenXmlPackaging.WorksheetPart worksheetPart = GetWorksheetPartByName(myDoc, sheetName);
                OpenXmlSpread.Stylesheet       stylesheet    = myDoc.WorkbookPart.WorkbookStylesPart.Stylesheet;

                OpenXmlSpread.CellFormat deafultFormat = new OpenXmlSpread.CellFormat()
                {
                    Alignment = new OpenXmlSpread.Alignment()
                    {
                        Horizontal = OpenXmlSpread.HorizontalAlignmentValues.Left,
                        Vertical   = OpenXmlSpread.VerticalAlignmentValues.Center
                    },
                    ApplyAlignment = true
                };

                stylesheet.CellFormats.AppendChild(deafultFormat);

                DocumentFormat.OpenXml.OpenXmlWriter
                    writer = DocumentFormat.OpenXml.OpenXmlWriter.Create(worksheetPart);

                var indexColumn      = ColumnIndex(ColunaDefault);
                var startColunaIndex = indexColumn + 1;

                writer.WriteStartElement(new OpenXmlSpread.Worksheet());
                writer.WriteStartElement(new OpenXmlSpread.Columns());

                AjustaLarguraColunas(writer, startColunaIndex, larguraColuna, dt.Columns.Count);

                writer.WriteStartElement(new OpenXmlSpread.SheetData());
                writer.WriteStartElement(new OpenXmlSpread.Row {
                    RowIndex = (UInt32)rowDefault
                });

                CriarColunas(writer, dt, indexColumn, rowDefault, styleIndexDefault);

                CriarLinha(writer, dt, indexColumn, rowDefault, styleIndexDefaultLine);

                writer.Dispose();
            }
        }
示例#15
0
        static Excel.Stylesheet SaveStyleSheet(Package.WorkbookPart exportedWorkbookPart, ref Dictionary <CellFormat, uint> cellFormatList, Spreadsheet document)
        {
            var exportedStyleSheetPart = exportedWorkbookPart.AddNewPart <Package.WorkbookStylesPart>();

            cellFormatList = new Dictionary <CellFormat, uint>();
            Excel.Stylesheet exportedStyleSheet = new Excel.Stylesheet();
            exportedStyleSheetPart.Stylesheet = exportedStyleSheet;
            exportedStyleSheet.CellFormats    = new Excel.CellFormats();
            exportedStyleSheet.CellFormats.Append(new Excel.CellFormat()
            {
                NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
            });
            exportedStyleSheet.Fills = new Excel.Fills();
            exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill()
            {
                PatternType = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.None)
            }));

            exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill()
            {
                PatternType = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.Gray125)
            }));
            exportedStyleSheet.Fonts = new Excel.Fonts();
            exportedStyleSheet.Fonts.Append(new Excel.Font()
            {
                FontSize = new Excel.FontSize()
                {
                    Val = 11
                },
                Color = new Excel.Color()
                {
                    Theme = 1
                },
                FontName = new Excel.FontName()
                {
                    Val = "Calibri"
                },
                FontFamilyNumbering = new Excel.FontFamilyNumbering()
                {
                    Val = 2
                },
                FontScheme = new Excel.FontScheme()
                {
                    Val = new OpenXml.EnumValue <Excel.FontSchemeValues>(Excel.FontSchemeValues.Minor)
                }
            });
            exportedStyleSheet.Borders = new Excel.Borders();
            exportedStyleSheet.Borders.Append(new Excel.Border()
            {
                LeftBorder     = new Excel.LeftBorder(),
                RightBorder    = new Excel.RightBorder(),
                TopBorder      = new Excel.TopBorder(),
                BottomBorder   = new Excel.BottomBorder(),
                DiagonalBorder = new Excel.DiagonalBorder()
            });
            exportedStyleSheet.CellStyleFormats = new Excel.CellStyleFormats();
            exportedStyleSheet.CellStyleFormats.Append(new Excel.CellFormat()
            {
                NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0
            });
            exportedStyleSheet.CellStyles = new Excel.CellStyles();
            exportedStyleSheet.CellStyles.Append(new Excel.CellStyle()
            {
                Name = "Normal", FormatId = 0, BuiltinId = 0
            });
            exportedStyleSheet.DifferentialFormats = new Excel.DifferentialFormats();
            exportedStyleSheet.TableStyles         = new Excel.TableStyles()
            {
                DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16"
            };

            foreach (var cellFormat in document.CellFormats())
            {
                if (cellFormat.Count() > 0)
                {
                    cellFormatList.Add(cellFormat, (uint)exportedStyleSheet.CellFormats.ChildElements.Count());
                    exportedStyleSheet.CellFormats.Append(new Excel.CellFormat()
                    {
                        NumberFormatId = 0,
                        FontId         = 0,
                        BorderId       = 0,
                        FormatId       = 0,
                        FillId         = (uint)exportedStyleSheet.Fills.ChildElements.Count(),
                        ApplyFill      = true
                    });
                    exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill()
                    {
                        PatternType     = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.Solid),
                        ForegroundColor = new Excel.ForegroundColor()
                        {
                            Rgb = new OpenXml.HexBinaryValue(cellFormat.FillColor)
                        }
                    }));
                }
            }

            exportedStyleSheet.Save();
            return(exportedStyleSheet);
        }
示例#16
0
        /// <summary>
        /// Adds the basic styles to the workbook
        /// </summary>
        /// <param name="spreadsheet">Spreadsheet to use</param>
        /// <returns>True if succesful</returns>
        public static bool AddBasicStyles(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet)
        {
            DocumentFormat.OpenXml.Spreadsheet.Stylesheet stylesheet = spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet;

            // Numbering formats (x:numFmts)
            stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.NumberingFormats>(new DocumentFormat.OpenXml.Spreadsheet.NumberingFormats(), 0);
            // Currency
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.NumberingFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.NumberingFormat>(
                new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat()
            {
                NumberFormatId = 164,
                FormatCode     = "#,##0.00"
                                 + "\\ \"" + System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencySymbol + "\""
            }, 0);

            // Fonts (x:fonts)
            stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fonts>(new DocumentFormat.OpenXml.Spreadsheet.Fonts(), 1);
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Fonts>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Font>(
                new DocumentFormat.OpenXml.Spreadsheet.Font()
            {
                FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize()
                {
                    Val = 11
                },
                FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName()
                {
                    Val = "Calibri"
                }
            }, 0);

            // Fills (x:fills)
            stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fills>(new DocumentFormat.OpenXml.Spreadsheet.Fills(), 2);
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Fills>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fill>(
                new DocumentFormat.OpenXml.Spreadsheet.Fill()
            {
                PatternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill()
                {
                    PatternType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.PatternValues>()
                    {
                        Value = DocumentFormat.OpenXml.Spreadsheet.PatternValues.None
                    }
                }
            }, 0);
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Fills>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fill>(
                new DocumentFormat.OpenXml.Spreadsheet.Fill()
            {
                PatternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill()
                {
                    PatternType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.PatternValues>()
                    {
                        Value = DocumentFormat.OpenXml.Spreadsheet.PatternValues.Gray125
                    }
                }
            }, 1);

            // Borders (x:borders)
            stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Borders>(new DocumentFormat.OpenXml.Spreadsheet.Borders(), 3);
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Borders>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Border>(
                new DocumentFormat.OpenXml.Spreadsheet.Border()
            {
                LeftBorder     = new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(),
                RightBorder    = new DocumentFormat.OpenXml.Spreadsheet.RightBorder(),
                TopBorder      = new DocumentFormat.OpenXml.Spreadsheet.TopBorder(),
                BottomBorder   = new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(),
                DiagonalBorder = new DocumentFormat.OpenXml.Spreadsheet.DiagonalBorder()
            }, 0);

            // Cell style formats (x:CellStyleXfs)
            stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats>(new DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats(), 4);
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
                new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
            {
                NumberFormatId = 0,
                FontId         = 0,
                FillId         = 0,
                BorderId       = 0
            }, 0);

            // Cell formats (x:CellXfs)
            stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormats>(new DocumentFormat.OpenXml.Spreadsheet.CellFormats(), 5);
            // General text
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
                new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
            {
                FormatId       = 0,
                NumberFormatId = 0
            }, 0);
            // Date
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
                new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
            {
                ApplyNumberFormat = true,
                FormatId          = 0,
                NumberFormatId    = 22,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0
            },
                1);
            // Currency
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
                new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
            {
                ApplyNumberFormat = true,
                FormatId          = 0,
                NumberFormatId    = 164,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0
            },
                2);
            // Percentage
            stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(
                new DocumentFormat.OpenXml.Spreadsheet.CellFormat()
            {
                ApplyNumberFormat = true,
                FormatId          = 0,
                NumberFormatId    = 10,
                FontId            = 0,
                FillId            = 0,
                BorderId          = 0
            },
                3);

            stylesheet.Save();

            return(true);
        }
示例#17
0
 public FillWriter(OpenXml.Stylesheet stylesheet)
 {
     this.stylesheet = stylesheet ?? throw new ArgumentNullException(nameof(stylesheet));
 }
示例#18
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);
        }