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;
}