Hullo Friends,I do need your help. Please help me.
I do encounter a very interesting problem regarding different Date Formatdisplay 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 SpreadsheetDataGridView = 02/06/1996Excel 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() {
// --- 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