Excel 到 SQL（无需 JET 或 OLE）（版本 2）

• 下载 ExcelToDB 示例 - 187.55 KB
• 下载 Excel 到数据库 - 895.61 KB

引言

在 x64 时代之前，将 Excel 文档导入 Microsoft SQL Server 是一项简单的任务。
令人惊讶的是，没有可用的驱动程序（如 Jet）可以直接与 Excel 交互。

为了解决这个问题，我最初开发了一个应用程序，它可以读取 Excel 文档并将数据输入到 SQL 数据库中。
有关初始程序的详细信息，请在此处查看文章 here。

它的局限性在于它只处理 Excel 97-2003 文档。最新版本试图解决这个问题。

创建单独文章的原因是，我修改了代码库的程度，以至于它实际上是一个不同的应用程序。

背景

以前，我使用 JET 将 XLS 文件导入 SQL。2010 版的驱动程序可用，但需要先卸载 Office 产品中的 32 位驱动程序，然后才能安装 64 位驱动程序。当产品处于生产环境中且仅有 32 位应用程序可用时，这是非常危险的。

不使用 OleDB 的原因是，要导入的数据是用户大约每月创建四次。由于导入过程包含在许多作业中，因此创建一个基于已定义结构读取和导入数据的过程，而不是依赖用户设置链接到表的命名对象，会更简单。

特别感谢

由于工作中的策略，SQL 服务器上未安装 Microsoft Office，因此我需要一个不需要 Office Interop DLL 的 Excel 读取器。我发现 Excel Reader 类（由 Liu-Junfeng 创建）易于使用和实现。

不幸的是，它还没有读取 Excel 2007 文档。但是，得益于 ExcelPackage 类（由 John Tunnicliffe 创建），我能够扩展该应用程序的支持。

工作原理

ExcelDB 类打开一个 Excel 文档，并将指定的行读取到 DataTable 中。

第一个任务是决定如何从一个未知的 Excel 电子表格中定义结构。
我决定最好以此为基础，使用一个已结构化的 DataTable，例如在 SQL 2005/2008 数据库中创建的 DataTable。

然后，代码将 Excel 电子表格读取到 DataTable 中，并将信息写回用于创建初始 DataTable 的表中。

ExcelToDB 类

ExcelDB 类是包装器，负责读取 Excel 文档并根据需要将其输入到 SQL Server 中。
构造函数负责通过使用 Workbook 文件扩展名来检查 Excel 文件的正确版本，然后实例化适当的 ExcelBaseHandler 类。

//97-2003 Excel File Handler
string Extension = System.IO.Path.GetExtension(FileName).ToLower();
if (Extension == ".xls")
{
    ExcelHandler = new Excel97to2003Handler
	(FileName, ConnInfo, TrunctTBL, X_StartRow, X_EndRow);
}
//2007+ Excel File Handler
else if (Extension == ".xlsx")
{
    ExcelHandler = new Excel2007UpHandler
	(FileName, ConnInfo, TrunctTBL, X_StartRow, X_EndRow);
}
//Any other file type
else
{
    WriteError(new NotSupportedException
	("This version ExcelToDB only Supports .xls and .xlsx files"));
    return;
}

ExcelDB 类之后只需要有一个 ExcelBaseHandler 实例的引用就可以运行 Excel 工作簿的两个（或将来）版本。

• SupportedFileTypes：ExcelDB 类支持的扩展名的 String 表示。它返回为 "*.xxx;*.xxy"。这样就可以直接在 *Dialog* 过滤器中使用。
• ExceptionEvnt：如果出现错误消息，则触发该事件。
• MessageEvnt：发送任何消息时触发该事件。
• ReadExcelIntoDatabase(int SheetNumber)：该过程负责利用 ExcelBaseHandler 类的实例将数据从 Excel 工作簿传输到 SQL Server 数据库。

ExcelBaseHandler 类

ExcelBaseHandler 类是 IExcel 接口的实现，因此它有一个可重写的过程 SpecificVersion_TableFromExcel。
此过程负责从 Excel 电子表格获取信息并将其转换为 DataTable。

为了简化此过程，我决定创建两个继承自 ExcelBaseHandler 的 Handler 类，并在此基础上重写 SpecificVersion_TableFromExcel。

• FileName：Excel 文件路径。
• TrnctTBL：**True** 或 **False** 指示是否删除 SQL 表中的所有先前信息。如果为 **True**，代码将不会请求确认，因为这是用于批量处理的。请**谨慎**使用。
• X_StartRow：Excel 电子表格中开始读取的行号。此数字必须小于 X_EndRow。数字为 -1 表示默认应用程序处理。
• X_EndRow：Excel 电子表格中结束读取的行号。此数字必须大于 X_StartRow。数字为 -1 表示应用程序应读取到最后一行。

Using the Code

在使用 DLL 文件之前，可能需要下载 OpenXML SDK 并将 DocumentFormat.OpenXml 和 WindowsBase 引用添加到项目中。

SvrDbInfo dbInfo = new SvrDbInfo("(local)\SQL", "Database", "ExcelTable");
ConnectionString ConnInfo = new ConnectionString(dbInfo, 30);

//Truncate the SQL Table prior to inputting the data?
bool TruncateTable = false;
//Does the Excel Worksheet have the 1st row as a Header?
bool ContainsHeader = false;

//The Sheet Number of the Workbook
int SheetNumberIndex = 0;

//String ExcelFile = "C:\\MyFile.xls";
String ExcelFile = "C:\\MyFile.xlsx";

ExcelDB ExcelToDatabasecls = new ExcelDB
	(ExcelFile, ConnInfo, TruncateTable, ContainsHeader);

//Assign the event listeners to each of the events
ExcelToDatabasecls.ExceptionEvnt += 
	new ExcelDB.ExceptionDelegate(ExcelToDatabasecls_ExceptionEvnt);
ExcelToDatabasecls.MessageEvnt += 
	new ExcelDB.MessageDelegate(ExcelToDatabasecls_MessageEvnt);
ExcelToDatabasecls.ReadExcelIntoDatabase(SheetNumberIndex);

private void ExcelToDatabasecls_ExceptionEvnt(Exception Error)
{
    Console.WriteLine(Error.Message);
}
private void ExcelToDatabasecls_MessageEvnt(string Message)
{
    Console.WriteLine(Message);
}

SvrDbInfo 类存储有关 SQL Server 名称、数据库名称以及将从中接收 Excel 电子表格结构并随后写入的表名称的信息。

ConnectionString 类存储有关 SQL Server 和访问的所有信息。
这包括截断表、SQL 或 Windows 身份验证等详细信息。它通过使用 SvrDbInfo 实例来实例化。

所有异常都由 ExcelBaseHandlerExcelDB 事件处理。

附加信息

为了使应用程序更易于使用，我开发了一个 *XML* 工具，该工具将创建一个设置文件，该文件可以通过开关 /XMLFile:<File> 传递给 ExcelToDatabase 控制台应用程序。

历史

• 2010 年 10 月 5 日：第一次提交