internal static int AddDateFormat(Stylesheet stylesheet, uint borderStyleId)
{
if (stylesheet.NumberingFormats == null)
{
stylesheet.NumberingFormats = new NumberingFormats();
}
NumberingFormat nf2decimal = new NumberingFormat();
nf2decimal.NumberFormatId = UInt32Value.FromUInt32(3454);
nf2decimal.FormatCode = StringValue.FromString(@"[$-404]e""-""m""-""d""-""h"":""mm"":""ss;@");
stylesheet.NumberingFormats.AppendChild <NumberingFormat>(nf2decimal);
var cellFormat = new CellFormat();
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = borderStyleId;
cellFormat.FormatId = 0;
cellFormat.NumberFormatId = nf2decimal.NumberFormatId;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormat.ApplyFont = true;
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Center, Horizontal = HorizontalAlignmentValues.Left
});
stylesheet.CellFormats.AppendChild <CellFormat>(cellFormat);
stylesheet.CellFormats.Count = UInt32Value.FromUInt32((uint)stylesheet.CellFormats.ChildElements.Count);
stylesheet.Save();
return(stylesheet.CellFormats.ChildElements.Count - 1);
}
/// <summary>CellFormat の取得</summary>
/// <param name="numFmtId">円貨の金額の場合 38 それ以外は、NumberingFormat を追加し、追加したNumberFormatId</param>
/// <param name="fmtId"></param>
/// <param name="fontId">FontId</param>
/// <param name="fillId">FillId</param>
/// <param name="borderId">BorderId</param>
/// <param name="horizontal"><see cref="HorizontalAlignmentValues"/> [配置]-[横位置]</param>
/// <returns></returns>
public static CellFormat GetCellFormat(
int numFmtId = 0,
int fmtId = 0,
int fontId = 0,
int fillId = 0,
int borderId = 0,
HorizontalAlignmentValues horizontal = HorizontalAlignmentValues.General)
{
var format = new CellFormat
{
NumberFormatId = (uint)numFmtId,
FormatId = (uint)fmtId,
FontId = (uint)fontId,
FillId = (uint)fillId,
BorderId = (uint)borderId,
};
if (numFmtId > 0u)
{
format.ApplyNumberFormat = true;
}
if (fillId > 0u)
{
format.ApplyFill = true;
}
if (borderId > 0u)
{
format.ApplyBorder = true;
}
if (horizontal == HorizontalAlignmentValues.General)
{
format.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Center,
});
}
else
{
format.AppendChild(new Alignment
{
Vertical = VerticalAlignmentValues.Center,
Horizontal = horizontal,
});
format.ApplyAlignment = true;
}
return(format);
}
internal static int AddNumberFormat(Stylesheet stylesheet, uint borderStyleId)
{
if (stylesheet.NumberingFormats == null)
{
stylesheet.NumberingFormats = new NumberingFormats();
}
NumberingFormat nf2decimal = new NumberingFormat();
nf2decimal.NumberFormatId = UInt32Value.FromUInt32(3453);
nf2decimal.FormatCode = StringValue.FromString("0.00");
stylesheet.NumberingFormats.AppendChild <NumberingFormat>(nf2decimal);
var cellFormat = new CellFormat();
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = borderStyleId;
cellFormat.FormatId = 0;
cellFormat.NumberFormatId = nf2decimal.NumberFormatId;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormat.ApplyFont = true;
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Center
});
if (stylesheet.CellFormats == null)
{
stylesheet.CellFormats = new CellFormats();
}
//append cell format for cells of header row
stylesheet.CellFormats.AppendChild <CellFormat>(cellFormat);
//update font count
stylesheet.CellFormats.Count = UInt32Value.FromUInt32((uint)stylesheet.CellFormats.ChildElements.Count);
stylesheet.Save();
return(stylesheet.CellFormats.ChildElements.Count - 1);
//save the changes to the style sheet part
}
public int GetHyperlinkStyleIndex(SpreadsheetStyle style)
{
var styleIdentifier = style.GetIdentifier();
if (_hyperlinkStyles.ContainsKey(styleIdentifier))
{
return(_hyperlinkStyles[styleIdentifier]);
}
var cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, ApplyNumberFormat = false, ApplyFill = false, ApplyBorder = false, ApplyAlignment = false, ApplyProtection = false
};
if (style.Alignment != null || style.Indent != 0 || style.VerticalAlignment != null)
{
var aligment = new Alignment();
if (style.Alignment != null)
{
aligment.Horizontal = style.Alignment.Value;
}
if (style.VerticalAlignment != null)
{
aligment.Vertical = style.VerticalAlignment.Value;
}
if (style.Indent != 0)
{
aligment.Indent = (UInt32)style.Indent;
}
cellFormat.AppendChild(aligment);
}
var styleIndex = _hyperlinkStyles[styleIdentifier] = (int)(UInt32)_stylesheet.CellFormats.Count;
_stylesheet.CellFormats.AppendChild(cellFormat);
_stylesheet.CellFormats.Count++;
return(styleIndex);
}
internal static int AddStringFormat(Stylesheet stylesheet, uint borderStyleId)
{
var cellFormat = new CellFormat();
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = borderStyleId;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormat.ApplyFont = true;
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Center, Horizontal = HorizontalAlignmentValues.Left
});
stylesheet.CellFormats.AppendChild <CellFormat>(cellFormat);
stylesheet.CellFormats.Count = UInt32Value.FromUInt32((uint)stylesheet.CellFormats.ChildElements.Count);
stylesheet.Save();
return(stylesheet.CellFormats.ChildElements.Count - 1);
}
public int GetStyleIndex(SpreadsheetStyle style)
{
var styleIdentifier = style.GetIdentifier();
if (_styles.ContainsKey(styleIdentifier))
{
return(_styles[styleIdentifier]);
}
var fontIndex = 0;
if (style.Font != null)
{
var key = new FontKey(style.Font, style.ForegroundColor);
if (_fonts.ContainsKey(key))
{
fontIndex = _fonts[key];
}
else
{
var newFont = new Font();
newFont.FontName = new FontName()
{
Val = style.Font.Name
};
newFont.FontSize = new FontSize()
{
Val = style.Font.Size
};
if (style.Font.Bold)
{
newFont.Bold = new Bold();
}
if (style.Font.Italic)
{
newFont.Italic = new Italic();
}
if (style.ForegroundColor != null)
{
newFont.Color = new Color()
{
Rgb = String.Format("{0:X2}{1:X2}{2:X2}{3:X2}", style.ForegroundColor.Value.A, style.ForegroundColor.Value.R, style.ForegroundColor.Value.G, style.ForegroundColor.Value.B)
};
}
_stylesheet.Fonts.AppendChild(newFont);
fontIndex = _fonts[key] = (int)((UInt32)_stylesheet.Fonts.Count);
_stylesheet.Fonts.Count++;
}
}
var fillIndex = 0;
if (style.BackgroundColor != null)
{
if (_fills.ContainsKey(style.BackgroundColor.Value))
{
fillIndex = _fills[style.BackgroundColor.Value];
}
else
{
var newFill = new PatternFill()
{
PatternType = PatternValues.Solid
};
newFill.ForegroundColor = new ForegroundColor()
{
Rgb = String.Format("{0:X2}{1:X2}{2:X2}{3:X2}", style.BackgroundColor.Value.A, style.BackgroundColor.Value.R, style.BackgroundColor.Value.G, style.BackgroundColor.Value.B)
};
newFill.BackgroundColor = new BackgroundColor()
{
Indexed = 64U
};
fillIndex = (int)(UInt32)_stylesheet.Fills.Count;
_fills[style.BackgroundColor.Value] = fillIndex;
_stylesheet.Fills.AppendChild(new Fill()
{
PatternFill = newFill
});
_stylesheet.Fills.Count++;
}
}
var numberingId = 0;
if (style.IsDate)
{
numberingId = 164;
}
var cellFormat = new CellFormat()
{
FormatId = 0, FontId = (UInt32)fontIndex, FillId = (UInt32)fillIndex, BorderId = 0, NumberFormatId = (UInt32)numberingId, ApplyFill = true
};
if (style.Alignment != null || style.VerticalAlignment != null || style.Indent != 0 || style.WrapText)
{
var aligment = new Alignment();
if (style.Alignment != null)
{
aligment.Horizontal = style.Alignment.Value;
}
if (style.VerticalAlignment != null)
{
aligment.Vertical = style.VerticalAlignment.Value;
}
if (style.Indent != 0)
{
aligment.Indent = (UInt32)style.Indent;
}
if (style.WrapText)
{
aligment.WrapText = true;
}
cellFormat.AppendChild(aligment);
}
var styleIndex = _styles[styleIdentifier] = (int)(UInt32)_stylesheet.CellFormats.Count;
_stylesheet.CellFormats.AppendChild(cellFormat);
_stylesheet.CellFormats.Count++;
return(styleIndex);
}
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);
}
internal static int AddCaptainFormat(Stylesheet stylesheet, uint borderId)
{
Fill fill = new Fill();
PatternFill patternFill = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor1 = new ForegroundColor()
{
Rgb = "FF305496"
};
BackgroundColor backgroundColor1 = new BackgroundColor()
{
Indexed = 64
};
patternFill.Append(foregroundColor1);
patternFill.Append(backgroundColor1);
fill.Append(patternFill);
if (stylesheet.Fills == null)
{
stylesheet.Fills = new Fills();
stylesheet.Fills.Count = 0U;
}
stylesheet.Fills.AppendChild <Fill>(fill);
uint fillId = (uint)stylesheet.Fills.Count++;
Font font = new Font()
{
Color = new Color()
{
Rgb = "FFFFFFFF"
}
};
stylesheet.Fonts.AppendChild(font);
uint fontId = stylesheet.Fonts.Count++;
var cellFormat = new CellFormat();
cellFormat.FontId = fontId;
cellFormat.FillId = fillId;
cellFormat.BorderId = borderId;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormat.ApplyFont = true;
cellFormat.AppendChild(new Alignment {
Vertical = VerticalAlignmentValues.Center, Horizontal = HorizontalAlignmentValues.Center
});
stylesheet.CellFormats.AppendChild <CellFormat>(cellFormat);
stylesheet.CellFormats.Count = UInt32Value.FromUInt32((uint)stylesheet.CellFormats.ChildElements.Count);
stylesheet.Save();
return(stylesheet.CellFormats.ChildElements.Count - 1);
}
private void CreateStylesheet(WorkbookPart workbookPart)
{
var workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rId101");
var stylesheet = new Stylesheet {
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "x14ac"
}
};
stylesheet.AddNamespaceDeclaration("mc", MC_SCHEMA);
stylesheet.AddNamespaceDeclaration("x14ac", X14_AC_SCHEMA);
var numberingFormats = new NumberingFormats(new NumberingFormat
{
NumberFormatId = 44U,
FormatCode = FORMAT_CODE
})
{
Count = 1U
};
var fonts = new Fonts {
Count = 3U, KnownFonts = true
};
var font1 = new Font(new FontSize {
Val = 11D
}, new Color {
Theme = 1U
}, new FontName {
Val = "Calibri"
},
new FontFamilyNumbering {
Val = 2
}, new FontScheme {
Val = FontSchemeValues.Minor
});
var font2 = new Font(new FontSize {
Val = 11D
}, new Color {
Theme = 1U
}, new FontName {
Val = "Calibri"
},
new FontFamilyNumbering {
Val = 2
}, new FontScheme {
Val = FontSchemeValues.Minor
});
var font3 = new Font(new Bold(), new FontSize {
Val = 11D
}, new Color {
Theme = 0U
},
new FontName {
Val = "Calibri"
}, new FontFamilyNumbering {
Val = 2
},
new FontScheme {
Val = FontSchemeValues.Minor
});
fonts.Append(font1, font2, font3);
var fills = new Fills {
Count = 3U
};
var fill1 = new Fill(new PatternFill {
PatternType = PatternValues.None
});
var fill2 = new Fill(new PatternFill {
PatternType = PatternValues.Gray125
});
var fill3 = new Fill(new PatternFill(
new ForegroundColor {
Rgb = "FF0070C0"
},
new BackgroundColor {
Indexed = 64U
})
{
PatternType = PatternValues.Solid
});
fills.Append(fill1, fill2, fill3);
var borders = new Borders(new Border(), new LeftBorder(), new RightBorder(), new TopBorder(),
new BottomBorder(), new DiagonalBorder())
{
Count = 1U
};
var cellStyleFormats = new CellStyleFormats {
Count = 2U
};
var cellFormat1 = new CellFormat {
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U
};
var cellFormat2 = new CellFormat
{
NumberFormatId = 44U,
FontId = 1U,
FillId = 0U,
BorderId = 0U,
ApplyFont = false,
ApplyFill = false,
ApplyBorder = false,
ApplyAlignment = false,
ApplyProtection = false
};
cellStyleFormats.Append(cellFormat1, cellFormat2);
var cellFormats = new CellFormats {
Count = 6U
};
var cellFormat3 = new CellFormat
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U
};
var cellFormat4 = new CellFormat
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyProtection = true
};
var protection1 = new Protection {
Locked = false
};
cellFormat4.AppendChild(protection1);
var cellFormat5 = new CellFormat
{
NumberFormatId = 44U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 1U,
ApplyFont = true,
ApplyProtection = true
};
var protection2 = new Protection {
Locked = false
};
cellFormat5.AppendChild(protection2);
var cellFormat6 = new CellFormat
{
NumberFormatId = 14U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyNumberFormat = true,
ApplyProtection = true
};
var protection3 = new Protection {
Locked = false
};
cellFormat6.AppendChild(protection3);
var cellFormat7 = new CellFormat
{
NumberFormatId = 0U,
FontId = 2U,
FillId = 2U,
BorderId = 0U,
FormatId = 0U,
ApplyFont = true,
ApplyFill = true,
ApplyProtection = true
};
var cellFormat8 = new CellFormat
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyProtection = true
};
cellFormats.Append(cellFormat3, cellFormat4, cellFormat5, cellFormat6, cellFormat7, cellFormat8);
var cellStyles = new CellStyles {
Count = 2U
};
var cellStyle1 = new CellStyle {
Name = "Currency", FormatId = 1U, BuiltinId = 4U
};
var cellStyle2 = new CellStyle {
Name = "Normal", FormatId = 0U, BuiltinId = 0U
};
cellStyles.Append(cellStyle1, cellStyle2);
var differentialFormats = new DifferentialFormats {
Count = 0U
};
var tableStyles = new TableStyles
{
Count = 0U,
DefaultTableStyle = "TableStyleMedium2",
DefaultPivotStyle = "PivotStyleLight16"
};
var stylesheetExtensionList = new StylesheetExtensionList();
var stylesheetExtension = new StylesheetExtension {
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension.AddNamespaceDeclaration("x14",
X14_SCHEMA);
var slicerStyles = new X14.SlicerStyles {
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension.AppendChild(slicerStyles);
stylesheetExtensionList.AppendChild(stylesheetExtension);
stylesheet.Append(numberingFormats, fonts, fills, borders, cellStyleFormats, cellFormats, cellStyles,
differentialFormats, tableStyles, stylesheetExtensionList);
workbookStylesPart.Stylesheet = stylesheet;
}
