多个Excel文件中的多个Sheet合并到一个Excel文件中两个函数
来源:互联网 发布:java算法1到n的阶乘和 编辑:程序博客网 时间:2024/06/09 13:38
{
多个Excel文件中的多个Sheet合并到一个Excel文件中两个函数即使用实例
create date :2008-04-01
create man:Internetroot
注意事项:本方法仅能成功复制字符数量小于256的单元格,超过255个字符将被截取,请谨慎参考。
}
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, OleServer, ComObj, Clipbrd, Buttons, ShellAPI; //务必加入ComObj
type
TForm1 = class(TForm)
btn3: TButton;
btn1: TBitBtn;
procedure btn3Click(Sender: TObject);
procedure btn1Click(Sender: TObject);
private
...{ Private declarations }
public
...{ Public declarations }
end;
//合并两个Excel文件到一个Excel文件
function MergerExcel(sFile1Path, sFile2Path: string; sModelFile: string;
sTargetFile: string; out sErrMsg: string): Boolean; overload;
//合并多个Excel文件到一个Excel文件
function MergerExcel(sFileList: TStrings; sModelFile: string;
sTargetFile: string; out sErrMsg: string): Boolean; overload;
var
Form1: TForm1;
implementation
...{$R *.dfm}
function MergerExcel(sFile1Path, sFile2Path: string; sModelFile: string; sTargetFile: string; out
sErrMsg: string): Boolean;
var ExcelApp: Variant;
ExcelWorkBook1: OleVariant;
ExcelWorkBook2: OleVariant;
ExcelWorkBook3: OleVariant;
i: Integer;
begin
Result:=False;
try
ExcelApp := CreateOleObject('Excel.Application');
//ExcelApp.Visible := True;
ExcelWorkBook1 := ExcelApp.WorkBooks.Open(sFile1Path);
ExcelWorkBook2 := ExcelApp.WorkBooks.Open(sFile2Path);
ExcelWorkBook3 := ExcelApp.WorkBooks.Open(sModelFile);
//骤文件骤Sheet复制到模版文件中
for i := 1 to ExcelWorkbook1.Sheets.Count do
begin
ExcelWorkBook1.Sheets[i].Copy(Before := ExcelWorkBook3.Sheets[i]);
end;
ExcelWorkBook1.Close;
for i := 1 to ExcelWorkbook2.Sheets.Count do
begin
ExcelWorkBook2.Sheets[i].Copy(Before := ExcelWorkBook3.Sheets[i]);
end;
ExcelWorkBook2.Close;
//删除可能已经存在的目标文件
if FileExists(sTargetFile) then
DeleteFile(sTargetFile);
//将生成的Model文件另存为
ExcelWorkBook3.SaveAs(sTargetFile);
ExcelWorkBook3.Close;
ExcelApp.Quit;
Result:=True;
except
on E: Exception do
begin
sErrMsg := E.Message;
Result:=False;
end;
end;
end;
//合并多个Excel文件到一个Excel文件
function MergerExcel(sFileList: TStrings; sModelFile: string;
sTargetFile: string; out sErrMsg: string): Boolean;
var ExcelApp: Variant;
ExcelWorkBookS: OleVariant;
ExcelWorkBookD: OleVariant;
i,iFileOrder: Integer;
begin
Result:=False;
try
ExcelApp := CreateOleObject('Excel.Application');
//ExcelApp.Visible := True;
ExcelWorkBookD := ExcelApp.WorkBooks.Open(sModelFile);
for iFileOrder :=0 to sFileList.Count-1 do
begin
ExcelWorkBookS := ExcelApp.WorkBooks.Open(sFileList.Strings[iFileOrder]);
//骤文件骤Sheet复制到模版文件中
for i := 1 to ExcelWorkbookS.Sheets.Count do
begin
ExcelWorkBookS.Sheets[i].Copy(Before := ExcelWorkBookD.Sheets[i]);
end;
ExcelWorkBookS.Close;
end;
//删除可能已经存在的目标文件
if FileExists(sTargetFile) then
DeleteFile(sTargetFile);
//将生成的Model文件另存为
ExcelWorkBookD.SaveAs(sTargetFile);
ExcelWorkBookD.Close;
ExcelApp.Quit;
Result:=True;
except
on E: Exception do
begin
sErrMsg := E.Message;
Result:=False;
end;
end;
end;
procedure TForm1.btn3Click(Sender: TObject);
var sXlsFileName1, sXlsFileName2, sXlsFileName3, sTargetFile, s: string;
h:THandle;
begin
sXlsFileName1 := ExtractFilePath(Application.ExeName) + '1.xls';
sXlsFileName2 := ExtractFilePath(Application.ExeName) + '2.xls';
sXlsFileName3 := ExtractFilePath(Application.ExeName) + '3.xls';
sTargetFile := ExtractFilePath(Application.ExeName) + '4.xls';
MergerExcel(sXlsFileName1, sXlsFileName2, sXlsFileName3, sTargetFile, s);
ShellExecute(Handle, 'open', pchar(sTargetFile), nil, nil, SW_SHOWNORMAL);
end;
procedure TForm1.btn1Click(Sender: TObject);
var sl:TStrings;
sModel,sTargetFile, s:string;
begin
sl:=TStringList.Create;
sl.Add(ExtractFilePath(Application.ExeName) + '1.xls');
sl.Add(ExtractFilePath(Application.ExeName) + '2.xls');
sl.Add(ExtractFilePath(Application.ExeName) + '包装方案1.xls');
sl.Add(ExtractFilePath(Application.ExeName) + '包装方案2.xls');
sl.Add(ExtractFilePath(Application.ExeName) + '包装方案3.xls');
sModel:= ExtractFilePath(Application.ExeName) + '3.xls';
sTargetFile:= ExtractFilePath(Application.ExeName) + '4.xls';
MergerExcel(sl,sModel, sTargetFile, s);
end;
end.
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, OleServer, ComObj, Clipbrd, Buttons, ShellAPI; //务必加入ComObj
type
TForm1 = class(TForm)
btn3: TButton;
btn1: TBitBtn;
procedure btn3Click(Sender: TObject);
procedure btn1Click(Sender: TObject);
private
...{ Private declarations }
public
...{ Public declarations }
end;
//合并两个Excel文件到一个Excel文件
function MergerExcel(sFile1Path, sFile2Path: string; sModelFile: string;
sTargetFile: string; out sErrMsg: string): Boolean; overload;
//合并多个Excel文件到一个Excel文件
function MergerExcel(sFileList: TStrings; sModelFile: string;
sTargetFile: string; out sErrMsg: string): Boolean; overload;
var
Form1: TForm1;
implementation
...{$R *.dfm}
function MergerExcel(sFile1Path, sFile2Path: string; sModelFile: string; sTargetFile: string; out
sErrMsg: string): Boolean;
var ExcelApp: Variant;
ExcelWorkBook1: OleVariant;
ExcelWorkBook2: OleVariant;
ExcelWorkBook3: OleVariant;
i: Integer;
begin
Result:=False;
try
ExcelApp := CreateOleObject('Excel.Application');
//ExcelApp.Visible := True;
ExcelWorkBook1 := ExcelApp.WorkBooks.Open(sFile1Path);
ExcelWorkBook2 := ExcelApp.WorkBooks.Open(sFile2Path);
ExcelWorkBook3 := ExcelApp.WorkBooks.Open(sModelFile);
//骤文件骤Sheet复制到模版文件中
for i := 1 to ExcelWorkbook1.Sheets.Count do
begin
ExcelWorkBook1.Sheets[i].Copy(Before := ExcelWorkBook3.Sheets[i]);
end;
ExcelWorkBook1.Close;
for i := 1 to ExcelWorkbook2.Sheets.Count do
begin
ExcelWorkBook2.Sheets[i].Copy(Before := ExcelWorkBook3.Sheets[i]);
end;
ExcelWorkBook2.Close;
//删除可能已经存在的目标文件
if FileExists(sTargetFile) then
DeleteFile(sTargetFile);
//将生成的Model文件另存为
ExcelWorkBook3.SaveAs(sTargetFile);
ExcelWorkBook3.Close;
ExcelApp.Quit;
Result:=True;
except
on E: Exception do
begin
sErrMsg := E.Message;
Result:=False;
end;
end;
end;
//合并多个Excel文件到一个Excel文件
function MergerExcel(sFileList: TStrings; sModelFile: string;
sTargetFile: string; out sErrMsg: string): Boolean;
var ExcelApp: Variant;
ExcelWorkBookS: OleVariant;
ExcelWorkBookD: OleVariant;
i,iFileOrder: Integer;
begin
Result:=False;
try
ExcelApp := CreateOleObject('Excel.Application');
//ExcelApp.Visible := True;
ExcelWorkBookD := ExcelApp.WorkBooks.Open(sModelFile);
for iFileOrder :=0 to sFileList.Count-1 do
begin
ExcelWorkBookS := ExcelApp.WorkBooks.Open(sFileList.Strings[iFileOrder]);
//骤文件骤Sheet复制到模版文件中
for i := 1 to ExcelWorkbookS.Sheets.Count do
begin
ExcelWorkBookS.Sheets[i].Copy(Before := ExcelWorkBookD.Sheets[i]);
end;
ExcelWorkBookS.Close;
end;
//删除可能已经存在的目标文件
if FileExists(sTargetFile) then
DeleteFile(sTargetFile);
//将生成的Model文件另存为
ExcelWorkBookD.SaveAs(sTargetFile);
ExcelWorkBookD.Close;
ExcelApp.Quit;
Result:=True;
except
on E: Exception do
begin
sErrMsg := E.Message;
Result:=False;
end;
end;
end;
procedure TForm1.btn3Click(Sender: TObject);
var sXlsFileName1, sXlsFileName2, sXlsFileName3, sTargetFile, s: string;
h:THandle;
begin
sXlsFileName1 := ExtractFilePath(Application.ExeName) + '1.xls';
sXlsFileName2 := ExtractFilePath(Application.ExeName) + '2.xls';
sXlsFileName3 := ExtractFilePath(Application.ExeName) + '3.xls';
sTargetFile := ExtractFilePath(Application.ExeName) + '4.xls';
MergerExcel(sXlsFileName1, sXlsFileName2, sXlsFileName3, sTargetFile, s);
ShellExecute(Handle, 'open', pchar(sTargetFile), nil, nil, SW_SHOWNORMAL);
end;
procedure TForm1.btn1Click(Sender: TObject);
var sl:TStrings;
sModel,sTargetFile, s:string;
begin
sl:=TStringList.Create;
sl.Add(ExtractFilePath(Application.ExeName) + '1.xls');
sl.Add(ExtractFilePath(Application.ExeName) + '2.xls');
sl.Add(ExtractFilePath(Application.ExeName) + '包装方案1.xls');
sl.Add(ExtractFilePath(Application.ExeName) + '包装方案2.xls');
sl.Add(ExtractFilePath(Application.ExeName) + '包装方案3.xls');
sModel:= ExtractFilePath(Application.ExeName) + '3.xls';
sTargetFile:= ExtractFilePath(Application.ExeName) + '4.xls';
MergerExcel(sl,sModel, sTargetFile, s);
end;
end.
//注意:把sheet1复制到sheet2时,如果sheet1中某个单元格内容字符数超过225时会出现截断该单元格内容的现象,这是Excel自身问题,获取解决办法请参阅MSDN相关文档。
- 多个Excel文件中的多个Sheet合并到一个Excel文件中两个函数
- 将Dataset中的多个DataTable导入到一个Excel文件的多个Sheet中
- Excel 多个sheet中的内容合并到一个sheet中
- 把多个excel文件的sheet1数据合并到一个excel文件的sheet1中
- Excel合并多个工作表(Sheet)到一个Sheet中脚本
- 多个Excel文件的第一个工作表(Sheet)合并到一个Excel文件的多个工作表里
- 用VBA实现把多个Excel文件合并到一个Excel文件的多个工作表(Sheet)里
- ASP.NET将数据导出到一个EXCEL文件的多个SHEET中
- c#(Asp.net)多个Excel文件合并到一个新的excel中
- 多个excel文件如何快速合并到一个excel中
- excel文件的多个sheet表合并成一个csv文件并导入数据库
- 使用VBA合并多个EXCEL文件到一个EXCEL文件
- Java将多个数据表导出多个Sheet到Excel文件
- 合并多个Excel文件
- Excel 合并多个文件
- 【Excel技巧】合并多个Sheet为一个的方法
- 快速合并同一个excel表中的多个sheet
- 合并多个文本文件中的内容到一个文件中
- 汽车驾驶技巧-倒车入库技巧图解-怎样倒车入库
- 浅谈在ASP.NET的权限控制机制
- 使用WebBrowser打开Powerpoint时只自动显示前两页的解决办法
- 汽车驾驶学习-驾驶技巧:倒车速成
- 如何查看Windows性能
- 多个Excel文件中的多个Sheet合并到一个Excel文件中两个函数
- raw socket 编程
- .NET学习笔记(八) ------委托(上)
- 汽车驾驶学习-驾驶技巧:图解如何安全停车的技巧
- 浅谈美国的动画片
- Archive of the forum RCE
- ARTeam: ArmaGeddon 1.31
- 汽车驾驶学习-驾驶技巧:小汽车如何起步停车-怎样驾驶小汽车-汽车起步要领
- WEB2.0应当主导天灾预测