In my ASP.Net web application project, I've to fill up an existing Excel 2003 spreadsheet (.xls) with some data from database and send it to the user/ client. I'm using Excel Interop for this. So far, I've been able to fill up the spreadsheet and open it while running it under debug on Visual Studio 2013/ IIS-Express.
When I publish the same application on IIS running on Windows 2012 and access it over the Internet through a client, although the server does send the file to the client, it is not filled with any data. I checked the server logs and found the following exception getting thrown:
System.Runtime.InteropServices.COMException (0x80080005):
Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80080005 Server execution failed (Exception from HRESULT: 0x80080005 (CO_E_SERVER_EXEC_FAILURE)). at System.Runtime.Remoting.RemotingServices.AllocateUninitializedObject(RuntimeType objectType) ....
I've set the permissions for the website folder to allow the following accounts with full control:
Additionally, while reading about it, I've also set the DCOM config for the Excel application to set following permissions for the above accounts:
The application is set to use windows authentication (identity) and from the client machine, I'm using my account credentials to log into the application.
Here is the code I'm using to fill the Excel spreasheet:
public bool FillExcel()
{
bool result = false;
try
{
string newName = "stage 2.xls";
if (!Directory.Exists(Server.MapPath("~") + @"\Files\xls\"))
Directory.CreateDirectory(Server.MapPath("~") + @"\Files\xls\");
if (System.IO.File.Exists(Server.MapPath("~") + @"\Files\xls\" + newName))
{
System.IO.File.Delete(Server.MapPath("~") + @"\Files\xls\" + newName);
}
System.IO.File.Copy(Server.MapPath("~") + @"\Files\stage2Copy.xls", Server.MapPath("~") + @"\Files\xls\" + newName);
List<Sheet1Model> sheet1ModelList = getSheet1Data(); // Model to fill the data from database to be filled into Excel
Application excelApplication = new Application();
Workbook wb = excelApplication.Workbooks.Open(Server.MapPath("~") + @"\Files\xls\" + newName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Worksheet sheet = (Worksheet)wb.Sheets["Sheet1"];
sheet.Select(Type.Missing);
Range excelRange = sheet.UsedRange;
int rNumber = 3;
string[] firstWorksheetHeaders = GetRange("A" + rNumber + ":HH" + rNumber + "", sheet);
int rn = 4; // start from row 4 in Excel
foreach (Sheet1Model sheetModel in sheet1ModelList)
{
sheet.Cells[rn, 1] = sheetModel.CustomerName
rn++;
}
String Path = Server.MapPath("~") + @"Files\xls\" + newName;
wb.SaveAs(Path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.Close();
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(excelApplication);
result = true;
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch (Exception ex)
{
logger.Info(ex.ToString());
}
return result;
}
How can I remove this error and get the excel sheet populated?
User contributions licensed under CC BY-SA 3.0