C# - Format Excel 2016 Cells as Date Inorder to Use Date Filter

Question

Using c# I need to change a row of cells in Excel 2016 Worksheet to date format so that when the column is filtered the dates in the column are in cronological order. I have a SQL table where the date field has a type of date. When I export this field to Excel the type changes to text and shows as yyyy-mm-dd, even though the cells were preformated as date. I have some code where I iterate through all cells in the column and change the format of the cell to the custom format "dd-mmm-yy" (see below). After the code runs if I click on a cell the format of the cell shows as custom with a format of "dd-mmm-yy" but the actual value of the cell still shows as "yyyy-mm-dd" format, not as 03-Feb-22 format. When I filter this column the rows in the dropbox are treated as text not as a cronological date. Can you please let me know what the code would be to convert the cells to date and show as "dd-mmm-yy"? Thank you.

foreach (Excel.Range item in col.cells)
{
item.NumberFormat ="dd-mmm-yy"
}

Answer 1

Assuming you are saving the book when you're done, the code you posted should set the format display to the value you expect. Are you not seeing it set the formatting correctly after saving the file? Did you confirm the cells you are enumerating are the correct cells?

Answer 2

If using .xlsx, there is a free NuGet package SpreadSheetLight.

Here is a very simple example

public static void SimpleDateFormattingAndFiltering(string fileName)  
{  
    // create new document, for an existing see the docs  
    using SLDocument document = new();  
  
    SLStyle dateStyle = document.CreateStyle();  
    dateStyle.FormatCode = "dd-mmm-yy";  
      
    Dictionary<string, DateTime> dictDates = new()  
    {  
        { "B1", new DateTime(2022, 1, 1) },  
        { "B2", new DateTime(2022, 1, 2) },  
        { "B3", new DateTime(2022, 1, 3) },  
        { "B4", new DateTime(2022, 1, 4) }  
    };  
  
    foreach (var dateItem in dictDates)  
    {  
        if (document.SetCellValue(dateItem.Key, dateItem.Value))  
        {  
            document.SetCellStyle(dateItem.Key, dateStyle);  
            document.SetColumnWidth(dateItem.Key, 12);  
        }  
  
    }  
  
    document.Filter("B1", "B3");  
    document.SaveAs(fileName);  
  
}