private static void CreateCellsFormatting(Workbook workbook)
{
// Define a style object adding a new style to the collection list.
Style stl0 = workbook.CreateStyle();
// Set a custom shading color of the cells.
stl0.ForegroundColor = Color.FromArgb(155, 204, 255);
stl0.Pattern = BackgroundType.Solid;
stl0.Font.Name = "Trebuchet MS";
stl0.Font.Size = 18;
stl0.Font.Color = Color.Maroon;
stl0.Font.IsBold = true;
stl0.Font.IsItalic = true;
// Define a style flag struct.
StyleFlag flag = new StyleFlag();
flag.CellShading = true;
flag.FontName = true;
flag.FontSize = true;
flag.FontColor = true;
flag.FontBold = true;
flag.FontItalic = true;
// Get the first row in the first worksheet.
Row row = workbook.Worksheets[0].Cells.Rows[0];
// Apply the style to it.
row.ApplyStyle(stl0, flag);
// Obtain the cells of the first worksheet.
Cells cells = workbook.Worksheets[0].Cells;
// Set the height of the first row.
cells.SetRowHeight(0, 30);
// Define a style object adding a new style to the collection list.
Style stl1 = workbook.CreateStyle();
// Set the rotation angle of the text.
stl1.RotationAngle = 45;
// Set the custom fill color of the cells.
stl1.ForegroundColor = Color.FromArgb(0, 51, 105);
stl1.Pattern = BackgroundType.Solid;
stl1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
stl1.Borders[BorderType.LeftBorder].Color = Color.White;
stl1.HorizontalAlignment = TextAlignmentType.Center;
stl1.VerticalAlignment = TextAlignmentType.Center;
stl1.Font.Name = "Times New Roman";
stl1.Font.Size = 10;
stl1.Font.Color = Color.White;
stl1.Font.IsBold = true;
// Set a style flag struct.
flag = new StyleFlag();
flag.LeftBorder = true;
flag.Rotation = true;
flag.CellShading = true;
flag.HorizontalAlignment = true;
flag.VerticalAlignment = true;
flag.FontName = true;
flag.FontSize = true;
flag.FontColor = true;
flag.FontBold = true;
row = workbook.Worksheets[0].Cells.Rows[1];
// Apply the style to it.
row.ApplyStyle(stl1, flag);
// Set the height of the second row.
cells.SetRowHeight(1, 48);
// Define a style object adding a new style to the collection list.
Style stl2 = workbook.CreateStyle();
// Set the custom cell shading color.
stl2.ForegroundColor = Color.FromArgb(155, 204, 255);
stl2.Pattern = BackgroundType.Solid;
stl2.Font.Name = "Trebuchet MS";
stl2.Font.Color = Color.Maroon;
stl2.Font.Size = 10;
flag = new StyleFlag();
flag.CellShading = true;
flag.FontName = true;
flag.FontColor = true;
flag.FontSize = true;
// Get the first column in the first worksheet.
Column col = workbook.Worksheets[0].Cells.Columns[0];
// Apply the style to it.
col.ApplyStyle(stl2, flag);
// Define a style object adding a new style to the collection list.
Style stl3 = workbook.CreateStyle();
// Set the custom cell filling color.
stl3.ForegroundColor = Color.FromArgb(124, 199, 72);
stl3.Pattern = BackgroundType.Solid;
cells["A2"].SetStyle(stl3);
// Define a style object adding a new style to the collection list.
Style stl4 = workbook.CreateStyle();
// Set the custom font text color.
stl4.Font.Color = Color.FromArgb(0, 51, 105);
stl4.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
stl4.Borders[BorderType.BottomBorder].Color = Color.FromArgb(124, 199, 72);
stl4.ForegroundColor = Color.White;
stl4.Pattern = BackgroundType.Solid;
// Set custom number format.
stl4.Custom = "$#,##0.0";
// Set a style flag struct.
flag = new StyleFlag();
flag.FontColor = true;
flag.CellShading = true;
flag.NumberFormat = true;
flag.BottomBorder = true;
// Define a style object adding a new style to the collection list.
Style stl5 = workbook.CreateStyle();
stl5.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
stl5.Borders[BorderType.BottomBorder].Color = Color.FromArgb(124, 199, 72);
stl5.ForegroundColor = Color.FromArgb(250, 250, 200);
stl5.Pattern = BackgroundType.Solid;
// Set custom number format.
stl5.Custom = "$#,##0.0";
stl5.Font.Color = Color.Maroon;
// Create a named range of cells (B3:M25)in the first worksheet.
Range range = workbook.Worksheets[0].Cells.CreateRange("B3", "M25");
// Name the range.
range.Name = "MyRange";
// Apply the style to cells in the named range.
range.ApplyStyle(stl4, flag);
// Apply different style to alternative rows in the range.
for (int i = 0; i <= 22; i++)
{
for (int j = 0; j < 12; j++)
{
if (i % 2 == 0)
{
range[i, j].SetStyle(stl5);
}
}
}
// Define a style object adding a new style to the collection list.
Style stl6 = workbook.CreateStyle();
// Set the custom fill color of the cells.
stl6.ForegroundColor = Color.FromArgb(0, 51, 105);
stl6.Pattern = BackgroundType.Solid;
stl6.Font.Name = "Arial";
stl6.Font.Size = 10;
stl6.Font.Color = Color.White;
stl6.Font.IsBold = true;
// Set the custom number format.
stl6.Custom = "$#,##0.0";
// Set the style flag struct.
flag = new StyleFlag();
flag.CellShading = true;
flag.FontName = true;
flag.FontSize = true;
flag.FontColor = true;
flag.FontBold = true;
flag.NumberFormat = true;
// Get the 26th row in the first worksheet which produces totals.
row = workbook.Worksheets[0].Cells.Rows[25];
// Apply the style to it.
row.ApplyStyle(stl6, flag);
// Now apply this style to those cells (N3:N25) which has productwise sales totals.
for (int i = 2; i < 25; i++)
{
cells[i, 13].SetStyle(stl6);
}
// Set N column's width to fit the contents.
workbook.Worksheets[0].Cells.SetColumnWidth(13, 9.33);
}
}