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 }
