6万笔数据瞬间导入进DB
命名空间
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
//Filename为文件路径
public JsonResult ConsumptionUploadcoupon(string filename)
{
CouponManageBLL _bll = new CouponManageBLL();
var result = new Result<string>();
result.flag = false;
string Msg = string.Empty;
try
{
CouponManageBLL _BLL = new CouponManageBLL();
BLL.DeleteTempMember(); //先删除临时表信息
DataTable excelTable = new DataTable();
excelTable = GetExcelDataTable(filename, id); //将文件的数据导入datatable
//插入数据
InsertMemberInfo(excelTable, id);
result.flag = true;
}
catch (Exception ex)
{
Log(ex);
result.msg = ex.Message;
throw;
}
return Json(result, JsonRequestBehavior.AllowGet);
}
//将文件数据转为DATATABLE 储存方法
public DataTable GetExcelDataTable(string filePath, int id)
{
IWorkbook Workbook;
DataTable table = new DataTable();
try
{
using (FileStream fileStream = new FileStream(Server.MapPath("~" + filePath), FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
string fileExt = Path.GetExtension(filePath).ToLower();
if (fileExt == ".xls")
{
Workbook = new HSSFWorkbook(fileStream);
}
else if (fileExt == ".xlsx")
{
Workbook = new XSSFWorkbook(fileStream);
}
else
{
Workbook = null;
}
}
}
catch (Exception ex)
{
throw ex;
}
//定位在第一个sheet
ISheet sheet = Workbook.GetSheetAt(0);
//第一行为标题行
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
int rowCount = sheet.LastRowNum;
//循环添加标题列
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
table.Columns.Add("coupon_id");
//数据
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = GetCellValue(row.GetCell(j));
dataRow[j + 1] = id;
}
}
}
table.Rows.Add(dataRow);
}
return table;
}
//分页存放数据
public void InsertMemberInfo(DataTable dt, int id)
{
int index = 60000;
if (dt.Rows.Count >= index)
{
int count = dt.Rows.Count / index;
for (int i = 1; i <= count; i++)
{
DataTable dts = GetPagedTable(dt, i, index);
InsertDateTable(dts, id);
}
}
else
{
InsertDateTable(dt, id);
}
}
//与DB表对应进行数据映射
public void InsertDateTable(DataTable dt, int id)
{
string connectionString = ConfigurationManager.AppSettings["SQLConnection"];
// "Data Source =192.168.1.111; Initial Catalog =DB库名; Persist Security Info =True; User ID =sa; Password =123456";
using (SqlConnection destinationConnection = new SqlConnection(connectionString))
{
destinationConnection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
{
try
{
bulkCopy.DestinationTableName = "rsl_temp_coupon";//要插入的表的表名
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.ColumnMappings.Add("coupon_no", "coupon_no");//映射字段名 DataTable列名 ,数据库 对应的列名
bulkCopy.ColumnMappings.Add("coupon_id", "coupon_id");
bulkCopy.WriteToServer(dt);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
}
}
}
}
注意导入进DB 数据的栏位一定要一致，否则导入失败。 如有好的建议请在下方评论