Lennie Kuah

Lennie Kuah

  • NA
  • 27
  • 0

C#NET2008 DataGridView and Microsoft Excel 2003 display different DATE format

Oct 3 2011 12:43 AM

Hullo Friends,
I do need your help. Please help me.

I do encounter a very interesting problem regarding different Date Format
display on DataGridView and Excel Spreadsheet. Surprising not all the Date Format different
only a few.


THIS IS THE SAME SQL STRING USE TO FILL DATAGRIDVIEW
AND ALSO THE SAME USE TO FILL MICROSOFT EXCEL 2003

            string strsql = string.Empty;
            strsql += "Select IV.OrderId, ";
            strsql += " Convert(varchar(10), IV.OrderDate, 103) as [OrderDate], ";
            strsql += " Convert(varchar(10), IV.RequiredDate, 103) as [RequiredDate], ";          
            strsql += " From TestCustomers 
            strsql += " Where ( CustomerID = N'" + strCustID + "')";
            strsql += " ORDER By IV.OrderId, IV.OrderDate ";


Surprising a few of the data format is different between DataGridView and Excel Spreadsheet
DataGridView          = 02/06/1996
Excel Spreadsheet = 06/02/1996

THE OVER ALL CODING OF FILLING DATAGRIDVIEW AND EXCEL SPREADSHEET LISTED BELOW:

 private void FFillDataGridView()
   {
             sqlconn = new SqlConnection(connstr);
                sqlconn.Open();
                DA = new SqlDataAdapter(strsql, sqlconn);
                DS = new System.Data.DataSet("DS");
                DS.Clear();
                DA.Fill(DS,"Invoice");

                //---clear DataGridView Prior to filling it ---
                this.dataGridView1.Columns.Clear();
                this.dataGridView1.DataSource = null;

                // --- Set DataGridView DataSource ---
                this.dataGridView1.AutoGenerateColumns = false;
                this.dataGridView1.AutoSize = false;
                this.dataGridView1.DataSource = DS.Tables["Invoice"];

                // --- format DataGridView Column ---
                FFormatDataGridViewCcolumn();

                this.dataGridView1.ClearSelection();
                this.dataGridView1.MultiSelect = false;
                this.dataGridView1.ReadOnly = true;
                sqlconn.Close();

}

 private void FFormatDataGridViewCcolumn()
        {  // --- format datagridview column ---

            try
            {   //format Column Header
                this.dataGridView1.ColumnHeadersDefaultCellStyle.Font = new System.Drawing.Font(dataGridView1.Font, FontStyle.Bold);

                // --- OrderID Column ---
                DataGridViewColumn colOrderID = new DataGridViewTextBoxColumn();
                colOrderID.DataPropertyName = "OrderID";
                colOrderID.HeaderText = "Order ID";
                colOrderID.Width = 90;
                dataGridView1.Columns.Add(colOrderID);

                // --- Order Date Column ---
                DataGridViewColumn colOrderDate = new DataGridViewTextBoxColumn();
                colOrderDate.DataPropertyName = "OrderDate";
                colOrderDate.HeaderText = "Order    Date";
                colOrderDate.Width = 110;
                dataGridView1.Columns.Add(colOrderDate);

                // --- Required Data Column ---
                DataGridViewColumn colReqDate = new DataGridViewTextBoxColumn();
                colReqDate.DataPropertyName = "RequiredDate";
                colReqDate.HeaderText = "Required    Date";
                colReqDate.Width = 110;
                dataGridView1.Columns.Add(colReqDate);          
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.Message);
            }
        }

-----------------------------------------------
 private void FCreateExcelFile()
 {

            string strsql = string.Empty;
            strsql += "Select IV.OrderId, ";
            strsql += " Convert(varchar(10), IV.OrderDate, 103) as [OrderDate], ";
            strsql += " Convert(varchar(10), IV.RequiredDate, 103) as [RequiredDate], ";          
            strsql += " From TestCustomers 
            strsql += " Where ( CustomerID = N'" + strCustID + "')";
            strsql += " ORDER By IV.OrderId, IV.OrderDate ";

             // --- Instantiate EXCEL Object ---
              Microsoft.Office.Interop.Excel.Application xlExcel =  null;
              Microsoft.Office.Interop.Excel.Workbook xlWrkBook = null;
              Microsoft.Office.Interop.Excel.Worksheet xlWrkSheet;
              Microsoft.Office.Interop.Excel.Range xlRange;
              Microsoft.Office.Interop.Excel.Range xlWrkSheet_Range;
               
            object OMissing = System.Reflection.Missing.Value;   

                 
            // --- using Object DataReader ---
            sqlconn = new SqlConnection(connstr);
            sqlconn.Open();
            sqlcmd = new SqlCommand(strsql, sqlconn);
            DR = sqlcmd.ExecuteReader();

            xlExcel = new Microsoft.Office.Interop.Excel.Application();
            xlWrkBook = xlExcel.Workbooks.Add(OMissing);
            xlWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWrkBook.Worksheets.get_Item(1);
            xlExcel.Visible = true;


   introw += 2;  // = 7
   int intcol = 0;    //column count
   int intField = 0;  // field count
   int intRecCnt = DR.FieldCount;
   int intAlign = DR.FieldCount;

 for (intcol = 1; intcol <= intRecCnt; intcol++)
    {    //  --- set Excel Column Width and  Date Format ---
      switch (intcol)
        {
         case 1:  //OrderID
           ((Range)xlWrkSheet.Cells[introw, intcol]).EntireColumn.ColumnWidth = 13;
           break;

          case 2:  //OrderDate
           ((Range)xlWrkSheet.Cells[introw, intcol]).EntireColumn.ColumnWidth = 13;
           ((Range)xlWrkSheet.Cells[introw, intcol]).EntireColumn.NumberFormat = "DD/MM/YYYY";
           break;               

         case 3:  //RequiredDate
           ((Range)xlWrkSheet.Cells[introw, intcol]).EntireColumn.ColumnWidth = 13;
           ((Range)xlWrkSheet.Cells[introw, intcol]).EntireColumn.NumberFormat = "DD/MM/YYYY";
            break;

       } //endswitch
     } //end for


Thank you very much for helping me. You are wonderful, Awesome and Helpful.


Cheers,
Lennie Kuah


Answers (2)