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; }