将Excel数据导入SQL Server数据库

遇到了一个需求，需要将Excel中的数据导入SQL Server数据库中，当然，不是使用企业管理器之类的直接导入数据的那种，而是要做到程序中。本来我最初的考虑是将Excel中的数据读到dataset中，然后分别插入到数据库中。

从Excel中读取数据并不太难，简单来说就是下面的几句代码而已：

 

 public DataSet ExcelToDataset(string path)
        {
            try
            {
string conStringExcel = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=NO;\"";
string sql = "select * from [Sheet1$]";
                DataSet ds = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(sql, conStringExcel);
                da.Fill(ds);
                return ds;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

path为Excel的路径。

 

像其他的情况，比如多个sheet啊，工作区域啊什么的，在园内查一下就可以了，并不太难。

不过考虑到这样有些麻烦，而且基本上Excel的格式和sheet是固定的，所以能不能使用别的方法解决呢？

在园内查了一下，园内的以为大神给出了一个存储过程，使用的是连接服务器的方式，基本代码如下：

 

ALTER PROCEDURE [dbo].[pro_xls]
@sheetName varchar(50),--Excel中sheet名
@path varchar(2000)--Excel的路径
as

/**//*
sp_addlinkedserver--创建一个链接的服务器，使其允许对分布式的、针对 OLE DB 数据源的异类查询进行访问
@server='USER_LIST',--被访问的服务器别名
@srvproduct='USER_LIST',--要添加为链接服务器的 OLE DB 数据源的产品名称
@datasrc='d:\file\Users.xls'   --要访问的服务器
*/
EXEC sp_addlinkedserver @server='server', @srvproduct='Excel', @provider='Microsoft.Jet.OLEDB.4.0', @datasrc=@path, @provstr='Excel 8.0;HDR=no;IMEX=1;'
EXEC sp_addlinkedsrvlogin 'server','false'
/**//*
当 SET XACT_ABORT 为 ON 时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。 
当 SET XACT_ABORT 为 OFF 时，有时只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。如果错误很严重，那么即使 SET XACT_ABORT 为 OFF，也可能回滚整个事务。
编译错误（如语法错误）不受 SET XACT_ABORT 的影响。
*/
SET XACT_ABORT ON

BEGIN DISTRIBUTED TRAN

    insert into test (testName,testPwd,Istest) select * from [@sheetName$]
IF @@ERROR > 0
    GOTO ChkRollBack

ChkRollBack:
IF @@ERROR > 0
    ROLLBACK TRAN
ELSE
    COMMIT TRAN

SET XACT_ABORT OFF
EXEC sp_dropserver 'server',null

上面的代码是拿的园内大神的并自己修改的，不过貌似还是有点问题，主要是对sp_addlinkedserver之流的存储过程不太明白，几个参数不知道是做什么的。

 

恩，有点头疼了。

结果又发现了一种方式，貌似叫做即席查询的东西，代码写起来就简单了：

 

--如果是导入数据到现有表，则采用

INSERT INTO 表 SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)


--如果是导入数据并新增表，则采用

SELECT * INTO 表 FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)


--以上语句是将 EXCEL文件里 SHEET1工作表中所有的列都读进来，如果只想导部分列，可以

INSERT INTO 表 (a1,a2,a3) SELECT a1,a2,a3 FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)

 

分布式查询传送门

 

---

我这里只是起到一个抛砖引玉的作用，希望各位园内大神不吝赐教。