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