Manj N

Manj N

  • NA
  • 148
  • 89k

problem in downloading the excel file through IIS8

Aug 16 2013 2:08 AM
when i run program locally it gives correct answer.. when i run on IIS it gives error...

error when download the excel page.... the error is 

The remote procedure call failed. (Exception from HRESULT: 0x800706BE)

the code is

protected void Expgo_Click(object sender, EventArgs e)
    {
        /*************************************
         * On export click, select date range 
         * Generate weekley TimeSheet Report
         ************************************/
        QueryRet Value = new QueryRet();
        String StartCalDate;
        String EndCalDate;
        StartCalDate = ExpCalStart.SelectedDate.ToString();
        EndCalDate = ExpCalEnd.SelectedDate.ToString();
        if (StartCalDate == EndCalDate)
        {
            Weekview.SetActiveView(Blank);
            lblerr.Text = "Please Select Range of Dates.";
            lblerr.Visible = true;
        }
        else
        {
            ExcelApp = new Excel.Application();
            ExcelApp.Visible = false;
            ExcelWork = (Excel._Workbook)(ExcelApp.Workbooks.Add(Missing.Value));
            string strCurrentDir = Server.MapPath("~/TimeSheets/");
            RemoveFiles(strCurrentDir);//removes all .xls files in directory
            DateTime Daylist;
            String Query;
            /*******************************************
             * Select all WeekStartDates in the given range of dates
            *********************************************/
            Query = "SELECT DISTINCT WeekStartDate FROM TimeSheetBreakDown";
            Query += " Where Date >= '" + Convert.ToDateTime(StartCalDate).ToString("yyyy/MM/dd") + "' AND Date<='" + Convert.ToDateTime(EndCalDate).ToString("yyyy/MM/dd") + "'";
            ArrayList DistinctStart = Value.ExecuteQry(Query);
            if (ExcelWork.Worksheets.Count <= DistinctStart.Count)
            {
                ExcelWork.Worksheets.Add(Missing.Value, Missing.Value, DistinctStart.Count - ExcelWork.Worksheets.Count + 1, Missing.Value);
            }
            for (int s = 0; s < DistinctStart.Count; s++)
            {
                /****************************************
                 * For each of the week which come under date selected
                 * Generate One Excel Work Sheet
                 ****************************************/
                try
                {
                    Query = "SELECT distinct Date FROM TimeSheetBreakDown WHERE (WeekStartDate = '" + DistinctStart[s] + "' AND Day='1') order by date";
                    Daylist = DateTime.Parse(Value.SelectQry(Query));
                    /******************************************
                     * From the Distinct StartDate Get (Date of) Monday of that week
                     * From Monday Calculate other Days From TimeSpan
                     * ****************************************/
                    mon = Daylist;
                    tue = Daylist + new TimeSpan(1, 0, 0, 0);
                    wed = Daylist + new TimeSpan(2, 0, 0, 0);
                    thu = Daylist + new TimeSpan(3, 0, 0, 0);
                    fri = Daylist + new TimeSpan(4, 0, 0, 0);
                    sat = Daylist + new TimeSpan(5, 0, 0, 0);
                    sun = Daylist + new TimeSpan(6, 0, 0, 0);
                }
                catch (Exception X)
                {
                    lblerr.Text = X.Message + " Looks like you are Exporting Blank TimeSheet";
                    lblerr.Visible = true;
                }
                finally
                {
                    GC.Collect();
                    /***************************************
                     * Generates one Excel WorkSheet (Gives TimeSheet of that Week)
                    ****************************************/
                    GenerateExcel(s + 1);
                }
            }
            DistinctStartCount = DistinctStart.Count + 1;
            SummarySheet();
            ExcelApp.Visible = false;
            ExcelApp.UserControl = false;
            String FileName = "" + User.Identity.Name + ".xlsx";
            ExcelWork.SaveCopyAs(strCurrentDir + FileName);/* shows error at this line */
            String TimeSheetPath = (String)System.Configuration.ConfigurationManager.AppSettings["TimeSheetPath"].ToString();
            string strMachineName = Request.ServerVariables["SERVER_NAME"];
            Expgo.Text = "<A href=http://" + strMachineName + TimeSheetPath + FileName + ">Download TimeSheet</a>";
/* when i click download TimeSheet link */
            //TODO: CLean Up...
            ExcelApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelRange);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelSheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWork);
            ExcelSheet = null;
            ExcelWork = null;
            ExcelApp = null;
            GC.Collect();
        }
    }

Answers (3)