asp.net mvc 批量导入excel数据
1、引入插件
<link href="~/Content/bootstrap.min.css" rel="stylesheet" />
<link href="~/Content/bootstrap-fileinput/css/fileinput.css" rel="stylesheet" />
<script src="~/Content/js/jquery/jquery-2.1.1.min.js"></script>
<script src="~/Content/js/fileinput.js"></script>
<script src="~/Content/js/bootstrap/bootstrap.js"></script>
2、前端代码编写
<div class="container kv-main" id="" style="height:450px;margin:20px;">
<form enctype="multipart/form-data">
<div class="form-group">
<input id="file-1" type="file" class="file" data-overwrite-initial="false" data-min-file-count="1" name="file-1">
</div>
</form>
</div>
3、前端js代码编写
<script>
var method = $.request("method");
if (!method) {
method = "UpLoadFile";
}
console.log(method);
$("#file-1").fileinput({
uploadAsync: true,
//language: 'zh',
uploadUrl: '/CommomManage/UnloadData/' + method, //you must set a valid URL here else you will get an error
allowedFileExtensions: ['xls'],
overwriteInitial: false,
maxFileSize: 600000000,
maxFilesNum: 1,
initialCaption: '请选择上传文件',
allowedFileTypes: ['xls'],
//allowedFileTypes: ['image', 'video', 'flash'],
slugCallback: function (filename) {
return filename;
}
}).on("filebatchselected", function (event, files) {
$(this).fileinput("upload"); //自动上传功能
$.modalMsg("导入中，请稍等...", "success");
}).on("fileuploaded", function (event, data) {
if (data.response) {
var result = data.response;
if (result.state == "success") {
top.frames[iframeId].submitForm($.activeWindow());
$.modalMsg(result.message, "success");
if (result.data) {
alert(result.data);
}
$.modalClose();
}
else {
//$.modalClose();
$.modalAlert(result.message, "error");
}
}
});;
$(function () {
$(".btn-file span.hidden-xs").html("浏览...");
$(".fileinput-upload span.hidden-xs").html(" 上传 ");
$(".fileinput-remove span.hidden-xs").html(" 清空 ");
$("div.file-drop-zone-title").html("拖放文件在这里...");
//$("#test-upload").fileinput({
// 'showPreview': false,
// 'allowedFileExtensions': ['pdf', 'dox'],
// 'elErrorContainer': '#errorBlock'
//});
/*
$("#test-upload").on('fileloaded', function(event, file, previewId, index) {
alert('i = ' + index + ', id = ' + previewId + ', file = ' + file.name);
});
*/
$(".fileinput-remove").click(function () {
$("div.file-drop-zone-title").html("拖放文件在这里...");
});
})
</script>
4、后端业务处理代码编写，分为两个部分
第一部分接受的方法
public ActionResult UpLoadFile(HttpPostedFileBase filebase)
{
HttpPostedFileBase file = Request.Files["file-1"];
if (file == null)
return Error("请导入文件！");
if (!file.FileName.Contains("会员信息表"))
return Error("请导入正确的excel表！（模板可从主页右上角按钮下载）");
return SubmitFormOfBegin(file.InputStream);
}
第二部分，处理的方法
#region 会员信息导入
private ActionResult SubmitFormOfBegin(Stream file)
{
string useId = OperatorProvider.Provider.GetCurrent().UserId;
//var organizeid = HttpContext.Request.Cookies["SCid"].Value;
var organizeid = OperatorProvider.Provider.GetCurrent().CompanyId;
DateTime now = DateTime.Now;
//if (db.WZGL_GoodsInfoOfBeginning.Count() > 0)
// return Error("不可二次期初导入！");
IWorkbook workbookDown;
try
{
workbookDown = new HSSFWorkbook(file); //HSSF适用2007以前的版本,XSSF适用2007版本及其以上的。
}
catch
{
workbookDown = new XSSFWorkbook(file);
}
//基本信息
string id = null, address = null, code = null, fullName = null, briefName = null, manager = null, managerPhone = null, bz = null;
bool status = false;
string membershiplevel = null, telPhone = null, postalCode = null, fax = null, president = null, mobilePhone = null, mailBox = null, contacts = null, cellPhone = null, legalPerson = null;
string legalPhone = null, levelTelephone = null, levelmailBox = null, qualificationlevel = null, enterprisesType = null;
DateTime admissionMonth = DateTime.Now, admissionTime = DateTime.Now;
try
{
IRow row;
//会员list
List<m_member> m_memberInfoList = new List<m_member>();
//
int sheetCount = workbookDown.NumberOfSheets;
int rowCount;
ISheet sheet;
ICell cell;
for (int j = 0; j < sheetCount; j++)
{
if (workbookDown.IsSheetHidden(j))
continue;
sheet = workbookDown.GetSheetAt(j);
rowCount = sheet.LastRowNum;
for (int i = 2; i <= rowCount; i++)
{
row = sheet.GetRow(i);
if (row == null) continue;
if (row.GetCell(0).StringCellValue == "结束")
break;
code = row.GetCell(0).StringCellValue.ToLower();
if (db.m_member.Any(s => s.code.ToLower() == code && s.deleteMark != true))
{
return Error("第" + (j + 1) + "个sheet表中第" + i + "行的编号“" + code + "”已存在，请检查！");
}
fullName = row.GetCell(1).StringCellValue.ToLower();
if (db.m_member.Any(s => s.fullName == fullName && s.organizeid == organizeid && s.deleteMark != true))
{
return Error("第" + (j + 1) + "个sheet表中第" + i + "行的单位名称“" + fullName + "”已存在，请检查！");
}
id = Guid.NewGuid().ToString();
code = row.GetCell(0).StringCellValue;//编号 fullName = row.GetCell(1).StringCellValue;//单位名称
briefName = row.GetCell(2).StringCellValue;//简称
membershiplevel = row.GetCell(3).StringCellValue;//会员级别
string membershiplevelId = (from a in db.Sys_ItemsDetail where a.F_ItemName == membershiplevel select a.F_Id).FirstOrDefault();
if (membershiplevelId.IsEmpty())
{
return Error("该会员级别“" + membershiplevel + "”没定义");
}
address = row.GetCell(4).StringCellValue;//地址
row.GetCell(5).SetCellType(CellType.String);
telPhone = row.GetCell(5).StringCellValue;//联系电话
postalCode = row.GetCell(6).StringCellValue;//邮政编码
fax = row.GetCell(7).StringCellValue;//传真
president = row.GetCell(8).StringCellValue;//总经理
row.GetCell(9).SetCellType(CellType.String);
mobilePhone = row.GetCell(9).StringCellValue;//总经理手机
mailBox = row.GetCell(10).StringCellValue;//邮箱
contacts = row.GetCell(11).StringCellValue;//联系人
row.GetCell(12).SetCellType(CellType.String);
cellPhone = row.GetCell(12).StringCellValue;//联系人手机
admissionTime = sheet.GetRow(i).GetCell(13).DateCellValue; //入会时间
//admissionMonth = admissionTime.Year + "-"+ admissionTime.Day;
admissionMonth = row.GetCell(14).DateCellValue;//入会月份
qualificationlevel = row.GetCell(15).StringCellValue;//资质等级
string qualificationlevelId = (from a in db.Sys_ItemsDetail where a.F_ItemName == qualificationlevel select a.F_Id).FirstOrDefault();
if (qualificationlevelId.IsEmpty())
{
return Error("该资质等级“" + qualificationlevel + "”没定义");
}
enterprisesType = row.GetCell(16).StringCellValue;//企业类型
string enterprisesTypeId = (from a in db.Sys_ItemsDetail where a.F_ItemName == enterprisesType select a.F_Id).FirstOrDefault();
if (enterprisesTypeId.IsEmpty())
{
return Error("该企业类型“" + enterprisesType + "”没定义");
}
legalPerson = row.GetCell(17).StringCellValue;//法人代表
row.GetCell(18).SetCellType(CellType.String);
legalPhone = row.GetCell(18).StringCellValue;//法人手机
levelmailBox = row.GetCell(19).StringCellValue;//法人邮箱
//启用状态
if (row.GetCell(20).StringCellValue.Equals("是"))
{
status = true;
}
//负责人
bz = row.GetCell(21).StringCellValue;
m_memberInfoList.Add(new m_member
{
id = id,
organizeid = organizeid,
code = code,
fullName = fullName,
briefName = briefName,
membershiplevel = membershiplevelId,
address = address,
telPhone = telPhone,
postalCode = postalCode,
fax = fax,
president = president,
mobilePhone = mobilePhone,
mailBox = mailBox,
contacts = contacts,
cellPhone = cellPhone,
admissionMonth = admissionMonth,
admissionTime = admissionTime,
legalPerson = legalPerson,
legalPhone = legalPhone,
levelTelephone = levelTelephone,
levelmailBox = levelmailBox,
qualificationlevel = qualificationlevelId,
enterprisesType = enterprisesTypeId,
status = status,
bz = bz,
creatorTime = now,
creatorUserId = useId
});
}
}
db.m_member.AddRange(m_memberInfoList);
db.SaveChanges();
return Success("导入成功！");
}
catch
{
return Error("导入失败,请检查下格式！");
}
}
#endregion
5、上传效果
