Java poi Excel导入数据

导入POI依赖：

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency>

HTML：

<button type="button" id="importSite" style="background-color: #00D2FF" class="layui-btn">导入站点</button>

JS：用的是layui的上传文件 需要下载并引入layui的js和css layui用法详情见 Layui官方文档

layui.use(['table', 'upload'], function () { //导入 upload.render({ elem: '#importSite' //绑定元素 , url: perfix + '/importSite' //上传接口 , accept: 'file' , exts: 'xlsx' , multiple: false , before: function () { indexC = layer.load(2, { shade: [0.1, '#fff'] //0.1透明度的白色背景 }); } , done: function (res) { //上传完毕回调 layer.close(indexC); var innerHtml = ''; if (res.code == 0) { layer.msg(res.msg, {icon: 1}); } else { layer.close(indexC); layer.msg(res.msg, {icon: 2}) } } , error: function () { //请求异常回调 layer.close(indexC); layer.msg(res.msg, {icon: 2}) } }); });

controller：

@ResponseBody @PostMapping("/importSite") public R importSite(MultipartFile file) { return baseSiteService.importSite(file); }

@serviceImpl：注释掉的为一些炎症操作

public R importSite(MultipartFile multipartFile) { //失败条数 int isDefeated = 0; //总条数 int count = 0; //文件名 String originalFilename = multipartFile.getOriginalFilename(); // if (StringUtils.isEmpty(originalFilename)) { // return R.error("文件名为空！"); // } // if (!originalFilename.substring(0, 4).equals("站点模板")) return R.error("请使用模板导入"); // if (!originalFilename.split("\\.")[1].equals("xlsx")) return R.error("请使用模板导入"); // List<String> list = new ArrayList<>(10); // list.add("站点名称"); // list.add("详细地址"); // list.add("最大可进车型"); List<ImportSiteStateVO> importSiteStateVOS = new ArrayList<>(); try { boolean isPass = true; ImportExcel ei = new ImportExcel(multipartFile, 0, 0);//构建工具类对象 //验证表头一行是否是用的模板的表头 // for (int i = 0; i < 3; i++) { // if (!list.contains(ImportExcel.getCellValue(ei.getRow(0), i))) { // isPass = false; // continue; // } // } // if (!isPass) { // return R.error("请使用模板导入！"); // } List<ImportSiteDTO> dataList = ei.getDataList(ImportSiteDTO.class); //这儿就能拿到数据封装为list 后续操作就根据自己业务修改 if (CollectionUtils.isEmpty(dataList)) { return R.error("导入数据不能为空！"); } count = dataList.size(); } catch (Exception e) { System.out.println(e); return R.error("数据解析异常"); } String msg = String.format("导入数据共计%s条,成功%s条,失败%s条", count, count - isDefeated, isDefeated); return R.ok(msg, count, importSiteStateVOS); }

domain实体类：@Data提供了Getter  Setter    当前对象就是读取出来的数据会自动封装到List中

@Data public class ImportSiteDTO implements Serializable { private static final long serialVersionUID = -6752399821682023719L; @ExcelField(title = "站点名称", sort = 1) private String siteName; @ExcelField(title = "详细地址", sort = 2) private String detailAddress; @ExcelField(title = "最大可进车型", sort = 3) private String maxCarTypeName; } ImportExcel工具类： package com.ysailine.utils.excel; import com.google.common.collect.Lists; import com.ysailine.common.service.DictService; import com.ysailine.common.service.impl.DictServiceImpl; import com.ysailine.utils.ReflectUtils; import com.ysailine.utils.excel.annotation.ExcelField; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.web.multipart.MultipartFile; import java.io.*; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.util.Collections; import java.util.Comparator; import java.util.Date; import java.util.List; /** * 导入Excel文件（支持“XLS”和“XLSX”格式） * */ public class ImportExcel { DictService dictService = new DictServiceImpl(); private static Logger log = LoggerFactory.getLogger(ImportExcel.class); /** * 工作薄对象 */ private Workbook wb; /** * 工作表对象 */ private Sheet sheet; /** * 工作表数组 */ private List<Sheet> sheets; /** * 标题行号 */ private int headerNum; public Workbook getWorkbook() { return wb; } /** * 构造函数 * * @param fileName 导入文件，读取第一个工作表 * @param headerNum 标题行号，数据行号=标题行号+1 * @throws InvalidFormatException * @throws IOException */ public ImportExcel(String fileName, int headerNum) throws InvalidFormatException, IOException { this(new File(fileName), headerNum); } /** * 构造函数 * * @param file 导入文件对象，读取第一个工作表 * @param headerNum 标题行号，数据行号=标题行号+1 * @throws InvalidFormatException * @throws IOException */ public ImportExcel(File file, int headerNum) throws InvalidFormatException, IOException { this(file, headerNum, 0); } /** * 构造函数 * * @param fileName 导入文件 * @param headerNum 标题行号，数据行号=标题行号+1 * @param sheetIndex 工作表编号 * @throws InvalidFormatException * @throws IOException */ public ImportExcel(String fileName, int headerNum, int sheetIndex) throws InvalidFormatException, IOException { this(new File(fileName), headerNum, sheetIndex); } /** * 构造函数 * * @param file 导入文件对象 * @param headerNum 标题行号，数据行号=标题行号+1 * @param sheetIndex 工作表编号 * @throws InvalidFormatException * @throws IOException */ public ImportExcel(File file, int headerNum, int sheetIndex) throws InvalidFormatException, IOException { this(file.getName(), new FileInputStream(file), headerNum, sheetIndex); } /** * 构造函数 * * @param multipartFile 导入文件对象 * @param headerNum 标题行号，数据行号=标题行号+1 * @param sheetIndex 工作表编号 * @throws InvalidFormatException * @throws IOException */ public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex) throws InvalidFormatException, IOException { this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex); } /** * 构造函数 * * @param fileName 导入文件对象 * @param headerNum 标题行号，数据行号=标题行号+1 * @param sheetIndex 工作表编号 * @throws InvalidFormatException * @throws IOException */ public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex) throws InvalidFormatException, IOException { if (StringUtils.isBlank(fileName)) { throw new RuntimeException("导入文档为空!"); } else if (fileName.toLowerCase().endsWith("xls")) { this.wb = new HSSFWorkbook(is); } else if (fileName.toLowerCase().endsWith("xlsx")) { // this.wb = new XSSFWorkbook(is); this.wb = WorkbookFactory.create(is); } else { throw new RuntimeException("文档格式不正确!"); } if (this.wb.getNumberOfSheets() < sheetIndex) { throw new RuntimeException("文档中没有工作表!"); } this.sheet = this.wb.getSheetAt(sheetIndex); this.headerNum = headerNum; log.debug("Initialize success."); } /** * 获取行对象 * * @param rownum * @return */ public Row getRow(int rownum) { return this.sheet.getRow(rownum); } /** * 获取数据行号 * * @return */ public int getDataRowNum() { return headerNum + 1; } /** * 获取最后一个数据行号 * * @return */ public int getLastDataRowNum() { return this.sheet.getLastRowNum() + headerNum; } /** * 获取最后一个列号 * * @return */ public int getLastCellNum() { return this.getRow(headerNum).getLastCellNum(); } /** * 获取单元格值 * * @param row 获取的行 * @param column 获取单元格列号 * @return 单元格值 */ public static Object getCellValue(Row row, int column) { Object val = ""; try { Cell cell = row.getCell(column); if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { val = cell.getNumericCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { val = cell.getStringCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { val = cell.getCellFormula(); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { val = cell.getBooleanCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) { val = cell.getErrorCellValue(); } } } catch (Exception e) { return val; } return val; } /** * 获取导入数据列表------------jeesite该方法有BUG 稳定性不行 建议不用 * * @param cls 导入对象类型 * @param groups 导入分组 */ public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException { List<Object[]> annotationList = Lists.newArrayList(); // Get annotation field Field[] fs = cls.getDeclaredFields(); for (Field f : fs) { ExcelField ef = f.getAnnotation(ExcelField.class); if (ef != null && (ef.type() == 0 || ef.type() == 2)) { if (groups != null && groups.length > 0) { boolean inGroup = false; for (int g : groups) { if (inGroup) { break; } for (int efg : ef.groups()) { if (g == efg) { inGroup = true; annotationList.add(new Object[]{ef, f}); break; } } } } else { annotationList.add(new Object[]{ef, f}); } } } // Get annotation method Method[] ms = cls.getDeclaredMethods(); for (Method m : ms) { ExcelField ef = m.getAnnotation(ExcelField.class); if (ef != null && (ef.type() == 0 || ef.type() == 2)) { if (groups != null && groups.length > 0) { boolean inGroup = false; for (int g : groups) { if (inGroup) { break; } for (int efg : ef.groups()) { if (g == efg) { inGroup = true; annotationList.add(new Object[]{ef, m}); break; } } } } else { annotationList.add(new Object[]{ef, m}); } } } // Field sorting Collections.sort(annotationList, new Comparator<Object[]>() { public int compare(Object[] o1, Object[] o2) { return new Integer(((ExcelField) o1[0]).sort()).compareTo(new Integer(((ExcelField) o2[0]).sort())); } ; }); // log.debug("Import column count:"+annotationList.size()); // Get excel data List<E> dataList = Lists.newArrayList(); for (int i = this.getDataRowNum(); i <= this.getLastDataRowNum(); i++) { E e = (E) cls.newInstance(); int column = 0; Row row = this.getRow(i); StringBuilder sb = new StringBuilder(); for (Object[] os : annotationList) { Object val = this.getCellValue(row, column++); if (val != null && val != "") { ExcelField ef = (ExcelField) os[0]; // If is dict type, get dict value if (StringUtils.isNotBlank(ef.dictType())) { val = dictService.getValue(val.toString(), ef.dictType()); // log.debug("Dictionary type value: ["+i+","+colunm+"] " + val); } // Get param type and type cast Class<?> valType = Class.class; if (os[1] instanceof Field) { valType = ((Field) os[1]).getType(); } else if (os[1] instanceof Method) { Method method = ((Method) os[1]); if ("get".equals(method.getName().substring(0, 3))) { valType = method.getReturnType(); } else if ("set".equals(method.getName().substring(0, 3))) { valType = ((Method) os[1]).getParameterTypes()[0]; } } // log.debug("Import value type: ["+i+","+column+"] " + valType); try { if (valType == String.class) { String s = String.valueOf(val.toString()); if (StringUtils.endsWith(s, ".0")) { val = StringUtils.substringBefore(s, ".0"); } else { val = String.valueOf(val.toString()); } } else if (valType == Integer.class) { val = Double.valueOf(val.toString()).intValue(); } else if (valType == Long.class) { val = Double.valueOf(val.toString()).longValue(); } else if (valType == Double.class) { val = Double.valueOf(val.toString()); } else if (valType == Float.class) { val = Float.valueOf(val.toString()); } else if (valType == Date.class) { val = DateUtil.getJavaDate((Double) val); } else { if (ef.fieldType() != Class.class) { val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString()); } else { val = Class .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), "fieldtype." + valType.getSimpleName() + "Type")) .getMethod("getValue", String.class).invoke(null, val.toString()); } } } catch (Exception ex) { log.info("Get cell value [" + i + "," + column + "] error: " + ex.toString()); val = null; } // set entity value if (os[1] instanceof Field) { ReflectUtils.invokeSetter(e, ((Field) os[1]).getName(), val); } else if (os[1] instanceof Method) { String mthodName = ((Method) os[1]).getName(); if ("get".equals(mthodName.substring(0, 3))) { mthodName = "set" + StringUtils.substringAfter(mthodName, "get"); } ReflectUtils.invokeMethod(e, mthodName, new Class[]{valType}, new Object[]{val}); } } sb.append(val + ", "); } dataList.add(e); log.debug("Read success: [" + i + "] " + sb.toString()); } return dataList; } }

@ExcelField 注解定义：

package com.ysailine.utils.excel.annotation; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * Excel注解定义 * */ @Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE}) @Retention(RetentionPolicy.RUNTIME) public @interface ExcelField { /** * 导出字段名（默认调用当前字段的“get”方法，如指定导出字段为对象，请填写“对象名.对象属性”，例：“area.name”、“office.name”） */ String value() default ""; /** * 导出字段标题（需要添加批注请用“**”分隔，标题**批注，仅对导出模板有效） */ String title(); /** * 字段类型（0：导出导入；1：仅导出；2：仅导入） */ int type() default 0; /** * 导出字段对齐方式（0：自动；1：靠左；2：居中；3：靠右） */ int align() default 0; /** * 导出字段字段排序（升序） */ int sort() default 0; /** * 如果是字典类型，请设置字典的type值 */ String dictType() default ""; /** * 反射类型 */ Class<?> fieldType() default Class.class; /** * 字段归属组（根据分组导出导入） */ int[] groups() default {}; }