Runtime Error - Export Excel using C# from IIS

Question

When I run on debugging mode on my local machine, there is no problems. I can export to excel...

private void ExportToExcel(string str)
    {
        _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 = "PO_Status";

        // Process the DataTable
        int rowCount = 1;
        foreach (DataRow dr in dtStatus(str).Rows)
        {
            rowCount += 1;
            for (int i = 1; i < dtStatus(str).Columns.Count + 1; i++)
            {
                // Add the header the first time through
                if (rowCount == 2)
                {
                    oSheet.Cells[1, i] = dtStatus(str).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, dtStatus(str).Columns.Count]);
        oRange.EntireColumn.AutoFit();

        try
        {
            // Save the sheet and close
            oSheet = null;
            oRange = null;
            oWB.SaveAs("POStatus.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();
        }
        catch (Exception ex)
        {
            Session["error"] = ex.Message;
            Response.Redirect("MessageBoard.aspx");
        }
    }

    private DataTable dtStatus(string str)
    {
        DataTable dt = new DataTable();
        SqlCommand cmd = new SqlCommand(@"Select " + str + ""
                            + " From tbl_RFI Full Join"
                            + " tbl_RFQ On tbl_RFI.RFINo = tbl_RFQ.RFINo Full Join"
                            + " tbl_NNB On tbl_RFQ.RFQNo = tbl_NNB.RFQNo Full Join"
                            + " tbl_PO On tbl_PO.NNBNo = tbl_NNB.NNBNo"
                            + " Where tbl_RFI.JobNo = '" + ddlJobNo.SelectedValue.ToString().Trim() + "'", connPMis);

        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        adp.Fill(dt);

        return dt;
    }
}

But when I run on IIS, it's not working... I got error message like this

Runtime Error Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.

Details: To enable the details of this specific error message to be viewable on remote machines, please create a tag within a "web.config" configuration file located in the root directory of the current web application. This tag should then have its "mode" attribute set to "Off".

Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's configuration tag to point to a custom error page URL.

My Server is Windows Server 2008/ IIS7 thx for your help

Answer 1

go into your application and in your web.config and change your <customErrors element to mode="Off" and try again and post the real error message here. The other thing you can do is login to the IIS machine and run it there and see the full error message.

It could be be permissions in saving the excel file, or it could be an error launching excel but we need to see the error message here. Make sure that the location you are saving the file to has permissions to the Network Service account (or whatever account you happen to have your application running under as its app pool account in iis)

See the following link for many many other (better) ways of doing this.

Create Excel (.XLS and .XLSX) file from C#

Answer 2

1. Excel needs to be installed on the server for this to work.

2. An alternative way to see the full exception is if you have access to the server desktop, you can navigate to your page from the server and because it is local you'll get the full stack trace.

3. This is an expressly discouraged approach to working with Excel on the server, says Microsoft: http://support.microsoft.com/kb/257757. The preferred approach is to work with a library/dll that can read and/or manipulate xls and xlsx files without launching and automating some other application. There are free libraries, like NPOI, and commercial products like SpreadsheetGear and Aspose. You have a lot more options if you limit usage to xlsx.

4. If you DO use this interop approach:

   a. You need to more carefully assign all items to variables for later cleanup. For example, each time you do oSheet.Cells[1, i] in a loop, you leak a cell reference.

   b. Each resource that you assign must be cleaned up by calling Marshal.ReleaseComObject(theVariable) when you are through with it. THESE THINGS ARE NOT GARBAGE COLLECTED, so GC.Anything will have no effect.

   c. Your try…catch redirects without cleaning up any resources that were acquired before the exception or closing Excel, which means every time an exception is encountered in this method, you are liable to have a phantom Excel instance running in the background. You'll probably need to use try…catch…finally and do all of your cleanup in the finally block.