炒鸡快速获取EXCEL数据

炒鸡快速获取EXCEL数据

一般我们要获取excel数据时，会采用COM组件（Excel.Application）来实现，当数据量相当大时（几万、几十万），将变得非常慢。

为了获得更高的性能，我们可以使用Microsoft.Ace.OleDb.12.0来快速连接excel文件，该方式采用数据连接的机制连接excel文件，相对于Excel.Application打开后用循环插入数据的方式，提升几百几千的效率（曾经优化过一段代码，导入4万行数据，从10分钟到4秒）。
示例：

"SELECT * FROM OPENDATASOURCE ('Microsoft.Ace.OleDb.12.0','" & _
"Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""" & _
";Data Source=""" & filePath & """' )..." & _
"[sheet$]"
’filePath为路径变量。
'sheet为excel文件的工作表名称，$不能省略。

备注
"Microsoft.Ace.OleDb.12.0"实际上连接了数据库，而excel数据作为外部表进行引用，在使用时，可能会出现不能使用的情景。
比如下图所示：

为避免出现上图的错误，代码应加入如下些代码：

Csql.DoSql("EXEC sp_configure 'show advanced options', 1;RECONFIGURE;")
Csql.DoSql("EXEC sp_configure 'Ad Hoc Distributed Queries', 1;")
Csql.DoSql("RECONFIGURE;")
Dim strSQL As String = "SELECT ID,品项编码,生产日期,数量 " & _
                        "FROM OPENDATASOURCE ('Microsoft.Ace.OleDb.12.0','" & _
                        "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""" & _
                        ";Data Source=""" & filePath & """' )..." & _
                        "[sheet$]"
Dim tt As DataTable = Csql.GetTable(strSQL)
Csql.DoSql("EXEC sp_configure 'Ad Hoc Distributed Queries', 0;")
Csql.DoSql("RECONFIGURE;")
Csql.DoSql("EXEC sp_configure 'show advanced options', 0;RECONFIGURE;")

上述代码的大概原理是，放开安全设置，引用外部表，重新安全设置（有始有终对吧）。

既然作为外部表，当然也支持与数据库的表进行join连接操作啦，如下所示：

        Dim strSQL As String = "SELECT '' AS 状态, 库位明细_1.库位, 库位明细_1.层, 库位明细_1.数量," & _
            " 库位明细.库位 AS 实际库位, 库位明细.层 AS 实际层,库位明细.数量 AS 实际数量,F13 AS 栈板ID," & _
            "库位明细_1 .state,库位明细.state as newSta" & "F14 AS 订单ID,F6 AS 新栈板ID " & _
            "FROM OPENDATASOURCE ('Microsoft.Ace.OleDb.12.0','Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""" & _
            ";Data Source=""" & filePath & """' )..." & _
            "[sheet1$] AS derivedtbl_1 LEFT OUTER JOIN 库位明细 ON derivedtbl_1.F6 = 库位明细.ID " & _
            "LEFT OUTER JOIN 库位明细 AS 库位明细_1 ON derivedtbl_1.F13 = 库位明细_1.ID where F6<>'栈板号'"
        Dim tt As DataTable = Csql.GetTable(strSQL)

是不是也很简单，真的是炒鸡快速的，赶紧去体验吧！
特别是结合我的上一篇博客“批量插入数据（dataTable至SQL SERVER）”
https://blog.csdn.net/haigecnpeng/article/details/86584066
本文的示例代码使用了第三方的程序集（pgzzCnn.dll），使用其它方式的可参考修改，原理是一样一样的。

如果大家觉得这些示例还是麻烦的话，可以花9.9去购买一个pgzzCnn.dll，该库使用炒鸡简单，包含了几乎所有的数据库操作，支持SqlClient、OleDb和Odbc方式，支持连接SQLSERVER、ACCESS、Oracle以及MYSQL，支持SELECT、UPDATE、INSERT操作，特别是支持批量写入（datatable到数据库）和数据库事务处理。
使用示例：
公共模块声明：

Private strConn As String = "Data Source=服务器实例;Initial Catalog=数据库;User ID=sa;Password=密码"
Public Csql As pgzz.sqlserver		’ 不同数据源可声明为对应的方式oledb/odbc

获取表：

Dim strSQL As String = "select * from 表1 where 查询条件"
Dim tt As DataTable = Csql.GetTable(strSQL)

更新或插入：

Dim strSQL As String = "update 表 set 字段=’值’ where 条件"
Dim exCount As integer = Csql. DoSql (strSQL)		‘dosql返回受影响的行数

批量插入（sqlserver支持，其它不支持）：

dim sw as long = Csql. DoSql(newTable,tableName)	‘这里返回的是执行的时间（ms）,如返回0则代表出错了。
'tableName为目标表（sqlserver中的表名）。
‘newTable为待更新的数据源，可以是任意数据源，但需与tableName结构一致。

数据库事务：

		Dim strSQL As String
        Dim strL As New List(Of String)
		strSQL = " insert into 表1(字段1,字段2……) values(值1,……)"
        strL.Add(strSQL)
		strSQL = " update表2 set 字段1=值 where 条件"
        strL.Add(strSQL)
        If Csql.DoSql(strL) Then
			‘执行成功！
        Else
            ‘执行失败！
        End If

是不是炒鸡简单呢？
9块9，买不了吃亏，买不了上当，pgzzCnn，你值得拥有（需要源码可以跟店主私聊）。
闲鱼链接：
https://market.m.taobao.com/app/idleFish-F2e/widle-taobao-rax/page-detail?wh_weex=true&wx_navbar_transparent=true&id=586152596728&ut_sk=1.WfFNPGwSx70DACo2hZcTFezZ_21407387_1548072402925.Copy.detail.586152596728.320966645&forceFlush=1
或在闲鱼搜索
“数据库使用程序集”