How to apply number formatting to an entire column in Excel?

24 Jun 20264 minutes to read

The following code examples demonstrate applying number formatting to an entire column in Excel 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(Path.GetFullPath(@"Data/Input.xlsx"));
    IWorksheet sheet = workbook.Worksheets[0];

    //Case 1: Apply direct number format (zero-based index)
    sheet.Columns[0].NumberFormat = "yyyy-mm-dd"; //Column A
    sheet.Columns[3].NumberFormat = "$#,##0.00"; //Column D
    sheet.Columns[4].NumberFormat = "0.00%"; //Column E

    //Case 2: Apply style-based format (one-based index)
    IStyle style = workbook.Styles.Add("DecimalStyle");
    style.NumberFormat = "0.00";
    sheet.SetDefaultColumnStyle(3, style); //Column C 

    //Saving the workbook
    workbook.SaveAs(Path.GetFullPath(@"Output/Output.xlsx"));
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("Input.xlsx");
    IWorksheet sheet = workbook.Worksheets[0];

    //Case 1: Apply direct number format (zero-based index)
    sheet.Columns[0].NumberFormat = "yyyy-mm-dd"; //Column A
    sheet.Columns[3].NumberFormat = "$#,##0.00"; //Column D
    sheet.Columns[4].NumberFormat = "0.00%"; //Column E

    //Case 2: Apply style-based format (one-based index)
    IStyle style = workbook.Styles.Add("DecimalStyle");
    style.NumberFormat = "0.00";
    sheet.SetDefaultColumnStyle(3, style); //Column C 

    //Saving the workbook
    workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Open("Input.xlsx")
    Dim sheet As IWorksheet = workbook.Worksheets(0)

    'Case 1: Apply direct number format (zero-based index)
    sheet.Columns(0).NumberFormat = "yyyy-mm-dd" 'Column A
    sheet.Columns(3).NumberFormat = "$#,##0.00" 'Column D
    sheet.Columns(4).NumberFormat = "0.00%" 'Column E

    'Case 2: Apply style-based format (one-based index)
    Dim style As IStyle = workbook.Styles.Add("DecimalStyle")
    style.NumberFormat = "0.00"
    sheet.SetDefaultColumnStyle(3, style) 'Column C

    'Save the workbook
    workbook.SaveAs("Output.xlsx")
End Using

A complete working example that shows how to apply number formatting to an entire column in Excel using C# is present on this GitHub page.