Click here to Skip to main content
15,882,063 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+

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!
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 ;)

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