How to apply styles to the entire worksheet in Excel?

24 Jun 20264 minutes to read

The following examples show how to apply font attributes (name and size) and fill color to an entire worksheet using C# (cross-platform and Windows-specific) and VB.NET.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("../../../Data/Input.xlsx", ExcelOpenType.Automatic);
    IWorksheet worksheet = workbook.Worksheets[0];

    //Define new styles to apply in rows and columns
    IStyle columnStyle = workbook.Styles.Add("ColumnStyle");
    columnStyle.Font.FontName = "Times New Roman";
    columnStyle.Font.Size = 10;
    columnStyle.Color = Color.Pink;

    worksheet.SetDefaultColumnStyle(1, 16384, columnStyle);

    //Save the Excel document
    workbook.SaveAs("../../../Output/FontStyle.xlsx");
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("../../Data/Input.xlsx", ExcelOpenType.Automatic);
    IWorksheet worksheet = workbook.Worksheets[0];

    //Define new styles to apply in rows and columns
    IStyle columnStyle = workbook.Styles.Add("ColumnStyle");
    columnStyle.Font.FontName = "Times New Roman";
    columnStyle.Font.Size = 10;
    columnStyle.Color = Color.Pink;

    worksheet.SetDefaultColumnStyle(1, 16384, columnStyle);

    //Save the Excel document
    workbook.SaveAs("../../Output/FontStyle.xlsx");

}
Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx

    Dim workbook As IWorkbook = application.Workbooks.Open("../../Data/Input.xlsx", ExcelOpenType.Automatic)
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    'Define new styles to apply in rows and columns
    Dim columnStyle As IStyle = workbook.Styles.Add("ColumnStyle")
    columnStyle.Font.FontName = "Times New Roman"
    columnStyle.Font.Size = 10
    columnStyle.Color = Color.Pink

    worksheet.SetDefaultColumnStyle(1, 16384, columnStyle)

    'Save the Excel document
    workbook.SaveAs("../../Output/FontStyle.xlsx")
End Using

NOTE

  • Applying a default style to cells replaces any existing styles. This is standard Excel behavior.
  • To add new styling without removing existing formats, set specific properties on targeted ranges.

The following code snippet shows how to apply a new style without affecting existing styles:

worksheet.Range["A1:F13"].CellStyle.Font.FontName = "Times New Roman";
 worksheet.Range["A1:F13"].CellStyle.Font.Size = 10;
 worksheet.Range["A1:F13"].CellStyle.Color = Color.Lavender;
worksheet.Range["A1:F13"].CellStyle.Font.FontName = "Times New Roman";
 worksheet.Range["A1:F13"].CellStyle.Font.Size = 10;
 worksheet.Range["A1:F13"].CellStyle.Color = Color.Lavender;
worksheet.Range("A1:F13").CellStyle.Font.FontName = "Times New Roman"
worksheet.Range("A1:F13").CellStyle.Font.Size = 10
worksheet.Range("A1:F13").CellStyle.Color = Color.Lavender