.NET MVC 学习笔记（六）—— 数据导入
在程序使用过程中，有时候需要新增大量数据，这样一条条数据去Add明显不是很友好，这时候最好就是有一个导入功能，导入所需要的数据，下面我们就一起来看一下导入功能。
一. 在导入之前，首先我们需要下载模板，模板下载方法
$("#btnDownloadTemplate")
.click(function () {
window.location.href = "@Url.Content("~/Content/ImportClientDataTemplate.xlsx")";
});
MVC .NET框架中该方法可以在很容易的下载模板文件。
如果是纯前端框架，该方法则无效，运行效果为直接在浏览器中打开模板文件，显然不是我们想要的。此时可以使用以下办法：
/*
* 下载文件
*/
window.downloadFile = function(sUrl) {
//iOS devices do not support downloading. We have to inform user about this.
if(/(iP)/g.test(navigator.userAgent)) {
alert('Your device does not support files downloading. Please try again in desktop browser.');
return false;
} //If in Chrome or Safari - download via virtual link click
if(window.downloadFile.isChrome || window.downloadFile.isSafari) {
//Creating new link node.
var link = document.createElement('a');
link.href = sUrl; if(link.download !== undefined) {
//Set HTML5 download attribute. This will prevent file from opening if supported.
var fileName = sUrl.substring(sUrl.lastIndexOf('/') + 1, sUrl.length);
link.download = fileName;
} //Dispatching click event.
if(document.createEvent) {
var e = document.createEvent('MouseEvents');
e.initEvent('click', true, true);
link.dispatchEvent(e);
return true;
}
} // Force file download (whether supported by server).
if(sUrl.indexOf('?') === -1) {
sUrl += '?download';
} window.open(sUrl, '_self');
return true;
} window.downloadFile.isChrome = navigator.userAgent.toLowerCase().indexOf('chrome') > -1;
window.downloadFile.isSafari = navigator.userAgent.toLowerCase().indexOf('safari') > -1;
方法调用：
downloadFile("../assets/template/Template.xlsx");
二. 数据导入
1. 导入按钮：
<div class="btn-group" style="text-align:right;width:82px">
<label class="input-group-btn">
<input id="btnSelectData" type="file" name="file" accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" style="left: -9999px; position: absolute;">
<span class="btn btn-default" style="border-radius:3px">导入会员</span>
</label>
</div>
2. 导入按钮事件
// 选择文件事件
$("#btnSelectData").change(function (e) {
var file = e.target.files[0] || e.dataTransfer.files[0];
if (file) {
$.bootstrapLoading.start(
{
loadingTips: "正在处理数据，请稍候...",
opacity: 0.8,
//loading页面透明度
backgroundColor: "#000",
TipsColor: "#555",
});
// 获取文件资源
var file = document.getElementById("btnSelectData").files[0];
var formData = new FormData();
formData.append('ExcelData', file);
// 保存信息
$.ajax({
type: "POST",
async: true,
url: "@Url.Content("~/Client/ImportClientData")",
data: formData,
contentType: false,
processData: false,
mimeType: "multipart/form-data",
success: function (response) {
response = $.parseJSON(response);
var option = {
message: response.ResultMessage,
title: response.ResultTitle
};
Ewin.alert(option);
if (response.ResultTitle == "Success") {
$('.message-dialog').on('hide.bs.modal', function () {
refresh();
});
}
},
complete: function () {
$.bootstrapLoading.end();
$("#btnSelectData").val('');
}
});
}
});
其中$.bootstrapLoading 是Loading功能，导入过程中等待界面，需要导入PerfectLoad.js
3. Controller方法
/// <summary>
/// 导入文件
/// </summary>
/// <returns></returns>
public JsonResult ImportClientData()
{
string result = String.Empty;
String fileName = String.Empty;
// 员工信息
List<ClientDomain> lsClient = new List<ClientDomain>();
try
{
if (Request.Files.Count > 0)
{
HttpPostedFileBase file = Request.Files["ExcelData"];
String filePath = @"../Upload/TempData/";
if (Directory.Exists(Server.MapPath(filePath)) == false)//如果不存在就创建file文件夹
{
Directory.CreateDirectory(Server.MapPath(filePath));
}
fileName = Server.MapPath(filePath) + file.FileName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + Path.GetExtension(file.FileName);
file.SaveAs(fileName);
// 解析XML文件
//读取xml
XmlDocument xdoc = new XmlDocument();
xdoc.Load(Server.MapPath("/App_Data/ExcelTemplate.xml"));
XmlElement root = xdoc.DocumentElement; //获取根节点
XmlNode node = xdoc.SelectSingleNode("/Functions/Function[@name='Client']");
// 字段列表
List<ExcelField> lsExcelFields = new List<ExcelField>();
foreach (XmlNode item in node.ChildNodes)
{
String columnName = item.SelectSingleNode("columnName").InnerText;
String fieldName = item.SelectSingleNode("fieldName").InnerText;
lsExcelFields.Add(new ExcelField() { ColumnName = columnName, FieldName = fieldName });
}
// 获取Excel信息
for (int iIndex = 0; iIndex < NPOIHelper.GetNumberOfSheets(fileName); iIndex++)
{
// Read the CLP head information
DataTable dtDatas = NPOIHelper.ReadExcel(fileName, iIndex, 0, 0);
if (dtDatas == null)
throw new Exception("Read excel error.");
ClientDomain client = null;
for (Int32 iRow = 0; iRow < dtDatas.Rows.Count; iRow++)
{
client = new ClientDomain();
// 遍历所有属性
lsExcelFields.ForEach(item =>
{
String sValue = dtDatas.Rows[iRow][item.ColumnName].ToString();
Type t = client.GetType();
PropertyInfo propertyInfo = t.GetProperty(item.FieldName);
if (propertyInfo.PropertyType == typeof(DateTime) || propertyInfo.PropertyType == typeof(DateTime?))
{
if (!String.IsNullOrEmpty(sValue))
{
propertyInfo.SetValue(client, DateTime.Parse(sValue), null);
}
}
else if (propertyInfo.PropertyType == typeof(Decimal) || propertyInfo.PropertyType == typeof(Decimal?))
{
propertyInfo.SetValue(client, Decimal.Parse(sValue), null);
}
else if (propertyInfo.PropertyType == typeof(Int32) || propertyInfo.PropertyType == typeof(Int32?))
{
propertyInfo.SetValue(client, Int32.Parse(sValue), null);
}
else
{
propertyInfo.SetValue(client, sValue, null);
}
});
lsClient.Add(client);
}
}
}
//保存员工
result = service.SaveImportDatas(lsClient, CurrentLoginUser);
// 删除临时文件
CommonMethod.DeleteFile(fileName);
if (String.IsNullOrEmpty(result))
{
LogHelper.LogOperate(String.Format("导入会员信息{0}条", lsClient.Count), Constant.OPEARTE_LOG_INFO, CurrentLoginUser);
return new JsonResult()
{
JsonRequestBehavior = JsonRequestBehavior.AllowGet,
Data = new { ResultTitle = Constant.Result_Title_Success, ResultMessage = String.Format(MessageConstant.MESSAGE_IMPORT_SUCCESS, lsClient.Count) }
};
}
else
{
LogHelper.LogOperate(String.Format("导入会员信息失败：{0}", result), Constant.OPEARTE_LOG_WARNING, CurrentLoginUser);
return new JsonResult()
{
JsonRequestBehavior = JsonRequestBehavior.AllowGet,
Data = new { ResultTitle = Constant.Result_Title_Warning, ResultMessage = result }
};
}
}
catch (Exception ex)
{
Log.SaveException(ex);
return new JsonResult()
{
JsonRequestBehavior = JsonRequestBehavior.AllowGet,
Data = new { ResultTitle = Constant.Result_Title_Error, ResultMessage = ex.Message }
};
}
finally
{
// 删除临时文件
CommonMethod.DeleteFile(fileName);
}
}
ExcelField.cs
/// <summary>
/// Excel Field
/// </summary>
[Serializable]
[DataContract]
public class ExcelField
{
[DataMember]
public String ColumnName { get; set; } [DataMember]
public String FieldName { get; set; }
}
ExcelTemplate.xml
<?xml version="1.0" encoding="utf-8" ?>
<Functions>
<Function name="Client">
<field>
<columnName>卡号</columnName>
<fieldName>CardNo</fieldName>
</field>
<field>
<columnName>姓名</columnName>
<fieldName>UserName</fieldName>
</field>
<field>
<columnName>性别</columnName>
<fieldName>Sex</fieldName>
</field>
<field>
<columnName>出生日期</columnName>
<fieldName>Birthdate</fieldName>
</field>
<field>
<columnName>手机号</columnName>
<fieldName>Phone</fieldName>
</field>
<field>
<columnName>地址</columnName>
<fieldName>Address</fieldName>
</field>
<field>
<columnName>积分</columnName>
<fieldName>Score</fieldName>
</field>
<field>
<columnName>等级</columnName>
<fieldName>GradeCode</fieldName>
</field>
</Function>
</Functions>
导入数据到数据库
/// <summary>
/// 导入数据
/// </summary>
/// <param name="manager"></param>
/// <param name="lsClient"></param>
/// <param name="user"></param>
/// <returns></returns>
public string SaveImportDatas(DBManager manager, List<ClientDomain> lsClient, LoginUser user)
{
Int32 iCount = 50;
Int32 iRunSize = (lsClient.Count / iCount) + 1;
List<ClientDomain> newList = null;
string result = String.Empty;
String sUserId = user.RolesName;
try
{
var waits = new List<EventWaitHandle>();
for (Int32 iIndex = 0; iIndex < iRunSize; iIndex++)
{
//计算每个线程执行的数据
Int32 startIndex = (iIndex * iCount);
Int32 iPage = iCount;
if ((lsClient.Count - startIndex) < iCount)
{
iPage = (lsClient.Count - startIndex);
}
newList = lsClient.GetRange(startIndex, iPage);
var handler = new ManualResetEvent(false);
waits.Add(handler);
ParamModel data = new ParamModel();
data.UserId = sUserId;
data.Data = newList;
data.manager = manager;
new Thread(new ParameterizedThreadStart(ImportData)).Start(new Tuple<ParamModel, EventWaitHandle>(data, handler));
WaitHandle.WaitAll(waits.ToArray());
}
}
catch (Exception ex)
{
Log.SaveException(ex);
result = ex.Message;
}
return result;
} /// <summary>
/// 导入数据
/// </summary>
/// <param name="obj"></param>
private void ImportData(Object obj)
{
var p = (Tuple<ParamModel, EventWaitHandle>)obj;
ParamModel param = p.Item1 as ParamModel;
String sUserId = param.UserId;
DBManager manager = param.manager;
List<ClientDomain> models = param.Data as List<ClientDomain>;
models.ForEach(model =>
{
List<ClientDomain> clients = ClientBiz.GetDomainByExactFilter(new ClientFilter() { CardNo = model.CardNo }) as List<ClientDomain>;
if (clients == null || clients.Count == 0)
{
// 添加
model.CreateUser = sUserId;
model.CreateDateTime = DateTime.Now;
model.UpdateUser = sUserId;
model.UpdateDateTime = DateTime.Now; String sql = DataHelper.GenerateInsertSQL(DbTableName.Client, model, new LoginUser() { Uid = sUserId }, DateTime.Now); manager.Execute(sql, model);
}
else
{
// 更新
model.Id = clients[0].Id;
model.CreateUser = clients[0].CreateUser;
model.CreateDateTime = clients[0].CreateDateTime;
model.UpdateUser = sUserId;
model.UpdateDateTime = DateTime.Now; String sql = DataHelper.GenerateUpdateAllFieldSQL(DbTableName.Client, model, new LoginUser() { Uid = sUserId }, DateTime.Now); manager.Execute(sql, model);
}
});
p.Item2.Set();
}
以上，数据导入功能完成。
以下程序运行效果
PS： 在Excel导入时，也可以在ExcelTemplate.xml中配置一些字段Check的问题
例如：
<field>
<columnName>卡号</columnName>
<fieldName>CardNo</fieldName>
<checkList>
<!--NotNull:非空 Length:字段长度 Type:字段类型-->
<NotNull>Y</NotNull>
<Length>20</Length>
<Type></Type>
</checkList>
</field>
ExcelField.cs & CheckModel.cs
/// <summary>
/// Excel 字段
/// </summary>
public class ExcelField
{
/// <summary>
/// Excel列名
/// </summary>
[DataMember]
public String ColumnName { get; set; }
/// <summary>
/// 字段名称
/// </summary>
[DataMember]
public String FieldName { get; set; }
/// <summary>
/// 检测
/// </summary>
[DataMember]
public CheckModel CheckModel { get; set; }
} /// <summary>
/// 检查项目
/// </summary>
public class CheckModel
{
/// <summary>
/// 非空
/// </summary>
[DataMember]
public String NotNull { get; set; }
/// <summary>
/// 字段长度检测
/// </summary>
[DataMember]
public Int32 Length { get; set; }
/// <summary>
/// 字段类型
/// </summary>
[DataMember]
public String Type { get; set; }
}
字段信息获取以及字段检查方法
/// <summary>
/// 获取所有Excel字段
/// </summary>
/// <param name="functionName">功能名</param>
/// <returns></returns>
public static List<ExcelField> GetExcelFields(String functionName)
{
// 解析XML文件
//读取xml
XmlDocument xdoc = new XmlDocument();
xdoc.Load("Content/ExcelTemplate.xml");
XmlElement root = xdoc.DocumentElement; //获取根节点
XmlNode node = xdoc.SelectSingleNode(String.Format("/Functions/Function[@name='{0}']", functionName));
// 字段列表
List<ExcelField> lsExcelFields = new List<ExcelField>();
foreach (XmlNode item in node.ChildNodes)
{
String columnName = item.SelectSingleNode("columnName").InnerText;
String fieldName = item.SelectSingleNode("fieldName").InnerText;
ExcelField excelField = new ExcelField();
// 列名
excelField.ColumnName = columnName;
// 字段名
excelField.FieldName = fieldName;
XmlNodeList childNode = item.SelectNodes("checkList");
if (childNode != null && childNode.Count != 0)
{
CheckModel check = new CheckModel();
// 非空判断
check.NotNull = childNode[0].SelectSingleNode(Constant.Check_NotNull) == null ? "" : (childNode[0].SelectSingleNode(Constant.Check_NotNull).FirstChild == null ? "" : childNode[0].SelectSingleNode(Constant.Check_NotNull).FirstChild.Value);
// 长度判断
check.Length = childNode[0].SelectSingleNode(Constant.Check_Length) == null ? -1 : (childNode[0].SelectSingleNode(Constant.Check_Length).FirstChild == null ? -1 : Int32.Parse(childNode[0].SelectSingleNode(Constant.Check_Length).FirstChild.Value));
// 字段类型
check.Type = childNode[0].SelectSingleNode(Constant.Check_Type) == null ? "" : (childNode[0].SelectSingleNode(Constant.Check_Type).FirstChild == null ? "" : childNode[0].SelectSingleNode(Constant.Check_Type).FirstChild.Value);
excelField.CheckModel = check;
}
lsExcelFields.Add(excelField);
} return lsExcelFields;
} /// <summary>
/// 检查字段
/// </summary>
/// <param name="excel"></param>
/// <param name="value"></param>
/// <param name="iRowIndex"></param>
/// <returns></returns>
public static String CheckFieldValue(ExcelField excel, String value, Int32 iRowIndex)
{
StringBuilder sb = new StringBuilder();
try
{
// 非空判断
if (Constant.NotNull_Y.Equals(excel.CheckModel.NotNull) && String.IsNullOrEmpty(value))
{
sb.AppendLine(String.Format("第{0}行，{1}列值不能为空。", iRowIndex, excel.ColumnName));
}
// 长度判断
if (excel.CheckModel.Length != -1 && (!String.IsNullOrWhiteSpace(value) && value.Length > excel.CheckModel.Length))
{
sb.AppendLine(String.Format("第{0}行，{1}列值长度不能超过{2}。", iRowIndex, excel.ColumnName, excel.CheckModel.Length));
}
// 类型判断
if (!String.IsNullOrWhiteSpace(excel.CheckModel.Type))
{
// 正则表达式
String pattern = String.Empty;
// 表达式结果
Boolean bResult = true;
switch (excel.CheckModel.Type)
{
// 正整数判断
case Constant.Type_PositiveInteger:
pattern = @"^[0-9]*[1-9][0-9]*$";
bResult = Regex.IsMatch(value ?? "", pattern);
if (!bResult)
{
sb.AppendLine(String.Format("第{0}行，{1}列值应该输入正整数。", iRowIndex, excel.ColumnName));
}
break;
case Constant.Type_Telephone:
pattern = @"(/(/d{3,4}/)|/d{3,4}-|/s)?/d{7,14}";
bResult = Regex.IsMatch(value ?? "", pattern);
if (!bResult)
{
sb.AppendLine(String.Format("第{0}行，{1}列值应该输入正确电话号码。", iRowIndex, excel.ColumnName));
}
break;
case Constant.Type_Email:
pattern = @"^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\.[a-zA-Z0-9_-]+)+$";
bResult = Regex.IsMatch(value ?? "", pattern);
if (!bResult)
{
sb.AppendLine(String.Format("第{0}行，{1}列值应该输入正确邮箱。", iRowIndex, excel.ColumnName));
}
break;
}
}
return sb.ToString().Trim();
}
catch (Exception ex)
{
return ex.Message;
}
} /// <summary>
/// 设置属性值
/// </summary>
/// <param name="obj">对象</param>
/// <param name="fieldName">字段名</param>
/// <param name="value">字段值</param>
public static void SetPropertyInfoValue(Object obj,String fieldName, String value)
{
Type t = obj.GetType();
PropertyInfo propertyInfo = t.GetProperty(fieldName);
// 时间类型
if (propertyInfo.PropertyType == typeof(DateTime) || propertyInfo.PropertyType == typeof(DateTime?))
{
if (!String.IsNullOrEmpty(value))
{
DateTime dt;
if (DateTime.TryParse(value, out dt))
{
propertyInfo.SetValue(obj, dt, null);
}
}
}
// Decimal 类型
else if (propertyInfo.PropertyType == typeof(Decimal)|| propertyInfo.PropertyType == typeof(Decimal?))
{
if (!String.IsNullOrEmpty(value))
{
Decimal dValue;
if (Decimal.TryParse(value, out dValue))
{
propertyInfo.SetValue(obj, dValue, null);
}
}
}
// Int32 类型
else if (propertyInfo.PropertyType == typeof(Int32) || propertyInfo.PropertyType == typeof(Int32?))
{
if (!String.IsNullOrEmpty(value))
{
Int32 iValue;
if (Int32.TryParse(value, out iValue))
{
propertyInfo.SetValue(obj, iValue, null);
}
}
}
else
{
propertyInfo.SetValue(obj, value, null);
}
}