How to export DataGridView to excel file
The following C# program demonstrate how to export data from Datagridview to an Excel file.
The program first connect database and load data from database to Datagridview and then create a new excel file and write the data from Datagridview to Excel file.
First step is to Load the Product table data to DataGridView , for detail of Product table please refer to Database Structure , and create new Excel file and write the data from Datagridview to Excel file.
Full Source C#
using System;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
SqlConnection cnn ;
string connectionString = null;
string sql = null;
connectionString = "data source=servername;initial catalog=databasename;user id=username;password=password;";
cnn = new SqlConnection(connectionString);
cnn.Open();
sql = "SELECT * FROM Product";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
DataSet ds = new DataSet();
dscmd.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
}
private void button2_Click(object sender, EventArgs e)
{
Excel.Application xlApp ;
Excel.Workbook xlWorkBook ;
Excel.Worksheet xlWorkSheet ;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
for (i = 0; i <= dataGridView1.RowCount - 1; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
Related Topics
- How to create Excel file in C#
- How to open an Excel file in C#
- How to read an Excel file using C#
- How to programmatically Add New Worksheets
- How to delete worksheet from an excel file
- How to format an Excel file using C#
- How to insert a picture in excel from C# App
- How to insert a background picture in excel
- How to create Excel Chart from C#
- How to export excel chart from C#
- How to excel chart in C# picturebox
- C# data validation input box in excel file
- Read and Import Excel File into DataSet or DataTable
- How to insert data to Excel file using OLEDB
- How to update data in Excel file using OLEDB
- How to export databse to excel file