使⽤Delphi和ADO编辑Excel⼯作表
This step-by-step guide describes how to connect to Microsoft Excel, retrieve sheet data, and enable editing of the data using the DBGrid. You'll also find a list of the most common errors that might appear in the process, plus how to deal with them.
本分步指南介绍了如何连接到Microsoft Excel，检索⼯作表数据以及使⽤DBGrid启⽤数据编辑。 您还将找到过程中可能出现的最常见错误的列表，以及如何处理它们。
What's Covered Below:
以下内容：
合肥婚纱Methods for transferring data between Excel and . How to connect to Excel with (ActiveX Data Objects) and Delphi.
Excel与之间的数据传输⽅法。 如何使⽤ (ActiveX数据对象)和Delphi连接到Excel。
Creating an Excel spreadsheet editor using Delphi and ADO
使⽤Delphi和ADO创建Excel电⼦表格编辑器
过火歌词
打印标题每页都有Retrieving the data from Excel. How to reference a table (or range) in an Excel workbook.
从Excel检索数据。 如何在Excel⼯作簿中引⽤表(或范围)。
A discussion on Excel field (column) types
关于Excel字段(列)类型的讨论
How to modify Excel sheets: edit, add and delete rows.
如何修改Excel⼯作表：编辑，添加和删除⾏。
Transferring data from a Delphi application to Excel. How to create a worksheet and fill it with custom data from an MS Access databa.
用心躁也
将数据从Delphi应⽤程序传输到Excel。 如何创建⼯作表并⽤MS Access数据库中的⾃定义数据填充⼯作表。
如何连接到Microsoft Excel ( How to Connect to Microsoft Excel )
Microsoft Excel is a powerful spreadsheet calculator and data analysis tool. Since rows and columns
of an Excel worksheet cloly relate to the rows and columns of a databa table, many developers find it appropriate to transport their data into an Excel workbook for analysis purpos; and retrieve data back to the application afterwards.好想和你有个家
Microsoft Excel是功能强⼤的电⼦表格计算器和数据分析⼯具。 由于Excel⼯作表的⾏和列与数据库表的⾏和列紧密相关，因此许多开发⼈员认为将其数据传输到Excel⼯作簿中以进⾏分析是合适的； 然后将数据检索回应⽤程序。
The most commonly ud approach to data exchange between your application and Excel is Automation. Automation provides a way to read Excel data using the Excel Object Model to dive into the worksheet, extract its data, and display it inside a grid-like component, namely DBGrid or StringGrid.
在应⽤程序和Excel之间进⾏数据交换的最常⽤⽅法是⾃动化 。 ⾃动化提供了⼀种使⽤Excel对象模型读取Excel数据的⽅法，以深⼊研究⼯作表，提取其数据，并将其显⽰在类似于⽹格的组件(即DBGrid或StringGrid)中。
Automation gives you the greatest flexibility for locating the data in the workbook as well as the ability to format the worksheet and make various ttings at run time.
⾃动化为您提供了最⼤的灵活性，可以在⼯作簿中定位数据，还可以在⼯作时格式化⼯作表并进⾏各种设置。
To transfer your data to and from Excel without Automation, you can u other methods such as:
若要在没有⾃动化的情况下与Excel进⾏数据传输，可以使⽤其他⽅法，例如：
Write data into a comma-delimited text file, and let Excel par the file into cells
将数据写⼊以逗号分隔的⽂本⽂件，然后让Excel将⽂件解析为单元格
Transfer data using DDE (Dynamic Data Exchange)
躬耕乐道
使⽤DDE(动态数据交换)传输数据
Transfer your data to and from a worksheet using ADO
使⽤ADO与⼯作表之间进⾏数据传输
使⽤ADO进⾏数据传输 ( Data Transfer Using ADO )
Since Excel is JET OLE DB compliant, you can connect to it with Delphi using ADO (dbGO or AdoExpress) and then retrieve the worksheet's data into an ADO datat by issuing an SQL query (just like you would open a datat against any databa table).
由于Excel符合JET OLE DB，因此可以使⽤ADO(dbGO或AdoExpress)与Delphi进⾏连接，然后通过发出SQL查询将⼯作表的数据检索到ADO数据集中(就像您针对任何数据库表打开数据集⼀样) 。
In this way, all the methods and features of the ADODatat object are available to process the Excel data. In other words, using the ADO components let you build an application that can u an Excel workbook as the databa. Another important fact is that Excel is an out-of-process . ADO runs in-process and saves the overhead of costly out-of-process calls.
这样，ADODatat对象的所有⽅法和功能都可⽤于处理Excel数据。 换句话说，使⽤ADO组件，您可以构建可以将Excel⼯作簿⽤作数据库的应⽤程序。 另⼀个重要的事实是Excel是进程外 。 ADO在进程内运⾏，并节省了代价⾼昂的进程外调⽤的开销。
When you connect to Excel using ADO, you can only exchange raw data to and from a workbook. An ADO connection cannot be ud for sheet formatting or implementing formulas to cells. However, if you transfer your data to a worksheet that is pre-formatted, the format is maintained. After the data is
inrted from your application to Excel, you can carry out any conditional formatting using a (pre-recorded) macro in the worksheet.
当您使⽤ADO连接到Excel时，您只能与⼯作簿交换原始数据。 ADO连接不能⽤于⼯作表格式或实现单元格的公式。 但是，如果将数据传输到预先设置格式的⼯作表中，则将保留该格式。 将数据从应⽤程序插⼊Excel之后，您可以使⽤⼯作表中的(预先记录的)宏执⾏任何条件格式。
You can connect to Excel using ADO with the two OLE DB Providers that are a part of MDAC: Microsoft Jet OLE DB
Provider or Microsoft OLE DB Provider for ODBC Drivers. We'll focus on Jet OLE DB Provider, which can be ud to access data in Excel workbooks through installable Indexed Sequential Access Method (ISAM) drivers.
您可以使⽤ADO与作为MDAC⼀部分的两个OLE DB提供程序连接到Excel：Microsoft Jet OLE DB提供程序或ODBC驱动程序的Microsoft OLE DB提供程序。 我们将重点介绍Jet OLE DB提供程序，该程序可⽤于通过可安装的索引顺序访问⽅法(ISAM)驱动程序访问Excel⼯作簿中的数据。
Tip: See the ADO Databa Programming if you're new to ADO.
提⽰：如果您不 ADO，请参阅 ADO数据库编程的 。
ConnectionString魔术 ( The ConnectionString Magic )
The ConnectionString property tells ADO how to connect to the datasource. The value ud for ConnectionString consists of one or more arguments ADO us to establish the connection.
ConnectionString属性告诉ADO如何连接到数据源。 ⽤于ConnectionString的值由ADO⽤于建⽴连接的⼀个或多个参数组成。
In Delphi, the TADOConnection component encapsulates the ADO connection object; it can be shared by multiple ADO datat (TADOTable, TADOQuery, etc.) components through their Connection properties.
在Delphi中，TADOConnection组件封装了ADO连接对象。 它可以由多个ADO数据集(TADOTable，TADOQuery等)组件通过其Connection属性共享。
In order to connect to Excel, a valid connection string involves only two additional pieces of information - the full path to the workbook and the Excel file version.
为了连接到Excel，有效的连接字符串仅涉及另外两条信息-⼯作簿的完整路径和Excel⽂件版本。
A legitimate connection string could look like this:
合法的连接字符串可能如下所⽰：
射雕英雄传读后感
ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyWorkBooks\myDataBook.xls;Extended Properties=Excel 8.0;';
When connecting to an external databa format supported by the Jet, the extended properties for the connection needs to be t. In our ca, when connecting to an Excel "databa," extended properties are ud to t the Excel file version.
当连接到Jet⽀持的外部数据库格式时，需要设置连接的扩展属性。 在我们的情况下，当连接到Excel“数据库”时，扩展属性⽤于设置Excel⽂件版本。鲈鱼做法大全菜谱
For an Excel95 workbook, this value is "Excel 5.0" (without the quotes); u "Excel 8.0" for Excel 97, Excel 2000, Excel 2002, and ExcelXP.
对于Excel95⼯作簿，此值为“ Excel 5.0”(不带引号)； 对于Excel 97，Excel 2000，Excel 2002和ExcelXP，请使⽤“ Excel
8.0”。
Important: You must u the Jet 4.0 Provider since Jet 3.5 does not support the ISAM drivers. If you t the Jet Provider to version 3.5, you'll receive the "Couldn't find installable ISAM" error.
重要说明：由于Jet 3.5不⽀持ISAM驱动程序，因此必须使⽤Jet 4.0 Provider。 如果将Jet Provider设置为版本3.5，则会收到“找不到可安装的ISAM”错误。
Another Jet extended property is "HDR=". "HDR=Yes" means that there is a header row in the range, so the Jet will not include the first row of the lection into the datat. If "HDR=No" is specified, then the provider will include the first row of the range (or named range) into the datat.
Jet的另⼀个扩展属性是“ HDR =“。 “ HDR =是”表⽰该范围中存在标题⾏，因此Jet不会将所选内容的第⼀⾏包括在数据集中。 如果指定“ HDR = No”，则提供程序会将范围(或命名范围)的第⼀⾏包括到数据集中。
The first row in a range is considered to be the header row by default ("HDR=Yes"). Therefore, if you have column heading, you do not need to specify this value. If you do not have column headings, you need to specify "HDR=No".
默认情况下，范围内的第⼀⾏被视为标题⾏(“ HDR = Yes”)。 因此，如果您具有列标题，则⽆需指定此值。 如果没有列标题，则需要指定“ HDR = No”。
Now that you're all t, this is the part where things become interesting since we're now ready for some code. Let's e how to create a simple Excel Spreadsheet editor using Delphi and ADO.
现在您已经准备就绪，这是使事情变得有趣的部分，因为我们现在已经准备好编写⼀些代码。 让我们看看如何使⽤Delphi和ADO创建⼀个简单的Excel Spreadsheet编辑器。
Note: You should proceed even if you lack knowledge on ADO and Jet programming. As you'll e, editing an Excel
workbook is as simple as editing data from any standard databa.
注意：即使您缺乏ADO和Jet编程知识，也应该继续进⾏。 如您所见，编辑Excel⼯作簿就像编辑任何标准数据库中的数据⼀样简单。
