Tuesday, November 29, 2011

Export data to excel in c#

Title:How to export grid view data to excel in asp.net using c#.net

Description:
Basically we can export the data to excel in two ways in asp.net with c#. One is using Download Format and Other one is using Microsoft.Office.Interop.Excel .In the Download format ,it  will render the grid view and export in XLST format.It is used when the data is export from grid view to excel.In Interop.Eexcel will create excel file dynamically and load the data into it.Exporting data to excel its pretty simple. Let's see how this can be done.For this i have taken data from database first then fill the existing data set.The following name space i have used for excel properties
Asp.Net Export to excel:
using Microsoft.Office.Interop.Excel;
Create Excel document:
Excel.Application App;
Excel.Workbook WorkBook;
Excel.Worksheet WorkSheet;
object misValue = System.Reflection.Missing.Value;
App = new Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
Load the data into Excel file:
Here i will get the data into data set then it will load in to excel file using the following iteration.
for (int col = 0; col < dsreportdata.Tables[0].Columns.Count; col++)
{
for (int row = 0; row < dsreportdata.Tables[0].Rows.Count; row++)
{
WorkSheet.Cells[row + 12, col + 3] = dsreportdata.Tables[0].Rows[row].ItemArray[col].ToString();
}
}

2 comments:

Anonymous said...

This works fine in the development environment on your local machine. It does not work when you deploy the app to a web server, where Excel is not installed.

ishita said...

You can export data to excel files in c# by using Aspose.Cells for .NET Library. Check out their demo page for exporting data to excel, i hope you will find it useful also:

http://www.aspose.com/demos/.net-components/aspose.cells/csharp/quick-start/data/export-data.aspx

Bel