怎样用Delphi读取电子表格文件

new_power 2000-01-21 06:07:00
我想知道具体的方法,有谁能帮我一下!
...全文
755 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
blaise 2000-01-25
  • 打赏
  • 举报
回复
good article
kola 2000-01-25
  • 打赏
  • 举报
回复
我最近刚好做了一个将Word,Excel文件转换成BMP文件的程序!搞得很头疼!耗费
内存太多,速度太慢!很不理想!不过碰到这样的客户也没办法!:(
基本上使用下面方法就可以做你想做的事了:
...
V:variant;
...
try
V:=CreateOleObject('Excel.Application');
except
on EOleException do
begin
v.Quit;
exit;
end;
end;

try
V.application.WorkBooks.Open('D:\1.xls');
except
on EOleException do
begin
v.Quit;
exit;
end;
end;
光明山人 2000-01-25
  • 打赏
  • 举报
回复
你看看这篇文章吧,可能不仅解决你的问题,对自动化理解也有帮助。
抱歉,翻译太累了!

Delphi 3 and Automation with Excel.

Automation allows one application to control another application. The application being controlled is called an automation server (in our case Excel). The application controlling the server is called an automation controller.

There are two ways that automation servers can be accessed:

Late Binding (IDispatch interface)

When using this method, function names and parameter datatypes are resolved at runtime, all parameters are passed as variants.

As no errors in function names or parameter types are reported at compile time, this method is error prone.

As function names and parameter types need to be looked up at runtime, performance is slow.

The only advantage of this method for Delphi programming is that it is the only way optional parameters can be omitted from function calls.

Early Binding (Using type libraries/interfaces)

When using this method, function names and parameter datatypes are all resolved at compile time.

A type library needs to be imported into Delphi. A type library is a language neutral description of all the objects and functions exposed by a server. (This is similar in nature to a C header file).

All parameters need to be supplied, even when calling functions where the documentation states that some are optional. This enables many errors to be detected and corrected before ever running a program.

Performance is better than for late binding.

Due to the advantages of the second approach, the rest of the document demonstrates the basics of creating an application with early binding. All applications that use Excel Automation should use this technique unless there is a strong justification for not doing so.

Preparing the Type Library.

A pascal unit needs to be created from the type library file.

Select Project and Import Type Library menu item.

Press the add button and select the following file

c:\program files\microsoft office\office\excel8.olb

Select OK.

Unfortunately, this leaves the project in a state that will not compile, this is because the excel_tlb unit redefines the word application to mean something else.

The easiest way to resolve this is to remove the excel_tlb unit from the project and only add it to the uses clause of the units that perform automation.

Documentation

The help file c:\program files\microsoft office\office\vbaxl8.hlp contains information about the objects available for excel.

The macro recorder allows VBA code to be created. This can then be taken and translated into Delphi quite easily.

Automation Example

Download code

The following code example demonstrates the creation of a simple spreadsheet and population of it with data. Ensure that the unit excel_tlb is included within the module.

It is strongly recommended that automation code is kept within a separate module as this will prevent problems caused by name clashes.

Unit sheet;
//--------------------------------------------------------------------
interface
//--------------------------------------------------------------------
uses
windows, sysutils, excel_tlb;

Procedure CreateSpreadsheet;
//--------------------------------------------------------------------
implementation
//--------------------------------------------------------------------
Procedure CreateSpreadsheet(filename : string);
var
xla : _Application;
xlw : _Workbook;
LCID : integer;
begin
xla := CoApplication.Create;
LCID := GetUserDefaultLCID;
try
xla.Visible[LCID] := true;
// blank workbook
//xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
// new workbook based on template
xlw := xla.Workbooks.Add('c:\delphi\excel\sample\demo.xlt',LCID);
xla.Range['A1', 'A1'].Value := 'Date';
xla.Cells[1, 2].Value := FormatDateTime('dd-mmm-yyyy', Now);
xla.Cells[3, 1].Value := 'Numbers';
xla.Range['B3', 'E3'].Value := VarArrayOf([1, 10, 100, 1000]);
xla.Range['F3', 'F3'].Formula := '=Sum(B3:E3)';
OLEVariant(xla).Run('Demo', FormatDateTime('dd-mmm-yyyy', Now));
xlw.SaveAs(filename,xlWorkbookNormal,'','',False,False,xlNoChange,
xlLocalSessionChanges,true,0,0,LCID);
finally
xla.Quit;
end;
end;
//--------------------------------------------------------------------
end.
Add the type library unit to the uses clause.

uses
windows, sysutils, excel_tlb;
The first line of code creates an Excel application object.

xla := CoApplication.Create;
The following line of code retrieves the user default locale identifier. This is required by many of Excel’s methods and properties.

LCID := GetUserDefaultLCID;
The following line of code sets the visible property to true. This causes the excel window to become visible. This is useful for checking that the code is working correctly during development.

Note : This call takes the LCID parameter. Unfortunately this is not documented within the Excel help file. The file c:\program files\borland\Delphi 3\imports\excel_tlb.pas shows the function property and method definitions.

xla.visible[LCID] := true;
The following creates a new worksheet, assigning a reference to it within a Delphi variable. Under VBA, the template parameter is optional, Under Delphi it must be supplied.

xlw := xla.Workbooks.Add('c:\delphi\excel\sample\demo.xlt', LCID);
Note : You do not need to supply an Excel template file (.xlt), though it is the best way to set the formatting information. The more that can be done within excel, the less needs to be done within Delphi. This leads to simpler and more maintainable systems.

To create a blank workbook use:

xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
The following two lines each set a single cell with a value. They show the two different cell referencing techinques.

xla.Range['A1', 'A1'].Value := 'Date';
xla.Cells[1, 2].Value := FormatDateTime('dd-mmm-yyyy', Now);
The following line demonstrates setting a row of data with one call. This give a significant performance advantage.

xla.Range['A2', 'D2'].Value := VarArrayOf([1, 10, 100, 1000]);
The following line demonstrates setting a formula.

xla.Range['E2', 'E2'].Formula := '=Sum(a2:d2)';
The follwing line of code executes a VBA function which is stored within the template file. The code does looks a bit puzzling at first. By typecasting xla to an OLEVariant forces the call to use late binding rather than early. (Causing the method name and parameters to be resolved at runtime rather than compile time). This is required for this call as Delphi cannot tell how many parameters the macro ‘Demo’ has.

OLEVariant(xla).Run( 'Demo', FormatDateTime('dd-mmm-yyyy', Now));
The following code saves the spreadsheet as filename. All parameters must be supplied even although most are optional within VBA.

xlw.SaveAs( filename, xlWorkbookNormal, '', '',False,False, xlNoChange, xlLocalSessionChanges,
true, 0, 0, LCID);
The following line of code closes Excel and de-allocates any memory associated with it.

xla.quit;
Summary

Always use early binding.
If late binding is required for some calls, use early binding in general and typecast the object variable to an OLEVariant for the calls that require late binding.
Do not include the type library unit within your project.
Keep automation code within a separate unit.
Use the Excel macro recorder to prototype your automation code.
Use the vbaxl8.hlp file for Excel object programming information.
Use the unit excel_tlb.pas to check for Delphi parameter types and numbers.
Load up Excel spreadsheet templates (.xlt files) that contain predefined formatting and then super-impose data. This is faster and reduces the programming required to create formatted spreadsheets. Templates MUST be stored with the application in the application preectory. This will remove the possibility of name clashes. Template files can also contain Excel macros that can be run from Delphi.
Ensure that the quit method is called for the Excel application object (xla.quit). Not calling xla.quit will quickly exhaust all windows resources as multiple instances of Excel will be created.
It is easy to check for multiple instances of Excel by using the NT Task Manager Processes Page (press CTL+ALT+Del to access this.)
On large spreadsheets, setting multiple cells on each automation call will dramatically improve performance. Try not to sacrifice code readability.
Appendix A – Performance

All tests were done on a P166/64mb memory. The initial timings were not recorded. This ensured that excel was available from cache rather than loading from disk. This made it easier to record consistent timings. For a real application there will be approx a 5 second delay in loading excel the first time it is used within the session.

The test involved loading a blank spreadsheet with 10 cols by n rows worth of numeric data. Three techniques were used to test performance:

Populating the sheet one cell at a time.
Populating the spreadsheet one row at a time.
Populating the spreadsheet in one go.
These timings do not include the time involved in retrieving data from the database. This will add a significant amount of time for the creation of large spreadsheets.

All the times shown below are in minutes, time was measured to the nearest second.

Spreadsheet size (rows * columns) Cell at a time Row at a time Sheet at a time
10 * 10 0:01 0:01 >0:01
100 * 10 0:07 0:01 0:01
1000 * 10 1:13 0:07 0:05
5000 * 10 5:22 0:35 0:25

Approx. cells/second 150 1500 2000



Only small spreadsheets can be efficiently populated one cell at a time.

Larger sheets should be populated a row at a time.

The additional complexity of implementing a sheet at a time data population is not worthwhile.

Using the clipboard to transfer data is not recommended as this will corrupt the clipboard and may lead to obscure code.

Saving data as a CSV file and loading it into Excel may be faster, but will require formatting to be done within code and make coding/error handling more complex.

The procedures used for testing:

//-----------------------------------------------------------------------
procedure FillByCell;
var
xla : _Application;
xlw : _Workbook;
LCID : integer;
i,j : integer;
begin
xla := CoApplication.Create;
LCID := GetUserDefaultLCID;
try
xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
for i:=1 to ROWS do begin
for j:=1 to 10 do begin
xla.Cells[i,j] := i+j;
end;
end;
xlw.close(false, '', false, LCID);
finally
xla.Quit;
end;
end;
//-----------------------------------------------------------------------
procedure FillByRow;
var
xla : _Application;
xlw : _Workbook;
CellFrom : string;
CellTo : string;
i,j : integer;
Row : array[1..10] of variant;
LCID : integer;
begin
xla := CoApplication.Create;
LCID := GetUserDefaultLCID;
try
xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
for i:=1 to ROWS do begin
for j:=1 to 10 do begin
Row[j] := i+j;
end;
CellFrom := 'A' + InttoStr(i);
CellTO := 'J' + InttoStr(i);
xla.Range[CellFrom, CellTo].Value := VarArrayOf(Row);
end;
xlw.close(false, '', False, LCID);
finally
xla.Quit;
end;
end;
//-----------------------------------------------------------------------
procedure FillBySheet;
var
xla : _Application;
xlw : _Workbook;
CellFrom : string;
CellTo : string;
i,j : integer;
range : Variant;
row : array [1..10] of Variant;
LCID : integer;
begin
xla := CoApplication.Create;
LCID := GetUserDefaultLCID;
try
xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
Range := VarArrayCreate([1, ROWS], varVariant);
for i:=1 to ROWS do begin
for j:=1 to 10 do begin
row[j] := i+j;
end;
Range[i] := VarArrayOf(row);
end;
CellFrom := 'A' + InttoStr(1);
CellTO := 'J' + InttoStr(ROWS);
xla.Range[CellFrom, CellTo].FormulaArray := Range;
xlw.close(false, '', False, LCID);
finally
xla.Quit;
end;
end;

Appendix 2 – Using a Delphi Wrapper Class

Download code

Rather than embedding Automation calls preectly within your application, this example demonstrates how a simple Delphi class can be used to encapsulate the Excel objects for your applicaions. This can give you a simpler interface to the objects and may help deal with any changes to the object interfaces in future versions of Excel.

unit sheet;

interface

uses
EXCEL_TLB, windows, sysutils;
//-------------------------------------------------------------------------
type
tExcel = class
private
xla : _Application;
xlw : _Workbook;
LCID : integer;
procedure fSetVisible(Visible : boolean);
function fGetVisible : boolean;
procedure fSetCell(Cell : string; Value : OLEVariant);
function fGetCell(Cell : string) : OleVariant;
public
constructor create;
destructor destroy; override;

procedure AddWorkBook(Template : OleVariant);
procedure SaveAs(filename : string);

property Visible : boolean read fGetVisible write fSetVisible;
property Cell[Cell : string] : OleVariant read fGetCell write fSetCell;
end;
//-------------------------------------------------------------------------
Procedure CreateSpreadsheet(filename : string);
//-------------------------------------------------------------------------

implementation
//-------------------------------------------------------------------------
constructor tExcel.create;
begin
LCID := GetUserDefaultLCID;
xla := CoApplication.Create;
end;
//-------------------------------------------------------------------------
destructor tExcel.destroy;
begin
xla.Quit;
inherited;
end;
//-------------------------------------------------------------------------
procedure tExcel.AddWorkBook(Template : OleVariant);
begin
xlw := xla.Workbooks.Add(Template, LCID);
end;
//-------------------------------------------------------------------------
procedure tExcel.fSetVisible(Visible : boolean);
begin
xla.visible[lcid] := Visible;
end;
//-------------------------------------------------------------------------
function tExcel.fGetVisible : boolean;
begin
result := xla.visible[lcid];
end;
//-------------------------------------------------------------------------
procedure tExcel.fSetCell(Cell : string; Value : OLEVariant);
begin
xla.Range['A1', 'A1'].Value := value;
end;
//-------------------------------------------------------------------------
function tExcel.fGetCell(Cell : string) : OleVariant;
begin
result := xla.Range['A1', 'A1'].Value;
end;
//-------------------------------------------------------------------------
procedure tExcel.SaveAs(filename : string);
begin
xlw.SaveAs(filename,xlWorkbookNormal,'','',False,False,xlNoChange,
xlLocalSessionChanges,true,0,0,LCID);
end;

//The following code example uses this class to create a spreadsheet.

Procedure CreateSpreadsheet(filename : string);
var
xl : tExcel;
begin
xl := tExcel.create;
try
xl.AddWorkBook('c:\graham\excel\sample2\ssddemo.xlt');
xl.visible := true;
xl.cell['a1'] := 'testing';
xl.SaveAs(filename);
finally
xl.free;
end;
end;
Ver 1.0 GSM 29/12/1997
Michael 2000-01-25
  • 打赏
  • 举报
回复
使用OleObject.
929 2000-01-24
  • 打赏
  • 举报
回复
在DELPHI中读取电子表格文件,属于OLE自动化问题.可用两种方法完成。
1、应用VARAIANT类型变量,采用CREATEOLEOBJECT函数生成EXCEL自动化对象。这种方法应用比较简单,但速度较慢,并且无法提供编译时的函数校验。
2、将EXCEL类型库引入DELPHI程序,这时可通过COM接口访问EXCEL自动化对象,速度较快。
此外,在DELPHI5中在SERVER页上提供了EXCEL自动化对象,可直接应用之。
Venne 2000-01-21
  • 打赏
  • 举报
回复
也许下面这个例子可以帮你,在窗口上放一个按钮和一个LISTBOX即可。
另外,你还可以使用OLE控件,然后在代码里使用 CreateObjectFromFile方法,在此之前,你可以好好文设置一下OLE的属性。


unit Main;

interface

uses
Windows, Messages, SysUtils,
Classes, Graphics, Controls,
Forms, Dialogs, StdCtrls;

type
TForm1 = class(TForm)
Button1: TButton;
ListBox1: TListBox;
procedure Button1Click(Sender: TObject);
procedure FormDestroy(Sender: TObject);
private
XLApplication: Variant;
public
end;

var
Form1: TForm1;

implementation

uses
ComObj;

{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
const
{ XlSheetType }
xlChart = -4109;
xlDialogSheet = -4116;
xlExcel4IntlMacroSheet = 4;
xlExcel4MacroSheet = 3;
xlWorksheet = -4167;

{ XlWBATemplate }
xlWBATChart = -4109;
xlWBATExcel4IntlMacroSheet = 4;
xlWBATExcel4MacroSheet = 3;
xlWBATWorksheet = -4167;
var
i, j: Integer;
Sheets: Variant;
begin
XLApplication := CreateOleObject('Excel.Application');
XLApplication.Visible := True;
XLApplication.Workbooks.Add;
XLApplication.Workbooks.Add(xlWBatChart);
XLApplication.Workbooks.Add(xlWBatWorkSheet);
XLApplication.Workbooks[2].Sheets.Add(,,1,xlChart);
XLApplication.Workbooks[3].Sheets.Add(,,1,xlWorkSheet);
for i := 1 to XLApplication.Workbooks.Count do begin
ListBox1.Items.Add('Workbook: ' + XLApplication.Workbooks[i].Name);
for j := 1 to XLApplication.Workbooks[i].Sheets.Count do
ListBox1.Items.Add(' Sheet: ' + XLApplication.Workbooks[i].Sheets[j].Name);
end;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
if not VarIsEmpty(XLApplication) then begin
XLApplication.DisplayAlerts := False; // Discard unsaved files....
XLApplication.Quit;
end;
end;

end.
kxy 2000-01-21
  • 打赏
  • 举报
回复
var
sheet : Variant;
XLApp : Variant;
begin
XLApp := CreateOleObject('Execl.Application');
XLApp.Visible := True;
Sheet := XLApp.WorkBooks[1].Worksheets['xxx'];
// Sheet.Cells[i,j]
end;
不需Excel也可讀寫xls檔的Delphi控件 用来读取Excel,Access的控件 读写任何单元值 数字型、字符型、布尔型以及错误型。但是你了解日期和时间型单元吗?在Excel中没有这样的单元。Excel是和Delphi一样的方式来存储日期和时间的,即浮点型。那是什么制造了日期或时间值啦,是格式化。 完全支持公式。你可以使用和Excel一样的函数,也支持对外部工作簿引用的公式,你甚至可以从外部引用读取结果。 与操作Delphi的TStringGrid一样的方式操作单元格,或则通过给单元格命名,比如:”D7” 根据单元格的规则以字符串格式读取单元值 根据你的需求格式化单元 格式化很简单。通过获取单元格,你就可以操作单元的格式属性。例如:XLSReadWriteII.Sheet[n].Cell[Col,Row].FontSize := 12; 你也可以对域进行格式化,比如设置边框。 支持所有的Excel格式选项 合并单元格 操作所有的打印选项 选择页面大小,设置边距 设置标题和页脚,包括格式码。 定义打印区域 设置分页符 XLSReadWriteII完全支持Unicode 所有的字符型都是宽字符型。你完全不必担心非英语字符。 控件 你可以插入控件以及定义他们的源和目的单元 绘图 插入所有种类的绘图,从简单的线条到AutoShapes 多行文本和文本框 创建和编辑文本框以及多行文本 图表 创建和Excel中一样选项的图表 复制/移动/删除 可以以Excel一样的行为复制、移动和删除单元 行列也可以像单元一样复制和移动 支持工作簿之间的复制和移动 可以复制整个工作簿 使用命名的域/单元 你可以在你想要的任何地方命名 通过命名访问单元格,例如:XLS.NameAsFloat['MyCell'] := 202.5; 支持指定、内置的名称 支持所有超链接类型 网页和E-mail地址 本地文件 服务器文件 工作薄引用 加密文档 你可以读写加密文件。条件是你需要知道文件的密码。 计算 可计算单元以及整个工作薄 计算引擎将依据单元进行计算。 可读取引用的外部工作薄。这通过一个特殊的快速查询程序进行执行 Rich Text单元 为了轻松地创建多字体格式的单元,单元可以以RTF格式读写 VBA宏 可以读写文件中的宏,使用XLSReadWriteII可以为控件比如:按钮、组合框等添加宏 导入及导出 从下列导入数据… Open Office Calc文档 CSV文件以及其他可以自动识别分隔符、小数分隔符和文本引用字符的文本文件 HTML表格(标签) 数据库 导出数据为… CSV文件 HTML文件 其他 自动过滤 单元验证 有条件的格式 合并单元 PaintCell方法:将单元内容渲染到TCanvas对象的 XLSReadWriteII包还包含下列组件: 从任何数据库中导入数据到工作薄 工作薄导出为HTML文件

5,379

社区成员

发帖
与我相关
我的任务
社区描述
Delphi 开发及应用
社区管理员
  • VCL组件开发及应用社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧