1. 前台代码
1 <!DOCTYPE html> 2 <html lang="en" xmlns:th="http://www.thymeleaf.org"> 3 <head> 4 <meta charset="UTF-8"> 5 <title>导入excel测试</title> 6 <style> 7 #fatherDiv{ 8 width:100px; 9 height:20px; 10 border:1px solid green; 11 } 12 #sonDiv{ 13 width:0px; 14 height:20px; 15 background:green; 16 } 17 </style> 18 19 <script th:src="@{|/js/jquery-2.1.4.min.js/|}"></script> 20 <script> 21 $(function () { 22 23 $("#importExcel").bind("click", function () { 24 25 /* 判断是否有导入文件 */ 26 if (!$("#excel").val()) { 27 window.alert("请导入excel文件"); 28 return; 29 } 30 31 /* 判断输入的文件的类型 */ 32 var splitArray = $("#excel").val().toLowerCase().split("."); 33 var type = splitArray[splitArray.length - 1]; 34 if (type != "xls" && type != "xlsx") { 35 window.alert("导入的文件类型有误"); 36 return; 37 } 38 39 // 获取excel对象 40 var excel = $("#excel")[0].files[0]; 41 42 /* 判断输入的excel文件的大小 */ 43 var excelSize = excel.size; 44 if (excelSize > 1024 * 1024 * 10) { 45 window.alert("当前上传的excel文件的大小为" + 46 Math.round(excelSize / 1024 / 1024 * 100) / 100 + 47 "M,超过10M"); 48 return; 49 } 50 51 var formData = new FormData(); 52 formData.append("file", excel); 53 54 // ajax异步文件上传 55 $.ajax({ 56 type: "post", 57 url: "/importExcel", 58 data: formData, 59 contentType: false, // 不再采用普通的form表单元素提交方式。(multipart/form-data) 60 processData: false, // 提交文件，不是提交普通的字符串。 61 xhr: function () { // 显示上传进度条 62 myXhr = $.ajaxSettings.xhr(); 63 if (myXhr.upload) { 64 myXhr.upload.addEventListener('progress', function (e) { 65 var loaded = e.loaded;//已经上传大小情况 66 var tot = e.total;//附件总大小 67 var per = Math.floor(100 * loaded / tot); //已经上传的百分比 68 $("#sonDiv").html(per + "%"); 69 $("#sonDiv").css("width", per + "%"); 70 $("#schedule").html(Math.round(loaded / 1024 / 1024 * 100) / 100 + "M"); 71 console.log('附件总大小 = ' + loaded); 72 console.log('已经上传大小 = ' + tot); 73 }, false); 74 } 75 return myXhr; 76 }, 77 success: function (json) { 78 if (json.result) { 79 alert("导入成功") 80 } else { 81 alert(json.errorMessage); 82 } 83 } 84 }); 85 }) 86 }) 87 </script> 88 </head> 89 <body> 90 请选择你要导入的excel文件(.xls;.xlsx)<br/> 91 <input type="file" id="excel"/> 92 <div id="fatherDiv"> 93 <div id="sonDiv"></div> <span id="schedule">已上传 0M</span> 94 <button id="importExcel">导入</button> 95 </div><br/> 96 97 98 99 </body> 100 </html>
2. 后台spring代码
2.1 controller层
1 package com.bjpowernode.excel.importExcel.controller; 2 3 import com.bjpowernode.excel.importExcel.service.ImportExcelService; 4 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 5 import org.apache.poi.ss.usermodel.Workbook; 6 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 7 import org.springframework.beans.factory.annotation.Autowired; 8 import org.springframework.stereotype.Controller; 9 import org.springframework.web.bind.annotation.RequestMapping; 10 import org.springframework.web.bind.annotation.RequestParam; 11 import org.springframework.web.bind.annotation.ResponseBody; 12 import org.springframework.web.multipart.MultipartFile; 13 14 import java.io.IOException; 15 import java.io.InputStream; 16 import java.lang.reflect.InvocationTargetException; 17 import java.util.HashMap; 18 import java.util.Map; 19 20 /** 21 * ClassName:ImportExcel 22 * Package:com.bjpowernode.excel.importExcel.controller 23 * Description: 24 * 25 * @Date:2019/2/25 21:46 26 * @Author: 郑军 27 */ 28 @Controller 29 public class ImportExcelController { 30 31 @Autowired 32 private ImportExcelService importExcelService; 33 34 @RequestMapping("/") 35 public String toIndex() { 36 return "index"; 37 } 38 39 @RequestMapping("/importExcel") 40 @ResponseBody 41 public Map<String, Object> importExcel(@RequestParam("file") MultipartFile file) { 42 43 Map<String, Object> map = new HashMap<>(); 44 45 /** 46 * 判断文件是否为空 47 */ 48 if (file.isEmpty()) { 49 map.put("result", false); 50 map.put("errorMessage", "导入数据为空"); 51 return map; 52 } 53 54 /** 55 * 判断文件类型是否正确 56 */ 57 String originalFilename = file.getOriginalFilename(); 58 String fileType = originalFilename.substring(originalFilename.lastIndexOf(".")); 59 if (!".xls".equals(fileType) && !".xlsx".equals(fileType)) { 60 map.put("result", false); 61 map.put("errorMessage", "导入的文件类型有误"); 62 return map; 63 } 64 65 /** 66 * 判断文件的大小 67 */ 68 long size = file.getSize(); 69 if (size > 1024 * 1024 * 10) { 70 map.put("result", false); 71 map.put("errorMessage", "当前文件大小为"+Math.ceil(size * 100 / 1024/ 1024 / 10 / 100) + 72 "M,超过10M"); 73 return map; 74 } 75 76 try { 77 InputStream inputStream = file.getInputStream(); 78 /** 79 * 通过上传的不同的文件后缀，创建不同的workbook 80 */ 81 Workbook workbook = null; 82 if (".xls".equals(fileType)) { 83 workbook = new HSSFWorkbook(inputStream); 84 }else { 85 workbook = new XSSFWorkbook(inputStream); 86 } 87 88 /** 89 * excel文件解析并且导入数据库。 90 */ 91 Map<String, Object> resultMap = importExcelService.importExcel(workbook); 92 if (resultMap.size() > 0) { 93 map.put("result", resultMap.get("result")); 94 map.put("errorMessage", resultMap.get("errorMessage")); 95 } else { 96 map.put("result", false); 97 map.put("errorMessage", "导入失败"); 98 } 99 100 } catch (Exception e) { 101 e.printStackTrace(); 102 map.put("result", false); 103 map.put("errorMessage", "导入失败"); 104 } 105 106 return map; 107 108 } 109 110 }
2.2 service业务层
1 package com.bjpowernode.excel.importExcel.service.impl; 2 3 import com.bjpowernode.excel.importExcel.domain.Salary; 4 import com.bjpowernode.excel.importExcel.mapper.SalaryMapper; 5 import com.bjpowernode.excel.importExcel.service.ImportExcelService; 6 import org.apache.commons.beanutils.BeanUtils; 7 import org.apache.ibatis.transaction.Transaction; 8 import org.apache.poi.ss.usermodel.Cell; 9 import org.apache.poi.ss.usermodel.Row; 10 import org.apache.poi.ss.usermodel.Sheet; 11 import org.apache.poi.ss.usermodel.Workbook; 12 import org.springframework.beans.factory.annotation.Autowired; 13 import org.springframework.stereotype.Service; 14 import org.springframework.transaction.annotation.Transactional; 15 16 import java.io.IOException; 17 import java.lang.reflect.InvocationTargetException; 18 import java.util.ArrayList; 19 import java.util.HashMap; 20 import java.util.List; 21 import java.util.Map; 22 23 /** 24 * ClassName:ImportExcelServiceImpl 25 * Package:com.bjpowernode.excel.importExcel.service.impl 26 * Description: 27 * 28 * @Date:2019/2/26 21:57 29 * @Author: 郑军 30 */ 31 @Service 32 public class ImportExcelServiceImpl implements ImportExcelService { 33 34 @Autowired 35 private SalaryMapper salaryMapper; 36 37 @Override 38 @Transactional(rollbackFor = RuntimeException.class) 39 public Map<String, Object> importExcel(Workbook workbook) throws InvocationTargetException, IllegalAccessException { 40 41 Map<String, Object> resultMap = new HashMap<>(); 42 List<List<Salary>> salaryDateList = new ArrayList<>(); 43 int numberOfSheets = workbook.getNumberOfSheets(); 44 45 46 for (int i = 0; i < numberOfSheets; i++) { 47 Sheet sheet = workbook.getSheetAt(i); 48 // 将sheet页中的数据，转换成list集合 49 List<Salary> salaryList = parseExcelToList(sheet); 50 // 将每一个sheet页的数据，然后放在一个list集合中。 51 salaryDateList.add(salaryList); 52 53 } 54 55 // 按照业务逻辑，将不同sheet页的数据，插入不同的数据库表中。这个地方只用了一张表 56 for (List<Salary> salaryList : salaryDateList) { 57 salaryMapper.insertExcelData(salaryList); 58 } 59 60 // 导入成功之后，向resultMap中添加返回成功信息 61 resultMap.put("result", true); 62 resultMap.put("errorMessage", "导入成功"); 63 64 return resultMap; 65 } 66 67 /** 68 * 将sheet中的数据转换成一个list集合。 69 * 70 * @param sheet 71 * @return 72 */ 73 private List<Salary> parseExcelToList(Sheet sheet) throws InvocationTargetException, IllegalAccessException { 74 75 // excel导入的时候，对应的数据库中的字段数组。 76 String[] property = {"account", "money"}; 77 List<Salary> salaryList = new ArrayList<>(); 78 79 int lastRowNum = sheet.getLastRowNum(); 80 // 如果sheet页的数据为空 81 if (lastRowNum < 1) { 82 return null; 83 } 84 85 for (int i = 1; i < lastRowNum + 1; i++) { 86 87 Map<String, Object> map = new HashMap<>(); 88 89 Row row = sheet.getRow(i); 90 if (row == null) { 91 continue; 92 } 93 94 for (int j = 0; j < property.length; j++) { 95 Cell cell = row.getCell(j); 96 // 获取单元格对应的数据值。单元格中的数据对应着可能为日期，字符串，数字 97 Object value = getCellValue(cell); 98 map.put(property[j], value); 99 } 100 101 Salary salary = new Salary(); 102 BeanUtils.populate(salary, map); 103 salary.setsId(i + ""); 104 salaryList.add(salary); 105 106 } 107 108 return salaryList; 109 } 110 111 /** 112 * 获取单元格对应的值 113 * 114 * @param cell 单元格对象 115 * @return 单元格对应的值 116 */ 117 private Object getCellValue(Cell cell) { 118 119 int cellType = cell.getCellType(); 120 Object cellValue = null; 121 122 switch (cellType) { 123 124 // 数值获取对应的数据 125 case 0: 126 cellValue = cell.getNumericCellValue(); 127 break; 128 129 // 字符串获取去除首位空格 130 case 1: 131 cellValue = cell.getStringCellValue().trim(); 132 break; 133 134 case 2: 135 cell.setCellType(1); 136 if (cell.getStringCellValue() == null 137 || cell.getStringCellValue().length() == 0) 138 break; 139 cellValue = cell.getStringCellValue().replaceAll("#N/A", "").trim(); 140 cellValue += "&CELL_TYPE_FORMULA"; 141 break; 142 143 case 3: 144 cellValue = ""; 145 break; 146 147 case 4: 148 cellValue = Boolean.toString(cell.getBooleanCellValue()); 149 break; 150 151 case 5: 152 cellValue = ""; 153 break; 154 155 default: 156 cellValue = ""; 157 } 158 159 return cellValue; 160 161 } 162 163 }