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> ///Sets the vertical alignment value ///</summary> public void SetVerticalAlignment(VerticalAlignmentValues value) { if (VerticalAlignment == null) { VerticalAlignment = new EnumValue <VerticalAlignmentValues>(); } VerticalAlignment.Value = value; }
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 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> /// 水直方向のアライメントを指定します。 /// </summary> /// <param name="value">アライメント</param> /// <returns>スタイル(メソッドチェーン用)</returns> public SpreadsheetStyle SetVerticalAlignment(VerticalAlignmentValues value) { Alignment.Vertical = value; return(this); }
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> /// Align text vertically. /// </summary> /// <param name="VerticalAlignment">Specifies the vertical alignment. Default value is Bottom.</param> public void SetVerticalAlignment(VerticalAlignmentValues VerticalAlignment) { this.Alignment.Vertical = VerticalAlignment; }
/// <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))); }
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; }
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); }
public void SetAlignment(HorizontalAlignmentValues hAlign, VerticalAlignmentValues vAlign, bool wrapText = true) { AlignmentValue = new Alignment { Horizontal = hAlign, Vertical = vAlign, WrapText = wrapText }; }