private void btnCompare_Click(object sender, EventArgs e) { bool ifOnlyCompareDistNum = false; if (checkBox1.Checked == true) { ifOnlyCompareDistNum = true; } if (string.IsNullOrEmpty(comboBox1.Text) || string.IsNullOrEmpty(comboBox2.Text)) { MessageBox.Show("Pls select format", "CDRcompare"); return; } int timeRange = 0; if (CanCovert(textBox3.Text, typeof(System.Int32))) { timeRange = Convert.ToInt32(textBox3.Text); } else { MessageBox.Show("Pls input correct time range", "CDRcompare"); return; } //存右边哈希值的Dictionary Dictionary <int, List <int> > dForSearchListRight = new Dictionary <int, List <int> >(); List <CDRItem> listLeft = new List <CDRItem>(); btnCompare.Enabled = false; label6.Text = "Loading..."; ultraActivityIndicator1.Start(true); try { if (comboBox1.Text == "WHC format") { listLeft = FormatLoad.importExcelUsingNetvantageWHC(textBox1.Text, Convert.ToInt32(textBox4.Text), Convert.ToInt32(textBox5.Text), Convert.ToInt32(textBox6.Text), Convert.ToInt32(textBox7.Text)); } if (comboBox1.Text == "ashan format") { listLeft = FormatLoad.importExcelUsingNetvantageAshan(textBox1.Text, Convert.ToInt32(textBox4.Text), Convert.ToInt32(textBox5.Text), Convert.ToInt32(textBox6.Text), Convert.ToInt32(textBox7.Text)); } if (comboBox1.Text == "Umobile format") { listLeft = FormatLoad.importExcelUsingNetvantageUmobile(textBox1.Text, Convert.ToInt32(textBox4.Text), Convert.ToInt32(textBox5.Text), Convert.ToInt32(textBox6.Text), Convert.ToInt32(textBox7.Text)); } } catch (Exception loade) { MessageBox.Show(loade.Message); } if (listLeft == null) { MessageBox.Show("error format"); btnCompare.Enabled = true; return; } List <CDRItem> listRight = new List <CDRItem>(); try { if (comboBox2.Text == "WHC format") { listRight = FormatLoad.importExcelUsingNetvantageWHC(textBox2.Text, Convert.ToInt32(textBox8.Text), Convert.ToInt32(textBox9.Text), Convert.ToInt32(textBox10.Text), Convert.ToInt32(textBox11.Text)); } if (comboBox2.Text == "ashan format") { listRight = FormatLoad.importExcelUsingNetvantageAshan(textBox2.Text, Convert.ToInt32(textBox8.Text), Convert.ToInt32(textBox9.Text), Convert.ToInt32(textBox10.Text), Convert.ToInt32(textBox11.Text)); } if (comboBox2.Text == "Umobile format") { listRight = FormatLoad.importExcelUsingNetvantageUmobile(textBox2.Text, Convert.ToInt32(textBox8.Text), Convert.ToInt32(textBox9.Text), Convert.ToInt32(textBox10.Text), Convert.ToInt32(textBox11.Text)); } } catch (Exception loade) { MessageBox.Show(loade.Message); } if (listRight == null) { btnCompare.Enabled = true; MessageBox.Show("error format"); return; } //写 dForSearchListRight for (int ii = 0; ii < listRight.Count; ii++) { CDRItem ci = listRight[ii]; string sRowText = ""; int iHash = 0; if (ifOnlyCompareDistNum == false) { sRowText = sRowText + ci.ani; sRowText = sRowText + ci.dest; } else { sRowText = ci.dest; } //MessageBox.Show(sRowText); iHash = sRowText.GetHashCode(); if (dForSearchListRight.ContainsKey(iHash)) { dForSearchListRight[iHash].Add(ii); } else { dForSearchListRight.Add(iHash, new List <int>()); dForSearchListRight[iHash].Add(ii); } } //-------------------------- Application.DoEvents(); label6.Text = "Comparing..."; //ultraActivityIndicator1.Start(true); //hash Dictionary already ok ,now start search matching record, //first search record in left but not in right int iDeleteCount = 0; List <int> listToBeDeleteLeft = new List <int>(); string filePath = "compare_result.csv"; for (int ii = 0; ii < listLeft.Count; ii++) { string strExportSame = ""; string sRowText = ""; int iHash = 0; CDRItem ci = listLeft[ii]; strExportSame = ci.ani.ToString() + "," + ci.dest.ToString() + "," + ci.start.ToString() + "," + ci.duration.ToString() + ","; if (ifOnlyCompareDistNum == false) { sRowText = sRowText + ci.ani; sRowText = sRowText + ci.dest; } else { sRowText = ci.dest; } //MessageBox.Show(sRowText); iHash = sRowText.GetHashCode(); if (dForSearchListRight.ContainsKey(iHash)) { double minDurationDiff = 0.0; double minDiffDuration = 0.0; string minDiffAnum = null; string minDiffBnum = null; DateTime minDiffConnecttime = DateTime.MinValue; bool exactMatch = false; bool mohuMatch = false; int recordElement = 0; foreach (int element in dForSearchListRight[iHash]) //loop A { //是否通过时间和duration检查 //if ((compareDateTime(ci.start, listRight[element].start, timeRange) == true)) if ((compareDateTime(ci.start.AddHours(Convert.ToDouble(textBox12.Text)), listRight[element].start.AddHours(Convert.ToDouble(textBox13.Text)), timeRange) == true)) { if (System.Math.Abs(ci.duration - listRight[element].duration) < 10.0) { strExportSame = strExportSame + listRight[element].ani.ToString() + "," + listRight[element].dest.ToString() + "," + listRight[element].start.ToString() + "," + listRight[element].duration.ToString(); //File.AppendAllText(filePath, strExportSame); //File.AppendAllText(filePath, strExportSame); listToBeDeleteLeft.Add(element); //在dictionary的list删除此条找到的纪录 dForSearchListRight[iHash].Remove(element); iDeleteCount++; exactMatch = true; break; //break 是跳出loop A } //if ((System.Math.Abs(ci.duration - listRight[element].duration) > 10.0) && (System.Math.Abs(ci.duration - listRight[element].duration) < 20)) //时间差距大于10 //{ // strExportSame = strExportSame + listRight[element].ani.ToString() + "," + listRight[element].dest.ToString() + "," + listRight[element].start.ToString() + "," + listRight[element].duration.ToString() + "," + "same a b num but duration diff GT 10"; // //File.AppendAllText(filePath, strExportSame); // //File.AppendAllText(filePath, strExportSame); // listToBeDeleteLeft.Add(element); // //在dictionary的list删除此条找到的纪录 // dForSearchListRight[iHash].Remove(element); // //iDeleteCount++; 这里不能加 ,因为不是完全相等的记录 // break; //} if ((System.Math.Abs(ci.duration - listRight[element].duration) > 10.0) && (System.Math.Abs(ci.duration - listRight[element].duration) < 30.0)) { if (minDurationDiff == 0.0) { minDurationDiff = System.Math.Abs(ci.duration - listRight[element].duration); minDiffAnum = listRight[element].ani; minDiffBnum = listRight[element].dest; minDiffConnecttime = listRight[element].start; minDiffDuration = listRight[element].duration; recordElement = element; mohuMatch = true; continue; } if (System.Math.Abs(ci.duration - listRight[element].duration) < minDurationDiff) { minDurationDiff = System.Math.Abs(ci.duration - listRight[element].duration); minDiffAnum = listRight[element].ani; minDiffBnum = listRight[element].dest; minDiffConnecttime = listRight[element].start; minDiffDuration = listRight[element].duration; recordElement = element; mohuMatch = true; continue; } } } } //loop A if ((!exactMatch) && (mohuMatch)) { strExportSame = strExportSame + minDiffAnum + "," + minDiffBnum + "," + minDiffConnecttime.ToString() + "," + minDiffDuration.ToString() + "," + "same a b num but duration diff GT 10"; listToBeDeleteLeft.Add(recordElement); dForSearchListRight[iHash].Remove(recordElement); } strExportSame = strExportSame + "\r\n"; File.AppendAllText(filePath, strExportSame); } else { strExportSame = strExportSame + "\r\n"; File.AppendAllText(filePath, strExportSame); } } //left--->right search ok if (MessageBox.Show("Search finished \r\nfrom left to right find " + iDeleteCount.ToString() + " same records\r\nDo you want to export right_only record? ", "CDRcompare", MessageBoxButtons.OKCancel) == DialogResult.OK) { //delete right record listToBeDeleteLeft.Sort(); listToBeDeleteLeft.Reverse(); for (int ii = 0; ii < listToBeDeleteLeft.Count; ii++) { listRight.RemoveAt(listToBeDeleteLeft[ii]); } for (int ii = 0; ii < listRight.Count; ii++) { string strExportRemain = ""; strExportRemain = ",,,," + listRight[ii].ani.ToString() + "," + listRight[ii].dest.ToString() + "," + listRight[ii].start.ToString() + "," + listRight[ii].duration.ToString() + "\r\n"; File.AppendAllText(filePath, strExportRemain); } MessageBox.Show("Export finished"); Application.DoEvents(); btnCompare.Enabled = true; label6.Text = "Finished"; ultraActivityIndicator1.Stop(true); } btnCompare.Enabled = true; label6.Text = "Finished"; ultraActivityIndicator1.Stop(true); }
// public static List <CDRItem> importExcelUsingNetvantageAshan(string strFilePath, int iSrc, int iDist, int iConTime, int iDuration) { //要进行的处理 //1:去掉distNum前缀的70743 ok //2:srcNum如果等于asterisk,改为空 ok //3:srcNum如果等于数字,去掉前面多余的0,例如0080 改为80 ok //4:支持多个worksheet ok List <CDRItem> list = new List <CDRItem>(); //----------to use this function ,remember to add reference :using Infragistics.Documents.Excel; if (File.Exists(strFilePath)) { //Load the Excel File into the Workbook Object //Application.DoEvents(); Workbook theWorkbook = Workbook.Load(strFilePath, false); for (int jj = 0; jj < theWorkbook.Worksheets.Count; jj++) { Worksheet theWorksheet = theWorkbook.Worksheets[jj]; int theRowCounter = 0; //Iterate through all Worksheet rows Iterate through foreach (WorksheetRow theWorksheetRow in theWorksheet.Rows) { if (theRowCounter == 0) { //check file format //string strHeadArray = ""; //foreach (WorksheetCell theWorksheetCell in theWorksheetRow.Cells) //{ // strHeadArray = strHeadArray + theWorksheetCell.Value.ToString().Trim() + ","; //} //strHeadArray = strHeadArray.Substring(0, strHeadArray.Length - 1); ////if (strHeadArray != "start time,duration,CalledIP,Callout callerID,Callout calledNum") //if (!strHeadArray.Equals("start time,duration,CalledIP,Callout callerID,Callout calledNum", StringComparison.OrdinalIgnoreCase)) //{ // return null; //} theRowCounter++; continue; } else //This is the actual data that will populate the data model { CDRItem ci = new CDRItem(); try { ci.ani = (theWorksheetRow.Cells[iSrc].Value != null) ? theWorksheetRow.Cells[iSrc].Value.ToString() : ""; if (ci.ani == "asterisk") { ci.ani = ""; } Int64 l1; Int64.TryParse(ci.ani, out l1); if (l1 != 0) { ci.ani = l1.ToString(); } ci.dest = (theWorksheetRow.Cells[iDist].Value != null) ? theWorksheetRow.Cells[iDist].Value.ToString() : ""; if (ci.dest.Length > 5) { ci.dest = ci.dest.Substring(5); } if (theWorksheetRow.Cells[iConTime].Value.GetType() == typeof(double)) { ci.start = DateTime.FromOADate((double)theWorksheetRow.Cells[iConTime].Value); } else { ci.start = DateTime.Parse(theWorksheetRow.Cells[iConTime].Value.ToString()); } ci.duration = Convert.ToDouble(theWorksheetRow.Cells[iDuration].Value); } catch (System.OverflowException tep) { MessageBox.Show("stack over flow" + tep.Message); } list.Add(ci); } theRowCounter++; } //loop worksheet's row } //loop worksheet } else { MessageBox.Show("No such file was found!"); } return(list); }
// public static List <CDRItem> importExcelUsingNetvantageUmobile(string strFilePath, int iSrc, int iDist, int iConTime, int iDuration) { List <CDRItem> list = new List <CDRItem>(); //----------to use this function ,remember to add reference :using Infragistics.Documents.Excel; if (File.Exists(strFilePath)) { //Load the Excel File into the Workbook Object //Application.DoEvents(); Workbook theWorkbook = Workbook.Load(strFilePath, false); //We will only work with the first Worksheet in the Workbook -- 只支持第一个WorkSheet Worksheet theWorksheet = theWorkbook.Worksheets[0]; int theRowCounter = 0; //Iterate through all Worksheet rows Iterate through foreach (WorksheetRow theWorksheetRow in theWorksheet.Rows) { if (theRowCounter == 0) { //check file format //string strHeadArray = ""; //foreach (WorksheetCell theWorksheetCell in theWorksheetRow.Cells) //{ // strHeadArray = strHeadArray + theWorksheetCell.Value.ToString().Trim() + ","; //} //strHeadArray = strHeadArray.Substring(0, strHeadArray.Length - 1); ////event_start_date event_start_time anum bnum event_duration billing_operator //if (strHeadArray != "event_start_date,event_start_time,anum,bnum,event_duration,billing_operator") //{ // return null; //} theRowCounter++; continue; } else //This is the actual data that will populate the data model { CDRItem ci = new CDRItem(); try { ci.ani = (theWorksheetRow.Cells[iSrc].Value != null) ? theWorksheetRow.Cells[iSrc].Value.ToString() : ""; ci.dest = (theWorksheetRow.Cells[iDist].Value != null) ? theWorksheetRow.Cells[iDist].Value.ToString() : ""; //ci.dest = "60" + ci.dest; if (ci.dest.Length > 3) { ci.dest = ci.dest.Substring(3); } //取日期 if (theWorksheetRow.Cells[iConTime].Value.GetType() == typeof(double)) { ci.start = DateTime.FromOADate((double)theWorksheetRow.Cells[iConTime].Value); } else { //ci.start = DateTime.ParseExact(theWorksheetRow.Cells[iConTime].Value.ToString(), "dd/MM/yy", new CultureInfo("en-US")); //ci.start = DateTime.ParseExact(theWorksheetRow.Cells[iConTime].Value.ToString(), "dd/MM/yy", new CultureInfo("en-US")); ci.start = DateTime.Parse(theWorksheetRow.Cells[iConTime].Value.ToString()); //年和日互换 int iyear = ci.start.Year; int imonth = ci.start.Month; int iday = ci.start.Day; ci.start = new DateTime(iday + 2000, imonth, iyear - 2000); } //ci.start = DateTime.Parse(theWorksheetRow.Cells[iConTime].Value.ToString()); //取时间 DateTime d2 = Convert.ToDateTime(theWorksheetRow.Cells[iConTime + 1].Value); TimeSpan timeSpan = d2.TimeOfDay; ci.start = ci.start.Date.Add(timeSpan); //if (theWorksheetRow.Cells[iConTime].Value.GetType() == typeof(double)) // ci.start = ci.start.Date.Add(DateTime.FromOADate((double)theWorksheetRow.Cells[iConTime].Value).TimeOfDay); //else // ci.start = ci.start.Date.Add(DateTime.Parse(theWorksheetRow.Cells[iConTime].Value.ToString()).TimeOfDay); ci.duration = Convert.ToDouble(theWorksheetRow.Cells[iDuration].Value); } catch (Exception tep) { MessageBox.Show(tep.Message + theRowCounter.ToString() + theWorksheetRow.Cells[0].Value.ToString() + theWorksheetRow.Cells[1].Value.ToString() + theWorksheetRow.Cells[2].Value.ToString() + theWorksheetRow.Cells[3].Value.ToString()); } list.Add(ci); } theRowCounter++; } } else { MessageBox.Show("No such file was found!"); } return(list); }
public static List <CDRItem> importExcelUsingNetvantageWHC(string strFilePath, int iSrc, int iDist, int iConTime, int iDuration) { List <CDRItem> list = new List <CDRItem>(); bool bOADateFormat = false; bool bDatetimeFormat = false; bool bXueliangFormat = false; //----------to use this function ,remember to add reference :using Infragistics.Documents.Excel; if (File.Exists(strFilePath)) { //Load the Excel File into the Workbook Object //Application.DoEvents(); Workbook theWorkbook = Workbook.Load(strFilePath, false); //We will only work with the first Worksheet in the Workbook -- 只支持第一个WorkSheet Worksheet theWorksheet = theWorkbook.Worksheets[0]; int theRowCounter = 0; //Iterate through all Worksheet rows Iterate through foreach (WorksheetRow theWorksheetRow in theWorksheet.Rows) { if (theRowCounter == 0) { //check file format /* * string strHeadArray=""; * foreach (WorksheetCell theWorksheetCell in theWorksheetRow.Cells) * { * strHeadArray = strHeadArray + theWorksheetCell.Value.ToString().Trim() + ","; * } * strHeadArray = strHeadArray.Substring(0, strHeadArray.Length - 1); * //ID CDRID SRCNum DSTNum TGIN IPIn TGOUT IPOut CDRDate SetupTime ConnectTime DisconnectTime Duration CauseValue Country City Remarks * if (strHeadArray != "ID,CDRID,SRCNum,DSTNum,TGIN,IPIn,TGOUT,IPOut,CDRDate,SetupTime,ConnectTime,DisconnectTime,Duration,CauseValue,Country,City,Remarks") * { * return null; * } * */ theRowCounter++; continue; } else //This is the actual data that will populate the data model { CDRItem ci = new CDRItem(); try { ci.ani = (theWorksheetRow.Cells[iSrc].Value != null) ? theWorksheetRow.Cells[iSrc].Value.ToString() : ""; ci.dest = (theWorksheetRow.Cells[iDist].Value != null) ? theWorksheetRow.Cells[iDist].Value.ToString() : ""; if ((bOADateFormat == false) && (bDatetimeFormat == false) && (bXueliangFormat == false)) { try { ci.start = DateTime.FromOADate(Convert.ToDouble(theWorksheetRow.Cells[iConTime].Value)); //ci.start = DateTime.Parse(theWorksheetRow.Cells[iConTime].Value.ToString()); bOADateFormat = true; } catch (Exception dep) { ci.start = DateTime.Parse(theWorksheetRow.Cells[iConTime].Value.ToString()); //ci.start = DateTime.FromOADate(Convert.ToDouble(theWorksheetRow.Cells[iConTime].Value)); bDatetimeFormat = true; } } else { if ((bOADateFormat == true) && (theWorksheetRow.Cells[iConTime].Value != null)) { ci.start = DateTime.FromOADate(Convert.ToDouble(theWorksheetRow.Cells[iConTime].Value)); } if ((bDatetimeFormat == true) && (theWorksheetRow.Cells[iConTime].Value != null)) { ci.start = DateTime.Parse(theWorksheetRow.Cells[iConTime].Value.ToString()); } if ((bXueliangFormat == true) && (theWorksheetRow.Cells[iConTime].Value != null)) { ci.start = DateTime.ParseExact(theWorksheetRow.Cells[iConTime].Value.ToString(), "yyyyMMddHHmmss", null); } } ci.duration = (theWorksheetRow.Cells[iDuration].Value != null) ? Convert.ToDouble(theWorksheetRow.Cells[iDuration].Value) : 0.0; //ci.duration = Convert.ToDouble(theWorksheetRow.Cells[iDuration].Value); } catch (Exception tep) { string sXueliangDatetime = ""; sXueliangDatetime = theWorksheetRow.Cells[iConTime].Value.ToString(); string formatString = "yyyyMMddHHmmss"; try { DateTime dt = DateTime.ParseExact(sXueliangDatetime, formatString, null); ci.start = dt; bXueliangFormat = true; } catch (Exception tmep) { } //MessageBox.Show(tep.Message + theRowCounter.ToString() + theWorksheetRow.Cells[0].Value.ToString() + theWorksheetRow.Cells[1].Value.ToString() + theWorksheetRow.Cells[2].Value.ToString() + theWorksheetRow.Cells[3].Value.ToString()); } if (ci.dest != "") { list.Add(ci); } } theRowCounter++; } } else { MessageBox.Show("No such file was found!"); } return(list); }