I have created the export to Excel utility. It is workini on

I have created a Export to Excel utility. It is working fine on the local development machine but when I deployed it on the Windows server 2008 R2 It is not working i.e. it is not doing anything i.e. it is not showing any exception. I have searched it on the google as well. as from the google search there is some permission issue for the com component of excel. I have tried many things but could not succeed. The problem is still not resolved.

Please help me.

The Code is as Follows.

public static void ExportToExcel(BusinessLogic.Employee_Report objEmployee_Report, GridView gvReport, Page objPage)

{

try

{

if (objEmployee_Report.strReportFormat == "EXCEL")

{

int i = 0;

int j = 0;

int k = 0;

string data = string.Empty;

Microsoft.Office.Interop.Excel.Application xlApp;

Microsoft.Office.Interop.Excel.Workbook xlWorkBook;

Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;

object misValue = System.Reflection.Missing.Value;

xlApp = new Microsoft.Office.Interop.Excel.Application();

xlApp.Visible = false;

xlWorkBook = (Microsoft.Office.Interop.Excel.Workbook)(xlApp.Workbooks.Add(System.Reflection.Missing.Value));

xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.ActiveSheet;

Microsoft.Office.Interop.Excel.Range picPosition = xlWorkSheet.Cells[1, 1]; // retrieve the range for picture insert

Microsoft.Office.Interop.Excel.Pictures p = xlWorkSheet.Pictures(System.Reflection.Missing.Value) as Microsoft.Office.Interop.Excel.Pictures;

Microsoft.Office.Interop.Excel.Picture pic = p.Insert(System.Configuration.ConfigurationManager.AppSettings["LogoLocation"], true);

pic.Left = Convert.ToDouble(picPosition.Left);

pic.Top = picPosition.Top;

pic.Height = 45;

pic.Placement = Microsoft.Office.Interop.Excel.XlPlacement.xlMoveAndSize;

Microsoft.Office.Interop.Excel.Range rangeHead = xlWorkSheet.get_Range("A14", "Z1");

Microsoft.Office.Interop.Excel.Range rangeLogo = xlWorkSheet.get_Range("A1", "Z1");

Microsoft.Office.Interop.Excel.Range range = xlWorkSheet.get_Range("A4", "Z1000");

xlWorkSheet.Cells[1, 4] = System.Configuration.ConfigurationManager.AppSettings["OrganizationName"];

xlWorkSheet.Cells[1, 4].EntireRow.Font.Bold = true;

xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 5]].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

xlWorkSheet.Range[xlWorkSheet.Cells[1, 4], xlWorkSheet.Cells[1, 5]].Merge();

xlWorkSheet.Cells[2, 4] = objEmployee_Report.strReportName;

xlWorkSheet.Cells[2, 4].EntireRow.Font.Bold = true;

xlWorkSheet.Range[xlWorkSheet.Cells[2, 4], xlWorkSheet.Cells[2, 5]].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

xlWorkSheet.Range[xlWorkSheet.Cells[2, 4], xlWorkSheet.Cells[2, 5]].Merge();

if (objEmployee_Report.From_Date != DateTime.MinValue)

{

xlWorkSheet.Cells[6, 1] = "From Date: " + Convert.ToDateTime(objEmployee_Report.From_Date).ToString("dd/MM/yyyy");

xlWorkSheet.Cells[6, 1].EntireRow.Font.Bold = true;

xlWorkSheet.Range[xlWorkSheet.Cells[6, 1], xlWorkSheet.Cells[6, 2]].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

xlWorkSheet.Range[xlWorkSheet.Cells[6, 1], xlWorkSheet.Cells[6, 2]].Merge();

xlWorkSheet.Cells[7, 1] = "To Date: " + Convert.ToDateTime(objEmployee_Report.To_Date).ToString("dd/MM/yyyy");

xlWorkSheet.Cells[7, 1].EntireRow.Font.Bold = true;

xlWorkSheet.Range[xlWorkSheet.Cells[7, 1], xlWorkSheet.Cells[7, 2]].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

xlWorkSheet.Range[xlWorkSheet.Cells[7, 1], xlWorkSheet.Cells[7, 2]].Merge();

}

if (objEmployee_Report.From_month > 0)

{

string FromMonth = (Convert.ToDateTime(objEmployee_Report.From_month + "-01" + "-2011").ToString("MMMM"));

string ToMonth = (Convert.ToDateTime(objEmployee_Report.To_Month + "-01" + "-2011").ToString("MMMM"));

xlWorkSheet.Cells[6, 1] = "From Month: " + FromMonth;

xlWorkSheet.Cells[6, 1].EntireRow.Font.Bold = true;

xlWorkSheet.Range[xlWorkSheet.Cells[6, 1], xlWorkSheet.Cells[6, 2]].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

xlWorkSheet.Range[xlWorkSheet.Cells[6, 1], xlWorkSheet.Cells[6, 2]].Merge();

xlWorkSheet.Cells[7, 1] = "To Month: " + ToMonth;

xlWorkSheet.Cells[7, 1].EntireRow.Font.Bold = true;

xlWorkSheet.Range[xlWorkSheet.Cells[7, 1], xlWorkSheet.Cells[7, 2]].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

xlWorkSheet.Range[xlWorkSheet.Cells[7, 1], xlWorkSheet.Cells[7, 2]].Merge();

}

xlWorkSheet.Cells[5, 1] = "Search By: " + objEmployee_Report.Flag_Value;

xlWorkSheet.Cells[5, 1].EntireRow.Font.Bold = true;

xlWorkSheet.Range[xlWorkSheet.Cells[5, 1], xlWorkSheet.Cells[5, 2]].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

xlWorkSheet.Range[xlWorkSheet.Cells[5, 1], xlWorkSheet.Cells[5, 2]].Merge();

if (objEmployee_Report.Flag_Value == "Zone")

{

xlWorkSheet.Cells[6, 1] = "Zone Name: " + objEmployee_Report.Zone_Name;

xlWorkSheet.Cells[6, 1].EntireRow.Font.Bold = true;

xlWorkSheet.Range[xlWorkSheet.Cells[6, 1], xlWorkSheet.Cells[6, 2]].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

xlWorkSheet.Range[xlWorkSheet.Cells[6, 1], xlWorkSheet.Cells[6, 2]].Merge();

}

if (objEmployee_Report.Flag_Value == "Region")

{

xlWorkSheet.Cells[6, 1] = "Region Name: " + objEmployee_Report.Region_Name;

xlWorkSheet.Cells[6, 1].EntireRow.Font.Bold = true;

xlWorkSheet.Range[xlWorkSheet.Cells[6, 1], xlWorkSheet.Cells[6, 2]].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

xlWorkSheet.Range[xlWorkSheet.Cells[6, 1], xlWorkSheet.Cells[6, 2]].Merge();

}

if (objEmployee_Report.Flag_Value == "Division")

{

xlWorkSheet.Cells[6, 1] = "Division Name: " + objEmployee_Report.Division_Name;

xlWorkSheet.Cells[6, 1].EntireRow.Font.Bold = true;

xlWorkSheet.Range[xlWorkSheet.Cells[6, 1], xlWorkSheet.Cells[6, 2]].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

xlWorkSheet.Range[xlWorkSheet.Cells[6, 1], xlWorkSheet.Cells[6, 2]].Merge();

}

if (objEmployee_Report.Flag_Value == "Branch")

{

xlWorkSheet.Cells[6, 1] = "Branch Name: " + objEmployee_Report.Branch_Name;

xlWorkSheet.Cells[6, 1].EntireRow.Font.Bold = true;

xlWorkSheet.Range[xlWorkSheet.Cells[6, 1], xlWorkSheet.Cells[6, 2]].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

xlWorkSheet.Range[xlWorkSheet.Cells[6, 1], xlWorkSheet.Cells[6, 2]].Merge();

}

if (objEmployee_Report.Flag_Value == "Education")

{

xlWorkSheet.Cells[6, 1] = "Branch Name: " + objEmployee_Report.Education_Name;

xlWorkSheet.Cells[6, 1].EntireRow.Font.Bold = true;

xlWorkSheet.Range[xlWorkSheet.Cells[6, 1], xlWorkSheet.Cells[6, 2]].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

xlWorkSheet.Range[xlWorkSheet.Cells[6, 1], xlWorkSheet.Cells[6, 2]].Merge();

}

if (!string.IsNullOrEmpty(objEmployee_Report.Department_Name))

{

xlWorkSheet.Cells[8, 1] = "Department Name: " + objEmployee_Report.Department_Name;

xlWorkSheet.Cells[8, 1].EntireRow.Font.Bold = true;

xlWorkSheet.Range[xlWorkSheet.Cells[8, 1], xlWorkSheet.Cells[8, 2]].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

xlWorkSheet.Range[xlWorkSheet.Cells[8, 1], xlWorkSheet.Cells[8, 2]].Merge();

}

if (!string.IsNullOrEmpty(objEmployee_Report.Designation_Name))

{

xlWorkSheet.Cells[8, 1] = "Designation Name: " + objEmployee_Report.Designation_Name;

xlWorkSheet.Cells[8, 1].EntireRow.Font.Bold = true;

xlWorkSheet.Range[xlWorkSheet.Cells[8, 1], xlWorkSheet.Cells[8, 2]].Style.HorizontalAlignment = HorizontalAlign.Left;

xlWorkSheet.Range[xlWorkSheet.Cells[8, 1], xlWorkSheet.Cells[8, 2]].Merge();

}

for (i = 0; i < gvReport.HeaderRow.Cells.Count; i++)

{

data = gvReport.HeaderRow.Cells[i].Text;

xlWorkSheet.Cells[10, k + 1] = data;

xlWorkSheet.Cells[10, k + 1].EntireRow.Font.Bold = true;

k++;

}

for (i = 0; i <= gvReport.Rows.Count - 1; i++)

{

int l = 0;

for (j = 0; j <= gvReport.HeaderRow.Cells.Count - 1; j++)

{

data = gvReport.Rows[i].Cells[j].Text;

if (data == " ")

{

data = "";

}

xlWorkSheet.Cells[i + 11, l + 1] = data;

l++;

}

range.Columns.AutoFit();

rangeLogo.Columns.AutoFit();

xlWorkBook.Close(true, misValue, misValue);

xlApp.Quit();

}

catch (Exception ex)

{

throw ex;

}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

ashutosh kumar gupta12y ago
I am getting this error.

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))
Lakshmanan Sethu Sankaranarayan12y ago
Hi, Please post the error message