private Stylesheet CreateStylesheet()
{
var stylesheet = new Stylesheet();
// Order is important!
stylesheet.AppendChild(_numberingFormatProvider.GetNumberingFormats());
stylesheet.AppendChild(_fontProvider.GetFonts());
stylesheet.AppendChild(_fillProvider.GetFills());
stylesheet.AppendChild(_borderProvider.GetBorders());
stylesheet.AppendChild(CreateCellFormats());
return(stylesheet);
}
/// <summary>
/// Import cell format
/// </summary>
/// <param name="sourceStyleSheet"></param>
/// <param name="srcFormat"></param>
/// <param name="targetStyleSheet"></param>
/// <returns></returns>
protected static uint ImportCellFormat(Stylesheet sourceStyleSheet, CellFormat srcFormat, Stylesheet targetStyleSheet)
{
var tgtCellFormats = targetStyleSheet.CellFormats ??
targetStyleSheet.AppendChild(new CellFormats());
var tgtCellFormat = tgtCellFormats.AppendChild(srcFormat.Clone() as CellFormat);
tgtCellFormats.Count++;
// Import Style
if (srcFormat.FormatId != null)
{
var srcStyle = sourceStyleSheet.CellStyleFormats.ElementAt((int)srcFormat.FormatId.Value) as CellFormat;
if (srcStyle == null)
{
throw new InvalidOperationException(String.Format("Style {0} was not found in source document!",
srcFormat.FormatId));
}
// Style is complex, so just clone it
var tgtStyle = srcStyle.Clone() as CellFormat;
// Add style to target
if (targetStyleSheet.CellStyleFormats == null)
{
targetStyleSheet.CellStyleFormats = new CellStyleFormats();
}
targetStyleSheet.CellStyleFormats.AppendChild(tgtStyle);
tgtCellFormat.FormatId = targetStyleSheet.CellStyleFormats.Count - 1;
// Import details
ImportCellFormatDetails(sourceStyleSheet, tgtStyle, targetStyleSheet);
}
// Import details
ImportCellFormatDetails(sourceStyleSheet, tgtCellFormat, targetStyleSheet);
return(tgtCellFormats.Count - 1);
}
/// <summary>
/// Import cell format
/// </summary>
/// <param name="sourceStyleSheet"></param>
/// <param name="srcFormat"></param>
/// <param name="targetStyleSheet"></param>
/// <returns></returns>
protected static uint ImportCellFormat(Stylesheet sourceStyleSheet, CellFormat srcFormat, Stylesheet targetStyleSheet)
{
var tgtCellFormats = targetStyleSheet.CellFormats ??
targetStyleSheet.AppendChild(new CellFormats());
var tgtCellFormat = tgtCellFormats.AppendChild(srcFormat.Clone() as CellFormat);
tgtCellFormats.Count ++;
// Import Style
if (srcFormat.FormatId != null)
{
var srcStyle = sourceStyleSheet.CellStyleFormats.ElementAt((int) srcFormat.FormatId.Value) as CellFormat;
if (srcStyle == null)
throw new InvalidOperationException(String.Format("Style {0} was not found in source document!",
srcFormat.FormatId));
// Style is complex, so just clone it
var tgtStyle = srcStyle.Clone() as CellFormat;
// Add style to target
if (targetStyleSheet.CellStyleFormats == null)
targetStyleSheet.CellStyleFormats = new CellStyleFormats();
targetStyleSheet.CellStyleFormats.AppendChild(tgtStyle);
tgtCellFormat.FormatId = targetStyleSheet.CellStyleFormats.Count - 1;
// Import details
ImportCellFormatDetails(sourceStyleSheet, tgtStyle, targetStyleSheet);
}
// Import details
ImportCellFormatDetails(sourceStyleSheet, tgtCellFormat, targetStyleSheet);
return tgtCellFormats.Count - 1;
}
// Stylesheet has to follow this order:
// Font -> Fills/Borders -> CellFormats
// If you change *any* of the order, Excel will consider the spreadsheet broken.
internal Stylesheet CreateStylesheet()
{
var stylesheet = new Stylesheet();
var fonts = new Fonts();
fonts.AppendChild(new Font
{
Bold = new Bold(),
FontName = new FontName {
Val = "Microsoft YaHei"
},
FontSize = new FontSize {
Val = 12
},
FontFamilyNumbering = new FontFamilyNumbering {
Val = 1
}
});
fonts.AppendChild(new Font
{
FontName = new FontName {
Val = "Microsoft YaHei Light"
},
FontSize = new FontSize {
Val = 12
},
FontFamilyNumbering = new FontFamilyNumbering {
Val = 1
}
});
fonts.KnownFonts = true;
fonts.Count = (uint)fonts.ChildElements.Count;
stylesheet.AppendChild(fonts);
// Default everything else because Excel considers this
// spreadsheet broken if it's missing *any* of these.
Fill fill = new Fill()
{
PatternFill = new PatternFill()
};
Fills fills = new Fills();
fills.AppendChild(fill);
fills.Count = (uint)fills.ChildElements.Count;
stylesheet.AppendChild(fills);
Border border = new Border()
{
LeftBorder = new LeftBorder(), RightBorder = new RightBorder(), BottomBorder = new BottomBorder(), DiagonalBorder = new DiagonalBorder(), TopBorder = new TopBorder()
};
Borders borders = new Borders();
borders.AppendChild(border);
borders.Count = (uint)borders.ChildElements.Count;
stylesheet.AppendChild(borders);
// Now we can actually define the cell formats.
// Screw OpenXML.
var cellFormats = new CellFormats();
var titleCellFormat = new CellFormat()
{
FontId = 0, FillId = 0, BorderId = 0
};
var regularCellFormat = new CellFormat()
{
FontId = 1, FillId = 0, BorderId = 0, ApplyFont = true
};
cellFormats.AppendChild(titleCellFormat);
cellFormats.AppendChild(regularCellFormat);
cellFormats.Count = (uint)cellFormats.ChildElements.Count;
stylesheet.AppendChild(cellFormats);
return(stylesheet);
}
public 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");
var fonts1 = AddFonts();
var fills1 = AddFills();
var borders1 = AddBorders();
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = 1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U
};
cellStyleFormats1.AppendChild(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = 4U
};
// Black text on White background
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U
};
// White text on Orange background
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = 0U, FontId = 1U, FillId = 2U, BorderId = 0U, FormatId = 0U, ApplyFill = true
};
// White text on Blue background
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = 0U, FontId = 1U, FillId = 3U, BorderId = 0U, FormatId = 0U, ApplyFill = true
};
// Black text on Yellow background
CellFormat cellFormat5 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 4U, BorderId = 0U, FormatId = 0U, ApplyFill = true
};
cellFormats1.AppendChild(cellFormat2);
cellFormats1.AppendChild(cellFormat3);
cellFormats1.AppendChild(cellFormat4);
cellFormats1.AppendChild(cellFormat5);
CellStyles cellStyles1 = new CellStyles()
{
Count = 1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = 0U, BuiltinId = 0U
};
cellStyles1.AppendChild(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = 0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = 0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9"
};
StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension.AppendChild(slicerStyles);
stylesheetExtensionList.AppendChild(stylesheetExtension);
stylesheet1.AppendChild(fonts1);
stylesheet1.AppendChild(fills1);
stylesheet1.AppendChild(borders1);
stylesheet1.AppendChild(cellStyleFormats1);
stylesheet1.AppendChild(cellFormats1);
stylesheet1.AppendChild(cellStyles1);
stylesheet1.AppendChild(differentialFormats1);
stylesheet1.AppendChild(tableStyles1);
stylesheet1.AppendChild(stylesheetExtensionList);
return(stylesheet1);
}