• CRUD全栈式编程架构之导入导出的设计


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

    组件

    1. office组件
    2. npoi
    3. 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;
            }
            
        }
    }
    

    导入

    有了上面两个扩展,那么实现起来就很方便了,步骤如下

    1. 接受网络流文件
    2. 将文件转换成DataTable
    3. 将DataTable转换成List
    4.  数据验证和预处理
    5.  导入数据库
    6.  返回导入的错误数据(包含错误数据)

    但是要注意下面两个问题

    分片导入

    在遇到大量数据的时候,虽然ef自带了unityofwork,但是如果一次性导入 几万数据会非常慢,而且基于之前的用户体验,如果失败的话就会全部失败。 所以我们将数据分片,分片大小我一般采用100条,这个最好能写成  配置,根据情况调整。这样错误顶多是100条错误。并且分片之后可以结合TPL并行库,并行提交,不过要注意数据库链接和cpu压力

    导入模板

    导入模板可以可以采取动态生成,也可采取预先生成,也可以两者结合的方式 动态生成,跟导出一样,只是数据为空。预先生成就是直接人工编辑放到指定目录。两者结合的话可以采用如果没有静态文件则生成,如果有则直接下载。如果导入模板有变化,可以采取文件名的方式来区分。由于我这里性能影响基本可以忽略,这里采用的是动态生成的方式。

    导出

    基于之前的组件导出步骤分为以下几步

    1. 通过ef查询数据
    2. 导入到DataTable
    3. 转换成excel
    4. 直接通过网络流下载

    这里要注意,由于查询和导出分离的原因最好是通过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的话数据更新又成了另外一个问题,这里我给出一个方案

    1. 定义表变量数据类型(列一般是原表列的一个子集)
    2. 编写存储过程逻辑(入参是之前定义表变量类型,然后用select into 来导入)
    3. 界面导入数据后显示在一个列表中
    4. 给出验证提示让用户需改excel数据,重复上一步和这一步,直到数据全部验证通过
    5. 调用存储过程提交DataTable到数据库

    通过上述步骤就可以做到快速的导入数据了,这样就结合了之前逻辑和速度。这里表变量的使用请参考如下链接:http://blog.csdn.net/downmoon/article/details/7431881

    其实百度的话有好多,具体自己参考下。

    总结

      这里我采用组合的逻辑组装的服务,考虑到不是所有增删该查的服务都需要导入导出,但是几乎所有的导入导出界面都需要增删改查的界面,所以controller采用继承来实现。

    核心业务代码部分就贴了代码没有做讲解,基本上在前面几章都有介绍,所以省略掉了,有问题留言吧。

      最近破事好多,代码直接从项目贴的也许无法使用,大家看看写法和实现即可。两周了写了一篇,下周争取这周末能把整个设计的代码整合成一个demo.

      下一篇更精简的设计只会有设计思路,并不会有具体的实现,因为我并不推崇这种方式,只是展示一下,这个设计可以让代码精简到什么程度。

  • 相关阅读:
    ESRI Shapefiles (SHP)
    Python与开源GIS:在OGR中使用SQL语句进行查询
    [推荐]网店代销的卖家,你的宝贝名称修改了吗?
    怎么把经纬度转换成标准的度分秒单位
    如何提高淘宝流量
    十八种方法提升淘宝店流量
    mysql备份数据库几种方法
    Linux查看文件编码格式及文件编码转换
    MySQL 修改字段类型或长度
    mysql外键使用和级联
  • 原文地址:https://www.cnblogs.com/Skyven/p/5725949.html
Copyright © 2020-2023  润新知