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