如何创建一个新的excel工作薄?
如何创建一个新的excel工作薄;
我想把query中的数据存入EXcel中(代码很简单):
procedure TForm1.Button2Click(Sender: TObject);
var
excel_application1:Texcelapplication;
excel_workbook1:Texcelworkbook;
excel_worksheet1:Texcelworksheet;
filename:string;
myfile:textfile;
row:integer;
begin
excel_application1:=Texcelapplication.create(application);
excel_worksheet1:=Texcelworksheet.create(application);
excel_workbook1:=Texcelworkbook.create(application);
excel_application1.workbooks.add(emptyparam,0);
excel_workbook1.connectto(excel_application1.workbooks[1]);
excel_worksheet1.connectTo(excel_workbook1.Worksheets[1] as _worksheet);
row:=1;
//存数据；
query1.First;
repeat
excel_worksheet1.Cells.Item[row,1] :=query1.fields[0].AsString ;
excel_worksheet1.cells.item[row,2] :=query1.fields[1].AsString ;
excel_worksheet1.Cells.Item[row,3] :=query1.fields[2].AsString ;
excel_worksheet1.Cells.Item[row,4] :=query1.fields[3].AsString ;
excel_worksheet1.Cells.Item[row,5] :=query1.fields[4].AsString ;
excel_worksheet1.Cells.Item[row,6] :=query1.fields[5].AsString ;
row:=row+1;
query1.Next;
until query1.Eof;
filename:=getcurrentdir+ '\盘点表.xls ';
assignfile(myfile,filename);
if fileexists(filename) then erase(myfile);
excel_worksheet1.SaveAs(filename);
excel_application1.Disconnect;
excel_application1.Quit;
freeandnil(excel_application1);
freeandnil(excel_worksheet1);
freeandnil(excel_workbook1);
end;
如果当前没有Excel工作薄打开的话,运行是正常的,可是如果有工作薄打开的话,
就会把当前工作薄的部分内容覆盖后再保存成新的excel工作薄;
这样一来我新存的工作薄就会留下一些当前打开的工作薄的内容,这是怎么回事?
我想要的是把数据存到一个新工作薄而不被当前打开的excel文件所影响,请问该咋办?谢谢!
[解决办法]
//test:
procedure TForm1.Button2Click(Sender: TObject);
var ExcelApp,MyWorkBook:Variant;
filename:string;
myfile:textfile;
row:integer;
begin
try
ExcelApp:=CreateOleObject( 'Excel.Application ');
MyWorkBook:=CreateOleObject( 'Excel.Sheet ');
except
Application.MessageBox( '确定您的机器是否已安裝EXCEL? ', '進度表 ',MB_ICONERROR);
MyWorkBook.close;
ExcelApp.Quit;
ExcelApp:=Unassigned;
Exit;
end;
row:=1;
//存数据；
query1.First;
repeat
MyWorkBook.Worksheets[1].Cells[row,1] :=query1.fields[0].AsString ;
MyWorkBook.Worksheets[1].Cells[row,2] :=query1.fields[1].AsString ;
MyWorkBook.Worksheets[1].Cells[row,3] :=query1.fields[2].AsString ;
MyWorkBook.Worksheets[1].Cells[row,4] :=query1.fields[3].AsString ;
MyWorkBook.Worksheets[1].Cells[row,5] :=query1.fields[4].AsString ;
MyWorkBook.Worksheets[1].Cells[row,6] :=query1.fields[5].AsString ;
row:=row+1;
query1.Next;
until query1.Eof;
end;
[解决办法]
如上
创建一个Variant类型的Excel对象，
然后创建OLE对象来操作。
如果你需要速度更快，建议使用粘贴板来实现。
[解决办法]
哦，应该是申明 一个Variant类型的Excel对象
[解决办法]
改了一点点地方，应该可以的，楼主试下
var
excel_application1: Texcelapplication;
excel_workbook1: Texcelworkbook;
excel_worksheet1: Texcelworksheet;
filename: string;
myfile: textfile;
row: integer;
begin
excel_application1 := Texcelapplication.create(application);
excel_worksheet1 := Texcelworksheet.create(application);
excel_workbook1 := Texcelworkbook.create(application);
// 打开新增的那页
excel_workbook1.connectto(excel_application1.workbooks.add(emptyparam, 0));
excel_worksheet1.connectTo(excel_workbook1.Worksheets[1] as _worksheet);
excel_worksheet1.Activate;
row := 1;
//存数据；
excel_worksheet1.Cells.Item[row, 1] := 'aa ';
excel_worksheet1.cells.item[row, 2] := 'bb ';
excel_worksheet1.Cells.Item[row, 3] := 'cc ';
excel_worksheet1.Cells.Item[row, 4] := 'dd ';
excel_worksheet1.Cells.Item[row, 5] := 'ee ';
excel_worksheet1.Cells.Item[row, 6] := 'ff ';
row := row + 1;
filename := getcurrentdir + '\盘点表.xls ';
assignfile(myfile, filename);
if fileexists(filename) then erase(myfile);
excel_worksheet1.SaveAs(filename);
excel_worksheet1.Disconnect;
excel_workbook1.Disconnect;
excel_application1.Disconnect;
excel_application1.Quit;
freeandnil(excel_worksheet1);
freeandnil(excel_workbook1);
freeandnil(excel_application1);
end;
[解决办法]
MyWorkBook.worksheets[1].copy(MyWorkBook.worksheets[1]);
MyWorkBook.worksheets[2].Activate;
[解决办法]
to yhyhai:
你先试一下再说亚，我写的那个和你写的那个可不一样的压，怪我注释没写清楚
// 打开新增的那页，你打开的是当前打开的excel的那页的，不是打开的你新增的那页，唉。。
excel_workbook1.connectto(excel_application1.workbooks.add(emptyparam, 0));
excel_worksheet1.connectTo(excel_workbook1.Worksheets[1] as _worksheet);
[解决办法]
这个肯定行，不过你自己改成你的数据：
procedure TForm1.Button3Click(Sender: TObject);
var
idx:integer;
begin
ExcelApplication1.Connect;
ExcelApplication1.Visible[0]:=True;
ExcelApplication1.Workbooks.Add( 'C:\Program Files\Microsoft Office\Templates\XEKFBB.xlt ',0);
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Sheets[1] as _WorkSheet);
ADOQuery1.First;
idx:=7;
while not ADOQuery1.Eof do
begin
ExcelWorksheet1.Cells.Item[idx,1]:=ADOQuery1.FieldValues[ 'unitcode '];
ExcelWorksheet1.Cells.Item[idx,2]:=ADOQuery1.FieldValues[ 'itemcode '];
ExcelWorksheet1.Cells.Item[idx,3]:=ADOQuery1.FieldValues[ 'itemname '];
ExcelWorksheet1.Cells.Item[idx,4]:=ADOQuery1.FieldValues[ 'speci '];
ExcelWorksheet1.Cells.Item[idx,5]:=ADOQuery1.FieldValues[ 'qty '];
ExcelWorksheet1.Cells.Item[idx,6]:=ADOQuery1.FieldValues[ 'qtyunit '];
ExcelWorksheet1.Cells.Item[idx,7]:=ADOQuery1.FieldValues[ 'rprice '];
ExcelWorksheet1.Cells.Item[idx,8]:=ADOQuery1.FieldValues[ 'minstock '];
ExcelWorksheet1.Cells.Item[idx,9]:=ADOQuery1.FieldValues[ 'unpaid '];
ADOQuery1.Next;
idx:=idx+1;
end;
ExcelWorksheet1.Cells.Item[idx,1]:= '合计: ';
ExcelWorksheet1.Cells.Item[idx,5]:= '=SUM(E7:E '+IntToStr(idx-1)+ ') ';
end;
关闭时用
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
------解决方案--------------------
