如何快速将ECXEL母表快速批量生成多个子表分表,并且在表中插入sheet子表的简单操作:
//AdvStringGrid2->SaveToXLS(filename,false);//生成新EXCEL表格
//AdvStringGrid2->SaveToXLSSheet(filename,Fname);//插入sheet子表
//---------------------------------------------------------------------------
void __fastcall TForm1::Button2Click(TObject *Sender)
{
if(RzButtonEdit1->Text=="")
{
ShowMessage("请选择生成分表路径!");
return;
}
//删除所有表格文件
TSearchRec sr;
String Dir =RzButtonEdit1->Text;
if (FindFirst(Dir+"\\*.xls",faAnyFile,sr) == 0)
{
do
{
DeleteFile(Dir+"\\"+sr.Name); //注数
} while(FindNext(sr) == 0);
FindClose(sr);
}
//删除所有表格文件
if (FindFirst(Dir+"\\*.xlsx",faAnyFile,sr) == 0)
{
do
{
DeleteFile(Dir+"\\"+sr.Name); //注数
} while(FindNext(sr) == 0);
FindClose(sr);
}
//生成表头
AdvStringGrid2->ClearAll();
TStringList *Bt =new TStringList;
Bt->Clear();
//全选文件
for(int i=0;i<RzListView1->Items->Count;i++)
{
if(RzListView1->Items->Item[i]->Checked)
{
Bt->Append(RzListView1->Items->Item[i]->SubItems->Strings[0] ); //有效字段分表
}
}
AdvStringGrid2->ColCount=Bt->Count;
for(int i=0;i<Bt->Count ;i++)
{
AdvStringGrid2->Cells[i][0]=Bt->Strings[i];
}
//开始分表
TStringList *Btzd =new TStringList;
Btzd->Clear();
int ZDint=0; //查找字段列
String ZDstr =ComboBox1->Text;
for(int i=0;i<AdvStringGrid1->ColCount;i++)
{
if(AdvStringGrid1->Cells[i][0]==ZDstr)
{
ZDint=i;
break; //找到字段列
}
}
//查找字段列有多少个不同类
for(int i=1;i<AdvStringGrid1->RowCount;i++)
{
if(!PDyn(AdvStringGrid1->Cells[ZDint][i],Btzd))
{
Btzd->Append(AdvStringGrid1->Cells[ZDint][i]); //找到不同类就入表
}
}
//开始分表
RzListBox1->Clear();
for(int Bi =0;Bi <Btzd->Count;Bi++)
{
int Bii =0; //分表的列
int Bj =0; //分表的行
for(int i=0;i<AdvStringGrid1->ColCount;i++)
{
if(PDyn(AdvStringGrid1->Cells[i][0],Bt)) //有效字段
{
Bj =0;
for(int j=1;j<AdvStringGrid1->RowCount;j++)
{
if( AdvStringGrid1->Cells[ZDint][j]== Btzd->Strings[Bi] ) //分表字段类
{
Bj++;
AdvStringGrid2->Cells[Bii][Bj]=AdvStringGrid1->Cells[i][j];
}
}
Bii ++;
}
}
AdvStringGrid2->RowCount=Bj+1;
//生成分表
AdvStringGrid2->SaveToXLS(RzButtonEdit1->Text+"\\"+Btzd->Strings[Bi]+"_分表.xlsx",false);
RzListBox1->Items->Append(RzButtonEdit1->Text+"\\"+Btzd->Strings[Bi]+"_分表.xlsx");
AdvStringGrid2->SaveToXLSSheet(Label2->Caption,Btzd->Strings[Bi]);//插入sheet子表,label2是指母表的文件名路径,后面参数是sheet表的名称。
}
delete Bt ;
delete Btzd ;
}
//---------------------------------------------------------------------------
bool TForm1::PDyn(String Pstr,TStringList *ZDlist)
{
bool Pyn =false;
for(int Bi =0;Bi <ZDlist->Count;Bi++)
{
if(ZDlist->Strings[Bi]==Pstr)
{
Pyn=true ;
break;
}
}
return Pyn;
}