DELPHIが指定したフォーマットのEXCELテンプレートにデータをエクスポートする方法
参照コード1
Delphi(Pascal) code
Delphi(Pascal) code path:=ExtractFilePath(Application.ExeName); if self.OpenDialog1.Execute then filename:=self.OpenDialog1.FileName; try Self.ExcelApplication1:=TExcelApplication.Create(self); Self.ExcelApplication1.Connect; Except messagebox(アプリケーション.Handle,'Excelレポートを生成できません.Excelをインストールして再試行してください','情報',mb_ok or mb_iconinformation);e…
try excel := CreateOleObject('Excel.Application'); WorkBook := excel.Workbooks.Add('テンプレートのパス.xls'); Sheet := WorkBook.Worksheets[1]; except excel := NULL; DJshow('Excel 97/2000をインストールしてください.'); EXIT; end; excel.Visible:=true; I := 2;//データベースからデータを取得すると仮定しますwith ADOQuery 1 do try first; while not eof do begin Sheet.cells(i, 1) := FieldbyName('Field1').asstring; Sheet.cells(i, 2) := FieldbyName('Field2').asstring; Sheet.cells(i, 3) := FieldbyName('Field3').asstring; Inc(I); Next; end; finally free; end; try excel.Visible:=true; WorkBook.Saved := True; finally excel.Quit; excel:=Unassigned; end;
Delphi(Pascal) code
path:=ExtractFilePath(Application.ExeName);
if self.OpenDialog1.Execute then
filename:=self.OpenDialog1.FileName;
try
Self.ExcelApplication1:=TExcelApplication.Create(self);
Self.ExcelApplication1.Connect;
except
messagebox(application.Handle,' Excel , Excel ',' ',mb_ok or mb_iconinformation);
exit;
end;
Self.ExcelApplication1.Visible[0]:=true;
self.ExcelApplication1.DisplayAlerts[0]:=False;
self.ExcelApplication1.Workbooks.Open(filename,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,0);
self.ExcelWorkbook1.ConnectTo(Self.ExcelApplication1.Workbooks[1]);
self.ExcelWorksheet1:=TExcelWorkSheet.Create(self);
self.ExcelWorksheet1.ConnectTo(Self.ExcelWorkbook1.Worksheets[1] as _worksheet);
i:=self.StringGrid2.RowCount;
for j:=1 to i-1 do
begin
xh:=Self.StringGrid2.Cells[0,j];
pscj:=self.StringGrid2.Cells[2,j];
kscj:=Self.StringGrid2.Cells[4,j];
zpcj:=Self.StringGrid2.Cells[5,j];
self.ExcelWorksheet1.cells.Item[l+j,m]:=pscj;
self.ExcelWorksheet1.cells.Item[l+j,n]:=kscj;
self.ExcelWorksheet1.cells.Item[l+j,k]:=zpcj;
end;
Self.ExcelWorksheet1.SaveAs(filename);
Self.ExcelApplication1.Disconnect;
Self.ExcelWorkbook1.Disconnect;
Self.ExcelWorksheet1.Disconnect;
Delphi(Pascal) code path:=ExtractFilePath(Application.ExeName); if self.OpenDialog1.Execute then filename:=self.OpenDialog1.FileName; try Self.ExcelApplication1:=TExcelApplication.Create(self); Self.ExcelApplication1.Connect; Except messagebox(アプリケーション.Handle,'Excelレポートを生成できません.Excelをインストールして再試行してください','情報',mb_ok or mb_iconinformation);e…
try excel := CreateOleObject('Excel.Application'); WorkBook := excel.Workbooks.Add('テンプレートのパス.xls'); Sheet := WorkBook.Worksheets[1]; except excel := NULL; DJshow('Excel 97/2000をインストールしてください.'); EXIT; end; excel.Visible:=true; I := 2;//データベースからデータを取得すると仮定しますwith ADOQuery 1 do try first; while not eof do begin Sheet.cells(i, 1) := FieldbyName('Field1').asstring; Sheet.cells(i, 2) := FieldbyName('Field2').asstring; Sheet.cells(i, 3) := FieldbyName('Field3').asstring; Inc(I); Next; end; finally free; end; try excel.Visible:=true; WorkBook.Saved := True; finally excel.Quit; excel:=Unassigned; end;
comobj;
procedure TForm1.Button1Click(Sender: TObject);
var a,b : string;
ExcelApp,WorkBook:Variant;
ExcelRowCount:integer;
i:integer;
begin
ExcelApp:= CreateOleObject('Excel.Application'); // Excel
opendialog1.Execute; //
WorkBook := ExcelApp.WorkBooks.Open(opendialog1.FileName);
ExcelApp.Visible := false;
ExcelRowCount := WorkBook.WorkSheets[1].UsedRange.Rows.Count; // Excel
for i:=1 to ExcelRowCount+1 do
begin
a:=excelapp.Cells.Value; //
b:=excelapp.Cells.Value; //
if (excelapp.Cells.Value='')and(excelapp.Cells.Value='') then //
break
else
begin
with adoquery1 do
begin
close;
sql.Clear;
sql.Add('insert into reny0830(a,b) values(:A,:B)'); // Excel
parameters.ParamByName('A').Value:=a;
parameters.ParamByName('B').Value:=b;
Execsql;
end;
end;
end;
WorkBook.Close;//
ExcelApp.Quit; // Excel
ExcelApp:=Unassigned;//
WorkBook:= Unassigned;
with adoquery1 do //
begin
close;
sql.Clear;
sql.Add('select * from reny0830');
open;
end;
end;
procedure TmainFrm.Button4Click(Sender: TObject);
var i, row, column:integer;
begin
if not mainAdo.Active then
begin
messagedlg(' , Excel!',mtwarning,[mbOK],0);
end
else
begin
try ExcelApplication1.Connect;
except
MessageDlg('Excel may not be installed',
mtError, [mbOk], 0);
Abort;
End;
mainAdo.First;
ExcelApplication1.Visible[0] := true;
ExcelApplication1.Caption := 'Excel';
ExcelApplication1.Workbooks.Add(Null, 0);
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);
row:=1;
for i:=0 to mainAdo.FieldCount-1 do
begin
ExcelWorksheet1.Cells.Item[1,i+1]:=mainAdo.Fields[i].DisplayName;
end;
row:=row+1;
while not mainAdo.Eof do
begin
column:=1;
for i:=1 to mainAdo.FieldCount do
begin
ExcelWorksheet1.Cells.Item[row,column]:=mainAdo.Fields[i-1].AsString;
column:=column+1;
end;
mainAdo.Next;
row:=row+1;
end;
ExcelApplication1.Disconnect;
ExcelWorkbook1.Disconnect;
ExcelWorksheet1.Disconnect;
end;
end;