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.

What is the correct format? – Austinh100 Jul 01 '14 at 19:36 — Austinh100, Jul 01 '14 at 19:36
The Custom "yyyy-mmm" would correct format – Lalo Jul 01 '14 at 19:47 — Lalo, Jul 01 '14 at 19:47

score 1 · Answer 1 · edited May 23 '17 at 11:43

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";

edited May 23 '17 at 11:43

Community

1
1

answered Jul 01 '14 at 19:44

Alexander Bell

7,842
3
26
42

Excel cell format by ACE.OLEDB

1 Answers1