Excel export from windows form using closedXML

Abhilash Vg

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.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo6y ago
Accepted answer
Hi

Define the variable in using statement with var key word
foreach (FileInfo file in sqlfiles)
{
string script = file.OpenText().ReadToEnd();
using (var 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;
}
}
Thanks and regards
+2
Jerry Maguire5y ago
C#
using System.IO;
using System.Data;
using System.Reflection;
using ClosedXML.Excel;

public Form1()
{
InitializeComponent();
this.BindDataGridView();
}

private void BindDataGridView()
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
new DataColumn("Name", typeof(string)),
new DataColumn("Country",typeof(string)) });
dt.Rows.Add(1, "John Hammond", "United States");
dt.Rows.Add(2, "Mudassar Khan", "India");
dt.Rows.Add(3, "Suzanne Mathews", "France");
dt.Rows.Add(4, "Robert Schidner", "Russia");
this.dataGridView1.DataSource = dt;
}
+1
Sapna Girish6y ago
Hi you could probably try Spire.XLS. Here is an article for your reference.

https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html
+1