Click here to Skip to main content
15,919,178 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am exporting C# database table to excel. Exporting is ok. But what is the problem is
after exporting I checked that excel file. in that one date column is there. there date display with time like
21-07-2017 12:00:00 AM
All data in date column is displaying like that only.Why is coming like that and how to stop that.
my code is
private void btnexport_Click(object sender, EventArgs e)
        {
            saveFileDialog1.InitialDirectory = "c:";
            saveFileDialog1.Title = "Save AS Excel";
            saveFileDialog1.FileName = "";
            saveFileDialog1.Filter = "Excel File 2003|*.xls|Excel File 2007|*.xlsx";
            if (saveFileDialog1.ShowDialog() != DialogResult.Cancel)
            {
                Microsoft.Office.Interop.Excel.Application Excelapp = new Microsoft.Office.Interop.Excel.Application();
                Excelapp.Application.Workbooks.Add(Type.Missing);
                Excelapp.Columns.ColumnWidth = 20;
                for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
                {
                    Excelapp.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
                }
                for (int i = 0; i < dataGridView1.Rows.Count; 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(saveFileDialog1.FileName.ToString());
                Excelapp.ActiveWorkbook.Saved = true;
                Excelapp.Quit();


            }


What I have tried:

Igoogled but no suitable result found
Posted
Updated 22-Aug-17 23:16pm
v2
Comments
Richard MacCutchan 23-Aug-17 3:33am    
Because you are saving all the items as strings.
vijay_bale 23-Aug-17 4:23am    
so what to do now with that date?
vijay_bale 23-Aug-17 4:23am    
how to export that date as a date ?
Richard MacCutchan 23-Aug-17 4:45am    
Convert it into proper Excel format.

1 solution

use this code

private void ExportToExcel()
       {
           try
           {
               Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
               Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
               Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
               app.Visible = true;
               worksheet = workbook.Sheets["Sheet1"];
               worksheet = workbook.ActiveSheet;
               worksheet.Name = "Records";

               try
               {
                   for (int i = 0; i < dgvCus.Columns.Count; i++)
                   {
                       worksheet.Cells[1, i + 1] = dgvCus.Columns[i].HeaderText;
                   }
                   for (int i = 0; i < dgvCus.Rows.Count; i++)
                   {
                       for (int j = 0; j < dgvCus.Columns.Count; j++)
                       {
                           if (dgvCus.Rows[i].Cells[j].Value != null)
                           {
                               worksheet.Cells[i + 2, j + 1] = dgvCus.Rows[i].Cells[j].Value.ToString();
                           }
                           else
                           {
                               worksheet.Cells[i + 2, j + 1] = "";
                           }
                       }
                   }

                   //Getting the location and file name of the excel to save from user.
                   SaveFileDialog saveDialog = new SaveFileDialog();
                   saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
                   saveDialog.FilterIndex = 2;

                   if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                   {
                       workbook.SaveAs(saveDialog.FileName);
                       MessageBox.Show("Export Successful", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                   }
               }
               catch (System.Exception ex)
               {
                   MessageBox.Show(ex.Message);
               }

               finally
               {
                   app.Quit();
                   workbook = null;
                   worksheet = null;
               }
           }
           catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); }

       }
 
Share this answer
 
Comments
vijay_bale 23-Aug-17 8:09am    
same is coming like before 31-07-2017 12:00:00 AM

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900