Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

C#
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;
            }
        }
Posted
Updated 28-Apr-14 19:51pm
v2

Start with the obvious ones: is Excel installed on the server? Probably not, in which case that code will not work - because it uses the Excel installation to do the job...
 
Share this answer
 
thanks for the suggestion ...but office 2007 is already installed on server.....and i solved my problem witth other code...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900