使用技术栈:NPOI+Sql Bulk Copy+C#
使用场景:
用户导入Excel,一般数据比较大为了效率用Sql Bulk Copy进行批量写入。
public static bool SqlBulkCopyByDataTable(DataTable sourceDataTable, int batchSize = 10000)
{
try
{
string connectionString = ConfigurationManager.AppSettings["DBConnectString"];
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
#region 注释Code:这样使用事务代码执行无问题,但是会出现WriteToServer写不进SQL
//SqlTransaction tran = connection.BeginTransaction();
//using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, tran))
#endregion
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
{
sqlBulkCopy.DestinationTableName = sourceDataTable.TableName;
sqlBulkCopy.BatchSize = batchSize;
for (int i = 0; i < sourceDataTable.Columns.Count; i++)
{
sqlBulkCopy.ColumnMappings.Add(sourceDataTable.Columns[i].ColumnName, sourceDataTable.Columns[i].ColumnName); //Table与sql Table字段一致
}
sqlBulkCopy.WriteToServer(sourceDataTable);
}
}
return true;
}
catch (Exception ex)
{
return false;
throw ex;
}
}
/// <summary>
/// 解析来访登记Excel的数据
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static DataTable Get_Visit_Registration_Excel(string filePath)
{
IWorkbook wk = null;
string extension = System.IO.Path.GetExtension(filePath);
DataTable dt = new DataTable();
dt = GetRegistrationDataTable();
try
{
using (FileStream fs = File.OpenRead(filePath))
{
if (extension.Equals(".xls"))
{
//把xls文件中的数据写入wk中
wk = new HSSFWorkbook(fs);
}
else
{
//把xlsx文件中的数据写入wk中
wk = new XSSFWorkbook(fs);
}
//读取当前表数据
ISheet sheet = wk.GetSheetAt(0);
for (int i = sheet.FirstRowNum + 2; i <= sheet.LastRowNum; i++) // 下标从0开始,行数加2 模版从第三行开始
{
DataRow row = dt.NewRow();
row["Company"] = sheet.GetRow(i).GetCell(1).ToString();//此处为Excel中的数据,按实际坐标来获取
dt.Rows.Add(row);
}
}
return dt;
}
catch (Exception ex)
{
throw new Exception("Get_Visit_Registration_Excel:" + ex.Message);
}
}
/// <summary>
/// 初始化Visit_Registration DataTable结构
/// </summary>
/// <returns></returns>
private static DataTable GetRegistrationDataTable()
{
DataTable dt = new DataTable();
dt.TableName = "Visit_Registration";
dt.Columns.Add("Company");
return dt;
}