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