域控 批量导入 用户_在学生用户管理页面中实现批量导入用户功能，将批量导入知识与现在所学的Knockout知识结合起来运用...

//前台导入按钮，点击跳出模态框

<button data-toggle="modal" id="BatchAdd" data-bind="click:BatchAdd" >导入</button>

//导入模态框

<div class="modal fade" id="myBatchAdd" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">

<div class="modal-dialog" role="document">

<div class="modal-content">

<div class="modal-header">

<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>

<h4 class="modal-title" id="myModalLabel6">数据数据</h4>

</div>

<div class="modal-body">

<input name="file" type="file" id="file" />

</div>

<div class="modal-footer">

<input id="SaveBatchAdd" data-bind="click:SaveBatchAdd" type="submit" value="批量导入" />

</div>

</div>

</div>

</div>

//前台js代码

SaveBatchAdd: ko.observable(""),

StuNameSearch: ko.observable(""),

BatchAdd: function () {

$('#myBatchAdd').modal('show');

},

SaveBatchAdd: function () {

var fd = new FormData();

fd.append("file", $('#file')[0].files[0]);

$.ajax({

url: '@Url.Action("Import","UsersInfo")',

type: "POST",

data: fd,

datatype: 'json',

processData: false,

contentType: false,

success: function (data) {

if (data == "导入成功") {

alert(data);

$('#myBatchAdd').modal('hide');

$('#page').val("1");

Search();

}

else {

alert(data);

}

}

})

},

//控制器

[HttpPost]

public ActionResult Import(HttpPostedFileBase file)

{

try

{

var fileName = file.FileName;

var filePath = Server.MapPath(string.Format("~/{0}", "Files"));

string path = Path.Combine(filePath, fileName);

file.SaveAs(path);

DataTable excelTable = new DataTable();

excelTable = daoru.GetExcelDataTable(path);

DataTable dbdata = new DataTable();

dbdata.Columns.Add("ID");

dbdata.Columns.Add("StuID");

dbdata.Columns.Add("StuName");

dbdata.Columns.Add("StuSex");

dbdata.Columns.Add("StuAge");

dbdata.Columns.Add("beiyong");

for (int i = 0; i < excelTable.Rows.Count; i++)

{

DataRow dr = excelTable.Rows[i];

DataRow dr_ = dbdata.NewRow();

dr_["ID"] = dr["序号"];

dr_["StuID"] = dr["学生ID"];

dr_["StuName"] = dr["学生姓名"];

dr_["StuSex"] = dr["学生性别"];

dr_["StuAge"] = dr["学生年龄"];

dr_["beiyong"] = dr["状态"];

dbdata.Rows.Add(dr_);

}

RemoveEmpty(dbdata);

string constr = System.Configuration.ConfigurationManager.AppSettings["meixinEntities_"];

SqlBulkCopyByDatatable(constr, "UserInfoes", dbdata);

//m_user1 表名 meixinEntities_ web.config 数据库连接名

//return Content("<script>alert('导入成功');window.location.href='/UsersInfo/Index';</script>");

return Content("导入成功");

}

catch

{

return Content("导入失败");

}

}

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]);

}

}

//新建类

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();

}

}

}

//web.config

<add key="meixinEntities_" value="server=172.16.12.5;database=kotest;uid=sa;pwd=123456" />

要引用程序包