mysql数据导入excel_MYSQL数据导入EXCEL

//

using namespace Excel;

void CImportExcel::OnBnClickedButtonSelectpath()

{

// TODO: 在此添加控件通知处理程序代码

CString strTestName,strTmp,strTmpEx;

BOOL bNameValid;

((CComboBox*)GetDlgItem(IDC_COMBO_DataName))->GetWindowText(strTestName);

if (strTestName.IsEmpty())

{

bNameValid&＃61;strTmp.LoadString(IDS_TiShi);

ASSERT(bNameValid);

bNameValid&＃61;strTmpEx.LoadString(IDS_TestNameNull);

ASSERT(bNameValid);

MessageBox(strTmpEx,strTmp);

return;

}

CFileDialog fileDlg(FALSE,

NULL,

strTestName,

OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,

"Excel文件(*.xls)|*.xls||",

NULL,0,TRUE);

if (fileDlg.DoModal()&＃61;&＃61;IDOK)

{

strTmp&＃61;fileDlg.GetPathName();

CString strFileExt&＃61;fileDlg.GetFileExt();

if (strFileExt.IsEmpty())

{

strTmp&＃43;&＃61;".xls";

}

CFile file;

if (file.Open(strTmp,CFile::modeCreate)&＃61;&＃61;FALSE)

{

return;

}

file.Close();

if (strTmp.IsEmpty()&＃61;&＃61;FALSE)

{

((CEdit *)GetDlgItem(IDC_EDIT_SavePath))->SetWindowText(strTmp);

((CButton*)GetDlgItem(IDOK))->EnableWindow(TRUE);

}

}

}

BOOL CImportExcel::ImprotDataExcel(CString strSavePath,CString strTestName)

{

if (m_DataTbNames.GetCount()<&＃61;0)

{

return FALSE;

}

if (&mysql&＃61;&＃61;NULL)

{

return FALSE;

}

pProgressCtrl->SetPos(5);

_bstr_t filePath&＃61;(_bstr_t)strSavePath;

//CoInitializeEx( 0, COINIT_APARTMENTTHREADED );//这两句语句放在函数外面

//_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&＃61; pApp->GetWorkbooks();

pBook &＃61; pBooks->Add(_variant_t(filePath));

pSheets&＃61; pBook->GetWorksheets();

pSheet&＃61; pBook->GetActiveSheet();

pSheet->Activate();//支持一个表单

pProgressCtrl->SetPos(10);

int nRowNum&＃61;0;

int nTbCount&＃61;m_DataTbNames.GetCount();

CString strTbName,strFildName,strColName1,strColName2,strColIndex1,strColIndex2,strCurRowNum,strIndex1,strIndex2,strSql;

CString strValue1,strValue2,strTmp,strTmpValue;

int nPos&＃61;80/nTbCount;

for (int ii&＃61;0;ii

{

nRowNum&＃61;1;

strCurRowNum.Empty();

strColName1.Empty();

strColName2.Empty();

strColIndex1.Empty();

strColIndex2.Empty();

strIndex1.Empty();

strIndex2.Empty();

strFildName.Empty();

strCurRowNum.Format("%d",nRowNum);

strTbName&＃61;m_DataTbNames.GetAt(ii);

GetColumnName(strTbName,strFildName,strColName1,strColName2);//动态得到列名和数据库对应的数据值的列名

if (strFildName.IsEmpty()||strColName1.IsEmpty()||strColName2.IsEmpty())

{

break;

}

GetColumnIndex(ii,strColIndex1,strColIndex2);//动态得到该列名相对应的EXCEL表中的列序号

if (ii&＃61;&＃61;0)

{

pSheet->Range[COleVariant("A1")][vtMissing]->Value2&＃61;COleVariant("序号");

}

strIndex1&＃43;&＃61;strColIndex1;

strIndex1&＃43;&＃61;strCurRowNum;//(strIndex1类似B1)

strIndex2&＃43;&＃61;strColIndex2;

strIndex2&＃43;&＃61;strCurRowNum;//(strIndex1类似C1)

pSheet->Range[COleVariant(strIndex1)][vtMissing]->Value2&＃61;COleVariant(strColName1);

pSheet->Range[COleVariant(strIndex2)][vtMissing]->Value2&＃61;COleVariant(strColName2);

//数据库获取数据

strSql.Format("select %s, totalTime from %s where testName&＃61;&＃39;%s&＃39;order by totalTime",\

strFildName,strTbName,strTestName);

if (mysql_query(mysql,strSql)!&＃61;0)

{

return FALSE;

}

m_res&＃61;mysql_store_result(mysql);

while(m_row&＃61;mysql_fetch_row(m_res))

{

nRowNum&＃43;&＃43;;

strCurRowNum.Empty();

strIndex1.Empty();

strIndex2.Empty();

strTmpValue.Empty();

strTmp.Empty();

strCurRowNum.Format("%d",nRowNum);

strIndex1&＃43;&＃61;strColIndex1;

strIndex1&＃43;&＃61;strCurRowNum;//(strIndex1类似B1)

strIndex2&＃43;&＃61;strColIndex2;

strIndex2&＃43;&＃61;strCurRowNum;//(strIndex1类似C1)

strValue1&＃61;m_row[0];//相应值

strValue2&＃61;m_row[1];//相应总时间值

strTmp.Format("A%d",nRowNum);//查询序号列是否为空&＃xff0c;为空时写值

strTmpValue&＃61;pSheet->Range[COleVariant(strTmp)][vtMissing]->Text;

if (strTmpValue.IsEmpty())

{

strTmpValue.Format("%d",nRowNum-1);

pSheet->Range[COleVariant(strTmp)][vtMissing]->Value2&＃61;COleVariant(strTmpValue);//先序号值

}

pSheet->Range[COleVariant(strIndex1)][vtMissing]->Value2&＃61;COleVariant(strValue2);//先写时间值

pSheet->Range[COleVariant(strIndex2)][vtMissing]->Value2&＃61;COleVariant(strValue1);//往后一列写相应值

}

mysql_free_result(m_res);

pProgressCtrl->SetPos(nPos*(ii&＃43;1));

}

pBook->SaveCopyAs(COleVariant(filePath));

pBook->put_Saved(0,TRUE);

}

catch (CException* e)

{

}

pApp->Quit();

pProgressCtrl->SetPos(100);

return TRUE;

}