export datetime to excel

Question

I want to export data to a excel file in a windows form application but when I try to export dates the format changes. I want to use the format dd-MM-yyyy

            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

        if (xlApp == null)
        {
            MessageBox.Show("Excel is not properly installed!!");
            return;
        }


        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);



        xlWorkSheet.Cells[1, 1].NumberFormat = "dd/mm/yyyy";
        xlWorkSheet.Cells[1, 1] = "19-02-2015";
        xlWorkSheet.Cells[2, 1] = "02-03-2015";
        xlWorkSheet.Cells[2, 1].NumberFormat = "dd/mm/yyyy";

        xlWorkBook.SaveAs("C:\\Users\\user\\Desktop\\Data.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);
    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

the result in the excel file is: 19-2-2015 as text 02/03/2015 as datetime I use excel 2013

so how to export dates where the day is above 12 to excel and give them the format mm/dd/jjjj

it dident work for the first date the output was still the same and the second changed to 2/3/yyyy but the value behind it is 3-2-2015 so it switched the day and month correct but changed the year into yyyy edit: changed the format to mm/dd/jjjj now the second date is working but the first 1 still isnt — Ewout, Feb 19 '15 at 12:41
Probably because 19 is not a valid month. "mm/dd/jjjj" => "19-02-2015". While testing, use a "mmm" format to verify you are getting the month you want. Are you sure your "02/03/2015" is March 2nd and not Feb 3rd? Why don't you place the dates you want in the spreadsheet and then make your app open and read that file, to see how it should be stored? Does "19-02-2015" correspond to your local regional configuration (dd-mm-yyyy)? — Andrew, Feb 19 '15 at 12:51
Hi Can you please try these lines and let me know if this solves. I will add that as answer xlWorkSheet.Cells[1, 1].NumberFormat = "dd-mm-yyyy"; xlWorkSheet.Cells[2, 1].NumberFormat = "dd-mm-yyyy"; xlWorkSheet.Cells[1, 1].Value = "19-02-2015"; xlWorkSheet.Cells[2, 1].Value = "02-03-2015"; — kishore V M, Feb 19 '15 at 12:54
@Andrew my local cnfiguration is dd-mm-yyyy and when I change the month to ddd it show feb 3rd so could you give me some sample code for a spreadsheet kishoreVM the format dd-mm-yyyy does not work it will result that the year will be show as yyyy when it still has the value 2015 — Ewout, Feb 19 '15 at 13:10
Why do use jjjj in xlWorkSheet.Cells[2, 1].NumberFormat = "mm/dd/jjjj";? Why not use mm/dd/yyyy instead? — NoChance, Feb 19 '15 at 13:14
when I use yyyy the year has the value 2015 but it shows yyyy in the cell when I use jjjj it has the value and shows 2015 — Ewout, Feb 19 '15 at 13:16
I am not sure where you got the jjjj from. Maybe this link adds value in your case:http://stackoverflow.com/questions/894805/excel-number-format-what-is-409 — NoChance, Feb 19 '15 at 13:29
when I use yyyy then go in excel to format cells it gives me more format options jjjj is one of them — Ewout, Feb 19 '15 at 13:39

score 1 · Accepted Answer · answered Feb 19 '15 at 12:59

1

I suggest you to use "Text" as cell type. It will get rid of automatic format changes for dates. You can use the following code:

xlWorkSheet.Cells[1, 1].NumberFormat = "@";

answered Feb 19 '15 at 12:59

Victor Sharovatov

605
9
19

Dident work the result was 42038 for the second date and the first date dident changed – Ewout Feb 19 '15 at 13:04
You need to use this code for every cell that you want to place date in without format changes. Or you can set text as column type for all cells in the spreadsheet: Range cells = xlWorkSheet.Cells; cells.NumberFormat = "@"; – Victor Sharovatov Feb 19 '15 at 13:10
still the same result – Ewout Feb 19 '15 at 13:16
Please add the code from my comment above before the following line - xlWorkSheet.Cells[1, 1] = "19-02-2015"; I believe it should work – Victor Sharovatov Feb 19 '15 at 13:30
These two lines Range cells = xlWorkSheet.Cells; cells.NumberFormat = "@"; should go before the following line from your code xlWorkSheet.Cells[1, 1] = "19-02-2015"; – Victor Sharovatov Feb 19 '15 at 13:39
changed to Excel.Range cells = xlWorkSheet.Cells; cells.NumberFormat = "@"; xlWorkSheet.Cells[1, 1].NumberFormat = "dd/mm/jjjj"; xlWorkSheet.Cells[1, 1] = "19-02-2015"; works now Thanks – Ewout Feb 19 '15 at 13:44

export datetime to excel

1 Answers1