none
C# - Creating an Excel File using ASP.NET

    Question

  • I have referenced Interop.Excel.dll, Microsoft.Vbe.Interop.dll, and Office.dll which ae part of the COM Component Microsoft Excel 5.0 Object Library.

     

    I created software that works fine on my development machine:

     

    Excel.Application oXL;

    Excel._Workbook oWB;

    Excel._Worksheet oSheet;

    GC.Collect();// clean up any other excel guys hangin' around...

    oXL = new Excel.Application();

    //Get a new workbook.

    oWB = (Excel._Workbook)(oXL.Workbooks.Add(template));

    oSheet = (Excel._Worksheet)oWB.ActiveSheet;

     

    When I push the software to my server, the message below is thrown:

    Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154.

    Line 955:
    Line 956:        GC.Collect();// clean up any other excel guys hangin' around...
    Line 957:        oXL = new Excel.Application();
    Line 958:        //Get a new workbook.
    Line 959:        oWB = (Excel._Workbook)(oXL.Workbooks.Add(template));

     

    Is there a doctor in the house?

     

    Any guidance is most appreciated.

     

    Mark

    Tuesday, February 19, 2008 9:27 PM

Answers

  • The easiest way to accomplish this is to use the GridView. You don't have to put one on a form, you can declare it in the Code Behind, populate it with the data, and then use the "RenderControl" method to generate the desired Excel File. No need to have Office installed on the server.

     

    Example code at

     

     

    http://aspalliance.com/771

    Tuesday, February 19, 2008 10:26 PM

All replies

  • The easiest way to accomplish this is to use the GridView. You don't have to put one on a form, you can declare it in the Code Behind, populate it with the data, and then use the "RenderControl" method to generate the desired Excel File. No need to have Office installed on the server.

     

    Example code at

     

     

    http://aspalliance.com/771

    Tuesday, February 19, 2008 10:26 PM
  •  

    Adrient, thank you.

     

    This is a much better option.

     

    Will implement tomorrow.

    Wednesday, February 20, 2008 1:56 AM
  • Hi,

    I would also recommend this C# / VB.NET Excel library, since it doesn't use Excel Automation so it is suitable for server applications.

    Here is a sample C# code how to import DataTable to Excel file and ASP.NET export to Excel:

    DataTable people = (DataTable)Session["people"];
     
    // Create excel file.
    ExcelFile ef = new ExcelFile();
    ExcelWorksheet ws = ef.Worksheets.Add("DataSheet");
    ws.InsertDataTable(people, "A1", true);
     
    Response.Clear();
     
    // Stream file to browser, in required type.
    switch (this.RadioButtonList1.SelectedValue)
    {
        case "XLS":
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment; filename=" +
                 "Report.xls");
            ef.SaveXls(Response.OutputStream);
            break;
     
        case "XLSX":
            Response.ContentType = "application/vnd.openxmlformats";
            Response.AddHeader("Content-Disposition", "attachment; filename=" +
                 "Report.xlsx");
            // With XLSX it is a bit more complicated as MS Packaging API
            // can't write directly to Response.OutputStream.
            // Therefore we use temporary MemoryStream.
            MemoryStream ms = new MemoryStream();
            ef.SaveXlsx(ms)
            ms.WriteTo(Response.OutputStream);
            break;
    }
    Response.End();

    Friday, April 13, 2012 8:09 AM