export dataset to excel in c#

Question

I m having function which export data of dataset to an excel sheet. it is working fine on local machine but whn i upload ths code to server it not work ....

code behind file:

    using Excel = Microsoft.Office.Interop.Excel;
    using System.Reflection;
    using System.Drawing;
    using System.ComponentModel;
    using System.Windows.Forms;

     string FilePath = ConfigurationManager.AppSettings["dataqueryfile"]
                 + "dataquery_" + DateTime.Now.ToString("yyyyMMddhhmm") ;


                Excel.Application oXL;
                Excel.Workbook oWB;
                Excel.Worksheet oSheet;
                Excel.Range oRange;

                // Start Excel and get Application object. 
                oXL = new Excel.Application();

                // Set some properties 
                oXL.Visible = true;
                oXL.DisplayAlerts = false;

                // Get a new workbook. 
                oWB = oXL.Workbooks.Add(Missing.Value);


                // Get the active sheet 
                oSheet = (Excel.Worksheet)oWB.ActiveSheet;
                oSheet.Name = "DataQuery";

                // Process the DataTable 
                DataTable dt = ds.Tables[0];

                int rowCount = 1;
                foreach (DataRow dr in dt.Rows)
                {
                    rowCount += 1;
                    for (int i = 1; i < dt.Columns.Count + 1; i++)
                    {
                        // Add the header the first time through 
                        if (rowCount == 2)
                        {
                            oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                        }
                        oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                    }
                }

                // Resize the columns 
                oRange = oSheet.get_Range(oSheet.Cells[1, 1],
                              oSheet.Cells[rowCount, dt.Columns.Count]);
                oRange.EntireColumn.AutoFit();

                // Save the sheet and close 
                oSheet = null;
                oRange = null;


                oWB.SaveAs(FilePath + ".xls", Excel.XlFileFormat.xlWorkbookNormal,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Excel.XlSaveAsAccessMode.xlExclusive,
                    Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value);
                oWB.Close(Missing.Value, Missing.Value, Missing.Value);
                oWB = null;
                oXL.Quit();

                // Clean up 
                // NOTE: When in release mode, this does the trick 
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();

web config file :

      <add key="dataqueryfile" value="C:\navin\"/>
      <add assembly="Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C"/>
  <add assembly="office, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C"/>
            <add assembly="Microsoft.Vbe.Interop, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C"/>
            <add assembly="stdole, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>

edited :

                da = new SqlDataAdapter();
                conn.Open();
                da.SelectCommand = command;
                da.Fill(ds);
                ds.WriteXml("c:\\Customers.xml");

when i execute solution from the local system the data set values are copied to the xml file along with column header..then i moved the files to virtual directory under inetpub folder. through iis manager i browsed the virtual directory and run the same page , the xml file is getting saved in the same path but without the column header :(.. then i opened the same page from other machine through URL (thru inetrnal ip address) but the xml file is not saving to the machine.. plz help me out..

thanx n regards

T.Navin

Answer 1

Automating Word or Excel on services is strongly discouraged, but you can read some considerations about it here. First of all you could try setting your service to run as Local System. Other thing to play with is to find the Excel in the:

Control Panel - Administrative Tools - Component services - Component services - My computer - DCOM Config

There you can set the priviledges under which the Excel can run, etc...

But, I have to say, even with everything setup properly it can still turn out not working. We had intense difficulties with this, with some server working, and the exact same configuration not working on some other server...

So, what I would suggest (since your need doesn't really require automation) is to create Excel files yourself using XSLT to create SpreadSheetML - it's actually more easy than you'll think at first, but it's really a goood way to go. Try saving your XLS file in an Office XML 2003 format, and see the structure of the XML - if you look at the body it's really not that complicated. When you create these XML's the Excel can open it just like any other file.

Another way (if you don't like using XSL Transformations) is to use Open XML Format SDK - we found that one to be a bit tedious to implement for Word (we needed some nuances) so we still prefer the XSLT's, but for Excel it could prove to be quite easy. With this SDK you can even save as XSLX/DOCX formats (which is actually very similar to ProcessingML, but packed into a zip)

Hope it helps.