exporting data from database to excel in asp.net

Question

I am getting an error on saving data in excel from database using c# . when click in button i get only one data in my excel file welcome.xlsx please suggest me to fix this error . thanks System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC at Microsoft.Office.Interop.Excel._Worksheet.set_Name(String RHS) at BasicGridView.btnclick(Object sender, EventArgs e) in F:\shubham\esin\esin\Default.aspx.cs:line 73

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.Web.Configuration;
using System.Data.SqlClient;
using System.Data;

public partial class BasicGridView : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            string connectionString = "server=Myserver; database=northwind; user id=sasa; password=123;integrated security=false ";

            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("SELECT * FROM uregistration", con);

            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = cmd;
            DataTable dt = new DataTable();
            dt.TableName = "uregistration";
            sda.Fill(dt);

            GridView1.DataSource = dt;
            GridView1.DataBind();
        }

    }
    protected void btnclick(object sender, EventArgs e)
    {

        string connectionString = "server=Myserver; database=northwind; user id=sa; password=123;integrated security=false ";

        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("SELECT * FROM uregistration", con);

        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = cmd;
        DataTable dt = new DataTable();

        sda.Fill(dt);

        object misValue = System.Reflection.Missing.Value;
        Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
        app.Visible = false;


        Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
        Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;

        for(int i =0;i<dt.Columns.Count;i++)
        {
            ws.Cells[1, i + 1] = dt.Columns[i].ColumnName;
        }

        for(int i=0; i < dt.Rows.Count; i++)
        {
            for(int j=0; j < dt.Columns.Count; j++)
            {
                ws.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
            }
        }

        try {
            ws.Name = dt.TableName;
        }catch(Exception ex)
        {
            lbl.Text = ex.ToString();
        }
        wb.SaveAs("welcome2.xl", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,misValue,misValue,misValue,misValue,misValue);

        wb.Close(true, misValue, misValue);
        app.Quit();
    }
}

Hope [this](http://stackoverflow.com/questions/7099770/hresult-0x800a03ec-on-worksheet-range) helps — शेखर, Jan 25 '17 at 12:24
its better to use this http://knowlton-group.com/using-ssis-to-export-data-to-excel/ — Karthikeyan, Jan 25 '17 at 12:28
Start using a specialized library for creating Excel files, like [EPPlus](http://stackoverflow.com/documentation/epplus/drafts/98280) for example. — VDWWD, Jan 25 '17 at 15:20

score 0 · Accepted Answer · answered Jan 25 '17 at 12:35

I've tested your code with only one modification and it works also it shows all the fetched data within excel sheet with no exception

wb.SaveAs("welcome2.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,misValue,misValue,misValue,misValue,misValue);

The extension ".xl" was wrong. The exception occurs when it goes to write the file while the file with the same name and at same destination is already there, dialog appears that asks you to overwrite that file with Yes, No, Cancel, if you select Cancel it will throw an exception. You should check for file already exists then rename/delete existing file and then write new one. Hope it helps.

thanks for your response , i am not getting data in my excel file any suggestion — shubham singh, Jan 25 '17 at 13:07
If my answer helped you to solve your issue, then select it as an answer and help others. — M. Adeel Khalid, Jan 25 '17 at 14:12

exporting data from database to excel in asp.net

1 Answers1