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;
}
}