Excel 到 SQL（无需 JET 或 OLE）

• 下载 ExcelToDB ConsoleEg - 98.91 KB
• 下载解决方案 - 130.2 KB

引言

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

为了克服这个问题，我设计了一个类来协助自动将 Excel 文档导入 SQL Server。

背景

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

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

特别感谢

由于公司政策，SQL Server 上未安装 Microsoft Office，因此我需要一个不需要 Office Interop DLL 的 Excel 读取器。我发现 Excel Reader 类，由 Liu-Junfeng 创建，易于使用和实现。

不幸的是，它尚不能读取 Excel 2007 文档，但它完成了应用程序所需的一切。

工作原理

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

第一个任务是决定如何定义来自未知 Excel 电子表格的结构。
我决定完成此任务的最佳方法是基于已结构化的 DataTable，例如在 SQL 2005/2008 数据库中创建的表。

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

ExcelDB 类

您将使用 ExcelDB 类将 Excel 文档读取到 SQL 中

• FileName：要读取的 Excel 文件的位置和名称
• TrnctTBL：指示 ReadExcelIntoDatabase() 过程是否删除表中的所有信息
• X_Header：指示第一行是否为标题（将从第二行读取）。
• X_StartRow：Excel 文档中要读取的第一行。此值将覆盖 X_Header
• X_EndRow：Excel 文档中要读取的最后一行

• ExceptionEvnt：发生错误消息时触发的事件
• MessageEvnt：发送任何消息时触发的事件

ExcelDB 类中发生的所有消息和错误都通过事件进行处理。

ConnectionString 类

ConnectionString 类用于存储连接到 SQL Server 的详细信息。

• D_Info：获取或设置与数据库相关的 SvrDBInfo 信息
• Password：获取或设置 SQL Server 用户名的密码
• UserName：获取或设置连接到 SQL 数据库的用户名
• Timeout：获取或设置连接（和命令）超时值（以秒为单位）
• TrustedConnection：获取连接是否使用 Windows 身份验证的值

SvrDBInfo 类

SvrDBInfo 用于存储 SQL Server 的详细信息。

• SVRName：获取或设置 SQL Server 名称
• DBName：获取或设置数据库名称
• TBLName：获取或设置表名称

Using the Code

在您的项目中，添加对 ExcelToDB.dll 的引用。在您的代码中，导入 ExcelToDB 命名空间。

using ExcelToDB;

使用 Server、Database 和 Table 信息创建 SvrDBInfo 类实例

SvrDbInfo D_Info = new SvrDbInfo("(local)\SQL", "ADatabase", "MyTempTable");

使用上述 SvrDBInfo 的 D_Info 实例创建 ConnectionString 类实例

//Creates a Windows Authenticated ConnectionString instance
ConnectionString ConnString = new ConnectionString(D_Info, 30);
//Creates a SQL Authenticated ConnectionString instance
//Using 'SA' for sake of argument.
ConnectionString ConnString = new ConnectionString(D_Info, 30, "SA", "SAPassword");

创建好这些之后，我们就可以创建 ExcelToDB 类了

//This will create the Class instance with just the default values.
//That is 
//    1. Read all rows.
//    2. The first row is a Header, read from the 2nd
//    3. Delete previous information from the Table 
//       (if the ReadExcelIntoDatabase() is called)
ExcelDB ExcelToDBcls = new ExcelDB("C:\MyFile.xls", ConnString);

完成后，我们添加一些消息处理程序以向用户显示消息

private void button1_click()(object sender, eventargs e)
{
    SvrDbInfo D_Info = new SvrDbInfo("(local)\SQL", "ADatabase", "MyTempTable");
    //Creates a Windows Authenticated ConnectionString instance
    ConnectionString ConnString = new ConnectionString(D_Info, 30);
    /Creates a SQL Authenticated ConnectionString instance
    //Using 'SA' for sake of argument.
    //Commented out so as to show construction for two different connections.
    //ConnectionString ConnString = new ConnectionString
    // (D_Info, 30, "SA", "SAPassword");
    
    //This will create the Class instance with just the default values.
    //That is 
    //    1. Read all rows.
    //    2. The first row is a Header, read from the 2nd
    //    3. Delete previous information from the Table 
    //       (if the ReadExcelIntoDatabase() is called)
    ExcelDB ExcelToDBcls = new ExcelDB("C:\MyFile.xls", ConnString);
    
    //Assign the event listeners to each of the events
    ExcelToDBcls.ExceptionEvnt += new ExcelDB.ExceptionDelegate
					(ExcelToDBcls_ExceptionEvnt);
    ExcelToDBcls.MessageEvnt += new ExcelDB.MessageDelegate(ExcelToDBcls_MessageEvnt);
    //Read the Excel Document, and write it to the Table.
    ExcelToDatabasecls.ReadExcelIntoDatabase();
}

/// <summary>
/// ExcelToDBcls instance Message Event Listener
/// </summary> 
/// <param name="args">ExcelDB.MessageEvnt Event Argument</param>
private static void ExcelToDBcls_MessageEvnt(ExcInfo_EventArgs args)
{
    //Write the message to the Console.
    Console.Write(args.Message);
}
/// <summary>
/// ExcelToDBcls instance Exception Event Listener
/// </summary> 
/// <param name="args">ExcelDB.ExceptionEvnt Event Argument</param>
private static void ExcelToDBcls_ExceptionEvnt(ExcError_EventArgs args)
{
    //Create the Error Message and post to the Error within Console.
    StringBuilder SB = new StringBuilder();
    SB.AppendLine(args.Error.Message);
    SB.AppendLine(args.Error.StackTrace);
    Console.Error.Write(SB.ToString());
    SB = null;
}

其他说明

请注意，使用 Console eg，仅使用默认值将删除表中先前的数据。
请添加开关 /TrnctTBL=F。

使用 SQL Server 2005

可以使用 SQL 的 xp_cmdshell 来运行应用程序。
xp_cmdshell 通常不建议启用，因为它可能被利用，存在安全漏洞。
为了解决这个问题，请仅在需要时将其开启。

开启 xp_cmdshell

--Reconfigure so that xp_cmdshell is on
USE master 
GO 
EXEC sp_configure 'show advanced options', 1 
GO 
RECONFIGURE WITH OVERRIDE 
GO 
EXEC sp_configure 'xp_cmdshell', 1 
GO 
RECONFIGURE WITH OVERRIDE 
GO 
EXEC sp_configure 'show advanced options', 0 
GO

创建 Table 并导入数据

--Create the Table and Import the Data
Use Data
Go
Create Table SQLImportTable 
(
    ExcelColumn1 varchar(5), 
    ExcelColumn2 varchar(10), 
    ExcelColumn3 varchar(20), 
    ExcelDateTimeColumn smalldatetime
)

Declare @ExeLocation varchar(255),
        @Arguments varchar(255)

Set @ExeLocation = 'C:\ExcelToDB\ExcelToDatabase.exe'
Set @Arguments = '/SVRName:Server\SQL '+
                 '/DBName:Data ' +
                 '/TBLName:SQLImportTable ' +
                 '/FileName:C:\ExcelToDB\ExcelReport.xls'

Set @ExeLocation = @ExeLocation + ' ' + @Arguments
exec Master..xp_cmdshell @ExeLocation 
GO

重新配置，使 xp_cmdshell 处于关闭状态

--Reconfigure so that xp_cmdshell is off
USE master
GO 
EXEC sp_configure 'show advanced options', 1 
GO 
RECONFIGURE WITH OVERRIDE 
GO 
EXEC sp_configure 'xp_cmdshell', 0
GO 
RECONFIGURE WITH OVERRIDE 
GO 
EXEC sp_configure 'show advanced options', 0 
GO

使用 CLR 函数运行应用程序

Xiangyang Liu 刘向阳 创建了一个 CLR DLL 文件，用于从 SQL Server 运行带参数的应用程序。
它被称为 XYRunProc。如果您更倾向于使用 CLR 集成而不是 xp_cmdshell，这里是相关文章。

历史

• 2010 年 7 月 13 日 - 为 ConsoleEg 添加了命令行参数文档。
• 2010 年 7 月 8 日 - 文章首次发布