Diamond4Dotnet: Best Ways Export To Excel in ASP.Net Using C#

.aspx.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.IO;

using System.Web.UI.HtmlControls;

using System.Data.OleDb;

using ExcelLibrary.SpreadSheet;

using QiHe.CodeLib;

using ExcelLibrary;

using NPOI.HSSF.UserModel;

using NPOI.HPSF;

using NPOI.SS.UserModel;

using OfficeOpenXml;

using OfficeOpenXml.Style;

using System.Drawing;

using System.Text.RegularExpressions;

namespace MyTestAppOne

{

public partial class ExportExcel : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

//Export_Excel_Html();

//Export_Excel_Oledb();

//Export_Excel_WorkSheet();

//Export_Excel_NPOI();

//GenerateMicrosoftExcel();

Export_excel_EPPlus();

}

#region Data

private DataTable GetData()

{

DataTable dt = new DataTable("MyData_One");

dt.Columns.Add("SNo");

dt.Columns.Add("Name");

dt.Columns.Add("Dept");

for (int i = 1; i <= 10; i++)

{

DataRow dr = dt.NewRow();

dr["SNo"] = i.ToString();

dr["Name"] = "Name" + i.ToString();

dr["Dept"] = "Dept" + i.ToString();

dt.Rows.Add(dr);

}

dt.AcceptChanges();

return dt;

}

private DataTable GetData_One()

{

DataTable dt = new DataTable("MyData_Two");

dt.Columns.Add("SNo");

dt.Columns.Add("Name");

dt.Columns.Add("Dept");

dt.Columns.Add("Address");

for (int i = 1; i <= 10; i++)

{

DataRow dr = dt.NewRow();

dr["SNo"] = i.ToString();

dr["Name"] = "Name" + i.ToString();

dr["Dept"] = "Dept" + i.ToString();

dr["Address"] = "Address" + i.ToString();

dt.Rows.Add(dr);

}

dt.AcceptChanges();

return dt;

}

#endregion

#region HTML

public override void VerifyRenderingInServerForm(Control control)

{

/* Verifies that the control is rendered */

}

private void Export_Excel_Html()

{

//GridView gv = new GridView();

//gv.ID = "gv";

gv.AutoGenerateColumns = true;

gv.AllowPaging = false;

DataSet dsSQL = new DataSet();

DataTable dtSQL = GetData();

dsSQL.Tables.Add(dtSQL);

dtSQL = GetData_One();

dsSQL.Tables.Add(dtSQL);

gv.DataSource = dsSQL;

gv.DataBind();

ChangeControlsToValue(gv);

//Method - I

Response.Clear();

Response.AddHeader("Content-disposition", string.Format("attachement;filename=Report.xls"));

Response.Buffer = true;

Response.ContentType = "application/ms-excel";

Response.Charset = "";

this.EnableViewState = false;

System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

this.VerifyRenderingInServerForm(gv);

gv.RenderControl(oHtmlTextWriter);

Response.Write(oStringWriter.ToString());

Response.End();

Response.Close();

Response.Flush();

//Method - II

//Response.ClearContent();

//Response.AddHeader("content-disposition", "attachment; filename=WorkReport.xls");

//Response.ContentType = "application/excel";

//StringWriter sWriter = new StringWriter();

//HtmlTextWriter hTextWriter = new HtmlTextWriter(sWriter);

//HtmlForm hForm = new HtmlForm();

//gv.Parent.Controls.Add(hForm);

//hForm.Attributes["runat"] = "server";

//hForm.Controls.Add(gv);

//hForm.RenderControl(hTextWriter);

//Response.Write(sWriter.ToString());

//Response.End();

}

private void ChangeControlsToValue(Control gridView)

{

Literal literal = new Literal();

for (int i = 0; i < gridView.Controls.Count; i++)

{

if (gridView.Controls[i].GetType() == typeof(LinkButton))

{

literal.Text = (gridView.Controls[i] as LinkButton).Text;

gridView.Controls.Remove(gridView.Controls[i]);

gridView.Controls.AddAt(i, literal);

}

else if (gridView.Controls[i].GetType() == typeof(DropDownList))

{

literal.Text = (gridView.Controls[i] as DropDownList).SelectedItem.Text;

gridView.Controls.Remove(gridView.Controls[i]);

gridView.Controls.AddAt(i, literal);

}

else if (gridView.Controls[i].GetType() == typeof(CheckBox))

{

literal.Text = (gridView.Controls[i] as CheckBox).Checked ? "True" : "False";

gridView.Controls.Remove(gridView.Controls[i]);

gridView.Controls.AddAt(i, literal);

}

if (gridView.Controls[i].HasControls())

{

ChangeControlsToValue(gridView.Controls[i]);

}

#endregion

#region OLEDB

private void Export_Excel_Oledb()

{

DataSet dsSQL = new DataSet();

DataTable dtSQL = GetData();

dsSQL.Tables.Add(dtSQL);

dtSQL = GetData_One();

dsSQL.Tables.Add(dtSQL);

ExportToExcel(dsSQL, "2003");

}

private void ExportToExcel(DataSet dtSQL, string choice)

{

string strExcelConn = string.Empty, strConn = string.Empty;

switch (choice)

{

// Excel 97-2003

case "2003": strExcelConn = Server.MapPath("~/Temp/" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelConn + ";Extended Properties='Excel 8.0;HDR=Yes'";

break;

// Excel 2007

case "2007": strExcelConn = "~/Temp/" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelConn + ";Extended Properties='Excel 12.0 Xml;HDR=Yes'";

break;

}

OleDbConnection conn = new OleDbConnection(strConn);

conn.Open();

string[] strTableQ = new string[dtSQL.Tables.Count];

//making table query

for (int i = 0; i < dtSQL.Tables.Count; i++)

{

strTableQ[i] = "CREATE TABLE [" + dtSQL.Tables[i].TableName + "](";

for (int j = 0; j < dtSQL.Tables[i].Columns.Count; j++)

{

DataColumn dCol;

dCol = dtSQL.Tables[i].Columns[j];

strTableQ[i] += " [" + dCol.ColumnName + "] varchar(255) , ";

}

strTableQ[i] = strTableQ[i].Substring(0, strTableQ[i].Length - 2);

strTableQ[i] += ")";

OleDbCommand cmd = new OleDbCommand(strTableQ[i], conn);

cmd.ExecuteNonQuery();

}

//making insert query

string[] strInsertQ = new string[dtSQL.Tables.Count];

for (int i = 0; i < dtSQL.Tables.Count; i++)

{

strInsertQ[i] = "Insert Into " + dtSQL.Tables[i].TableName + " Values (";

for (int k = 0; k < dtSQL.Tables[i].Columns.Count; k++)

{

strInsertQ[i] += "@" + dtSQL.Tables[i].Columns[k].ColumnName + " , ";

}

strInsertQ[i] = strInsertQ[i].Substring(0, strInsertQ[i].Length - 2);

strInsertQ[i] += ")";

}

//Now inserting data

for (int i = 0; i < dtSQL.Tables.Count; i++)

{

for (int j = 0; j < dtSQL.Tables[i].Rows.Count; j++)

{

OleDbCommand cmd = new OleDbCommand(strInsertQ[i], conn);

for (int k = 0; k < dtSQL.Tables[i].Columns.Count; k++)

{

cmd.Parameters.AddWithValue("@" + dtSQL.Tables[i].Columns[k].ColumnName.ToString(), dtSQL.Tables[i].Rows[j][k].ToString());

}

cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

}

conn.Close();

conn.Dispose();

string name = Path.GetFileName(strExcelConn);

byte[] file = File.ReadAllBytes(strExcelConn);

File.Delete(strExcelConn);

Response.Clear();

Response.AddHeader("Content-disposition", string.Format("attachement;filename={0}", name));

Response.Buffer = true;

Response.ContentType = "application/ms-excel";

Response.BinaryWrite(file.ToArray());

Response.End();

Response.Close();

Response.Flush();

}

#endregion

#region WorkSheet

private void Export_Excel_WorkSheet()

{

string strExcelConn = string.Empty;

strExcelConn = Server.MapPath("~/Temp/" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");

DataSet dsSQL = new DataSet();

DataTable dtSQL = GetData();

dsSQL.Tables.Add(dtSQL);

dtSQL = GetData_One();

dsSQL.Tables.Add(dtSQL);

DataSetHelper.CreateWorkbook(strExcelConn, dsSQL);

string name = Path.GetFileName(strExcelConn);

byte[] file = File.ReadAllBytes(strExcelConn);

File.Delete(strExcelConn);

Response.Clear();

Response.AddHeader("Content-disposition", string.Format("attachement;filename={0}", name));

Response.Buffer = true;

Response.ContentType = "application/ms-excel";

Response.BinaryWrite(file.ToArray());

Response.End();

Response.Close();

Response.Flush();

}

#endregion

#region NPOI

HSSFWorkbook hssfworkbook;

private void Export_Excel_NPOI()

{

string filename = "Report.xls";

Response.ContentType = "application/vnd.ms-excel";

Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));

Response.Clear();

//InitializeWorkbook

hssfworkbook = new HSSFWorkbook();

DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

dsi.Company = "Report";

hssfworkbook.DocumentSummaryInformation = dsi;

SummaryInformation si = PropertySetFactory.CreateSummaryInformation();

si.Subject = "Report Details";

hssfworkbook.SummaryInformation = si;

//End

DataSet dsSQL = new DataSet();

DataTable dtSQL = GetData();

dsSQL.Tables.Add(dtSQL);

dtSQL = GetData_One();

dsSQL.Tables.Add(dtSQL);

GenerateData(dsSQL);

//Write the stream data of workbook

MemoryStream file = new MemoryStream();

hssfworkbook.Write(file);

Response.Clear();

Response.BinaryWrite(file.GetBuffer());

Response.End();

Response.Close();

Response.Flush();

}

private void GenerateData(DataSet dslist)

{

for (int pkv = 0; pkv < dslist.Tables.Count; pkv++)

{

DataTable excellist = dslist.Tables[pkv];

IRow nonHeaderRow = null;

IRow headerRow = null;

ISheet sheet1 = hssfworkbook.CreateSheet(excellist.TableName);

//set header font

NPOI.SS.UserModel.IFont font = hssfworkbook.CreateFont();

font.Boldweight = (short)FontBoldWeight.BOLD;

font.Color = (short)FontColor.RED;

ICellStyle style1 = hssfworkbook.CreateCellStyle();

style1.SetFont(font);

headerRow = sheet1.CreateRow(0);

//header text appears in first row of workbook

for (int pk = 0; pk < excellist.Columns.Count; pk++)

{

ICell cell = headerRow.CreateCell(pk);

cell.SetCellValue(excellist.Columns[pk].ColumnName);

cell.CellStyle = style1;

}

for (int j = 0; j < excellist.Rows.Count; j++)

{

nonHeaderRow = sheet1.CreateRow(j + 1);//don't use j=0 because it will remove the first row i.e Headerrow

//nonHeaderRow.CreateCell(0).SetCellValue(excellist[j - 1].EMPCOMPANYUSERID);

for (int k = 0; k < excellist.Columns.Count; k++)

{

nonHeaderRow.CreateCell(k).SetCellValue(excellist.Rows[j][k].ToString());

}

for (int pk = 0; pk < excellist.Columns.Count; pk++)

{

sheet1.AutoSizeColumn(pk);

}

#endregion

#region Microsoft Excel

//Better to avoid this methosd due to hosted server need to be installed Microsoft Office(for this example 2010).

public void GenerateMicrosoftExcel()

{

DataSet dsSQL = new DataSet();

DataTable dtSQL = GetData();

dsSQL.Tables.Add(dtSQL);

dtSQL = GetData_One();

dsSQL.Tables.Add(dtSQL);

MS_ExportExcel(dtSQL);

}

protected void MS_ExportExcel(DataTable dt)

{

if (dt == null || dt.Rows.Count == 0)

{

return;

}

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

if (xlApp == null)

{

return;

}

System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;

System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;

Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];

Microsoft.Office.Interop.Excel.Range range;

worksheet.Name = dt.TableName;

long totalCount = dt.Rows.Count;

long rowRead = 0;

float percent = 0;

for (int i = 0; i < dt.Columns.Count; i++)

{

worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;

range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];

range.Interior.ColorIndex = 15;

range.Font.Bold = true;

}

for (int r = 0; r < dt.Rows.Count; r++)

{

for (int i = 0; i < dt.Columns.Count; i++)

{

worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();

}

rowRead++;

percent = ((float)(100 * rowRead)) / totalCount;

}

xlApp.Visible = true;

}

#endregion

#region EPPlus

private void Export_excel_EPPlus()

{

DataSet dsSQL = new DataSet();

DataTable dtSQL = GetData();

dsSQL.Tables.Add(dtSQL);

dtSQL = GetData_One();

dsSQL.Tables.Add(dtSQL);

using (ExcelPackage pck = new ExcelPackage())

{

for (int PK = 0; PK < dsSQL.Tables.Count; PK++)

{

DataTable tbl = dsSQL.Tables[PK];

//Create the worksheet

ExcelWorksheet ws = pck.Workbook.Worksheets.Add(tbl.TableName);

//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1

ws.Cells["A1"].LoadFromDataTable(tbl, true);

string from_to = GetColumnName(tbl.Columns.Count);

//Format the header row use below option for Cells or "A1:" + from_to + "1"

using (ExcelRange rng = ws.Cells[1, 1, 1, tbl.Columns.Count])

{

rng.Style.Font.Bold = true;

rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid

rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue

rng.Style.Font.Color.SetColor(Color.White);

}

//Example how to Format Column 1 as numeric

using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])

{

col.Style.Numberformat.Format = "#,##0.00";

col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;

}

//Write it back to the client

Response.Clear();

Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

Response.AddHeader("content-disposition", "attachment; filename=ExcelDemo.xlsx");

Response.BinaryWrite(pck.GetAsByteArray());

Response.End();

Response.Flush();

}

private string GetColumnName(int index)

{

const int alphabetsCount = 26;

string result = string.Empty;

while (index > 0)

{

result = char.ConvertFromUtf32(64 + (index % alphabetsCount)) + result;

index /= alphabetsCount;

}

return result;

}

#endregion

}

.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ExportExcel.aspx.cs" Inherits="MyTestAppOne.ExportExcel" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

<asp:GridView ID="gv" runat="server" AutoGenerateColumns="true" Width="866px"

CaptionAlign="Top" Height="187px" BackColor="White" BorderColor="#CC9966" BorderStyle="None"

BorderWidth="1px" CellPadding="4" Caption="<b>Test Report</b>" ShowFooter="True"

EmptyDataText="No Records Found">

</Columns>

</asp:GridView>

</div>

</form>

</body>

</html>

You Can Download the Working Code of Export To Excel From here.