How to export dataset to excel in C#?
Export dataset to the excel sheet in asp.net c#
Many times we need to export reports in excel format. So here I will explain how to export Dataset or Datatable in Excel file. Follow the below instructions:1. Add reference to Microsoft.Office.Interop.Excel.dll
2. using System.Runtime.InteropServices;
Source:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
<br />
<asp:Button ID="btnExportExcel" runat="server" Text="Export To Excel"
onclick="btnExportExcel_Click" />
</div>
</form>
</body>
</html>
Code Behind:
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Runtime.InteropServices;
public partial class _Default : System.Web.UI.Page
{
DataTable productsDataTable = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
// create DataTable object
// auto increment column for productsDataTable
DataColumn dataColumn = new DataColumn("AutoID", System.Type.GetType("System.Int32"));
dataColumn.AutoIncrement = true;
dataColumn.AutoIncrementSeed = 1;
dataColumn.AutoIncrementStep = 1;
dataColumn.Unique = true;
productsDataTable.Columns.Add(dataColumn);
// product id column
dataColumn = new DataColumn("ProductID", System.Type.GetType("System.Int32"));
productsDataTable.Columns.Add(dataColumn);
// product name column
dataColumn = new DataColumn("ProductName", System.Type.GetType("System.String"));
productsDataTable.Columns.Add(dataColumn);
// quantity column
dataColumn = new DataColumn("ProductQuantity", System.Type.GetType("System.Int32"));
productsDataTable.Columns.Add(dataColumn);
// product price column
dataColumn = new DataColumn("ProductPrice", System.Type.GetType("System.Decimal"));
productsDataTable.Columns.Add(dataColumn);
// After Creating columns you can add data rows to store the data dynamically
DataRow dataRow = productsDataTable.NewRow();
dataRow["ProductID"] = 43;
dataRow["ProductName"] = "Ipoh Coffee";
dataRow["ProductQuantity"] = 1;
dataRow["ProductPrice"] = 46.00;
productsDataTable.Rows.Add(dataRow);
dataRow = productsDataTable.NewRow();
dataRow["ProductID"] = 40;
dataRow["ProductName"] = "Boston Crab Meat";
dataRow["ProductQuantity"] = 1;
dataRow["ProductPrice"] = 18.40;
productsDataTable.Rows.Add(dataRow);
GridView1.DataSource = productsDataTable;
GridView1.DataBind();
}
protected void btnExportExcel_Click(object sender, EventArgs e)
{
try
{
DataSet ds = new DataSet();
ds.Tables.Add(productsDataTable);
ExportToExcel(ds, "test");
}
catch (Exception ex)
{
throw;
}
}
// Function to export dataset to excel file
public void ExportToExcel(DataSet dataset, string DestFileName)
{
try
{
if (System.IO.File.Exists(DestFileName))
{
System.IO.File.Delete(DestFileName);
}
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelWorkBook = default(Microsoft.Office.Interop.Excel.Workbook);
excelWorkBook = excelApp.Workbooks.Add(Type.Missing);
int sheetIndex = 0;
int col = 0;
int row = 0;
Microsoft.Office.Interop.Excel.Worksheet excelSheet = default(Microsoft.Office.Interop.Excel.Worksheet);
foreach (System.Data.DataTable dt1 in dataset.Tables)
{
sheetIndex += 1;
object[,] rawData = new object[dt1.Rows.Count + 1, dt1.Columns.Count];
for (col = 0; col <= dt1.Columns.Count - 1; col++)
{
rawData[0, col] = dt1.Columns[col].ColumnName;
}
for (col = 0; col <= dt1.Columns.Count - 1; col++)
{
for (row = 0; row <= dt1.Rows.Count - 1; row++)
{
rawData[row + 1, col] = dt1.Rows[row].ItemArray[col];
}
}
string finalColLetter = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt1.Columns.Count > colCharsetLen)
{
finalColLetter = colCharset.Substring((dt1.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring((dt1.Columns.Count - 1) % colCharsetLen, 1);
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets.get_Item(sheetIndex);
excelSheet.Name = dt1.TableName;
string excelRange = string.Format("A1:{0}{1}", finalColLetter, dt1.Rows.Count + 1);
excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
//int colcount = excelSheet.Cells.CurrentRegion.Columns.Count;
string excelRange1 = string.Format("A1:{0}{1}", finalColLetter, 1);
excelSheet.get_Range(excelRange1, Type.Missing).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
excelSheet.get_Range(excelRange1, Type.Missing).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
excelSheet.get_Range(excelRange1, Type.Missing).Font.Bold = true;
excelSheet = null;
}
string e = System.IO.Path.GetExtension(DestFileName);
if (e == ".csv")
{
excelWorkBook.SaveAs(DestFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVMSDOS, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
}
else if (e == ".xls")
{
excelWorkBook.SaveAs(DestFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
}
//use this commented code for auto save file
//else if (e == "")
//{
// excelWorkBook.SaveAs(DestFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing);
//}
GC.Collect();
GC.WaitForPendingFinalizers();
excelWorkBook.Close(true, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(excelWorkBook);
excelApp.Quit();
Marshal.FinalReleaseComObject(excelApp);
}
catch (Exception ex)
{
throw;
}
}
}
To download the sample code click here. I hope this will help you out.
How to export dataset to excel in C#?
Reviewed by Ravi Kumar
on
12:26 AM
Rating:
Reviewed by Ravi Kumar
on
12:26 AM
Rating:
No comments: