MySQL data import into Excel

//

using namespace Excel;

void Cimportexcel::onbnclickedbuttonselectpath ()
{
TODO: Add control notification Handler code here
CString Strtestname,strtmp,strtmpex;
BOOL Bnamevalid;
((ccombobox*) GetDlgItem (idc_combo_dataname))->getwindowtext (strtestname);
if (Strtestname.isempty ())
{
Bnamevalid=strtmp.loadstring (Ids_tishi);
ASSERT (Bnamevalid);
Bnamevalid=strtmpex.loadstring (Ids_testnamenull);
ASSERT (Bnamevalid);
MessageBox (STRTMPEX,STRTMP);
Return
}
CFileDialog Filedlg (FALSE,
Null
Strtestname,
ofn_hidereadonly | Ofn_overwriteprompt,
"Excel file (*.xls) |*.xls| |",
Null,0,true);
if (Filedlg.domodal () ==idok)
{
Strtmp=filedlg.getpathname ();
CString Strfileext=filedlg.getfileext ();
if (Strfileext.isempty ())
{
strtmp+= ". xls";
}
CFile file;
if (file. Open (strtmp,cfile::modecreate) ==false)
{
Return
}
File. Close ();
if (Strtmp.isempty () ==false)
{
((CEdit *) GetDlgItem (Idc_edit_savepath))->setwindowtext (strtmp);
((cbutton*) GetDlgItem (IDOK))->enablewindow (TRUE);
}
}
}

BOOL cimportexcel::improtdataexcel (CString strsavepath,cstring strtestname)
{
if (M_datatbnames.getcount () <=0)
{
return FALSE;
}
if (&mysql==null)
{
return FALSE;
}
Pprogressctrl->setpos (5);

_bstr_t filepath= (_bstr_t) Strsavepath;
CoInitializeEx (0, coinit_apartmentthreaded);//These two sentences are placed outside the function
_applicationptr Papp;
Workbooksptr Pbooks;
_workbookptr Pbook;
Sheetsptr psheets;
_worksheetptr Psheet;
Rangeptr PRang;
_variant_t vartmp;

Mysql_res *m_res;
Mysql_row M_row;

COleVariant vtrue ((short) TRUE), Vfalse ((short) FALSE);
Try
{
Papp.createinstance (__uuidof (excel::application));
Pbooks= Papp->getworkbooks ();
Pbook = Pbooks->add (_variant_t (FilePath));
Psheets= pbook->getworksheets ();
Psheet= Pbook->getactivesheet ();
Psheet->activate ();//support for a single form
Pprogressctrl->setpos (10);

int nrownum=0;
int Ntbcount=m_datatbnames.getcount ();
CString Strtbname,strfildname,strcolname1,strcolname2,strcolindex1,strcolindex2,strcurrownum,strindex1, Strindex2,strsql;
CString Strvalue1,strvalue2,strtmp,strtmpvalue;
int npos=80/ntbcount;
for (int ii=0;ii<ntbcount;ii++)
{
Nrownum=1;
Strcurrownum.empty ();
Strcolname1.empty ();
Strcolname2.empty ();
Strcolindex1.empty ();
Strcolindex2.empty ();
Strindex1.empty ();
Strindex2.empty ();
Strfildname.empty ();
Strcurrownum.format ("%d", nrownum);
Strtbname=m_datatbnames.getat (ii);
getColumnName (strtbname,strfildname,strcolname1,strcolname2);//Dynamically get the column name of the column name and the data value corresponding to the database
if (Strfildname.isempty () | | | Strcolname1.isempty () | | Strcolname2.isempty ())
{
Break
}
Getcolumnindex (II,STRCOLINDEX1,STRCOLINDEX2);//Dynamically get column ordinal in Excel table corresponding to the column name
if (ii==0)
{
Psheet->range[colevariant ("A1")][vtmissing]->value2=colevariant ("serial number");
}
Strindex1+=strcolindex1;
strindex1+=strcurrownum;//(StrIndex1 similar to B1)
Strindex2+=strcolindex2;
strindex2+=strcurrownum;//(StrIndex1 similar to C1)
Psheet->range[colevariant (STRINDEX1)][vtmissing]->value2=colevariant (strColName1);
Psheet->range[colevariant (STRINDEX2)][vtmissing]->value2=colevariant (strColName2);

Database Fetch data

Strsql.format ("Select%s, totaltime from%s where testname= '%s ' ORDER by TotalTime", \
Strfildname,strtbname, Strtestname);
if (mysql_query (mysql,strsql)!=0)
{
return FALSE;
}
M_res=mysql_store_result (MySQL);
while (M_row=mysql_fetch_row (m_res))
{
nrownum++;
Strcurrownum.empty ();
Strindex1.empty ();
Strindex2.empty ();
Strtmpvalue.empty ();
Strtmp.empty ();
Strcurrownum.format ("%d", nrownum);
Strindex1+=strcolindex1;
strindex1+=strcurrownum;//(strIndex1 similar to B1)
Strindex2+=strcolindex2;
strindex2+=strcurrownum;//(StrIndex1 similar to C1)
strvalue1=m_row[0];//corresponding value
strvalue2=m_row[1];//corresponding total time value

Strtmp.format ("a%d", nrownum);//Query ordinal column is empty, empty-time write value
Strtmpvalue=psheet->range[colevariant (strtmp)][vtmissing]->text;
if (Strtmpvalue.isempty ())
{
Strtmpvalue.format ("%d", nRowNum-1);
Psheet->range[colevariant (strtmp)][vtmissing]->value2=colevariant (strtmpvalue);//First Ordinal value
}
Psheet->range[colevariant (STRINDEX1)][vtmissing]->value2=colevariant (strValue2);//write Time value first
Psheet->range[colevariant (STRINDEX2)][vtmissing]->value2=colevariant (strValue1);//write the corresponding value in the next column
}
Mysql_free_result (M_res);
Pprogressctrl->setpos (npos* (ii+1));
}
Pbook->savecopyas (COleVariant (FilePath));
Pbook->put_saved (0,true);
}
catch (cexception* e)
{

}
Papp->quit ();
Pprogressctrl->setpos (100);
return TRUE;
}

MySQL data import into Excel