65.9K
CodeProject 正在变化。 阅读更多。
Home

使用OLEDB读写Excel

Dieder Timmerman
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.61/5 (51投票s)

2004年10月7日

4分钟阅读

viewsIcon

902007

downloadIcon

26565

展示了如何使用 OLEDB 来读取和写入 Excel 工作簿文件。

Application screenshot

引言

该项目包含一个 `ExcelReader` 类。此类使用 OLEDB 驱动程序读取 Excel 文件。关于此已有许多文章。然而，此类是一种读取和写入 Excel 值的便捷方法。可以读取或写入单个值或数据表。

然而，由于 Excel 驱动程序的限制，无法删除表中的行。更新空范围也不是一个选项。可以读取一个范围并更新或插入一个现有范围。Excel 有其自己的数据类型化列的方式。DaberElay 根据我的文章做出了回应，其中

这是如何发生的？

显然，引擎会读取每列的前 8 个单元格并检查其数据类型。如果前 8 个单元格中的大多数是整数/双精度，则问题依然存在。

这可以解决吗？可以，也可以不可以。我们可以要求引擎检查超过 8 个单元格（通过设置注册表值

HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows 为 0，这将检查前 16,000 行，并会产生少许性能损失）。

但是，如果您的前 16000 行全是数字，之后才出现文本值，那么您就遇到了问题。

我们还可以做的另一件事是设置 TypeGuessRows 为 1，并将连接字符串的扩展属性 HDR 设置为 No，这样如果您总是有 Excel 中的标题行，它将读取第一行并将其识别为文本字段。

请注意，这意味着您必须从现在拥有的额外第一行（现在是数据行）创建列名。

如果 OLEDB 解决方案不适合您的需求，您可以购买一个组件。有一些组件可以在没有 MS Excel 的情况下读写 Excel 文件，并且可以进行编辑。

这里有一些建议。

背景

在一个项目中，我需要在 Web 服务器上读取和写入 MS Excel 文件。MS Excel 文件将被上传并在服务器上读取到 SQL Server 数据库中。

通常，我会使用 Microsoft 发布的 XML 语法 用于 Web。不幸的是，这是 MS Excel 2002 或更高版本支持的语法。这使得创建一个组件来修改和读取 Excel 工作簿变得更加容易。只是在我的项目中，客户总是使用不支持此 XML 的旧软件，如 MS Excel '97。我更喜欢选择一个仅使用一个版本的 Excel 2002 和一个编程转换器类的解决方案。但是这个过程将在 Web 服务器上运行，而 MS Office 及其组件不可扩展且不允许。也请阅读 Microsoft 关于此问题的说明。因此，我开始编写一个使用 OLEDB 驱动程序的类，该驱动程序可以对上传的 Excel 文件执行一些基本任务。

使用代码

演示窗体使用以下代码来初始化 `ExcelReader` 类

exr = new ExcelReader();
_dt = new DataTable("par");
exr.KeepConnectionOpen =true;
exr.ExcelFilename = _strExcelFilename;
exr.Headers =false;
exr.MixedData =true;
exr.SheetName = this.txtSheet.Text;
exr.SheetRange = this.txtRange.Text;
exr.SetPrimaryKey(0);
_dt = exr.GetTable();

首先，创建一个此类的新实例。同时声明一个 `DataTable`。我更倾向于将其作为私有类变量。更新网格后，我将使用表变量通过 `ExcelReader` 类来更新表。`keepconnection` 属性会在 `ExcelReader` 操作后保持连接打开，并节省时间。标题选项表示，MS Excel 中是否有标题行来解释列数据。`MixedData` 属性使用 `IMEX` 选项（0=导出，1=导入，2=链接）。默认情况下，该属性为 true，`IMEX` =2。如果为 false，则连接字符串中没有 `IMEX` 选项。还要设置工作表名称和范围。

主键对于更新 Excel 工作表是必需的。它现在只支持一个主键，但该类可以扩展。如果表没有主键，`DataAdapter` 将无法工作。Excel 驱动程序不会发现主键，因此必须手动设置。`DataColumnNumber` 0 是范围设置的第一个列。`GetTable()` 以 `DataTable` 的形式返回请求的 Excel 范围的数据。可以通过 `SetTable(DataTable)` 方法更新 Excel 文件本身中的范围。只需下载演示并查看。

如何实现

首先，设置连接

private string ExcelConnection()
{
    return
        @"Provider=Microsoft.Jet.OLEDB.4.0;" + 
        @"Data Source=" + _strExcelFilename  + ";" + 
        @"Extended Properties=" + Convert.ToChar(34).ToString() + 
        @"Excel 8.0;"+ ExcelConnectionOptions() + Convert.ToChar(34).ToString(); 
}
#endregion

打开连接

_oleConn = new OleDbConnection(ExcelConnection());
_oleConn.Open();

然后创建一个 `OledbCommand`，文本类似 select * from [sheetname$[range]。

_oleCmdSelect =new OleDbCommand(
    @"SELECT * FROM [" 
    + _strSheetName 
    + "$" + _strSheetRange
    + "]", _oleConn);

使用 `select` 命令填充表以实际检索数据

OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
oleAdapter.SelectCommand = _oleCmdSelect;
DataTable dt = new DataTable(strTableName);
oleAdapter.FillSchema(dt,SchemaType.Source);
oleAdapter.Fill(dt);

更新表

首先设置主键。然后调用 update 方法来更新 excel 表。在演示中尝试更新一个空范围！将发生错误。
if (this._intPKCol>-1)
{
    int[] intPKCols = new int[]  { _intPKCol};
    _exr.PKCols = intPKCols;
}
_exr.SetTable(_dt);

历史

  • 1.1 修复了一些 bug 并添加了一些函数
    • 可以通过方法调用检索 Excel 工作表名称。
    • 用于检索实际 Excel 列名或列号的函数。
    • 修复了连接字符串和 `SetSheetQueryAdapter` 中的一些 bug。
  • 1.0 初始 `ExcelReader` 类。
© . All rights reserved.