public void SetCellBackgroundColor(Int32 rowIndex, Int32 columnIndex, String bgColor) { // ищем ячейку и её старый формат Row row = GetRow((UInt32)rowIndex); Cell cell = GetCell(row, (UInt32)columnIndex); CellFormats cellFormats = stylesheet.GetFirstChild <CellFormats>(); CellFormat cellFormat = cellFormats.Elements <CellFormat>().ElementAt <CellFormat>((Int32)cell.StyleIndex.Value); Int32 newCellFormatIndex = cellFormats.Elements <CellFormat>().Count <CellFormat>(); CellFormat newCellFormat = (CellFormat)cellFormat.Clone(); { // новый background ----------------------------------------- проверить существующий to do ... Fills fills = stylesheet.GetFirstChild <Fills>(); Int32 fillIndex = fills.Elements <Fill>().Count <Fill>(); fills.Append( new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor() { Rgb = "ff" + bgColor } } } ); newCellFormat.FillId = (UInt32)fillIndex; } cellFormats.Append(newCellFormat); cell.StyleIndex = (UInt32)newCellFormatIndex; }
/// <summary> /// Adds the basic styles to the workbook /// </summary> public static bool AddBasicStyles(SpreadsheetDocument spreadsheet) { Stylesheet stylesheet = spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet; // Numbering formats stylesheet.InsertAt <NumberingFormats>(new NumberingFormats(), 0); // Currency stylesheet.GetFirstChild <NumberingFormats>().InsertAt <NumberingFormat>(new NumberingFormat { NumberFormatId = 164, FormatCode = "#,##0.00" + "\\ \"" + CultureInfo.CurrentUICulture.NumberFormat.CurrencySymbol + "\"" }, 0); // Fonts (x:fonts) stylesheet.InsertAt <Fonts>(new Fonts(), 1); stylesheet.GetFirstChild <Fonts>().InsertAt <Font>(new Font { FontSize = new FontSize() { Val = 11 }, FontName = new FontName() { Val = "Calibri" } }, 0); // Fills (x:fills) stylesheet.InsertAt <Fills>(new Fills(), 2); stylesheet.GetFirstChild <Fills>().InsertAt <Fill>(new Fill { PatternFill = new PatternFill { PatternType = new EnumValue <PatternValues> { Value = PatternValues.None } } }, 0); stylesheet.GetFirstChild <Fills>().InsertAt <Fill>(new Fill { PatternFill = new PatternFill() { PatternType = new EnumValue <PatternValues> { Value = PatternValues.Gray125 } } }, 1); // Borders (x:borders) stylesheet.InsertAt <Borders>(new Borders(), 3); stylesheet.GetFirstChild <Borders>().InsertAt <Border>(new Border { LeftBorder = new LeftBorder(), RightBorder = new RightBorder(), TopBorder = new TopBorder(), BottomBorder = new BottomBorder(), DiagonalBorder = new DiagonalBorder() }, 0); // Cell style formats (x:CellStyleXfs) stylesheet.InsertAt <CellStyleFormats>(new CellStyleFormats(), 4); stylesheet.GetFirstChild <CellStyleFormats>().InsertAt <CellFormat>( new CellFormat() { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0 }, 0); // Cell formats (x:CellXfs) stylesheet.InsertAt <CellFormats>(new CellFormats(), 5); // General text stylesheet.GetFirstChild <CellFormats>().InsertAt <CellFormat>( new CellFormat() { FormatId = 0, NumberFormatId = 0 }, 0); // Date stylesheet.GetFirstChild <CellFormats>().InsertAt <CellFormat>( new CellFormat() { ApplyNumberFormat = true, FormatId = 0, NumberFormatId = 22, FontId = 0, FillId = 0, BorderId = 0 }, 1); // Currency stylesheet.GetFirstChild <CellFormats>().InsertAt <CellFormat>( new CellFormat() { ApplyNumberFormat = true, FormatId = 0, NumberFormatId = 164, FontId = 0, FillId = 0, BorderId = 0 }, 2); // Percentage stylesheet.GetFirstChild <CellFormats>().InsertAt <CellFormat>( new CellFormat() { ApplyNumberFormat = true, FormatId = 0, NumberFormatId = 10, FontId = 0, FillId = 0, BorderId = 0 }, 3); stylesheet.Save(); return(true); }
private Stylesheet CreatStylesheet() { var style = new Stylesheet(); #region Font Style style.InsertAt(new Fonts(), StyleConst.FontsConst.FONT); style.GetFirstChild<Fonts>() .InsertAt<Font>( new Font { FontSize = new FontSize {Val = 11}, Bold = new Bold {Val = true} }, StyleConst.FontsConst.FontStyle.FONT_BOLD); style.GetFirstChild<Fonts>().InsertAt<Font>(new Font { FontSize = new FontSize {Val = 11}, Bold = new Bold {Val = false} }, StyleConst.FontsConst.FontStyle.FONT_REGULAR); #endregion #region Fill Style style.InsertAt(new Fills(), StyleConst.FillConst.FILL); style.GetFirstChild<Fills>().InsertAt<Fill>( new Fill { PatternFill = new PatternFill { PatternType = new EnumValue<PatternValues> { Value = PatternValues.Gray125 }, } }, StyleConst.FillConst.FillStyle.REGULAR); #endregion #region Border Style style.InsertAt(new Borders(), 2/*StyleConst.BorderConst.BORDER*/); style.GetFirstChild<Borders>().InsertAt<Border>( new Border { LeftBorder = new LeftBorder() { Style = BorderStyleValues.Thick }, RightBorder = new RightBorder { Style = BorderStyleValues.Thick }, TopBorder = new TopBorder { Style = BorderStyleValues.Thick }, BottomBorder = new BottomBorder { Style = BorderStyleValues.Thick }, DiagonalBorder = new DiagonalBorder() }, StyleConst.BorderConst.Bordertyle.REGULAR); #endregion #region Cell Format style.InsertAt(new CellFormats(), StyleConst.CellFormat.CELL_FORMAT_STYLE); style.GetFirstChild<CellFormats>().InsertAt<CellFormat>( new CellFormat { FontId = StyleConst.FontsConst.FontStyle.FONT_BOLD, NumberFormatId = 0, FillId = 0, BorderId = 0 }, StyleConst.CellFormat.CellFormatStyle.CELL_HEADER); style.GetFirstChild<CellFormats>().InsertAt<CellFormat>( new CellFormat { FontId = StyleConst.FontsConst.FontStyle.FONT_REGULAR, NumberFormatId = 0, FillId = 0, BorderId = 0 }, StyleConst.CellFormat.CellFormatStyle.CELL_REGULAR); #endregion return style; }