创建一个Excel OLE COM 对象,然后操作Excel的工作簿对象即可 var MSExcel:Variant; MSExcel:=CreateOLEObject('Excel.Application'); 操作: MSExcel.WorkBooks
代码如下: var ExcelApp:Variant; begin ExcelApp:=CreateOleObject('Excel.Application'); ExcelApp.Visible:=true; ExcelApp.WorkBooks.Open('C:\文件名.xls'); ExcelApp.Cells[3,1].Value:='三行一列'; ShowMessage(ExcelApp.Cells[3,1].Value); end
procedure Tshezhi.Button2Click(Sender: TObject); var msexcel:variant; i:integer; begin OpenDialog1.Filter:='*.XLS|*.XLS'; OpenDialog1.DefaultExt:='XLS'; if OpenDialog1.Execute then try begin msexcel:=createoleobject('excel.application'); msexcel.workbooks.open(opendialog1.FileName); MSExcel.Visible:=False; for i:=2 to MSExcel.ActiveSheet.UsedRange.Rows.Count do begin with DataM.susheTable1 do begin insert; fieldbyname('字段1').AsString:=MSExcel.Cells[i,1].Value; fieldbyname('字段2').AsString:=MSExcel.Cells[i,2].Value; post; end; end; MSExcel.ActiveWorkBook.Close; MSExcel.Quit; showmessage('数据导入成功!'); end except showmessage('操作失败!'); end; end;
var MSExcel:Variant;
MSExcel:=CreateOLEObject('Excel.Application');
操作: MSExcel.WorkBooks
var
ExcelApp:Variant;
begin
ExcelApp:=CreateOleObject('Excel.Application');
ExcelApp.Visible:=true;
ExcelApp.WorkBooks.Open('C:\文件名.xls');
ExcelApp.Cells[3,1].Value:='三行一列';
ShowMessage(ExcelApp.Cells[3,1].Value);
end
ADOConnection1.ConnectionString := 'DRIVER={Microsoft EXCEL Driver (*.xls)};DBQ='+Excel文件的路径+';Extended Properties=EXCEL 8.0';再ADOQuery1.SQL.Add('select * from [Sheet1$]');然后再写入DB
var
msexcel:variant;
i:integer;
begin
OpenDialog1.Filter:='*.XLS|*.XLS';
OpenDialog1.DefaultExt:='XLS';
if OpenDialog1.Execute then
try begin
msexcel:=createoleobject('excel.application');
msexcel.workbooks.open(opendialog1.FileName);
MSExcel.Visible:=False; for i:=2 to MSExcel.ActiveSheet.UsedRange.Rows.Count do
begin
with DataM.susheTable1 do
begin
insert;
fieldbyname('字段1').AsString:=MSExcel.Cells[i,1].Value;
fieldbyname('字段2').AsString:=MSExcel.Cells[i,2].Value;
post;
end;
end;
MSExcel.ActiveWorkBook.Close;
MSExcel.Quit;
showmessage('数据导入成功!');
end
except
showmessage('操作失败!');
end;
end;
其中用到 OFFICE2000和EXCEL2000单元(DELPHI自带的)
procedure TexpbankdataForm.BitBtn3Click(Sender: TObject);
var
systime :SYSTEMTIME;
filedir :string;
// eclApp,WorkBook :Variant;
//声明为OLE Automation 对象
xlsFileName :string; //EXCEL文件名
i,j:integer;
name,bankzh,totalsf :string; ExcelApplication1: TExcelApplication;
ExcelWorksheet1: TExcelWorksheet;
ExcelWorkbook1: TExcelWorkbook;
//声明EXCEL应用,工作表,工作表中的页三个对象begin
Screen.Cursor := waitcur;
mainForm.mainTimer.Enabled := False; try
bankexpDBSL.Active := False;
mainDM.makebankdataOSP.ParamByName('DT').AsString := FormatDateTime('YYYYMMDD:HHMMSS',Now());
mainDM.makebankdataOSP.Prepare();
mainDM.makebankdataOSP.ExecProc();
mainDM.makebankdataOSP.UnPrepare();
mainDM.bankexpOT.Refresh();
bankexpDBSL.Active := True;
totalL.Caption := '汇总:(总水费 ' + FloatToStr(bankexpDBSL.SumCollection.Items[0].SumValue);
totalL.Caption := totalL.Caption + ' 其中 应收水费 ' + FloatToStr(bankexpDBSL.SumCollection.Items[1].SumValue);
totalL.Caption := totalL.Caption + ' 排放费 ' + FloatToStr(bankexpDBSL.SumCollection.Items[2].SumValue);
totalL.Caption := totalL.Caption + ' 卫生费 ' + FloatToStr(bankexpDBSL.SumCollection.Items[3].SumValue);
totalL.Caption := totalL.Caption + ' 户数(用户) ' + FloatToStr(bankexpDBSL.SumCollection.Items[4].SumValue) + ')';
except
ShowMessage('执行过程失败!!!');
Screen.Cursor := defaultcur;
mainForm.mainTimer.Enabled := true;
Exit;
end;//以上从水费中形成银行数据
if (mainDM.bankexpOT.IsEmpty()) then
begin
ShowMessage('没有形成数据!!');
Screen.Cursor := defaultcur;
mainForm.mainTimer.Enabled := true;
Exit;
end;
//filenameEdit.Text := ; bankexpSQ.Close();
bankexpSQ.Open();
if (bankexpSQ.IsEmpty()) then
begin
ShowMessage('没有形成数据!!');
Screen.Cursor := defaultcur;
mainForm.mainTimer.Enabled := true;
Exit;
end;
//生成数据,多个帐号生成一条 GetSystemTime(systime);
filenameEdit.Text := '银联' + IntToStr(systime.wYear)
+ IntToStr(systime.wMonth)
+ IntToStr(systime.wDay)
+ '-'
+ IntToStr(systime.wHour)
+ IntToStr(systime.wMinute)
+ IntToStr(systime.wSecond);
// + IntToStr(systime.wMilliseconds);
filedir := ExtractFilePath(Application.ExeName) + 'data\';
// ShowMessage(filedir);
xlsFileName := filedir + filenameEdit.Text; //全路径文件名
//以上代码将EXCEL文件放在软件可执行目录的DATA目录下,文件名以当前时间为准,不会重名
try
ExcelApplication1 := TExcelApplication.Create(Application);
except
ShowMessage('没有安装EXCEL2000!!!');
Screen.Cursor := defaultcur;
mainForm.mainTimer.Enabled := true;
exit;
end;
try
ExcelWorksheet1 := TExcelWorksheet.Create(Application);
except
ExcelApplication1.Free;
ShowMessage('没有安装EXCEL2000!!!');
Screen.Cursor := defaultcur;
mainForm.mainTimer.Enabled := true;
exit;
end;
try
ExcelWorkbook1 := TExcelWorkbook.Create(Application);
except
ExcelApplication1.Free;
ExcelWorksheet1.Free;
Screen.Cursor := defaultcur;
mainForm.mainTimer.Enabled := true;
ShowMessage('没有安装EXCEL2000!!!');
exit;
end; try
ExcelApplication1.Connect;
except
ExcelApplication1.Quit;
ExcelApplication1.Free;
ExcelWorksheet1.Free;
ExcelWorkbook1.Free;
ShowMessage('没有安装EXCEL2000!!!');
Screen.Cursor := defaultcur;
mainForm.mainTimer.Enabled := true;
exit;
end;
//以上4个异常结构分别创建EXCELAPP,WORKBOOK,WORKSHEET对象并连接EXCEL应用程序 //检测系统是否装有EXCEL
// i := 1;
j := 1;
ExcelApplication1.workbooks.Add(EmptyParam,0);
//加一页,利用EXCELAPP的WORKBOOKS属性的Add方法
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _worksheet);
//以上两句使工作表连接到应用程序的工作中,和作工作表单连接到工作表的第一个表单中
i := bankexpSQ.RecordCount;
totalL.Caption := totalL.Caption + ' 户数(帐号) ' + IntToStr(i) + ')';
bankexpSQ.First();
while (j <= i) do
begin
name := '无';
//mainDM.bankexpOT.FieldByName('kh_name').AsString;
// bankzh := mainDM.bankexpOT.FieldByName('bank_zh').AsString;
bankzh := bankexpSQ.FieldByName('bank_zh').AsString;
totalsf := bankexpSQ.FieldByName('total_sf').AsString;
// ExcelWorksheet1.Cells.item[j,1].NumberFormat := '0';
ExcelWorksheet1.Cells.item[j,1].HorizontalAlignment := xlHAlignRight;
ExcelWorksheet1.Cells.item[j,1] := '''' + bankzh;
ExcelWorksheet1.Cells.item[j,2] := totalsf;
ExcelWorksheet1.Cells.item[j,3] := name;
// eclApp.Cells(j,4) := systemno;
if (j = i) then break;
bankexpSQ.Next();
inc(j);
end;
//循环,取数据库中表的相应字段值,写入EXCEL中,按行列 1,1 1,2 1,3写
// WorkBook.saveas(xlsFileName);
// WorkBook.Close();
try
ExcelWorksheet1.SaveAs(xlsFileName);
except
ShowMessage('不能正确保存Excel文件。可能是该文件已被其他程序打开, 或系统错误。');
Screen.Cursor := defaultcur;
mainForm.mainTimer.Enabled := True;
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
ExcelApplication1.Free;
ExcelWorksheet1.Free;
ExcelWorkbook1.Free;
exit;
end;
Screen.Cursor := defaultcur;
mainForm.mainTimer.Enabled := True;
mainDM.bankparamsOT.Edit();
mainDM.bankparamsOT.FindField('dzflage').AsString := '1';//设置已发送标志
mainDM.bankparamsOT.Post();
ShowMessage('成功生成Excel文件。文件保存在:' + xlsFileName + '.xls');
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
ExcelApplication1.Free;
ExcelWorksheet1.Free;
ExcelWorkbook1.Free;end;