Click here to Skip to main content
15,915,674 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,
C#
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);
        con.Open();
        sql = "Select * from mis";
        SqlDataAdapter da = new SqlDataAdapter(sql, con);
        DataSet ds = new DataSet();
        da.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;
            }
        }

        MessageBox.Show("Excel file created , you can find the file C:\\Documents and Settings\\narasiman\\Desktop\\Excel\\export.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();
            }
        }
    }

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.

Thanks,
Regards,
Narasiman P.
Posted
Updated 28-Aug-12 4:41am
v2
Comments
Legor 28-Aug-12 10:46am    
This is not a question, it's a request and nobody here will send you code.
[no name] 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+

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:
C#
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:
C#
xlWorkBook = xlapp.Workbooks.Open("C:\\Documents and Settings\\narasiman\\Desktop\\Excel\\export.xls");

and then call method
C#
xlWorkBook.Save;


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".
 
Share this answer
 
Comments
Abhinav S 28-Aug-12 12:11pm    
Correct. 5.
Maciej Los 28-Aug-12 15:39pm    
Thank you, Abhinav ;)
You are not saving work book. In message box you just said saved, but actually its not.

write code for save.

C#
book.SaveAs(@"C:\Documents and Settings\narasiman\Desktop\Excel\export.xls.");  // or book.Save();
            book.Close();
 
Share this answer
 
Comments
Abhinav S 28-Aug-12 12:12pm    
5!
Maciej Los 28-Aug-12 15:40pm    
Yes, SaveAs method is correct. My 5!

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