//Update sent on if it has not already been sent public string Final_Sent_Merge(CapitalFrm cform, int i) { string tprString = ""; if ((string)cform.dgvFinalView.Rows[i].Cells[(int)Capital.fTpr].Value == "PAY") { tprString = " t.Sent_AMT = t.Sent_AMT - " + Convert.ToDouble(cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTamount].Value); } else { tprString = " t.Sent_AMT = t.Sent_AMT + " + Convert.ToDouble(cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTamount].Value); } SSQL = "Merge INTO OPERATIONS.COST_OF_CAPITAL_SCF t " + " Using( " + " Select '" + (string)cform.dgvFinalView.Rows[i].Cells[(int)Capital.fTcomment].Value + "' TCOMMENT, " + " ' " + (string)cform.dgvFinalView.Rows[i].Cells[(int)Capital.fPortfolio].Value + "' PORTFOLIO " + " From DUAL ) d " + " on (d.TCOMMENT = t.TCOMMENT and d.PORTFOLIO = t.PORTFOLIO) " + " When MATCHED THEN " + " UPDATE SET t.Sent_ON = TO_DATE(SYSDATE), " + tprString; return(SSQL); }
public void MergeFeedView(CapitalFrm cform) //Display DGV on Feed tab { Connection connection = new Connection(); GetSQL sql = new GetSQL(); //loop through feed view and use merge to insert if unmatched for (int i = 0; i < cform.dgvFeedView.Rows.Count; ++i) { connection.SendQuery(sql.CapitalCost_of_capital_Merge(cform, i)); } }
public void DisplayComboBox(CapitalFrm cform) //Populate the Month Year Combo Box { DateTime myDate = DateTime.Now.Date.AddMonths(-1); // Show last 6 months in combobox for (int i = 1; i < 7; i++) { cform.dateCB.Items.Add(myDate.ToString("MMMM yyyy", CultureInfo.InvariantCulture)); myDate = myDate.AddMonths(-1); } // Show first index as default cform.dateCB.SelectedIndex = 0; }
public void AppInit(CapitalFrm cform) //Main Setup for form { //Variables GetUserInfo user = new GetUserInfo(); user.SetUserInfo(signOnName: Environment.UserName, signOnTime: DateTime.Now); //ComboBox DisplayComboBox(cform); //Log on info cform.userInfoTb.Text = user.SignOnName.ToUpper() + ": " + user.SignOnTime.ToShortDateString(); }
public void GenerateFile(CapitalFrm cform) { // Test paths //string archivefileloc = @"\\#\Temp\"; //string tempfileloc = @"\\#\Cost of Capital\Temp\"; //string finalfileloc = @"C:\Temp\"; string archivefileloc = @"\\#\Cost of Capital\"; string tempfileloc = @"\\#\Temp\"; string finalfileloc = @"\\#\MurexFiles\"; string finalfilename = DateTime.Now.ToString("yyyyMMdd") + DateTime.Now.ToString("HHmmss") + "_COSTCAPSCF_new.csv"; string archivefilename = DateTime.Now.ToString("MMMM") + "_" + DateTime.Now.ToString("yyyy") + "_COSTCAPSCF.xls"; Connection connection = new Connection(); GetSQL sql = new GetSQL(); //loop through feed view and use merge to update values for (int i = 0; i < cform.dgvFeedView.Rows.Count; ++i) { connection.SendQuery(sql.Final_Sent_Merge(cform, i)); } // Create and save CSV try { // CreateCSV CreateCSV(cform.dgvFinalView, Path.Combine(tempfileloc, finalfilename)); File.Move(Path.Combine(tempfileloc, finalfilename), Path.Combine(finalfileloc, finalfilename)); //Archive Data CreateExcel(cform, archivefileloc, archivefilename); //Clear final view to force action cform.dgvFinalView.Rows.Clear(); cform.dgvFinalView.Refresh(); //Select tab cform.CapitalTabs.SelectTab(1); } catch (Exception ex) { MessageBox.Show("Exception occurred while creating files " + ex.Message); } }
//Cost of capital view public string Cost_of_capital_View(CapitalFrm cform) { // Text Formated as 'Month[0] Year[1]' String[] dateText = cform.dateCB.Text.Split(' '); SSQL = " SELECT C.trader_description Trader, P.Portfolio, " + "SUM(C.mtd_credit_charges) MTD, SUM(C.ytd_credit_charges) YTD, P.FUND_ID FUND " + "FROM table(RPTTRADE.populate_cost_of_cap('" + DateTime.ParseExact(dateText[0], "MMMM", CultureInfo.CurrentCulture).Month + "','" + dateText[1] + "')) C " + " INNER Join (Select distinct t.TRADER_ID, t.PORTFOLIO_LABEL Portfolio, trim(UPPER(t.fund_ID)) FUND_ID, F.NAME " + " FROM ODSTRADE.TRADER_SUB_OBJECTIVE t " + " Inner join ODSTRADE.fund f on F.FUND_ID= T.FUND_ID " + " Where t.ACTIVE_FLAG = 'Y' and T.FUND_ID in (Select FUND_ID From OPERATIONS.VW_FUND_ACTIVE) " + " AND t.PORTFOLIO_LABEL is not null " + " ) P on P.TRADER_ID=C.TRADER_ID and P.Name=C.fund_NAME " + " Where P.FUND_ID not in ('XH') " + "GROUP BY C.trader_description,P.Portfolio, P.FUND_ID " + "order by C.trader_description "; return(SSQL); }
//Cost of Capital Merge public string CapitalCost_of_capital_Merge(CapitalFrm cform, int i) { //(string)cform.dgvCostView.Rows[i].Cells[(int)Capital.portfolio].Value SSQL = "Merge INTO OPERATIONS.COST_OF_CAPITAL_SCF t " + " Using( " + " Select '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTcomment].Value + "' TCOMMENT, " + " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fPortfolio].Value + "' PORTFOLIO " + " From DUAL ) d " + " on (d.TCOMMENT = t.TCOMMENT and d.PORTFOLIO = t.PORTFOLIO) " + " When NOT MATCHED THEN " + " Insert ( " + " t.TYPOLOGY, t.PORTFOLIO, t.CPTY, t.INST, t.FX, t.TPR, " + " t.TDATE, t.TAMOUNT, t.TFX, t.TCOMMENT, t.STRATEGY, t.BRKSRCE, " + " t.BRKDEST, t.UNIQUE_ID, t.FUND, t.ACCT_NUM, t.SENT_AMT) " + "VALUES ( " + " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTypology].Value + "', " // Typology + " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fPortfolio].Value + "', " // Portfolio + " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fCpty].Value + "', " // CPTY + " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fInst].Value + "', " // INST + " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fFx].Value + "', " // FX + " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTpr].Value + "', " // TPR + " TO_DATE('" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTdate].Value + "', 'MM/DD/YYYY'), " // TDATE + " " + Convert.ToDouble(cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTamount].Value) + ", " // TAMOUNT + " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTfx].Value + "', " // TFX + " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTcomment].Value + "', " // TCOMMENT + " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fStrategy].Value + "', " // STRATEGY + " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fBrksrce].Value + "', " // BRKSRCE + " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fBrkdest].Value + "', " // BRKDEST + " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fUnique_Id].Value + "', " // UNIQUE_ID + " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fFund].Value + "', " // FUND + " '" + (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fAcct_Num].Value + "', " // ACCT_NUM + " " + Convert.ToDouble(cform.dgvFeedView.Rows[i].Cells[(int)Capital.fSent_Amt].Value) + " " // SENT_AMT + " ) "; return(SSQL); }
public void DisplayCapitalView(CapitalFrm cform) //Display DGV on Cost of Capital { //Clear all DGVs cform.dgvFinalView.Rows.Clear(); cform.dgvFinalView.Refresh(); cform.dgvFeedView.Rows.Clear(); cform.dgvFeedView.Refresh(); cform.dgvCostView.Rows.Clear(); cform.dgvCostView.Refresh(); // Variables Connection connection = new Connection(); GetSQL sql = new GetSQL(); OdbcDataReader dataReader = connection.RunQuery(sql.Cost_of_capital_View(cform)); if (dataReader.HasRows) { while (dataReader.Read()) { cform.dgvCostView.Rows.Add(dataReader["TRADER"].ToString(), dataReader["PORTFOLIO"].ToString(), dataReader["MTD"], dataReader["YTD"], dataReader["FUND"].ToString() ); } //Add fund level totals double ytdCI, ytdQA, ytdQE, ytdQO, ytdQM; double mtdCI, mtdQA, mtdQE, mtdQO, mtdQM; ytdCI = ytdQA = ytdQE = ytdQO = ytdQM = 0; mtdCI = mtdQA = mtdQE = mtdQO = mtdQM = 0; for (int i = 0; i < cform.dgvCostView.Rows.Count; ++i) { switch ((string)cform.dgvCostView.Rows[i].Cells[(int)Capital.fund].Value) { case "CI": ytdCI += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.ytd].Value); mtdCI += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value); break; case "QA": ytdQA += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.ytd].Value); mtdQA += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value); break; case "QE": ytdQE += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.ytd].Value); mtdQE += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value); break; case "QO": ytdQO += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.ytd].Value); mtdQO += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value); break; case "QM": ytdQM += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.ytd].Value); mtdQM += Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value); break; } } //Display totals if MTD not 0 int nRowIndex = 0; if (mtdCI != 0 || ytdCI != 0) { nRowIndex = cform.dgvCostView.Rows.Count; cform.dgvCostView.Rows.Add("CI", "CAXTON INTL", mtdCI, ytdCI, "CI"); cform.dgvCostView.Rows[nRowIndex].DefaultCellStyle.BackColor = Color.Beige; } if (mtdQA != 0 || ytdQA != 0) { nRowIndex = cform.dgvCostView.Rows.Count; cform.dgvCostView.Rows.Add("QA", "QA-TREASURY", mtdQA, ytdQA, "QA"); cform.dgvCostView.Rows[nRowIndex].DefaultCellStyle.BackColor = Color.Beige; } if (mtdQE != 0 || ytdQE != 0) { nRowIndex = cform.dgvCostView.Rows.Count; cform.dgvCostView.Rows.Add("QE", "QE-TREASURY", mtdQE, ytdQE, "QE"); cform.dgvCostView.Rows[nRowIndex].DefaultCellStyle.BackColor = Color.Beige; } if (mtdQO != 0 || ytdQO != 0) { nRowIndex = cform.dgvCostView.Rows.Count; cform.dgvCostView.Rows.Add("QO", "QO-TREASURY", mtdQO, ytdQO, "QO"); cform.dgvCostView.Rows[nRowIndex].DefaultCellStyle.BackColor = Color.Beige; } if (mtdQM != 0 || ytdQM != 0) { nRowIndex = cform.dgvCostView.Rows.Count; cform.dgvCostView.Rows.Add("QM", "QM-TREASURY", mtdQM, ytdQM, "QM"); cform.dgvCostView.Rows[nRowIndex].DefaultCellStyle.BackColor = Color.Beige; } } // close dataReader.Close(); }
public void EnrichFinalFeed(CapitalFrm cform, string sent_unid, string fund) //Add fund level totals with updated amounts { double tAmount; string tTpr; tAmount = 0; Connection connection = new Connection(); GetSQL sql = new GetSQL(); OdbcDataReader dataReader = connection.RunQuery(sql.Lookup_Sent_Info(sent_unid)); //If fund matches update amount for items beign sent for (int i = 0; i < cform.dgvFinalView.Rows.Count; ++i) { if (fund == (string)cform.dgvFinalView.Rows[i].Cells[(int)Capital.fFund].Value) { if ((string)cform.dgvFinalView.Rows[i].Cells[(int)Capital.fTpr].Value == "PAY") { tAmount = tAmount - Convert.ToDouble(cform.dgvFinalView.Rows[i].Cells[(int)Capital.fTamount].Value); } else { tAmount = tAmount + Convert.ToDouble(cform.dgvFinalView.Rows[i].Cells[(int)Capital.fTamount].Value); } } } //Update direction of TPR if (tAmount > 0) { tTpr = "PAY"; } else { tTpr = "RECEIVE"; } int nRowIndex = 0; //loop through and update amounts //apply to feed if (dataReader.HasRows) { nRowIndex = cform.dgvFinalView.Rows.Count; cform.dgvFinalView.Rows.Add( (string)dataReader["TYPOLOGY"], // Typology (string)dataReader["PORTFOLIO"], // Portfolio (string)dataReader["CPTY"], // CPTY (string)dataReader["INST"], // INST (string)dataReader["FX"], // FX tTpr, // TPR ((DateTime)dataReader["TDATE"]).ToString("MM/dd/yyyy"), // TDATE Math.Abs(tAmount), // TAMOUNT (string)dataReader["TFX"], // TFX dataReader["TTRANS"].ToString(), // Ttrans (string)dataReader["TCOMMENT"], // TCOMMENT (string)dataReader["STRATEGY"], // STRATEGY dataReader["BRKTYPE"].ToString(), // BRKTYPE (string)dataReader["BRKSRCE"], // BRKSRCE (string)dataReader["BRKDEST"], // BRKDEST (string)dataReader["UNIQUE_ID"], // UNIQUE_ID (string)dataReader["FUND"], // FUND (string)dataReader["ACCT_NUM"] // ACCT_NUM ); cform.dgvFinalView.Rows[nRowIndex].DefaultCellStyle.BackColor = Color.Beige; } // close dataReader.Close(); }
public void CreateFinalFeedView(CapitalFrm cform) //Display DGV on FINAL Feed tab { //Clear cform.dgvFinalView.Rows.Clear(); cform.dgvFinalView.Refresh(); //Variables string sent_unid; double sent_amt; bool fundCI, fundQA, fundQE, fundQO, fundQM; fundCI = fundQA = fundQE = fundQO = fundQM = false; Connection connection = new Connection(); GetSQL sql = new GetSQL(); //loop through feed view and use merge to insert if unmatched for (int i = 0; i < cform.dgvFeedView.Rows.Count; ++i) { //Construct Unid sent_unid = (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.portfolio].Value + ":" + cform.dateCB.Text.ToUpper() + " CAP USAGE"; if (sent_unid.Length >= 31) { sent_unid = sent_unid.Substring(0, 31); } OdbcDataReader dataReader = connection.RunQuery(sql.Lookup_Sent_Info(sent_unid)); //Retrieve amount sent if (dataReader.HasRows) { sent_amt = Convert.ToDouble(dataReader["SENT_AMT"]); } else { sent_amt = 0; } //If box checked and it has not been sent an amount if ((treasArray.Contains((string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.portfolio].Value) == false) && ((bool)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fSent_Box].Value == true) && sent_amt == 0 ) { cform.dgvFinalView.Rows.Add( (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTypology].Value, // Typology (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fPortfolio].Value, // Portfolio (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fCpty].Value, // CPTY (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fInst].Value, // INST (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fFx].Value, // FX (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTpr].Value, // TPR (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTdate].Value, // TDATE Convert.ToDouble(cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTamount].Value), // TAMOUNT (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTfx].Value, // TFX (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTtrans].Value, // Ttrans (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fTcomment].Value, // TCOMMENT (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fStrategy].Value, // STRATEGY (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fBrktype].Value, // BRKTYPE (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fBrksrce].Value, // BRKSRCE (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fBrkdest].Value, // BRKDEST (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fUnique_Id].Value, // UNIQUE_ID (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fFund].Value, // FUND (string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fAcct_Num].Value // ACCT_NUM ); //set fund bools switch ((string)cform.dgvFeedView.Rows[i].Cells[(int)Capital.fFund].Value) { case "CI": fundCI = true; break; case "QA": fundQA = true; break; case "QE": fundQE = true; break; case "QO": fundQO = true; break; case "QM": fundQM = true; break; } } // close dataReader.Close(); } //Call enrich feed for treasury if (fundCI == true) { sent_unid = "CAXTON INTL" + ":" + cform.dateCB.Text.ToUpper() + " CAP USAGE"; if (sent_unid.Length >= 31) { sent_unid = sent_unid.Substring(0, 31); } EnrichFinalFeed(cform, sent_unid, "CI"); } if (fundQA == true) { sent_unid = "QA-TREASURY" + ":" + cform.dateCB.Text.ToUpper() + " CAP USAGE"; if (sent_unid.Length >= 31) { sent_unid = sent_unid.Substring(0, 31); } EnrichFinalFeed(cform, sent_unid, "QA"); } if (fundQE == true) { sent_unid = "QE-TREASURY" + ":" + cform.dateCB.Text.ToUpper() + " CAP USAGE"; if (sent_unid.Length >= 31) { sent_unid = sent_unid.Substring(0, 31); } EnrichFinalFeed(cform, sent_unid, "QE"); } if (fundQO == true) { sent_unid = "QO-TREASURY" + ":" + cform.dateCB.Text.ToUpper() + " CAP USAGE"; if (sent_unid.Length >= 31) { sent_unid = sent_unid.Substring(0, 31); } EnrichFinalFeed(cform, sent_unid, "QO"); } if (fundQM == true) { sent_unid = "QM-TREASURY" + ":" + cform.dateCB.Text.ToUpper() + " CAP USAGE"; if (sent_unid.Length >= 31) { sent_unid = sent_unid.Substring(0, 31); } EnrichFinalFeed(cform, sent_unid, "QM"); } //Select tab cform.CapitalTabs.SelectTab(2); }
public void DisplayFeedView(CapitalFrm cform) //Display DGV on Feed tab { //Clear cform.dgvFeedView.Rows.Clear(); cform.dgvFeedView.Refresh(); Connection connection = new Connection(); GetSQL sql = new GetSQL(); //Variables string cpty, tpr, strat, sent_unid, sent_date; double amt, sent_amt; bool send_bool; //loop through capital view to create feed view for (int i = 0; i < cform.dgvCostView.Rows.Count; ++i) { //If MTD has a value add to feed if (Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value) != 0) { //----Set variables switch ((string)cform.dgvCostView.Rows[i].Cells[(int)Capital.trader].Value) { case "CI": cpty = "CXI"; strat = "CAP USAGE"; break; case "QA": case "QE": case "QO": case "QM": cpty = "SCF"; strat = "CAP USAGE"; break; default: cpty = "SCF"; strat = "ADJUSTMENTS"; break; } sent_unid = (string)cform.dgvCostView.Rows[i].Cells[(int)Capital.portfolio].Value + ":" + cform.dateCB.Text.ToUpper() + " CAP USAGE"; if (sent_unid.Length >= 31) { sent_unid = sent_unid.Substring(0, 31); } OdbcDataReader dataReader = connection.RunQuery(sql.Lookup_Sent_Info(sent_unid)); if (dataReader.HasRows) { sent_date = ((DateTime)dataReader["Sent_ON"]).ToString("MM/dd/yyyy"); sent_amt = Convert.ToDouble(dataReader["SENT_AMT"]); } else { sent_date = null; sent_amt = 0; } if (Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value) > 0) { tpr = "RECEIVE"; amt = Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value); } else { tpr = "PAY"; amt = (Convert.ToDouble(cform.dgvCostView.Rows[i].Cells[(int)Capital.mtd].Value) * -1); } if (sent_date == null) { send_bool = true; } else if ((treasArray.Contains((string)cform.dgvCostView.Rows[i].Cells[(int)Capital.portfolio].Value) == false) && (Math.Abs(amt) > Math.Abs(sent_amt))) { send_bool = true; } else { send_bool = false; } // Fill out feed DGV cform.dgvFeedView.Rows.Add("IMPUTED INTEREST", //Typology (string)cform.dgvCostView.Rows[i].Cells[(int)Capital.portfolio].Value, //Portfolio cpty, //cpty "IMPIT", //Inst "USD", //FX tpr, //TPR DateTime.Now.ToString("MM/dd/yyyy"), //TDATE amt, //AMOUNT "USD", //TFX "", //TTRANS cform.dateCB.Text.ToUpper() + " CAP USAGE", //TCOMMENT strat, //STRATEGY "", //BRKTYPE (string)cform.dgvCostView.Rows[i].Cells[(int)Capital.portfolio].Value, //BRKSRC "ADJLAA", //BRKDEST "COSTOFCAP_SCF_" + DateTime.Now.ToString("yymmddhhMMss") + i.ToString(), //UNID (string)cform.dgvCostView.Rows[i].Cells[(int)Capital.fund].Value, //FUND "CAXT", //ACCT sent_date, sent_amt, send_bool ); // close dataReader.Close(); } } }
public void CreateExcel(CapitalFrm cform, string filepath, string filename) { object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application() { DisplayAlerts = false }; //Add workbook Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); // Loop through DGVS for (int i = 1; i < 4; ++i) { // Copy DataGridView results to clipboard switch (i) { case 1: //cform.dgvCostView.RowHeadersVisible = false; //No hidden row CopyAlltoClipboard(cform.dgvCostView); break; case 2: CopyAlltoClipboard(cform.dgvFeedView); break; case 3: CopyAlltoClipboard(cform.dgvFinalView); break; } //select sheet Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(i); // Paste clipboard results to worksheet range xlWorkSheet.Activate(); Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); xlWorkSheet.get_Range("A2").Select(); ReleaseObject(xlWorkSheet); ReleaseObject(CR); } // Save the excel file xlWorkBook.SaveAs(Path.Combine(filepath, filename), Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); ReleaseObject(xlWorkBook); ReleaseObject(xlexcel); // Clear Clipboard and DataGridView selection Clipboard.Clear(); cform.dgvFinalView.ClearSelection(); cform.dgvFeedView.ClearSelection(); cform.dgvCostView.ClearSelection(); }