Import an Excel report to listctrl and insert SQL statements (customer information import tool)

Project, made such a small tool, interested to see!

 

1. Excel ---> listctrl ---> SQL 2000

2. traverse the row specified as "failed" in listctrl of the parent window, insert it to the new listctrl in the subwindow, and generate an Excel report with an import error.

 

(In this way, users can modify the wrong data and import the data multiple times)

 

After reading the code, you will think the method is indeed a little "Tricky "......

 

When re-painting the listctrl coloring error column, I checked the information for a long time and hoped that friends would not take a "detour" and the code was rough, I hope that you will not be able to see it again "......

 

Related code:

 

1. Specify row coloring

/*************************************** *******************************/<Br/> /* function Description: redraw the listctrl control -- display failed records in red <br/>/* parameter number: parameter msdn <br/>/* return value: none <br/>/* standby note: none <br/> /********************************** ***********************************/< br/> void cimportdlg:: oncustomdrawmylist (nmhdr * pnmhdr, lresult * presult) <br/>{< br/> nmlvcustomdraw * plvcd = reinterpret_cast <nmlvcustomdraw *> (pnmhdr ); </P> <p> // take the default processing unless we set this to something else below. <br/> * presult = 0; </P> <p> // first thing-check the draw stage. if it's the control's prepaint <br/> // stage, then tell windows we want messages for every item. <br/> If (cdds_prepaint = plvcd-> nmcd. dwdrawstage) <br/>{< br/> * presult = cdrf_policyitemdraw; <br/>}</P> <p> // This is the notification message for an item. we'll request <br/> // configurations before each subitem's prepaint stage. <br/> else if (plvcd-> nmcd. dwdrawstage = cdds_itemprepaint) <br/>{< br/> colorrefm_clrtext; <br/> intnitem = static_cast <int> (plvcd-> nmcd. dwitemspec); </P> <p> // determines the conditions that make listctrl different colors realistic based on the text content <br/> cstring STR = m_list1.getitemtext (nitem, 6 ); <br/> If (STR = "successful") <br/>{< br/> m_clrtext = RGB (234 ); <br/>}< br/> else if (STR = "") <br/>{< br/> m_clrtext = RGB (0, 0 ); <br/>}< br/> else <br/> {<br/> m_clrtext = RGB (255, 0, 0 ); <br/>}< br/> plvcd-> clrtext = m_clrtext; <br/> * presult = cdrf_dodefault; <br/>}< br/>}

 

2. Browse Excel-import Excel Data

/*************************************** *******************************/<Br/> /* function Description: browse an Excel file <br/>/* parameter: None <br/>/* return value: None <br/>/* Note: none <br/> /********************************** ***********************************/< br/> void cimportdlg:: onsyschangefile () <br/>{< br/> // todo: add your command handler code here <br/> m_list1.deleteallitems (); <br/> cstringstrfile; <br/> charszfilter [] = "Excel file (*. XLS) | *. xls "; </P> <p> cfiledialogdlg (1, null, szfilter); <br/> If (idok = DLG. domodal () <br/>{< br/> strfile = DLG. getpathname (); <br/>}</P> <p> cspreadsheet SS (strfile, "sheet1"); <br/> cstringarray rows, column; <br/> cstring STR = ""; <br/> int nitem = 0; <br/> char szyw [1, 100]; <br/> memset (szyw, 0, sizeof (szyw); <br/> copini: readstring ("History", "lastuser", szyw, "setinfo. ini "); <br/> Str. format ("% s", szyw); <br/> cstring str1, str2, str3, str4, str5; // insert a database <br/> for (INT I = 2; I <= ss. gettotalrows (); I ++) <br/>{< br/> SS. readrow (rows, I); // read a row <br/> str1 = rows. getat (0); <br/> str2 = rows. getat (1); <br/> str3 = rows. getat (2); <br/> str4 = rows. getat (3); <br/> str5 = rows. getat (4); </P> <p> str1.replace (". 0 "," "); // avoid recognizing as words <br/> str2.replace (". 0 "," "); // customer phone number <br/> str3.replace (". 0 "," "); // customer Fax <br/> str4.replace (". 0 "," "); <br/> str5.replace (". 0 "," "); <br/> If (str1.find (" individual ", 0)> = 1) // if the customer is "individual" <br/>{// if the recipient is not "Mr. or miss", replace the customer name <br/> If (str4.find ("Mr", 0) <= 0) & <br/> (str4.find ("miss", 0) <= 0) <br/>{< br/> str1 = str4; <br/>}< br/> nitem = m_list1.insertitem (0, STR); <br/> m_list1.setitem (nitem, 1, 1, str1, null, 0, 0, 0); <br/> m_list1.setitem (nitem, str2, null, 0); <br/> m_list1.setitem (nitem, str3, null, 0); <br/> m_list1.setitem (nitem, str4, null, 0); <br/> m_list1.setitem (nitem, str5, null, 0); <br/> invalidate (true); <br/>}< br/>}

 

3. Traverse listctrl and insert SQL

/*************************************** *******************************/<Br/> /* function Description: traverse listctrl and write records to the database <br/>/* parameter number: None <br/>/* return value: None <br/>/* standby note: none <br/> /********************************** ***********************************/< br/> void cimportdlg:: onsysstart () <br/>{< br/> // todo: add your command handler code here <br/> If (m_list1.getitemcount () <= 0) <br/> {<br/> MessageBox ("no data needs to be imported! "," Prompt ", mb_ OK | mb_iconwarning); <br/> return; <br/>}< br/> If (idyes! = MessageBox ("are you sure you want to start importing? "," Prompt ", mb_yesno | mb_iconwarning) <br/>{< br/> return; <br/>}< br/> intnret = 0; <br/> intncount = m_list1.getitemcount ()-1; <br/> charszyewu [100], szkehu [100], szdian [100]; <br/> charszchua [100], szshou [100], szaddr [100]; <br/> cstring str1, str2, str3, str4, str5, str6; </P> <p> for (Int J = 0; j <= ncount; j ++) // start listctrl traversal <br/>{< br/> m_list1.getitemtext (J, 0, szyewu, 100); // obtain the clerk <br/> m_list1.getitemtext (J, 1, szkehu, 100); // obtain the customer name <br/> m_list1.getitemtext (J, 2, szdian, 100); // obtain the customer's phone number <br/> m_list1.getitemtext (J, 3, szchua, 100); // get the customer Fax <br/> m_list1.getitemtext (J, 4, szshou, 100); // get the recipient <br/> m_list1.getitemtext (J, 5, szaddr, 100); // obtain the customer address </P> <p> str1.format ("% s", szyewu); <br/> str2.format ("% s ", szkehu); <br/> str3.format ("% s", szdian); <br/> str4.format ("% s", szchua ); <br/> str5.format ("% s", szshou); <br/> str6.format ("% s", szaddr ); </P> <p> nret = insertkehu (str1, str2, str3, str4, str5, str6); <br/> If (nret = 2) <br/>{< br/> m_list1.setitemtext (J, 6, "succeeded"); <br/> m_list1.setitemtext (J, 7 ,""); <br/>}< br/> else if (nret = 0) <br/>{< br/> m_list1.setitemtext (J, 6, "failed "); <br/> m_list1.setitemtext (J, 7, "customer name is blank"); <br/> nerrorcount ++; <br/>}< br/> else if (nret = 1) <br/>{< br/> m_list1.setitemtext (J, 6, "failed "); <br/> m_list1.setitemtext (J, 7, "the customer name cannot be Mr., miss, or individual"); <br/> nerrorcount ++; <br/>}< br/> else if (nret = 3) <br/>{< br/> m_list1.setitemtext (J, 6, "failed "); <br/> m_list1.setitemtext (J, 7, m_strlasterror); <br/> nerrorcount ++; <br/>}< br/> invalidate (true ); <br/>}< br/>}

 

 

4. as follows:

 

VC ++ source code:

 

Http://www.rayfile.com/files/4c86cd6b-3f3e-11df-9bad-0015c55db73d/