示例#1
0
        private void GetInfoFromExcel(result FOSresult)
        {
            List<int> listOld = new List<int>();
            List<int> listNew = new List<int>();
            foreach (Process process in Process.GetProcessesByName("Excel"))
            {
                listOld.Add(process.Id);
            }
            string filename = GetFileName(parentpath);

            impersonateValidUser(username, Domain, password);

            //End -Get IssuerList From FOS
            DirectoryInfo di = new DirectoryInfo(databasepathG1);
            foreach (FileInfo f in di.GetFiles().Where(s => s.Name.IndexOf('~') != 0))
            {
                f.CopyTo(databasepath1 + @"\" + f.Name, true);
            }

            int year = DateTime.Now.Year;
            CopyDirectory(databasepathG2 + @"\" + year, databasepath2 + @"\" + year);

            Application celapp = new Application();
            foreach (System.Diagnostics.Process process in System.Diagnostics.Process.GetProcessesByName("Excel"))
            {
                listNew.Add(process.Id);
            }
            object mss = Missing.Value;
            Workbook celwbook1 = celapp.Workbooks.Open(tempworkpath + @"\" + "Book1.xls", mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss);
            //Worksheet celsh = celwbook.Sheets["Live"] as Worksheet;
            Worksheet celsh1 = celwbook1.ActiveSheet as Worksheet;
            //celsh.Select(Missing.Value);
            celapp.Visible = true;
            AddIn addin = celapp.AddIns.Add(@"C:\blp\API\Office Tools\BloombergUI.xla", Missing.Value);
            addin.Installed = false;

            addin.Installed = true;
            Workbook celwbook = celapp.Workbooks.Open(tempworkpath + @"\" + filename, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss);
            Worksheet celsh = celwbook.Sheets["Live"] as Worksheet;
            celsh.Select(Missing.Value);
            celapp.Visible = true;
            Range r;
            string s1 = string.Empty;
            for (int i = 1; i < celsh.UsedRange.Rows.Count; i++)
            {
                r = (Range)celsh.Cells[i, 1];
                s1 = r.Text.ToString();
                if (s1.Trim() == "Acc #")
                {
                    r = (Range)celsh.Cells[i - 1, 19];
                    s1 = r.Text.ToString();
                    break;
                }
            }
            FOSresult.TAP = s1.Substring(0, s1.LastIndexOf(",000,000")).Trim();
            celsh = celwbook.Sheets["Curncy"] as Worksheet;
            celsh.Select(Missing.Value);
            bool flag = true;
            while (flag)
            {
                flag = false;
                for (int icurncy = 1; icurncy <= celsh.UsedRange.Rows.Count; icurncy++)
                {
                    if (((Range)(celsh.Cells[icurncy, 1])).Text.ToString() == FOSresult.CRNCY)
                    {
                        if (((Range)(celsh.Cells[icurncy, 3])).Text.ToString().IndexOf("#") != -1)
                        {
                            flag = true;
                            break;
                        }
                        else
                            FOSresult.Exchange = ((Range)(celsh.Cells[icurncy, 3])).Text.ToString();
                    }
                }
            }

            celapp.Quit();
            undoImpersonation();
            impersonateValidUser("bbuser1", "WSG053", "bbUser01");
            if (celapp != null)
            {
                int processID = listNew.Except(listOld).FirstOrDefault();
                Process process = System.Diagnostics.Process.GetProcessById(processID);
                process.Kill();
                //Process.GetProcessesByName("Excel")[0].Kill();
            }
        }
示例#2
0
        public result Submit(string ISIN)
        {
            result FOSresult = new result();
            //open excel to get two parameter

            SessionOptions sessionOptions = new SessionOptions();
            sessionOptions.ServerHost = "WSG053";
            sessionOptions.ServerPort = 0x2002;
            Session session = new Session(sessionOptions);
            if (!session.Start())
            {
                Console.WriteLine("Could not start session.");
                Environment.Exit(1);
            }
            if (!session.OpenService("//blp/refdata"))
            {
                Console.WriteLine("Could not open service //blp/refdata");
                Environment.Exit(1);
            }
            CorrelationID requestID = new CorrelationID(1L);
            Request request = session.GetService("//blp/refdata").CreateRequest("ReferenceDataRequest");
            request.GetElement("securities").AppendValue(ISIN + " corp");
            Element fields = request.GetElement("fields");
            fields.AppendValue("SECURITY_DES");
            fields.AppendValue("CRNCY");
            fields.AppendValue("ISSUER");
            fields.AppendValue("COUNTRY_FULL_NAME");
            fields.AppendValue("MARKET_SECTOR_DES");
            fields.AppendValue("INDUSTRY_SECTOR");
            fields.AppendValue("PX_BID");
            fields.AppendValue("COUPON");
            fields.AppendValue("YLD_YTM_BID");
            fields.AppendValue("DUR_BID");
            fields.AppendValue("RTG_SP");
            fields.AppendValue("RTG_SP_LT_LC_ISSUER_CREDIT");
            fields.AppendValue("RTG_MOODY");
            fields.AppendValue("RTG_MOODY_LONG_TERM");
            fields.AppendValue("RTG_MDY_LC_CURR_ISSUER_RATING");
            fields.AppendValue("RTG_FITCH");
            fields.AppendValue("RTG_FITCH_LT_ISSUER_DEFAULT");
            fields.AppendValue("COLLAT_TYP");
            fields.AppendValue("MTY_YEARS_TDY");
            fields.AppendValue("NXT_CALL_DT");
            fields.AppendValue("MATURITY");
            fields.AppendValue("GUARANTOR");
            Console.WriteLine("Sending Request:" + request);
            session.SendRequest(request, null);
            while (true)
            {
                Event eventObj = session.NextEvent();
                this.processEvent(eventObj, session);
                if (eventObj.Type == Event.EventType.RESPONSE)
                {
                    IssueRating = RTG_SP + "/" + this.GetMoody(RTG_MOODY, RTG_MOODY_LONG_TERM) + "/" + RTG_FITCH;
                    IssuerRating = RTG_SP_LT_LC_ISSUER_CREDIT + "/" + RTG_MDY_LC_CURR_ISSUER_RATING + "/" + RTG_FITCH_LT_ISSUER_DEFAULT;
                    if (CallDate != "NA")
                    {
                        DateTime newdate = DateTime.Parse(CallDate);
                        DateTime nowdate = DateTime.Now;
                        decimal dCallDate = (decimal)(((TimeSpan)(newdate - nowdate)).Days) / 365;
                        CallDate = StandardFormat(Math.Round(dCallDate, 2).ToString(), 2);
                    }

                    FOSresult.CallDate = CallDate;
                    FOSresult.COLLATERAL_TYPE = COLLATERAL_TYPE;
                    FOSresult.COUNTRY_FULL_NAME = COUNTRY_FULL_NAME;
                    FOSresult.COUPON = COUPON;
                    FOSresult.CRNCY = CRNCY;
                    FOSresult.DUR_BID = DUR_BID;
                    FOSresult.INDUSTRY_SECTOR = INDUSTRY_SECTOR.ToUpper();
                    FOSresult.PX_BID = PX_BID;
                    FOSresult.YLD_YTM_BID = YLD_YTM_BID;
                    FOSresult.SECURITY_DES = SECURITY_DES;
                    FOSresult.IssueRating = IssueRating;
                    FOSresult.IssuerRating = IssuerRating;
                    FOSresult.MARKET_SECTOR_DES = MARKET_SECTOR_DES;
                    FOSresult.YearToMaturity = Maturity;
                    FOSresult.ISSUER = ISSUER;

                    GetInfoFromExcel(FOSresult);
                    //undoImpersonation();
                    return FOSresult;
                    //return base.Json(new { SECURITY_DES = SECURITY_DES, CRNCY = CRNCY, ISSUER = ISSUER, COUNTRY_FULL_NAME = COUNTRY_FULL_NAME, MARKET_SECTOR_DES = MARKET_SECTOR_DES, INDUSTRY_SECTOR = INDUSTRY_SECTOR.ToUpper(), PX_BID = PX_BID, COUPON = COUPON, YLD_YTM_BID = YLD_YTM_BID, DUR_BID = DUR_BID, IssueRating = IssueRating, IssuerRating = IssuerRating, COLLATERAL_TYPE = COLLATERAL_TYPE, YearToMaturity = Maturity, CallDate = CallDate });
                }
            }
        }
        public int Insert(result FOSresult, string FinalRating, int TradeID, out htmlresult htmlResult)
        {
            htmlResult = new htmlresult();
            List<int> listOld = new List<int>();
            List<int> listNew = new List<int>();
            foreach (Process process in Process.GetProcessesByName("Excel"))
            {
                listOld.Add(process.Id);
            }
            System.DateTime lastmodifytime;
            string filename = GetFileName(parentpath, out lastmodifytime);
            if (impersonateValidUser(username, Domain, password))
            //if(1==1)
            {
                int i;
                Range r;
                int j;
                Range rr;
                int paramstartindex = 0;
                int paramendindex = 0;
                //string[] IssueRatings = IssueRating.Split(new char[] { '/' });
                //string SPRating = IssueRatings[0];
                //string MoodyRating = IssueRatings[1];
                //string FitchRating = IssueRatings[2];
                //string RTG1 = this.GetRTG1(SPRating, MoodyRating, FitchRating);
                //string RTG2 = this.GetRTG2(SPRating, MoodyRating, FitchRating);
                //Dictionary<string, string> insertresult = new Dictionary<string, string>();
                //string filename = @"D:\ACR Portfolio - 120229.xlsm";

                try
                {
                    #region insert
                    Application celapp = new Application();
                    object mss = Missing.Value;
                    Workbook celwbook = null;
                    FileInfo fi1 = new FileInfo(parentpath + @"\" + filename);
                    if (this.Application.AllKeys.Contains("ExcelInstance") && this.Application["ExcelInstance"] != null)
                    {
                        celapp = ((Workbook)(this.Application["ExcelInstance"])).Application;
                        celwbook = (Workbook)(this.Application["ExcelInstance"]);
                    }
                    else
                    {
                        FileInfo fireadonly = new FileInfo(parentpath + @"\~$" + filename);
                        if (fireadonly.Exists)
                            return 1;
                        fireadonly = new FileInfo(tempworkpath + @"\~$" + filename);
                        if (fireadonly.Exists)
                            return 2;
                        fi1.CopyTo(tempworkpath + @"\" + filename, true);
                        Workbook celwbook1 = celapp.Workbooks.Open(tempworkpath + @"\" + "Book1.xls", mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss);
                        //Worksheet celsh = celwbook.Sheets["Live"] as Worksheet;
                        Worksheet celsh1 = celwbook1.ActiveSheet as Worksheet;
                        //celsh.Select(Missing.Value);
                        celapp.Visible = true;
                        AddIn addin = celapp.AddIns.Add(@"C:\blp\API\Office Tools\BloombergUI.xla", Missing.Value);
                        addin.Installed = false;

                        addin.Installed = true;
                        celwbook = celapp.Workbooks.Open(tempworkpath + @"\" + filename, mss, mss, mss, mss, mss, mss, mss, mss, mss, false, mss, mss, mss, mss);
                    }
                    //foreach (System.Diagnostics.Process process in System.Diagnostics.Process.GetProcessesByName("Excel"))
                    //{
                    //    listNew.Add(process.Id);
                    //}
                    Worksheet celsh = celwbook.Sheets["Live"] as Worksheet;
                    celsh.Select(Missing.Value);
                    celapp.Visible = true;
                    string s1 = string.Empty;
                    Dictionary<int, string> columnname = new Dictionary<int, string>();
                    int rowindex = 0;
                    for (i = 1; i < celsh.UsedRange.Rows.Count; i++)
                    {
                        r = (Range)celsh.Cells[i, 1];
                        s1 = r.Text.ToString();
                        if (s1.Trim() == "Mkt Value (US$)")
                        {
                            paramstartindex = i;
                        }
                        else if (s1.Trim() == "Ave Credit Quality")
                        {
                            paramendindex = i;
                        }
                        else if (s1.Trim() == "Acc #")
                        {
                            rowindex = i;
                            break;
                        }
                    }
                    float ftap = 0;
                    if (float.TryParse(FOSresult.TAP, out ftap))
                        celsh.Cells[rowindex - 1, 19] = ftap * 1000000;
                    else
                        return -1;
                    float ftfa = 0;
                    if (float.TryParse(FOSresult.TFA, out ftfa))
                        celsh.Cells[rowindex - 2, 20] = ftfa * 1000000;
                    else
                        return -1;
                    Range oldrange = (Range)celsh.Rows[rowindex + 2, mss];
                    ((Range)(celsh.Rows[rowindex + 1, mss])).Insert(XlDirection.xlDown, Missing.Value);
                    Range newrange = (Range)celsh.Rows[rowindex + 1, mss];
                    oldrange.Copy(newrange);
                    for (i = 1; i <= celsh.UsedRange.Columns.Count; i++)
                    {
                        r = (Range)celsh.Cells[rowindex, i];
                        columnname.Add(i, r.Text.ToString());

                    }
                    string temp = string.Empty;
                    i = 1;
                    if (FOSresult.BORS == "S" && FOSresult.Quatity != "")
                    {
                        FOSresult.Quatity = "-" + FOSresult.Quatity;
                        FOSresult.MVLCY = "-" + FOSresult.MVLCY;
                    }
                    while (i <= celsh.UsedRange.Columns.Count)
                    {
                        temp = this.updateValue(columnname, i, BondLife(FOSresult), FinalRating, FOSresult.FundType, FOSresult.ISIN, FOSresult.SECURITY_DES, FOSresult.CRNCY, FOSresult.ISSUER, FOSresult.COUNTRY_FULL_NAME, FOSresult.MARKET_SECTOR_DES, FOSresult.INDUSTRY_SECTOR, FOSresult.Quatity.Replace(",", ""), FOSresult.PX_BID, FOSresult.COUPON, FOSresult.YLD_YTM_BID, FOSresult.DUR_BID, TradeID.ToString(), FOSresult.MVLCY, "pending for approval");
                        if (temp != "")
                        {
                            ((Range)(celsh.Cells[rowindex + 1, i])).Formula = temp;
                        }
                        i++;
                    }
                    #endregion
                    #region runmacro
                    string sectorissuer = ((Range)(celsh.Cells[rowindex + 1, 39])).Text.ToString();
                    //celwbook.Save();
                    bool flag = true;
                    while (flag)
                    {
                        flag = false;
                        i = paramstartindex;
                        while (i <= (paramendindex + 1))
                        {
                            j = 2;
                            while (j <= 29)
                            {
                                if (((Range)(celsh.Cells[i, j])).Text.ToString().IndexOf("#V") != -1)
                                {
                                    flag = true;
                                    break;
                                }
                                j++;
                            }
                            i++;
                        }
                    }
                    celwbook.RefreshAll();
                    this.RunMacro(celapp, new object[] { "RefreshSummary" });

                    /*StringBuilder html = new StringBuilder();
                    StringBuilder html2 = new StringBuilder();
                    StringBuilder html3 = new StringBuilder();
                    StringBuilder html4 = new StringBuilder();
                    celsh = celwbook.Sheets["IssuerExp"] as Worksheet;
                    for (int iii = 1; iii < celsh.UsedRange.Rows.Count; iii++)
                    {
                        r = (Range)celsh.Cells[iii, 1];
                        if (r.Text.ToString() == FOSresult.ISSUER)
                        {
                            htmlResult.issuer = FOSresult.ISSUER;
                        }
                    }

                    celsh = celwbook.Sheets["GUIDELINES CHECK"] as Worksheet;
                    //transfer data from datatable to html
                    int step1 = 0;
                    int step2 = 0;
                    int step3 = 0;
                    int row = 0;
                    int column = 0;
                    int BBBGuidlineCheck = 0;
                    //Range r;
                    //Range rr;
                    for (int iii = 1; iii < celsh.UsedRange.Rows.Count; iii++)
                    {
                        for (int jjj = 1; jjj < celsh.UsedRange.Columns.Count; jjj++)
                        {
                            r = (Range)celsh.Cells[iii, jjj];
                            switch ((string)(r.Text.ToString()))
                            {
                                case "LIMITS":
                                    column = jjj;
                                    step1 = iii;
                                    break;
                                case "LIMITS BY SECURITY TYPES":
                                    step2 = iii;
                                    break;
                                case "LIMITS BY ISSUER":
                                    step3 = iii;
                                    break;
                                case "Issuer/Issue Rating":
                                    BBBGuidlineCheck = iii;
                                    break;
                            }
                            row++;
                            if (step3 > 0)
                                break;
                        }
                    }
                    if (RTG_FITCHlist.Contains(FinalRating))
                        celsh.Cells[BBBGuidlineCheck, column + 5] = "YES";
                    else
                    {
                        if (CRNCY == "IDR" || CRNCY == "INR" || CRNCY == "PHP")
                            celsh.Cells[BBBGuidlineCheck, column + 5] = "YES";
                        else
                            celsh.Cells[BBBGuidlineCheck, column + 5] = "NO";
                    }
                    for (int jj = step1 + 3; jj < celsh.UsedRange.Rows.Count; jj++)
                    {
                        r = (Range)celsh.Cells[jj, column];
                        if (r.Text.ToString() == "")
                        {
                            break;
                        }
                        else
                        {
                            html.Append("<tr>");
                            for (int k = column; k <= 8; k++)
                            {
                                rr = (Range)celsh.Cells[jj, k];
                                html.Append("<td>" + rr.Text.ToString() + "</td>");
                            }
                            html.Append("</tr>");
                        }
                    }
                    for (int jj = step2 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
                    {
                        r = (Range)celsh.Cells[jj, column];
                        if (r.Text.ToString() == "")
                            break;
                        else
                        {
                            html2.Append("<tr>");
                            for (int k = column; k <= 8; k++)
                            {
                                if (k == 3 || k == 5)
                                    continue;
                                rr = (Range)celsh.Cells[jj, k];
                                html2.Append("<td>" + rr.Text.ToString() + "</td>");
                                if (k == 2 || k == 4)
                                    k++;
                            }
                            html2.Append("</tr>");
                        }
                    }
                    for (int jj = step3 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
                    {
                        r = (Range)celsh.Cells[jj, column];
                        if (r.Text.ToString() == "")
                            break;
                        else
                        {
                            string stemp = r.Text.ToString();
                            if (stemp.Trim().ToUpper() == sectorissuer.Trim().ToUpper())
                            //if (stemp.Substring((temp.IndexOf('-') + 1), (stemp.ToString().Length - stemp.ToString().IndexOf('-') - 1)) == sectorissuer.ToUpper())
                            {
                                html3.Append("<tr>");
                                for (int k = column; k <= 8; k++)
                                {
                                    if (k == 3 || k == 5)
                                        continue;
                                    rr = (Range)celsh.Cells[jj, k];
                                    html3.Append("<td>" + rr.Text.ToString() + "</td>");
                                    if (k == 2 || k == 4)
                                        k++;
                                }
                                html3.Append("</tr>");
                            }
                        }
                    }
                    for (int jj = step3 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
                    {
                        r = (Range)celsh.Cells[jj, column];
                        if (r.Text.ToString() == "")
                            break;
                        else
                        {
                            rr = (Range)celsh.Cells[jj, 4];
                            string stemp = rr.Text.ToString();
                            float fstemp = 0;
                            if (float.TryParse(stemp.Substring(0, stemp.Length - 1), out fstemp))
                            {
                                if (fstemp > 3.5)
                                {
                                    html4.Append("<tr>");
                                    for (int k = column; k <= 8; k++)
                                    {
                                        if (k == 3 || k == 5)
                                            continue;
                                        rr = (Range)celsh.Cells[jj, k];
                                        html4.Append("<td>" + rr.Text.ToString() + "</td>");
                                        if (k == 2 || k == 4)
                                            k++;
                                    }
                                    html4.Append("</tr>");
                                }
                            }
                        }
                    }*/
                    celwbook.Save();

                    fi1 = new FileInfo(tempworkpath + @"\" + filename);
                    fi1.CopyTo(parentpath + @"\" + filename, true);
                    undoImpersonation();

                    /*StringBuilder finalhtml = new StringBuilder();
                    finalhtml.Append("<div><h2>SUMMARY OF GUIDELINES CHECK</h2><h4>LIMITS</h4><table border='1'><tr><th>S/N</th><th>GUIDELINE</th><th>Official Guidelines</th><th>Exception (Jan 2012 - Jun 2012)</th><th>ACR PORTFOLIO</th><th>MET GUIDELINE?</th><th>BUFFER BEFORE HITTING LIMIT</th></tr>");
                    finalhtml.Append(html.ToString());
                    finalhtml.Append("</table><p>*Note: Exception for Guideline 5 applies only if Guideline 4 is met</p><h4>LIMITS BY SECURITY TYPES</h4><table border='1'><tr><th>Security Types</th><th>% Weight of Target Asian Bond Portfolio</th><th>Maximum Limits(Guidelines)</th><th>Met Guidelines?</th><th>Buffer Before Hitting Limit</th></tr>");
                    finalhtml.Append(html2.ToString());
                    finalhtml.Append("</table><h4>LIMITS BY ISSUER</h4><table border='1'><tr><th>Security Types</th><th>% Weight of Target Asian Bond Portfolio</th><th>Maximum Limits(Guidelines)</th><th>Met Guidelines?</th><th>Buffer Before Hitting Limit</th></tr>");
                    finalhtml.Append(html3.ToString());
                    finalhtml.Append("</table><h4>LIMITS BY ISSUER(>3.5%)</h4><table border='1'><tr><th>Security Types</th><th>% Weight of Target Asian Bond Portfolio</th><th>Maximum Limits(Guidelines)</th><th>Met Guidelines?</th><th>Buffer Before Hitting Limit</th></tr>");
                    finalhtml.Append(html4.ToString());
                    finalhtml.Append("</table></div>");
                    htmlResult.htmlfortable = finalhtml.ToString();*/
                    return 0;
                    #endregion
                    //#region norunmacro
                    //else
                    //{
                    //    celwbook.Save();
                    //    celapp.DisplayAlerts = false;
                    //    celapp.Quit();

                    //    fi1 = new FileInfo(tempworkpath + @"\" + filename);
                    //    fi1.CopyTo(parentpath + @"\" + filename, true);
                    //    undoImpersonation();
                    //    impersonateValidUser("bbuser1", "WSG053", "bbUser01");
                    //    if (celapp != null)
                    //    {
                    //        Process process = new Process();
                    //        foreach (int pID in listNew.Except(listOld))
                    //        {
                    //            process = System.Diagnostics.Process.GetProcessById(pID);
                    //            process.Kill();
                    //        }

                    //        //Process.GetProcessesByName("Excel")[0].Kill();
                    //    }
                    //    undoImpersonation();
                    //    return 0;
                    //}
                    //#endregion
                }
                catch (Exception e)
                {
                    return -1;
                }
                finally
                {
                    //impersonateValidUser("bbuser1", "WSG053", "bbUser01");
                    //if (celapp != null)
                    //{
                    //    Process process = new Process();
                    //    foreach (int pID in listNew.Except(listOld))
                    //    {
                    //        process = System.Diagnostics.Process.GetProcessById(pID);
                    //        process.Kill();
                    //    }

                    //    //Process.GetProcessesByName("Excel")[0].Kill();
                    //}
                    //undoImpersonation();
                }
            }
            else
            {
                return -1;
            }
        }
 private string BondLife(result FOSresult)
 {
     string year = "";
     float YTM = 0;
     float CD = 0;
     float.TryParse(FOSresult.YearToMaturity, out YTM);
     float.TryParse(FOSresult.CallDate, out CD);
     if (YTM == 0)
         year = FOSresult.CallDate;
     else
     {
         if (CD == 0)
             year = FOSresult.YearToMaturity;
         else
         {
             if (YTM <= CD)
             {
                 year = FOSresult.YearToMaturity;
             }
             else
                 year = FOSresult.CallDate;
         }
     }
     return year;
 }
        public int Revise(result FOSresult, string FinalRating, int TradeID, bool RerunMacro, out htmlresult htmlResult)
        {
            htmlResult = new htmlresult();
            //List<int> listOld = new List<int>();
            //List<int> listNew = new List<int>();
            //foreach (Process process in Process.GetProcessesByName("Excel"))
            //{
            //    listOld.Add(process.Id);
            //}
            System.DateTime lastmodifytime;
            string filename = GetFileName(parentpath, out lastmodifytime);
            if (impersonateValidUser(username, Domain, password))
            //if(1==1)
            {
                int i;
                Range r;
                int j;
                Range rr;
                int paramstartindex = 0;
                int paramendindex = 0;
                //string[] IssueRatings = IssueRating.Split(new char[] { '/' });
                //string SPRating = IssueRatings[0];
                //string MoodyRating = IssueRatings[1];
                //string FitchRating = IssueRatings[2];
                //string RTG1 = this.GetRTG1(SPRating, MoodyRating, FitchRating);
                //string RTG2 = this.GetRTG2(SPRating, MoodyRating, FitchRating);
                //Dictionary<string, string> insertresult = new Dictionary<string, string>();
                //string filename = @"D:\ACR Portfolio - 120229.xlsm";

                try
                {
                    #region Revise
                    Application celapp = new Application();
                    object mss = Missing.Value;
                    Workbook celwbook = null;
                    FileInfo fi1 = new FileInfo(parentpath + @"\" + filename);
                    if (this.Application.AllKeys.Contains("ExcelInstance") && this.Application["ExcelInstance"] != null)
                    {
                        celapp = ((Workbook)(this.Application["ExcelInstance"])).Application;
                        celwbook = (Workbook)(this.Application["ExcelInstance"]);
                    }
                    else
                    {
                        FileInfo fireadonly = new FileInfo(parentpath + @"\~$" + filename);
                        if (fireadonly.Exists)
                            return 1;
                        fireadonly = new FileInfo(tempworkpath + @"\~$" + filename);
                        if (fireadonly.Exists)
                            return 2;
                        fi1.CopyTo(tempworkpath + @"\" + filename, true);
                        Workbook celwbook1 = celapp.Workbooks.Open(tempworkpath + @"\" + "Book1.xls", mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss);
                        //Worksheet celsh = celwbook.Sheets["Live"] as Worksheet;
                        Worksheet celsh1 = celwbook1.ActiveSheet as Worksheet;
                        //celsh.Select(Missing.Value);
                        celapp.Visible = true;
                        AddIn addin = celapp.AddIns.Add(@"C:\blp\API\Office Tools\BloombergUI.xla", Missing.Value);
                        addin.Installed = false;

                        addin.Installed = true;
                        celwbook = celapp.Workbooks.Open(tempworkpath + @"\" + filename, mss, mss, mss, mss, mss, mss, mss, mss, mss, false, mss, mss, mss, mss);
                        this.Application["ExcelInstance"] = celwbook;
                    }
                    //foreach (System.Diagnostics.Process process in System.Diagnostics.Process.GetProcessesByName("Excel"))
                    //{
                    //    listNew.Add(process.Id);
                    //}
                    Worksheet celsh = celwbook.Sheets["Live"] as Worksheet;
                    celsh.Select(Missing.Value);
                    celapp.Visible = true;
                    string s1 = string.Empty;
                    Dictionary<int, string> columnname = new Dictionary<int, string>();
                    int rowindex = 0;
                    int tradeIDcolumnindex = 0;
                    for (i = 1; i < celsh.UsedRange.Rows.Count; i++)
                    {
                        r = (Range)celsh.Cells[i, 1];
                        s1 = r.Text.ToString();
                        if (s1.Trim() == "Mkt Value (US$)")
                        {
                            paramstartindex = i;
                        }
                        else if (s1.Trim() == "Ave Credit Quality")
                        {
                            paramendindex = i;
                        }
                        else if (s1.Trim() == "Acc #")
                        {
                            rowindex = i;
                            break;
                        }
                    }
                    for (i = 1; i <= celsh.UsedRange.Rows.Columns.Count; i++)
                    {
                        r = (Range)celsh.Cells[rowindex, i];
                        if (r.Text.ToString().Trim() == "TRADE ID")
                        {
                            tradeIDcolumnindex = i;
                            break;
                        }
                    }
                    for (i = rowindex; i <= celsh.UsedRange.Rows.Count; i++)
                    {
                        r = (Range)celsh.Cells[i, tradeIDcolumnindex];
                        if (r.Text.ToString().Trim() == TradeID.ToString())
                        {
                            ((Range)(celsh.Cells[i, 4])).Formula = FOSresult.ISIN;
                            ((Range)(celsh.Cells[i, 6])).Formula = FOSresult.SECURITY_DES;
                            ((Range)(celsh.Cells[i, 7])).Formula = FOSresult.Quatity.Replace(",", "");
                            ((Range)(celsh.Cells[i, 12])).Formula = FOSresult.CRNCY;
                            ((Range)(celsh.Cells[i, 13])).Formula = FOSresult.PX_BID;
                            ((Range)(celsh.Cells[i, 14])).Formula = FOSresult.PX_BID;
                            ((Range)(celsh.Cells[i, 23])).Formula = FOSresult.YLD_YTM_BID;
                        }

                    }
                    #endregion
                    #region runmacro
                    if (RerunMacro)
                    {
                        string sectorissuer = ((Range)(celsh.Cells[rowindex + 1, 39])).Text.ToString();
                        //celwbook.Save();
                        bool flag = true;
                        while (flag)
                        {
                            flag = false;
                            i = paramstartindex;
                            while (i <= (paramendindex + 1))
                            {
                                j = 2;
                                while (j <= 29)
                                {
                                    if (((Range)(celsh.Cells[i, j])).Text.ToString().IndexOf("#V") != -1)
                                    {
                                        flag = true;
                                        break;
                                    }
                                    j++;
                                }
                                i++;
                            }
                        }
                        celwbook.RefreshAll();
                        this.RunMacro(celapp, new object[] { "RefreshSummary" });

                        StringBuilder html = new StringBuilder();
                        StringBuilder html2 = new StringBuilder();
                        StringBuilder html3 = new StringBuilder();
                        StringBuilder html4 = new StringBuilder();
                        celsh = celwbook.Sheets["IssuerExp"] as Worksheet;
                        for (int iii = 1; iii < celsh.UsedRange.Rows.Count; iii++)
                        {
                            r = (Range)celsh.Cells[iii, 1];
                            if (r.Text.ToString() == FOSresult.ISSUER)
                            {
                                htmlResult.issuer = FOSresult.ISSUER;
                            }
                        }

                        celsh = celwbook.Sheets["GUIDELINES CHECK"] as Worksheet;
                        //transfer data from datatable to html
                        int step1 = 0;
                        int step2 = 0;
                        int step3 = 0;
                        int row = 0;
                        int column = 0;
                        int BBBGuidlineCheck = 0;
                        //Range r;
                        //Range rr;
                        for (int iii = 1; iii < celsh.UsedRange.Rows.Count; iii++)
                        {
                            for (int jjj = 1; jjj < celsh.UsedRange.Columns.Count; jjj++)
                            {
                                r = (Range)celsh.Cells[iii, jjj];
                                switch ((string)(r.Text.ToString()))
                                {
                                    case "LIMITS":
                                        column = jjj;
                                        step1 = iii;
                                        break;
                                    case "LIMITS BY SECURITY TYPES":
                                        step2 = iii;
                                        break;
                                    case "LIMITS BY ISSUER":
                                        step3 = iii;
                                        break;
                                    case "Issuer/Issue Rating":
                                        BBBGuidlineCheck = iii;
                                        break;
                                }
                                row++;
                                if (step3 > 0)
                                    break;
                            }
                        }
                        if (RTG_FITCHlist.Contains(FinalRating))
                            celsh.Cells[BBBGuidlineCheck, column + 5] = "YES";
                        else
                        {
                            if (CRNCY == "IDR" || CRNCY == "INR" || CRNCY == "PHP")
                                celsh.Cells[BBBGuidlineCheck, column + 5] = "YES";
                            else
                                celsh.Cells[BBBGuidlineCheck, column + 5] = "NO";
                        }
                        html.Append("<tr>");
                        for (int kk = column; kk <= 8; kk++)
                        {
                            r = (Range)celsh.Cells[step1 + 1, kk];
                            html.Append("<th>" + r.Text.ToString() + "</th>");
                        }
                        for (int jj = step1 + 3; jj < celsh.UsedRange.Rows.Count; jj++)
                        {
                            r = (Range)celsh.Cells[jj, column];
                            if (r.Text.ToString() == "")
                            {
                                break;
                            }
                            else
                            {
                                html.Append("<tr>");
                                for (int k = column; k <= 8; k++)
                                {
                                    rr = (Range)celsh.Cells[jj, k];
                                    html.Append("<td>" + rr.Text.ToString() + "</td>");
                                }
                                html.Append("</tr>");
                            }
                        }
                        html2.Append("<tr>");
                        for (int kk = column; kk <= 8; kk++)
                        {
                            if (kk == 3 || kk == 5)
                                continue;
                            r = (Range)celsh.Cells[step2 + 1, kk];
                            html2.Append("<th>" + r.Text.ToString() + "</th>");
                        }
                        html2.Append("</tr>");
                        for (int jj = step2 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
                        {
                            r = (Range)celsh.Cells[jj, column];
                            if (r.Text.ToString() == "")
                                break;
                            else
                            {
                                html2.Append("<tr>");
                                for (int k = column; k <= 8; k++)
                                {
                                    if (k == 3 || k == 5)
                                        continue;
                                    rr = (Range)celsh.Cells[jj, k];
                                    html2.Append("<td>" + rr.Text.ToString() + "</td>");
                                    if (k == 2 || k == 4)
                                        k++;
                                }
                                html2.Append("</tr>");
                            }
                        }
                        html3.Append("<tr>");
                        for (int kk = column; kk <= 8; kk++)
                        {
                            if (kk == 3 || kk == 5)
                                continue;
                            r = (Range)celsh.Cells[step3 + 1, kk];
                            html3.Append("<th>" + r.Text.ToString() + "</th>");
                        }
                        html3.Append("</tr>");
                        for (int jj = step3 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
                        {
                            r = (Range)celsh.Cells[jj, column];
                            if (r.Text.ToString().Trim() == "Grand Total" || r.Text.ToString() == "")
                                break;
                            else
                            {
                                string stemp = r.Text.ToString();
                                if (stemp.Trim().ToUpper() == sectorissuer.Trim().ToUpper())
                                //if (stemp.Substring((temp.IndexOf('-') + 1), (stemp.ToString().Length - stemp.ToString().IndexOf('-') - 1)) == sectorissuer.ToUpper())
                                {
                                    html3.Append("<tr>");
                                    for (int k = column; k <= 8; k++)
                                    {
                                        if (k == 3 || k == 5)
                                            continue;
                                        rr = (Range)celsh.Cells[jj, k];
                                        html3.Append("<td>" + rr.Text.ToString() + "</td>");
                                        if (k == 2 || k == 4)
                                            k++;
                                    }
                                    html3.Append("</tr>");
                                }
                            }
                        }
                        html4.Append("<tr>");
                        for (int kk = column; kk <= 8; kk++)
                        {
                            if (kk == 3 || kk == 5)
                                continue;
                            r = (Range)celsh.Cells[step3 + 1, kk];
                            html4.Append("<th>" + r.Text.ToString() + "</th>");
                        }
                        html4.Append("</tr>");
                        for (int jj = step3 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
                        {
                            r = (Range)celsh.Cells[jj, column];
                            if (r.Text.ToString().Trim() == "Grand Total" || r.Text.ToString() == "")
                                break;
                            else
                            {
                                rr = (Range)celsh.Cells[jj, 4];
                                string stemp = rr.Text.ToString();
                                if (float.Parse(stemp.Substring(0, stemp.Length - 1)) > 3.5)
                                {
                                    html4.Append("<tr>");
                                    for (int k = column; k <= 8; k++)
                                    {
                                        if (k == 3 || k == 5)
                                            continue;
                                        rr = (Range)celsh.Cells[jj, k];
                                        html4.Append("<td>" + rr.Text.ToString() + "</td>");
                                        if (k == 2 || k == 4)
                                            k++;
                                    }
                                    html4.Append("</tr>");
                                }
                            }
                        }
                        celwbook.Save();

                        fi1 = new FileInfo(tempworkpath + @"\" + filename);
                        fi1.CopyTo(parentpath + @"\" + filename, true);
                        undoImpersonation();
                        //impersonateValidUser("bbuser1", "WSG053", "bbUser01");
                        //if (celapp != null)
                        //{
                        //    Process process = new Process();
                        //    foreach (int pID in listNew.Except(listOld))
                        //    {
                        //        process = System.Diagnostics.Process.GetProcessById(pID);
                        //        process.Kill();
                        //    }

                        //    //Process.GetProcessesByName("Excel")[0].Kill();
                        //}
                        //undoImpersonation();
                        StringBuilder finalhtml = new StringBuilder();
                        finalhtml.Append("<div><h2>SUMMARY OF GUIDELINES CHECK</h2><h4>LIMITS</h4><table border='1'>");
                        finalhtml.Append(html.ToString());
                        finalhtml.Append("</table><p>*Note: Exception for Guideline 5 applies only if Guideline 4 is met</p><h4>LIMITS BY SECURITY TYPES</h4><table border='1'>");
                        finalhtml.Append(html2.ToString());
                        finalhtml.Append("</table><h4>LIMITS BY ISSUER</h4><table border='1'>");
                        finalhtml.Append(html3.ToString());
                        finalhtml.Append("</table><h4>LIMITS BY ISSUER(>3.5%)</h4><table border='1'>");
                        finalhtml.Append(html4.ToString());
                        finalhtml.Append("</table></div>");
                        htmlResult.htmlfortable = finalhtml.ToString();
                        return 0;
                    }
                    #endregion
                    #region norunmacro
                    else
                    {
                        celwbook.Save();

                        fi1 = new FileInfo(tempworkpath + @"\" + filename);
                        fi1.CopyTo(parentpath + @"\" + filename, true);
                        undoImpersonation();
                        //impersonateValidUser("bbuser1", "WSG053", "bbUser01");
                        //if (celapp != null)
                        //{
                        //    Process process = new Process();
                        //    foreach (int pID in listNew.Except(listOld))
                        //    {
                        //        process = System.Diagnostics.Process.GetProcessById(pID);
                        //        process.Kill();
                        //    }

                        //    //Process.GetProcessesByName("Excel")[0].Kill();
                        //}
                        //undoImpersonation();
                        return 0;
                    }
                    #endregion
                }
                catch (Exception e)
                {
                    return -1;
                }
                finally
                {
                //    impersonateValidUser("bbuser1", "WSG053", "bbUser01");
                //    if (celapp != null)
                //    {
                //        Process process = new Process();
                //        foreach (int pID in listNew.Except(listOld))
                //        {
                //            process = System.Diagnostics.Process.GetProcessById(pID);
                //            process.Kill();
                //        }

                //        //Process.GetProcessesByName("Excel")[0].Kill();
                //    }
                //    undoImpersonation();
                }
            }
            else
            {
                return -1;
            }
        }
        public int PTCCheck(result FOSresult, string FinalRating, out htmlresult htmlResult)
        {
            impersonateValidUser("bbuser1", "WSG053", "bbUser01");
            StreamWriter tw = new StreamWriter("d:\\Temp\\Miao\\log.txt");
            undoImpersonation();
            htmlResult = new htmlresult();
            /*List<int> listOld = new List<int>();
            List<int> listNew = new List<int>();
            foreach (Process process in Process.GetProcessesByName("Excel"))
            {
                listOld.Add(process.Id);
            }*/
            System.DateTime lastmodifytime;
            string filename = GetFileName(parentpath, out lastmodifytime);
            if (impersonateValidUser(username, Domain, password))
            //if(1==1)
            {
                int i;
                Range r;
                int j;
                Range rr;
                int paramstartindex = 0;
                int paramendindex = 0;
                //string[] IssueRatings = IssueRating.Split(new char[] { '/' });
                //string SPRating = IssueRatings[0];
                //string MoodyRating = IssueRatings[1];
                //string FitchRating = IssueRatings[2];
                //string RTG1 = this.GetRTG1(SPRating, MoodyRating, FitchRating);
                //string RTG2 = this.GetRTG2(SPRating, MoodyRating, FitchRating);
                //Dictionary<string, string> insertresult = new Dictionary<string, string>();
                //string filename = @"D:\ACR Portfolio - 120229.xlsm";
                //impersonateValidUser("bbuser1", "WSG053", "bbUser01");

                try
                {
                    Application celapp = new Application();
                    object mss = Missing.Value;
                    Workbook celwbook = null;
                    FileInfo fi1 = new FileInfo(parentpath + @"\" + filename);
                    bool isOpen = false;
                    bool book1isOpen = false;

                    if (Process.GetProcessesByName("Excel").Count() > 0)
                    {
                        impersonateValidUser("bbuser1", "WSG053", "bbUser01");
                        tw.WriteLine("Begin to get obj");
                        object o=null;
                        try
                        {
                            o = Marshal.GetActiveObject("Excel.Application");

                        }
                        catch(Exception e)
                        {
                            tw.WriteLine("null");
                            o=null;
                        }
                        tw.WriteLine("End to get obj");
                        undoImpersonation();
                        if (o != null)
                        {
                            impersonateValidUser("bbuser1", "WSG053", "bbUser01");
                            tw.WriteLine("Begin to get app");
                            undoImpersonation();
                            celapp = (Microsoft.Office.Interop.Excel.Application)o;
                            Microsoft.Office.Interop.Excel.Workbooks s = celapp.Workbooks;
                            foreach (Microsoft.Office.Interop.Excel.Workbook ss in s)
                            {
                                string excelfilename = ss.FullName;
                                if (excelfilename == tempworkpath + "\\" + filename)
                                {
                                    isOpen = true;
                                    celwbook = ss;
                                }
                                if (excelfilename == tempworkpath + "\\Book1.xls")
                                {
                                    book1isOpen = true;
                                }
                            }
                        }
                    }
                    if(!isOpen)
                    {
                        impersonateValidUser("bbuser1", "WSG053", "bbUser01");
                        tw.WriteLine("IsOpen: "+isOpen);
                        undoImpersonation();
                        //Process[] pr = Process.GetProcessesByName("EXCEL");

                        impersonateValidUser(username, Domain, password);
                        FileInfo fireadonly = new FileInfo(parentpath + @"\~$" + filename);
                        if (fireadonly.Exists)
                            return 1;
                        fireadonly = new FileInfo(tempworkpath + @"\~$" + filename);
                        if (fireadonly.Exists)
                        {
                            tw.Close();
                            return 2;
                        }
                        fi1.CopyTo(tempworkpath + @"\" + filename, true);

                        if (!book1isOpen)
                        {
                            Workbook celwbook1 = celapp.Workbooks.Open(tempworkpath + @"\" + "Book1.xls", mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss, mss);
                            //Worksheet celsh = celwbook.Sheets["Live"] as Worksheet;
                            Worksheet celsh1 = celwbook1.ActiveSheet as Worksheet;
                            //celsh.Select(Missing.Value);
                            celapp.Visible = true;
                            AddIn addin = celapp.AddIns.Add(@"C:\blp\API\Office Tools\BloombergUI.xla", Missing.Value);
                            addin.Installed = false;

                            addin.Installed = true;
                            //celwbook = celapp.Workbooks.Open(tempworkpath + @"\" + filename, mss, mss, mss, mss, mss, mss, mss, mss, mss, false, mss, mss, mss, mss);
                        }
                        celwbook = celapp.Workbooks.Open(tempworkpath + @"\" + filename, mss, mss, mss, mss, mss, mss, mss, mss, mss, false, mss, mss, mss, mss);
                        //foreach (System.Diagnostics.Process process in System.Diagnostics.Process.GetProcessesByName("Excel"))
                        //{
                        //    if (process.MainWindowTitle.IndexOf(filename) > 0)
                        //    {
                        //        this.Application["ProcessID"] = process.Id;
                        //        break;
                        //    }
                        //}
                        //this.Application["ExcelInstance"] = celwbook;
                    }
                    impersonateValidUser("bbuser1", "WSG053", "bbUser01");
                    tw.Close();
                    undoImpersonation();
                    Worksheet celsh = celwbook.Sheets["IssuerExp"] as Worksheet;
                    celsh.Select(Missing.Value);
                    celapp.Visible = true;
                    for (i = 1; i < celsh.UsedRange.Rows.Count; i++)
                    {
                        r = (Range)celsh.Cells[i, 1];
                        if (r.Text.ToString() == FOSresult.ISSUER)
                        {
                            htmlResult.SumofMV = ((Range)celsh.Cells[i, 2]).Text.ToString();
                            break;
                        }
                    }

                    celsh = celwbook.Sheets["Live"] as Worksheet;
                    celsh.Select(Missing.Value);
                    string s1 = string.Empty;
                    Dictionary<int, string> columnname = new Dictionary<int, string>();
                    int rowindex = 0;
                    for (i = 1; i < celsh.UsedRange.Rows.Count; i++)
                    {
                        r = (Range)celsh.Cells[i, 1];
                        s1 = r.Text.ToString();
                        if (s1.Trim() == "Mkt Value (US$)")
                        {
                            paramstartindex = i;
                        }
                        else if (s1.Trim() == "Ave Credit Quality")
                        {
                            paramendindex = i;
                        }
                        else if (s1.Trim() == "Acc #")
                        {
                            rowindex = i;
                            break;
                        }
                    }
                    float ftap = 0;
                    if (float.TryParse(FOSresult.TAP, out ftap))
                        celsh.Cells[rowindex - 1, 19] = ftap * 1000000;
                    else
                        return -1;
                    float ftfa = 0;
                    if (float.TryParse(FOSresult.TFA, out ftfa))
                        celsh.Cells[rowindex - 2, 20] = ftfa * 1000000;
                    else
                        return -1;
                    Range oldrange = (Range)celsh.Rows[rowindex + 2, mss];
                    ((Range)(celsh.Rows[rowindex + 1, mss])).Insert(XlDirection.xlDown, Missing.Value);
                    Range newrange = (Range)celsh.Rows[rowindex + 1, mss];
                    oldrange.Copy(newrange);
                    for (i = 1; i < celsh.UsedRange.Columns.Count; i++)
                    {
                        r = (Range)celsh.Cells[rowindex, i];
                        columnname.Add(i, r.Text.ToString());

                    }
                    string temp = string.Empty;
                    i = 1;
                    if (FOSresult.BORS == "S" && FOSresult.Quatity != "")
                    {
                        FOSresult.Quatity = "-" + FOSresult.Quatity;
                        FOSresult.MVLCY = "-" + FOSresult.MVLCY;
                    }
                    while (i < celsh.UsedRange.Columns.Count)
                    {
                        temp = this.updateValue(columnname, i, BondLife(FOSresult), FinalRating, FOSresult.FundType, FOSresult.ISIN, FOSresult.SECURITY_DES, FOSresult.CRNCY, FOSresult.ISSUER, FOSresult.COUNTRY_FULL_NAME, FOSresult.MARKET_SECTOR_DES, FOSresult.INDUSTRY_SECTOR, FOSresult.Quatity.Replace(",", ""), FOSresult.PX_BID, FOSresult.COUPON, FOSresult.YLD_YTM_BID, FOSresult.DUR_BID, "", FOSresult.MVLCY, "");
                        if (temp != "")
                        {
                            ((Range)(celsh.Cells[rowindex + 1, i])).Formula = temp;
                        }
                        i++;
                    }
                    string sectorissuer = ((Range)(celsh.Cells[rowindex + 1, 39])).Text.ToString();
                    //celwbook.Save();
                    bool flag = true;
                    while (flag)
                    {
                        flag = false;
                        i = paramstartindex;
                        while (i <= (paramendindex + 1))
                        {
                            j = 2;
                            while (j <= 29)
                            {
                                if (((Range)(celsh.Cells[i, j])).Text.ToString().IndexOf("#") != -1)
                                {
                                    flag = true;
                                    break;
                                }
                                j++;
                            }
                            i++;
                        }
                    }
                    celwbook.RefreshAll();
                    this.RunMacro(celapp, new object[] { "RefreshSummary" });

                    StringBuilder html = new StringBuilder();
                    StringBuilder html2 = new StringBuilder();
                    StringBuilder html3 = new StringBuilder();
                    StringBuilder html4 = new StringBuilder();
                    /*celsh = celwbook.Sheets["IssuerExp"] as Worksheet;
                    for (int iii = 1; iii < celsh.UsedRange.Rows.Count; iii++)
                    {
                        r = (Range)celsh.Cells[iii, 1];
                        if (r.Text.ToString() == FOSresult.ISSUER)
                        {
                            htmlResult.issuer = FOSresult.ISSUER;
                        }
                    }*/
                    bool AllYes = true;
                    htmlResult.issuer = FOSresult.ISSUER;
                    celsh = celwbook.Sheets["GUIDELINES CHECK"] as Worksheet;
                    //transfer data from datatable to html
                    int step1 = 0;
                    int step2 = 0;
                    int step3 = 0;
                    int row = 0;
                    int column = 0;
                    int BBBGuidlineCheck = 0;
                    //Range r;
                    //Range rr;
                    for (int iii = 1; iii < celsh.UsedRange.Rows.Count; iii++)
                    {
                        for (int jjj = 1; jjj < celsh.UsedRange.Columns.Count; jjj++)
                        {
                            r = (Range)celsh.Cells[iii, jjj];
                            switch ((string)(r.Text.ToString()))
                            {
                                case "LIMITS":
                                    column = jjj;
                                    step1 = iii;
                                    break;
                                case "LIMITS BY SECURITY TYPES":
                                    step2 = iii;
                                    break;
                                case "LIMITS BY ISSUER":
                                    step3 = iii;
                                    break;
                                case "Issuer/Issue Rating":
                                    BBBGuidlineCheck = iii;
                                    break;
                            }
                            row++;
                            if (step3 > 0)
                                break;
                        }
                    }
                    if (FOSresult.BORS == "B")
                    {
                        if (RTG_FITCHlist.Contains(FinalRating))
                            celsh.Cells[BBBGuidlineCheck, column + 5] = "YES";
                        else
                        {
                            if (CRNCY == "IDR" || CRNCY == "INR" || CRNCY == "PHP")
                                celsh.Cells[BBBGuidlineCheck, column + 5] = "YES";
                            else
                                celsh.Cells[BBBGuidlineCheck, column + 5] = "NO";
                        }
                    }
                    else
                        celsh.Cells[BBBGuidlineCheck, column + 5] = "-";
                    html.Append("<tr>");
                    for (int kk = column; kk <= 8; kk++)
                    {
                        r = (Range)celsh.Cells[step1 + 1, kk];
                        html.Append("<th>" + r.Text.ToString() + "</th>");
                    }
                    html.Append("</tr>");
                    for (int jj = step1 + 3; jj < celsh.UsedRange.Rows.Count; jj++)
                    {
                        r = (Range)celsh.Cells[jj, column];
                        if (r.Text.ToString() == "")
                        {
                            break;
                        }
                        else
                        {
                            html.Append("<tr>");
                            for (int k = column; k <= 8; k++)
                            {
                                rr = (Range)celsh.Cells[jj, k];
                                switch (rr.Text.ToString().Trim().ToUpper())
                                {
                                    case "YES":
                                        html.Append("<td style='color:GREEN;'>" + rr.Text.ToString() + "</td>");
                                        break;
                                    case "NO":
                                        html.Append("<td style='color:RED;'>" + rr.Text.ToString() + "</td>");
                                        AllYes = false;
                                        break;
                                    default:
                                        html.Append("<td>" + rr.Text.ToString() + "</td>");
                                        break;
                                }
                            }
                            html.Append("</tr>");
                        }
                    }
                    html2.Append("<tr>");
                    for (int kk = column; kk <= 8; kk++)
                    {
                        if (kk == 3 || kk == 5)
                            continue;
                        r = (Range)celsh.Cells[step2 + 1, kk];
                        html2.Append("<th>" + r.Text.ToString() + "</th>");
                    }
                    html2.Append("</tr>");
                    for (int jj = step2 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
                    {
                        r = (Range)celsh.Cells[jj, column];
                        if (r.Text.ToString() == "")
                            break;
                        else
                        {
                            html2.Append("<tr>");
                            for (int k = column; k <= 8; k++)
                            {
                                if (k == 3 || k == 5)
                                    continue;
                                rr = (Range)celsh.Cells[jj, k];
                                switch (rr.Text.ToString().Trim().ToUpper())
                                {
                                    case "YES":
                                        html2.Append("<td style='color:GREEN;'>" + rr.Text.ToString() + "</td>");
                                        break;
                                    case "NO":
                                        html2.Append("<td style='color:RED;'>" + rr.Text.ToString() + "</td>");
                                        AllYes = false;
                                        break;
                                    default:
                                        html2.Append("<td>" + rr.Text.ToString() + "</td>");
                                        break;
                                }
                                if (k == 2 || k == 4)
                                    k++;
                            }
                            html2.Append("</tr>");
                        }
                    }
                    html3.Append("<tr>");
                    for (int kk = column; kk <= 8; kk++)
                    {
                        if (kk == 3 || kk == 5)
                            continue;
                        r = (Range)celsh.Cells[step3 + 1, kk];
                        html3.Append("<th>" + r.Text.ToString() + "</th>");
                    }
                    html3.Append("</tr>");
                    for (int jj = step3 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
                    {
                        r = (Range)celsh.Cells[jj, column];
                        if (r.Text.ToString().Trim() == "Grand Total" || r.Text.ToString() == "")
                            break;
                        else
                        {
                            string stemp = r.Text.ToString();
                            if (stemp.Trim().ToUpper() == sectorissuer.Trim().ToUpper())
                            //if (stemp.Substring((temp.IndexOf('-') + 1), (stemp.ToString().Length - stemp.ToString().IndexOf('-') - 1)) == sectorissuer.ToUpper())
                            {
                                html3.Append("<tr>");
                                for (int k = column; k <= 8; k++)
                                {
                                    if (k == 3 || k == 5)
                                        continue;
                                    rr = (Range)celsh.Cells[jj, k];
                                    switch (rr.Text.ToString().Trim().ToUpper())
                                    {
                                        case "YES":
                                            html3.Append("<td style='color:GREEN;'>" + rr.Text.ToString() + "</td>");
                                            break;
                                        case "NO":
                                            html3.Append("<td style='color:RED;'>" + rr.Text.ToString() + "</td>");
                                            AllYes = false;
                                            break;
                                        default:
                                            html3.Append("<td>" + rr.Text.ToString() + "</td>");
                                            break;
                                    }
                                    if (k == 2 || k == 4)
                                        k++;
                                }
                                html3.Append("</tr>");
                            }
                        }
                    }

                    html4.Append("<tr>");
                    for (int kk = column; kk <= 8; kk++)
                    {
                        if (kk == 3 || kk == 5)
                            continue;
                        r = (Range)celsh.Cells[step3 + 1, kk];
                        html4.Append("<th>" + r.Text.ToString() + "</th>");
                    }
                    html4.Append("</tr>");

                    for (int jj = step3 + 2; jj < celsh.UsedRange.Rows.Count; jj++)
                    {
                        r = (Range)celsh.Cells[jj, column];
                        if (r.Text.ToString().Trim() == "Grand Total" || r.Text.ToString() == "")
                            break;
                        else
                        {
                            rr = (Range)celsh.Cells[jj, 4];
                            string stemp = rr.Text.ToString();
                            float fstemp = 0;
                            if (float.TryParse(stemp.Substring(0, stemp.Length - 1), out fstemp))
                            {
                                if (fstemp > 3.5)
                                {
                                    html4.Append("<tr>");
                                    for (int k = column; k <= 8; k++)
                                    {
                                        if (k == 3 || k == 5)
                                            continue;
                                        rr = (Range)celsh.Cells[jj, k];
                                        switch (rr.Text.ToString().Trim().ToUpper())
                                        {
                                            case "YES":
                                                html4.Append("<td style='color:GREEN;'>" + rr.Text.ToString() + "</td>");
                                                break;
                                            case "NO":
                                                html4.Append("<td style='color:RED;'>" + rr.Text.ToString() + "</td>");
                                                AllYes = false;
                                                break;
                                            default:
                                                html4.Append("<td>" + rr.Text.ToString() + "</td>");
                                                break;
                                        }
                                        if (k == 2 || k == 4)
                                            k++;
                                    }
                                    html4.Append("</tr>");
                                }
                            }
                        }
                    }
                    //celapp.DisplayAlerts = false;
                    //celapp.Quit();
                    celwbook.Save();
                    //celwbook.Close();
                    impersonateValidUser(username, Domain, password);
                    fi1 = new FileInfo(tempworkpath +@"\"+ filename);
                    fi1.CopyTo(parentpath +@"\"+ filename, true);
                    undoImpersonation();
                    //impersonateValidUser("bbuser1", "WSG053", "bbUser01");
                    //if (celapp != null)
                    //{
                    //    Process process = new Process();
                    //    foreach (int pID in listNew.Except(listOld))
                    //    {
                    //        process = System.Diagnostics.Process.GetProcessById(pID);
                    //        process.Kill();
                    //    }

                    //    //Process.GetProcessesByName("Excel")[0].Kill();
                    //}
                    //undoImpersonation();
                    StringBuilder finalhtml = new StringBuilder();
                    finalhtml.Append("<div><h2>SUMMARY OF GUIDELINES CHECK</h2><h4>LIMITS</h4><table border='1'>");
                    finalhtml.Append(html.ToString());
                    finalhtml.Append("</table><p>*Note: Exception for Guideline 5 applies only if Guideline 4 is met</p><h4>LIMITS BY SECURITY TYPES</h4><table border='1'>");
                    finalhtml.Append(html2.ToString());
                    finalhtml.Append("</table><h4>LIMITS BY ISSUER</h4><table border='1'>");
                    finalhtml.Append(html3.ToString());
                    finalhtml.Append("</table><h4>LIMITS BY ISSUER(>3.5%)</h4><table border='1'>");
                    finalhtml.Append(html4.ToString());
                    finalhtml.Append("</table></div>");
                    htmlResult.htmlfortable = finalhtml.ToString();
                    htmlResult.Flag = AllYes;
                    impersonateValidUser("bbuser1", "WSG053", "bbUser01");
                    tw.WriteLine("Done");
                    undoImpersonation();
                    return 0;
                }
                catch (Exception e)
                {
                    return -1;

                }
                finally
                {
                    impersonateValidUser("bbuser1", "WSG053", "bbUser01");
                    tw.Close();
                    undoImpersonation();
                    //impersonateValidUser("bbuser1", "WSG053", "bbUser01");
                    //if (celapp != null)
                    //{
                    //    Process process = new Process();
                    //    foreach (int pID in listNew.Except(listOld))
                    //    {
                    //        process = System.Diagnostics.Process.GetProcessById(pID);
                    //        process.Kill();
                    //    }

                    //    //Process.GetProcessesByName("Excel")[0].Kill();
                    //}
                    //undoImpersonation();
                }
            }
            else
                return 3;
        }