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