ASP.NET - Export data to excel from c#

Asked By theiry henry on 25-Apr-11 08:04 AM

I want to export my data to excel using c# code.I hav already used the technique of using Response object but this gets failed in case when data is around 50 thousand.
Is there ny other method by which i can export data to excel. It sould work even if the server doesnot contain MS Office installed in the system.

Plz ny 1 help me out to solve this problem ..I m badly stucked....

Jitendra Faye replied to theiry henry on 25-Apr-11 08:11 AM

To export data from GridView to excel follow these link-

http://www.eggheadcafe.com/sample-code/csharp.NET/b691cee7-856e-498c-b7e2-20ceca61b307/gridview-data-to-excel-exporter.aspx
and
http://www.c-sharpcorner.com/uploadfile/dipalchoksi/exportxl_asp2_dc11032006003657am/exportxl_asp2_dc.aspx

I hope this will help you.

Sahil Kumar replied to theiry henry on 25-Apr-11 08:17 AM

Hi,

I had same issue when I will try to export rows more than 10000 it will just error out. Then I follow this approach and I have tried exporting more than 500k rows and works perfect for me. Just I have put this function in background worked in order to get UI efficency.

Use this function and issue will be resolved.

private void exportToExcel(string filename, DataTable dtRecords)

{

StreamWriter wtr = null;

try

{

log.Info("started creating excel file using xml");

const string initial = "<xml version>\r\n<Workbook " +

"xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +

" xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +

"xmlns:x=\"urn:schemas- microsoft-com:office:" +

"excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +

"office:spreadsheet\">\r\n <Styles>\r\n " +

"<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +

"<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +

"\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" +

"\r\n <Protection/>\r\n </Style>\r\n " +

"<Style ss:ID=\"BoldColumn\">\r\n <Font " +

"x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " +

"<Style ss:ID=\"StringLiteral\">\r\n <NumberFormat" +

" ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +

"ss:ID=\"Decimal\">\r\n <NumberFormat " +

"ss:Format=\"0.0000\"/>\r\n </Style>\r\n " +

"<Style ss:ID=\"Integer\">\r\n <NumberFormat " +

"ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +

"ss:ID=\"DateLiteral\">\r\n <NumberFormat " +

"ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " +

"</Styles>\r\n " + "<Worksheet ss:Name=\"Sheet1\">";

StringBuilder data = new StringBuilder();

List<string> columns = new List<string>();

List<string> headers = new List<string>();

//DevExpress.XtraGrid.Views.Grid.GridView gv = (DevExpress.XtraGrid.Views.Grid.GridView)this.MainView;

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

{

if (dtRecords.Columns[i].ColumnName == "__Offset__") continue;

headers.Add(dtRecords.Columns[i].ColumnName);

columns.Add(dtRecords.Columns[i].ColumnName);

}

data.Append(" <Table>\n");

//Write the Gird column headrers

data.Append("<Row>\r\n");

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

data.Append("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">" + headers[i].ToString() + "</Data></Cell>\r\n");

data.Append("</Row>\r\n");

wtr = new StreamWriter(filename);

wtr.WriteLine(initial);

//wtr.WriteLine(data);

XmlDocument xdoc = new XmlDocument();

XmlNode xnode = xdoc.CreateElement("Temp");

xdoc.AppendChild(xnode);

//Grid data

DataView dvRecordsView = new DataView();

dvRecordsView.Table = dtRecords;

dvRecordsView.RowFilter = expressions;

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

{

data.Append("<Row>\r\n");

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

{

if (dvRecordsView[i][columns[j]] == DBNull.Value ||

dvRecordsView[i][columns[j]] == null)

xnode.InnerText = string.Empty;

else

xnode.InnerText = dvRecordsView[i][columns[j]].ToString();

data.Append("<Cell><Data ss:Type=\"String\">" + xnode.InnerXml + "</Data></Cell>\r\n");

}

data.Append("</Row>\r\n");

if (((i + 1) % 1000) == 0)

{

wtr.WriteLine(data);

wtr.Flush();

data = new StringBuilder();

}

data.Append("</Table>\r\n</Worksheet>\r\n</Workbook>");

wtr.WriteLine(data);

wtr.Flush();

wtr.Close();

wtr.Dispose();

wtr = null;

log.Info("All records exported successfully to excel file");

}

catch (Exception ex)

{

// Log.LogError(ex, true);

log.Error("Error while exporting records to excel file", ex);

throw ex;

}

finally

{

if (wtr != null)

{

try

{

wtr.Flush();

wtr.Close();//safe close

wtr.Dispose();

wtr = null;

}

catch { }

}

// Log.LogExit("frmDocDetails exportToExcel end");

}

I hope this will solve your issue.

Ravi S replied to theiry henry on 25-Apr-11 08:22 AM

Hi Henry

Try this code

using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel; 
namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp ;
            Excel.Workbook xlWorkBook ;
            Excel.Worksheet xlWorkSheet ;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            //add data 
            xlWorkSheet.Cells[1, 1] = "";
            xlWorkSheet.Cells[1, 2] = "Student1";
            xlWorkSheet.Cells[1, 3] = "Student2";
            xlWorkSheet.Cells[1, 4] = "Student3";
            xlWorkSheet.Cells[2, 1] = "Term1";
            xlWorkSheet.Cells[2, 2] = "80";
            xlWorkSheet.Cells[2, 3] = "65";
            xlWorkSheet.Cells[2, 4] = "45";
            xlWorkSheet.Cells[3, 1] = "Term2";
            xlWorkSheet.Cells[3, 2] = "78";
            xlWorkSheet.Cells[3, 3] = "72";
            xlWorkSheet.Cells[3, 4] = "60";
            xlWorkSheet.Cells[4, 1] = "Term3";
            xlWorkSheet.Cells[4, 2] = "82";
            xlWorkSheet.Cells[4, 3] = "80";
            xlWorkSheet.Cells[4, 4] = "65";
            xlWorkSheet.Cells[5, 1] = "Term4";
            xlWorkSheet.Cells[5, 2] = "75";
            xlWorkSheet.Cells[5, 3] = "82";
            xlWorkSheet.Cells[5, 4] = "68";
            Excel.Range chartRange ;
            Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
            Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
            Excel.Chart chartPage = myChart.Chart;
            chartRange = xlWorkSheet.get_Range("A1", "d5");
            chartPage.SetSourceData(chartRange, misValue);
            chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
            //export chart as picture file
            chartPage.Export(@"C:\excel_chart_export.bmp","BMP",misValue );
            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-Excel.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();
            }
        }
    }
}

For more details,refer the below links
http://www.adventuresindevelopment.com/2009/05/27/how-to-export-data-to-excel-in-aspnet/
http://itsrashid.wordpress.com/2007/05/14/export-dataset-to-excel-in-c/

Reena Jain replied to theiry henry on 25-Apr-11 08:23 AM

hi,

Example imports/exports DataTable to Excel file (in XLS format) by directly working with DataTable using InsertDataTable and ExtractToDataTable methods:

ExcelFile ef = new ExcelFile();

DataTable dataTable = new DataTable();

// Depending on the format of the input file, you need to change this:

dataTable.Columns.Add("FirstName", typeof(string));

dataTable.Columns.Add("LastName", typeof(string));

// Load Excel file.

ef.LoadXls("FileName.xls");

// Select the first worksheet from the file.

ExcelWorksheet ws = ef.Worksheets[0];

// Extract the data from the worksheet to the DataTable.

// Data is extracted starting at first row and first column for 10 rows or until the first empty row appears.

ws.ExtractToDataTable(dataTable, 10, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[0], ws.Columns[0]);

// Change the value of the first cell in the DataTable.

dataTable.Rows[0][0] = "Hello world!";

// Insert the data from DataTable to the worksheet starting at cell "A1".

ws.InsertDataTable(dataTable, "A1", true);

// Save the file to XLS format.

ef.SaveXls("DataTable.xls");

Hope this will help you

theiry henry replied to Reena Jain on 25-Apr-11 08:46 AM

Dear reena,
I think this code wont work if u dont hav MsOffice Installed in your system Microsoft.Office.Interop.Excel;

Reena Jain replied to theiry henry on 25-Apr-11 09:19 AM

hi,

here is one more code for you

public static void CreateWorkbook(DataSet ds, String path)

{

XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);

XslTransform xt = new XslTransform();

StreamReader reader =new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”));

XmlTextReader xRdr = new XmlTextReader(reader);

xt.Load(xRdr, null, null);

StringWriter sw = new StringWriter();

xt.Transform(xmlDataDoc, null, sw, null);

StreamWriter myWriter = new StreamWriter (path + “\\Report.xls“);

myWriter.Write (sw.ToString());

myWriter.Close ();

}

check the below links for more help

http://www.eggheadcafe.com/community/aspnet/2/10241894/data-grid-view-export-to-excel.aspx
http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm

let me know if its not work

crish crish replied to theiry henry on 25-Apr-11 09:25 AM

Step: The Actual Export

The code to do the Excel Export is very straightforward. You can also export to different application type by changing the content-disposition and ContentType.

string attachment = "attachment; filename=Contacts.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

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

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

GridView1.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

If you run the code as above, it will result in an HttpException as follows:

Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."

To avoid this error, add the following code:

public override void VerifyRenderingInServerForm(Control control)

{

}

Step : Convert the contents

If the GridView contains any controls, such as Checkboxes, Dropdownlists, we need to replace the contents with their relevant values. The following recursive function uses Reflection to determine the type of control. The control is deleted in preparation for the Excel export and the relevant value of the control is added.

private void PrepareGridViewForExport(Control gv)

{

LinkButton lb = new LinkButton();

Literal l = new Literal();

string name = String.Empty;

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

{

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

{

l.Text = (gv.Controls[i] as LinkButton).Text;

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

gv.Controls.AddAt(i, l);

}

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

{

l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;

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

gv.Controls.AddAt(i, l);

}

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

{

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

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

gv.Controls.AddAt(i, l);

}

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

{

PrepareGridViewForExport(gv.Controls[i]);

}

Code Listing:

Image: Page Design

Image : Sample in action

Image: Export to Excel button is clicked

Image: GridView contents exported to Excel

ExcelExport.aspx

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

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

<title>Contacts Listing</title>

</head>

<body>

<div>

Contacts Listing

<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Export To Excel" /></span></strong><br />

<br />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ContactID"

DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." style="font-size: small; font-family: Arial" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical">

<asp:BoundField DataField="ContactID" HeaderText="ContactID" ReadOnly="True" SortExpression="ContactID" Visible="False" />

<asp:BoundField DataField="FName" HeaderText="First Name" SortExpression="FName" />

<asp:BoundField DataField="LName" HeaderText="Last Name" SortExpression="LName" />

<asp:BoundField DataField="ContactPhone" HeaderText="Phone" SortExpression="ContactPhone" />

<asp:TemplateField HeaderText="Favorites">

<asp:CheckBox ID="CheckBox1" runat="server" />

</ItemTemplate></asp:TemplateField>

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ContactsConnectionString1 %>"

DeleteCommand="DELETE FROM [ContactPhone] WHERE [ContactID] = @ContactID" InsertCommand="INSERT INTO [ContactPhone] ([FName], [LName], [ContactPhone]) VALUES (@FName, @LName, @ContactPhone)"

ProviderName="<%$ ConnectionStrings:ContactsConnectionString1.ProviderName %>"

SelectCommand="SELECT [ContactID], [FName], [LName], [ContactPhone] FROM [ContactPhone]"

UpdateCommand="UPDATE [ContactPhone] SET [FName] = @FName, [LName] = @LName, [ContactPhone] = @ContactPhone WHERE [ContactID] = @ContactID">

<asp:Parameter Name="FName" Type="String" />

<asp:Parameter Name="LName" Type="String" />

<asp:Parameter Name="ContactPhone" Type="String" />

</InsertParameters>

<asp:Parameter Name="FName" Type="String" />

<asp:Parameter Name="LName" Type="String" />

<asp:Parameter Name="ContactPhone" Type="String" />

<asp:Parameter Name="ContactID" Type="Int32" />

</UpdateParameters>

<asp:Parameter Name="ContactID" Type="Int32" />

</DeleteParameters>

</asp:SqlDataSource>

<br />

</div>

</form>

</body>

</html>

ExcelExport.aspx.cs

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Text;

using System.IO;

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

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

//Export the GridView to Excel

PrepareGridViewForExport(GridView1);

ExportGridView();

}

private void ExportGridView()

{

string attachment = "attachment; filename=Contacts.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

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

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

GridView1.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

public override void VerifyRenderingInServerForm(Control control)

{

}

private void PrepareGridViewForExport(Control gv)

{

LinkButton lb = new LinkButton();

Literal l = new Literal();

string name = String.Empty;

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

{

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

{

l.Text = (gv.Controls[i] as LinkButton).Text;

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

gv.Controls.AddAt(i, l);

}

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

{

l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;

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

gv.Controls.AddAt(i, l);

}

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

{

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

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

gv.Controls.AddAt(i, l);

}

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

{

PrepareGridViewForExport(gv.Controls[i]);

}