Hi raiamit,
Refer the below sample code. For this you have use OpenXml and ClosedXml library.
You can download the libraries using the following download locations.
C#
private void btnExportToExcel_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT TOP 10 * FROM Customers"))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
sda.Fill(dt);
}
}
}
string folderPath = "C:\\Users\\dharmendra\\Desktop\\Test\\";
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt, "Customers");
wb.SaveAs(folderPath + "DataGridViewExport.xlsx");
}
}
VB.Net
Private Sub btnExportToExcel_Click(sender As Object, e As EventArgs)
Dim dt As New DataTable()
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("SELECT TOP 10 * FROM Customers")
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
sda.Fill(dt)
End Using
End Using
End Using
Dim folderPath As String = "C:\\Users\\dharmendra\\Desktop\\Test\\"
If Not Directory.Exists(folderPath) Then
Directory.CreateDirectory(folderPath)
End If
Using wb As New XLWorkbook()
wb.Worksheets.Add(dt, "Customers")
wb.SaveAs(folderPath & Convert.ToString("DataGridViewExport.xlsx"))
End Using
End Sub