c# exporting datetime from datagridview to excel greek regional setting

My issue is that, I have the mysql database and when i insert data:

string date = dateTimePicker1.Value.ToString("yyyy:MM:dd");

MySqlCommand cmd = new MySqlCommand ("INSERT INTO EXPENSES (ID, E_DATE, AMMOUNT, PERSON, COMMENTS, T_ID) VALUES (@id, @date,@ammount, @person, @comments, @tid)",myMySqlConnection);
                    cmd.Parameters.AddWithValue("@id", id);
                    cmd.Parameters.AddWithValue("@date", date);
                    cmd.Parameters.AddWithValue("@ammount", ammount);
                    cmd.Parameters.AddWithValue("@person", person);
                    cmd.Parameters.AddWithValue("@comments", comments);
                    cmd.Parameters.AddWithValue("@tid", tid);
                    cmd.ExecuteNonQuery();

when i view them in datagridview:

string test = startdate1.ToString("yyyy:MM:dd");
        string test2 = enddate1.ToString("yyyy:MM:dd");
        DataTable table1 = new DataTable();
        cmd1 = new MySqlCommand("SELECT E_DATE, AMMOUNT, PERSON, COMMENTS, T_ID FROM EXPENSES WHERE DATE (E_DATE) BETWEEN '" + test + "' AND '" + test2 + "' AND ID =@userid ORDER BY E_DATE ASC", myMySqlConnection);            
        cmd1.Parameters.AddWithValue("@userid", id);
        da1.SelectCommand = cmd1;
        da1.Fill(table1);
        BindingSource bSource1 = new BindingSource();
        bSource1.DataSource = table1;
        dataGridView1.DataSource = bSource1;

now i want to export them in excel:

Excel_12.Application oExcel_12 = null;
            Excel_12.Workbook oBook = null;
            Excel_12.Sheets oSheetsColl = null;collection
            Excel_12.Worksheet oSheet = null;
            Excel_12.Range oRange = null;
            Object oMissing = System.Reflection.Missing.Value;
            oExcel_12 = new Excel_12.Application();
            oExcel_12.Visible = true;
            oExcel_12.UserControl = true;
            System.Globalization.CultureInfo ci = System.Globalization.CultureInfo.CurrentCulture;
            oBook = oExcel_12.Workbooks.Add(oMissing);
            oSheetsColl = oExcel_12.Worksheets;
            oSheet = (Excel_12.Worksheet)oSheetsColl.get_Item("Sheet1");
            // Export titles
            for (int j = 0; j < myDataGridView.Columns.Count; j++)
            {
                oRange = (Excel_12.Range)oSheet.Cells[4, j + 1];
                oRange.Value2 = myDataGridView.Columns[j].HeaderText;
                oRange.Cells[1].ColumnWidth = 13;

            }
            // Export data
            for (int i = 0; i < myDataGridView.Rows.Count; i++)
            {
                for (int j = 1; j < myDataGridView.Columns.Count; j++)
                {
                    oRange = (Excel_12.Range)oSheet.Cells[i + 5, j + 1];
                    oRange.Value2 = myDataGridView[j, i].Value;
                }
            }//This is for everything except the dates

            for (int i = 0; i < myDataGridView.Rows.Count; i++)
            {
                for (int j = 0; j < 1; j++)
                {

                    oRange = (Excel_12.Range)oSheet.Cells[i + 5, j + 1];
                    oRange.Cells[1].NumberFormat = "dd/MMM/yyyy;@";
                    DateTime test = Convert.ToDateTime( myDataGridView[j, i].ToString(),ci);
                    oRange.Value2 = test; //test.Remove(test.Length - 11);
                 //   oRange.NumberFormat = "dd-MM-yyyy";
                }
            }//This is for the dates

            //oBook.Close(false, oMissing, oMissing);
            oBook = null;
            //oExcel_12.Quit();
            oExcel_12 = null;
            // Collect garbage.
            GC.Collect();

The problem is that, when the Regional settings are set to GREEK, Excel doesn't recognize the number format for the dates. If the Regional Settings are set to English -US there is no problem with the dates. How can i make it recognize the date without having to worry about the computer's Regional Settings.Any help would be truly appreciated since these Greek are driving me crazy. Best Regards George Georgiou

Answers


I think you can get round this by:

oRange = (Excel_12.Range)oSheet.Cells[i + 5, j + 1];
oRange.Cells[1].NumberFormat = "dd/MMM/yyyy;@";
DateTime test = Convert.ToDateTime( myDataGridView[j, i].ToString(),ci);
oRange.Value2 = test.ToOADate();

System.Globalization.CultureInfo ci = System.Globalization.CultureInfo.CurrentCulture;

this will return that the current culture is "el-GR"

and then:

string test = (Convert.ToDateTime(myDataGridView[j, i].Value).ToShortDateString());
                    DateTime asd = Convert.ToDateTime (test,ci);
                    oRange.NumberFormat = "dd-MMM-yyyy";
                    oRange.Value2 = asd;

we get the value and give it as a string and then convert it to datetime based on the culture info (which is el-GR) and give a date value to excel and format it the way you want it


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

    ExcelApp.Application.Workbooks.Add(Type.Missing);

    // Change properties of the Workbook 
    ExcelApp.Columns.ColumnWidth = 20;

    // Storing header part in Excel
    for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
    {
        ExcelApp.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
    }

    // Storing Each row and column value to excel sheet
    for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
    {
        for (int j = 0; j < dataGridView1.Columns.Count; j++)
        {
            ExcelApp.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();


        }
    }

    ExcelApp.ActiveWorkbook.SaveCopyAs("F:\\Exportdata\\" + "DealEntry.xls");
    ExcelApp.ActiveWorkbook.Saved = true;
    MessageBox.Show("It's Saved on Path F:\\Exportdata\\DealEntry.Xls");
    ExcelApp.Quit();
    ExcelApp.Visible = true;
    string workbookPath = "F:\\Exportdata\\" + "DealEntry.xls";  // Add your own path here
    Microsoft.Office.Interop.Excel.Workbook excelWorkbook = ExcelApp.Workbooks.Open(workbookPath, 0,
        false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,
        false, 0, true, false, false);

}
catch { }

Need Your Help

TDD: Stub, Mock, or None of the Above

c# tdd mocking stub

I'm trying to learn TDD by applying it to a simple project of mine. Some details (and an earlier question) are here:

How to share Internet connection on Mac OS X to Virtualbox vm's using Host-only

macos networking virtualbox bridge

In one line: is the following possible: AirPort &lt;- Mac OS X bridge -> vbox-Host-only -> VM's