带有Delphi XE中XLSX文件的ADO [英] ADO with XLSX files in Delphi XE
问题描述
这里的问题是ADO与Excel的连接-这仍然是在Dephi XE环境中读取/写入excel文件的标准方法吗?使用ACEOLEDB驱动程序(ACE 12)进行读写时，我们遇到了多个问题，其中包括
The issue here is ADO connection with Excel - is this still the standard way to read/write excel files within a Dephi XE environment? We're coming up with multiple issues when reading/writing using the ACEOLEDB driver (ACE 12) and this includes
- 读取带有标签的单元格不会返回结果
- 导出网格时出现无效的浮点数".
我们还注意到，Microsoft网站上有许多版本的ACE 12驱动程序(通过Access Database驱动程序可执行文件)，并且它们每个在Delphi中似乎都有不同的问题.
We've also noticed that there's many versions of the ACE 12 driver out on Microsoft's website (via Access Database driver executables) and they each seem to have different issues with Delphi.
记住这些事情，
- 此时在Excel中使用ADO是否不好?
- 还有其他人遇到这些问题吗?您是怎么解决这些问题的(除了使用XLS文件而不是XLSX之外)?
推荐答案
Delphi中的ADO倾向于TDataSet模型，这意味着严格的表格数据...而excel不是.每个excel工作表都填充有一个随机单元格，其中一些可能会构成准表格范围，或者可能不会.
ADO in Delphi is leaning to TDataSet model, which mean strictly tabular data... that excel is not. Each excel sheet has a random cells filled, some of those may constitute quazi-tabular ranges, or may not.
您可以根据所安装的软件
Depending on the installed software you can
1)使用Excel应用程序打开XLSX，读取单元格并将其传递给您的程序.这是最简单和兼容的方法，但是由于COM IPC的编组和切换而明显慢.有一些技巧可以固定它，例如隐藏Excel窗口，复制数据数组而不是逐个单元的方法等等.
开始探索TExcelApplication组件- http://docwiki.embarcadero.com/RADStudio/XE3/zh/Using_Component_Wrappers
1) use Excel application to open XLSX, read the cells and pass them to your program. This is most easy and compatible method, though is noticeably slow due to COM IPC marshalling and switching. There are tricks to fasten it, like hiding Excel window, copying arrays of data instead of cell-by-cell approach and such.
Start exploring TExcelApplication component - http://docwiki.embarcadero.com/RADStudio/XE3/en/Using_Component_Wrappers
2)如果不想依靠安装商业Excel，可以尝试使用OpenOffice读取XLSX文件. Vanilla OpenOffice只能读取它们，但是其他发行版也可以编写它们. OpenOffice还公开了基于COM和HTTP的外部API.我知道有Delphi的Delphi项目-OOo进行交互，但个人没有使用它们，除了指出这种方法外，我无法对其进行详细评估.
2) If you do not want to rely on having commercial Excel installed, you may try reading XLSX files with OpenOffice. Vanilla OpenOffice can only read them though, but some other distro's can write them as well. OpenOffice also exposes external APIs both COM-based and HTTP-based. I know there are Delphi projects of Delphi - OOo interacting but personally did not used them and apart of noting that approach i can say no detailed assesment of it.
3)微软还曾经出售过Office for Developers或类似的产品，从而为您提供了Access和Excel内核作为可再发行产品，您可以将其与应用程序一起传递并安装并使用它们.如果仍然可行，邓诺.
3) Microsoft also used to sell Office for Developers or such, that gave you Access and Excel kernels as redistributables, that you could pass with your application and install them and use them. Dunno if it is still feasible though.
4)有一套商业组件，可以直接读写这些文件，而无需外部EXE来完成.尽管这将是最快的工作方式，但它仅支持某些功能子集(可能无法满足您的特定目标)，并且由于Microsoft会推出XLS的更新版本，因此可能存在未来兼容性"方面的麻烦和XLSX格式(可能与您无关或无关).就像Biff8格式的TXLSFile一样，例如有 OExport库.还有一个来自著名的TMS Studio的组件，也许还有更多.
4) there is a set of commercial components reading and writing those files directly w/o need to have external EXE's doing the job. While that would be the most fast way to work, it would only support some subset of features (which may or may not be ok for your particular goals) and may have troubles with "future compatibility" as Microsoft would roll out updated versions of XLS and XLSX formats (which again may be of some or none concern to you). Like there was TXLSFile for Biff8 format, there is for example OExport library. There is also a component from well known TMS Studio and maybe some more.
5)您可以加入一些开源项目，并尝试根据需要进行增强，然后再次取决于您需要多少子集.
5) You can join some open-source project and try to enhance it for your needs, then again that depends upon how much the subset you need.
我知道，许多人都使用OLE DB来访问Excel数据，但对我来说，它听起来像是个perversino，因为Excel文件根本没有任何内部常规数据排列，因此它像表格一样严格地以RDBMS形式存在.
I know, many people succefulyl use OLE DB to access Excel data, but for me it always sounded as some perversino, because Excel files do not have any internal regular data arrangement at all, less so strictly-tabular RDBMS-like one.
这篇关于带有Delphi XE中XLSX文件的ADO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
