.Net Tips & Tricks
This code example demonstrates how to export data from DataGridView control to Excel document using C#.
Step 1: Add a reference to the ‘Microsoft.Office.Interop’ to your project from .NET components.
Step 2: Creating Excel Application
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
Step 3: Change properties of the Workbook
ExcelApp.Columns.ColumnWidth = 20;
Step 4: Storing header part in Excel
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
ExcelApp.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
Step 5: Storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
ExcelApp.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
Step 6: Save Workbook and exit
ExcelApp.ActiveWorkbook.SaveCopyAs("C:\\test.xls");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
Windows Forms Layout
and excel file after exporting
Complete Source Code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Microsoft.Office.Interop;
namespace Export_DataGridView_to_Excel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
DataSet ds = new DataSet();
ds.ReadXml("C:\\Products.xml");
dataGridView1.DataSource = ds.Tables[0];
}
private void button1_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
// Change properties of the Workbook
ExcelApp.Columns.ColumnWidth = 20;
// Storing header part in Excel
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
ExcelApp.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
// Storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
ExcelApp.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs("C:\\test.xls");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
}
}
}
Related posts:
Getting a rather interesting error message when I followed this. I get a COM exception stating the file name is either in use and open or can’t be found. What am I missing here?
The COM exception is pointing to the ExcelApp.ActiveWorkbook.SaveCopyAs(“C:\\test.xls”); line.
Hello,
I have tried to use this example code but I get the follwing error on this line:
ExcelApp.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
I am using a Gridview control. It does not have a property for Value. Any ideas?
Error 1 ‘System.Web.UI.WebControls.TableCell’ does not contain a definition for ‘Value’ and no extension method ‘Value’ accepting a first argument of type ‘System.Web.UI.WebControls.TableCell’ could be found (are you missing a using directive or an assembly reference?) C:\VS 2010 Compliance Web App\ComplianceWebApp\WebForm2.aspx.cs 129 79 ComplianceWebApp
Found the issue. I changed it to an xlsx file and it worked.
i found error when running this program in line 20 ds.ReadXml(“C:\\Products.xml”); .why??
but in my gridview has image and data that is not exporting to excel but it take as control like namespace so what shoud i do
Thanks a Lot for providing this fantastic Doc abt Exporting Datagridview to Excel..
This is my code for Export Grdview to Exceel File i think this will help u people
thanks
hussain
on aspx page add this
public override void VerifyRenderingInServerForm(Control control)
{
//base.VerifyRenderingInServerForm(control);
}
and EnableEventValidation =”false” on aspx page
and the below code in user control
public void PrepareGridViewForExport(Control gv)
{
LinkButton lb = new LinkButton();
Literal l = new Literal();
string name = String.Empty;
for (int i = 0; i 0)
{
PrepareGridViewForExport(grid);
grid.HeaderRow.Style.Add(“background-color”, “#C8CACB”);
Response.Clear();
Response.AddHeader(“content-disposition”, “attachment; filename=MatchedRecord.xls”);
Response.ContentType = “application/vnd.xls”;
System.IO.StringWriter WriteItem = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlText = new HtmlTextWriter(WriteItem);
grid_artists.RenderControl(htmlText);
Response.Output.Write(WriteItem.ToString());
Response.Flush();
Response.End();
}
else
{
lbl_message.Visible = true;
lbl_message.Text = “There are no records to export”;
}
}
catch (Exception ex)
{
lbl_message.Visible = true;
lbl_message.Text = errorMessage;
}
}
Hi Dear all , i found an error when i wanted to run my program.
this is my error : (( Old Format or invalid type library ))
please Help me to solve this problem.
Hi,
I get the exact same error.
Any help with this would be appreciated.
Regards.
Hi,
Thanks to provide us very helpful code..
Regards
Ketan(KT)
thanks, i will try this code
How can I open Excel files in c# without using ExcelViewer
Alternatively, have a look at the link below. I’ve written a completely free C# library, which lets you export data from a DataTable or DataSet into a “real” Office 2007 .xlsx file, using the OpenXML libraries.
Full source code and a demo are provided, no registration required. I can’t be bothered to spam people, or subject them to Google adverts !!
http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm
It doesn’t use the VSTO libraries, so isn’t as slow, resource hungry, and doesn’t require Excel to be installed on your server.
Dear,
I am working vstudio 2010 in windows forms application. I am using this code but it give me error msg “APPLICATION CLASS”. Can any body help me why this is bieng happneing?
hi,
i tried this piece of code in export to excel button click event and tried running it
i am getting following error
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
please help
Hi,
Thanks for the above code for export to excel. Currently am looking for export to excel with multiple sheets. I have a dataset and each table values should be exported in different sheets of the single excel file. I need it in windows application.
Kindly do the needful.
I appreciate your earliest response.
Thanks in advance.
Great code but how do I maintain leading zero’s? I want all columns to be text but don’t see anywhere to set the option for EXCEL
Hi
Hi Dear all
I found errore
Error Interop type ‘Microsoft.Office.Interop.Excel.ApplicationClass’ cannot be embedded. Use the applicable interface instead.
And change
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.Application();
to
var ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Right click the offending assembly that you added in the solution explorer under your project References. (In my case WIA)
Click properties.
And there should be the option there for Embed Interop Assembly.
Set it to False
it ‘ll help you…………..
The right click on interop and changing the embed to false worked for me VS2010
try use this code:
Microsoft.Office.Interop.Excel._Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
To prevent EXCEL.EXE process keeping alive in the background after the program finishes, you must add the following line at the Step 2:
var workbooks = excelApp.Application.Workbooks;
So Step 2 looks like the following:
var excelApp = new Microsoft.Office.Interop.Excel.Application();
var workbooks = excelApp.Application.Workbooks;
workbooks.Add(Type.Missing);
This is caused by an issue with the COM components. For more info see: http://www.velocityreviews.com/forums/showpost.php?s=f87f0674feda4442dcbd40019cbca65b&p=528575&postcount=2