How to download Excel object in ASP.Net

Question

Please consider this code:

Microsoft.Office.Interop.Excel.Application objApp;
    Microsoft.Office.Interop.Excel._Workbook objBook;
    Microsoft.Office.Interop.Excel.Workbooks objBooks;
    Microsoft.Office.Interop.Excel.Sheets objSheets;
    Microsoft.Office.Interop.Excel._Worksheet objSheet;
    Microsoft.Office.Interop.Excel.Range range;

    int ColumnsCount = dt.Columns.Count;
    int RowsCount = dt.Rows.Count;

    objApp = new Microsoft.Office.Interop.Excel.Application();
    objBooks = objApp.Workbooks;
    objBook = objBooks.Add(System.Reflection.Missing.Value);
    objSheets = objBook.Worksheets;
    objSheet = (Microsoft.Office.Interop.Excel._Worksheet)objSheets.get_Item(1);

    for (int i = 1; i < ColumnsCount + 1; i++)
    {
        objSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
    }

    range = objSheet.get_Range("A2", System.Reflection.Missing.Value);
    range = range.get_Resize(RowsCount, ColumnsCount);

    string[,] saRet = new string[RowsCount, ColumnsCount];

    for (int iRow = 0; iRow < RowsCount; iRow++)
    {
        for (int iCol = 0; iCol < ColumnsCount; iCol++)
        {
            saRet[iRow, iCol] = dt.Rows[iRow][iCol].ToString();
        }
    }

    //Set the range value to the array.
    range.set_Value(Missing.Value, saRet);

I want to download Excel object that I created. The problem is Microsoft.Office.Interop.Excel._Workbook is not serializable and until I save it I can't access Excel file. How I can download Excel that created in memory?

thanks

Answer 1

This seems like one of the many problems related to Microsoft KB: Considerations for server-side Automation of Office. Using Excel Interop server-side isn't supported.

I'd recommend you export csv files or look into OpenXML or the easier version ClosedXML to generate xslx files on the server.

See the other methods in this MSDN guide: How To Asp.Net Export Excel