layui上传Excel更新数据并下载

前言: 最近做项目遇到了一个需求,上传Excel获取数据更新Excel文档,并直接返回更新完的Excel到前端下载;其实需求并没有什么问题,关键是前端用到的是layui上传组件(layui.upload)踩了不少坑啊;为此写下了如下笔记:

(一)后端:

  1  public async Task<string> UploadExcelUpdateExcel()
  2         {
  3             var file = Request.Form.Files.FirstOrDefault();//这里只获取一个文件
  4             if (file == null)
  5                 throw new UserFriendlyException(L("File_Empty_Error"));
  6 
  7             long fileSize = file.Length;
  8             if (fileSize > 102400)
  9                 throw new UserFriendlyException(L("File_SizeLimit_Error"));
 10 
 11             string fileExtension = Path.GetExtension(file.FileName).ToLower();
 12             //限定只能上传xls和xlsx
 13             string[] allowExtension = { ".xls", ".xlsx" };
 14 
 15             //上传文件类型不正确
 16             if (!allowExtension.Any(x => x == fileExtension))
 17                 throw new UserFriendlyException(L("File_Invalid_Type_Error"));
 18 
 19             //获取本机储存地址
 20             var filePath = Path.Combine(_hostingEnvironment.WebRootPath, "uploadfiles", file.FileName);
 21 
 22             //写入cookie
 23             var httpContext = IocManager.Instance.Resolve<IHttpContextAccessor>().HttpContext;
 24             
 25             try
 26             {
 27                 var memoryStream = new MemoryStream();
 28 
 29                 //创建工作台
 30                 IWorkbook workbook = null;
 31                 //操作Excel
 32                 using (var fileStream = file.OpenReadStream())
 33                 {
 34                     //判断版本2007版本.xlsx,2003版本.xls
 35                     if (fileExtension == ".xlsx")
 36                         workbook = new XSSFWorkbook(fileStream);
 37                     else
 38                         workbook = new HSSFWorkbook(fileStream);
 39                     //获取第一个sheet
 40                     ISheet sheet = workbook.GetSheetAt(0);
 41                     IRow rowSour;//定义行数据
 42                     //定义要修改的列索引
 43                     var cellIndex = new int[] { 6, 11 };
 44                     //获取第一行的数据
 45                     var firstSour = sheet.GetRow(1);
 46                     var input = new ImportExcelSourInput();
 47                     input.BeginDate = Convert.ToDateTime(firstSour.GetCell(4).ToString()).AddMonths(-1);
 48                     input.EndDate = Convert.ToDateTime(firstSour.GetCell(5).ToString()).AddMonths(-1);
 49                     //获取数据源
 50                     var usersSour = await _basicSalaryAppService.ImportExcelSourServices(input);
 51 
 52                     //遍历所有行
 53                     var cells = sheet.GetRow(0).PhysicalNumberOfCells;
 54                     for (var i = 1; i <= sheet.LastRowNum; i++)
 55                     {
 56                         rowSour = sheet.GetRow(i);
 57                         if (rowSour == null || cells != rowSour.PhysicalNumberOfCells)
 58                             break;
 59                         //获取第三列的这个人的身份证
 60                         var IDCard = rowSour.GetCell(3).ToString();
 61                         var thisSour = usersSour.Where(s => s.IDCard == IDCard).FirstOrDefault();
 62                         //修改本期收入,住房公积金
 63                         if (thisSour != null)
 64                         {
 65                             rowSour.GetCell(cellIndex[0]).SetCellValue(Convert.ToDouble(thisSour.ShouldSalary));
 66                             rowSour.GetCell(cellIndex[1]).SetCellValue(Convert.ToDouble(thisSour.PublicAccumulationFundsDeduction));
 67                         }
 68                         else
 69                         {
 70                             var Name = rowSour.GetCell(1).ToString();
 71                             throw new UserFriendlyException($"系统中:[ {Name} ] 身份证号:( {IDCard} )与Excel数据不一致,请修正员工档案数据!");
 72                         }
 73                     }
 74                     //写入流
 75                     workbook.Write(memoryStream);
 76                     //恢复起始位置
 77                     memoryStream.Position = 0;
 78                     //关闭
 79                     fileStream?.Close();
 80                     workbook?.Close();
 81                 }
 82                 //保存至本地
 83                 using (var fileStm = new FileStream(filePath, FileMode.Create))
 84                 {
 85                     memoryStream.WriteTo(fileStm);
 86 
 87                     fileStm.Dispose();
 88                 }
 89             }
 90             catch (Exception e)
 91             {
 92                 httpContext.Response.Cookies.Append("xinzi_message", Convert.ToBase64String(Encoding.UTF8.GetBytes($"上传错误!错误消息:{e.Message}")), new CookieOptions
 93                 {
 94                     Expires = DateTime.Now.AddMinutes(5)
 95                 });
 96 
 97                 throw new UserFriendlyException($"上传错误!错误消息:{e.Message}");
 98             }
 99 
100             var path = $"{httpContext.Request.Scheme}://{httpContext.Request.Host.Value}/uploadfiles/{file.FileName}";
101             return path;//文件路径;
102         }
后端上传服务代码

(二)前端:

 1  //读取cookies 
 2         function getCookie(name) {
 3             var arr, reg = new RegExp("(^| )" + name + "=([^;]*)(;|$)");
 4             if (arr = document.cookie.match(reg))
 5                 return unescape(arr[2]);
 6             else
 7                 return null;
 8         }
 9 
10  upload.render({
11             elem: '#upload-salary'
12             , url: 'UploadExcelUpdateExcel'
13             , acceptMime: ".xlsx,.xls"
14             , before: function (obj) {
15                 abp.ui.setBusy("body") //上传loading
16             }
17             , type: "file"
18             , accept: 'file' //普通文件
19             , done: function (res) {
20                 var a = document.createElement('a');
21                 a.download = "导出文件名";
22                 a.href = res.result;
23                 $("body").append(a);
24                 a.click();
25                 abp.ui.clearBusy("body")  //关闭loading
26             }
27             , error: function () {
28                 var base = new Base64();
29                 //获取后端写入的cookie message
30                 var msg = getCookie("xinzi_message");
31                 if (msg != null)
32                     abp.message.error(base.decode(msg));
33                 abp.ui.clearBusy("body")  //关闭loading
34             }
35         });
前端渲染代码

  存在的问题:

    1),layui.upload上传组件成功后,返回类型应该是不支持返回流文件下载(欢迎大佬们指正解决方案),无奈之举我只有在后端生成好填充完数据的Excel表格,返回服务器文件路径下载了;

    2),layui.upload上传组件失败的时候,服务器端抛出异常消息,前端无法展示,失败回调函数只有两个参数(当前文件的索引,上传函数,官网解释如下图),没有返回参数!(我去什么情况,淡淡的忧桑啊!)

   百般思考终于有了如下解决方案:

      a.后端catch捕获到异常消息后,将异常消息填充到响应的Cookie中;前端异常回调函数里获取Cookie来获取后端传来的消息异常。前后端代码示例:

      b.此时layui默认的上传失败回调函数,会抛出一个消息框上传失败,这里就需要手动改下upload.js的失败回调消息提示了,我是把默认的消息提示删了;

 

   注:后端返回中文消息前端可能会有显示问题,我个人是后端base64加密,前端解密来完成显示的;

 

  1 /**
  2 *
  3 *  Base64 encode / decode
  4 *
  5 *  @author haitao.tu
  6 *  @date   2010-04-26
  7 *  @email  tuhaitao@foxmail.com
  8 *
  9 */
 10 
 11 function Base64() {
 12 
 13     // private property
 14     _keyStr = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=";
 15 
 16     // public method for encoding
 17     this.encode = function (input) {
 18         var output = "";
 19         var chr1, chr2, chr3, enc1, enc2, enc3, enc4;
 20         var i = 0;
 21         input = _utf8_encode(input);
 22         while (i < input.length) {
 23             chr1 = input.charCodeAt(i++);
 24             chr2 = input.charCodeAt(i++);
 25             chr3 = input.charCodeAt(i++);
 26             enc1 = chr1 >> 2;
 27             enc2 = ((chr1 & 3) << 4) | (chr2 >> 4);
 28             enc3 = ((chr2 & 15) << 2) | (chr3 >> 6);
 29             enc4 = chr3 & 63;
 30             if (isNaN(chr2)) {
 31                 enc3 = enc4 = 64;
 32             } else if (isNaN(chr3)) {
 33                 enc4 = 64;
 34             }
 35             output = output +
 36                 _keyStr.charAt(enc1) + _keyStr.charAt(enc2) +
 37                 _keyStr.charAt(enc3) + _keyStr.charAt(enc4);
 38         }
 39         return output;
 40     }
 41 
 42     // public method for decoding
 43     this.decode = function (input) {
 44         var output = "";
 45         var chr1, chr2, chr3;
 46         var enc1, enc2, enc3, enc4;
 47         var i = 0;
 48         input = input.replace(/[^A-Za-z0-9\+\/\=]/g, "");
 49         while (i < input.length) {
 50             enc1 = _keyStr.indexOf(input.charAt(i++));
 51             enc2 = _keyStr.indexOf(input.charAt(i++));
 52             enc3 = _keyStr.indexOf(input.charAt(i++));
 53             enc4 = _keyStr.indexOf(input.charAt(i++));
 54             chr1 = (enc1 << 2) | (enc2 >> 4);
 55             chr2 = ((enc2 & 15) << 4) | (enc3 >> 2);
 56             chr3 = ((enc3 & 3) << 6) | enc4;
 57             output = output + String.fromCharCode(chr1);
 58             if (enc3 != 64) {
 59                 output = output + String.fromCharCode(chr2);
 60             }
 61             if (enc4 != 64) {
 62                 output = output + String.fromCharCode(chr3);
 63             }
 64         }
 65         output = _utf8_decode(output);
 66         return output;
 67     }
 68 
 69     // private method for UTF-8 encoding
 70     _utf8_encode = function (string) {
 71         string = string.replace(/\r\n/g, "\n");
 72         var utftext = "";
 73         for (var n = 0; n < string.length; n++) {
 74             var c = string.charCodeAt(n);
 75             if (c < 128) {
 76                 utftext += String.fromCharCode(c);
 77             } else if ((c > 127) && (c < 2048)) {
 78                 utftext += String.fromCharCode((c >> 6) | 192);
 79                 utftext += String.fromCharCode((c & 63) | 128);
 80             } else {
 81                 utftext += String.fromCharCode((c >> 12) | 224);
 82                 utftext += String.fromCharCode(((c >> 6) & 63) | 128);
 83                 utftext += String.fromCharCode((c & 63) | 128);
 84             }
 85 
 86         }
 87         return utftext;
 88     }
 89 
 90     // private method for UTF-8 decoding
 91     _utf8_decode = function (utftext) {
 92         var string = "";
 93         var i = 0;
 94         var c = c1 = c2 = 0;
 95         while (i < utftext.length) {
 96             c = utftext.charCodeAt(i);
 97             if (c < 128) {
 98                 string += String.fromCharCode(c);
 99                 i++;
100             } else if ((c > 191) && (c < 224)) {
101                 c2 = utftext.charCodeAt(i + 1);
102                 string += String.fromCharCode(((c & 31) << 6) | (c2 & 63));
103                 i += 2;
104             } else {
105                 c2 = utftext.charCodeAt(i + 1);
106                 c3 = utftext.charCodeAt(i + 2);
107                 string += String.fromCharCode(((c & 15) << 12) | ((c2 & 63) << 6) | (c3 & 63));
108                 i += 3;
109             }
110         }
111         return string;
112     }
113 }
前端base64解密,js代码

 

posted @ 2019-07-27 16:30  代码驿站  阅读(2116)  评论(0编辑  收藏  举报