Abhilash Vg

Abhilash Vg

  • 2k
  • 21
  • 101

Excel export from windows form using closedXML

Aug 6 2020 5:40 AM
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:
  1. cnn.Open();  
  2. foreach (FileInfo file in sqlfiles)  
  3. {  
  4. string script = file.OpenText().ReadToEnd();  
  5. using (cm = cnn.CreateCommand())  
  6. cm.CommandText = script;  
  7. SqlDataReader reader = cm.ExecuteReader();  
  8. var dt = new DataTable();  
  9. dt.Load(reader);  
  10. DataGV.AutoGenerateColumns = true;  
  11. DataGV.DataSource = dt;  
  12. DataGV.Refresh();  
  13. DataGV.ReadOnly = true;  
  14. }  
  15. then I exported it using the below code :  
  16. DataTable dt = new DataTable();  
  17. //Adding the Columns  
  18. foreach (DataGridViewColumn column in DataGV.Columns)  
  19. {  
  20. dt.Columns.Add(column.HeaderText, column.ValueType);  
  21. }  
  22. //Adding the Rows  
  23. foreach (DataGridViewRow row in DataGV.Rows)  
  24. {  
  25. dt.Rows.Add();  
  26. foreach (DataGridViewCell cell in row.Cells)  
  27. {  
  28. dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();  
  29. }  
  30. }  
  31. SaveFileDialog sfd = new SaveFileDialog();  
  32. sfd.Filter = "Excel Documents (*.xlsx)|*.xlsx";  
  33. sfd.FileName = "export.xlsx";  
  34. if (sfd.ShowDialog() == DialogResult.OK)  
  35. {  
  36. if (!(DataGV.RowCount == 0))  
  37. {  
  38. using (XLWorkbook wb = new XLWorkbook())  
  39. {  
  40. wb.Worksheets.Add(dt, "Sheet1");  
  41. wb.SaveAs(sfd.FileName);  
  42. }  
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.

Brought to you by:

Answers (2)