using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; using System.Web.Routing; using Coralcode.Framework.Domains; using Coralcode.Framework.Extensions; using Coralcode.Framework.Log; using Coralcode.Framework.Models; using Coralcode.Framework.Mvc.Extensions; using Coralcode.Framework.Services; namespace Coralcode.Framework.Mvc.ControlContent { public abstract class ImportExportCoralController<TModel, TSearch> : CoralController where TModel : class, IViewModel, new() where TSearch : SearchBase, new() { private readonly ICrudCoralService<TModel, TSearch> _service; protected readonly ImportExportService<TModel, TSearch> ImportExportService; protected Action<TModel> CreateAction; protected ImportExportCoralController(ICrudCoralService<TModel, TSearch> service) { _service = service; ImportExportService = new ImportExportService<TModel, TSearch>(service); if (CreateAction == null) CreateAction = item => _service.Create(item); } protected override void Initialize(RequestContext requestContext) { base.Initialize(requestContext); var routeValues = Request.GetRouteValues(); ViewBag.ImportUrl = Url.Action("Import", routeValues); ViewBag.ExportUrl = Url.Action("Export", routeValues); ViewBag.ExportTemplateUrl = Url.Action("DownloadTemplate"); } protected virtual string FileName { get { return (typeof(TModel).GetDescription() ?? IdentityGenerator.NewGuidString()) + DateTime.Now.ToString("yyyyMMddHHmmss"); } } /// <summary> /// 下载模板 /// </summary> /// <returns></returns> public virtual ActionResult DownloadTemplate() { var stream = ImportExportService.ExportTemplate(); return File(stream.ToArray(), "application/zip-x-compressed", FileName); } /// <summary> /// 导出查询结果集 /// </summary> /// <returns></returns> public virtual ActionResult Export(TSearch searchModel) { searchModel = searchModel ?? new TSearch(); var stream = ImportExportService.Export(_service.Search(searchModel)); return File(stream.ToArray(), "application/zip-x-compressed", FileName); } public ActionResult Import() { return PartialView("Import"); } /// <summary> /// 导入返回结果 /// </summary> /// <param name="uploadFile"></param> /// <returns></returns> [HttpPost] public virtual ActionResult Import(HttpPostedFileBase uploadFile) { if (uploadFile == null || uploadFile.ContentLength < 0) return AjaxErrorResult(null, "请选择导入文件!"); List<ImportMessage<TModel>> results; try { results = ImportExportService.Import(uploadFile.InputStream, ValidateAndPreProccess); } catch (Exception ex) { LoggerFactory.Instance.Error("导入产生错误;信息:{0}", ex.ToString()); return AjaxExceptionResult(ex); } if (results.All(item => item.State == ResultState.Fail)) { string errorMessage = results.Aggregate("<br/>", (seed, item) => string.Format("行号:{0},错误信息:{1}", item.Index, item.ErrorMessage)); return AjaxErrorResult(null, errorMessage); } if (results.Any(item => item.State == ResultState.Fail)) { var errorDatas = results.Where(item => item.State == ResultState.Fail).ToList(); string errorMessage = errorDatas.Aggregate("<br/>", (seed, item) => string.Format("行号:{0},错误信息:{1},操作对象:{2}", item.Index, item.ErrorMessage, item.Model.ToString())); System.IO.File.WriteAllBytes(FileName, errorDatas.ExportWithDescription().Export().ToArray()); return AjaxPartSuccessResult(null, errorMessage); } return AjaxOkResult(null, "导入成功!"); } /// <summary> /// 验证导入数据 /// </summary> /// <param name="model"></param> /// <param name="index"></param> /// <returns></returns> protected abstract ImportMessage<TModel> ValidateAndPreProccess(TModel model, int index); } }
组件
- office组件
- npoi
- epplus
office组件依赖于机器的office库文件,所以对于服务器来说不太友好。npoi是从java搬过来的,而且支持2003版本的excel,扩展比较丰富,唯一遗憾是不支持vba。
epplus支持vba可以说扩展性能最强,我这里由于用不到vba,而且一直都是用npoi对这个组件比较熟悉,所以使用一直沿用这今我这里就选用这个组件作为基础的excel和转换
通用类
无论是导入导出都要涉及到list,datatable,excel的转换,另外excel中。为了方便用户的编辑列头应该是使用中文,然后代码中的列是英文了,那么就有一个mapping的工作,在之前的文章中我们选用了dispalynameattribute作为界面lable的显示,我们这里也沿用这种方式,另外一种是采用desciptionattribute来标注中文。这里我全部采用扩展方法来实现,可以很好的结合链式编程,让语法更优美
List的扩展
主要包括四个扩展方法,其中在导入的时候有一个out 参数作为导入时候错误消息,通常我们在导入很大量数据的时候,作为一个事务要么全部成功要么全部错误,但是实际情况中很多用户会将数据编辑错,那么如果每次事务提交有可能需要来回修改很多次,所以我将导入分为成功,部分成功,和失败三种,这也是我们之前设计resultstate的时候有个部分成功的原因。如果用户数据错误则,收集所有的数据,可以根据你需求给出友好的提示。
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SqlTypes; using System.Linq; using System.Reflection; using System.Text; using System.Threading.Tasks; using Coralcode.Framework.Common; using Coralcode.Framework.Models; using Coralcode.Framework.Mvc.Models; using Coralcode.Framework.Reflection; using Coralcode.Framework.Services; namespace Coralcode.Framework.Extensions { public static class ListExtensions { /// <summary> /// 把list转换成数据表, /// todo 这里如果属性是类,需要从类里面取一个字段作为值 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entities"></param> /// <returns></returns> public static DataTable Export<T>(this List<T> entities) where T : class { var dt = new DataTable(); var properties = typeof(T).GetProperties().ToList(); properties.ForEach(item => dt.Columns.Add(new DataColumn(item.Name) { DataType = item.PropertyType })); entities.ToList().ForEach(item => { var dr = dt.NewRow(); properties.ForEach( property => { var value = property.GetValue(item, null); dr[property.Name] = value; }); dt.Rows.Add(dr); }); return dt; } /// <summary> /// 把数据表转换成List /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list"></param> /// <param name="dt"></param> /// <returns></returns> public static List<T> Import<T>(this List<T> list, DataTable dt, out List<ImportMessage> errorMessages) where T : class,new() { var plist = new List<PropertyInfo>(typeof(T).GetProperties()); errorMessages = new List<ImportMessage>(); for (int i = 0; i < dt.Rows.Count; i++) { DataRow item = dt.Rows[i]; var s = Activator.CreateInstance<T>(); foreach (DataColumn column in dt.Columns) { var info = plist.FirstOrDefault(p => p.Name == column.ColumnName); if (info == null) continue; if (item[column.ColumnName] == null) continue; dynamic dest; var isConvert = false; try { isConvert = CoralConvert.Convert(item[column.ColumnName], info.PropertyType, out dest); } catch (Exception ex) { errorMessages.Add(new ImportMessage { Index = i, State = ResultState.Fail, ErrorMessage = string.Format("{0}的值:{1} 类型转换失败,{2}", column.ColumnName, item[column.ColumnName], ex.Message) }); continue; } if (!isConvert) { errorMessages.Add(new ImportMessage { Index = i, State = ResultState.Fail, ErrorMessage = string.Format("{0}的值:{1} 类型转换失败", column.ColumnName, item[column.ColumnName]) }); continue; } info.SetValue(s, dest, null); } list.Add(s); } return list; } /// <summary> /// 把list转换成数据表, /// todo 这里如果属性是类,需要从类里面取一个字段作为值 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entities"></param> /// <returns></returns> public static DataTable ExportWithDescription<T>(this List<T> entities) where T : class { var dt = new DataTable(); var properties = typeof(T).GetProperties().ToList(); properties.ForEach(item => { var des = PropertyExtensions.GetDisplayName(item); if (!string.IsNullOrEmpty(des)) dt.Columns.Add(new DataColumn(des) { DataType = item.PropertyType }); }); entities.ToList().ForEach(item => { var dr = dt.NewRow(); properties.ForEach( property => { var des = PropertyExtensions.GetDisplayName(property); if (string.IsNullOrEmpty(des)) return; var value = property.GetValue(item, null); dr[des] = value; }); dt.Rows.Add(dr); }); return dt; } /// <summary> /// 把数据表转换成List /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list"></param> /// <param name="dt"></param> /// <param name="errorMessages">错误信息</param> /// <returns></returns> public static List<T> ImportWithDescription<T>(this List<T> list, DataTable dt, out List<ImportMessage> errorMessages) where T : class,new() { var plist = new List<PropertyInfo>(typeof(T).GetProperties()); errorMessages = new List<ImportMessage>(); for(int i =0;i < dt.Rows.Count;i++) { DataRow item = dt.Rows[i]; var s = Activator.CreateInstance<T>(); foreach (DataColumn column in dt.Columns) { var info = plist.FirstOrDefault(p =>PropertyExtensions.GetDisplayName( p) == column.ColumnName); if (info == null) continue; if(item[column.ColumnName] == null) continue; dynamic dest; var isConvert = false; try { isConvert = CoralConvert.Convert(item[column.ColumnName], info.PropertyType, out dest); } catch (Exception ex) { errorMessages.Add(new ImportMessage { Index = i, State = ResultState.Fail, ErrorMessage = string.Format("{0}的值:{1} 类型转换失败,{2}", column.ColumnName, item[column.ColumnName], ex.Message) }); continue; } if (!isConvert) { errorMessages.Add(new ImportMessage { Index = i, State = ResultState.Fail, ErrorMessage = string.Format("{0}的值:{1} 类型转换失败",column.ColumnName, item[column.ColumnName]) }); continue; } info.SetValue(s, dest, null); } list.Add(s); } return list; } } }
DataTable的扩展
主要是excel和datatable的转换,这里注意是两阶段处理。在excel中没有列头的概念,但是在datatable中有,所以第一行一般是作为datatable的列来处理。然后导入导出这部分,我全部是采用stream来处理,在web应用这,可以直接从网络流转换成内存流,然后直接导入到excel,不需要写硬盘,如果写硬盘还要负责清理,稍显麻烦。
using System; using System.CodeDom; using System.Collections.Generic; using System.Configuration; using System.Data; using System.IO; using System.Linq; using System.Reflection; using Coralcode.Framework.Mvc.Models.MiniUI; using Coralcode.Framework.Mvc.Template; using Coralcode.Framework.Utils; using NPOI.HPSF; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.SS.Util; using NPOI.XSSF.UserModel; using ServiceStack.Common.Extensions; using ServiceStack.Messaging.Rcon; namespace Coralcode.Framework.Extensions { public static class DataSetExtensions { /// <summary> /// 按照sheet的名称导入 /// </summary> /// <param name="table"></param> /// <param name="stream"></param> /// <param name="sheetName"></param> /// <param name="rowIndex"></param> /// <returns></returns> public static DataTable ImportBySheetName(this DataTable table, Stream stream, string sheetName = "Sheet1", int rowIndex = 1) { //根据路径通过已存在的excel来创建HSSFWorkbook,即整个excel文档 var workbook = WorkbookFactory.Create(stream); //获取excel的第一个sheet var sheet = workbook.GetSheet(sheetName); //生成表头 sheet.GetRow(0).Cells.ForEach(item => { var column = new DataColumn(item.StringCellValue); table.Columns.Add(column); }); //从第二行开始取数据 for (int i = (sheet.FirstRowNum + rowIndex); i <= sheet.LastRowNum; i++) { DataRow dataRow = table.NewRow(); sheet.GetRow(i) .Cells.Where(item => item != null) .ToList() .ForEach(item => { dataRow[item.ColumnIndex] = item.ToString(); }); table.Rows.Add(dataRow); } return table; } /// <summary> /// 按照sheet的索引导入 /// </summary> /// <param name="table"></param> /// <param name="stream"></param> /// <param name="sheetIndex"></param> /// <param name="rowIndex"></param> /// <returns></returns> public static DataTable ImportBySheetIndex(this DataTable table, Stream stream, int sheetIndex = 0, int rowIndex = 1) { //根据路径通过已存在的excel来创建HSSFWorkbook,即整个excel文档 var workbook = WorkbookFactory.Create(stream); //获取excel的第一个sheet var sheet = workbook.GetSheetAt(sheetIndex); //生成表头 sheet.GetRow(0).Cells.ForEach(item => { if (string.IsNullOrEmpty(item.StringCellValue)) return; if (string.IsNullOrWhiteSpace(item.StringCellValue)) return; var column = new DataColumn(item.StringCellValue.Trim()); table.Columns.Add(column); }); //从第三行开始取数据 for (int i = (sheet.FirstRowNum + rowIndex); i <= sheet.LastRowNum; i++) { DataRow dataRow = table.NewRow(); var row = sheet.GetRow(i); if (row == null || row.FirstCellNum == -1 || string.IsNullOrEmpty(row.Cells[0].ToString())) continue; row.Cells.Where(item => item != null).ToList().ForEach(item => { if (item.CellType == CellType.Numeric) { short format = item.CellStyle.DataFormat; if (format == 14 || format == 31 || format == 57 || format == 58 || format == 176) { DateTime date = item.DateCellValue; dataRow[item.ColumnIndex] = date.ToString("yyyy-MM-dd"); return; } } if (item.ColumnIndex < table.Columns.Count) dataRow[item.ColumnIndex] = item.ToString().Trim().Trim('_'); }); table.Rows.Add(dataRow); } return table; } /// <summary> /// 导出 /// </summary> /// <param name="table"></param> /// <returns></returns> public static MemoryStream Export(this DataTable table) { var ms = new MemoryStream(); var workbook = new XSSFWorkbook(); var sheet = workbook.CreateSheet(); var headerRow = sheet.CreateRow(0); // handling header. foreach (DataColumn column in table.Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption); //If Caption not set, returns the ColumnName value // handling value. int rowIndex = 1; foreach (DataRow row in table.Rows) { var dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in table.Columns) { var columnValue = row[column].ToString(); if (column.DataType == typeof(Enum) || column.DataType.BaseType == typeof(Enum)) { columnValue = EnumExtensions.GetDescriptionByInt(column.DataType, (int)row[column]); } dataRow.CreateCell(column.Ordinal).SetCellValue(columnValue); } rowIndex++; } workbook.Write(ms); return ms; } /// <summary> /// 按照列名导出 /// </summary> /// <param name="table"></param> /// <param name="header"></param> /// <param name="focusHeader">是否只导出对应的列</param> /// <returns></returns> public static MemoryStream Export(this DataTable table, Dictionary<string, string> header, bool focusHeader=false) { var ms = new MemoryStream(); var workbook = new XSSFWorkbook(); var sheet = workbook.CreateSheet(); var headerRow = sheet.CreateRow(0); int columnIndex = 0; // handling header. foreach (DataColumn column in table.Columns) { if (header.ContainsKey(column.ColumnName)) { headerRow.CreateCell(columnIndex).SetCellValue(header[column.ColumnName]); columnIndex++; } else if (!focusHeader) { headerRow.CreateCell(columnIndex).SetCellValue(column.Caption); columnIndex++; } } //If Caption not set, returns the ColumnName value // handling value. int rowIndex = 1; foreach (DataRow row in table.Rows) { var dataRow = sheet.CreateRow(rowIndex); columnIndex = 0; foreach (DataColumn column in table.Columns) { if (focusHeader && !header.ContainsKey(column.ColumnName)) { continue; } dataRow.CreateCell(columnIndex).SetCellValue(row[column].ToString()); columnIndex++; } rowIndex++; } workbook.Write(ms); return ms; } } }
导入
有了上面两个扩展,那么实现起来就很方便了,步骤如下
- 接受网络流文件
- 将文件转换成DataTable
- 将DataTable转换成List
- 数据验证和预处理
- 导入数据库
- 返回导入的错误数据(包含错误数据)
但是要注意下面两个问题
分片导入
在遇到大量数据的时候,虽然ef自带了unityofwork,但是如果一次性导入 几万数据会非常慢,而且基于之前的用户体验,如果失败的话就会全部失败。 所以我们将数据分片,分片大小我一般采用100条,这个最好能写成 配置,根据情况调整。这样错误顶多是100条错误。并且分片之后可以结合TPL并行库,并行提交,不过要注意数据库链接和cpu压力
导入模板
导入模板可以可以采取动态生成,也可采取预先生成,也可以两者结合的方式 动态生成,跟导出一样,只是数据为空。预先生成就是直接人工编辑放到指定目录。两者结合的话可以采用如果没有静态文件则生成,如果有则直接下载。如果导入模板有变化,可以采取文件名的方式来区分。由于我这里性能影响基本可以忽略,这里采用的是动态生成的方式。
导出
基于之前的组件导出步骤分为以下几步
- 通过ef查询数据
- 导入到DataTable
- 转换成excel
- 直接通过网络流下载
这里要注意,由于查询和导出分离的原因最好是通过session,所见即所得的方式。先查询数据,然后把查询条件放到session,点击导出按钮的时候直接从session获取
查询条件然后走上述流程即可。
导入导出服务
这里使用一个委托作为验证,复用了之前controller的验证和预处理逻辑,做了维度的变化,和之前的服务层的设计类似
导入导出控制器
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; using System.Web.Routing; using Coralcode.Framework.Domains; using Coralcode.Framework.Extensions; using Coralcode.Framework.Log; using Coralcode.Framework.Models; using Coralcode.Framework.Mvc.Extensions; using Coralcode.Framework.Services; namespace Coralcode.Framework.Mvc.ControlContent { public abstract class ImportExportCoralController<TModel, TSearch> : CoralController where TModel : class, IViewModel, new() where TSearch : SearchBase, new() { private readonly ICrudCoralService<TModel, TSearch> _service; protected readonly ImportExportService<TModel, TSearch> ImportExportService; protected Action<TModel> CreateAction; protected ImportExportCoralController(ICrudCoralService<TModel, TSearch> service) { _service = service; ImportExportService = new ImportExportService<TModel, TSearch>(service); if (CreateAction == null) CreateAction = item => _service.Create(item); } protected override void Initialize(RequestContext requestContext) { base.Initialize(requestContext); var routeValues = Request.GetRouteValues(); ViewBag.ImportUrl = Url.Action("Import", routeValues); ViewBag.ExportUrl = Url.Action("Export", routeValues); ViewBag.ExportTemplateUrl = Url.Action("DownloadTemplate"); } protected virtual string FileName { get { return (typeof(TModel).GetDescription() ?? IdentityGenerator.NewGuidString()) + DateTime.Now.ToString("yyyyMMddHHmmss"); } } /// <summary> /// 下载模板 /// </summary> /// <returns></returns> public virtual ActionResult DownloadTemplate() { var stream = ImportExportService.ExportTemplate(); return File(stream.ToArray(), "application/zip-x-compressed", FileName); } /// <summary> /// 导出查询结果集 /// </summary> /// <returns></returns> public virtual ActionResult Export(TSearch searchModel) { searchModel = searchModel ?? new TSearch(); var stream = ImportExportService.Export(_service.Search(searchModel)); return File(stream.ToArray(), "application/zip-x-compressed", FileName); } public ActionResult Import() { return PartialView("Import"); } /// <summary> /// 导入返回结果 /// </summary> /// <param name="uploadFile"></param> /// <returns></returns> [HttpPost] public virtual ActionResult Import(HttpPostedFileBase uploadFile) { if (uploadFile == null || uploadFile.ContentLength < 0) return AjaxErrorResult(null, "请选择导入文件!"); List<ImportMessage<TModel>> results; try { results = ImportExportService.Import(uploadFile.InputStream, ValidateAndPreProccess); } catch (Exception ex) { LoggerFactory.Instance.Error("导入产生错误;信息:{0}", ex.ToString()); return AjaxExceptionResult(ex); } if (results.All(item => item.State == ResultState.Fail)) { string errorMessage = results.Aggregate("<br/>", (seed, item) => string.Format("行号:{0},错误信息:{1}", item.Index, item.ErrorMessage)); return AjaxErrorResult(null, errorMessage); } if (results.Any(item => item.State == ResultState.Fail)) { var errorDatas = results.Where(item => item.State == ResultState.Fail).ToList(); string errorMessage = errorDatas.Aggregate("<br/>", (seed, item) => string.Format("行号:{0},错误信息:{1},操作对象:{2}", item.Index, item.ErrorMessage, item.Model.ToString())); System.IO.File.WriteAllBytes(FileName, errorDatas.ExportWithDescription().Export().ToArray()); return AjaxPartSuccessResult(null, errorMessage); } return AjaxOkResult(null, "导入成功!"); } /// <summary> /// 验证导入数据 /// </summary> /// <param name="model"></param> /// <param name="index"></param> /// <returns></returns> protected abstract ImportMessage<TModel> ValidateAndPreProccess(TModel model, int index); } }
导入模板
@{ Layout = "~/Views/Shared/_EditLayout.cshtml"; } <div style="padding-left: 11px; padding-bottom: 5px;"> <table style="table-layout: fixed; margin-left: 25px;"> <tr> <td style=" 75px;">导入文件:</td> <td style=" 360px;"> <input id="fileUpload" type="file" /> </tr> </table> <div style="text-align: center; padding: 10px;"> <input type="button" onclick="onUpload()" value="上传" /> <input type="button" onclick="onCancel()" value="取消" /> </div> </div> @section Script { <script type="text/javascript"> function onUpload(e) { var url = "@Html.Raw(ViewBag.ImportAction)"; var data = new FormData(); var files = $("#fileUpload").get(0).files; // Add the uploaded image content to the form data collection if (files.length < 0) { mini.alert("请上传导入文件!"); } data.append("uploadFile", files[0]); // Make Ajax request with the contentType = false, and procesDate = false $.ajax({ type: "POST", url: url, contentType: false, processData: false, data: data, success: function (e) { mini.showMessageBox({ title: "导入提示!", iconCls: "mini-messagebox-question", buttons: ["ok", "cancel"], message: e.Message, callback: function (action) { if (e.State == 0 || e.State == 1) { return; } if (action == "ok") { //ajax post download file $.dynamicSubmit = function (url) { var form = $('#dynamicForm'); if (form.length <= 0) { form = $("<form>"); form.attr('id', 'dynamicForm'); form.attr('style', 'display:none'); form.attr('target', ''); form.attr('method', 'post'); var input = ''; $.each(e.Data, function (i, model) { $.each(model, function (k, v) { input += '<input type="hidden" name="viewModels[' + i + '].' + k + '" value="' + v + '" />'; }); }); $('body').append(form.append(input)); } form = $('#dynamicForm'); form.attr('action', url); form.submit(); } $.dynamicSubmit("@ViewBag.ExportTemplateUrl"); } } }); var file = $("#fileUpload"); file.after(file.clone().val("")); file.remove(); } }); } </script> }
大批量excel数据的处理
这里我们之前虽然做了分片,但是大量数据,几万几十万数据的时候肯定会很慢,我们知道sqlserver有一种bcp的导入方式,但是直接使用bcp的话数据更新又成了另外一个问题,这里我给出一个方案
- 定义表变量数据类型(列一般是原表列的一个子集)
- 编写存储过程逻辑(入参是之前定义表变量类型,然后用select into 来导入)
- 界面导入数据后显示在一个列表中
- 给出验证提示让用户需改excel数据,重复上一步和这一步,直到数据全部验证通过
- 调用存储过程提交DataTable到数据库
通过上述步骤就可以做到快速的导入数据了,这样就结合了之前逻辑和速度。这里表变量的使用请参考如下链接:http://blog.csdn.net/downmoon/article/details/7431881
其实百度的话有好多,具体自己参考下。
总结
这里我采用组合的逻辑组装的服务,考虑到不是所有增删该查的服务都需要导入导出,但是几乎所有的导入导出界面都需要增删改查的界面,所以controller采用继承来实现。
核心业务代码部分就贴了代码没有做讲解,基本上在前面几章都有介绍,所以省略掉了,有问题留言吧。
最近破事好多,代码直接从项目贴的也许无法使用,大家看看写法和实现即可。两周了写了一篇,下周争取这周末能把整个设计的代码整合成一个demo.
下一篇更精简的设计只会有设计思路,并不会有具体的实现,因为我并不推崇这种方式,只是展示一下,这个设计可以让代码精简到什么程度。