Java导出Excel模板，导出数据到指定模板，通过模板导入数据（一）

本文章主要是介绍阿里巴巴的easyexcel的使用

首先需要我们导入easyexcel的依赖包

                    com.alibaba            easyexcel            2.2.7        

前期工作准备

编写相关导出模板和导入模板。在项目的resources下创建文件夹，命名为excel

导出模板（此处仅做示例，字段根据自己项目来）：

 导入模板（导入时需要哪些字段根据自己项目业务来订）：

将创建好的模板放置到创建好的resources下的excel文件夹内

 编写相关基础工具类

ExcelFillCellMergePrevColUtils.java

import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.HashMap;import java.util.List;import java.util.Map;public class ExcelFillCellMergePrevColUtils implements CellWriteHandler {    private static final String KEY = "%s-%s";    //所有的合并信息都存在了这个map里面    Map mergeInfo = new HashMap<>();    public ExcelFillCellMergePrevColUtils() {    }    @Override    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {    }    @Override    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {    }    @Override    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {    }    @Override    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List list, Cell cell, Head head, Integer integer, Boolean aBoolean) {        //当前行        int curRowIndex = cell.getRowIndex();        //当前列        int curColIndex = cell.getColumnIndex();        Integer num = mergeInfo.get(String.fORMat(KEY, curRowIndex, curColIndex));        if (null != num) {            // 合并最后一行 ,列            this.mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, curColIndex, num);        }    }    public void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int num) {        Sheet sheet = writeSheetHolder.getSheet();        CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + num);        sheet.addMergedRegion(cellRangeAddress);    }    //num从第几列开始增加多少列，(6,2,7)代表的意思就是第6行的第2列至第2+7也就是9列开始合并    public void add(int curRowIndex, int curColIndex, int num) {        mergeInfo.put(String.format(KEY, curRowIndex, curColIndex), num);    }}

ExcelFillCellMergeStrategyUtils.java

import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;public class ExcelFillCellMergeStrategyUtils implements CellWriteHandler {        private int[] mergeColumnIndex;        private int mergeRowIndex;    public ExcelFillCellMergeStrategyUtils(int mergeRowIndex, int[] mergeColumnIndex) {        this.mergeRowIndex = mergeRowIndex;        this.mergeColumnIndex = mergeColumnIndex;    }    @Override    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,     Head head, Integer integer, Integer integer1, Boolean aBoolean) {    }    @Override    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,    Head head, Integer integer, Boolean aBoolean) {    }    @Override    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,           CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {    }    @Override    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,     List list, Cell cell, Head head, Integer integer, Boolean aBoolean) {        //当前行        int curRowIndex = cell.getRowIndex();        //当前列        int curColIndex = cell.getColumnIndex();        if (curRowIndex > mergeRowIndex) {            for (int i = 0; i < mergeColumnIndex.length; i++) {                if (curColIndex == mergeColumnIndex[i]) {                    this.mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);                    break;                }            }        }    }    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {        //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并        //获取当前行的第一列        Cell firstNowCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex);        Object curData = firstNowCell.getCellTypeEnum() == CellType.STRING ? firstNowCell.getStrinGCellValue() : firstNowCell.getNumericCellValue();        Row preRow = cell.getSheet().getRow(curRowIndex - 1);        if (preRow == null) {            // 当获取不到上一行数据时，使用缓存sheet中数据            preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);        }        Cell preCell = preRow.getCell(curColIndex);        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();        // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行        if (curData.equals(preData)) {            Sheet sheet = writeSheetHolder.getSheet();            List mergeRegions = sheet.getMergedRegions();            boolean isMerged = false;            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {                CellRangeAddress cellRangeAddr = mergeRegions.get(i);                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {                    sheet.removeMergedRegion(i);                    cellRangeAddr.setLastRow(curRowIndex);                    sheet.addMergedRegion(cellRangeAddr);                    isMerged = true;                }            }            // 若上一个单元格未被合并，则新增合并单元            if (!isMerged) {                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);                sheet.addMergedRegion(cellRangeAddress);            }        }    }}

ExcelUtils.java

import com.alibaba.excel.EasyExcel;import com.alibaba.excel.ExcelWriter;import com.alibaba.excel.write.metadata.WriteSheet;import com.alibaba.excel.write.metadata.fill.FillConfig;import com.alibaba.excel.write.metadata.fill.FillWrapper;import org.apache.commons.lang3.StringUtils;import org.springframework.core.io.ClassPathResource;import javax.servlet.Http.httpservletResponse;import java.io.IOException;import java.io.InputStream;import java.net.URLEncoder;import java.time.LocalDate;import java.time.ZoneId;import java.time.format.DateTimeFormatter;import java.util.*;public class ExcelUtils {        public static void exportToTemplate(HttpServletResponse response, String excelPath, String excelFileName, Map outerMap, List> innerMapList,            ExcelFillCellMergePrevColUtils excelFillCellMergePrevColUtils, ExcelFillCellMergeStrategyUtils excelFillCellMergeStrategyUtils) throws IOException {        InputStream inputStream = new ClassPathResource(excelPath).getInputStream();        response.setContentType("application/vnd.ms-excel");        response.setCharacterEncoding("utf-8");        String fileName = URLEncoder.encode(excelFileName, "UTF-8");        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())                .withTemplate(inputStream)                .registerWriteHandler(excelFillCellMergePrevColUtils)                .registerWriteHandler(excelFillCellMergeStrategyUtils)                .build();        WriteSheet writeSheet = EasyExcel.writerSheet().build();        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();        if (null != innerMapList && innerMapList.size() > 0) {            FillWrapper listWrapper = new FillWrapper("list", innerMapList);            excelWriter.fill(listWrapper, fillConfig, writeSheet);        }        if (null != outerMap && outerMap.size() > 0) {            excelWriter.fill(outerMap, writeSheet);        }        excelWriter.finish();    }        public static String checkValue(Object columnData, int rowIndex, int columnIndex, String fieldName, Integer lengthLimit,        Boolean ifJudgeEmpty) throws Exception {        String value = getStringValue(columnData);        ifJudgeEmpty = null == ifJudgeEmpty ? true : ifJudgeEmpty;        if (ifJudgeEmpty) {            //需要判空            if (StringUtils.isEmpty(value)) {                throw new Exception("第" + (rowIndex + 1) + "行，第" + (columnIndex + 1) + "列，" + fieldName + "不能为空");            }        }        if (null != lengthLimit && lengthLimit > 0) {            //需要判断字符长度            if (StringUtils.isNotEmpty(value)) {                if (value.length() > lengthLimit) {                    throw new Exception("第" + (rowIndex + 1) + "行，第" + (columnIndex + 1) + "列，" + fieldName + "不能超过" + lengthLimit + "个字符");                }            }        }        return value;    }        public static LocalDate str2LocalDate(String str) {        if (StringUtils.isEmpty(str)) {            return null;        }        if (str.indexOf("-") != -1 || str.indexOf("/") != -1) {            String pattern = str.indexOf("/") != -1 ? "yyyy/MM/dd" : "yyyy-MM-dd";            try {                //测试日期字符串是否符合日期                DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(pattern);                return LocalDate.parse(str, dateTimeFormatter);            } catch (Exception e) {                pattern = str.indexOf("/") != -1 ? "yyyy/M/d" : "yyyy-M-d";                DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(pattern);                return LocalDate.parse(str, dateTimeFormatter);            }        } else {            Calendar calendar = new GregorianCalendar(1900, 0, -1);            Date date = calendar.getTime();            int amount = Integer.parseInt(str);            if (amount > 0) {                Calendar calendar1 = Calendar.getInstance();                calendar1.setTime(date);                calendar1.add(Calendar.DAY_OF_YEAR, amount);                date = calendar.getTime();            }            return date.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();        }    }        public static String getStringValue(Object columnData) {        if (columnData == null) {            return null;        } else {            String res = columnData.toString().replace("[\\t\\n\\r]", "").trim();            return res;//            //判断是否是科学计数法  true是科学计数法,false不是科学计数法//            boolean isMache=SCIENTIFIC_COUNTING_METHOD_PATTERN.matcher(res).matches();//            if(isMache){//                BigDecimal resDecimal = new BigDecimal(res);//                return resDecimal.toPlainString();//            }else {//                return res;//            }        }    }}

 上面的Excel中牵扯合并相关类，下一个帖子到时候会讲述合并相关用法。

 控制层用法

1 导出模板

    @GetMapping(value = "/exportTemplate", produces = "application/octet-stream")    public void exportTemplate(HttpServletResponse response) throws Exception {        ExcelUtils.exportToTemplate(response, "excel/某某管理导入模板.xlsx", "某某管理导入模板", null, null, null, null);    }

2 导出数据

接收导出数据的入参，可以单个单个的接收，也可以直接定义一个对象去接收，此处采用对象来接收的，如需对参数进行校验，可以通过注解的方式进行数据校验

UserExcelParam.java

import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import java.io.Serializable;@Data@NoArgsConstructor@AllArgsConstructorpublic class UserExcelParam implements Serializable {    private static final long serialVersionUID = 0L;        private String dataIds;        private String departmentId;        private String userName;        private Integer status;}

    @GetMapping(value = "/export", produces = "application/octet-stream")    public void export(@RequestBody @Validated UserExcelParam excelParam, HttpServletResponse response) throws Exception {        // excelParam为入参对象，也可拆成单个参数来接收        // 根据入参查询用户数据集合        List resultList = userService.list(excelParam);        String title = "这个是Excel导出后Excel里面显示的标题";        Map outerMap = new HashMap<>(2);        outerMap.put("title", title);        List> innerMapList = new ArrayList<>();        User item;        String startDate, endDate;        for (int i = 0; i < resultList.size(); ++i) {            item = resultList.get(i);            Map innerMap = new HashMap<>(16);            innerMap.put("index", i + 1);            innerMap.put("name", item.getName());            innerMap.put("departmentName", item.getDepartmentName());            innerMap.put("userName", item.getUserName());            // 注意：时间需要转换成字符串形式            startDate = DateUtils.localDate2String(item.getStartValidity(), "yyyy-MM-dd");            if (null == item.getEndValidity()) {                innerMap.put("validityPeriod", startDate + " ~ ");            } else {                endDate = DateUtils.localDate2String(item.getEndValidity(), "yyyy-MM-dd");                innerMap.put("validityPeriod", startDate + " ~ " + endDate);            }            innerMap.put("someTypeName", item.getSomeTypeName());            innerMap.put("statusVal", item.getStatusVal());            innerMapList.add(innerMap);        }        ExcelUtils.exportToTemplate(response, "excel/某某管理导出模板.xlsx", title, outerMap, innerMapList, null, null);    }

如果导出模板中字段太多，可以在上述代码的for循环中直接对象转map，对于个别特殊字段手动在处理一次。

3 导入数据

这个稍微有点麻烦，首先我们需要创建监听类。监听类中需要对导入的Excel中的每一条数据进行校验，当发现有数据异常时会抛出异常，终止后面的数据校验。数据全部校验完成后会产生一个数据集合，最后执行的批量插入。

UserReadExcelListener.java

import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.event.AnalysisEventListener;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import lombok.SneakyThrows;import lombok.extern.slf4j.Slf4j;import org.apache.commons.lang3.StringUtils;import java.time.LocalDate;import java.util.ArrayList;import java.util.LinkedHashMap;import java.util.List;@Data@NoArgsConstructor@AllArgsConstructor@Slf4jpublic class UserReadExcelListener extends AnalysisEventListener {        List dataList = new ArrayList<>();        static List dictDataList;        static String departmentId;    public static UserReadExcelListener newBean(List dictDatas, String deptId) {        dictDataList = dictDatas;        departmentId = deptId;        return new UserReadExcelListener();    }        @SneakyThrows    @Override    public void invoke(Object data, AnalysisContext context) {        User excelData = new User();        // 当前数据行号,从0开始        Integer rowIndex = context.readRowHolder().getRowIndex();        LinkedHashMap dataTemp = (LinkedHashMap) data;        for (int i = 0; i < dataTemp.size(); i++) {            this.parseColumnData(rowIndex, i, dataTemp.get(i), excelData);        }        // 解析完一行数据后,添加到集合中        excelData.setDepartmentId(departmentId);        dataList.add(excelData);    }        private void parseColumnData(Integer rowIndex, Integer columnIndex, Object columnData, User excelData) throws Exception {        // 逐列判断并使用正确的类型接收value,列号从0开始        if (columnIndex == 1) {            //某编号            String value = ExcelUtils.checkValue(columnData, rowIndex, columnIndex, "某编号", 16, null);            excelData.setNo(value);        } else if (columnIndex == 2) {            //某类型            String value = ExcelUtils.checkValue(columnData, rowIndex, columnIndex, "某类型", null, null);            DictData dictData = dictDataList.stream().filter(item -> value.equals(item.getDictLabel())).findFirst().orElse(null);            if (null == dictData) {                throw new Exception("第" + (rowIndex + 1) + "行，第" + (columnIndex + 1) + "列，某类型有误");            }            excelData.setSomeType(dictData.getDataId());        } else if (columnIndex == 3) {            //某单位            excelData.setIssuingUnit(ExcelUtils.checkValue(columnData, rowIndex, columnIndex, "某单位", 64, false));        } else if (columnIndex == 4) {            //有效期之开始时间            String value = ExcelUtils.checkValue(columnData, rowIndex, columnIndex, "有效期之开始时间", null, null);            try {                excelData.setStartValidity(ExcelUtils.str2LocalDate(value));            } catch (Exception e) {                throw new Exception("第" + (rowIndex + 1) + "行，第" + (columnIndex + 1) + "列，有效期之开始时间格式有误");            }        } else if (columnIndex == 5) {            //有效期之结束时间            String value = ExcelUtils.checkValue(columnData, rowIndex, columnIndex, "有效期之结束时间", null, false);            LocalDate endValidity = null;            if (StringUtils.isNotEmpty(value)) {                try {                    endValidity = ExcelUtils.str2LocalDate(value);                } catch (Exception e) {                    throw new Exception("第" + (rowIndex + 1) + "行，第" + (columnIndex + 1) + "列，有效期之结束时间格式有误");                }                long start = DateUtils.localDate2Date(excelData.getStartValidity()).getTime();                if (DateUtils.localDate2Date(endValidity).getTime() <= start) {                    throw new Exception("第" + (rowIndex + 1) + "行，第" + (columnIndex + 1) + "列，有效期之结束时间必须大于开始时间");                }            }            excelData.setEndValidity(endValidity);        }    }        @Override    public void doAfterAllAnalysed(AnalysisContext context) {        System.out.println("excel解析后置处理器");    }}

    @PostMapping("/import")    @ResponseBody//    @Transactional(rollbackFor = Exception.class)    public Object importExcel(@RequestParam("file") @NotNull MultipartFile file) {        Map map = new HashMap<>(8);        // 类型字典数据        List dictDataList = dictDataService.queryDataByType("A_TYPE");        // 获取当前公司id        String departmentId = departmentService.getNowUserDepartmentsId();        // 将参数传递到读取监听类中        UserReadExcelListener excelListener = UserReadExcelListener.newBean(dictDataList, departmentId);        try {            EasyExcel.read(file.getInputStream()).sheet(0).headRowNumber(1).reGISterReadListener(excelListener).doRead();        } catch (Exception e) {            e.printStackTrace();            map.put("code", "100");            map.put("msg", "请求失败");            return map;        }        List dataList = excelListener.getDataList();        if (dataList.size() == 0) {            map.put("code", "500");            map.put("msg", "导入数据不能为空");            return map;        }        boolean result = true;        // 批量插入基数（插入数据量为100时性能还行）        int baseY = 100;        int y = dataList.size() / baseY + 1;        int z = dataList.size() % 100;        for (int i = 0; i < y; i++) {            if (i != y - 1) {                result = userService.insertMore(dataList.subList(i * baseY, (i + 1) * baseY));            } else {                result = dataList.subList(i * baseY, i * baseY + z).size() == 0 ? true : userService.insertMore(dataList.subList(i * baseY, i * baseY + z));            }            if (!result) {                map.put("code", "500");                map.put("msg", "数据导入失败");                return map;            }        }        map.put("code", "200");        map.put("msg", "请求成功");        return map;    }

监听类中的校验可能用用到其他的一些数据，而这些数据可以在实例化监听类时通过构造方法进行传递操作，如不需要就不多说了。

来源地址：https://blog.csdn.net/Dai_Haijiao/article/details/128921620