Import and export of MFC ListControl and Excel data

ListControl View Type Select report

voidCexceltestdlg::onbnclickedinput () {//Todo:add your control notification handler code hereCFileDialog Dlg (TRUE,//true or False. True to open the file; False to save the file_t ("xls"),//is the default extension_t ("FileList"),//The file name for the edit box that appears in the File name combo box, which is generally optional nullofn_hidereadonly| Ofn_overwriteprompt,//for dialog style, generally ofn_hidereadonly | Ofn_overwriteprompt, that is, hides the read-only option and prompts before overwriting existing files. _t ("Excel file (*.xls) |*.xls| |")//show file types in pivot for drop-down list                  ); Dlg.m_ofn.lpstrTitle= _t ("Import Data"); if(DLG. DoModal ()! =IDOK)return; CString strFilePath; //get file path namestrFilePath =dlg. GetPathName (); //to determine if the file already exists, open the fileDWORD dwre =GetFileAttributes (strFilePath);if(Dwre! = (DWORD)-1 ) {  //ShellExecute (null, NULL, strFilePath, NULL, NULL, sw_restore); } Else return; CDatabase db;//Database library needs to include header files #include <afxdb.h>CString Sdriver =_t ("MICROSOFT EXCEL DRIVER (*. XLS)") ;//Excel DriverCString ssql,arr[3]; Ssql.format (_t ("driver={%s};D sn= "; Firstrowhasnames=1; Readonly=false; Create_db=\ "%s\";D bq=%s"), Sdriver, strFilePath, strFilePath); if(!db. OpenEx (Ssql,cdatabase::noodbcdialog))//connect the data source DJB. XLS{MessageBox (_t ("failed to open Excel file!","Error")); return; } //Open Excel TableCRecordset PSet (&db); M_list. Deleteallitems (); AfxMessageBox (_t ("OK"));/*sSQL = "Select study number, name, performance" from Exceldemo; */                        //"ORDER by name";Ssql.format (_t ("SELECT * FROM [sheet1$]")); PSet. Open (crecordset::forwardonly,ssql,crecordset::readonly);  while(!PSet. IsEOF ()) {PSet. GetFieldValue (_t ("School Number"), arr[0]);//The preceding field must be the same as in the table, otherwise an error occurs. PSet. GetFieldValue (_t ("name"), arr[1]); PSet. GetFieldValue (_t ("Achievements"), arr[2]); intCount = M_list. GetItemCount ();//inserting into the ListctrlM_list. InsertItem (count,arr[0]); M_list. Setitemtext (Count,1, arr[1]); M_list. Setitemtext (Count,2, arr[2]); PSet. MoveNext (); } db.  Close (); MessageBox (_t ("Excel data import system successfully!","Import succeeded"));} //Export data to ExcelvoidCexceltestdlg::onbnclickedoutput () {////Todo:add Your control notification handler code here    //first determine if there is a record in the list box    if(M_list. GetItemCount () <=0) {AfxMessageBox (_t ("There are no records in the list to save! ")); return; }    //to open the Save As dialog box, you need to include #include <Afxdlgs.h>CFileDialog dlg (FALSE, _t ("xls"), _t ("FileList"), Ofn_hidereadonly|ofn_overwriteprompt, _t ("Excel file (*.xls) |*.xls| |")); Dlg.m_ofn.lpstrTitle=_t ("save File list as"); if(DLG. DoModal ()! =IDOK)return;    CString strFilePath; //get file path namestrFilePath =dlg.    GetPathName (); //determine if the file already exists, delete the rebuildDWORD dwre =GetFileAttributes (strFilePath); if(Dwre! = (DWORD)-1) {DeleteFile (strFilePath); } CDatabase database;//Database library needs to include header files #include <afxdb.h>CString Sdriver =_t ("MICROSOFT EXCEL DRIVER (*. XLS)";)//Excel DriverCString Ssql,strinsert; TRY {//Create a string to accessSsql.format (_t ("driver={%s};D sn= "; Firstrowhasnames=1; Readonly=false; Create_db=\ "%s\";D bq=%s"), Sdriver, strFilePath, strFilePath); //Create a database (both Excel table files)        if(database. OpenEx (Ssql,cdatabase::noodbcdialog)) {//get the total number of columns in a column box            intIcolumnnum,irowcount;            Lvcolumn Lvcol; CString Strcolname; //used to save column header names            intI,j;//column, row loop parametersIcolumnnum= M_list. GetHeaderCtrl ()GetItemCount (); Irowcount=m_list.            GetItemCount (); sSQL= _t ("CREATE TABLE dso_dx ("); Strinsert= _t ("INSERT into DSO_DX (") ; //Get column Header nameLvcol.mask = Lvcf_text;//Required setting, indicating that the Psztext parameter in the Lvcolumn variable is validLvcol.cchtextmax = +;//must be set, the size of the string to which the Psztext parameter pointsLvcol.psztext = Strcolname.getbuffer ( +);//required, the actual storage location of the string to which the pszText points. //the above three parameters are set before the name of the column header can be obtained through the GetColumn () function             for(i=0; i< Icolumnnum; i++ )            {                if( ! (M_list. GetColumn (i,&Lvcol)) )                    return; if(i<icolumnnum-1) {sSQL= sSQL + Lvcol.psztext + _t ("TEXT,"); Strinsert= Strinsert + Lvcol.psztext + _t (" , "); }                Else{sSQL= sSQL + Lvcol.psztext + _t ("TEXT)"); Strinsert= Strinsert + Lvcol.psztext +_t (") VALUES ("); }            }            //Create an Excel table filedatabase.            ExecuteSQL (sSQL); //loop-Fetch records and insert them into ExcelsSQL =Strinsert; Charchtemp[ -];  for(j=0; J<irowcount; J + +) {memset (chtemp,0, -);  for(i=0; I<icolumnnum; i++) {m_list. GetItemText (J,i,chtemp, -); if(I < (icolumnnum-1) ) {sSQL= sSQL + _t ("'") + Chtemp + _t ("',"); }                    Else{sSQL= sSQL +_t ("'") + Chtemp +_t ("')") ; }                }                //insert a record in a tabledatabase.                ExecuteSQL (sSQL); sSQL=Strinsert; }        }             //Close Excel Table filedatabase.        Close (); AfxMessageBox (_t ("Save query results for Excel file Success! ")); } catch_all (e) {//There are many types of errors, as needed to make an error. AfxMessageBox (_t ("Excel File save failed. ")); } End_catch_all;}

Import and export of MFC ListControl and Excel data