1.准备工作
1.1 创建模板表头与数据库表字段一一对应，示例如下
1.2将模板放入项目中,如下图所示：
2.前端页面
2.1 使用超链接提供模板下载地址
1 <html lang="zh_CN" xmlns:th="http://www.thymeleaf.org" 2 xmlns:shiro="http://www.pollix.at/thymeleaf/shiro"> 3 <meta charset="utf-8"> 4 <head th:include="include::header"></head> 5 <title>导入</title> 6 <style type="text/css"> 7 .message .files { 8 position: absolute; 9 left: -1000px; 10 top: 52px; 11 heigth: 26px; 12 cursor: pointer; 13 filter: Alpha(opacity = 0); 14 -moz-opacity: 0; 15 opacity: 0; 16 } 17 </style> 18 </head> 19 <body> 20 <form id="signupForm" method="post" enctype="multipart/form-data"> 21 <input name="publishTaskId" id="publishTaskId" th:value="${publishTaskId}" hidden> 22 <div id="dpLTE" class="container-fluid tc-box"> 23 <table class="form" id="form" style="table-layout: fixed;"> 24 <tr> 25 <td colspan="2"> 26 <a id="zhCna" 27 href='/modelExcle/fieldTemplate.xlsx'> 28 <button type="button" class="btn btn-default" 29 style="margin-bottom: 10px"> 30 <i class="fa fa-download"></i> 下载导入模板 31 </button> 32 </a> 33 <div class="alert alert-warning">提示：请先下载批量导入模板-excel文件，按格式填写后上传提交，方可导入； 34 </div> 35 </td> 36 </tr> 37 <tr> 38 <td><input type="text" id="txt" name="txt" 39 class="input form-control" value="文件域" disabled="disabled" /></td> 40 <td class="message"> 41 <input type="button" 42 onMouseMove="f.style.pixelLeft=event.x-60;f.style.pixelTop=this.offsetTop;" 43 value="选择文件" size="30" onClick="f.click()" class="btn btn-orange" 44 style="margin-left: 10px"> 45 <input type="file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" name="SensitiveExcle" id="f" 46 onChange="txt.value=this.value" style="height: 26px;" class="files" size="1" hidefocus> 47 </td> 48 </tr> 49 </table> 50 </div> 51 <div class="form-group"> 52 <div class="col-sm-6 col-sm-offset-5"> 53 <button type="submit" class="btn btn-primary">提交</button> 54 </div> 55 </div> 56 </form> 57 <div th:include="include::footer"></div> 58 59 <script src="/js/appjs/sys/auditSecPage/import.js"></script> 60 </body> 61 </html>
2.2 js中调用后台方法接收EXCEL文件流
1 function save() { 2 var formData = new FormData($('#signupForm')[0]); 3 $.ajax({ 4 url : "/sys/audit/importdata", 5 type: 'POST', 6 data: formData, 7 async: true, 8 cache: false, 9 contentType: false, 10 processData: false, 11 error : function(request) { 12 parent.layer.alert("网络超时"); 13 }, 14 success : function(data) { 15 if (data.code == 0) { 16 parent.layer.msg("操作成功"); 17 parent.reLoad(); 18 var index = parent.layer.getFrameIndex(window.name); 19 parent.layer.close(index); 20 } else { 21 parent.layer.alert(data.msg) 22 } 23 24 } 25 }); 26 27 }
3.对应后台业务逻辑
3.1 pom文件中引入对应依赖
1 <!-- 文件上传组件 --> 2 <!-- https://mvnrepository.com/artifact/commons-net/commons-net --> 3 <dependency> 4 <groupId>commons-net</groupId> 5 <artifactId>commons-net</artifactId> 6 <version>3.3</version> 7 </dependency> 8 <dependency> 9 <groupId>org.apache.poi</groupId> 10 <artifactId>poi</artifactId> 11 <version>3.9</version> 12 </dependency> 13 <dependency> 14 <groupId>org.apache.poi</groupId> 15 <artifactId>poi-ooxml</artifactId> 16 <version>3.9</version> 17 </dependency>
3.2对应的工具类编写
3.2.1封装返回结果
1 import java.util.HashMap; 2 import java.util.Map; 3 4 public class R extends HashMap<String, Object> { 5 private static final long serialVersionUID = 1L; 6 7 public R() { 8 put("code", 0); 9 put("msg", "操作成功"); 10 } 11 12 public static R error() { 13 return error(1, "操作失败"); 14 } 15 16 public static R error(String msg) { 17 return error(500, msg); 18 } 19 20 public static R error(int code, String msg) { 21 R r = new R(); 22 r.put("code", code); 23 r.put("msg", msg); 24 return r; 25 } 26 27 public static R ok(String msg) { 28 R r = new R(); 29 r.put("msg", msg); 30 return r; 31 } 32 33 public static R ok(Map<String, Object> map) { 34 R r = new R(); 35 r.putAll(map); 36 return r; 37 } 38 39 public static R ok() { 40 return new R(); 41 } 42 43 @Override 44 public R put(String key, Object value) { 45 super.put(key, value); 46 return this; 47 } 48 }
3.2.2 Excel导入工具类
1 import org.apache.commons.lang.StringUtils; 2 import org.apache.poi.hssf.usermodel.HSSFCell; 3 import org.apache.poi.hssf.usermodel.HSSFDateUtil; 4 import org.apache.poi.ss.usermodel.Cell; 5 import org.apache.poi.ss.usermodel.Row; 6 import org.apache.poi.ss.usermodel.Sheet; 7 import org.apache.poi.ss.usermodel.Workbook; 8 import org.apache.poi.xssf.usermodel.XSSFCell; 9 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 10 11 import java.io.FileInputStream; 12 import java.io.FileNotFoundException; 13 import java.io.IOException; 14 import java.text.DateFormat; 15 import java.text.DecimalFormat; 16 import java.text.SimpleDateFormat; 17 import java.util.ArrayList; 18 import java.util.Date; 19 import java.util.List; 20 21 22 /** 23 * Excel导入工具类 24 * 25 * @author Evan.Zhang 26 */ 27 public class ImportExcelUtils { 28 29 /** 30 * 创建WorkBook对象 31 * 32 * @param filePath 33 * @return 34 * @throws IOException 35 */ 36 public static final Workbook createWorkbook(String filePath) throws IOException { 37 if (StringUtils.isBlank(filePath)) { 38 throw new IllegalArgumentException(MassageUtils.getMessage("10011")); 39 } 40 if (!FileUtil.isExists(filePath)) { 41 throw new FileNotFoundException(MassageUtils.getMessage("10012")); 42 } 43 if (filePath.trim().toLowerCase().endsWith("xls")) { 44 return new XSSFWorkbook(new FileInputStream(filePath)); 45 } else if (filePath.trim().toLowerCase().endsWith("xlsx")) { 46 return new XSSFWorkbook(new FileInputStream(filePath)); 47 } else { 48 throw new IllegalArgumentException(MassageUtils.getMessage("10013")); 49 } 50 } 51 52 /** 53 * 获取Sheet页面(按名称) 54 * 55 * @param wb 56 * @param sheetName 57 * @return 58 */ 59 public static final Sheet getSheet(Workbook wb, String sheetName) { 60 return wb.getSheet(sheetName); 61 } 62 63 /** 64 * 获取Sheet页面(按页标) 65 * 66 * @param wb 67 * @param index 68 * @return 69 */ 70 public static final Sheet getSheet(Workbook wb, int index) { 71 return wb.getSheetAt(index); 72 } 73 74 /** 75 * 获取Sheet页内容 76 * 77 * @param sheet 78 * @return 79 */ 80 public static final List<Object[]> listFromSheet(Sheet sheet) { 81 82 List<Object[]> list = new ArrayList<Object[]>(); 83 for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) { 84 Row row = sheet.getRow(r); 85 if (row == null || row.getPhysicalNumberOfCells() == 0) continue; 86 Object[] cells = new Object[row.getLastCellNum()]; 87 for (int c = row.getFirstCellNum(); c <= row.getLastCellNum(); c++) { 88 Cell cell = row.getCell(c); 89 if (cell == null) continue; 90 //判断是否为日期类型 91 if (HSSFDateUtil.isCellDateFormatted(cell)) { 92 //用于转化为日期格式 93 Date d = cell.getDateCellValue(); 94 DateFormat formater = new SimpleDateFormat("yyyy-MM-dd"); 95 cells[c] = formater.format(d); 96 } else { 97 cells[c] = getValueFromCell(cell); 98 } 99 100 101 } 102 list.add(cells); 103 } 104 return list; 105 } 106 107 108 /** 109 * 获取单元格内信息 110 * 111 * @param cell 112 * @return 113 */ 114 public static final Object getValueFromCell(Cell cell) { 115 if (cell == null) { 116 System.out.println("Cell is null !!!"); 117 return null; 118 } 119 Object result = null; 120 if (cell instanceof HSSFCell) { 121 if (cell != null) { 122 // 单元格类型：Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5 123 int cellType = ((HSSFCell) cell).getCellType(); 124 switch (cellType) { 125 case HSSFCell.CELL_TYPE_STRING: 126 result = ((HSSFCell) cell).getStringCellValue(); 127 break; 128 case HSSFCell.CELL_TYPE_NUMERIC: 129 DecimalFormat df = new DecimalFormat("###.####"); 130 result = df.format(((HSSFCell) cell).getNumericCellValue()); 131 break; 132 case HSSFCell.CELL_TYPE_FORMULA: 133 result = ((HSSFCell) cell).getNumericCellValue(); 134 break; 135 case HSSFCell.CELL_TYPE_BOOLEAN: 136 result = ((HSSFCell) cell).getBooleanCellValue(); 137 break; 138 case HSSFCell.CELL_TYPE_BLANK: 139 result = null; 140 break; 141 case HSSFCell.CELL_TYPE_ERROR: 142 result = null; 143 break; 144 default: 145 System.out.println("枚举了所有类型"); 146 break; 147 } 148 } 149 } else if (cell instanceof XSSFCell) { 150 if (cell != null) { 151 // 单元格类型：Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5 152 int cellType = ((XSSFCell) cell).getCellType(); 153 switch (cellType) { 154 case XSSFCell.CELL_TYPE_STRING: 155 result = ((XSSFCell) cell).getRichStringCellValue().getString(); 156 break; 157 case XSSFCell.CELL_TYPE_NUMERIC: 158 DecimalFormat df = new DecimalFormat("###.####"); 159 result = df.format(((XSSFCell) cell).getNumericCellValue()); 160 break; 161 case XSSFCell.CELL_TYPE_FORMULA: 162 result = ((XSSFCell) cell).getNumericCellValue(); 163 break; 164 case XSSFCell.CELL_TYPE_BOOLEAN: 165 result = ((XSSFCell) cell).getBooleanCellValue(); 166 break; 167 case XSSFCell.CELL_TYPE_BLANK: 168 result = null; 169 break; 170 case XSSFCell.CELL_TYPE_ERROR: 171 result = null; 172 break; 173 default: 174 System.out.println("枚举了所有类型"); 175 break; 176 } 177 } 178 } 179 return result; 180 } 181 182 183 /** 184 * 根据Sheet页导入Excel信息 185 * 186 * @param filePath 文件路径 187 * @param sheetIndex Sheet页下标 188 * @param startRow 开始列 ：默认第一列 189 * @param startLine 开始行 ：默认第一行 190 * @throws Exception 191 */ 192 public static final List<Object[]> importExcelBySheetIndex(String filePath, int sheetIndex 193 , int startRow, int startLine) throws Exception { 194 195 List<Object[]> resultList = null; 196 197 //创建WorkBook对象 198 Workbook wb = createWorkbook(filePath); 199 200 // 获取Sheet 201 Sheet sheet = ImportExcelUtils.getSheet(wb, sheetIndex); 202 203 // 判断Sheet是否为空 204 if (sheet != null) { 205 206 // 遍历Sheet 207 List<Object[]> list = ImportExcelUtils.listFromSheet(sheet); 208 if (list != null && list.size() > 0) { 209 resultList = new ArrayList<Object[]>(); 210 if (startLine <= list.size()) { 211 for (int i = startLine; i < list.size(); i++) { 212 int nullCount = 0; 213 Object[] rows = list.get(i); 214 if (rows != null && rows.length > 0) { 215 List<Object> resultObjects = new ArrayList<Object>(); 216 for (int n = startRow; n < rows.length; n++) { 217 if (IsNullUtils.isEmpty(rows[n])) { 218 nullCount++; 219 } 220 resultObjects.add(rows[n]); 221 } 222 223 //判断空的单元格个数 224 if (nullCount >= rows.length) { 225 break; 226 } else { 227 resultList.add(resultObjects.toArray()); 228 } 229 } 230 } 231 } 232 } 233 } 234 return resultList; 235 } 236 }
3.2.3 封装判空方法工具类
1 import java.util.List; 2 import java.util.Map; 3 import java.util.Set; 4 5 /** 6 * 判空方法工具类 7 * 8 */ 9 public class IsNullUtils { 10 11 12 /** 13 * 对象是否为空 14 * @param o String,List,Map,Object[],int[],long[] 15 * @return 16 */ 17 @SuppressWarnings("rawtypes") 18 public static boolean isEmpty(Object o) { 19 if (o == null) { 20 return true; 21 } 22 if (o instanceof String) { 23 if (o.toString().trim().equals("")) { 24 return true; 25 } 26 if (o.equals("null") || o.equals("NULL")) { 27 return true; 28 } 29 } else if (o instanceof List) { 30 if (((List) o).size() == 0) { 31 return true; 32 } 33 } else if (o instanceof Map) { 34 if (((Map) o).size() == 0) { 35 return true; 36 } 37 } else if (o instanceof Set) { 38 if (((Set) o).size() == 0) { 39 return true; 40 } 41 } else if (o instanceof Object[]) { 42 if (((Object[]) o).length == 0) { 43 return true; 44 } 45 } else if (o instanceof int[]) { 46 if (((int[]) o).length == 0) { 47 return true; 48 } 49 } else if (o instanceof long[]) { 50 if (((long[]) o).length == 0) { 51 return true; 52 } 53 } 54 return false; 55 } 56 57 58 }
3.2.4 文件上传方法
1 import org.springframework.web.multipart.MultipartFile; 2 import javax.servlet.http.HttpServletRequest; 3 import java.io.File; 4 import java.io.IOException; 5 6 /** 7 * 文件上传 8 */ 9 public class UploadFile { 10 11 /** 12 * 文件上传方法 13 */ 14 15 public static boolean fileUpLoad(MultipartFile[] files, HttpServletRequest request, String path) 16 throws IOException { 17 18 if (files != null && files.length > 0) { 19 for (int i = 0; i < files.length; i++) { 20 MultipartFile file = files[i]; 21 // 保存文件 22 return saveFile(request, file, path); 23 } 24 } 25 return false; 26 } 27 28 /** 29 * 保存上传文件 30 * 31 * @param request 32 * @param file 33 * @return 34 */ 35 36 public static boolean saveFile(HttpServletRequest request, MultipartFile file, String path) { 37 38 if (!file.isEmpty()) { 39 try { 40 File saveDir = new File(path); 41 if (!saveDir.getParentFile().exists()) 42 saveDir.getParentFile().mkdirs(); 43 // 转存文件 44 file.transferTo(saveDir); 45 return true; 46 } catch (Exception e) { 47 e.printStackTrace(); 48 } 49 } 50 return false; 51 } 52 53 }
3.3控制层接收文件流
1 /* 2 * 批量导入数据 3 * */ 4 @ResponseBody 5 @PostMapping("/importdata") 6 R importdata(@RequestParam("publishTaskId") String publishTaskId,@RequestParam("SensitiveExcle") MultipartFile[] files,HttpServletRequest request)throws Exception { 7 return checkFieldInfoService.importData(publishTaskId,files,request); 8 }
3.4 编写接口
1 import org.springframework.web.multipart.MultipartFile; 2 3 import javax.servlet.http.HttpServletRequest; 4 5 public interface CheckFieldInfoService { 6 R importData(String publishTaskId,MultipartFile[] files, HttpServletRequest request); 7 }
3.5 实现插入业务逻辑
1 package com.system.service.impl; 2 3 4 import com.system.dao.CheckFieldInfoMapper; 5 import com.system.domain.audit.CheckFieldInfo; 6 import com.system.service.CheckFieldInfoService; 7 import com.common.utils.R; 8 import com.system.utils.ImportExcelUtils; 9 import com.system.utils.IsNullUtils; 10 import com.system.utils.UploadFile; 11 import org.apache.poi.ss.usermodel.Sheet; 12 import org.apache.poi.ss.usermodel.Workbook; 13 import org.springframework.beans.factory.annotation.Autowired; 14 import org.springframework.stereotype.Service; 15 import org.springframework.web.multipart.MultipartFile; 16 import javax.servlet.http.HttpServletRequest; 17 import java.io.IOException; 18 import java.util.*; 19 20 @Service 21 public class CheckFieldInfoServiceImpl implements CheckFieldInfoService { 22 23 @Autowired 24 private CheckFieldInfoMapper checkFieldInfoMapper; 25 @Override 26 public R importData(String publishTaskId, MultipartFile[] files, HttpServletRequest request) { 27 int count = 0; 28 /*上传路径*/ 29 String path = "/file/fileExcle/" + files[0].getOriginalFilename(); 30 try { 31 boolean status = UploadFile.fileUpLoad(files, request, path); 32 if (!status) { 33 return R.error("文件上传失败！"); 34 } 35 } catch (IOException e1) { 36 // TODO Auto-generated catch block 37 e1.printStackTrace(); 38 } 39 Workbook workbook = null; //工作簿 40 Sheet sheet = null; //工作表 41 String[] headers = null; //表头信息 42 43 try { 44 workbook = ImportExcelUtils.createWorkbook(path); 45 } catch (Exception e) { 46 e.printStackTrace(); 47 } 48 sheet = ImportExcelUtils.getSheet(workbook, 0); 49 List<Object[]> oList = ImportExcelUtils.listFromSheet(sheet); 50 if (!IsNullUtils.isEmpty(oList)) { 51 headers = Arrays.asList(oList.get(0)).toArray(new String[0]); 52 if (!headers[0].replaceAll(" ", "").equals("ID")) { 53 return R.error("请选择正确模板导入！"); 54 } 55 } 56 57 List<CheckFieldInfo> senList = new ArrayList<CheckFieldInfo>(); 58 if (!IsNullUtils.isEmpty(oList.get(1))) { 59 for (int s = 1; s < oList.size(); s++) { 60 String[] rows = null; 61 rows = Arrays.asList(oList.get(s)).toArray(new String[0]); 62 R r = null; 63 CheckFieldInfo checkFieldInfo = new CheckFieldInfo(); 64 checkFieldInfo.setFieldId(UUID.randomUUID().toString()); 65 checkFieldInfo.setPublishTaskId(publishTaskId); 66 checkFieldInfo.setId(Integer.parseInt(rows[0])); 67 checkFieldInfo.setFieldname(rows[1]); 68 checkFieldInfo.setFieldtype(rows[2]); 69 checkFieldInfo.setLenPrecision(rows[3]); 70 checkFieldInfo.setLenScala(rows[4]); 71 checkFieldInfo.setFieldformat(rows[5]); 72 checkFieldInfo.setChecknull(rows[6]); 73 checkFieldInfo.setCheckrepeat(rows[7]); 74 checkFieldInfo.setCheckenum(rows[8]); 75 checkFieldInfo.setEnumvalue(rows[9]); 76 senList.add(checkFieldInfo); 77 } 78 if (senList.size() > 0) { 79 for (CheckFieldInfo c : senList) { 80 count = checkFieldInfoMapper.insertData(c); 81 if (count<=0){ 82 R.error("批量导入异常"); 83 } 84 85 } 86 87 } 88 } 89 return R.ok(); 90 } 91 }
完成以上操作即可简单实现POI方式使用Excel表格实现数据批量导入功能