public void AddAlignment(string name, HorizontalAlignmentValues hv, VerticalAlignmentValues vv, string c) { var filepath = getOutputDir() + name; using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(filepath, true)) { var iter = spreadSheet.WorkbookPart.WorksheetParts.GetEnumerator(); iter.MoveNext(); var workSheetPart = iter.Current; Cell cell = InsertCellInWorksheet(GetColumnName(c), GetRowIndex(c), workSheetPart); Fills fs = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Fills; var cf = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats; CellFormat cellFormat2 = new CellFormat() { NumberFormatId = 0, FontId = 0, FillId = (UInt32)(fs.Elements <Fill>().Count() - 1), BorderId = 0, FormatId = 0, Alignment = new Alignment() { Horizontal = hv, Vertical = vv }, ApplyFill = true }; cf.Append(cellFormat2); spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save(); cell.StyleIndex = (UInt32)(spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Elements <CellFormat>().Count() - 1); workSheetPart.Worksheet.Save(); } }
/// ----------------------------------------------------------------------------------------------- /// <summary> /// Overload 2: Constructor, Initialize all fields /// </summary> /// <param name="boundColumnName">string</param> /// <param name="userDefinedColumnName">string</param> /// <param name="numberFormat"></param> /// <param name="horizontalAlignment"></param> /// <param name="showField">bool</param> /// <param name="fieldOrder">int?</param> /// <param name="userDefinedNumberFormat">string</param> /// ----------------------------------------------------------------------------------------------- public Column( string boundColumnName, string userDefinedColumnName, NumberFormats numberFormat = NumberFormats.General, HorizontalAlignmentValues horizontalAlignment = HorizontalAlignmentValues.Center, bool showField = true, int?fieldOrder = null, string userDefinedNumberFormat = null) { try { BoundColumnName = boundColumnName; UserDefinedColumnName = userDefinedColumnName; NumberFormat = numberFormat; HorizontalAlignment = horizontalAlignment; ShowField = showField; FieldOrder = fieldOrder; UserDefinedNumberFormat = userDefinedNumberFormat; } catch (Exception ex) { Log.Error("SpreadsheetLightWrapper.Export.Models.Column.Contructor:Overload 2 -> " + ex.Message + ": " + ex); } }
/// <summary> /// Добавление формата ячейки /// </summary> /// <param name="valueHorizontalAlignment">Выравнивание по горизонтали</param> /// <param name="valueVerticalAlignment">Выравнивание по горизонтали</param> /// <param name="fontId">Ссылка на шрифт</param> /// <param name="borderId">Ссылка на границу</param> /// <param name="fillId">Ссылка на fill</param> /// <param name="numberingFormatsId">Id Формата</param> /// <returns></returns> public CellFormat GenerateCellFormat(HorizontalAlignmentValues valueHorizontalAlignment = HorizontalAlignmentValues.Left, VerticalAlignmentValues valueVerticalAlignment = VerticalAlignmentValues.Bottom, uint fontId = 0, uint borderId = 0, uint fillId = 0, uint numberingFormatsId = 0) { CellFormat cellFormat = new CellFormat(new Alignment() { WrapText = true, Horizontal = valueHorizontalAlignment, Vertical = valueVerticalAlignment }) { ApplyAlignment = true, }; if (fontId != 0) { cellFormat.FontId = fontId; cellFormat.ApplyFont = true; } if (borderId != 0) { cellFormat.BorderId = borderId; cellFormat.ApplyBorder = true; } if (fillId != 0) { cellFormat.FillId = fillId; cellFormat.ApplyFill = true; } if (numberingFormatsId != 0) { cellFormat.NumberFormatId = numberingFormatsId; cellFormat.ApplyNumberFormat = true; cellFormat.FormatId = numberingFormatsId; } return(cellFormat); }
/// <summary> /// Привязка к шрифту и указывать полная ли граница или нет! /// </summary> /// <param name="border">Граница</param> /// <param name="font">Модель шрифта</param> /// <param name="valueAlignment">Выравнивание по Горизонтали</param> /// <param name="valueVerticalAlignment">Выравнивание по Вертикале</param> /// <param name="isFill">Наполнение Fill</param> /// <param name="isFormatDouble">Формат с плавающей точкой</param> /// <param name="fill">Наполнение</param> /// <returns></returns> public uint StyleTimesNewRoman(Font font, Border border = null, HorizontalAlignmentValues valueAlignment = HorizontalAlignmentValues.Left, VerticalAlignmentValues valueVerticalAlignment = VerticalAlignmentValues.Bottom, bool isFill = false, bool isFormatDouble = false, Fill fill = null) { uint idBorder = 0; uint idFill = 0; uint idFont = 0; uint idnumberingFormats = 0; idFont = InsertFont(font); if (isFill) { idFill = InsertFill(GenerateFill()); } if (border != null) { idBorder = InsertBorder(border); } if (fill != null) { idFill = InsertFill(fill); } if (isFormatDouble) { idnumberingFormats = InsertNumberingFormats(GenerateNumberingFormats()); } return(InsertCellFormat(GenerateCellFormat(valueAlignment, valueVerticalAlignment, idFont, idBorder, idFill, idnumberingFormats))); }
///<summary> ///Sets the horizontal alignment value ///</summary> public void SetHorizontalAlignment(HorizontalAlignmentValues value) { if (HorizontalAlignment == null) { HorizontalAlignment = new EnumValue <HorizontalAlignmentValues>(); } HorizontalAlignment.Value = value; }
private Alignment GetDefaultAlignment(Alignment tplAlignment, HorizontalAlignmentValues defHorizontalAlignment) { var alignment = tplAlignment != null?tplAlignment.CloneNode(true) as Alignment : new Alignment(); if (defHorizontalAlignment != HorizontalAlignmentValues.General) { alignment.Horizontal = defHorizontalAlignment; } return(alignment); }
public StyleExcel(StyleFont font, StyleFill fill, CellFormat format, VerticalAlignmentValues vertical, HorizontalAlignmentValues horizontal, bool iswordWrap, bool isBorder) { Font = font; Fill = fill; Format = format; Vertical = vertical; Horizontal = horizontal; IsWordWrap = iswordWrap; IsBorder = isBorder; }
public ExcelNumberFormatAttribute(bool applyNumberFormat, UInt32 numberFormat = 0U, double width = 10D, int horizontalAlignment = -1) { NumberFormat = numberFormat; ApplyNumberFormat = applyNumberFormat; Width = DoubleValue.FromDouble(width); if (Enum.IsDefined(typeof(HorizontalAlignmentValues), horizontalAlignment)) { HorizontalAlignment = (HorizontalAlignmentValues)horizontalAlignment; } }
public OpenXmlExStyleCell(KeyValuePair <uint, OpenXmlExStyleFont> Font, KeyValuePair <uint, OpenXmlExStyleFill> Fill, KeyValuePair <uint, OpenXmlExStyleBorderGrand> Border, bool Wrap, HorizontalAlignmentValues h_align, VerticalAlignmentValues v_align) { FontStyle = Font; FillStyle = Fill; BorderStyle = Border; HorizontalAlignment = h_align; VerticalAlignment = v_align; WrapText = Wrap; }
private void SetAllNull() { this.vHorizontal = HorizontalAlignmentValues.General; this.HasHorizontal = false; this.vVertical = VerticalAlignmentValues.Bottom; this.HasVertical = false; this.TextRotation = null; this.WrapText = null; this.Indent = null; this.RelativeIndent = null; this.JustifyLastLine = null; this.ShrinkToFit = null; this.vReadingOrder = SLAlignmentReadingOrderValues.LeftToRight; this.HasReadingOrder = false; }
private void SetAllNull() { vHorizontal = HorizontalAlignmentValues.General; HasHorizontal = false; vVertical = VerticalAlignmentValues.Bottom; HasVertical = false; TextRotation = null; WrapText = null; Indent = null; RelativeIndent = null; JustifyLastLine = null; ShrinkToFit = null; vReadingOrder = SLAlignmentReadingOrderValues.LeftToRight; HasReadingOrder = false; }
/// <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); }
/// ----------------------------------------------------------------------------------------------- /// <summary> /// Overload 2: Overrides the default bound column names with custom ones and determines the formatting, /// visibility and order. /// ** Note: If none are added then the column names from the dataset table will be used and /// there will no formatting. /// <para /> /// "boundColumnName" string: Bound column name /// <para /> /// "userDefinedColumnName" string: Custom column name /// <para /> /// "numberFormat" NumberFormats: Column format /// <para /> /// "horizontalAlignment" HorizontalAlignmentValues: Column horizontalAlignment /// <para /> /// "showField" bool: Show/Hide column /// <para /> /// "fieldOrder" int: Set the order of the field /// <para /> /// "userDefinedNumberFormat" string: User-defined excel format /// * Must be populated when "numberFormat" is set to "User-Defined" /// </summary> /// <param name="boundColumnName">string: Bound column name</param> /// <param name="userDefinedColumnName">string: User-Defined column name</param> /// <param name="numberFormat">string: Column number format</param> /// <param name="horizontalAlignment">string: Column horizontalAlignment</param> /// <param name="showField">bool: Show/Hide column</param> /// <param name="fieldOrder">int: Set the order of the field</param> /// <param name="userDefinedNumberFormat">string</param> /// ----------------------------------------------------------------------------------------------- public void SetUserDefinedColumnNames( string boundColumnName, string userDefinedColumnName, NumberFormats numberFormat, HorizontalAlignmentValues horizontalAlignment, bool showField, int?fieldOrder, string userDefinedNumberFormat = null) { try { if (UserDefinedColumns.Any(x => x.BoundColumnName == boundColumnName)) { UserDefinedColumns.RemoveAt(UserDefinedColumns.FindIndex(x => x.BoundColumnName == boundColumnName)); UserDefinedColumns.Add(new Column { BoundColumnName = boundColumnName, UserDefinedColumnName = userDefinedColumnName, NumberFormat = numberFormat, HorizontalAlignment = horizontalAlignment, ShowField = showField, FieldOrder = fieldOrder, UserDefinedNumberFormat = userDefinedNumberFormat }); } else { UserDefinedColumns.Add(new Column { BoundColumnName = boundColumnName, UserDefinedColumnName = userDefinedColumnName, NumberFormat = numberFormat, HorizontalAlignment = horizontalAlignment, ShowField = showField, FieldOrder = fieldOrder, UserDefinedNumberFormat = userDefinedNumberFormat }); } } catch (Exception ex) { Log.Error( "SpreadsheetLightWrapper.Export.Models.ChildSetting.SetUserDefinedColumnNames:Overload 2 -> " + ex.Message + ": " + ex); } }
/// <summary> /// Inserts the value. /// </summary> /// <param name="text">The text.</param> /// <param name="column">The column.</param> /// <param name="rowindex">The rowindex.</param> /// <param name="align">The align.</param> /// <param name="valueType">Type of the value.</param> public void InsertValue(string text, string column, int rowindex, HorizontalAlignmentValues? align, CellValues valueType) { if (CurrentSheet == null) throw new Exception("No sheet selected"); Cell cell = LocateCell(column, rowindex); cell.CellValue = new CellValue(text); cell.DataType = new EnumValue<CellValues>(valueType); CurrentSheet.Worksheet.Save(); }
/// <summary> /// Inserts the value. /// </summary> /// <param name="text">The text.</param> /// <param name="column">The column.</param> /// <param name="rowindex">The rowindex.</param> /// <param name="align">The align.</param> public void InsertValue(string text, string column, int rowindex, HorizontalAlignmentValues? align) { InsertValue(text, column, rowindex, align, CellValues.String); }
private void SetAllNull() { this.vHorizontal = HorizontalAlignmentValues.General; this.HasHorizontal = false; this.vVertical = VerticalAlignmentValues.Bottom; this.HasVertical = false; this.TextRotation = null; this.WrapText = null; this.Indent = null; this.RelativeIndent = null; this.JustifyLastLine = null; this.ShrinkToFit = null; this.vReadingOrder = SLAlignmentReadingOrderValues.LeftToRight; this.HasReadingOrder = false; }
/// <summary> /// 水平方向のアライメントを指定します。 /// </summary> /// <param name="value">アライメント</param> /// <returns>スタイル(メソッドチェーン用)</returns> public SpreadsheetStyle SetHorizontalAlignment(HorizontalAlignmentValues value) { Alignment.Horizontal = value; return(this); }
/// <summary> /// Inserts the value. /// </summary> /// <param name="value">The value.</param> /// <param name="column">The column.</param> /// <param name="rowindex">The rowindex.</param> /// <param name="align">The align.</param> public void InsertValue(int value, string column, int rowindex, HorizontalAlignmentValues? align) { InsertValue((double)value, column, rowindex, align); }
public Stylesheet CreateStylesheet() { try { HorizontalAlignmentValues leftHorizontal = HorizontalAlignmentValues.Left; HorizontalAlignmentValues rightHorizontal = HorizontalAlignmentValues.Right; HorizontalAlignmentValues centerHorizontal = HorizontalAlignmentValues.Center; VerticalAlignmentValues topVertical = VerticalAlignmentValues.Top; VerticalAlignmentValues centerVertical = VerticalAlignmentValues.Center; return(new Stylesheet( new Fonts( /*Index 0 - Black*/ CreateFont("000000", false), /*Index 1 - Bold Black*/ CreateFont("000000", true), /*Index 2 - Purple*/ CreateFont("660066", false), /*Index 3 - Bold Purple*/ CreateFont("660066", true), /*Index 4 - Red*/ CreateFont("990000", false), /*Index 5 - Bold Red*/ CreateFont("990000", true), /*Index 6 - Orange*/ CreateFont("FF6600", false), /*Index 7 - Bold Orange*/ CreateFont("FF6600", true), /*Index 8 - Blue*/ CreateFont("0066FF", false), /*Index 9 - Bold Blue*/ CreateFont("0066FF", true), /*Index 10 - Green*/ CreateFont("339900", false), /*Index 11 - Bold Green*/ CreateFont("339900", true), /*Index 12 - Bold Black Large*/ CreateFont("000000", true) ), new Fills( /*Index 0 - Default Fill (None)*/ CreateFill(string.Empty, PatternValues.None), /*Index 1 - Default Fill (Gray125)*/ CreateFill(string.Empty, PatternValues.Gray125), /*Index 2 - Dark Gray Fill*/ CreateFill("BBBBBB", PatternValues.Solid), /*Index 3 - Light Gray Fill*/ CreateFill("EEEEEE", PatternValues.Solid), /*Index 4 - Yellow Gray Fill*/ CreateFill("FFCC00", PatternValues.Solid) ), new Borders( /*Index 0 - Default Border (None)*/ CreateBorder(false, false, false, false), /*Index 1 - All Borders*/ CreateBorder(true, true, true, true), /*Index 2 - Top & Bottom Borders*/ CreateBorder(true, false, true, false) ), new CellFormats( /*Index 0 - Black Font, No Fill, No Borders, Wrap Text*/ CreateCellFormat(0, 0, 0, leftHorizontal, null, true), /*Index 1 - Black Font, No Fill, No Borders, Horizontally Centered*/ CreateCellFormat(0, 0, 0, centerHorizontal, null, false), /*Index 2 - Bold Black Font, Dark Gray Fill, All Borders*/ CreateCellFormat(1, 2, 1, null, null, false), /*Index 3 - Bold Black Font, Dark Gray Fill, Top & Bottom Borders, Centered*/ CreateCellFormat(1, 2, 2, centerHorizontal, centerVertical, false), /*Index 4 - Bold Black Font, Dark Gray Fill, All Borders, Centered*/ CreateCellFormat(1, 2, 1, centerHorizontal, centerVertical, false), /*Index 5 - Bold Purple Font, Light Gray Fill, Top & Bottom Borders, Centered*/ CreateCellFormat(3, 3, 2, centerHorizontal, centerVertical, false), /*Index 6 - Bold Red Font, Light Gray Fill, Top & Bottom Borders, Centered*/ CreateCellFormat(5, 3, 2, centerHorizontal, centerVertical, false), /*Index 7 - Bold Orange Font, Light Gray Fill, Top & Bottom Borders, Centered*/ CreateCellFormat(7, 3, 2, centerHorizontal, centerVertical, false), /*Index 8 - Bold Blue Font, Light Gray Fill, Top & Bottom Borders, Centered*/ CreateCellFormat(9, 3, 2, centerHorizontal, centerVertical, false), /*Index 9 - Bold Green Font, Light Gray Fill, Top & Bottom Borders, Centered*/ CreateCellFormat(11, 3, 2, centerHorizontal, centerVertical, false), /*Index 10 - Purple Font, No Fill, Top & Bottom Borders, Centered*/ CreateCellFormat(2, 0, 2, centerHorizontal, centerVertical, false), /*Index 11 - Red Font, No Fill, Top & Bottom Borders, Centered*/ CreateCellFormat(4, 0, 2, centerHorizontal, centerVertical, false), /*Index 12 - Orange Font, No Fill, Top & Bottom Borders, Centered*/ CreateCellFormat(6, 0, 2, centerHorizontal, centerVertical, false), /*Index 13 - Blue Font , No Fill, Top & Bottom Borders, Centered*/ CreateCellFormat(8, 0, 2, centerHorizontal, centerVertical, false), /*Index 14 - Green Font, No Fill, Top & Bottom Borders, Centered*/ CreateCellFormat(10, 0, 2, centerHorizontal, centerVertical, false), /*Index 15 - Bold Black Font, Yellow Fill, All Borders, Centered, Wrap Text*/ CreateCellFormat(1, 4, 1, centerHorizontal, centerVertical, true), /*Index 16 - Bold Black Font, No Fill, All Borders, Wrap Text*/ CreateCellFormat(1, 0, 1, centerHorizontal, centerVertical, true), /*Index 17 - Bold Black Font, Light Gray Fill, Top & Bottom Borders, Centered*/ CreateCellFormat(1, 3, 2, centerHorizontal, centerVertical, false), /*Index 18 - Bold Black Font, No Fill, Top & Bottom Borders, Centered*/ CreateCellFormat(0, 0, 2, centerHorizontal, centerVertical, false), /*Index 19 - Bold Black Font, Dark Gray Fill, Top & Bottom Borders, Centered Vertically*/ CreateCellFormat(1, 2, 1, null, centerVertical, false), /*Index 20 - Black Font, No Fill, All Borders, Top Aligned, Wrap Text*/ CreateCellFormat(0, 0, 1, null, topVertical, true), /*Index 21 - Black Font, No Fill, All Borders, Centered Vertically, Wrap Text*/ CreateCellFormat(0, 0, 1, null, centerVertical, true), /*Index 22 - Black Font, No Fill, All Borders, Centered, Wrap Text*/ CreateCellFormat(0, 0, 1, centerHorizontal, centerVertical, true), /*Index 23 - Black Font, No Fill, All Borders, Centered Vertically, Right Aligned*/ CreateCellFormat(0, 0, 1, rightHorizontal, centerVertical, false), /*Index 24 - Black Font, No Fill, All Borders, Centered Horizontally, Top Aligned, Wrap Text*/ CreateCellFormat(0, 0, 1, centerHorizontal, topVertical, true), /*Index 25 - Bold Black Font, No Fill, No Borders, Wrap Text*/ CreateCellFormat(1, 0, 0, leftHorizontal, topVertical, false), /*Index 26 - Bold Black Font, Dark Gray Fill, All Borders, Centered, Wrap Text*/ CreateCellFormat(1, 2, 1, centerHorizontal, centerVertical, true) ) )); } catch (Exception exception) { LogWriter.LogError("Unable to create Excel report Stylesheet."); throw exception; } }
/// <summary> /// Inserts the value. /// </summary> /// <param name="value">The value.</param> /// <param name="column">The column.</param> /// <param name="rowindex">The rowindex.</param> /// <param name="align">The align.</param> public void InsertValue(double value, string column, int rowindex, HorizontalAlignmentValues? align) { InsertValue(value.ToString(), column, rowindex, align, CellValues.Number); }
/// <summary> /// Inserts the value. /// </summary> /// <param name="value">if set to <c>true</c> [value].</param> /// <param name="column">The column.</param> /// <param name="rowindex">The rowindex.</param> /// <param name="align">The align.</param> public void InsertValue(bool value, string column, int rowindex, HorizontalAlignmentValues? align) { string svalue = "0"; if (value) svalue = "1"; InsertValue(svalue, column, rowindex, align, CellValues.Boolean); }
/// <summary> /// Align text horizontally. /// </summary> /// <param name="HorizontalAlignment">Specifies the horizontal alignment. Default value is General.</param> public void SetHorizontalAlignment(HorizontalAlignmentValues HorizontalAlignment) { this.Alignment.Horizontal = HorizontalAlignment; }
/// <summary> /// Inserts the value. /// </summary> /// <param name="value">The value.</param> /// <param name="column">The column.</param> /// <param name="rowindex">The rowindex.</param> /// <param name="align">The align.</param> public void InsertValue(DateTime value, string column, int rowindex, HorizontalAlignmentValues? align) { if (CurrentSheet == null) throw new Exception("No sheet selected"); string svalue; if (value.Hour == 0 && value.Minute == 0 && value.Millisecond == 0) { svalue = value.ToString("dd/MM-yyyy"); } else svalue = value.ToString("dd/MM-yyyy HH:mm:ss"); InsertValue(svalue, column, rowindex, align, CellValues.String); }
public void SetAlignment(HorizontalAlignmentValues hAlign, VerticalAlignmentValues vAlign, bool wrapText = true) { AlignmentValue = new Alignment { Horizontal = hAlign, Vertical = vAlign, WrapText = wrapText }; }
public void CreateStyle(string numberFormatCode, double fontSize, string fontName, string fontColor , bool fontBold, bool fontItalic, PatternValues fillPattern, string fillForeGroundColor, string borderLeftColor , BorderStyleValues borderLeftStyleValue, string borderBottomColor, BorderStyleValues borderBottomStyleValue, string borderRightColor , BorderStyleValues borderRightStyleValue, string borderTopColor, BorderStyleValues borderTopStyleValue, HorizontalAlignmentValues alignmentHorizontal, VerticalAlignmentValues alignmentVertical) { LCreateStyle(numberFormatCode, fontSize, fontName, fontColor , fontBold, fontItalic, fillPattern, fillForeGroundColor, borderLeftColor , borderLeftStyleValue, borderBottomColor, borderBottomStyleValue, borderRightColor , borderRightStyleValue, borderTopColor, borderTopStyleValue, alignmentHorizontal, alignmentVertical, textRotate); }
//创建一个单元格样式 //todo 如果参数下次再多,就需要考虑把同类的细节参数放到一个新建立参数类中,以提升代码阅读性。 private static UInt32Value createCellFormat(Stylesheet styleSheet, UInt32Value borderid, UInt32Value fontIndex, UInt32Value fillIndex, UInt32Value numberFormatId, HorizontalAlignmentValues horizontal = HorizontalAlignmentValues.Left) { if (styleSheet.CellFormats.Count == null) { styleSheet.CellFormats.Count = (UInt32Value)0; } CellFormat cellFormat = null; if (horizontal == HorizontalAlignmentValues.Left) { cellFormat = new CellFormat(); } else { Alignment alignment = new Alignment(); alignment.Horizontal = horizontal; alignment.Vertical = VerticalAlignmentValues.Center; cellFormat = new CellFormat(alignment); } cellFormat.BorderId = 0; if (fontIndex != null) { cellFormat.ApplyFont = true; cellFormat.FontId = fontIndex; } if (borderid != null) { cellFormat.ApplyBorder = true; cellFormat.BorderId = borderid; } if (fillIndex != null) { cellFormat.FillId = fillIndex; cellFormat.ApplyFill = true; } if (numberFormatId != null) { cellFormat.NumberFormatId = numberFormatId; cellFormat.ApplyNumberFormat = true; } styleSheet.CellFormats.Append(cellFormat); UInt32Value result = styleSheet.CellFormats.Count; styleSheet.CellFormats.Count++; return(result); }
private void LCreateStyle(string numberFormatCode, double fontSize, string fontName, System.Drawing.Color fontColor , bool fontBold, bool fontItalic, PatternValues fillPattern, System.Drawing.Color fillForeGroundColor, System.Drawing.Color borderLeftColor , BorderStyleValues borderLeftStyleValue, System.Drawing.Color borderBottomColor, BorderStyleValues borderBottomStyleValue, System.Drawing.Color borderRightColor , BorderStyleValues borderRightStyleValue, System.Drawing.Color borderTopColor, BorderStyleValues borderTopStyleValue, HorizontalAlignmentValues alignmentHorizontal, VerticalAlignmentValues alignmentVertical) { if (numberFormatCode != null) { NumberingFormat numberingFormat = new NumberingFormat(); numberingFormat.FormatCode = numberFormatCode; _oNumberingFormat = numberingFormat; } //font Font font = new Font(); FontSize _fontSize = new FontSize() { Val = DoubleValue.FromDouble(fontSize) }; font.Append(_fontSize); if (fontName != null) { FontName _fontName = new FontName() { Val = fontName }; font.Append(_fontName); } if (fontColor != null) { Color _fontColor = new Color() { Rgb = new HexBinaryValue() { Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(fontColor.R, fontColor.G, fontColor.B)).Replace("#", "") } }; font.Append(_fontColor); } if (fontBold) { Bold _fontBold = new Bold(); font.Append(_fontBold); } if (fontItalic) { Italic _fontItalic = new Italic(); font.Append(_fontItalic); } _oFont = font; //fill Fill _fontFill = null; if (fillForeGroundColor == null) { _fontFill = new Fill(new PatternFill() { PatternType = fillPattern }); } else { _fontFill = new Fill(new PatternFill( new ForegroundColor() { Rgb = new HexBinaryValue() { Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(fillForeGroundColor.R, fillForeGroundColor.G, fillForeGroundColor.B)).Replace("#", "") } } ) { PatternType = PatternValues.Solid }); } _oFill = _fontFill; //border Border _border = new Border(); LeftBorder leftBorder = new LeftBorder() { Style = borderLeftStyleValue }; Color colorLeftBorder = null; if (borderLeftColor != null) { colorLeftBorder = new Color() { Rgb = new HexBinaryValue { Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(borderLeftColor.R, borderLeftColor.G, borderLeftColor.B)).Replace("#", "") } }; } else { colorLeftBorder = new Color(); } leftBorder.Append(colorLeftBorder); _border.Append(leftBorder); RightBorder rightBorder = new RightBorder() { Style = borderRightStyleValue }; Color colorRightBorder = null; if (borderRightColor != null) { colorRightBorder = new Color() { Rgb = new HexBinaryValue { Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(borderRightColor.R, borderRightColor.G, borderRightColor.B)).Replace("#", "") } }; } else { colorRightBorder = new Color(); } rightBorder.Append(colorRightBorder); _border.Append(rightBorder); TopBorder topBorder = new TopBorder() { Style = borderTopStyleValue }; Color colorTopBorder = null; if (borderTopColor != null) { colorTopBorder = new Color() { Rgb = new HexBinaryValue { Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(borderTopColor.R, borderTopColor.G, borderTopColor.B)).Replace("#", "") } }; } else { colorTopBorder = new Color(); } topBorder.Append(colorTopBorder); _border.Append(topBorder); BottomBorder bottomBorder = new BottomBorder() { Style = borderBottomStyleValue }; Color colorBottomBorder = null; if (borderBottomColor != null) { colorBottomBorder = new Color() { Rgb = new HexBinaryValue { Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(borderBottomColor.R, borderBottomColor.G, borderBottomColor.B)).Replace("#", "") } }; } else { colorBottomBorder = new Color(); } bottomBorder.Append(colorBottomBorder); _border.Append(bottomBorder); _oBorder = _border; Alignment _alignment = new Alignment(); _alignment.Horizontal = alignmentHorizontal; _alignment.Vertical = alignmentVertical; _oAlignment = _alignment; _oAlignment.WrapText = wrapText; _oAlignment.TextRotation.Value = textRotate; }