From DataBase to Excel : date format issue

Question

I'm currently trying to export some data from my database to an Excel file. Everything goes fine except for one column.

I've got two date fields in my targeted table (start and end date) which I want to display in my Excel sheet. The thing is, the end date is correctly displayed (dd/mm/yyyy) while my start date isn't (mm/dd/yyyy). Their definition are absolutely the same at the database level.

Here's how I'm building my DataTable :

    public System.Data.DataTable ExportLastChangesToExcel()
    {


        List<HD_DISCOUNTS> listDiscount = (from d in dbHosp.HD_DISCOUNTS where d.TO_EXTRACT == "N" orderby d.EXTRACT_TEXT descending select d).ToList();


        System.Data.DataTable table = new System.Data.DataTable();
        table.Columns.Add("Condition Type", typeof(string));
        table.Columns.Add("Sales organisation", typeof(string));
        table.Columns.Add("Distribution Channel", typeof(string));
        table.Columns.Add("Customer", typeof(string));
        table.Columns.Add("Customer Name", typeof(string));
        table.Columns.Add("Material", typeof(string));
        table.Columns.Add("Material name", typeof(string));
        table.Columns.Add("Amount", typeof(string));
        table.Columns.Add("Unit", typeof(string));
        table.Columns.Add("C..", typeof(string));
        table.Columns.Add("Valid From", typeof(string));
        table.Columns.Add("Valid To", typeof(string));
        table.Columns.Add("Valid Action", typeof(string));

        var allCust = (from v in dbCust.CUSTOMER_MASTER where v.INCLUDE_INTO_HD == "Y" select v).ToList();

        foreach (HD_DISCOUNTS disc in listDiscount)
        {
            string compareId = disc.ID_HOSP.ToString();
            CUSTOMER_MASTER cust = (from h in allCust where h.SOLDTOPARTY.TrimStart('0') == compareId select h).FirstOrDefault();
            DIM_PRODUCT prod = (from p in db.DIM_PRODUCT where p.P_KEY_AZ == disc.ID_PRODUCT select p).FirstOrDefault();
            DIM_PRODUCT_SAP sap = (from s in db.DIM_PRODUCT_SAP where s.P_KEY_AZ == prod.P_KEY_AZ && s.P_TYPE_SAP == "Domestic" select s).FirstOrDefault();

            table.Rows.Add("ZD22", "BE10", "1", cust.SOLDTOPARTY, cust.SOLDTOPARTY_DESC, sap.P_KEY_SAP.ToString(), prod.P_DESC_AZ, disc.DISCOUNT.ToString(), "%", "A", disc.START_DATE.Value.ToString("dd/MM/yyyy"), disc.END_DATE.Value.ToString("dd/MM/yyyy"), disc.EXTRACT_TEXT);

        }


        return table;

    }

I have debugged and start date should be well displayed. Here's the code I use to build my Excel :

    protected void btnExport_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel.Application excel;
        Microsoft.Office.Interop.Excel.Workbook worKbooK;
        Microsoft.Office.Interop.Excel.Worksheet worksheet;
        Microsoft.Office.Interop.Excel.Range celLrangE;

        try
        {
            excel = new Microsoft.Office.Interop.Excel.Application();
            excel.Visible = false;
            excel.DisplayAlerts = false;
            worKbooK = excel.Workbooks.Add(Type.Missing);

            worksheet = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet;
            worksheet.Name = "Sheet1";

            System.Data.DataTable tab = ExportLastChangesToExcel();
            Range excelRange = worksheet.get_Range("K1");

            excelRange.NumberFormat = "dd/mm/yyyy;@";
            int rowcount = 2;

            foreach (DataRow datarow in tab.Rows)
            {
                rowcount += 1;
                for (int i = 1; i <= tab.Columns.Count; i++)
                {

                    if (rowcount == 3)
                    {
                        worksheet.Cells[1, i] = tab.Columns[i - 1].ColumnName;
                        worksheet.Cells.Font.Color = System.Drawing.Color.Black;

                    }

                    worksheet.Cells[rowcount-1, i] = datarow[i - 1].ToString();
                    //worksheet.Cells[i, 11].NumberFormat = "@";

                    if (rowcount > 3)
                    {
                        if (i == tab.Columns.Count)
                        {
                            if (rowcount % 2 == 0)
                            {
                                celLrangE = worksheet.Range[worksheet.Cells[rowcount, 1], worksheet.Cells[rowcount, tab.Columns.Count]];


                            }

                        }
                    }

                }

            }
            celLrangE = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[rowcount, tab.Columns.Count]];
            celLrangE.EntireColumn.AutoFit();

            celLrangE = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[2, tab.Columns.Count]];

            worKbooK.SaveAs("C:\\Temp\\Hospital Discount\\Genpact\\GenpactExtract.xlsx");
            worKbooK.Close();
            excel.Quit();



        }
        catch (Exception ex)
        {

        }
        finally
        {
            worksheet = null;
            celLrangE = null;
            worKbooK = null;

            lblResultMessage.Text = "Extract for Genpact has been successfully generated";
            lblResultMessage.Visible = true;
        }

As the column K is the one containing the wrong dates, I tried to format this column to get my dates well formatted, but without success.

Any idea on what's possibly going wrong ?

Answer 1

I think the issue is you are leaving too much to Excel and need to be explicit in how you enter and render the data.

My recommendation:

1. Read the data from the database as a date, not a string
2. Enter it into Excel as a date, as Excel interprets dates
3. Format the cell the way you want to see it

Example:

Excel.Range r = worksheet.Cells[rowcount - 1, i];

if (datarow[i - 1] is DateTime)
{
    r.Value2 = ((DateTime)(datarow[i - 1])).ToOADate();
    r.NumberFormat = "dd/MM/yyyy";
}
else
{
    r.Value2 = datarow[i - 1]
}

Bear in mind you can skip the datatable all together and just use an OracleDataReader to read the data and then put it directly into Excel.

Further, if you have an ODBC connection, you can have Excel read it directly from Oracle and put it into a Table -- it will even handle the dates properly if you do it that way. You can still do this from C#, by the way, with a few lines of code, and you let Excel do the heavy lifting.