利用delphi打开excel文件 同时进行写入数据!(100分)

  • 主题发起人 主题发起人 燕狂徒
  • 开始时间 开始时间

燕狂徒

Unregistered / Unconfirmed
GUEST, unregistred user!
本人做了一个程序,但是从远程数据库获得数据要导入的一个格式已经固定excel表格中,<br>因为表格中有计算公式!这样就不用编写复杂的代码!求救如何打开excel,文件并对特定的单元格送入数据!<br><br>
 
最好提供源代码!分数不够我再加!
 
&nbsp; try<br>&nbsp; &nbsp; &nbsp; try<br>&nbsp; &nbsp; &nbsp; &nbsp; ExcelApplication.Connect;<br>&nbsp; &nbsp; &nbsp; except<br>&nbsp; &nbsp; &nbsp; &nbsp; <br>&nbsp; &nbsp; &nbsp; &nbsp; Exit;<br>&nbsp; &nbsp; &nbsp; end;<br>&nbsp; &nbsp; &nbsp; try<br>&nbsp; &nbsp; &nbsp; &nbsp; ExcelWorkbook.ConnectTo(ExcelApplication.Workbooks.Open<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (ExtractFilePath(Application.ExeName)+'WeekReportFormat.xls', EmptyParam,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, 0));<br>&nbsp; &nbsp; &nbsp; &nbsp; ExcelWorksheet.ConnectTo(ExcelWorkbook.Sheets[1] as _WorkSheet);<br>&nbsp; &nbsp; &nbsp; except<br>Exit;<br>&nbsp; &nbsp; &nbsp; end;<br>&nbsp; &nbsp; &nbsp; try<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;for i := 1 to 10 do<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;for j := 1 to 10 do<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ExcelWorksheet.Cells.Item[i, j] := i*j<br>&nbsp; &nbsp; &nbsp; &nbsp; ExcelWorksheet.Name := 'C:/1.xls';<br>&nbsp; &nbsp; &nbsp; &nbsp; ExcelWorksheet.SaveAs(sFileName, EmptyParam, EmptyParam, EmptyParam,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, 0);<br>&nbsp; &nbsp; &nbsp; exceptend;<br>&nbsp; &nbsp; finally<br>&nbsp; &nbsp; &nbsp; ExcelWorkbook.Close(false);<br>&nbsp; &nbsp; &nbsp; ExcelApplication.Disconnect;<br>&nbsp; &nbsp; end;<br>&nbsp; end<br><br><br><br>大概就是这样
 
要先放上excel控件<br><br>
 
ExcelWorkbook.ConnectTo(ExcelApplication.Workbooks.Open<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (ExtractFilePath(Application.ExeName)+'WeekReportFormat.xls', EmptyParam,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, 0));<br>&nbsp; &nbsp; &nbsp; &nbsp; ExcelWorksheet.ConnectTo(ExcelWorkbook.Sheets[1] as _WorkSheet);<br>能详细解释一下上面代码的意思马??谢谢!
 
我已前写的,找出来给你.<br><br><br>unit WjUtilsExcel;<br><br>interface<br><br>uses<br>&nbsp; DB, SysUtils;<br><br>const<br>&nbsp; AllFields: string = '[ALL]';<br>&nbsp; VisibleFields: string = '[VISIBLE]';<br><br>type<br>&nbsp; TExportDataSetFlag = (ifWriteColumnTitle, ifMoveActiveCell, ifUseFieldDisplayText, ifFormatAsString);<br>&nbsp; TExportDataSetFlags = set of TExportDataSetFlag;<br><br>&nbsp; IExcelUtils = interface(IUnknown)<br>&nbsp; &nbsp; ['{9DAAE4C0-5835-4A2E-81CD-FF8998AE9A47}']<br>&nbsp; &nbsp; function Server: OleVariant;<br>&nbsp; &nbsp; function Save: boolean;<br>&nbsp; &nbsp; function ExportDataSet(ADataSet: TDataSet; const AFields: string = ''; ARecordCount: integer = -1;<br>&nbsp; &nbsp; &nbsp; AExportFlags: TExportDataSetFlags = []): integer;<br>&nbsp; &nbsp; function PromptForWorkbookFileName: string;<br>&nbsp; end;<br><br>&nbsp; TExcelServer = class(TInterfacedObject, IExcelUtils)<br>&nbsp; protected<br>&nbsp; &nbsp; FServer: OleVariant;<br>&nbsp; public<br>&nbsp; &nbsp; constructor Create(AIsCreateServerNow: boolean = false);<br>&nbsp; &nbsp; destructor Destroy; override;<br>&nbsp; protected<br>&nbsp; &nbsp; { IExcelUtils implementation }<br>&nbsp; &nbsp; function Server: OleVariant;<br>&nbsp; &nbsp; function Save: boolean;<br>&nbsp; &nbsp; function ExportDataSet(ADataSet: TDataSet; const AFields: string; ARecordCount: integer;<br>&nbsp; &nbsp; &nbsp; AExportFlags: TExportDataSetFlags): integer;<br>&nbsp; &nbsp; function PromptForWorkbookFileName: string;<br>&nbsp; end;<br><br>implementation<br><br>uses Variants, ComObj, Dialogs, Forms, Controls, Classes;<br><br>{ TExcelServer }<br><br>constructor TExcelServer.Create(AIsCreateServerNow: boolean = false);<br>begin<br>&nbsp; if AIsCreateServerNow then Server;<br>end;<br><br>destructor TExcelServer.Destroy;<br>begin<br>&nbsp; if VarType(FServer) = varDispatch then<br>&nbsp; begin<br>&nbsp; &nbsp; FServer.Quit;<br>&nbsp; &nbsp; FServer := Null;<br>&nbsp; end;<br>&nbsp; inherited;<br>end;<br><br>function TExcelServer.ExportDataSet(ADataSet: TDataSet; const AFields: string; ARecordCount: integer;<br>&nbsp; AExportFlags: TExportDataSetFlags): integer;<br>var<br>&nbsp; i, RowOffset: integer;<br>&nbsp; PrevCursor: TCursor;<br>&nbsp; Fields: TList;<br>begin<br>&nbsp; Result := -1;<br>&nbsp; Fields := TList.Create;<br>&nbsp; try<br>&nbsp; &nbsp; if UpperCase(AFields) = ALLFields then<br>&nbsp; &nbsp; &nbsp; for i := 0 to ADataSet.FieldCount - 1 do Fields.Add(ADataSet.Fields)<br>&nbsp; &nbsp; else if (AFields = '') or (UpperCase(AFields) = VisibleFields) then<br>&nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; for i := 0 to ADataSet.FieldCount - 1 do<br>&nbsp; &nbsp; &nbsp; &nbsp; if ADataSet.Fields.Visible then Fields.Add(ADataSet.Fields)<br>&nbsp; &nbsp; end<br>&nbsp; &nbsp; else<br>&nbsp; &nbsp; &nbsp; ADataSet.GetFieldList(Fields, AFields);<br><br>&nbsp; &nbsp; if Fields.Count &gt; 0 then<br>&nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; PrevCursor := Screen.Cursor;<br>&nbsp; &nbsp; &nbsp; try<br>&nbsp; &nbsp; &nbsp; &nbsp; Result := 0;<br>&nbsp; &nbsp; &nbsp; &nbsp; RowOffset := 0;<br>&nbsp; &nbsp; &nbsp; &nbsp; if ifWriteColumnTitle in AExportFlags then<br>&nbsp; &nbsp; &nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for i := 0 to Fields.Count - 1 do<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Server.ActiveCell.Offset[0, i].Value := TField(Fields.Items).DisplayLabel;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RowOffset := 1;<br>&nbsp; &nbsp; &nbsp; &nbsp; end;<br><br>&nbsp; &nbsp; &nbsp; &nbsp; while ((ARecordCount &lt; 0) or (Result &lt; ARecordCount)) and (not ADataSet.Eof) do<br>&nbsp; &nbsp; &nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for i := 0 to Fields.Count - 1 do<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if ifFormatAsString in AExportFlags then<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Server.ActiveCell.Offset[Result + RowOffset, i].NumberFormatLocal := '@';<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if ifUseFieldDisplayText in AExportFlags then<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Server.ActiveCell.Offset[Result + RowOffset, i].Value := TField(Fields.Items).DisplayText<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Server.ActiveCell.Offset[Result + RowOffset, i].Value := TField(Fields.Items).Value;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Inc(Result);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if ARecordCount &lt;&gt; 1 then ADataSet.Next; //如只导入一条记录,则不移动记录指针<br>&nbsp; &nbsp; &nbsp; &nbsp; end;<br><br>&nbsp; &nbsp; &nbsp; &nbsp; if ifMoveActiveCell in AExportFlags then<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Server.ActiveCell.Offset[Result + RowOffset].Activate;<br>&nbsp; &nbsp; &nbsp; finally<br>&nbsp; &nbsp; &nbsp; &nbsp; Screen.Cursor := PrevCursor;<br>&nbsp; &nbsp; &nbsp; end;<br>&nbsp; &nbsp; end;<br>&nbsp; finally<br>&nbsp; &nbsp; Fields.Free;<br>&nbsp; end;<br>end;<br><br>function TExcelServer.PromptForWorkbookFileName: string;<br>begin<br>&nbsp; Result := '';<br>&nbsp; PromptForFileName(Result, 'Microsoft Excel 文件(*.xls)|*.xls', 'xls', '另存为', '', true);<br>end;<br><br>function TExcelServer.Save: boolean;<br>var<br>&nbsp; Workbook: OleVariant;<br>&nbsp; FileName: string;<br>begin<br>&nbsp; Result := false;<br>&nbsp; Workbook := Server.ActiveWorkbook;<br>&nbsp; if TVarData(Workbook).VDispatch = nil then<br>&nbsp; &nbsp; Raise Exception.Create('None of workbooks is actived.');<br>&nbsp; if Workbook.Path &lt;&gt; '' then<br>&nbsp; begin<br>&nbsp; &nbsp; Workbook.Save;<br>&nbsp; &nbsp; Result := true;<br>&nbsp; end<br>&nbsp; else<br>&nbsp; begin<br>&nbsp; &nbsp; FileName := PromptForWorkbookFileName;<br>&nbsp; &nbsp; if FileName &lt;&gt; '' then<br>&nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; Workbook.SaveAs(FileName);<br>&nbsp; &nbsp; &nbsp; Result := true;<br>&nbsp; &nbsp; end;<br>&nbsp; end;<br>end;<br><br>function TExcelServer.Server: OleVariant;<br>var<br>&nbsp; PrevCursor: TCursor;<br>begin<br>&nbsp; if VarType(FServer) = varEmpty then<br>&nbsp; begin<br>&nbsp; &nbsp; PrevCursor := Screen.Cursor;<br>&nbsp; &nbsp; try<br>&nbsp; &nbsp; &nbsp; Screen.Cursor := crHourGlass;<br>&nbsp; &nbsp; &nbsp; FServer := CreateOleObject('Excel.Application');<br>&nbsp; &nbsp; &nbsp; FServer.DisplayAlerts := false;<br>&nbsp; &nbsp; finally<br>&nbsp; &nbsp; &nbsp; Screen.Cursor := PrevCursor;<br>&nbsp; &nbsp; end;<br>&nbsp; end;<br>&nbsp; Result := FServer;<br>end;<br><br>end.<br>
 
ExcelWorkbook.ConnectTo(ExcelApplication.Workbooks.Open<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(ExtractFilePath(Application.ExeName)+'WeekReportFormat.xls', EmptyParam,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, 0));<br><br>打開在exe文件目錄下的'WeekReportFormat.xls文件<br><br>最好自己找VBAXL9.CHM看看,在offi的目錄下面<br>這個是關與excel的操作
 
[h2][red]下面是轉載的[/red][/h2]<br>可以學習學習<br><br>全面控制 Excel<br>首先创建 Excel 对象,使用ComObj:<br>var ExcelID: Variant;<br>&nbsp;ExcelID := CreateOleObject( 'Excel.Application' );<br>1) 显示当前窗口:<br>ExcelID.Visible := True;<br>2) 更改 Excel 标题栏:<br>ExcelID.Caption := '应用程序调用 Microsoft Excel';<br>3) 添加新工作簿:<br>&nbsp; ExcelID.WorkBooks.Add;<br>4) 打开已存在的工作簿:<br>&nbsp; ExcelID.WorkBooks.Open( 'C:/Excel/Demo.xls' );<br>5) 设置第2个工作表为活动工作表:<br>&nbsp; ExcelID.WorkSheets[2].Activate; &nbsp;<br>&nbsp;或 ExcelID.WorkSheets[ 'Sheet2' ].Activate;<br>6) 给单元格赋值:<br>&nbsp;ExcelID.Cells[1,4].Value := '第一行第四列';<br>7) 设置指定列的宽度(单位:字符个数),以第一列为例:<br>&nbsp;ExcelID.ActiveSheet.Columns[1].ColumnsWidth := 5;<br>8) 设置指定行的高度(单位:磅)(1磅=0.035厘米),以第二行为例:<br>&nbsp; ExcelID.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米<br>9) 在第8行之前插入分页符:<br>&nbsp; ExcelID.WorkSheets[1].Rows[8].PageBreak := 1;<br>10) 在第8列之前删除分页符:<br>&nbsp; ExcelID.ActiveSheet.Columns[4].PageBreak := 0;<br>11) 指定边框线宽度:<br>&nbsp;ExcelID.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;<br>&nbsp; &nbsp;1-左 &nbsp; &nbsp;2-右 &nbsp; 3-顶 &nbsp; &nbsp;4-底 &nbsp; 5-斜( / ) &nbsp; &nbsp; 6-斜( / )<br>12) 清除第一行第四列单元格公式:<br>&nbsp;ExcelID.ActiveSheet.Cells[1,4].ClearContents;<br>13) 设置第一行字体属性:<br>ExcelID.ActiveSheet.Rows[1].Font.Name := '隶书';<br>ExcelID.ActiveSheet.Rows[1].Font.Color &nbsp;:= clBlue;<br>ExcelID.ActiveSheet.Rows[1].Font.Bold &nbsp; := True;<br>ExcelID.ActiveSheet.Rows[1].Font.UnderLine := True;<br>14) 进行页面设置:<br>&nbsp; a.页眉:<br>&nbsp; &nbsp; ExcelID.ActiveSheet.PageSetup.CenterHeader := '报表演示';<br>&nbsp; b.页脚:<br>&nbsp; &nbsp; ExcelID.ActiveSheet.PageSetup.CenterFooter := '第&amp;P页';<br>&nbsp; c.页眉到顶端边距2cm:<br>&nbsp; &nbsp; ExcelID.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;<br>&nbsp; d.页脚到底端边距3cm:<br>&nbsp; &nbsp; ExcelID.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;<br>&nbsp; e.顶边距2cm:<br>&nbsp; &nbsp; ExcelID.ActiveSheet.PageSetup.TopMargin := 2/0.035;<br>&nbsp; f.底边距2cm:<br>&nbsp; &nbsp; ExcelID.ActiveSheet.PageSetup.BottomMargin := 2/0.035;<br>&nbsp; g.左边距2cm:<br>&nbsp; &nbsp; ExcelID.ActiveSheet.PageSetup.LeftMargin := 2/0.035;<br>&nbsp; h.右边距2cm:<br>&nbsp; &nbsp; ExcelID.ActiveSheet.PageSetup.RightMargin := 2/0.035;<br>&nbsp; i.页面水平居中:<br>&nbsp; &nbsp; ExcelID.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;<br>&nbsp; j.页面垂直居中:<br>&nbsp; &nbsp; ExcelID.ActiveSheet.PageSetup.CenterVertically := 2/0.035;<br>&nbsp; k.打印单元格网线:<br>&nbsp; &nbsp; ExcelID.ActiveSheet.PageSetup.PrintGridLines := True;<br>15) 拷贝操作:<br>&nbsp; a.拷贝整个工作表:<br>&nbsp; &nbsp; ExcelID.ActiveSheet.Used.Range.Copy;<br>&nbsp; b.拷贝指定区域:<br>&nbsp; &nbsp; ExcelID.ActiveSheet.Range[ 'A1:E2' ].Copy;<br>&nbsp; c.从A1位置开始粘贴:<br>&nbsp; &nbsp; ExcelID.ActiveSheet.Range.[ 'A1' ].PasteSpecial;<br>&nbsp; d.从文件尾部开始粘贴:<br>&nbsp; &nbsp; ExcelID.ActiveSheet.Range.PasteSpecial;<br>&nbsp;16) 插入一行或一列:<br>&nbsp; &nbsp;a. ExcelID.ActiveSheet.Rows[2].Insert;<br>&nbsp; &nbsp;b. ExcelID.ActiveSheet.Columns[1].Insert;<br>&nbsp;17) 删除一行或一列:<br>&nbsp; a. ExcelID.ActiveSheet.Rows[2].Delete;<br>&nbsp; b. ExcelID.ActiveSheet.Columns[1].Delete;<br>&nbsp;18) 打印预览工作表:<br>&nbsp; ExcelID.ActiveSheet.PrintPreview;<br>&nbsp;19) 打印输出工作表:<br>&nbsp; ExcelID.ActiveSheet.PrintOut;<br>&nbsp;20) 工作表保存:<br>&nbsp;if not ExcelID.ActiveWorkBook.Saved then<br>&nbsp; &nbsp;ExcelID.ActiveSheet.PrintPreview;<br>&nbsp;21) 工作表另存为:<br>&nbsp; ExcelID.SaveAs( 'C:/Excel/Demo1.xls' );<br>&nbsp;22) 放弃存盘:<br>&nbsp;ExcelID.ActiveWorkBook.Saved := True;<br>&nbsp;23) 关闭工作簿:<br>&nbsp;ExcelID.WorkBooks.Close;<br>&nbsp;24) 退出 Excel:<br>ExcelID.Quit;<br><br>如何在Delphi中操作Excel<br>在数据库应用软件的开发过程中,经常需要把数据转换成Excel文件,让用户作进一步的数据处理。而Delphi是开发数据库应用系统的常用工具,那么,如何在Delphi中操作Excel呢? <br>我们知道,在Microsoft Office软件中有一种内嵌的编程语言VBA,它是一种宏语言,利用它,你可以编写出功能强大的代码,如打开文件、修改数据、保存数据和设置字体等。另一方<br>面,Microsoft Office软件中的宏能以VBA代码的形式记录下你的操作过程。因此借助宏操作,可以很轻松地实现某一功能,并把这些代码稍作修改嵌入到你的软件中。但是VBA也存在<br>一个缺点,它必须有Microsoft Office作平台,在哪里编写,必须在哪里执行。例如在Excel下编写的一段VBA代码,则它只有在Excel下才能运行。因此,笔者的方法就是把VBA代码嵌入<br>到Delphi中,从而实现用Delphi操作Excel。 <br>  下面,我们以Delphi程序为例,说明这种调用方法: <br>  Unit excel; <br>  interface <br>  uses <br>  Windows,Messages,SysUtils,Classes,Graphics,Controls,Forms,Dialogs,StdCtrls,ComObj; <br>  //ComObj是操作OLE对象的函数集 <br>  type TForm1 =class(TForm) <br>  Button1: TButton; <br>  procedure Button1Click(Sender: TObject); <br>  private <br>  { Private declarations } <br>  public <br>  { Public declarations } <br>  end; <br>  var <br>  Form1: TForm1; <br>  implementation <br>  {$R *.DFM} <br>  procedure TForm1.Button1Click(Sender: TObject); <br>  var <br>  eclApp,WorkBook:Variant; <br>  //声明为OLE Automation 对象 <br>  xlsFileName:string;begin <br>  xlsFileName:='ex.xls'; <br>  try <br>  //创建OLE对象Excel Application与 WorkBook <br>  eclApp:=CreateOleObject('Excel.Application'); <br>  WorkBook:=CreateOleobject('Excel.Sheet'); <br>  except <br>  ShowMessage('您的机器里未安装Microsoft Excel。'); <br>  Exit; <br>  end; <br>  try <br>  ShowMessage('下面演示:新建一个XLS文件,并写入数据,最后关闭它。'); <br>  workBook:=eclApp.workBooks.Add; <br>  eclApp.Cells(1 , 1):='字符型'; <br>  eclApp.Cells(2 , 1):='Excel文件'; <br>  eclApp.Cells(1 , 2):='Money型'; <br>  eclApp.Cells(2 , 2):=10.01; <br>  eclApp.Cells(1 , 3):='日期型';eclApp.Cells(2 , 3):=Date; <br>  WorkBook.saveas(xlsFileName); <br>  WorkBook.close; <br>  ShowMessage('下面演示:打开刚创建的XLS文件,并修改其中的内容,然后,由用户决定是否保存。'); <br>  WorkBook:=eclApp.workBooks.Open(xlsFileName); <br>  eclApp.Cells(2 , 1):='Excel文件类型'; <br>  if MessageDlg(xlsFileName+'文件已被修改,是否保存?',mtConfirmation, [mbYes, mbNo], 0) = mrYes then <br>  WorkBook.save <br>  else <br>  workBook.Saved := True; //放弃修改 <br>  WorkBook.Close; <br>  eclApp.Quit; <br>  //退出Excel Application <br>  //释放VARIANT变量 <br>  eclApp:=Unassigned; <br>  except <br>  ShowMessage('不能正确操作Excel文件。可能是该文件已被其他程序打开,或系统错误。'); <br>  WorkBook.close; <br>  eclApp.Quit; <br>  //释放VARIANT变量 <br>  eclApp:=Unassigned; <br>  end; <br>  end; <br>  end. <br>  经过上述操作过程后,我们就可以很放心地将数据库中的数据转换成Excel文件了。<br>///////2<br>在Delphi 5中简单地封装了一组Microsoft Office自动化对象(Automation servers)。<br>它使得我们很容易地把Office中的应用程序(Word, Excel, PowerPoint, Outlook and<br>&nbsp;Access等)当作一个com应用服务器进行控制。在Delphi 5中已经带了Word与PowerPoint<br>的例子,因为Excel的调用与这两个应用服务器的调用略有不同,所以本人根据这两个例子<br>写了个Excel 97的简单例子以供参考。 <br>步聚 <br>创建一个普通Application。<br>在Form中分别放入ExcelApplication, ExcelWorkbook和ExcelWorksheet。<br>连接Excel 97,具体方法如下: <br>打开Excel97。<br>Try<br>&nbsp; &nbsp; &nbsp;ExcelApplication1.Connect;<br>&nbsp; &nbsp; &nbsp;Except<br>&nbsp; &nbsp; &nbsp;End;<br>&nbsp; &nbsp; &nbsp;ExcelApplication1.Visible[0]:=True;<br>增加一个Workbook。<br>ExcelWorkbook1.ConnectTo(ExcelApplication1.<br>Workbooks.Add(EmptyParam,0));<br>添加一个Worksheet。<br>&nbsp;var<br>&nbsp; Temp_Worksheet: _WorkSheet;<br>&nbsp; begin<br>&nbsp; Try<br>&nbsp; Temp_Worksheet:=ExcelWorkbook1.<br>&nbsp; WorkSheets.Add(EmptyParam,<br>EmptyParam,EmptyParam,EmptyParam,0) <br>as _WorkSheet;//(注意)<br>ExcelWorkSheet1.ConnectTo(Temp_WorkSheet);<br>Except<br>ShowMessage('Failure');<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;End;<br>&nbsp; &nbsp; &nbsp; &nbsp; end;<br>关闭Excel.<br>&nbsp;Try<br>&nbsp;ExcelApplication1.Quit;<br>&nbsp;ExcelWorksheet1.Disconnect;<br>&nbsp;ExcelWorkbook1.Disconnect;<br>&nbsp;ExcelApplication1.Disconnect;<br>&nbsp;Except<br>&nbsp;End;<br>对Excel的一些操作: <br>选择当前Workbook的某一Worksheet.<br>procedure TForm1.ComboBox1DropDown<br>(Sender: TObject);<br>var<br>&nbsp; &nbsp;i: Integer;<br>begin<br>&nbsp; &nbsp; &nbsp;ComboBox1.Clear;<br>&nbsp; &nbsp; &nbsp;For i:=1 to ExcelWorkbook1.<br>&nbsp; &nbsp; &nbsp;Worksheets.Count do<br>&nbsp; &nbsp; &nbsp; ComboBox1.Items.Add<br>&nbsp; &nbsp; &nbsp; &nbsp; ((ExcelWorkbook1.Worksheets.Item <br>&nbsp; &nbsp; &nbsp; &nbsp; as _WorkSheet).Name);<br>end;<br>procedure TForm1.ComboBox1Change<br>(Sender: TObject);<br>begin<br>&nbsp; &nbsp; &nbsp;ExcelWorkSheet1.ConnectTo<br>(ExcelWorkbook1.Worksheets.Item<br>&nbsp;[ComboBox1.ItemIndex+1] as _WorkSheet);<br>&nbsp; &nbsp; &nbsp;ExcelWorkSheet1.Activate;<br>end;<br>选择某一Workbook:<br>procedure TForm1.ComboBox2DropDown<br>(Sender: TObject);<br>var<br>&nbsp; &nbsp;i: Integer;<br>begin<br>ComboBox2.Clear;<br>if ExcelApplication1.Workbooks.Count &gt;0 then<br>For i:=1 to ExcelApplication1.Workbooks.Count do<br>Combobox2.Items.Add(ExcelApplication1.<br>Workbooks.Item.Name);<br>end;<br>procedure TForm1.ComboBox2Change(Sender: TObject);<br>begin<br>ExcelWorkSheet1.Disconnect;<br>ExcelWorkBook1.ConnectTo(ExcelApplication1.Workbooks.<br>Item[Combobox2.ItemIndex+1]);<br>&nbsp; &nbsp; &nbsp;ExcelWorkBook1.Activate;<br>&nbsp; &nbsp; &nbsp;ExcelWorksheet1.ConnectTo(ExcelWorkBook1.<br>ActiveSheet as _WorkSheet);<br>&nbsp; &nbsp; &nbsp;ExcelWorkSheet1.Activate;<br>end;<br>对某一单元格进行赋值及取值。<br>procedure TForm1.Button5Click(Sender: TObject);<br>begin<br>&nbsp; &nbsp; &nbsp;ExcelWorksheet1.Cells.Item[SpinEdit2.Value,<br>SpinEdit1.Value]:=Edit1.Text;<br>end;<br>procedure TForm1.Button6Click(Sender: TObject);<br>begin<br>&nbsp; &nbsp; &nbsp;Edit1.Text:=ExcelWorksheet1.Cells.Item[<br>SpinEdit2.Value,SpinEdit1.Value];<br>end;<br>选择某一区域<br>ExcelWorkSheet1.Range['A1','C1'].Select;<br>打开一个Excel文件。<br>&nbsp; &nbsp; &nbsp;if OpenDialog1.Execute then<br>&nbsp; &nbsp; &nbsp; &nbsp; Begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Try<br>ExcelWorkBook1.ConnectTo<br>(ExcelApplication1.Workbooks.Open<br>&nbsp; &nbsp; &nbsp;(OpenDialog1.FileName,<br>EmptyParam,EmptyParam,EmptyParam,<br>EmptyParam,EmptyParam,<br>EmptyParam,EmptyParam,EmptyParam,<br>&nbsp; &nbsp; &nbsp;EmptyParam,EmptyParam,<br>EmptyParam,EmptyParam,0));<br>&nbsp; &nbsp; &nbsp;ExcelWorkSheet1.ConnectTo<br>(ExcelWorkBook1.Activesheet<br>&nbsp; &nbsp; &nbsp; &nbsp;as _Worksheet);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Except;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;End;<br>&nbsp; &nbsp; &nbsp; &nbsp; End;<br>
 
我的,简单实用,供你参考一下:<br>/////////<br>unit XuLib;<br><br>interface<br>uses<br>&nbsp; Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,<br>&nbsp; Dialogs, DB, DBTables, StdCtrls, ExtCtrls, Grids, DBGrids, Excel2000,<br>&nbsp; OleServer, Mask, DBCtrls, ComCtrls;<br><br>////////////////////////////////////////////////////////////////////////////////<br>Procedure DBToExcel<br>( ModuleName : String ; &nbsp;// Excel 模块文件名 *.Xlt<br>&nbsp; FileName: String ; &nbsp;// Excel 目标文件名 *.Xls<br>&nbsp; DBGrid : TDBGrid ; &nbsp;// TDBGrid 控件名<br>&nbsp; Row : Integer ; &nbsp;// 起始行<br>&nbsp; Col : Integer &nbsp; // 起始列<br>) ;<br>////////////////////////////////////////////////////////////////////////////////<br><br>implementation<br><br>////////////////////////////////////////////////////////////////////////////////<br>//函数名 : &nbsp;DBToExcel<br>//<br>// &nbsp;功能 : 将TDBGrid 的数据导入到从某模板文件建立的 Excel 文档中,<br>// &nbsp;举例 : &nbsp;DBToExcel( 'C:/ReportTable.Xlt', 'C:/ReportTable.Xls',<br>// &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DBGrid1, 5, 1 ) ; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //<br>// &nbsp;注意 &nbsp;:所指定的模板文件*.xlt, 必须已存在<br>////////////////////////////////////////////////////////////////////////////////<br>Procedure DBToExcel<br>( ModuleName : String ; &nbsp;// Excel 模块文件名 *.Xlt<br>&nbsp; FileName: String ; &nbsp;// Excel 目标文件名 *.Xls<br>&nbsp; DBGrid : TDBGrid ; &nbsp;// TDBGrid 控件名<br>&nbsp; Row : Integer ; &nbsp;// 起始行<br>&nbsp; Col : Integer &nbsp;// 起始列<br>) ;<br>Var<br>&nbsp; &nbsp; &nbsp; ExlApp : TExcelApplication ;<br>&nbsp; &nbsp; &nbsp; ExlBook : TExcelWorkBook ;<br>&nbsp; &nbsp; &nbsp; ExlSheet : TExcelWorkSheet ;<br>&nbsp; &nbsp; &nbsp; i, j : Integer ;<br>&nbsp; &nbsp; &nbsp; temp1, temp2 : OleVariant ;<br>Begin<br>&nbsp; &nbsp; &nbsp; // 判断指定的模板文件是否存在<br>&nbsp; &nbsp; &nbsp; ModuleName := Trim(ModuleName) ;<br>&nbsp; &nbsp; &nbsp; If FileExists(ModuleName)= False Then<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ShowMessage( '模板文件: '+ ModuleName + ' 不存在 !' ) ;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Exit ;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end ;<br><br>&nbsp; &nbsp; &nbsp; // 创建 Excel 服务器控件<br>&nbsp; &nbsp; &nbsp; ExlApp := TExcelApplication.Create(nil);<br>&nbsp; &nbsp; &nbsp; ExlBook := TExcelWorkBook.Create (nil);<br>&nbsp; &nbsp; &nbsp; ExlSheet := TExcelWorkSheet.Create (nil) ;<br><br>&nbsp; &nbsp; &nbsp; // 连接 Excel 服务器<br>&nbsp; &nbsp; &nbsp; Try<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ExlApp.Connect ;<br>&nbsp; &nbsp; &nbsp; Except<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ShowMessage ('连接失败,可以没装 Excle') ;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Abort ;<br>&nbsp; &nbsp; &nbsp; End ;<br><br>&nbsp; &nbsp; &nbsp; // 从指定的模板文件*.xlt 新建一 Excel 文档<br>&nbsp; &nbsp; &nbsp; temp1 := ModuleName ; temp2 := 1 ;<br>&nbsp; &nbsp; &nbsp; ExlApp.Workbooks.Add( temp1, temp2) ;<br>&nbsp; &nbsp; &nbsp; ExlBook.ConnectTo(ExlApp.Workbooks[1] );<br>&nbsp; &nbsp; &nbsp; ExlSheet.ConnectTo(ExlBook.Worksheets[1] As _WorkSheet );<br><br>&nbsp; &nbsp; &nbsp; // 把 TDBGrid 的数据导入 Excel 中<br>&nbsp; &nbsp; &nbsp; DBGrid.DataSource.DataSet.First ;<br>&nbsp; &nbsp; &nbsp; i := Row ;<br>&nbsp; &nbsp; &nbsp; While Not(DBGrid.DataSource.DataSet.Eof) &nbsp;Do<br>&nbsp; &nbsp; &nbsp; &nbsp; Begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; For j:=Col &nbsp;To DBGrid.FieldCount+ Col-1 &nbsp;Do<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ExlSheet.Cells.Item[i,j]:= DBGrid.Fields[j-col].AsString ;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DBGrid.DataSource.DataSet.Next ;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; i := i+1 ;<br>&nbsp; &nbsp; &nbsp; &nbsp; End ;<br><br>&nbsp; &nbsp; &nbsp; // 保存到 指定的文件<br>&nbsp; &nbsp; &nbsp; FileName := Trim(FileName) ;<br>&nbsp; &nbsp; &nbsp; IF FileExists(FileName) Then DeleteFile(FileName) ;<br>&nbsp; &nbsp; &nbsp; ExlSheet.SaveAs(FileName);<br><br>&nbsp; &nbsp; &nbsp; // 关闭 Excel 服务器<br>&nbsp; &nbsp; &nbsp; ExlApp.Disconnect ;<br>&nbsp; &nbsp; &nbsp; ExlApp.Quit ;<br>&nbsp; ExlApp.Free; &nbsp;ExlBook.Free; &nbsp;ExlSheet.Free;<br><br>End; // End of The Procedure DbToExcel_2() /////////////////////////////////////<br>end.<br><br>
 
to:luky_99<br>呵呵 精华都贴了 <br>楼主 luky_99的东西好 收藏吧<br>
 
谢谢各位了 &nbsp;我的问题解决了![:D][:D]
 

Similar threads

回复
0
查看
1K
不得闲
D
回复
0
查看
2K
DelphiTeacher的专栏
D
D
回复
0
查看
1K
DelphiTeacher的专栏
D
后退
顶部