前端代码
<button type="button" class="layui-btn" id="uploadExcel"><i class="fa fa-lg fa-file-excel-o"></i> 上传Excel
</button>layui.config({base: 'assets/layuiadmin/' //静态资源所在路径}).extend({index: 'lib/index', //主入口模块excel: 'layui_exts/excel'}).use(['form', 'upload' , 'excel'], function(){var form = layui.form;var excel = layui.excel;var timestart = Date.now();var uploadLoading;// Excel上传layui.upload.render({elem: '#uploadExcel',url: 'uploadexcel',accept: 'file',exts: 'xlsx',data: {},field: 'excel',before: function() {this.data = { "name": "动态携带参数"};uploadLoading = layer.msg('正在导入中，请稍候...', {icon: 16, time: 0, shade: 0.3});timestart = Date.now();},done: function(data) {console.log(data);// 弹窗. 显示导入结果var array = new Array();for(var key in data) {array.push({ "number" : key , "result" : data[key]})}layer.close(uploadLoading);exportApi(array);},error: function(data, index){layer.close(uploadLoading);if(data != null) {top.dialog.msg(data.msg);}},});function exportApi(list) {var loadLayerIndex = top.dialog.msg("正在导出Excel数据中...", {icon: 16, time: 0, shade: 0.3});/*** v：单元格的值* t：单元格的类型 'b'布尔值、'n'数字、'e'错误、's'字符串、'd'日期* s：单元格的样式*/var data = [{number: getExcelFormat("行号", true , '000000'),result: getExcelFormat("结果", true , '000000'),}];for(var i = 0 ; i < list.length; i++) {data.push({number:getExcelFormat(list[i].number, false , '000000'),result:getExcelFormat(list[i].result, list[i].result.indexOf("成功") != -1 ? false : true, list[i].result.indexOf("成功") != -1 ? 'FF9900' : 'FF0000')});}// A列宽 200, 默认给80var colConf = excel.makeColConfig({'A': 150,'B': 450,}, 140);// 每一行的行高, 默认给20var rowConf = excel.makeRowConfig({1: 30,}, 20)excel.exportExcel({sheet1: data}, "数据导入结果" + '.xlsx', 'xlsx', {extend: {sheet1: {'!cols': colConf, '!rows': rowConf}}})var timeend = Date.now()var spent = (timeend - timestart) / 1000top.dialog.close(loadLayerIndex);layer.msg('导入耗时 :' + spent + ' 秒');}
});
Java后台代码
@PostMapping("uploadexcel")public synchronized JSONObject saveUploadExcel(MultipartFile excel, String name) {String extName = ""; // 扩展名格式：String saveFilePath = "";try {if (excel.getOriginalFilename().lastIndexOf(".") >= 0){extName = excel.getOriginalFilename().substring(excel.getOriginalFilename().lastIndexOf("."));}if (!extName.equals(".xlsx")) {throw new SaveException("文件格式错误");}String fileName = UUIDUtil.getUUID();File file = new File(fileProperties.getUploadPath());if(!file.exists()) {file.mkdirs();}File saveFile = new File(fileProperties.getUploadPath() + File.separator + "tempFile" + File.separator + fileName + extName);FileUtils.copyInputStreamToFile(excel.getInputStream(), saveFile);saveFilePath = fileProperties.getUploadPath() + File.separator + "tempFile" + File.separator + fileName + extName;} catch (IOException e) {e.printStackTrace();}JSONObject json = new JSONObject();try {json = execlUpload(saveFilePath);}catch(Exception e) {e.printStackTrace();}return json;}public JSONObject execlUpload(String saveFilePath) {File areaDataFile = new File(saveFilePath);JSONObject resultMap = new JSONObject();final int[] lineNumber = {1};final int[] starNumber = {1};final int[] notData = {0};final Map<String, Integer> identityMap = new HashMap<String, Integer>();// 读取第一个sheet 文件流会自动关闭EasyExcel.read(areaDataFile, new AnalysisEventListener<Map<Integer, String>>() {//直接使用Map来保存数据@Overridepublic void invoke(Map<Integer, String> rowData, AnalysisContext context) {lineNumber[0] = lineNumber[0] +1;if (starNumber[0] > lineNumber[0]) {return;}int currentLineNumber = lineNumber[0];Map<String, Object> saveMap = new HashMap<String, Object>();List<Map<String, Object>> queryList = new ArrayList<Map<String, Object>>();String identity = rowData.get(0) == null ? "" : rowData.get(0).trim();// 获取每行第一列// 判断本行是否有效if (StringUtil.isEmpty(identity)) {resultMap.put(currentLineNumber + "", "该行无效");notData[0] = notData[0] + 1;return;} else {notData[0] = 0;}// 连续3行无效则不再继续if(notData[0] > 3) {return;}// 判断是否有重复if (identityMap.get(identity) != null) {resultMap.put(currentLineNumber + "", "身份证号与第" + identityMap.get(identity) + "行重复");return;}else{identityMap.put(identity, currentLineNumber);}// 对数据校验if (!IdCardVerifyUtil.isIDCard(identity) ) {resultMap.put(currentLineNumber + "", "格式错误");return;}try{// 做存储等操作resultMap.put(currentLineNumber + "", "处理成功");}catch(Exception e) {resultMap.put(currentLineNumber + "", "处理失败【" + e.getLocalizedMessage() + "】");e.printStackTrace();}}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {//所有行都解析完成}}).sheet().headRowNumber(1).doRead();return resultMap;}
