excel - 是否有相当于 SCHEMA.INI 来阅读 Excel 工作簿

Question

我目前正在从事一个项目，该项目将以各种格式和结构从多个不同来源导入数据 -例如，CSV、固定长度、其他分隔（制表符、管道等）纯文本和 Excel 工作表/工作簿。为此，我正在尝试为这些文件构建“通用”阅读器，这会将文件的内容放入我可以在其他方法中使用的DataTable/中。DataSet纯文本文件非常简单，因为我创建了一个大SCHEMA.INI文件，其中包含系统将处理的每个文件的字段定义。它SCHEMA.INI驻留在“处理文件夹”中，文件临时存储在其中，直到它们的数据与其他系统集成。使用此方法可以轻松提取定义的文本文件的数据：

Private Function TextFileToDataTable(ByVal TextFile As IO.FileInfo) As DataTable
    Dim TextFileData As New DataTable("TextFileData")

    Using TapeFileConnect As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source='" + TextFile.DirectoryName + "';Extended Properties='Text';")
        Using TapeAdapter As New OleDb.OleDbDataAdapter(String.Format("SELECT * FROM {0};", TextFile.Name), TapeFileConnect)
            Try
                TapeAdapter.Fill(TextFileData)
            Catch ex As Exception
                TextFileData = Nothing
            End Try
        End Using
    End Using

    Return TextFileData
End Function

这很有效，因为纯文本文件的数据结构并不十分复杂。单个文件通常（至少对于我的要求）最多包含一个表的数据价值 -当然，除非它是某种复杂的 XML 或 JSON 结构文件，无论如何都可以/应该完全不同地处理它们- 所以除此之外，没有必要遍历不同的元素。

注意： 上面的代码依赖于与SCHEMA.INI正在读取的纯文本文件位于同一目录中的文件，并且该文件中有一个SCHEMA.INI与该纯文本文件同名的部分。

例子：

[EXAMPLE_TEXT_FILE.TXT]
CharacterSet=ANSI
Format=FixedLength
ColNameHeader=FALSE
DateTimeFormat="YYYYMMDD"

COL1=CUSTOMER_NUMBER TEXT WIDTH 20
COL2=CUSTOMER_FIRSTNAME TEXT WIDTH 30
COL3=CUSTOMER_LASTNAME TEXT WIDTH 40
COL4=CUSTOMER_ADDR1 TEXT WIDTH 40
COL5=CUSTOMER_ADDR2 TEXT WIDTH 40
COL6=CUSTOMER_ADDR3 TEXT WIDTH 40
...

然而，Excel 工作簿可能有点棘手。我必须处理的几个工作簿包含多个工作表的数据，我想将这些数据合并为一个DataSet，DataTable每个工作表都有一个。同样，基本功能相当简单，我想出了以下方法将任何和所有工作表读入DataSet：

Private Function ExcelFileToDataSet(ByVal ExcelFile As IO.FileInfo, ByVal HasHeaderRow As Boolean) As DataSet
    Dim ExcelFileData As New DataSet("ExcelFileData")
    Dim ExcelConnectionString As String = String.Empty
    Dim UseHeaders As String = "NO"

    Select Case ExcelFile.Extension.ToUpper.Trim
        Case ".XLS"
            ExcelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"
        Case ".XLSX"
            ExcelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"
    End Select

    If HasHeaderRow Then
        UseHeaders = "YES"
    End If

    ExcelConnectionString = String.Format(ExcelConnectionString, ExcelFile.FullName, UseHeaders)

    Try
        Using ExcelConnection As New OleDb.OleDbConnection(ExcelConnectionString)
            Dim ExcelSchema As New DataTable

            ExcelConnection.Open()
            ExcelSchema = ExcelConnection.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, Nothing)

            For Each ExcelSheet As DataRow In ExcelSchema.Rows
                Dim SheetTable As New DataTable

                Using ExcelAdapter As New OleDb.OleDbDataAdapter
                    Dim SheetName As String = ExcelSheet("TABLE_NAME").ToString
                    Dim ExcelCommand As New OleDb.OleDbCommand

                    SheetTable.TableName = SheetName.Substring(0, SheetName.Length - 1)
                    ExcelCommand.Connection = ExcelConnection
                    ExcelCommand.CommandText = String.Format("SELECT * FROM [{0}]", SheetName)
                    ExcelAdapter.SelectCommand = ExcelCommand
                    ExcelAdapter.Fill(SheetTable)
                End Using

                ExcelFileData.Tables.Add(SheetTable)
            Next ExcelSheet
        End Using
    Catch ex As Exception
        ExcelFileData = Nothing
    End Try

    Return ExcelFileData
End Function

上面的代码在我处理的大多数情况下都可以工作，但我的“困难”是可能有一些工作表有标题行，而一些工作表不在同一个工作簿中。此外，对于那些没有标题行的工作表，我希望能够定义字段名称和数据类型，类似于我使用纯文本的方式SCHEMA.INI。在这些情况下，我唯一要做的就是“客户端”为我提供了一个数据映射，以帮助我识别每个字段中的数据元素。

我想知道的是是否有类似于文本文件的方法SCHEMA.INI来定义 Excel 工作簿及其包含的工作表的结构 - 包括列数据类型以避免 OleDb 驱动程序“误解”列的数据——提前。我想这可能是任何类型的结构化文件，例如INI,XML或其他任何类型的文件，但它需要能够识别特定工作表是否包含标题行，或者代替这样的行，（预期的）列定义。Excel 工作簿是否存在任何此类“标准定义”文件？

需要注意的一件事：正如您在该方法的代码中可能已经注意到的那样ExcelFileToDataSet()，我可能正在处理较旧的.XLS(97-03) 格式或.XLSX(07+) 格式，因此我不一定要依赖打开的工作簿符合 XML。我想我可以尝试将每个扩展名的方法分解为一个，但我宁愿找到一些我可以使用的东西，而不管 Excel 文件使用的是哪种文件格式。

标签： excelvb.netschemaoledb