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 uint SetErrorStyle(Stylesheet stylesheet)
{
Fill fill = new Fill()
{
PatternFill = new PatternFill()
{
PatternType = PatternValues.Solid,
BackgroundColor = new BackgroundColor()
{
Rgb = "D8D8D8"
}
}
};
stylesheet.Fills.AppendChild(fill);
//Adding the CellFormat which uses the Fill element
CellFormats cellFormats = stylesheet.CellFormats;
CellFormat cf = new CellFormat();
cf.FillId = stylesheet.Fills.Count;
cellFormats.AppendChild(cf);
stylesheet.Save();
return(stylesheet.CellFormats.Count);
}
/// <summary>
/// Save the styl for worksheet headers.
/// </summary>
/// <param name="cellLocation">Cell location.</param>
/// <param name="spreadSheet">Spreadsheet to change.</param>
/// <param name="workSheetPart">Worksheet to change.</param>
private static void SeatHeaderStyle(string cellLocation, SpreadsheetDocument spreadSheet, WorksheetPart workSheetPart)
{
Stylesheet styleSheet = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
Cell cell = workSheetPart.Worksheet.Descendants <Cell>().Where(c => c.CellReference == cellLocation).FirstOrDefault();
if (cell == null)
{
throw new ArgumentNullException("Cell not found");
}
cell.SetAttribute(new OpenXmlAttribute("", "s", "", "1"));
OpenXmlAttribute cellStyleAttribute = cell.GetAttribute("s", "");
CellFormats cellFormats = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
// pick tthe first cell format.
CellFormat cellFormat = (CellFormat)cellFormats.ElementAt(0);
CellFormat cf = new CellFormat(cellFormat.OuterXml);
cf.FontId = styleSheet.Fonts.Count;
cf.FillId = styleSheet.Fills.Count;
cellFormats.AppendChild(cf);
int a = (int)styleSheet.CellFormats.Count.Value;
cell.SetAttribute(cellStyleAttribute);
cell.StyleIndex = styleSheet.CellFormats.Count;
workSheetPart.Worksheet.Save();
}
private CellFormats BuildCellFormats()
{
var cellFormats = new CellFormats();
var cellFormatDefault = new CellFormat()
{
FontId = FontDefaultId,
FillId = FillDefaultId,
BorderId = BorderDefaultId
};
cellFormats.AppendChild(cellFormatDefault);
var cellFormatTitle = new CellFormat(new Alignment()
{
Horizontal = HorizontalAlignmentValues.Left,
Vertical = VerticalAlignmentValues.Center
})
{
FontId = FontBoldId,
FillId = FillDefaultId,
BorderId = BorderDefaultId,
ApplyAlignment = true
};
cellFormats.AppendChild(cellFormatTitle);
CellFormatTitleRowId = 1;
var cellFormatBody = new CellFormat(new Alignment()
{
Horizontal = HorizontalAlignmentValues.Left,
Vertical = VerticalAlignmentValues.Center
})
{
FontId = FontDefaultId,
FillId = FillDefaultId,
BorderId = BorderDefaultId,
ApplyAlignment = true
};
cellFormats.AppendChild(cellFormatBody);
CellFormatDefaultId = 2;
return(cellFormats);
}
public Stylesheet CreateStylesheet(List <ExcelSheet> sheets)
{
var formats = new CellFormats();
var allStyles = new List <ExcelCellStyle>();
foreach (var item in sheets.SelectMany(s => s.Cells))
{
if (item.Value.CellStyle != null)
{
allStyles.Add(item.Value.CellStyle);
}
}
var distinctStyles = allStyles.Distinct().ToList();
foreach (var item in distinctStyles)
{
var formatIndex = (uint)formats.Count();
foreach (var style in allStyles.Where(m => m.Equals(item)))
{
style.SetStyleIndex(formatIndex);
}
var cellFormat = new CellFormat();
this.AddNumberFormatToCellFormat(item, cellFormat);
this.AddBackgroundToCellFormat(item, cellFormat);
this.AddFontToCellFormat(item, cellFormat);
this.AddAlignmentToCellFormat(item, cellFormat);
this.AddBordersToCellFormat(item, cellFormat);
formats.AppendChild(cellFormat);
}
var styleSheet = new Stylesheet
{
Fonts = new Fonts(this.fonts),
Fills = new Fills(this.fills),
Borders = new Borders(this.borders),
CellStyleFormats = new CellStyleFormats(new CellFormat()),
CellFormats = formats
};
return(styleSheet);
}
private static void SetHeaderStyle(SpreadsheetDocument spreadSheet, Cell cell)
{
Stylesheet styleSheet = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
cell.SetAttribute(new OpenXmlAttribute("", "s", "", "1"));
OpenXmlAttribute cellStyleAttribute = cell.GetAttribute("s", "");
CellFormats cellFormats = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
// pick the first cell format.
CellFormat cellFormat = (CellFormat)cellFormats.ElementAt(0);
CellFormat cf = new CellFormat(cellFormat.OuterXml);
cf.FontId = styleSheet.Fonts.Count;
cf.FillId = styleSheet.Fills.Count;
cellFormats.AppendChild(cf);
cell.SetAttribute(cellStyleAttribute);
cell.StyleIndex = styleSheet.CellFormats.Count;
}
/// <summary>
/// Ensure cell formats are added in the order specified by the enumeration
/// </summary>
private static CellFormats CreateCellFormats(NumberingFormat nfDateTime, NumberingFormat nf5Decimal,
NumberingFormat nfDuration, NumberingFormat nfTotalDuration)
{
var cfs = new CellFormats();
// CustomCellFormats.DefaultText
var cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(false);
cfs.AppendChild(cf);
cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 1;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(false);
cfs.AppendChild(cf);
// CustomCellFormats.DefaultDate
cf = new CellFormat();
cf.NumberFormatId = 22; // mm-dd-yy
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.AppendChild(cf);
// CustomCellFormats.DefaultNumber2DecimalPlace
//cf = new CellFormat();
//cf.NumberFormatId = 4; // #,##0.00
//cf.FontId = 0;
//cf.FillId = 0;
//cf.BorderId = 0;
//cf.FormatId = 0;
//cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
//cfs.AppendChild(cf);
//// CustomCellFormats.DefaultNumber5DecimalPlace
//cf = new CellFormat();
//cf.NumberFormatId = nf5Decimal.NumberFormatId;
//cf.FontId = 0;
//cf.FillId = 0;
//cf.BorderId = 0;
//cf.FormatId = 0;
//cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
//cfs.AppendChild(cf);
//// CustomCellFormats.DefaultDateTime
//cf = new CellFormat();
//cf.NumberFormatId = nfDateTime.NumberFormatId;
//cf.FontId = 0;
//cf.FillId = 0;
//cf.BorderId = 0;
//cf.FormatId = 0;
//cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
//cfs.AppendChild(cf);
//// CustomCellFormats.HeaderText
//cf = new CellFormat();
//cf.NumberFormatId = 0;
//cf.FontId = 1;
//cf.FillId = 2;
//cf.BorderId = 0;
//cf.FormatId = 0;
//cf.ApplyNumberFormat = BooleanValue.FromBoolean(false);
//cfs.AppendChild(cf);
//// CustomCellFormats.TotalsNumber
//cf = new CellFormat();
//cf.NumberFormatId = 0;
//cf.FontId = 0;
//cf.FillId = 3;
//cf.BorderId = 2;
//cf.FormatId = 0;
//cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
//cfs.AppendChild(cf);
//// CustomCellFormats.TotalsNumber2DecimalPlace
////cf = new CellFormat();
////cf.NumberFormatId = 4; // #,##0.00
////cf.FontId = 0;
////cf.FillId = 3;
////cf.BorderId = 2;
////cf.FormatId = 0;
////cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
////cfs.AppendChild(cf);
//// CustomCellFormats.TotalsText
////cf = new CellFormat();
////cf.NumberFormatId = 49; // @
////cf.FontId = 0;
////cf.FillId = 3;
////cf.BorderId = 2;
////cf.FormatId = 0;
////cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
////cfs.AppendChild(cf);
//// CustomCellFormats.TitleText
//cf = new CellFormat();
//cf.NumberFormatId = 0;
//cf.FontId = 2;
//cf.FillId = 0;
//cf.BorderId = 0;
//cf.FormatId = 0;
//cf.ApplyNumberFormat = BooleanValue.FromBoolean(false);
//cf.Alignment = new Alignment
//{
// Vertical = new EnumValue<VerticalAlignmentValues>(VerticalAlignmentValues.Bottom)
//};
//cfs.AppendChild(cf);
//// CustomCellFormats.SubtitleText
//cf = new CellFormat();
//cf.NumberFormatId = 0;
//cf.FontId = 3;
//cf.FillId = 0;
//cf.BorderId = 0;
//cf.FormatId = 0;
//cf.ApplyNumberFormat = BooleanValue.FromBoolean(false);
//cf.Alignment = new Alignment
//{
// Vertical = new EnumValue<VerticalAlignmentValues>(VerticalAlignmentValues.Top)
//};
//cfs.AppendChild(cf);
//// CustomCellFormats.Duration
//cf = new CellFormat();
//cf.NumberFormatId = nfDuration.NumberFormatId; // [h]:mm
//cf.FontId = 0;
//cf.FillId = 0;
//cf.BorderId = 0;
//cf.FormatId = 0;
//cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
//cf.Alignment = new Alignment
//{
// Horizontal = new EnumValue<HorizontalAlignmentValues>(HorizontalAlignmentValues.Right)
//};
//cfs.AppendChild(cf);
//// CustomCellFormats.TotalsNumber
////cf = new CellFormat();
////cf.NumberFormatId = nfTotalDuration.NumberFormatId; // d:h:mm
////cf.FontId = 0;
////cf.FillId = 3;
////cf.BorderId = 2;
////cf.FormatId = 0;
////cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
////cf.Alignment = new Alignment
////{
//// Horizontal = new EnumValue<HorizontalAlignmentValues>(HorizontalAlignmentValues.Right)
////};
////cfs.AppendChild(cf);
//// CustomCellFormats.Hyperlink
//cf = new CellFormat();
//cf.NumberFormatId = 0;
//cf.FontId = 4;
//cf.FillId = 0;
//cf.BorderId = 0;
//cf.FormatId = 0;
//cf.ApplyNumberFormat = BooleanValue.FromBoolean(false);
//cfs.AppendChild(cf);
cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);
return(cfs);
}
private Stylesheet GenerateNumberingStylesheet()
{
Stylesheet stylesheet = null;
// Create a numberingformat,
//stylesheet.NumberingFormats = new NumberingFormats();
NumberingFormats numberingFormats = new NumberingFormats();
//#.##% is also Excel style index 1
//uint iExcelIndex = 164;
NumberingFormat nf2decimal = new NumberingFormat()
{
NumberFormatId = UInt32Value.FromUInt32(3453),
FormatCode = StringValue.FromString("0.0%")
};
numberingFormats.Append(nf2decimal);
var nformat4Decimal = new NumberingFormat
{
//NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
NumberFormatId = UInt32Value.FromUInt32(3500),
FormatCode = StringValue.FromString("#,##0.0000")
};
numberingFormats.Append(nformat4Decimal);
var dateFormat = new NumberingFormat()
{
NumberFormatId = (UInt32Value)4000,
FormatCode = StringValue.FromString("dd.mm.yyyy")
};
numberingFormats.Append(dateFormat);
//stylesheet.Fonts = new Fonts(
var fonts = new Fonts(
new Font(),
new Font(
new FontSize()
{
Val = 10
},
new Bold()
)
);
// Create a cell format and apply the numbering format id
CellFormats cellFormats = new CellFormats();
var cellFormat = new CellFormat();
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = 0;
cellFormat.FormatId = 0;
cellFormat.NumberFormatId = nf2decimal.NumberFormatId;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormat.ApplyFont = true;
//append cell format for cells of header row
//stylesheet.CellFormats = new CellFormats();
cellFormats.AppendChild <CellFormat>(cellFormat);
cellFormat = new CellFormat();
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = 0;
cellFormat.FormatId = 0;
cellFormat.NumberFormatId = nformat4Decimal.NumberFormatId;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormat.ApplyFont = true;
//append cell format for cells of header row
cellFormats.AppendChild <CellFormat>(cellFormat);
// Percentage
cellFormat = new CellFormat();
cellFormat.FontId = 1;
cellFormat.FillId = 0;
cellFormat.BorderId = 0;
cellFormat.FormatId = 0;
cellFormat.NumberFormatId = 10;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormat.ApplyFont = true;
//append cell format for cells of header row
cellFormats.AppendChild <CellFormat>(cellFormat);
// Date
cellFormat = new CellFormat();
cellFormat.FontId = 1;
cellFormat.FillId = 0;
cellFormat.BorderId = 0;
cellFormat.FormatId = 0;
cellFormat.NumberFormatId = dateFormat.NumberFormatId;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormat.ApplyFont = true;
//append cell format for cells of header row
cellFormats.AppendChild <CellFormat>(cellFormat);
Fills fills = new Fills(new Fill());
Borders borders = new Borders(new Border());
stylesheet = new Stylesheet(fonts, fills, borders, cellFormats);//, numberingFormats);
stylesheet.NumberingFormats = numberingFormats;
//update font count
stylesheet.CellFormats.Count = UInt32Value.FromUInt32((uint)stylesheet.CellFormats.ChildElements.Count);
return(stylesheet);
}
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);
}
public static void GetAndOrSetErrorStyleID(SpreadsheetDocument workdocument, Worksheet worksheet, Cell currentCell)
{
WorkbookStylesPart stylesPart = workdocument.WorkbookPart.WorkbookStylesPart;
uint fillId = GetOrSetErrorFillID(workdocument);
uint styleId = currentCell.StyleIndex ?? 0;
CellFormats cellFormats = stylesPart.Stylesheet.CellFormats;
CellFormat currentCellCellFormat = cellFormats.Descendants <CellFormat>().ElementAt((int)styleId);
bool cellFormatComparation = true;
uint checkedStyleIndex = 0;
//iterate over all cellFormat of the page
foreach (CellFormat cfItem in cellFormats)
{
checkedStyleIndex++;
cellFormatComparation = true;
//iterate over all attributes of the current cellFormat to check if all are the same
foreach (var item in cfItem.GetAttributes())
{
bool check = false;
try
{
check = Equals(currentCellCellFormat.GetAttribute(item.LocalName, item.NamespaceUri), item);
}
catch (KeyNotFoundException)
{
check = false;
break;
}
if (!check) //
{
cellFormatComparation = false;
break;
}
}
if (cellFormatComparation)
{
styleId = checkedStyleIndex;
break;
}
}
if (cellFormatComparation)
{
if (currentCellCellFormat.FillId != fillId)
{
CellFormat currentCellNewFormat = (CellFormat)currentCellCellFormat.CloneNode(true);
currentCellNewFormat.FillId = fillId;
cellFormats.AppendChild(currentCellNewFormat);
cellFormats.Count++;
}
}
else
{
cellFormats.AppendChild(new CellFormat()
{
BorderId = DefaultStyle, FillId = ErrorStyleFillId, FontId = DefaultStyle, NumberFormatId = DefaultStyle
});
cellFormats.Count++;
}
styleId = (uint)cellFormats.Descendants <CellFormat>().Count();
currentCell.StyleIndex = styleId - 1;
worksheet.Save();
}
private void AppendWithIndexSave(CellFormats formats, CellFormat child, ExcelSheetStyleIndex excelSheetIndex)
{
formats.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);
}
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);
}
/// <summary>
/// Ensure cell formats are added in the order specified by the enumeration
/// </summary>
private static CellFormats CreateCellFormats(NumberingFormat nfDateTime, NumberingFormat nf5Decimal,
NumberingFormat nfDuration, NumberingFormat nfTotalDuration)
{
var cfs = new CellFormats();
// CustomCellFormats.DefaultText
cfs.AppendChild(new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(false)
});
// CustomCellFormats.DefaultDate
// mm-dd-yy
cfs.AppendChild(new CellFormat
{
NumberFormatId = 14,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
});
// CustomCellFormats.DefaultNumber2DecimalPlace
// #,##0.00
cfs.AppendChild(new CellFormat
{
NumberFormatId = 4,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
});
// CustomCellFormats.DefaultNumber5DecimalPlace
cfs.AppendChild(new CellFormat
{
NumberFormatId = nf5Decimal.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
});
// CustomCellFormats.DefaultDateTime
cfs.AppendChild(new CellFormat
{
NumberFormatId = nfDateTime.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
});
// CustomCellFormats.HeaderText
cfs.AppendChild(new CellFormat
{
NumberFormatId = 0,
FontId = 1,
FillId = 2,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(false)
});
// CustomCellFormats.TotalsNumber
cfs.AppendChild(new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 3,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
});
// CustomCellFormats.TotalsNumber2DecimalPlace
// #,##0.00
cfs.AppendChild(new CellFormat
{
NumberFormatId = 4,
FontId = 0,
FillId = 3,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
});
// CustomCellFormats.TotalsText
// @
cfs.AppendChild(new CellFormat
{
NumberFormatId = 49,
FontId = 0,
FillId = 3,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
});
// CustomCellFormats.TitleText
cfs.AppendChild(new CellFormat
{
NumberFormatId = 0,
FontId = 2,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(false),
Alignment = new Alignment
{
Vertical = new EnumValue <VerticalAlignmentValues>(VerticalAlignmentValues.Bottom)
}
});
// CustomCellFormats.SubtitleText
cfs.AppendChild(new CellFormat
{
NumberFormatId = 0,
FontId = 3,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(false),
Alignment = new Alignment
{
Vertical = new EnumValue <VerticalAlignmentValues>(VerticalAlignmentValues.Top)
}
});
// CustomCellFormats.Duration
// [h]:mm
cfs.AppendChild(new CellFormat
{
NumberFormatId = nfDuration.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment
{
Horizontal = new EnumValue <HorizontalAlignmentValues>(HorizontalAlignmentValues.Right)
}
});
// CustomCellFormats.TotalsNumber
// d:h:mm
cfs.AppendChild(new CellFormat
{
NumberFormatId = nfTotalDuration.NumberFormatId,
FontId = 0,
FillId = 3,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment
{
Horizontal = new EnumValue <HorizontalAlignmentValues>(HorizontalAlignmentValues.Right)
}
});
// CustomCellFormats.Hyperlink
cfs.AppendChild(new CellFormat
{
NumberFormatId = 0,
FontId = 4,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(false)
});
cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);
return(cfs);
}
public CustomStylesheet()
{
#region Fonts
var fonts = new Fonts();
var color = new Color();
//Font Index 0
var font = new DocumentFormat.OpenXml.Spreadsheet.Font();
var fontName = new FontName {
Val = StringValue.FromString("Arial")
};
var fontSize = new FontSize {
Val = DoubleValue.FromDouble(9)
};
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
//Font Index 1
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Arial")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(10)
};
font.FontName = fontName;
font.FontSize = fontSize;
font.Bold = new Bold();
fonts.Append(font);
//Font Index 2
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Arial")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(15)
};
font.FontName = fontName;
font.FontSize = fontSize;
font.Bold = new Bold();
fonts.Append(font);
//Font Index 3 Posted
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Arial")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(9)
};
font.FontName = fontName;
font.FontSize = fontSize;
color = new Color()
{
Rgb = new HexBinaryValue()
{
Value = "0000FF"
}
};
font.Color = color;
fonts.Append(font);
//Font Index 4 InProgress
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Arial")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(9)
};
font.FontName = fontName;
font.FontSize = fontSize;
color = new Color()
{
Rgb = new HexBinaryValue()
{
Value = "FF8B45"
}
};
font.Color = color;
fonts.Append(font);
//Font Index 5 Submitted/resubmitted
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Arial")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(9)
};
font.FontName = fontName;
font.FontSize = fontSize;
color = new Color()
{
Rgb = new HexBinaryValue()
{
Value = "FF0000"
}
};
font.Color = color;
fonts.Append(font);
//Font Index 6 Accepted
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Arial")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(9)
};
font.FontName = fontName;
font.FontSize = fontSize;
color = new Color()
{
Rgb = new HexBinaryValue()
{
Value = "2C6100"
}
};
font.Color = color;
fonts.Append(font);
//Font Index 7 Dashboard
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Calibri")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(15)
};
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
//Font Index 8
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Calibri")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(9)
};
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
//Font Index 9 Header
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Calibri")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(18)
};
color = new Color()
{
Rgb = new HexBinaryValue()
{
Value = "FFFFFF"
}
};
font.Color = color;
font.Bold = new Bold();
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
//Font Index 10 Body Bold
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Calibri")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
};
font.Bold = new Bold();
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
//Font Index 11 Body Normal
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName {
Val = StringValue.FromString("Calibri")
};
fontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
};
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
#endregion
#region Fills
var fills = new Fills();
//Fill index 0
var fill = new Fill();
var patternFill = new PatternFill {
PatternType = PatternValues.None
};
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 1
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Gray125
};
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 2
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.LightBlue);
patternFill.BackgroundColor = new BackgroundColor {
Rgb = patternFill.ForegroundColor.Rgb
};
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 3
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.FromArgb(211, 211, 211));
patternFill.BackgroundColor = new BackgroundColor {
Rgb = patternFill.ForegroundColor.Rgb
};
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 4
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.FromArgb(211, 211, 211));
patternFill.BackgroundColor = new BackgroundColor()
{
Rgb = patternFill.ForegroundColor.Rgb
};
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 5
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.FromArgb(245, 245, 245));
patternFill.BackgroundColor = new BackgroundColor {
Rgb = patternFill.ForegroundColor.Rgb
};
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 6 ForeGround Header Blue
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.FromArgb(0, 176, 240));
patternFill.BackgroundColor = new BackgroundColor {
Rgb = patternFill.ForegroundColor.Rgb
};
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 7 ForeGround Header green
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.FromArgb(146, 208, 80));
patternFill.BackgroundColor = new BackgroundColor {
Rgb = patternFill.ForegroundColor.Rgb
};
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
#endregion
#region Borders
var borders = new Borders();
//All Boarder Index 0
var border = new Border
{
LeftBorder = new LeftBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
RightBorder = new RightBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
TopBorder = new TopBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
BottomBorder = new BottomBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
//All Boarder Index 1
border = new Border
{
LeftBorder = new LeftBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
RightBorder = new RightBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
TopBorder = new TopBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
BottomBorder = new BottomBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
//All Boarder Index 2
border = new Border
{
LeftBorder = new LeftBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
RightBorder = new RightBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
TopBorder = new TopBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
BottomBorder = new BottomBorder {
Style = BorderStyleValues.Thin, Color = new Color()
{
Indexed = (UInt32Value)64U
}
},
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
#endregion
#region CellStyleFormats
var cellStyleFormats = new CellStyleFormats();
var cellFormat = new CellFormat {
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 1
};
cellStyleFormats.Append(cellFormat);
cellFormat = new CellFormat {
NumberFormatId = 0, FontId = 2, FillId = 4, BorderId = 1
};
cellStyleFormats.Append(cellFormat);
cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
uint iExcelIndex = 164;
#endregion
#region NumberFormats
var numberingFormats = new NumberingFormats();
var nformatDateTime = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
};
numberingFormats.Append(nformatDateTime);
var nformat4Decimal = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.0000")
};
numberingFormats.Append(nformat4Decimal);
var nformat2Decimal = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.00")
};
numberingFormats.Append(nformat2Decimal);
var nformatForcedText = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex),
FormatCode = StringValue.FromString("@")
};
numberingFormats.Append(nformatForcedText);
#endregion
#region CellFormats
var cellFormats = new CellFormats();
// index 0
cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
FormatId = 0
};
cellFormats.Append(cellFormat);
// index 1
// Cell Standard Date format
cellFormat = new CellFormat
{
NumberFormatId = 14,
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Top
});
cellFormats.Append(cellFormat);
// Index 2
// Cell Standard Number format with 2 decimal placing
cellFormat = new CellFormat
{
NumberFormatId = 4,
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Top
});
cellFormats.Append(cellFormat);
// Index 3
// Cell Date time custom format
cellFormat = new CellFormat
{
NumberFormatId = nformatDateTime.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 4
// Cell 4 decimal custom format
cellFormat = new CellFormat
{
NumberFormatId = nformat4Decimal.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 5
// Cell 2 decimal custom format
cellFormat = new CellFormat
{
NumberFormatId = nformat2Decimal.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 6
// Cell forced number text custom format
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 7
// Cell text with font 12
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 8
// Cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 9
// Coloured 2 decimal cell text
cellFormat = new CellFormat
{
NumberFormatId = nformat2Decimal.NumberFormatId,
FontId = 0,
FillId = 2,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 10
// Coloured cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 2,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 11
// Coloured cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 3,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// index 12 for insight header
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 2,
FillId = 4,
//BorderId = 1,
FormatId = 1,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// index 13
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Top
});
cellFormats.Append(cellFormat);
// index 14 alternate for index 1
// Cell Standard Date format
cellFormat = new CellFormat
{
NumberFormatId = 14,
FontId = 0,
FillId = 5,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Top
});
cellFormats.Append(cellFormat);
// Index 15 alternate for index 2
// Cell Standard Number format with 2 decimal placing
cellFormat = new CellFormat
{
NumberFormatId = 4,
FontId = 0,
FillId = 5,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Top
});
cellFormats.Append(cellFormat);
// index 16 alternate for index 13
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 5,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Top
});
cellFormats.Append(cellFormat);
// Index 17
// Integer Cell format
cellFormat = new CellFormat
{
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Top
});
cellFormats.Append(cellFormat);
// Index 18
// Integer Cell format alternate
cellFormat = new CellFormat
{
FontId = 0,
FillId = 5,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Top
});
cellFormats.Append(cellFormat);
/*Style index for ITAreaList */
// index 19 In-Progress
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 4,
FillId = 0,//6
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// index 20 Submitted
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 5,
FillId = 0,//7
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// index 21 Posted
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 3,
FillId = 0,//8
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// index 22 Accepted
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 6,
FillId = 0,//9
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
/*Style index for ITAreaList */
/*Style index for Dashboard */
// Index 23
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 7,
FillId = 4,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 24
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 8,
FillId = 4,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
Horizontal = HorizontalAlignmentValues.Right
});
cellFormats.Append(cellFormat);
//cellFormat.ApplyAlignment = new BooleanValue(true);
// Index 25
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 7,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 26
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 7,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
};
cellFormat.AppendChild(new Alignment {
Horizontal = HorizontalAlignmentValues.Right
});
cellFormats.AppendChild(cellFormat);
// index 27 for wrapText --AllComments
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
WrapText = true
});
cellFormats.AppendChild(cellFormat);
// index 28 for Alternate Wrap --AllComments
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 5,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormat.AppendChild(new Alignment {
WrapText = true
});
cellFormats.AppendChild(cellFormat);
//cellFormat.ApplyAlignment = new BooleanValue(true);
// index 29 for Alternate Wrap --AllComments
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
};
cellFormat.AppendChild(new Alignment {
Horizontal = HorizontalAlignmentValues.Right
});
cellFormats.AppendChild(cellFormat);
// Index 30
// Coloured cell text without border
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 3,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 31
// Coloured cell text without border right aligned
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 3,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
}
};
cellFormats.AppendChild(cellFormat);
// index 32
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
}
};
cellFormats.Append(cellFormat);
// index 33 alternate for index 32
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 5,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
}
};
cellFormats.Append(cellFormat);
// index 34 date format 17 = 'mmm-yy'
cellFormat = new CellFormat
{
NumberFormatId = 17,
FontId = 1,
FillId = 3,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
}
};
cellFormats.Append(cellFormat);
// index 35
// Cell Standard date format 17 = 'mmm-yy'
cellFormat = new CellFormat
{
NumberFormatId = 17,
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
}
};
cellFormats.Append(cellFormat);
// index 36 alternate for index 35
// Cell Standard date format 17 = 'mmm-yy'
cellFormat = new CellFormat
{
NumberFormatId = 17,
FontId = 0,
FillId = 5,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right, Vertical = VerticalAlignmentValues.Top
}
};
cellFormats.Append(cellFormat);
// Index 37
// Coloured cell text for Excel Header
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 2,
FillId = 3,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 38
// Bold text Cell - Left Align
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 39
// Bold text Cell with background - Center Align
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 3,
BorderId = 2,
FormatId = 0,
ApplyBorder = true,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Center
}
};
cellFormats.Append(cellFormat);
// Index 40
// Bold text Cell - Right Align
cellFormat = new CellFormat
{
NumberFormatId = 4,
FontId = 1,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right
}
};
cellFormats.Append(cellFormat);
/* End Style index for Dashboard */
// Fonts For Tax Savings
// Index 41 For Header
cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 9,
FillId = 6,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Left
}
};
cellFormats.Append(cellFormat);
// Index 42 For Header
cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 10,
FillId = 7,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Left
}
};
cellFormats.Append(cellFormat);
// Index 43 For Body Header Bold
cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 10,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Center
}
};
cellFormats.Append(cellFormat);
// Index 44 For Body Header Normal
cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 11,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Left
}
};
cellFormats.Append(cellFormat);
// Index 45 For Body Cell Float
cellFormat = new CellFormat
{
NumberFormatId = 4,
FontId = 11,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Right
}
};
cellFormats.Append(cellFormat);
numberingFormats.Count = UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);
cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
#endregion
this.Append(numberingFormats);
this.Append(fonts);
this.Append(fills);
this.Append(borders);
this.Append(cellStyleFormats);
this.Append(cellFormats);
var css = new CellStyles();
var cs = new CellStyle
{
Name = StringValue.FromString("Normal"),
FormatId = 0,
BuiltinId = 0
};
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
this.Append(css);
var dfs = new DifferentialFormats {
Count = 0
};
this.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
};
this.Append(tss);
}
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 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;
}
