Click here to Skip to main content
14,695,617 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
i am Exporting Database to Excel File

Code As Follows,
public partial class _Default : System.Web.UI.Page 
    SqlConnection con ;
    protected void Page_Load(object sender, EventArgs e)

    protected void Button1_Click(object sender, EventArgs e)
        con== new SqlConnection("Server=(local);initial catalog=Rental;Trusted_Connection=True");
        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.ApplicationClass();
        xlWorkBook = xlapp.Workbooks.Add(misvalue);
        xlWorksheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        //cnn = new SqlConnection(connectionstring);
        sql = "Select * from mis";
        SqlDataAdapter da = new SqlDataAdapter(sql, con);
        DataSet ds = new DataSet();

        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;

        MessageBox.Show("Excel file created , you can find the file C:\\Documents and Settings\\narasiman\\Desktop\\Excel\\export.xls");

        private void releaseObject(object obj)
                obj = null;
            catch (Exception ex)
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());

When i run the message shows as Excel file created , you can find the file C:\\Documents and Settings\\narasiman\\Desktop\\Excel\\export.xls.

But in the above mention path the excel file is not there.
please send me the code from the above code i send to you.

Narasiman P.
Updated 28-Aug-12 5:41am
Legor 28-Aug-12 10:46am
This is not a question, it's a request and nobody here will send you code.
Wes Aday 28-Aug-12 10:48am
As far as I can see you are not saving the excel file.
fjdiewornncalwe 28-Aug-12 11:12am
You should put that as an answer because that is the solution. Take the credit where credit it due.
Maciej Los 28-Aug-12 11:24am
Yes! My virtual 5+

You are not saving work book. In message box you just said saved, but actually its not.

write code for save.

book.SaveAs(@"C:\Documents and Settings\narasiman\Desktop\Excel\export.xls.");  // or book.Save();
Abhinav S 28-Aug-12 12:12pm
Maciej Los 28-Aug-12 15:40pm
Yes, SaveAs method is correct. My 5!
Wes Aday - wrote: "As far as I can see you are not saving the excel file." And he's absolutly right!

Because you create new workbook, after writing data, save the workbook, like this:
xlWorkBook.SaveAs("C:\\Documents and Settings\\narasiman\\Desktop\\Excel\\export.xls");

You can write data in to existing file, but you need to open it before:
xlWorkBook = xlapp.Workbooks.Open("C:\\Documents and Settings\\narasiman\\Desktop\\Excel\\export.xls");

and then call method

In the Button1_Click procedure i don't see the part of code, where you close an Excel.
Every created workbook exists in the memory (but you can't see it). Open Task Maneger (ALT+CTRL+DEL), find Excel application and choose "Go to" or "Kill process".
Abhinav S 28-Aug-12 12:11pm
Correct. 5.
Maciej Los 28-Aug-12 15:39pm
Thank you, Abhinav ;)

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900