Excel cell format by ACE.OLEDB

Question

I have the following code which I update a cell in an Excel file, but does not follow the format I have in my cell:

string cad = @" Provider=Microsoft.ACE.OLEDB.12.0;" +
             @"Data Source=" + 
             Server.MapPath("~/SVG/" + filenameexport + ".xlsx;") + 
             @"Extended Properties=" + '"' + "Excel 12.0 Xml;HDR=NO" + '"';
try
{
    using (OleDbConnection con = new OleDbConnection(cad))
    {
        con.Open();
        string query = @"UPDATE [Cost Planning$] SET F9= ? WHERE F1 = 'Category'";
        using (OleDbCommand cmd = new OleDbCommand(query, con))
        {
            cmd.Parameters.AddWithValue("@param1", Convert.ToDateTime("2014/07/01"));
            cmd.ExecuteNonQuery();
        }
        con.Close();
    }
}

As I can make it respect me the cell format?

This code line, where I add in my code?

((Excel.Range)worksheetobject.Range("A1")).EntireColumn.NumberFormat = "MM/DD/YYYY";

Because the link that send is when generate Excel file of 0, and my Excel file this created.

Answer 1

You can format Excel column using NumberFormat property (as discussed in How to make correct date format when writing data to Excel ). For example, to set it to date format like the following:

((Excel.Range)worksheetobject.Range("A1")).EntireColumn.NumberFormat = "MM/DD/YYYY";