WPF program and parallel data comparison

Recently, a WPF applet has been created. The interface is as follows:

Let's take a look at some comments.

The main business logic is: Many contract fulfillment conditions are listed in an Excel file. You need to use this data to compare it with the original contract to find the unfinished project.

The interface is as follows:

....

Let's talk about how it works.

Winform programs are always running, and WPF is rarely used. Therefore, it is very difficult to use them for the first time. Many templates and styles are unavailable. This is the key to accumulation.

In addition, the parallel computing of framework4.0 is used when processing data. Now the code is pasted below:

Public class dataoperate {public static datatable dtalldata = NULL; public static dictionary <string, datatable> hetonginfolist = new dictionary <string, datatable> (); public static dictionary <string, datatable> anainfolist = new dictionary <string, datatable> (); public static list <string> columnamelist = new list <string> (); public static void cooperatedata (string hetongname) {datatable dthetong = H Etonginfolist [hetongname]; If (dthetong! = NULL) {datatable resultdt = dthetong. Clone (); // compare all... VaR Sw = stopwatch. startnew (); parallel. foreach (dthetong. asenumerable (), row => {If (columnamelist. count = 0) {// check whether this record exists in the Compare DT. If not, save it in the result stringbuilder strfilter = new stringbuilder (); For (INT I = 0; I <dthetong. columns. count; I ++) {If (! Dtalldata. columns. contains (dthetong. columns [I]. columnname) continue; strfilter. append (dthetong. columns [I]. columnname + "= '" + row [I]. tostring () + "'and");} string filter = strfilter. tostring (); If (filter. endswith ("'and") filter = filter. substring (0, filter. length-3); datarow [] rows = dtalldata. select (filter); If (rows. length = 0) {datarow newrow = resultdt. newrow (); newrow. itemarray = row. Itemarray; resultdt. rows. add (newrow) ;}} else // compare key columns {stringbuilder strfilter = new stringbuilder (); For (INT I = 0; I <columnamelist. count; I ++) {If (! Dtalldata. columns. contains (columnamelist [I]) continue; strfilter. append (columnamelist [I] + "= '" + row [columnamelist [I]. tostring () + "'and");} string filter = strfilter. tostring (); If (filter. endswith ("'and") filter = filter. substring (0, filter. length-3); datarow [] rows = dtalldata. select (filter); If (rows. length = 0) {datarow newrow = resultdt. newrow (); newrow. itemarray = row. itemarray; resultdt. rows. add (newrow) ;}}); // dataview DV = resultdt. defaultview; DV. sort = resultdt. columns [0]. columnname; datatable sorttable = DV. totable (); If (anainfolist. keys. contains (hetongname) anainfolist [hetongname] = sorttable; else anainfolist. add (hetongname, sorttable); // SW. stop () ;}/// <summary> /// replace it if it already exists, delete the analytic data /// </Summary> /// <Param name = "hetongname"> </param> /// <Param name = "hetongdt"> </Param> Public static void addhetong (string hetongname, datatable hetongdt) {If (hetonginfolist. keys. contains (hetongname) {hetonginfolist [hetongname] = hetongdt; If (anainfolist. keys. contains (hetongname) anainfolist. remove (hetongname);} else hetonginfolist. add (hetongname, hetongdt);} internal static void cooperatealldata () {foreach (string htname in hetonginfolist. keys) {cooperatedata (htname );}}}

In addition, when importing an Excel file, if the column is a mix of numbers and characters, the last line is a string, followed by numbers, oledb generally considers this column as a numeric column rather than a string column. There is no good solution. I first thought of copying a file and then changing the format of all cells as a string, but it seems that it does not work in excel2003, or reading it as a number column. So we have to read all the data without reading the first row title. In this way, the first row is a string, so all columns are strings, and then the first row is removed and the column name is changed. This facilitates future data comparison.

Paste the read code first:

Public class excelhelper {// <summary> // read the Excel file to dataset /// </Summary> /// <Param name = "filepath"> file path </param> // <returns> </returns> Public static dataset filetodatatable (string filepath) {// string fpath2 = system. environment. getfolderpath (environment. specialfolder. mydocuments); // fpath2 + = "//" + system. io. path. getfilename (filepath); // file. copy (filepath, fpath2, true); // changecellformat (Fpath2); // change all columns to string type // string connstr = ""; string filetype = system. io. path. getextension (filepath); If (string. isnullorempty (filetype) return NULL; If (filetype = ". xls ") connstr =" provider = Microsoft. jet. oledb.4.0; "+" Data Source = "+ filepath +"; "+"; extended properties = \ "Excel 8.0; HDR = no; IMEX = 1 \""; else connstr = "provider = Microsoft. ace. oledb.12.0; "+" Data Source = "+ filepath +"; "+ "; Extended properties = \ "Excel 12.0; HDR = no; IMEX = 1 \" "; string SQL _f =" select * from [{0}] "; oledbconnection conn = NULL; oledbdataadapter da = NULL; system. data. datatable dtsheetname = NULL; dataset DS = new dataset (); try {// initialize the connection and open conn = new oledbconnection (connstr); Conn. open (); // obtain the table definition metadata of the data source string sheetname = ""; dtsheetname = Conn. getoledbschematable (oledbschemaguid. tables, new object [] {Null, "table"}); // initialize the adapter da = new oledbdataadapter (); For (INT I = 0; I <dtsheetname. rows. count; I ++) {sheetname = (string) dtsheetname. rows [I] ["table_name"]; If (sheetname. contains ("$ ")&&! Sheetname. replace ("'",""). endswith ("$") {continue;} da. selectcommand = new oledbcommand (string. format (SQL _f, sheetname), Conn); dataset dsitem = new dataset (); DA. fill (dsitem, sheetname); For (Int J = 0; j <dsitem. tables [0]. columns. count; j ++) dsitem. tables [0]. columns [J]. columnname = dsitem. tables [0]. rows [0] [J]. tostring (); dsitem. tables [0]. rows. removeat (0); // foreach (datacolumn Col in dsitem. tables [0]. columns) // {// Col. datatype = typeof (string); //} Ds. tables. add (dsitem. tables [0]. copy () ;}} catch (exception ex) {// file. delete (fpath2);} finally {// close the connection if (Conn. state = connectionstate. open) {Conn. close (); DA. dispose (); Conn. dispose (); // file. delete (fpath2) ;}/// file. delete (fpath2); Return DS ;}}

This basically solves the problem.

Please indicate the source of the Post. Thank you.