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 Borders CreateBorders()
{
var borders = new Borders();
// boarder index 0
borders.AppendChild(new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
});
// boarder Index 1
borders.AppendChild(new Border
{
LeftBorder = new LeftBorder {
Style = BorderStyleValues.Thin
},
RightBorder = new RightBorder {
Style = BorderStyleValues.Thin
},
TopBorder = new TopBorder {
Style = BorderStyleValues.Thin
},
BottomBorder = new BottomBorder {
Style = BorderStyleValues.Thin
},
DiagonalBorder = new DiagonalBorder()
});
// boarder Index 2
borders.AppendChild(new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder {
Style = BorderStyleValues.Thin
},
BottomBorder = new BottomBorder {
Style = BorderStyleValues.Thin
},
DiagonalBorder = new DiagonalBorder()
});
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
return(borders);
}
private static Borders CreateBorders()
{
var borders = new Borders();
// boarder index 0
var border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.BottomBorder = new BottomBorder();
border.DiagonalBorder = new DiagonalBorder();
borders.AppendChild(border);
// boarder Index 1
border = new Border();
border.LeftBorder = new LeftBorder();
border.LeftBorder.Style = BorderStyleValues.Thin;
border.RightBorder = new RightBorder();
border.RightBorder.Style = BorderStyleValues.Thin;
border.TopBorder = new TopBorder();
border.TopBorder.Style = BorderStyleValues.Thin;
border.BottomBorder = new BottomBorder();
border.BottomBorder.Style = BorderStyleValues.Thin;
border.DiagonalBorder = new DiagonalBorder();
borders.AppendChild(border);
// boarder Index 2
border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.TopBorder.Style = BorderStyleValues.Thin;
border.BottomBorder = new BottomBorder();
border.BottomBorder.Style = BorderStyleValues.Thin;
border.DiagonalBorder = new DiagonalBorder();
borders.AppendChild(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
return(borders);
}
private static Borders AddBorders()
{
Borders borders1 = new Borders()
{
Count = 1U
};
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.AppendChild(leftBorder1);
border1.AppendChild(rightBorder1);
border1.AppendChild(topBorder1);
border1.AppendChild(bottomBorder1);
border1.AppendChild(diagonalBorder1);
borders1.AppendChild(border1);
return(borders1);
}
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));
}
public SpreadsheetStylesManager(WorkbookPart workbookPart)
{
_workbookPart = workbookPart;
_styles = new Dictionary <string, int>();
_fonts = new Dictionary <FontKey, int>();
_fills = new Dictionary <System.Drawing.Color, int>();
var stylesPart = _workbookPart.AddNewPart <WorkbookStylesPart>();
_stylesheet = new Stylesheet();
stylesPart.Stylesheet = _stylesheet;
_stylesheet.Fills = new Fills();
_stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
}
}); // required, reserved by Excel
_stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.Gray125
}
}); // required, reserved by Excel
_stylesheet.Fills.Count = 2;
stylesPart.Stylesheet.Fonts = new Fonts();
stylesPart.Stylesheet.Fonts.Count = 2;
stylesPart.Stylesheet.Fonts.AppendChild(new Font());
Font hyperLinkFont = new Font();
Underline underline1 = new Underline();
FontSize fontSize2 = new FontSize()
{
Val = 11D
};
Color color2 = new Color()
{
Theme = (UInt32Value)10U
};
FontName fontName2 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
{
Val = 2
};
FontCharSet fontCharSet2 = new FontCharSet()
{
Val = 238
};
FontScheme fontScheme2 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
hyperLinkFont.Append(underline1);
hyperLinkFont.Append(fontSize2);
hyperLinkFont.Append(color2);
hyperLinkFont.Append(fontName2);
hyperLinkFont.Append(fontFamilyNumbering2);
hyperLinkFont.Append(fontCharSet2);
hyperLinkFont.Append(fontScheme2);
stylesPart.Stylesheet.Fonts.AppendChild(hyperLinkFont);
stylesPart.Stylesheet.Borders = new Borders();
stylesPart.Stylesheet.Borders.Count = 1;
stylesPart.Stylesheet.Borders.AppendChild(new Border());
stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
stylesPart.Stylesheet.CellStyleFormats.Count = 2;
stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat());
stylesPart.Stylesheet.CellFormats = new CellFormats();
// empty one for index 0, seems to be required
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());
CellFormat hyperLinkFormt = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, ApplyNumberFormat = false, ApplyFill = false, ApplyBorder = false, ApplyAlignment = false, ApplyProtection = false
};
stylesPart.Stylesheet.CellFormats.AppendChild(hyperLinkFormt);
stylesPart.Stylesheet.CellFormats.Count = 2;
_hyperlinkStyles[new SpreadsheetStyle().GetIdentifier()] = 1;
Borders borders = new Borders()
{
Count = (UInt32Value)1U
};
Border border = new Border();
LeftBorder leftBorder = new LeftBorder();
RightBorder rightBorder = new RightBorder();
TopBorder topBorder = new TopBorder();
BottomBorder bottomBorder = new BottomBorder();
DiagonalBorder diagonalBorder = new DiagonalBorder();
border.AppendChild(leftBorder);
border.AppendChild(rightBorder);
border.AppendChild(topBorder);
border.AppendChild(bottomBorder);
border.AppendChild(diagonalBorder);
borders.AppendChild(border);
_stylesheet.Borders = borders;
CellStyles cellStyles = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles.AppendChild(cellStyle);
_stylesheet.CellStyles = cellStyles;
var dateTimeFormatInfo = DateTimeFormatInfo.CurrentInfo;
var dateTimeFormat = "d.mm.yyyy hh:mm:ss";
if (dateTimeFormatInfo != null)
{
dateTimeFormat = dateTimeFormatInfo.ShortDatePattern + " " + dateTimeFormatInfo.LongTimePattern;
dateTimeFormat = dateTimeFormat.Replace("/", "\\/");
dateTimeFormat = dateTimeFormat.Replace("tt", "AM/PM");
}
NumberingFormats numberingFormats = new NumberingFormats()
{
Count = (UInt32Value)1U
};
NumberingFormat numberingFormat1 = new NumberingFormat()
{
NumberFormatId = (UInt32Value)164U, FormatCode = dateTimeFormat
};
numberingFormats.Append(numberingFormat1);
_stylesheet.NumberingFormats = numberingFormats;
}
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);
}
private void AppendWithIndexSave(Borders parent, Border child, ExcelSheetBorderIndex excelSheetIndex)
{
parent.AppendChild(child);
_indexes.Add(excelSheetIndex, (uint)_indexes.Count);
}
// 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);
}