How to Export Database table data into excel file in C# on button click?

Question

0.00/5 (No votes)

See more:

Hi
How to export database table data into excel file.

I have button click code which gets empty excel file.

Now when i click on button the database table data export the data into excel file.

i need some help to do this steps.

private void button1_Click_1(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application xl = default(Microsoft.Office.Interop.Excel.Application);
xl = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = default(Microsoft.Office.Interop.Excel.Workbook);
wb = xl.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet ws = default(Microsoft.Office.Interop.Excel.Worksheet);
ws = wb.ActiveSheet;

xl.Visible = true;
}

Thanks Regrades
sam.198979

Posted 22-May-13 20:44pm

sam.198979

Updated 18-Sep-16 23:27pm

Add a Solution

6 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shihab Jamil · Answer 1 · 2014-09-21T21:53:00

I know, it's been a while to answer but yet to help others:

For those who want to add column header in the excel sheet:

C#

foreach (DataTable dt in ds.Tables)
    {
       foreach (DataColumn dc in dt.Columns)
           {
              columnName.Add(dc.ColumnName.ToString());
           }
    }

for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
     {
      xlWorkSheet.Cells[1, j + 1] = columnName[j].ToString();
     }

Jignesh Khant · Answer 2 · 2013-05-22T20:54:00

private void ExportToExcel(string strFileName, GridView gv)
{
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
Response.ContentType = "application/excel";
System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gv.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}

protected void btnexport_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
SqlConnection objcon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connstr"].ToString());
SqlDataAdapter objda = new SqlDataAdapter("select * from attendance", objcon);
objda.Fill(dt);
gvreport.DataSource = dt;
gvreport.DataBind();
ExportToExcel("Report.xls", gvreport);
gvreport = null;
gvreport.Dispose();
}

public override void VerifyRenderingInServerForm(Control control)
{
/* Confirms that an HtmlForm control is rendered for the specified ASP.NET
server control at run time. */
}

sam.198979 · Answer 3 · 2013-05-22T23:29:00

I tried this code it's working.
 

 private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection cnn;
            string connectionstring = null;
            string sql = null;
            string data = null;
            int i = 0;
            int j = 0;
 
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
 

            xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
 
            connectionstring ="Data Source=IN-WTS-SAM;Initial Catalog=MSNETDB;Integrated Security=True;Pooling=False";
            cnn = new SqlConnection(connectionstring);
            cnn.Open();
            sql = "SELECT * FROM Emp";
            SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
            DataSet ds = new DataSet();
            dscmd.Fill(ds);
 
            for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
            {
                for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
                {
                    data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                    xlWorkSheet.Cells[i + 1, j + 1] = data;
                }
            }
 

            xlWorkBook.SaveAs("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 D:\\Sam-informations.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();
            }
 
        }
    }

 

But in Excel it's displaying only columns data only.
 
I Want with column names also in excel file.
 
can anyone help.

Thanks
sam.198979

Hitesh Gohel · Answer 4 · 2013-05-23T01:14:00

Hello ,
Please try this code as below..

ASP.NET

<asp:button id="btnExport" runat="server" text="Export to Excel" xmlns:asp="#unknown">
    onclick="btnExport_Click" /></asp:button>

And

C#

protected void btnExport_Click(object sender, EventArgs e)
    {
        SqlConnection cnn;
        string connectionstring = null;
        string sql = null;

        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=Members.xls");

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

        connectionstring ="Data Source=IN-WTS-SAM;Initial Catalog=MSNETDB;Integrated Security=True;Pooling=False";
            cnn = new SqlConnection(connectionstring);
            cnn.Open();
            sql = "SELECT * FROM Emp";
            SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
            DataSet ds = new DataSet();
            dscmd.Fill(ds);

        Repeater Repeater1 = new Repeater();
        Repeater1.DataSource = ds;
        Repeater1.HeaderTemplate = new MyTemplate(ListItemType.Header, null);
        Repeater1.ItemTemplate = new MyTemplate(ListItemType.Item, ds);
        Repeater1.FooterTemplate = new MyTemplate(ListItemType.Footer, null);
        Repeater1.DataBind();

        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        Repeater1.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }

    public class MyTemplate : ITemplate
    {
        ListItemType type;
        LiteralControl lc;
        DataSet ds;
        static int ctr;
        public MyTemplate(ListItemType type, DataSet ds)
        {
            this.type = type;
            this.ds = ds;
            ctr = 0;
        }
        public void InstantiateIn(Control container)
        {
            switch (type)
            {
                case ListItemType.Header:
                    lc = new LiteralControl("<table border="1"><tr><th>User name</th><th>Email-Id</th></tr>");
                    break;
                case ListItemType.Item:
                    lc = new LiteralControl("<tr><td>" + ds.Tables[0].Rows[ctr]["UserName"] + "</td><td>" + ds.Tables[0].Rows[ctr]["Email"] + "</td></tr>");
                    ctr++;
                    break;
                case ListItemType.Footer:
                    lc = new LiteralControl("</table>");
                    ctr = 0;
                    break;
            }
            container.Controls.Add(lc);
        }
    }

sam.198979 · Answer 5 · 2013-05-23T21:05:00

C#

private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection cnn;
            string connectionstring = null;
            string sql = null;
            string data = null;
            int i = 0;
            int j = 0;

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;


            xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            connectionstring ="Data Source=HostName;Initial Catalog=MSNETDB;Integrated Security=True;Pooling=False";
            cnn = new SqlConnection(connectionstring);
            cnn.Open();
            sql = "SELECT * FROM Emp";
            SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
            DataSet ds = new DataSet();
            dscmd.Fill(ds);

            for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
            {
                for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
                {
                    data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                    xlWorkSheet.Cells[i + 1, j + 1] = data;
                }
            }
            
            xlApp.Visible = true;
}

But i want data with column name in excel file.
which is same as database table.

can any one help me.
Thanks
sam.198979

Member 11194584 · Answer 6 · 2015-02-26T23:57:00

I am Solve Problem Of Column Name Here Please Use This Code & Get Perfect Solution of Your Question.

private void Button1_Click(object sender, EventArgs e)
      {
          try {
          SqlConnection cnn;
          string connectionString = null;
          string sql = null;
          string data = null;
          int i = 0;
          int j = 0;

          Excel.Application xlApp;
          Excel.Workbook xlWorkBook;
          Excel.Worksheet xlWorkSheet;
          object misValue = System.Reflection.Missing.Value;

          xlApp = new Excel.Application();
          xlWorkBook = xlApp.Workbooks.Add(misValue);
          xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

          connectionString = "Data Source=Yashodhan-PC;Initial Catalog=SalesManagement;Persist Security Info=True;User ID=sa;Password=sql";
          cnn = new SqlConnection(connectionString);
          cnn.Open();
          sql = "SELECT * FROM SalesOrder";
          SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
          DataSet ds = new DataSet();
          dscmd.Fill(ds);

          foreach (DataTable dt in ds.Tables)
          {
              for (int i1 = 0; i1 < dt.Columns.Count; i1++)
              {
                  xlWorkSheet.Cells[1, i1 + 1] = dt.Columns[i1].ColumnName;
              }
          }

          for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
          {
              int s = i + 1;
              for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
              {
                  data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                  xlWorkSheet.Cells[s + 1, j + 1] = data;
              }
          }

          xlWorkBook.SaveAs("SaleOrder.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:\\User\\UserName\\MyDocuments\\SaleOrder.xls.xls");
          }
          catch (Exception Ex)
          {
              MessageBox.Show("Please Move Your SaleOrder.xls and Retry Again...........");

          }
      }

How to Export Database table data into excel file in C# on button click?

6 solutions

Solution 5

Solution 1

Solution 2

Solution 3

Solution 4

Solution 6

Add your solution here

Preview 0