private static Fills CreateFills()
{
// fill 0
var fills = new Fills();
fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
}
});
// fill 1 (in-built fill)
fills.AppendChild(new Fill
{
PatternFill = new PatternFill
{
PatternType = PatternValues.Gray125
}
});
// fill 2
fills.AppendChild(new Fill
{
PatternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValueFromColor(Color.LightSkyBlue)
},
BackgroundColor = new BackgroundColor {
Rgb = HexBinaryValueFromColor(Color.LightSkyBlue)
}
}
});
// fill 3
fills.AppendChild(new Fill
{
PatternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValueFromColor(Color.Orange)
},
BackgroundColor = new BackgroundColor {
Rgb = HexBinaryValueFromColor(Color.Orange)
}
}
});
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
return(fills);
}
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);
}
public static uint GetOrSetErrorFillID(SpreadsheetDocument workdocument)
{
WorkbookStylesPart stylesPart = workdocument.WorkbookPart.WorkbookStylesPart;
Fills fills = stylesPart.Stylesheet.Fills;
Fill cellFill = null;
uint fillId = 0;
if (!ExistsRedFillPattern(fills, ref cellFill, ref fillId))
{
Fill newErrorFill = new Fill();
PatternFill newErrorPattern = new PatternFill()
{
PatternType = PatternValues.Solid
};
newErrorPattern.AppendChild(new ForegroundColor()
{
Rgb = "FFFF0000"
});
newErrorPattern.AppendChild(new BackgroundColor()
{
Indexed = 64
});
newErrorFill.AppendChild(newErrorPattern);
fills.AppendChild(newErrorFill);
fills.Count++;
}
workdocument.Save();
ErrorStyleFillId = fillId;
return(fillId);
}
private static Fills CreateFills()
{
// fill 0
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill {
PatternType = PatternValues.None
};
fill.PatternFill = patternFill;
fills.AppendChild(fill);
// fill 1 (in-built fill)
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Gray125
};
fill.PatternFill = patternFill;
fills.AppendChild(fill);
// fill 2
//fill = new Fill();
//patternFill = new PatternFill();
//patternFill.PatternType = PatternValues.Solid;
//var fillColor = Color.LightSkyBlue;
//patternFill.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValueFromColor(fillColor) };
//patternFill.BackgroundColor = new BackgroundColor { Rgb = HexBinaryValueFromColor(fillColor) };
//fill.PatternFill = patternFill;
//fills.AppendChild(fill);
//// fill 3
//fill = new Fill();
//patternFill = new PatternFill();
//patternFill.PatternType = PatternValues.Solid;
//fillColor = Color.Orange;
//patternFill.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValueFromColor(fillColor) };
//patternFill.BackgroundColor = new BackgroundColor { Rgb = HexBinaryValueFromColor(fillColor) };
//fill.PatternFill = patternFill;
//fills.AppendChild(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
return(fills);
}
private Fills BuildFills()
{
var fills = new Fills();
var fillDefault = new Fill(new PatternFill()
{
PatternType = PatternValues.None
});
fills.AppendChild(fillDefault);
FillDefaultId = 0;
return(fills);
}
private static void ResolveFillWithPattern(Fills fills, PatternValues patternValues)
{
if (fills.Elements<Fill>().Any(f =>
f.PatternFill.PatternType == patternValues
&& f.PatternFill.ForegroundColor == null
&& f.PatternFill.BackgroundColor == null
)) return;
var fill1 = new Fill();
var patternFill1 = new PatternFill {PatternType = patternValues};
fill1.AppendChild(patternFill1);
fills.AppendChild(fill1);
}
private Stylesheet GenerateStylesheet()
{
Fonts fonts = new Fonts(
new Font( // Index 0 - The default font.
new FontSize {
Val = 10
},
new Color {
Rgb = new HexBinaryValue {
Value = "000000"
}
},
new FontName {
Val = "微软雅黑"
}),
new Font( // Index 1 - The bold font.
new Bold(),
new FontSize {
Val = 10
},
new Color {
Rgb = new HexBinaryValue {
Value = "000000"
}
},
new FontName {
Val = "微软雅黑"
})
);
Fills fills = new Fills(
new Fill( // Index 0 - The default fill.
new PatternFill {
PatternType = PatternValues.None
}),
new Fill( // Index 1 - The default fill of gray 125 (required)
new PatternFill {
PatternType = PatternValues.Gray125
}),
new Fill( // Index 2 - The header fill.
new PatternFill(new ForegroundColor {
Rgb = new HexBinaryValue {
Value = "FFD9E1F2"
}
})
{
PatternType = PatternValues.Solid
}));
for (int i = 0; i < groupColors.Length; i++)
{
fills.AppendChild(new Fill(
new GradientFill(CreateGradientStop(0), CreateGradientStop(1, groupColors[i]))
{
Degree = 180
}));
fills.AppendChild(new Fill(
new GradientFill(CreateGradientStop(0), CreateGradientStop(1, groupColors[i]))
{
Degree = 0
}));
}
Borders borders = new Borders(
new Border( // Index 0 - The default border.
new LeftBorder(),
new RightBorder(),
new TopBorder(),
new BottomBorder(),
new DiagonalBorder()),
new Border( // Index 1 - Applies a Left, Right, Top, Bottom border to a cell
new LeftBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new RightBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new TopBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new BottomBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new DiagonalBorder()),
new Border( // Index 2 - Top Border.
new LeftBorder(),
new RightBorder(),
new TopBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new BottomBorder(),
new DiagonalBorder())
);
CellFormats cellFormats = new CellFormats(
new CellFormat()
{
FontId = 0,
FillId = 0,
BorderId = 0
}, // Index 0 - The default cell style. If a cell does not have a style index applied it will use this style combination instead
new CellFormat(new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
})
{
FontId = 0,
FillId = 0,
BorderId = 1,
ApplyBorder = true,
ApplyAlignment = true,
}, // Index 1 - All
new CellFormat(new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
})
{
FontId = 1,
FillId = 2,
BorderId = 1,
ApplyFont = true,
ApplyFill = true,
ApplyBorder = true,
ApplyAlignment = true,
}, // Index 2 - Header
new CellFormat(new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
})
{
FontId = 0,
FillId = 2,
BorderId = 1,
ApplyFill = true,
ApplyBorder = true,
ApplyAlignment = true,
}, // Index 3 - Sub Header
new CellFormat(new Alignment()
{
Horizontal = HorizontalAlignmentValues.Fill
})
{
FontId = 1,
FillId = 0,
BorderId = 1,
ApplyFont = true,
ApplyBorder = true,
ApplyAlignment = true,
}, // Index 4
new CellFormat()
{
FontId = 0,
FillId = 0,
BorderId = 2,
ApplyBorder = true,
} // Index 5 - Enum
);
for (uint i = 0; i < groupColors.Length; i++)
{
cellFormats.AppendChild(new CellFormat(new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
})
{
FontId = 0,
FillId = i * 2 + 3,
BorderId = 1,
ApplyBorder = true,
ApplyAlignment = true,
ApplyFill = true,
});
cellFormats.AppendChild(new CellFormat(new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
})
{
FontId = 0,
FillId = i * 2 + 4,
BorderId = 1,
ApplyBorder = true,
ApplyAlignment = true,
ApplyFill = true,
});
}
return(new Stylesheet(fonts, fills, borders, cellFormats));
}
private Stylesheet GenerateStylesheet(IEnumerable <SheetCellItem> cellItems)
{
if (cellItems != null && cellItems.Any(x => x.CellFormats != null))
{
IEnumerable <SheetCellFormats> cfs = cellItems.Where(x => x.CellFormats != null).Select(x => x.CellFormats).Distinct();
Fills fills = new Fills(new Fill(new PatternFill()
{
PatternType = PatternValues.None
}),
new Fill(new PatternFill()
{
PatternType = PatternValues.Gray125
}));
Dictionary <string, Fill> fgColorAndFills = new Dictionary <string, Fill>();
if (cfs.Any(x => !string.IsNullOrEmpty(x.FGColor)))
{
foreach (var fg in cfs.Where(x => !string.IsNullOrEmpty(x.FGColor)).Select(x => x.FGColor).Distinct())
{
var f = new Fill(new PatternFill()
{
PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
{
Rgb = new HexBinaryValue(fg)
}
});
fgColorAndFills.Add(fg, f);
fills.AppendChild(f);
}
}
Fonts fonts = new Fonts(new Font(new FontSize()
{
Val = 10
}), new Font(new FontSize()
{
Val = 10
}, new FontName()
{
Val = "Arial"
}));
Borders borders = new Borders(new Border(),
new Border(
new LeftBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new RightBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new TopBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new BottomBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new DiagonalBorder()),
new Border(new TopBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
}),
new Border(new RightBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
}),
new Border(new BottomBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
}),
new Border(new LeftBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
}));
CellFormat[] lstCellFormats = new CellFormat[cfs.Count() + 1];
lstCellFormats[0] = (new CellFormat()
{
FillId = 0, FontId = 0
});
uint index = 1;
bool tag = false;
foreach (SheetCellFormats itm in cfs)
{
tag = false;
CellFormat cf = new CellFormat();
if (itm.FontBold || itm.FontSize > 0 || !string.IsNullOrEmpty(itm.FontColor) || !string.IsNullOrEmpty(itm.FontName))
{
var f = new Font();
if (itm.FontSize > 0)
{
f.Append(new FontSize()
{
Val = itm.FontSize
});
}
if (itm.FontBold)
{
f.Append(new Bold()
{
Val = true
});
}
if (!string.IsNullOrEmpty(itm.FontColor))
{
f.Append(new Color {
Rgb = itm.FontColor
});
}
if (!string.IsNullOrEmpty(itm.FontName))
{
f.Append(new FontName {
Val = itm.FontName
});
}
int i = FindFont(fonts, f);
if (i > -1)
{
cf.FontId = (uint)i;
}
else
{
fonts.Append(f);
cf.FontId = (uint)(fonts.ChildElements.Count - 1);
}
cf.ApplyFont = true;
tag = true;
}
if (itm.HorizontalAlignment != HorizontalAlignments.Default || itm.VerticalAlignment != VerticalAlignments.Default || itm.WrapText)
{
var align = new Alignment();
switch (itm.HorizontalAlignment)
{
case HorizontalAlignments.Default:
break;
case HorizontalAlignments.Center:
align.Horizontal = HorizontalAlignmentValues.Center;
break;
case HorizontalAlignments.Left:
align.Horizontal = HorizontalAlignmentValues.Left;
break;
case HorizontalAlignments.Right:
align.Horizontal = HorizontalAlignmentValues.Right;
break;
}
switch (itm.VerticalAlignment)
{
case VerticalAlignments.Default:
break;
case VerticalAlignments.Top:
align.Vertical = VerticalAlignmentValues.Top;
break;
case VerticalAlignments.Middle:
align.Vertical = VerticalAlignmentValues.Center;
break;
case VerticalAlignments.Bottom:
align.Vertical = VerticalAlignmentValues.Bottom;
break;
}
align.WrapText = itm.WrapText;
cf.Append(align);
cf.ApplyAlignment = true;
tag = true;
}
if (tag)
{
cf.FillId = 0;
lstCellFormats[index] = cf;
}
index++;
}
index = 1;
foreach (SheetCellFormats itm in cfs)
{
if (itm.Borders != null && itm.Borders.Any(x => x == true))
{
if (itm.FontBold || itm.FontSize > 0 || !string.IsNullOrEmpty(itm.FontColor) || !string.IsNullOrEmpty(itm.FontName) ||
itm.HorizontalAlignment != HorizontalAlignments.Default || itm.VerticalAlignment != VerticalAlignments.Default)
{
if (itm.Borders[0] && itm.Borders[1] && itm.Borders[2] && itm.Borders[3])
{
lstCellFormats[index].BorderId = 1;
}
else if (itm.Borders[0])
{
lstCellFormats[index].BorderId = 2;
}
else if (itm.Borders[1])
{
lstCellFormats[index].BorderId = 3;
}
else if (itm.Borders[2])
{
lstCellFormats[index].BorderId = 4;
}
else if (itm.Borders[3])
{
lstCellFormats[index].BorderId = 5;
}
}
else
{
CellFormat fc = new CellFormat();
if (itm.Borders[0] && itm.Borders[1] && itm.Borders[2] && itm.Borders[3])
{
fc.BorderId = 1;
}
else if (itm.Borders[0])
{
fc.BorderId = 2;
}
else if (itm.Borders[1])
{
fc.BorderId = 3;
}
else if (itm.Borders[2])
{
fc.BorderId = 4;
}
else if (itm.Borders[3])
{
fc.BorderId = 5;
}
lstCellFormats[index] = fc;
}
lstCellFormats[index].ApplyBorder = true;
}
if (!string.IsNullOrEmpty(itm.FGColor))
{
lstCellFormats[index].FillId = (uint)FindForGroundFill(fills, fgColorAndFills[itm.FGColor]);
}
index++;
}
index = 1;
foreach (SheetCellFormats itm in cfs)
{
foreach (SheetCellItem x in cellItems.Where(x => x.CellFormats == itm))
{
x.FormatIndex = index;
}
index++;
}
CellFormats cellFormats = new CellFormats(lstCellFormats);
return(new Stylesheet(fonts, fills, borders, cellFormats));
}
return(null);
}
/// <summary>
/// create the default excel formats. These formats are required for the excel in order for it to render
/// correctly.
/// </summary>
/// <returns></returns>
private Stylesheet CreateDefaultStylesheet()
{
Stylesheet ss = new Stylesheet();
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = "Calibri";
FontSize ftsz = new FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
//default fills used by Excel, don't changes these
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fills.Count = (uint)fills.ChildElements.Count;
Borders borders = new Borders();
Border border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.BottomBorder = new BottomBorder();
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
CellStyleFormats csfs = new CellStyleFormats();
CellFormat cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
CellFormats cfs = new CellFormats();
cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
var nfs = new NumberingFormats();
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
CellStyles css = new CellStyles(
new CellStyle()
{
Name = "Normal",
FormatId = 0,
BuiltinId = 0,
}
);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
ss.Append(dfs);
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = "TableStyleMedium9";
tss.DefaultPivotStyle = "PivotStyleLight16";
ss.Append(tss);
return(ss);
}
private static Stylesheet GenerateStyleSheet()
{
Stylesheet stylesheet = new Stylesheet();
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
stylesheet.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
stylesheet.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
var fonts = new Fonts()
{
Count = 2U
};
var fills = new Fills()
{
Count = 5U
};
var borders = new Borders()
{
Count = 1
};
var cellFormats = new CellFormats()
{
Count = 4U
};
// Create Default Row Font : Verdana Black 12
Font rowFont = new Font();
rowFont.Append(new FontSize()
{
Val = 12D
});
rowFont.Append(new Color()
{
Rgb = "FF000000"
});
rowFont.Append(new FontName()
{
Val = "Verdana"
});
// Create Header Font : Calibri White 16 Bold
Font headerFont = new Font();
headerFont.Append(new Bold());
headerFont.Append(new FontSize()
{
Val = 16D
});
headerFont.Append(new Color()
{
Rgb = "FFFFFFFF"
});
headerFont.Append(new FontName()
{
Val = "Calibri"
});
fonts.Append(rowFont);
fonts.Append(headerFont);
// Create Header Fill : Dark Grey
PatternFill headerPatternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = "FF4D4D4D"
},
BackgroundColor = new BackgroundColor {
Indexed = 64
}
};
// Create Row Odd Fill : Light Grey
PatternFill oddRowPatternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = "FFEAEAEA"
},
BackgroundColor = new BackgroundColor {
Indexed = 64
}
};
// Create Row Even Fill : White
PatternFill evenRowPatternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = "FFFFFFFF"
},
BackgroundColor = new BackgroundColor {
Indexed = 64
}
};
fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
}
}); // required
fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.Gray125
}
}); // required
fills.AppendChild(new Fill {
PatternFill = headerPatternFill
});
fills.AppendChild(new Fill {
PatternFill = oddRowPatternFill
});
fills.AppendChild(new Fill {
PatternFill = evenRowPatternFill
});
// Create default border
Border border1 = new Border();
border1.Append(new LeftBorder());
border1.Append(new RightBorder());
border1.Append(new TopBorder());
border1.Append(new BottomBorder());
border1.Append(new DiagonalBorder());
borders.Append(border1);
cellFormats.AppendChild(new CellFormat());
cellFormats.AppendChild(new CellFormat {
FontId = 1, FillId = 2, BorderId = 0, ApplyFill = true
}); // 1.header cell format
cellFormats.AppendChild(new CellFormat {
FontId = 0, FillId = 3, BorderId = 0, ApplyFill = true
}); // 2.odd row cell format
cellFormats.AppendChild(new CellFormat {
FontId = 0, FillId = 4, BorderId = 0, ApplyFill = true
}); // 3.even row cell format
stylesheet.Append(fonts);
stylesheet.Append(fills);
stylesheet.Append(borders);
stylesheet.Append(cellFormats);
return(stylesheet);
}
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);
}
private void AppendWithIndexSave(Fills parent, Fill child, ExcelStylesheetFillIndex excelStylesheetIndex)
{
parent.AppendChild(child);
_indexes.Add(excelStylesheetIndex, (uint)_indexes.Count);
}
/// <summary>
/// create the default excel formats. These formats are required for the excel in order for it to render
/// correctly.
/// </summary>
/// <returns></returns>
public Stylesheet CreateDefaultStylesheet()
{
Stylesheet ss = new Stylesheet();
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = "Calibri";
FontSize ftsz = new FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
//default fills used by Excel, don't changes these
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fills.Count = (uint)fills.ChildElements.Count;
Borders borders = new Borders();
Border border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.BottomBorder = new BottomBorder();
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
CellStyleFormats csfs = new CellStyleFormats();
CellFormat cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
CellFormats cfs = new CellFormats();
cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
var nfs = new NumberingFormats();
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
CellStyles css = new CellStyles(
new CellStyle()
{
Name = "Normal",
FormatId = 0,
BuiltinId = 0,
}
);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
ss.Append(dfs);
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = "TableStyleMedium9";
tss.DefaultPivotStyle = "PivotStyleLight16";
ss.Append(tss);
return ss;
}
private static Fills AddFills()
{
Fills fills1 = new Fills()
{
Count = 5U
};
// FillId = 0
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill1.AppendChild(patternFill1);
// FillId = 1
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill()
{
PatternType = PatternValues.Gray125
};
fill2.AppendChild(patternFill2);
// FillId = 2,ORANGE
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor1 = new ForegroundColor()
{
Rgb = "FFED7D31"
};
BackgroundColor backgroundColor1 = new BackgroundColor()
{
Indexed = 64U
};
patternFill3.AppendChild(foregroundColor1);
patternFill3.AppendChild(backgroundColor1);
fill3.AppendChild(patternFill3);
// FillId = 3,BLUE
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor2 = new ForegroundColor()
{
Rgb = "FF4472C4"
};
BackgroundColor backgroundColor2 = new BackgroundColor()
{
Indexed = 64U
};
patternFill4.AppendChild(foregroundColor2);
patternFill4.AppendChild(backgroundColor2);
fill4.AppendChild(patternFill4);
// FillId = 4,YELLO
Fill fill5 = new Fill();
PatternFill patternFill5 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor3 = new ForegroundColor()
{
Rgb = "FFFFFF00"
};
BackgroundColor backgroundColor3 = new BackgroundColor()
{
Indexed = 64U
};
patternFill5.AppendChild(foregroundColor3);
patternFill5.AppendChild(backgroundColor3);
fill5.AppendChild(patternFill5);
fills1.AppendChild(fill1);
fills1.AppendChild(fill2);
fills1.AppendChild(fill3);
fills1.AppendChild(fill4);
fills1.AppendChild(fill5);
//fills1.AppendChild(fill6);
return(fills1);
}
private static void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart)
{
Stylesheet styleSheet = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac x16r2 xr"
}
};
//this namespace are revelead using the took OPEN XML PRODUCTIVITY TOOL -- REFLECT CODE feature
styleSheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
styleSheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
styleSheet.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
styleSheet.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
#region fonts
Fonts fontList = new Fonts {
Count = 3
};
Font f1 = new Font();
Color color1 = new Color()
{
Rgb = HexBinaryValue.FromString("FF000000")
};
f1.Append(color1);
Font f2 = new Font();
FontSize fontSize2 = new FontSize()
{
Val = 12D
};
Color color2 = new Color()
{
Rgb = HexBinaryValue.FromString("FF808080")
};
Bold bold2 = new Bold();
Italic it2 = new Italic();
FontName fontName2 = new FontName()
{
Val = "Arial Black"
};
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
{
Val = 2
};
f2.Append(fontSize2);
f2.Append(color2);
f2.Append(fontName2);
f2.Append(bold2);
f2.Append(it2);
f2.Append(fontFamilyNumbering2);
Font f3 = new Font();
FontSize fontSize3 = new FontSize()
{
Val = 16D
};
Color color3 = new Color()
{
Rgb = HexBinaryValue.FromString("FF0000FF")
};
Underline ud3 = new Underline();
FontName fontName3 = new FontName()
{
Val = "Times New Roman"
};
FontFamilyNumbering fontFamilyNumbering3 = new FontFamilyNumbering()
{
Val = 1
};
f3.Append(fontSize3);
f3.Append(color3);
f3.Append(ud3);
f3.Append(fontName3);
f3.Append(fontFamilyNumbering3);
fontList.Append(f1);
fontList.Append(f2);
fontList.Append(f3);
#endregion
#region Fills
Fills fillList = new Fills();
//solid red fill
var solidRed = new PatternFill()
{
PatternType = PatternValues.Solid
};
solidRed.ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValue.FromString("FFFF0000")
};
solidRed.BackgroundColor = new BackgroundColor {
Indexed = 64
};
fillList.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
}
}); // required, reserved by Excel
fillList.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.Gray125
}
}); // required, reserved by Excel
fillList.AppendChild(new Fill {
PatternFill = solidRed
});
fillList.Count = 3;
#endregion
#region Borders
Borders bordersList = new Borders
{
Count = 1
};
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.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
bordersList.Append(border1);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder()
{
Style = BorderStyleValues.Thick
};
Color colorborder = new Color()
{
Indexed = (UInt32Value)64U
};
leftBorder2.Append(colorborder);
RightBorder rightBorder2 = new RightBorder()
{
Style = BorderStyleValues.Thin
};
Color color4 = new Color()
{
Indexed = (UInt32Value)64U
};
rightBorder2.Append(color4);
TopBorder topBorder2 = new TopBorder()
{
Style = BorderStyleValues.Thick
};
Color color5 = new Color()
{
Indexed = (UInt32Value)64U
};
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
Color color6 = new Color()
{
Indexed = (UInt32Value)64U
};
bottomBorder2.Append(color6);
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
bordersList.Append(border2);
#endregion
#region Cellformats
// blank cell format list
CellStyleFormats blankcellStyleFormatList = new CellStyleFormats
{
Count = 1
};
blankcellStyleFormatList.AppendChild(new CellFormat());
// cell format list
CellFormats cellStyleFormatList = new CellFormats();
// empty one for index 0, seems to be required
cellStyleFormatList.AppendChild(new CellFormat());
// cell format references style format 0, font 0, border 0, fill 2 and applies the fill
cellStyleFormatList.AppendChild(new CellFormat {
FormatId = 0, FontId = 0, BorderId = 1, FillId = 2, ApplyFill = true
}).AppendChild(new Alignment {
Horizontal = HorizontalAlignmentValues.Center
});
cellStyleFormatList.AppendChild(new CellFormat {
FormatId = 0, FontId = 1, BorderId = 1, FillId = 0, ApplyFill = true
});
cellStyleFormatList.AppendChild(new CellFormat {
FormatId = 0, FontId = 2, BorderId = 1, FillId = 0, ApplyFill = true
});
cellStyleFormatList.Count = 4;
#endregion
styleSheet.Append(fontList);
styleSheet.Append(fillList);
styleSheet.Append(bordersList);
styleSheet.Append(blankcellStyleFormatList);
styleSheet.Append(cellStyleFormatList);
workbookStylesPart.Stylesheet = styleSheet;
}