private void AddSheetToExcel(ExcelObj._Worksheet NewSheet) { if (!string.IsNullOrEmpty(this.StrNewFilePassword)) { this.xlWorkBookTarget = base.xlApp.Workbooks.Open(StrNewFilePath, misValue, misValue, misValue, StrNewFilePassword); this.IsWorkBookTargetOpened = true; } else { this.xlWorkBookTarget = base.xlApp.Workbooks.Open(StrNewFilePath); this.IsWorkBookTargetOpened = true; } this.xlWorkSheetTarget = (ExcelObj.Worksheet) this.xlWorkBookTarget.Sheets[this.xlWorkBookTarget.Sheets.Count]; NewSheet.Copy(Type.Missing, xlWorkSheetTarget); if (StrNewSheetName != null && !string.IsNullOrEmpty(StrNewSheetName)) { this.xlWorkSheetTarget = (ExcelObj.Worksheet) this.xlWorkBookTarget.Sheets.Item[this.xlWorkBookTarget.Sheets.Count]; this.xlWorkSheetTarget.Name = StrNewSheetName; } xlWorkBookTarget.Save(); xlWorkBookTarget.Close(); this.IsWorkBookTargetOpened = false; ReleaseObj.Marshal.ReleaseComObject(NewSheet); NewSheet = null; this.ClearTargetObject(); }
public void CopySheet(long NewSheetIndex = -1) { if (CheckSheetIndex(NewSheetIndex)) { NewSheetIndex = SheetTotal(); } CurrSheet.Copy(CurrSheets[NewSheetIndex]); }
public static void CreateWorkbook(string suf, int MaxColumnLength, string dir, int MinG, int MaxG, int MinR, int MaxR) { var dirs = FileReader.DigForFiles(dir, suf); // creating Excel Application Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); // creating new WorkBook within Excel application Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); // creating new Excelsheet in workbook Microsoft.Office.Interop.Excel._Worksheet worksheet = null; Sheets xlSheets = null; xlSheets = workbook.Sheets as Sheets; // see the excel sheet behind the program app.Visible = false; //Select the sheet worksheet = workbook.Worksheets[1]; //Rename the sheet worksheet.Name = "Summary"; string[] names = GetNames(dirs); Console.WriteLine("Processing Files:"); for (int i = 0; i < names.Length; i++) { Console.WriteLine(names[i]); Microsoft.Office.Interop.Excel._Workbook csvWorkbook = app.Workbooks.Open(dirs[i]); Microsoft.Office.Interop.Excel._Worksheet worksheetCSV = ((Microsoft.Office.Interop.Excel._Worksheet)csvWorkbook.Worksheets[1]); worksheetCSV.Copy(xlSheets[1]); xlSheets[1].Name = names[i]; ((_Worksheet)xlSheets[1]).Cells[1, 24] = "Mean_Cell - Mean_Noise"; for (int row = 2; row < MaxColumnLength; row++) { ((_Worksheet)xlSheets[1]).Cells[row, 24] = "=E" + row + "-U" + row; } ((_Worksheet)xlSheets[1]).Cells[1, 25] = "Max_Cell"; ((_Worksheet)xlSheets[1]).Cells[2, 25] = "=MAX(X2:X" + MaxColumnLength + ")"; // Exit from the application csvWorkbook.Close(); } worksheet.Move(Before: workbook.Sheets[1]); Console.WriteLine("Preparing the summary..."); //worksheet.Cells[row, column] = "=cell57_Q2_Ch0_Green_Results!E2"; int currentColumn = 1; int currentRow = 1; int increment = names.Length; for (currentRow = 1; currentRow < MaxColumnLength; currentRow++) { worksheet.Cells[currentRow, currentColumn] = "=" + names[0] + "!C" + currentRow; } currentRow = 1; for (int i = 0; i < names.Length; i++) { //Area cell currentColumn = i + 2; worksheet.Cells[currentRow, currentColumn] = "Area_Cell_" + names[i]; //Mean cell currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Mean_Cell_" + names[i]; //Area noise currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Area_Noise_" + names[i]; //Mean noise currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Mean_Noise_" + names[i]; //Area spots currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Area_Spot_" + names[i]; //Mean spots currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Mean_Spot_" + names[i]; //Mean-Noise cell currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Mean-Noise_Cell_" + names[i]; //Max cell currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Max_Cell_" + names[i]; worksheet.Cells[currentRow + 1, currentColumn] = "=" + names[i] + "!Y2"; //Max-Results currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Max-Results_" + names[i]; //Results currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "Results_" + names[i]; //ResultsTo0 currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "To0_Results_" + names[i]; //ResultsTo1 currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "nTo0_Results_" + names[i]; } //Mean result worksheet.Cells[currentRow, currentColumn + 1] = "nAvgMob"; worksheet.Cells[currentRow, currentColumn + 2] = "nnAvgMob"; worksheet.Cells[currentRow, currentColumn + 3] = "nStDevMob"; worksheet.Cells[currentRow, currentColumn + 4] = "AvgMob"; worksheet.Cells[currentRow, currentColumn + 5] = "StDevMob"; int spotSignal, MaxCell, CellMinusNoise; for (currentRow = 2; currentRow < MaxColumnLength; currentRow++) { for (int i = 0; i < names.Length; i++) { //Area cell currentColumn = i + 2; worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!D" + currentRow; //Mean cell currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!E" + currentRow; //Area noise currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!T" + currentRow; //Mean noise currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!U" + currentRow; //Area spots currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "=AVERAGE(" + names[i] + "!H" + currentRow + "," + names[i] + "!L" + currentRow + "," + names[i] + "!P" + currentRow + ")"; //Mean spots currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "=AVERAGE(" + names[i] + "!I" + currentRow + "," + names[i] + "!M" + currentRow + "," + names[i] + "!Q" + currentRow + ")"; spotSignal = currentColumn; //Cell-Noise mean currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!X" + currentRow; CellMinusNoise = currentColumn; //Max Cell currentColumn += increment; MaxCell = currentColumn; //Min-Results currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "=" + ColumnLabel(MaxCell) + "2-(" + ColumnLabel(MaxCell) + "2/" + ColumnLabel(CellMinusNoise) + currentRow + ")*(" + ColumnLabel(spotSignal) + currentRow + "-" + names[i] + "!U" + currentRow + ")"; //Results currentColumn += increment; worksheet.Cells[currentRow, currentColumn] = "=(" + ColumnLabel(MaxCell) + "2/" + ColumnLabel(CellMinusNoise) + currentRow + ")*(" + ColumnLabel(spotSignal) + currentRow + "-" + names[i] + "!U" + currentRow + ")"; } } //calculations currentColumn++; int MaxMinResults = 2 + 8 * increment; int ResultsTo0 = currentColumn; for (int i = 0; i < names.Length; i++) { for (currentRow = 2; currentRow < MaxColumnLength; currentRow++) { if (suf == "Green") { worksheet.Cells[currentRow, currentColumn] = "=(" + ColumnLabel(MaxMinResults) + currentRow + "-AVERAGE(" + ColumnLabel(MaxMinResults) + MinG + ":" + ColumnLabel(MaxMinResults) + MaxG + ")" + ")"; } else { worksheet.Cells[currentRow, currentColumn] = "=(" + ColumnLabel(MaxMinResults) + currentRow + "-AVERAGE(" + ColumnLabel(MaxMinResults) + MinR + ":" + ColumnLabel(MaxMinResults) + MaxR + ")" + ")"; } } currentColumn++; MaxMinResults++; } //NormTo1 MaxMinResults = currentColumn - increment; for (int i = 0; i < names.Length; i++) { for (currentRow = 2; currentRow < MaxColumnLength; currentRow++) { worksheet.Cells[currentRow, currentColumn] = "=(" + ColumnLabel(MaxMinResults) + currentRow + "/MAX(" + ColumnLabel(MaxMinResults) + "2" + ":" + ColumnLabel(MaxMinResults) + MaxColumnLength + ")" + ")"; } currentColumn++; MaxMinResults++; } { int MinInd = currentColumn - increment; int MaxInd = currentColumn - 1; int Mean = currentColumn; int nMean = currentColumn + 1; int StDev = currentColumn + 2; for (currentRow = 2; currentRow < MaxColumnLength; currentRow++) { worksheet.Cells[currentRow, Mean] = "=" + "AVERAGE(" + ColumnLabel(MinInd) + currentRow + ":" + ColumnLabel(MaxInd) + currentRow + ")"; worksheet.Cells[currentRow, StDev] = "=" + "STDEV.S(" + ColumnLabel(MinInd) + currentRow + ":" + ColumnLabel(MaxInd) + currentRow + ")"; } for (currentRow = 2; currentRow < MaxColumnLength; currentRow++) { worksheet.Cells[currentRow, nMean] = "=(" + ColumnLabel(Mean) + currentRow + "/MAX(" + ColumnLabel(Mean) + "2:" + ColumnLabel(Mean) + MaxColumnLength + ")" + ")"; } MinInd -= increment; MaxInd -= increment; Mean = currentColumn + 3; StDev = currentColumn + 4; for (currentRow = 2; currentRow < MaxColumnLength; currentRow++) { worksheet.Cells[currentRow, Mean] = "=" + "AVERAGE(" + ColumnLabel(MinInd) + currentRow + ":" + ColumnLabel(MaxInd) + currentRow + ")"; worksheet.Cells[currentRow, StDev] = "=" + "STDEV.S(" + ColumnLabel(MinInd) + currentRow + ":" + ColumnLabel(MaxInd) + currentRow + ")"; } } string name = dir.Substring(dir.LastIndexOf("\\") + 1, dir.Length - dir.LastIndexOf("\\") - 1); name = dir + "\\" + "Res_" + suf + ".xlsx"; app.Visible = true; workbook.SaveAs(name, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Console.WriteLine("Results saved to :\n" + name); app.Quit();//Check is working Console.WriteLine("Done!"); }
public static void Open_xls_Files() { // creating Excel Application Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); // creating new WorkBook within Excel application Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Open(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\SPA特价.xlsx"); //app.Workbooks.Add(Type.Missing); // creating new Excelsheet in workbook Microsoft.Office.Interop.Excel._Worksheet worksheet = workbook.Sheets[1]; // see the excel sheet behind the program app.Visible = false; // get the reference of first sheet. By default its name is Sheet1. // store its reference to worksheet worksheet = workbook.Sheets["SPA台湾"]; worksheet = workbook.ActiveSheet; // changing the name of active sheet worksheet.Copy(Type.Missing, worksheet); Microsoft.Office.Interop.Excel.Worksheet wcSheet = workbook.Sheets[2]; wcSheet.Name = "ExSPA台湾"; wcSheet.UsedRange.UnMerge(); Console.WriteLine(wcSheet.UsedRange.Rows.Count); Range Ro1 = null; Range Rd1 = null; Range Ro2 = null; Range Rd2 = null; Range tempR = null; for (int i = 1; i <= wcSheet.UsedRange.Rows.Count; i++) { try { string s = wcSheet.UsedRange.Cells[i, 1].Value2.ToString(); Console.WriteLine(s); if (wcSheet.Cells[i, 1].Value2 != null)//DBNull) { if (s == "SPA: ") { Ro1 = wcSheet.Range[wcSheet.Cells[i, 1], wcSheet.Cells[i, 9]]; // Console.WriteLine("=============="+wcSheet.Cells[i, 1].Value2.ToString()); Rd1 = wcSheet.Range[wcSheet.Cells[i + 2, 10], wcSheet.Cells[i + 2, 19]]; Ro1.Cut(Rd1); ///////////////////////////////////// Ro2 = wcSheet.Range[wcSheet.Cells[i + 1, 1], wcSheet.Cells[i + 1, 9]]; // Console.WriteLine(Ro.Cells.Count); Rd2 = wcSheet.Range[wcSheet.Cells[i + 4, 10], wcSheet.Cells[i + 4, 19]]; Ro2.Cut(Rd2); // Console.WriteLine(wcSheet.UsedRange.Cells[i,1]); i = i + 4; } //Rd2.Copy(wcSheet.Range[wcSheet.Cells[i + 4, 10], wcSheet.Cells[i + 4, 19]]); tempR = wcSheet.Range[wcSheet.Cells[i, 10], wcSheet.Cells[i, 19]]; //= //Rd2.Copy(wcSheet.Range[wcSheet.Cells[i + 3, 10], wcSheet.Cells[i + 3, 19]]); Ro2.Copy(tempR); // tempR.Clear(); // Ro2.Clear(); } else if (wcSheet.Cells[i, 1].Value == "") { } } catch (Exception ex) { Console.WriteLine(ex.Message); } } //for (int i = 1; i <= wcSheet.UsedRange.Rows.Count; i++) //{ // try // { // string s = wcSheet.UsedRange.Cells[i, 1].Value2.ToString(); // Console.WriteLine(s); // if (wcSheet.Cells[i, 1].Value2 != null)//DBNull) // { // if (s == "SPA: ") // { // ///////////////////////////////////// // // Ro2 = wcSheet.Range[wcSheet.Cells[i + 1, 1], wcSheet.Cells[i + 1, 9]]; // // Console.WriteLine(Ro.Cells.Count); // // Rd2 = wcSheet.Range[wcSheet.Cells[i + 4, 10], wcSheet.Cells[i + 4, 19]]; // // Ro2.Cut(Rd2); // // Console.WriteLine(wcSheet.UsedRange.Cells[i,1]); // Ro1 = wcSheet.Range[wcSheet.Cells[i, 1], wcSheet.Cells[i, 9]]; // // Console.WriteLine(Ro.Cells.Count); // Rd1 = wcSheet.Range[wcSheet.Cells[i + 2, 10], wcSheet.Cells[i + 2, 19]]; // Ro1.Cut (Rd1); // } // //Rd2.Copy(wcSheet.Range[wcSheet.Cells[i + 4, 10], wcSheet.Cells[i + 4, 19]]); // // tempR = wcSheet.Range[wcSheet.Cells[i, 10], wcSheet.Cells[i, 19]]; // //= //Rd2.Copy(wcSheet.Range[wcSheet.Cells[i + 3, 10], wcSheet.Cells[i + 3, 19]]); // // Ro2.Copy(tempR); // } // } // catch (Exception ex) // { // Console.WriteLine(ex.Message); // } //} //// storing header part in Excel ////for (int i = 1; i < this.DGV1.Columns.Count + 1; i++) ////{ //// worksheet.Cells[1, i] = DGV1.Columns[i - 1].Header;//.HeaderText; // // worksheet.Columns.ColumnWidth //} //// storing Each row and column value to excel sheet //for (int i = 0; i < DGV1.Items.Count - 1; i++) //{ // for (int j = 0; j < DGV1.Columns.Count; j++) // { // // var drview= DGV1.Items[i]; // // worksheet.Cells[i + 2, j + 1] = .Cells[j].Value.ToString(); // // System.Data.DataRowView dataRow = drview; // DataRowView dataRow = (DataRowView)DGV1.Items[i];//.Row.//.Row; // worksheet.Cells[i + 2, j + 1] = dataRow[j].ToString();//dataRow.Row..ToString(); // // worksheet.Cells[i + 2, j + 1] // //MessageBox.Show(DGV1.Items[i].ToString()); // } //} // save the application // Microsoft.Office.Interop.Excel.Worksheet ws = app.ActiveWorkbook.Worksheets[1]; // Microsoft.Office.Interop.Excel.Range range = ws.UsedRange; // ws.Columns.AutoFit(); // ws.Rows.AutoFit(); try { Fname = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\temp" + DateTime.Now.ToString().Trim().Replace(':', '_').Replace('/', '_').Trim() + ".xlsx"; workbook.SaveAs(Fname, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // Exit from the application // workbook.SaveCopyAs(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\列宽output.xls"); } catch (Exception es) { // MessageBox.Show(es.Message); Console.WriteLine(es.Message); } // workbook.Saved = true; app.Quit(); Marshal.ReleaseComObject(app); Console.WriteLine("excel文件导出到当前app运行目录下,命名为output.xls,Excel将关闭退出~"); }
public void TheBingSearchTest() { IWebDriver driver = new ChromeDriver(); driver.Navigate().GoToUrl("https://asknypadmindev.azurewebsites.net/botmain"); driver.Manage().Window.Maximize(); IWebElement imageclick = driver.FindElement(By.XPath("//img[@src='https://asknypadmin.azurewebsites.net/BotFolder/NYPChatBotRight.png']")); imageclick.Click(); IWebElement frame = driver.FindElement(By.XPath(".//iframe[@id='nypBot']")); driver.SwitchTo().Frame(frame); driver.FindElement(By.XPath("/html/body/div[1]/div/div/div[3]/div/input")).Click(); string questions; string columnheader; string newcolumnheaders; string answercells; string responsecells; int count = 0; int yescount = 0; excel.Application x1app = new excel.Application(); x1app.SheetsInNewWorkbook = 1; x1app.Visible = true; excel.Workbook x1workbook = x1app.Workbooks.Open(@"C:\Users\L33539\Desktop\JUNJIE FYP PROJECT\Overall_QnA4"); excel.Workbook NewWorkBook = x1app.Workbooks.Add(); for (int x = 4; x <= 7; x++) //original x=4 x<=23 { excel._Worksheet x1worksheet = x1workbook.Sheets[x]; x1worksheet.Copy(Type.Missing, After: NewWorkBook.Sheets[x - 3]); } Thread.Sleep(2000); for (int w = 2; w <= 5; w++) //original w=2 w<=21 { excel._Worksheet NewWorkSheet = NewWorkBook.Sheets[w]; excel.Range NewWorkRange = NewWorkSheet.UsedRange; int colCount2 = NewWorkRange.Columns.Count; int rowCount2 = NewWorkRange.Rows.Count; NewWorkSheet.Cells[colCount2 + 1][1] = "Response"; NewWorkSheet.Cells[colCount2 + 2][1] = "Timing of response retrieval"; NewWorkSheet.Cells[colCount2 + 3][1] = "Does the answer and response match?"; Console.WriteLine("Rows Count: " + (rowCount2 - 1)); var numOfRes = 1; var newNumOfRes = 0; var testString = ""; var testString2 = ""; for (int i = 2; i <= rowCount2; i++) { questions = NewWorkRange.Cells[1][i].value2; driver.FindElement(By.XPath("//html/body/div[1]/div/div/div[3]/div/input")).SendKeys(questions); //"Send questions" driver.FindElement(By.XPath("//html/body/div[1]/div/div/div[3]/button[1]")).Click(); //click button to send the question Thread.Sleep(1000); var textboxmsg = driver.FindElements(By.ClassName("format-markdown")); var wrongqnmsg = driver.FindElements(By.XPath("//div[@class='wc-list']")); count += 1; newNumOfRes = textboxmsg.Count() + wrongqnmsg.Count(); if (newNumOfRes >= 1) { try { testString = textboxmsg.Last().GetAttribute("outerHTML"); Console.WriteLine("MESSAGES :" + testString); testString2 = wrongqnmsg.Last().GetAttribute("outerHTML"); } catch { } } while (newNumOfRes == numOfRes) { // Pooling Thread.Sleep(1000); textboxmsg = driver.FindElements(By.ClassName("format-markdown")); wrongqnmsg = driver.FindElements(By.XPath("//div[@class='wc-list']")); newNumOfRes = textboxmsg.Count() + wrongqnmsg.Count(); try { testString = textboxmsg.Last().GetAttribute("outerHTML"); testString2 = wrongqnmsg.Last().GetAttribute("outerHTML"); } catch { } } numOfRes = newNumOfRes; // foreach (var textmsg in textboxmsg) // { for (int c = 1; c <= colCount2 + 2; c++) { columnheader = NewWorkRange.Cells[1, c].value2; newcolumnheaders = NewWorkSheet.Cells[1, c].value2; answercells = NewWorkSheet.Cells[i, 2].Text; responsecells = NewWorkSheet.Cells[i, 3].Text; //retrieve response with all tags then remove all the tags below //try //{ var outerhtml = testString; //figure this out later (system no element exception) //} //catch //{ //} outerhtml = outerhtml.Replace("<br />", Environment.NewLine); outerhtml = Regex.Replace(outerhtml, @"<(?!a|/a|ol|ul[\x20/>])[^<>]+>", string.Empty); outerhtml = outerhtml.TrimEnd('\r', '\n'); //remove carriage return //all to replace some to match outerhtml = outerhtml.Replace("“", "\""); outerhtml = outerhtml.Replace("”", "\""); outerhtml = outerhtml.Replace("<ul>", "-"); outerhtml = outerhtml.Replace("‘", "'"); outerhtml = outerhtml.Replace("’", "'"); var outerhtml2 = wrongqnmsg.Last().GetAttribute("outerHTML"); outerhtml2 = outerhtml2.Replace("<br />", Environment.NewLine); outerhtml2 = Regex.Replace(outerhtml2, @"<(?!a|/a|ol|ul[\x20/>])[^<>]+>", string.Empty); outerhtml2 = outerhtml2.TrimEnd('\r', '\n'); //remove carriage return //all to replace some to match outerhtml2 = outerhtml2.Replace("“", "\""); outerhtml2 = outerhtml2.Replace("”", "\""); outerhtml2 = outerhtml2.Replace("<ul>", "-"); outerhtml2 = outerhtml2.Replace("‘", "'"); outerhtml2 = outerhtml2.Replace("’", "'"); //to replace ol with numerics int result = 0; StringBuilder sb = new StringBuilder(outerhtml); result = outerhtml.IndexOf("<ol"); while (result > -1) { if (result == outerhtml.IndexOf("<ol>")) { sb.Remove(result, 4); sb.Insert(result, "1)"); } else { char number = outerhtml[result + 11]; sb.Remove(result, 14); sb.Insert(result, number + ")"); } outerhtml = sb.ToString(); result = outerhtml.IndexOf("<ol"); } //below is to remove linebreaks and whitespace for both answer and response cells to do matching var compareresponsecells = Regex.Replace(outerhtml, @"\r\n?|\n", ""); //to remove line breaks for comparison compareresponsecells = Regex.Replace(compareresponsecells, @"\s+", ""); //to remove whitespace for comparison var compareanswercells = Regex.Replace(answercells, @"\r\n?|\n", ""); compareanswercells = Regex.Replace(compareanswercells, @"\s+", ""); //Console.WriteLine(newcolumnheaders); if (columnheader == "Question") { } else if (columnheader == "Answer") { } else if (columnheader == "Answers") { } else if (newcolumnheaders == "Response") { //Console.WriteLine("YES " + count); try { NewWorkSheet.Cells[i, c] = outerhtml; Console.WriteLine("SICK FEELING :" + outerhtml); var wcmessagecontented = driver.FindElements(By.XPath("//div[@class='wc-message-content']")); var lastwcmsgcontent = wcmessagecontented.Last(); var child = lastwcmsgcontent.FindElement(By.XPath("./div/div")); // ./ means go down from this element //Console.WriteLine("LAST CHILD :" + child.GetAttribute("outerHTML")); if (child.GetAttribute("class").Contains("wc-list")) { NewWorkSheet.Cells[i, c] = outerhtml2; //Console.WriteLine("Panini"); } //NewWorkSheet.Cells[i, c] = outerhtml2; } catch { } //outerhtml.Contains((char)13); //Console.WriteLine(outerhtml.Contains((char)13)); //Console.WriteLine("WELP:" + outerhtml); } else if (newcolumnheaders == "Timing of response retrieval") { try { NewWorkSheet.Cells[i, c] = DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss"); } catch { } } else if (newcolumnheaders == "Does the answer and response match?") { if (compareanswercells.Equals(compareresponsecells)) { try { NewWorkSheet.Cells[i, c] = "Yes"; } catch { } } else { try { NewWorkSheet.Cells[i, c] = "No"; } catch { } } } else if (columnheader == null) { } else { try { NewWorkSheet.Columns[c].Delete(); c--; } catch { } } } // } if (NewWorkSheet.Cells[i, 5].Text == "Yes") { yescount += 1; //Console.WriteLine("For yes:" + NewWorkSheet.Cells[1][i].Text); //Console.WriteLine("Yes: " + yescount); } else { } //Console.WriteLine("TOTALMATCHCOUNT:" + count); //x1workbook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); } excel._Worksheet NewWorkSheet1 = NewWorkBook.Sheets[1]; excel.Range NewWorkRange1 = NewWorkSheet1.UsedRange; //int colCount3 = NewWorkRange.Columns.Count; //int rowCount3 = NewWorkRange.Rows.Count; NewWorkSheet1.Cells[1][1] = "Total Count of Matches: " + count; NewWorkSheet1.Cells[1][2] = "Total Count of Matches Matched: " + yescount; } }
private void generateLabelSheet(_Workbook workbook) { Microsoft.Office.Interop.Excel._Worksheet labelSheet = null; Microsoft.Office.Interop.Excel._Worksheet calcSheet = null; System.Data.DataTable labelDt = getLabelDataFromView(); if (labelDt.Rows.Count > 0) { labelDt.Columns.Add("OTH_REF"); labelDt.Columns.Add("OLD_ITEM"); labelDt.Columns.Add("BOXCODE"); labelDt.Columns.Add("MOGNBR1"); labelDt.Columns.Add("ENGDES"); labelDt.Columns.Add("SPNDES"); labelDt.Columns.Add("GERDES"); labelDt.Columns.Add("LBLCODE"); labelDt.Columns.Add("LBLTYPE"); labelDt.Columns.Add("LBLSTYE"); labelDt.Columns.Add("OENBR1"); labelDt.Columns.Add("OENBR2"); labelDt.Columns.Add("OENBR3"); labelDt.Columns.Add("OENBR4"); labelDt.Columns.Add("OENBR5"); labelDt.Columns.Add("OENBR6"); labelDt.Columns.Add("OENBR7"); labelDt.Columns.Add("OENBR8"); labelDt.Columns.Add("OENBR9"); labelDt.Columns.Add("OENBR10"); labelDt.Columns.Add("MOGNBR2"); labelDt.Columns.Add("TPFNBR1"); labelDt.Columns.Add("TPFNBR2"); labelDt.Columns.Add("TRWNBR1"); labelDt.Columns.Add("TRWNBR2"); labelDt.Columns.Add("DANNBR1"); labelDt.Columns.Add("DANNBR2"); labelDt.Columns.Add("MCQNBR1"); labelDt.Columns.Add("MCQNBR2"); labelDt.Columns.Add("DESC1"); labelDt.Columns.Add("DESC2"); labelDt.Columns.Add("DESC3"); labelDt.Columns.Add("DESC4"); labelDt.Columns.Add("DESC5"); labelDt.Columns.Add("DESC6"); labelDt.Columns.Add("DESC7"); labelDt.Columns.Add("DESC8"); labelDt.Columns.Add("DESC9"); labelDt.Columns.Add("DESC10"); labelDt.Columns.Add("QRCODE"); labelDt.Columns.Add("HBSQRCODE"); var rfcGetPackingFM = rfcRepo.CreateFunction(getLabelData); for (int i = 0; i <= labelDt.Rows.Count - 1; i++) { var nbr = labelDt.Rows[i]["NBR"]; var posnr = labelDt.Rows[i]["POSNR"].ToString(); rfcGetPackingFM.SetValue("P_VBELN", nbr); //訂單號碼 rfcGetPackingFM.SetValue("P_POSNR", posnr); //訂單項次 rfcGetPackingFM.Invoke(rfcDest); IRfcTable ITAB = rfcGetPackingFM.GetTable("ITAB"); if (ITAB.CurrentIndex == 0) { //只取第一列 ITAB.CurrentIndex = 0; labelDt.Rows[i]["OTH_REF"] = ITAB.GetString("KDMAT"); labelDt.Rows[i]["OLD_ITEM"] = ITAB.GetString("BISMT"); labelDt.Rows[i]["OENBR1"] = ITAB.GetString("OENBR1"); labelDt.Rows[i]["OENBR2"] = ITAB.GetString("OENBR2"); labelDt.Rows[i]["OENBR3"] = ITAB.GetString("OENBR3"); labelDt.Rows[i]["OENBR4"] = ITAB.GetString("OENBR4"); labelDt.Rows[i]["OENBR5"] = ITAB.GetString("OENBR5"); labelDt.Rows[i]["OENBR6"] = ITAB.GetString("OENBR6"); labelDt.Rows[i]["OENBR7"] = ITAB.GetString("OENBR7"); labelDt.Rows[i]["OENBR8"] = ITAB.GetString("OENBR8"); labelDt.Rows[i]["OENBR9"] = ITAB.GetString("OENBR9"); labelDt.Rows[i]["OENBR10"] = ITAB.GetString("OENBR10"); labelDt.Rows[i]["MOGNBR1"] = ITAB.GetString("MOGNBR1"); labelDt.Rows[i]["MOGNBR2"] = ITAB.GetString("MOGNBR2"); labelDt.Rows[i]["TPFNBR1"] = ITAB.GetString("TPFNBR1"); labelDt.Rows[i]["TPFNBR2"] = ITAB.GetString("TPFNBR2"); labelDt.Rows[i]["TRWNBR1"] = ITAB.GetString("TRWNBR1"); labelDt.Rows[i]["TRWNBR2"] = ITAB.GetString("TRWNBR2"); labelDt.Rows[i]["DANNBR1"] = ITAB.GetString("DANNBR1"); labelDt.Rows[i]["DANNBR2"] = ITAB.GetString("DANNBR2"); labelDt.Rows[i]["MCQNBR1"] = ITAB.GetString("MCQNBR1"); labelDt.Rows[i]["MCQNBR2"] = ITAB.GetString("MCQNBR2"); labelDt.Rows[i]["ENGDES"] = ITAB.GetString("ENGDES"); labelDt.Rows[i]["SPNDES"] = ITAB.GetString("SPNDES"); labelDt.Rows[i]["GERDES"] = ITAB.GetString("GERDES"); labelDt.Rows[i]["DESC1"] = ITAB.GetString("DESC1"); labelDt.Rows[i]["DESC2"] = ITAB.GetString("DESC2"); labelDt.Rows[i]["DESC3"] = ITAB.GetString("DESC3"); labelDt.Rows[i]["DESC4"] = ITAB.GetString("DESC4"); labelDt.Rows[i]["DESC5"] = ITAB.GetString("DESC5"); labelDt.Rows[i]["DESC6"] = ITAB.GetString("DESC6"); labelDt.Rows[i]["DESC7"] = ITAB.GetString("DESC7"); labelDt.Rows[i]["DESC8"] = ITAB.GetString("DESC8"); labelDt.Rows[i]["DESC9"] = ITAB.GetString("DESC9"); labelDt.Rows[i]["DESC10"] = ITAB.GetString("DESC10"); labelDt.Rows[i]["BOXCODE"] = ITAB.GetString("BOXCODE"); labelDt.Rows[i]["LBLCODE"] = ITAB.GetString("LBLCODE"); labelDt.Rows[i]["LBLTYPE"] = ITAB.GetString("LBLTYPE"); labelDt.Rows[i]["LBLSTYE"] = ITAB.GetString("LBLSTYE"); if ((ITAB.GetString("QRCODE") == null) || (ITAB.GetString("QRCODE") == "") || (ITAB.GetString("QRCODE") == " ")) { labelDt.Rows[i]["QRCODE"] = ""; labelDt.Rows[i]["HBSQRCODE"] = ""; } else { labelDt.Rows[i]["QRCODE"] = ITAB.GetString("QRCODE"); labelDt.Rows[i]["HBSQRCODE"] = labelDt.Rows[i]["QRCODE"].ToString() + labelDt.Rows[i]["CUS_ITEM"].ToString(); } } } BindingSource bs = new BindingSource(); bs.DataSource = labelDt.DefaultView; dgvPacking.DataSource = null; dgvPacking.DataSource = bs; foreach (DataGridViewColumn column in dgvPacking.Columns) { //表頭選擇 column.HeaderCell = new DataGridViewAutoFilterColumnHeaderCell(column.HeaderCell); //禁止排序 column.SortMode = DataGridViewColumnSortMode.NotSortable; } labelSheet = workbook.Sheets["工作表2"]; labelSheet.Name = "標籤明細"; labelSheet.Select(); //計算 dataGrid 的欄列數 string maxCols = GetStandardExcelColumnName(dgvPacking.Columns.Count); string maxRows = dgvPacking.Rows.Count.ToString(); //將 datagrid 填入 excel 中 //填上標題列 for (int i = 1; i < dgvPacking.Columns.Count + 1; i++) { labelSheet.Cells[1, i] = dgvPacking.Columns[i - 1].HeaderText; } //宣告 datagrid 沒有標題列 dgvPacking.RowHeadersVisible = false; //將資料從 datagrid 貼到 object dgvPacking.SelectAll(); DataObject dataObj = dgvPacking.GetClipboardContent(); if (dataObj != null) { Clipboard.SetDataObject(dataObj); } //將 object 傳給 workbook labelSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1); //將資料貼入第二列 (第一列是標題列) labelSheet = workbook.ActiveSheet; //資料格式化 labelSheet.get_Range("A1", "BZ" + maxRows).NumberFormat = "@"; labelSheet.Application.Goto(labelSheet.Range["A2"], true); //從剪貼薄貼上 labelSheet.PasteSpecial(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); //將 worksheet 複製一份到 calcsheet labelSheet.Copy(Type.Missing, workbook.Sheets[workbook.Sheets.Count]); // 複製的 sheet 更名 workbook.Sheets[workbook.Sheets.Count].Name = "計算張數"; //選取 calcSheet calcSheet = workbook.Sheets[workbook.Sheets.Count]; //刪掉不要的部份 calcSheet.Range["A1:I" + maxRows].Delete(); calcSheet.Range["B1:" + maxCols + maxRows].Delete(); //去除重覆料號 calcSheet.Range["A1:A" + maxRows].RemoveDuplicates(1); calcSheet.UsedRange.NumberFormat = "General"; calcSheet.Range["A1"].Value = "料號"; calcSheet.Range["B1"].Value = "張數"; //求有值的最大列數 int calcsheetMaxRows = calcSheet.Range["A1"].Offset[calcSheet.Rows.Count - 1, 0].End[Microsoft.Office.Interop.Excel.XlDirection.xlUp].Row; calcSheet.Range["B2"].Value = "=COUNTIF(標籤明細!J:J,A2)"; //將公式往下複製 calcSheet.Range["B2"].Copy(calcSheet.Range["B3:B" + calcsheetMaxRows]); //刪掉多的工作表 //workbook.Sheets["工作表2"].Delete(); } else { MessageBox.Show("標籤明細沒有資料", "錯誤"); } }
public static void CreateWorkbook(string dir, string txtDir, Dictionary <string, Data> inputFiles) { // creating Excel Application Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); try { Data MyData; string[][] results; // creating new WorkBook within Excel application // creating new Excelsheet in workbook Sheets xlSheets = null; xlSheets = workbook.Sheets as Sheets; // see the excel sheet behind the program app.Visible = false; for (int i = 0; i < inputFiles.Count; i++) { MyData = inputFiles.ElementAt(i).Value; var xlNewSheet = xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); xlNewSheet.Name = MyData.FileName.Replace("_Ch0_Lime_Results", ""); results = MyData.GetResults; for (int col = 0; col < results.Length; col++) { xlNewSheet.Cells[1, col + 1].Value2 = results[col][0]; } var startCell = (Range)xlNewSheet.Cells[2, 1]; var endCell = (Range)xlNewSheet.Cells[results[0].Length, results.Length]; var writeRange = xlNewSheet.Range[startCell, endCell]; writeRange.Value2 = TranformArray(results); } //add the summery if (File.Exists(txtDir)) { Microsoft.Office.Interop.Excel._Workbook csvWorkbook = app.Workbooks.Open(txtDir); Microsoft.Office.Interop.Excel._Worksheet worksheetCSV = ((Microsoft.Office.Interop.Excel._Worksheet)csvWorkbook.Worksheets[1]); worksheetCSV.Copy(xlSheets[1]); xlSheets[1].Name = "Summery"; // Exit from the application csvWorkbook.Close(false); } app.Visible = true; workbook.SaveAs(dir, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } catch (Exception e) { Console.WriteLine(e.Message); } app.Visible = true; workbook.Close(false); app.Quit(); }