使用 Delphi 读取 Excel 电子表格
我需要使用 Delphi 2010 读取和写入 Excel 电子表格。没什么花哨的。只需读取和写入不同工作表上特定单元格和范围的值。需要在没有安装 Excel 的情况下工作并支持 Excel 2007。
我看过的一些内容：
我尝试使用 ADO，它可以很好地选择整个工作表中的所有内容，但我在读取特定单元格或范围时运气不佳。
NativeExcel 看起来很有前途，但它似乎并没有在积极开发中，而且他们也没有'不回复电子邮件。
Axolot 有几种产品。主要产品看似功能强大，但价格昂贵。他们有一个精简版本，但不支持 Delphi 2010。
有什么建议吗？免费固然很好，但我愿意接受商业解决方案，只要它可靠且得到良好的支持。
I need to read from and write to Excel spreadsheets using Delphi 2010. Nothing fancy. Just reading and writing values from specific cells and ranges on different sheets. Needs to work without having Excel installed and support Excel 2007.
Some things I've looked at:
I've tried using ADO, which works OK for selecting everything in an entire sheet, but I haven't had much luck reading specific cells or ranges.
NativeExcel looked promising, but it doesn't seem to be in active development, and they don't respond to e-mails.
Axolot has a couple of products. The main product seems to be very functional, but is pricey. They have a lite version, but it doesn't support Delphi 2010.
Any recommendations? Free would be great, but I'm open to a commercial solution as long as it's reliable and well supported.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（11）
TMS Flexcel - 我知道它看起来像 Excel 的报告组件（它做得非常好，并且是工具包中非常方便的工具），但它还包括用于读取和显示 Excel 文件的组件。 Adrian Gallero 对 Excel API（包括 Excel 2007）的了解程度给我留下了深刻的印象。
http ://www.tmssoftware.com/site/flexcel.asp
当然不是免费的，但 75 欧元，我认为它很有价值。
TMS Flexcel - I know it looks like a reporting component for Excel (which it does very well and is a very handy tool to have in your toolkit) but it also includes components for reading and displaying Excel files. I've been very impressed with how well Adrian Gallero seems to know the Excel API, including Excel 2007.
http://www.tmssoftware.com/site/flexcel.asp
Not free of course, but at 75 Euros I think it's good value.
我在 ADO 方面非常幸运，只要 Excel 工作表是相当简单的行/列布局。
我发现使用 ADO 的关键是将 Excel 工作表视为数据库。如果您的 Excel 工作表主要是直行和列布局，只需将行视为数据库记录，将列视为字段。首先通过搜索特定列（字段）值（最好是唯一的值）导航到所需的行，然后通过引用作为列名称的字段来读取该行中所需的单元格。
如果你的 Excel 工作表格式更自由，那么这会更困难。
I've had very good luck with ADO, provided the Excel sheet is a fairly straight-forward row/column layout.
The key with using ADO, I've found, is treating the Excel sheet like a database. If your Excel sheets are primarily straight row and column layouts, just treat the rows as database records and the columns as fields. Navigate to the desired row first by searching for a particular column (field) value (preferably something unique), and then read the desired cell in that row by referencing the field that is the column name.
If your Excel sheets are more free-form, then it will be more difficult.
我推荐 http://www.scalabium.com 的 SMImport / SMExport
迈克一直非常乐于助人，快速响应。
I'd recommend SMImport / SMExport from http://www.scalabium.com
Mike has always been really helpful and quick to respond.
不要放弃 NativeExcel。我已经使用它几年了，效果非常好。它速度快且用途广泛。我用它来生成一个格式良好的多页电子表格，其中包含冻结的窗格、单元格中的公式以及来自客户数据库的数据，供他们用于输入，然后发送回给我。当我的客户从我这里得到第一个电子表格时，他们非常兴奋，因为它极大地减少了他们的工作量，而且使用起来相当直观。
我不知道为什么他们没有回复你，因为在过去的两年里我至少更新了他们的包裹几次。当我的许可证到期时，我肯定打算续签。
Don't write off NativeExcel. I have used it for a couple of years now with excellent results. It's fast and versatile. I use it to produce a nicely formatted multi-page spreadsheet with frozen panes, formulas in cells, and data from a client's database for them to use for input and then send back to me. My clients were really thrilled when they got the first spreadsheet from me because it reduced their workload tremendously and it was fairly intuitive for them to use.
I don't know why they have not responded to you because I have updated their package at least a couple of time over the last two years. When my license expires, I definitely intend to renew.
真正有帮助的是，如果您对 Excel 文件的布局有某种控制。
我已经构建了一个完整的单元和验收测试框架，其中数据和测试控件都包含在 Excel 电子表格中。
我通过 ADO 完成了一切。您可以将 ADO SQL 查询限制为整个工作表、命名范围或任何范围。以我的观点和经验来看，这个方法非常强大。
有两件事确实给我带来了一些问题：
1. 根据工作表的命名方式，ADO 可能会或可能不会看到它们（同样，如果您可以控制布局，那就太好了！）
2. 读取数据时请注意 ADO 返回的数据类型，即它可能会将数字显示为字符串。这是因为 ADO 尝试根据前几行 IIRC 来猜测数据类型。
免责声明：我从未使用过上述任何工具。 ADO 为我解决了这个问题，自从我为我的框架编写了代码以来，我感觉自己更有控制力（显然保存 ADO 部分......）。
What really helps is if you have some kind of control over the layout of the excel file.
I have built a whole unit and acceptance testing framework where the data and the test controls are all contained within an Excel spreadsheet.
I did everything through ADO. You can restrict your ADO SQL query to a whole sheet, a named range or any range for the matter. In my opinion and experience, this method is very powerful.
Two things that did cause me some problems :
1. depending on how your sheets are named, ADO might or might not see them (again, if you have control over the layout, great !)
2. be careful about the data type ADO returns when you read data i.e. it might show numbers as strings. This is because ADO tries, IIRC, to guess the data type based on the first few rows.
Disclaimer : I have never used any of the tools mentioned above. ADO did the trick for me, and I feel more in control since I wrote the code for my framework (save the ADO part obviously...).
Bruce，我使用 Axolot XLSReadWriteII 组件已有 10 年了。这非常好，他们的支持论坛（虽然内容很少）似乎受到很好的监控。 XLSReadWriteII2 版本速度快得令人眼花缭乱，支持各种功能，例如图表和图形、命名范围、动态添加公式、单元格格式（包括边框和阴影、合并单元格、垂直和水平对齐、自动宽度列大小调整）以及依此类推）。
我没有升级到最新版本（我们仍然使用XLSReadWriteII2），因为我们仍然可以使用Excel XP格式文件，而且我根本没有使用XLSMini。不过，我可以对整个产品说一些非常好的话；事实上，上周我只是用它来导出一些数据库。
如果您决定走这条路，我有一堆关于如何做可能有用的不同事情的注释；如果你想要它们，请给我留言。我还有一个 Delphi 2007 应用程序，它仅展示如何进行不同的格式设置和对齐；实际上，我在 Excel 中复制了一份现有的、相当复杂的报告，其中包含所有格式、边框等，我很高兴也能提供给您。
免责声明：我与 Axolot 或其任何员工没有任何关系。我只是一个非常高兴的客户，在上一份工作中了解了该产品，并且当我开始当前的工作时印象深刻，因此购买了它。
Bruce, I've used the Axolot XLSReadWriteII component for going on 10 years now. It's been very good, and their support forums (while lite on content) seem to be monitored pretty well. The XLSReadWriteII2 version is blindingly fast, and supports all sorts of things like charts and graphics, named ranges, adding formulas on the fly, cell formatting (including borders and shading, merging cells, vertical and horizontal alignment, auto-width column sizing, and so forth).
I haven't upgraded to the latest version (we're still using XLSReadWriteII2) because we can still use the Excel XP format files, and I haven't used the XLSMini at all. I can say really good things about the full product, though; in fact, I just used it for a couple of database export things this past week.
If you decide to go that route, I have a bunch of notes about how to do different things that might be useful; if you want them, drop me a note. I also have a Delphi 2007 app that just shows how to do different formatting and alignments; I actually reproduced an existing, fairly complex report in Excel complete with all of the formats, borders, etc. that I'd be glad to let you have as well.
DISCLAIMER: I have no connection with Axolot or any of their employees. I'm just a very happy customer who learned of the product at a previous job, and was impressed enough to buy it when I started my current one.
不要为 Axolot 的 XLSMini（精简版）版本烦恼。我还没有购买其中任何一个，但我在 2008 年初询问了 Excel 2007 支持情况，Lars 告诉我 XLSMini 是基于 XLSReadWriteII 的，并且两者将同时更新为支持 Excel 2007。 XLSReadWriteII 自 2008 年 4 月起获得 Excel 2007 支持； XLSMini 还没有。
Don't bother with Axolot's XLSMini (lite) version. I haven't purchased either of them yet, but I asked about Excel 2007 support in early 2008 and Lars told me XLSMini was based on the XLSReadWriteII and that both would be updated with Excel 2007 support at the same time. XLSReadWriteII has had Excel 2007 support since April 2008; XLSMini still doesn't have it.
很幸运，我已经使用 Axolot 好几年了。支持论坛并没有充满消息，但这也许是因为它效果很好？
With great luck, I've used Axolot for some years now. The support forum isn't exactly brimming with messages, but maybe that's because it works so well?
您可以使用 ADO 连接字符串，例如
http://www.connectionstrings.com/excel
比包含选项（在 ado 连接字符串的第三个选项卡中）：
出于安全目的，Microsoft 防止修改（IMEX=1）
http://support.microsoft.com/kb/904953/en
示例 SQL（不要忘记括号）：
唯一不能做的就是删除：
http://support.microsoft.com/kb/257819/en
因此，要删除一行，请将其清空！
您还可以通过 ADO 使用 SQL 来导出：
You can use ADO connection string like
http://www.connectionstrings.com/excel
than include the options (in the third tab of ado connection string):
for security purposes Microsoft prevent modifications (with IMEX=1)
http://support.microsoft.com/kb/904953/en
Sample SQL (don't forget the brackets):
The only thing you can't do, is deletion:
http://support.microsoft.com/kb/257819/en
So to delete a row make it empty!
You can also use SQL via ADO to export:
我建议选择不需要在计算机上安装 Excel 的选项。我曾经使用过一个组件，可以轻松地在一张表中填写一些数据，而无需安装 Excel。我还会在 Excel 工作表本身中完成大部分 Excel 工作。只需使用组件在工作表上填写一些数据即可。
我的2克拉。
I would advise to go for an option where you don't need Excel installed on the machine. I once used a component that could easily fill in some data in one sheet without needing excel installed. I would also do most of the Excel work in the Excel sheet itself. And just use the components to fill in some data on the sheet.
My 2cts.
我还没有尝试过，它已经有 3 年历史了，但是有一个开源版本 https://github。 com/rareMaxim/Excel4Delphi
I haven’t tried it yet, and it’s 3 years old, but there’s a Open Source version https://github.com/rareMaxim/Excel4Delphi