使用 FDBatchMove 导入 Excel 文件到数据库

使用 FDBatchMove 导入 Excel 文件到数据库

void __fastcall TForm1::Button8Click(TObject *Sender)
{TLocateOptions Opts1;OpenDialog1->Title = "打开 Excel 表文件";OpenDialog1->Filter = "Excel文件(*.xls)|*.xls;*.xlsx";if (OpenDialog1->Execute()) {Memo2->Lines->Add("开始生成数据库并迁移数据");for (int i = 0; i < OpenDialog1->Files->Count; i++) {String OPfile = OpenDialog1->Files->Strings[i];if (FDConnection2->Connected == true) {FDConnection2->Connected = false;}FDConnection2->Params->Clear();FDConnection2->Params->Add("ODBCDriver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}");FDConnection2->Params->Add("DataSource=Excel Files");FDConnection2->Params->Add("DriverID=ODBC");FDConnection2->Params->Add("Database=" + OPfile);// OpenDialog1->FileName);FDConnection2->Connected = true;//Memo1->Lines->Add(OPfile);// FDConnection2->GetTableNames("","","",TableList); //这里不知道为什么取不出来Excel表中的Sheet名字，if (FDConnection1->Connected == true) {FDConnection1->Connected = false;}FDConnection1->Params->Clear();FDConnection1->Params->Add("DriverID=SQLite");FDConnection1->Params->Add("Database=New1.sdb");FDConnection1->Connected = true;FDCommand1->Connection = FDConnection1;FDCommand1->CommandText->Clear();FDCommand1->CommandText->Text = "DELETE FROM tablename";FDCommand1->Active = True;FDQuery1->Connection = FDConnection2;FDQuery1->SQL->Text = "select * from [Sheet0$] where F7 like '%4%'";FDQuery1->Active =true;//	String rq = FDQuery1->Fields->Fields[6]->Text;//	FDQuery1->Active = false;FDBatchMoveSQLReader1->Connection = FDConnection2;FDBatchMoveSQLReader1->ReadSQL ="select * from [Sheet0$] where F7 like '%4%'";FDTable1->Connection = FDConnection1;FDTable1->TableName ="tablename";FDTable1->Active = true;FDBatchMoveDataSetWriter1->DataSet = FDTable1;FDBatchMove1->Mappings->Clear();for(int i=0;i<FDQuery1->FieldCount;i++){String aa=FDTable1->Fields->Fields[i]->FieldName+"="+FDQuery1->Fields->Fields[i]->FieldName;//FDTable1->Fields->Fields[i]->FieldName+"="+FDQuery1->Fields->Fields[i]->FieldName;//	FDBatchMove1->Mappings->Add(FDTable1->Fields->Fields[i]->FieldName+"="+FDQuery1->Fields->Fields[i]->FieldName);Memo2->Lines->Add(aa);FDBatchMove1->Mappings->Add()->AsText=aa; //如果Excel表中的第一行是中文名字，程序不能运行，提示invalid character found，如果把Excelg表的第一行第一个字段改为英文，运行成功。}FDBatchMove1->Reader= *FDBatchMoveSQLReader1;FDBatchMove1->Writer=  *FDBatchMoveDataSetWriter1;FDBatchMove1->Execute();Memo2->Lines->Add(String().sprintf(L"---- %s  中 %d 条记录已成功迁移 ", FDTable1->TableName, FDBatchMove1->InsertCount));OPfile = "";}}FDBatchMove1->Mappings->Clear();FDConnection2->Connected = false;FDConnection1->Connected = false;}