Hi All,
I am creating a tool to execute a SQL select in a DB which is specified the config file and it will show the result in a grid view and on clicking the export button it will export it as .xlsx file.
I have used the below code to execute the script and show it on the gridview:
- cnn.Open();
- foreach (FileInfo file in sqlfiles)
- {
- string script = file.OpenText().ReadToEnd();
- using (cm = cnn.CreateCommand())
- cm.CommandText = script;
- SqlDataReader reader = cm.ExecuteReader();
- var dt = new DataTable();
- dt.Load(reader);
- DataGV.AutoGenerateColumns = true;
- DataGV.DataSource = dt;
- DataGV.Refresh();
- DataGV.ReadOnly = true;
- }
- then I exported it using the below code :
- DataTable dt = new DataTable();
-
- foreach (DataGridViewColumn column in DataGV.Columns)
- {
- dt.Columns.Add(column.HeaderText, column.ValueType);
- }
-
- foreach (DataGridViewRow row in DataGV.Rows)
- {
- dt.Rows.Add();
- foreach (DataGridViewCell cell in row.Cells)
- {
- dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
- }
- }
- SaveFileDialog sfd = new SaveFileDialog();
- sfd.Filter = "Excel Documents (*.xlsx)|*.xlsx";
- sfd.FileName = "export.xlsx";
- if (sfd.ShowDialog() == DialogResult.OK)
- {
- if (!(DataGV.RowCount == 0))
- {
- using (XLWorkbook wb = new XLWorkbook())
- {
- wb.Worksheets.Add(dt, "Sheet1");
- wb.SaveAs(sfd.FileName);
- }
Now the problem is when I triy to export a table which is having a field DateofBirth in DateTime datatype and it is NULL.
So while executing it gives the below error at the highlighted code :
String was not recognized as a valid DateTime.Couldn't store <> in DateOfBirth Column. Expected type is DateTime.'
Could you please help me to solve this issue.