How to create Excel file in asp.net

Question

I want to create Excel Sheet in asp.net. I have already done code for this and it is working fine in local system but when I am testing it on server then it gives the following error.

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

My code is.

private static System.Data.DataTable GetDataSet()
    {
        System.Data.DataTable dt = new System.Data.DataTable("Table");
        dt.Columns.Add("Name", Type.GetType("System.String"));
        dt.Columns.Add("Address", Type.GetType("System.String"));
        dt.Columns.Add("Phone", Type.GetType("System.String"));

        DataRow dr = dt.NewRow();
        dr["Name"] = "Balaji Selvarajan";
        dr["Address"] = "Reddiyur";
        dr["Phone"] = "000-000-0000";
        dt.Rows.Add(dr);

        dr = dt.NewRow();
        dr["Name"] = "Balaji S";
        dr["Address"] = "Kattumannar Koil";
        dr["Phone"] = "000-000-0000";
        dt.Rows.Add(dr);
        return dt;
    }


    private static void DataSetToExcel(System.Data.DataTable dt, Boolean generateIdentity)
    {
        try
        {
            string Filename = HttpContext.Current.Server.MapPath("~/Doc/") + "Test.xls";
            string imagepath1 = HttpContext.Current.Server.MapPath("~/Doc/") + "Test.xls";
            FileInfo fi = new FileInfo(HttpContext.Current.Server.MapPath("~/Doc/") + "Test.xls");
            if (fi.Exists)
            {
                fi.Delete();
            }
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlApp.Visible = false;
            Microsoft.Office.Interop.Excel.Workbook wb = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            object misValue = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.Add(misValue, misValue, misValue, misValue);
            ws.Name = "Test";

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (i == 0)
                        ws.Cells[1, j + 1] = dt.Columns[j].ColumnName;
                    ws.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
                }
                ws.Protect("1235", true, true, true, true, true, true, true, true, true, true, true, true, true, true, true);
            }
            wb.Protect("my", true, true);
            wb.Password = "1235";
            wb.SaveAs(Filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, "1235", misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            wb.Close(true, misValue, misValue);
            xlApp.Visible = true;
            xlApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
            HttpContext.Current.Response.ContentType = "Application/Excel";
            string FilePath = imagepath1;
            HttpContext.Current.Response.WriteFile(FilePath);
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=Test.xls");
            HttpContext.Current.Response.End();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    protected void BtnDn_Click(object sender, EventArgs e)
    {
        DataSetToExcel(GetDataSet(), false);
    }

I am not able to solve this problem please help me. and give me solution as soon as possible please.

I am using Asp.net C#.

Answer 1

Error 80070005 is an Access Denied error. Try configuring your AppPool to run under LocalSystem (instead of ApplicationPoolIdentity), or try granting permissions for Excel in Component Services:

1. From the Start menu, click Run and type Dcomcnfg.exe.
2. In Component Services, click Console root, expand Component Services, expand Computers, expand My computer,expand DCOMConfig.
3. Search for Microsoft Word 14.0 Object Library. Click on it.
4. Right click and select Properties.
5. On security tab, select Customize in "Launch and Activation" section.
6. Click edit and add identity of app pool under which you application is running.
7. Repeat above step for "Access Permission"

AppPool account solution is from here: "Retrieving the COM class factory for component.... error: 80070005 Access is denied." (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).

Component Services Permission answer is from here: Retrieving the COM class factory for component with CLSID failed due to the following error: 80070005 Access is denied.

Answer 2

Try with this

     using System;  
     using System.Collections.Generic;   
     using System.Data;  
     using System.Linq;  
     using System.Text;  
     using System.Threading.Tasks; 
     using Office = Microsoft.Office.Core;  
     using Excel = Microsoft.Office.Interop.Excel;

     namespace Excel   
     {   
         public class ExcelUtlity   
          {                      

    public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)        {
     Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
    // Start Excel and get Application object.
   excel = new Microsoft.Office.Interop.Excel.Application();
    // for making Excel visible
    excel.Visible = false;
    excel.DisplayAlerts = false;
    // Creation a new Workbook
    excelworkBook = excel.Workbooks.Add(Type.Missing);
    // Workk sheet             
    excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
    excelSheet.Name = worksheetName;
    excelSheet.Cells[1, 1] = ReporType;
    excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();               
    // loop through each row and add values to our sheet
    int rowcount = 2;
    foreach (DataRow datarow in dataTable.Rows)
    {
        rowcount += 1;
        for (int i = 1; i <= dataTable.Columns.Count; i++)
        {
            // on the first iteration we add the column headers
            if (rowcount == 3)
            {
                excelSheet.Cells[2, i] = dataTable.Columns[i-1].ColumnName;
                excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
            }
            excelSheet.Cells[rowcount, i] = datarow[i-1].ToString();
            //for alternate rows
            if (rowcount > 3)
            {
                if (i == dataTable.Columns.Count)
                {
                    if (rowcount % 2 == 0)
                    {
                        excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
                        FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black,false);
                    }
                }
            }
        }
    }
    // now we resize the columns
    excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
    excelCellrange.EntireColumn.AutoFit();
    Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
    border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
    border.Weight = 2d;
    excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
    FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
    //now save the workbook and exit Excel
    excelworkBook.SaveAs(saveAsLocation);;
    excelworkBook.Close();
    excel.Quit();
    return true;
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
    return false;
}
finally
{
    excelSheet = null;
    excelCellrange = null;
    excelworkBook = null;
}
 }       
    /// FUNCTION FOR FORMATTING EXCEL CELLS

public void FormattingExcelCells(Microsoft.Office.Interop.Excel.Range range, string HTMLcolorCode, System.Drawing.Color fontColor, bool IsFontbool)
  {
      range.Interior.Color=System.Drawing.ColorTranslator.FromHtml(HTMLcolorCode);
range.Font.Color = System.Drawing.ColorTranslator.ToOle(fontColor);
if (IsFontbool == true)
{
    range.Font.Bold = IsFontbool;
}
   }

  }