SpringBoot使用poi实现Excel导入与导出

package com.example.demo.utils; import org.apache.commons.collections.CollectionUtils; import com.example.demo.model.ExcelColumn; import org.apache.commons.lang3.BooleanUtils; import org.apache.commons.lang3.CharUtils; import org.apache.commons.lang3.StringUtils; import org.apache.commons.lang3.math.NumberUtils; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.http.MediaType; import org.springframework.stereotype.Component; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.lang.reflect.Constructor; import java.lang.reflect.Field; import java.math.BigDecimal; import java.net.URLEncoder; import java.util.*; import java.util.concurrent.atomic.AtomicInteger; import java.util.stream.Collectors; import java.util.stream.Stream; /** * @program: * @description: ExcelUtils Excel导出工具类 * @author: LiangCheng * @create: 2019-02-26 09:16 **/ @Component public class ExcelUtils { private final static Logger log = LoggerFactory.getLogger(ExcelUtils.class); private final static String EXCEL2003 = "xls"; private final static String EXCEL2007 = "xlsx"; /** * @Title:readExcel * @Author: liangcheng * @Description: //读取上传的Excel并转换成对应的javaBean * @Param: [path 文件路径 可以传null或者空串, * cls, 实体类 * file 接收的文件] * @Return: java.util.List<T> * @Date: 2019/3/1 13:13 */ public static <T> List<T> readExcel(Class<T> cls, MultipartFile file) { // String path 根据情况添加入参 String fileName = file.getOriginalFilename(); if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) { log.error("上传文件格式不正确"); } List<T> dataList = new ArrayList<>(); Workbook workbook = null; try { InputStream is = file.getInputStream(); if (fileName.endsWith(EXCEL2007)) { //FileInputStream is = new FileInputStream(new File(path)); workbook = new XSSFWorkbook(is); } if (fileName.endsWith(EXCEL2003)) { //FileInputStream is = new FileInputStream(new File(path)); workbook = new HSSFWorkbook(is); } if (workbook != null) { //类映射 注解 value-->bean columns Map<String, List<Field>> classMap = new HashMap<>(); List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList()); fields.forEach( field -> { ExcelColumn annotation = field.getAnnotation(ExcelColumn.class); if (annotation != null) { String value = annotation.value(); if (StringUtils.isBlank(value)) { return;//return起到的作用和continue是相同的 语法 } if (!classMap.containsKey(value)) { classMap.put(value, new ArrayList<>()); } field.setAccessible(true); classMap.get(value).add(field); } } ); //索引-->columns Map<Integer, List<Field>> reflectionMap = new HashMap<>(16); //默认读取第一个sheet Sheet sheet = workbook.getSheetAt(0); boolean firstRow = true; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); //首行 提取注解 if (firstRow) { for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { Cell cell = row.getCell(j); String cellValue = getCellValue(cell); if (classMap.containsKey(cellValue)) { reflectionMap.put(j, classMap.get(cellValue)); } } firstRow = false; } else { //忽略空白行 if (row == null) { continue; } try { T t = cls.newInstance(); //判断是否为空白行 boolean allBlank = true; for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { if (reflectionMap.containsKey(j)) { Cell cell = row.getCell(j); String cellValue = getCellValue(cell); if (StringUtils.isNotBlank(cellValue)) { allBlank = false; } List<Field> fieldList = reflectionMap.get(j); fieldList.forEach( x -> { try { handleField(t, cellValue, x); } catch (Exception e) { log.error(String.format("reflect field:%s value:%s exception!", x.getName(), cellValue), e); } } ); } } if (!allBlank) { dataList.add(t); } else { log.warn(String.format("row:%s is blank ignore!", i)); } } catch (Exception e) { log.error(String.format("parse row:%s exception!", i), e); } } } } } catch (Exception e) { log.error(String.format("parse excel exception!"), e); } finally { if (workbook != null) { try { workbook.close(); } catch (Exception e) { log.error(String.format("parse excel exception!"), e); } } } return dataList; } /** * @Title:writeExcel * @Author: liangcheng * @Description: //页面下载Excel * @Param: [response, dataList 后台返回的数据集合, cls 实体类, fileName 生成后的文件名] * @Return: void * @Date: 2019/3/1 13:09 */ public static <T> void writeExcel(HttpServletResponse response, List<T> dataList, Class<T> cls, String fileName) { Field[] fields = cls.getDeclaredFields(); List<Field> fieldList = Arrays.stream(fields) .filter(field -> { ExcelColumn annotation = field.getAnnotation(ExcelColumn.class); if (annotation != null && annotation.col() > 0) { field.setAccessible(true); return true; } return false;