Excel export from windows form using closedXML

Question

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();
//Adding the Columns
foreach (DataGridViewColumn column in DataGV.Columns)
{
dt.Columns.Add(column.HeaderText, column.ValueType);
}
//Adding the Rows
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.