I have an C# .Net MVC Application. I have done code for Export to Excel for Exporting data stored in datatable. I have stored the datatable in session until user press export button. When user press on export I fetch data from datatable via Ajax Call and export it. I have also kept a message when data exported successfully.
The problem is its works fine while debugging the application, but when I deploy the application it doesn't works. But it shows success message instead of error message. The other ajax calls in application for fetching data or storing data works fine.
I tried to debug the deployed application; exception occurs at line
Excel.Application excelApp = new Excel.Application();
the exception is
System.UnauthorizedAccessException was caught
Message=Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).
The Export to Excel function
public static void ExportExcel(this DataTable Tbl, string ExcelFilePath = null)
{
try
{
if (Tbl == null || Tbl.Columns.Count == 0)
//throw new Exception("ExportToExcel: Null or empty input table!\n");
Console.WriteLine("ExportToExcel: Null or empty input table!\n");
// load excel, and create a new workbook
Excel.Application excelApp = new Excel.Application();
excelApp.Workbooks.Add();
// single worksheet
Excel._Worksheet workSheet = excelApp.ActiveSheet;
// column headings
for (int i = 0; i < Tbl.Columns.Count; i++)
{
workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].ColumnName;
workSheet.Cells[1, (i + 1)].Font.Bold = true;
workSheet.Cells[1, (i + 1)].Font.Size = 12;
}
// rows
for (int i = 0; i < Tbl.Rows.Count; i++)
{
// to do: format datetime values before printing
for (int j = 0; j < Tbl.Columns.Count; j++)
{
workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
}
}
//int k = Tbl.Rows.Count;
// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
workSheet.SaveAs(ExcelFilePath);
excelApp.Quit();
//MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
//throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"+ ex.Message);
Console.WriteLine("ExportToExcel: Excel file could not be saved! Check filepath.\n"+ ex.Message);
}
}
else // no filepath is given
{
excelApp.Visible = true;
}
}
catch (Exception ex)
{
//throw new Exception("ExportToExcel: \n" + ex.Message);
Console.WriteLine("ExportToExcel: \n" + ex.Message);
}
}
This is code to call the function
public ActionResult JsonExportToExcel()
{
DataTable table = (DataTable)Session["filterCRMRequestDatatable"];
ExportToExcel.ExportExcel(table, "");
var result = "true";
return Json(result, JsonRequestBehavior.AllowGet);
}
The ajax call code
$("#ExportToExcel").click(function () {
//blur page
$('#loading_div').removeClass("loading-css-back").addClass("loading-css-front");
$('#loading_div').fadeIn();
$('#Full_page_content').removeClass("main-div-front").addClass("main-div-back");
$('#Full_page_content').fadeOut();
$.ajax({
url: '/FilterCRMRequest/JsonExportToExcel/',
type: "POST",
dataType: "json",
success: function (data) {
alert("Data Exported Successfully");
//active the page
$('#loading_div').removeClass("loading-css-front").addClass("loading-css-back");
$('#loading_div').fadeOut();
$('#Full_page_content').removeClass("main-div-back").addClass("main-div-front");
$('#Full_page_content').fadeIn();
},
error: function (xhr, props) {
if (xhr.status == 401) {
alert("Session Expired. Please Login Again");
window.location.href = "/CRMLogin/LogOn";
}
else {
alert("Sorry some Error occured. Please Try again.");
//active the page
$('#loading_div').removeClass("loading-css-front").addClass("loading-css-back");
$('#loading_div').fadeOut();
$('#Full_page_content').removeClass("main-div-back").addClass("main-div-front");
$('#Full_page_content').fadeIn();
}
}
});
});
I don't see you throwing any exception or returning any error code - that might be, why you see the success message still.
The access-denied can be a permission problem. Do you have MS Excel installed on the server where you are deploying the application? - Also I got an Access Denied after switching from Server 2003 to Server 2012 and couldn't resolve the issue anymore. Even I found several places, where you could change permissions for WebSites to access MS-Office applications, none of them helped.
In the end I found several articles, that you shouldn't use Office Interop on the server, because:
You might want to consider using a .net implementation of apache POI - NPOI. With this library you can manipulate Excel, Word and PowerPoint files. If you are ok with using the new xml-based Excel Files (xlsx) you can use also directly Microsofts OpenXML SDK or a wrapper around this library - SpreadsheetLight