private Fonts BuildFonts()
{
var fonts = new Fonts();
fonts.AppendChild(BuildFont(11, "000000", "Calibri"));
FontDefaultId = 0;
fonts.AppendChild(BuildFont(11, "000000", "Calibri", true));
FontBoldId = 1;
fonts.AppendChild(BuildFont(11, "000000", "Calibri", false, true));
FontItalicId = 2;
return(fonts);
}
public CustomStylesheet()
{
// blank font list
var fonts = new Fonts();
fonts.AppendChild(new Font());
fonts.Count = 1;
Append(fonts);
// create fills
var fills = new Fills();
// create a solid blue fill
var solidBlue = new PatternFill() { PatternType = PatternValues.Solid };
solidBlue.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("397FDB") }; // blue fill
solidBlue.BackgroundColor = new BackgroundColor { Indexed = 64 };
fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } }); // required, reserved by Excel
fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Gray125 } }); // required, reserved by Excel
fills.AppendChild(new Fill { PatternFill = solidBlue });
fills.Count = 3;
Append(fills);
// blank border list
var borders = new Borders();
borders.AppendChild(new Border());
borders.AppendChild(new Border()
{
TopBorder = new TopBorder() { Style = BorderStyleValues.Thin },
RightBorder = new RightBorder() { Style = BorderStyleValues.Thin },
BottomBorder = new BottomBorder() { Style = BorderStyleValues.Thin },
LeftBorder = new LeftBorder() { Style = BorderStyleValues.Thin }
});
borders.Count = 2;
Append(borders);
// blank cell format list
var cellStyleFormats = new CellStyleFormats();
cellStyleFormats.AppendChild(new CellFormat());
cellStyleFormats.Count = 1;
Append(cellStyleFormats);
// cell format list
var cellFormats = new CellFormats();
// empty one for index 0, seems to be required
cellFormats.AppendChild(new CellFormat());
// cell format default with border
cellFormats.AppendChild(new CellFormat() { FormatId = 0, FontId = 0, BorderId = 1, FillId = 0 }).AppendChild(new Alignment() { WrapText = true });
// cell format for header (blue with border)
cellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 0, BorderId = 1, FillId = 2, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center });
cellFormats.Count = 2;
Append(cellFormats);
}
private static Fonts CreateFonts()
{
var fts = new Fonts();
// font 0
fts.AppendChild(new Font
{
FontName = new FontName {
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
}
});
// font 1
fts.AppendChild(new Font
{
FontName = new FontName {
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(12)
},
Bold = new Bold()
});
// font 2
fts.AppendChild(new Font
{
FontName = new FontName {
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(18)
},
Bold = new Bold()
});
// font 3
fts.AppendChild(new Font
{
FontName = new FontName {
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(14)
}
});
// font 4
fts.AppendChild(new Font
{
Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
Rgb = HexBinaryValueFromColor(Color.MediumBlue)
},
FontName = new FontName {
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
}
});
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
return(fts);
}
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);
}
private static Stylesheet SetDefault()
{
//Fonts
var fonts = new Fonts();
var font0 = new Font();
var fontSize0 = new FontSize {
Val = 10
};
font0.AppendChild(fontSize0);
var font1 = new Font();
var fontSize1 = new FontSize {
Val = 12
};
var fontBold1 = new Bold();
var fontColor1 = new Color {
Rgb = "FFFFFF"
};
font1.AppendChild(fontSize1);
font1.AppendChild(fontBold1);
font1.AppendChild(fontColor1);
fonts.AppendChild(font0);
fonts.AppendChild(font1);
// Alignments
var alignmentCenter = new Alignment();
alignmentCenter.Horizontal = HorizontalAlignmentValues.Center;
//Fill
var fills = new Fills();
var fill0 = new Fill();
var patternFill0 = new PatternFill {
PatternType = PatternValues.None
};
fill0.Append(patternFill0);
var fillSkip = new Fill(); // Not valid needs to skip
var patternFillSkip = new PatternFill {
PatternType = PatternValues.None
};
fillSkip.Append(patternFillSkip);
var fill1 = new Fill();
var patternFill1 = new PatternFill {
PatternType = PatternValues.Solid
};
var foregroundColor1 = new ForegroundColor {
Rgb = "00000000"
};
var backgroundColor1 = new BackgroundColor {
Indexed = 64U
};
patternFill1.Append(foregroundColor1);
patternFill1.Append(backgroundColor1);
fill1.Append(patternFill1);
fills.Append(fill0);
fills.Append(fillSkip);
fills.Append(fill1);
//Border
var borders = new Borders();
var border0 = new Border();
var border1 = new Border();
borders.AppendChild(border0);
borders.AppendChild(border1);
var cellFormats = new CellFormats(
new CellFormat {
FontId = 0, FillId = 0, BorderId = 0, ApplyFill = true
}, // Index 0
new CellFormat {
FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true, Alignment = alignmentCenter
} // Index 1
);
return(new Stylesheet(fonts, fills, borders, cellFormats));
}
private void AppendWithIndexSave(Fonts parent, Font child, ExcelStylesheetFontIndex excelSheetIndex)
{
parent.AppendChild(child);
_indexes.Add(excelSheetIndex, (uint)_indexes.Count);
}
public ExcelStylesheetProvider()
{
var fonts = new Fonts();
//default Font
fonts.AppendChild(new Font {
Color = new Color()
});
uint defaultFontsCount = (uint)fonts.ChildElements.Count;
var fills = new Fills();
//default Fills
fills.AppendChild(new Fill(new PatternFill {
PatternType = PatternValues.None
}));
fills.AppendChild(new Fill(new PatternFill {
PatternType = PatternValues.Gray125
}));
uint defaultFillsCount = (uint)fills.ChildElements.Count;
foreach (var color in Colors.Values)
{
fonts.AppendChild(new Font
{
Color = new Color {
Rgb = color
}
});
fonts.AppendChild(new Font
{
Color = new Color {
Rgb = color
},
Bold = new Bold()
});
fonts.AppendChild(new Font
{
Color = new Color {
Rgb = color
},
Strike = new Strike()
});
fonts.AppendChild(new Font
{
Color = new Color {
Rgb = color
},
Bold = new Bold(),
Strike = new Strike()
});
fills.AppendChild(new Fill
(
new PatternFill
{
ForegroundColor = new ForegroundColor {
Rgb = color
},
PatternType = PatternValues.Solid
}
));
}
fonts.Count = (uint)fonts.ChildElements.Count;
fills.Count = (uint)fills.ChildElements.Count;
var borders = new Borders();
//default Border
borders.AppendChild(new Border());
borders.Append(new Border
{
LeftBorder = new LeftBorder
{
Style = BorderStyleValues.Medium,
Color = new Color
{
Indexed = (UInt32Value)64U
}
},
RightBorder = new RightBorder
{
Style = BorderStyleValues.Medium,
Color = new Color
{
Indexed = (UInt32Value)64U
}
},
TopBorder = new TopBorder
{
Style = BorderStyleValues.Medium,
Color = new Color
{
Indexed = (UInt32Value)64U
}
},
BottomBorder = new BottomBorder
{
Style = BorderStyleValues.Medium,
Color = new Color
{
Indexed = (UInt32Value)64U
}
},
DiagonalBorder = new DiagonalBorder()
});
borders.Count = (uint)borders.ChildElements.Count;
var cellFormats = new CellFormats();
//default CellFormat
cellFormats.AppendChild(new CellFormat {
FontId = 0, FillId = 0, BorderId = 0
});
uint fontIndex = 0;
uint csIndex = 0;
foreach (Font font in fonts.ChildElements)
{
if (fontIndex < defaultFontsCount)
{
fontIndex++;
continue;
}
uint fillIndex = 0;
foreach (Fill fill in fills.ChildElements)
{
if (fillIndex < defaultFillsCount)
{
fillIndex++;
continue;
}
foreach (var typeDetails in SupportedTypesFormats.Data)
{
foreach (HorizontalAlignment hor in Enum.GetValues(typeof(HorizontalAlignment)))
{
foreach (VerticalAlignment ver in Enum.GetValues(typeof(VerticalAlignment)))
{
cellFormats.AppendChild(new CellFormat
{
ApplyNumberFormat = true,
NumberFormatId = typeDetails.Value,
ApplyAlignment = true,
Alignment = new Alignment
{
Vertical = ToVerticalAlignmentValues(ver),
WrapText = true,
Horizontal = ToHorizontalAlignmentValues(hor)
},
ApplyBorder = true,
BorderId = 1,
ApplyFont = true,
FontId = fontIndex,
ApplyFill = true,
FillId = fillIndex,
FormatId = 0
});
csIndex++;
_styles[GetKey(typeDetails.Key,
font.Color.Rgb,
fill.PatternFill.ForegroundColor.Rgb.Value,
font.Bold != null,
font.Strike != null,
hor, ver)] = csIndex;
}
}
}
fillIndex++;
}
fontIndex++;
}
cellFormats.Count = (uint)cellFormats.ChildElements.Count;
Stylesheet = new Stylesheet(fonts, fills, borders, cellFormats);
}
// 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);
}
