背景：最近在做和HW对接的某项目，需要将第三方接口提供的数据进行展示；在对方提供了详细的excel后，觉得也挺简单的就是将excel数据导入到数据库中。
方案一：
普通的初学者肯定会想，那我读取excel数据，一行一行的读，然后插入到数据库中进行存储，然而这样利用事务进行存储本身也没有问题；但是数据库如果涉及不规范，某列数据的数据类型（长度不够后），就会导致整个执行过程进行回滚；效率也不是很高。
excel数据存放到table中代码：
DataTable dt = new DataTable();
IWorkbook workbook;
string fileExt = Path.GetExtension(path).ToLower();
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = null;
}
if (workbook == null) return;
ISheet sheet = workbook.GetSheetAt(0);//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
}
else dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow(); bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j)); if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
遍历table数据到数据库
string connectstring = "";//数据库连接字符串
SqlConnection conn = new SqlConnection(connectstring); //数据库连接
conn.Open();
for (int i = 0; i < dt.Rows.Count; i++)
{
string sqlstr = "insert t_table (Column1,Column2,Column3,Column4,Column5) values ('" + dt.Rows[i][0].ToString()+"','" + dt.Rows[i][1].ToString() + "','"
+ dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','"+ dt.Rows[i][4].ToString() + "')";
SqlCommand com = new SqlCommand(sqlstr, conn);
if (com.ExecuteNonQuery() < 1)
{
Console.WriteLine("导入数据失败！");
System.Console.WriteLine("编码："+dt.Rows[i][0].ToString()+"导入失败！");
}
System.Console.WriteLine("编码：" + dt.Rows[i][0].ToString() + "导入成功！");
}
其中 t_table为数据库中表名称，Column1~Column5为数据库中字段名称.
方案二：
比较高效的做法是利用office自带的功能OLEDB结合OPENROWSET导入数据；具体代码如下：
string connectstring = "";//数据库连接字符串
SqlConnection conn = new SqlConnection(connectstring); //数据库连接
conn.Open();
string sqlstr = @"insert into t_HWPromptMsg select 编码, 提示, 场景,提示信息,备注 from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE="+ path + "',sheet1$)";
SqlCommand cmd = new SqlCommand(sqlstr, conn);
cmd.ExecuteNonQuery();
conn.Close();
注意事项：在利用 OPENROWSET时需要打开SQL Server的Ad Hoc Distributed Queries组件，否则会报：报错内容是：SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。
具体执行步骤：
1.开启Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
2.执行c#程序代码
3.关闭Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
