2
Answers

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

Ask a question
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 == "&nbsp;")
                            {
                                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;
            }
        }

Answers (2)