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();
}
