How to apply TimePeriod conditional formatting in Excel using C#?

14 Jul 20254 minutes to read

You can apply TimePeriod-based conditional formatting in an Excel worksheet using the IConditionalFormats interface.

The following examples show how to highlight cells that match specific time periods in C# (cross-platform and Windows-specific) and VB.NET.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine()) 
{ 
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/Input.xlsx"), FileMode.Open, FileAccess.Read);
    IWorkbook workbook = application.Workbooks.Open(inputStream);
    IWorksheet worksheet = workbook.Worksheets[0];

    //Apply conditional format for specific time period
    IConditionalFormats conditionalFormats = worksheet.UsedRange.ConditionalFormats;
    IConditionalFormat conditionalFormat = conditionalFormats.AddCondition();

    //Set the format type to 'TimePeriod' to apply time-based conditional formatting
    conditionalFormat.FormatType = ExcelCFType.TimePeriod;
    conditionalFormat.TimePeriodType = CFTimePeriods.Today;

    //Set the background color of the matching cells 
    conditionalFormat.BackColor = ExcelKnownColors.Sky_blue;

    //Saving the workbook
    FileStream outputStream = new FileStream(Path.GetFullPath("Output/Output.xlsx"), FileMode.Create, FileAccess.Write);
    workbook.SaveAs(outputStream);

    //Dispose streams
    outputStream.Dispose();
    inputStream.Dispose();
}

C# [Windows-specific]

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

    //Apply conditional format for specific time period
    IConditionalFormats conditionalFormats = worksheet.UsedRange.ConditionalFormats;
    IConditionalFormat conditionalFormat = conditionalFormats.AddCondition();

    //Set the format type to 'TimePeriod' to apply time-based conditional formatting
    conditionalFormat.FormatType = ExcelCFType.TimePeriod;
    conditionalFormat.TimePeriodType = CFTimePeriods.Today;

    //Set the background color of the matching cells
    conditionalFormat.BackColor = ExcelKnownColors.Sky_blue;

    //Saving the workbook
    workbook.SaveAs("Output.xlsx");
}

VB.NET [Windows-specific]

Using excelEngine As ExcelEngine = New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Open("Input.xlsx") 
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    'Apply conditional format for specific time period
    Dim conditionalFormats As IConditionalFormats = worksheet.UsedRange.ConditionalFormats
    Dim conditionalFormat As IConditionalFormat = conditionalFormats.AddCondition()

    'Set the format type to 'TimePeriod' to apply time-based conditional formatting
    conditionalFormat.FormatType = ExcelCFType.TimePeriod
    conditionalFormat.TimePeriodType = CFTimePeriods.Today

    'Set the background color of the matching cells
    conditionalFormat.BackColor = ExcelKnownColors.Sky_blue

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

A complete working example to apply TimePeriod conditional formatting in an Excel is available on this GitHub page.