1、点击引用右键，管理NuGet程序包，输入NPIO，选择红框选中的下载
2、创建一个 ExcelController 控制器，将Index 方法生成视图
3、批量导入excel的页面源码：
<h2>批量导入excel</h2>
<div>
@using (Html.BeginForm("Import", "Excel", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<input name="files" type="file" multiple="multiple" id="=file" />
<br />
<br />
<input name="submit" id="submit" type="submit" value="批量导入" />
}
</div>
这里需要注意：file 的multiple属性是能多个文件选中，enctype = "multipart/form-data" 是上传必须的表单属性
4、在ExcelController 控制器里面添加方法
[HttpPost]
public ActionResult Import(IEnumerable<HttpPostedFileBase> files)
{
// var fileName = file.FileName;
var filePath = Server.MapPath(string.Format("~/{0}", "Files"));
foreach (var file in files)
{
if (file != null && file.ContentLength > 0)
{
var path = Path.Combine(filePath, file.FileName);
file.SaveAs(path);
DataTable excelTable = new DataTable();
excelTable = ImportExcel.GetExcelDataTable(path);
DataTable dbdata = new DataTable();
dbdata.Columns.Add("ids");
dbdata.Columns.Add("users");
dbdata.Columns.Add("area");
dbdata.Columns.Add("school");
dbdata.Columns.Add("classes");
dbdata.Columns.Add("name");
dbdata.Columns.Add("phone");
dbdata.Columns.Add("integration");
dbdata.Columns.Add("states");
dbdata.Columns.Add("createDate");
dbdata.Columns.Add("refreshDate");
for (int i = 0; i < excelTable.Rows.Count; i )
{
DataRow dr = excelTable.Rows[i];
DataRow dr_ = dbdata.NewRow();
dr_["ids"] = dr["ID"];
dr_["users"] = dr["用户"];
dr_["area"] = dr["区域"];
dr_["school"] = dr["学校"];
dr_["classes"] = dr["班级"];
dr_["name"] = dr["姓名"];
dr_["phone"] = dr["手机"];
dr_["integration"] = dr["积分"];
dr_["states"] = dr["状态"];
dr_["createDate"] = dr["创建时间"];
dr_["refreshDate"] = dr["更新时间"];
dbdata.Rows.Add(dr_);
}
RemoveEmpty(dbdata);
string constr = System.Configuration.ConfigurationManager.AppSettings["cool"];
SqlBulkCopyByDatatable(constr, "student", dbdata);
}
}
return RedirectToAction("Index", "DataRefsh");
}
/// <summary>
/// 大数据插入
/// </summary>
/// <param name="connectionString">目标库连接</param>
/// <param name="TableName">目标表</param>
/// <param name="dtSelect">来源数据</param>
public static void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dtSelect)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
{
try
{
sqlbulkcopy.DestinationTableName = TableName;
sqlbulkcopy.BatchSize = 20000;
sqlbulkcopy.BulkCopyTimeout = 0;//不限时间
for (int i = 0; i < dtSelect.Columns.Count; i )
{
sqlbulkcopy.ColumnMappings.Add(dtSelect.Columns[i].ColumnName, dtSelect.Columns[i].ColumnName);
}
sqlbulkcopy.WriteToServer(dtSelect);
}
catch (System.Exception ex)
{
throw ex;
}
}
}
}
protected void RemoveEmpty(DataTable dt)
{
List<DataRow> removelist = new List<DataRow>();
for (int i = 0; i < dt.Rows.Count; i )
{
bool IsNull = true;
for (int j = 0; j < dt.Columns.Count; j )
{
if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
{
IsNull = false;
}
}
if (IsNull)
{
removelist.Add(dt.Rows[i]);
}
}
for (int i = 0; i < removelist.Count; i )
{
dt.Rows.Remove(removelist[i]);
}
}
这里需要注意：IEnumerable<HttpPostedFileBase> 是读取多个文件的名称，HttpPostedFileBase只能读取一个，还有就是files 是file 控件name 相对应的
5、string constr = System.Configuration.ConfigurationManager.AppSettings["cool"]; 需要在web.config 里面的appSettings 里面添加数据库连接字符串的配置
6、控制器import 方法里面有用到GetExcelDataTable 和GetCellValue 方法
这里创建一个ImportExcel 类，创建以下两个方法
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NPOI;
using System.Data;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
namespace Cool{
public class ImportExcel
{
public static DataTable GetExcelDataTable(string filePath)
{
IWorkbook Workbook;
DataTable table = new DataTable();
try
{
using (FileStream fileStream = new FileStream(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);
}
//数据
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));
}
}
}
table.Rows.Add(dataRow);
}
return table;
}
private static string GetCellValue(ICell cell)
{
if (cell == null)
{
return string.Empty;
}
switch (cell.CellType)
{
case CellType.Blank:
return string.Empty;
case CellType.Boolean:
return cell.BooleanCellValue.ToString();
case CellType.Error:
return cell.ErrorCellValue.ToString();
case CellType.Numeric:
case CellType.Unknown:
default:
return cell.ToString();
case CellType.String:
return cell.StringCellValue;
case CellType.Formula:
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
}
catch
{
return cell.NumericCellValue.ToString();
}
}
}
}}
这里需要注意：NPOI 只支持93-2007的offcie 的excel文件，如果是高版本的excel文件，需要降级，打开excel文件，另存为93-2007 的.xls 文件即可
这样就可以批量把文件数据导入到数据库中了！！！
