如何以编程方式连接Excel电子表格？

I have a request for some contract work from an organization that uses Excel as a database and wants to do some work on the Excel data via a real database. (Yeah, I know, never mind...)

我要求使用Excel作为数据库的组织进行一些合同工作，并希望通过真实数据库对Excel数据进行一些工作。（是的，我知道，没关系......）

The client has an Excel sheet that they use internally to keep track of some government programmes. The data from this Excel sheet used to be manually imported into a SQL database via CSV as intermediate format and made available via a tiny web app. Changes in either the spreadsheet or the db were done manually (by different people) and had to be kept in sync manually.

客户有一张Excel表，他们在内部使用这些表来跟踪一些*计划。此Excel工作表中的数据曾经通过CSV作为中间格式手动导入SQL数据库，并通过一个小型Web应用程序提供。电子表格或数据库中的更改是手动完成的（由不同的人员完成），并且必须手动保持同步。

The spec for new functionality includes:

新功能的规范包括：

upload the Excel file into the web app
将Excel文件上传到Web应用程序
make minor changes via the web app (this bit is, of course, a no-brainer)
通过网络应用程序进行微小的更改（当然，这一点很简单）
occasionally export the data back into Excel
偶尔将数据导出回Excel

The spreadsheet (actually, it's a couple of them in a workbook) implements some guidelines necessary to interact with other institutions and therefore has to remain the same structurally before and after import. It contains a lot of formatting, hidden columns and sort buttons as well as a lot of data links between the cells in the different sheets.

电子表格（实际上，它是工作簿中的几个）实现了与其他机构交互所必需的一些准则，因此在导入之前和之后必须保持结构相同。它包含许多格式，隐藏列和排序按钮以及不同工作表中单元格之间的大量数据链接。

I don't want to have to reproduce the spreadsheet from scratch to deliver the export, nor do I want to manually extract the proper columns into CSV before making the import. I'm rather looking for a way to load the Excel, "query" certain fields, write them to the DB and later load the data back from the DB and manipulate only the contents of the proper cells.

我不想从头开始重现电子表格以提供导出，也不想在导入之前手动将正确的列提取到CSV中。我正在寻找一种加载Excel的方法，“查询”某些字段，将它们写入数据库，然后从数据库加载数据并仅操作正确单元格的内容。

Is there a way to programatically interface with an existing spreadsheet and only read or change the bits that you need?

有没有办法以编程方式与现有电子表格连接，只读取或更改您需要的位？

6 个解决方案

#1

We're reading and manipulating Excel-Data via Apache POI, which is not complete in decoding Excel files (namely formula cells are not completely supported) but our customers are quite happy with us.

我们正在通过Apache POI阅读和操作Excel-Data，这在解码Excel文件时并不完整（即不完全支持公式单元格），但我们的客户对我们非常满意。

POI is a Java Library, so if you are a pure Windows shop there may be other more natural options, but as I said, our experience with POI is very good, people are happy.

POI是一个Java库，所以如果你是一个纯粹的Windows商店，可能还有其他更自然的选择，但正如我所说，我们对POI的体验非常好，人们很高兴。

Additionally: I believe to have heard of Excel ODBC drivers - maybe this is what you want/need? (Sorry, I've never worked with them)

另外：我相信已经听说过Excel ODBC驱动程序 - 也许这就是你想要/需要的东西？（对不起，我从来没有和他们合作过）

#2

Excel is a 'COM Capable Application' and as such you can use COM to access and manipulate the data in an Excel document. You don't say what platform you are using - but if it's .NET then it's really very easy. See http://support.microsoft.com/kb/302084 for how to get started with C#.

Excel是一个“COM功能应用程序”，因此您可以使用COM访问和操作Excel文档中的数据。你没有说你正在使用什么平台 - 但如果它是.NET那么它真的很容易。有关如何开始使用C＃的信息，请参见http://support.microsoft.com/kb/302084。

If you're not using .net then any language that can interact with a COM component will work.

如果您不使用.net，那么任何可以与COM组件交互的语言都可以使用。

#3

The same API that's used by VBA is available through an external COM interface. There are quite a few books on the subject. I recommend the O'Reilly one by Steven Roman but your tastes may vary.

VBA使用的相同API可通过外部COM接口获得。关于这个主题有很多书。我推荐Steven Roman的O'Reilly，但你的口味可能会有所不同。

#4

You don't specify a language, so if you are language agnostic .Net gives you some very powerful classes for data handling:

你没有指定一种语言，所以如果你是语言无关的.Net为你提供了一些非常强大的数据处理类：

to open a csv file:

打开一个csv文件：

Imports System.Data.OleDb, Imports Excel = Microsoft.Office.Interop.Excel

Imports System.Data.OleDb，Imports Excel = Microsoft.Office.Interop.Excel

    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DataFolder + "\;Extended Properties='text;HDR=Yes'"

    Dim conn As New System.Data.OleDb.OleDbConnection(ConnectionString)
    conn.Open()

    Dim CommandText As String = CommandText = "select * from [" + CSVFileName + "]"
    If Filter.Length > 0 Then
        CommandText += " WHERE " + Filter
    End If

    Dim daAsset As New OleDbDataAdapter(CommandText, conn)
    Dim dsAsset As New DataSet
    daAsset.Fill(dsAsset, "Asset")

opening a sheet in a workbook is very similar - you specify the sheet name and can then fill a DataSet with the entire sheet - you can then access the Tables().Rows() of the DataSet to get each row and field, iterate over every row etc.

在工作簿中打开工作表非常相似 - 您指定工作表名称，然后可以使用整个工作表填充DataSet - 然后您可以访问Data（）。DataSet的Rows（）以获取每个行和字段，迭代每排等

#5

You may be interested in Excel 2007 Collaboration features (like editing an xls from the web).

您可能对Excel 2007 Collaboration功能感兴趣（例如从Web编辑xls）。

#6

Another approach would be to write an excel function that talks to the database directly and returns the result as an array.

另一种方法是编写一个直接与数据库对话的excel函数，并将结果作为数组返回。

If you think this approach would work well you could try XLLoop - this allows you to easily write excel functions in Java, Python, Ruby, Perl, R, Lisp, Erlang.

如果您认为这种方法运行良好，您可以尝试使用XLLoop - 这使您可以轻松地在Java，Python，Ruby，Perl，R，Lisp，Erlang中编写Excel函数。

#1