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

1. 首先需要我們導(dǎo)入easyexcel的依賴包

<!-- alibaba/easyexcel 使用高版本,低版本string接收數(shù)字丟小數(shù)位的bug -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.7</version>
        </dependency>

2. 前期工作準(zhǔn)備

編寫相關(guān)導(dǎo)出模板和導(dǎo)入模板。在項目的resources下創(chuàng)建文件夾，命名為excel

導(dǎo)出模板（此處僅做示例，字段根據(jù)自己項目來）：

?導(dǎo)入模板（導(dǎo)入時需要哪些字段根據(jù)自己項目業(yè)務(wù)來訂）：

將創(chuàng)建好的模板放置到創(chuàng)建好的resources下的excel文件夾內(nèi)

3.?編寫相關(guān)基礎(chǔ)工具類

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;

/**
 * 列合并工具類
 *
 * @author DaiHaijiao
 */
public class ExcelFillCellMergePrevColUtils implements CellWriteHandler {
    private static final String KEY = "%s-%s";
    //所有的合并信息都存在了這個map里面
    Map<String, Integer> 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<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //當(dāng)前行
        int curRowIndex = cell.getRowIndex();
        //當(dāng)前列
        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;

/**
 * 行合并工具類
 *
 * @author DaiHaijiao
 */
public class ExcelFillCellMergeStrategyUtils implements CellWriteHandler {

    /**
     * 合并字段的下標(biāo)
     */
    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<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //當(dāng)前行
        int curRowIndex = cell.getRowIndex();
        //當(dāng)前列
        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) {
        //獲取當(dāng)前行的當(dāng)前列的數(shù)據(jù)和上一行的當(dāng)前列列數(shù)據(jù)，通過上一行數(shù)據(jù)是否相同進行合并
        //獲取當(dāng)前行的第一列
        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) {
            // 當(dāng)獲取不到上一行數(shù)據(jù)時，使用緩存sheet中數(shù)據(jù)
            preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
        }
        Cell preCell = preRow.getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        // 比較當(dāng)前行的第一列的單元格與上一行是否相同，相同合并當(dāng)前單元格與上一行
        if (curData.equals(preData)) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一個單元格已經(jīng)被合并，則先移出原有的合并單元，再重新添加合并單元
                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.*;

/**
 * excel導(dǎo)出工具類
 *
 */
public class ExcelUtils {

    /**
     * 導(dǎo)出數(shù)據(jù)到指定Excel
     *
     * @param response                        HttpServletResponse對象
     * @param excelPath                       Excel模板地址
     * @param excelFileName                   文件名稱
     * @param outerMap                        頭部內(nèi)容map
     * @param innerMapList                    表格內(nèi)容list
     * @param excelFillCellMergePrevColUtils  列合并參數(shù)
     * @param excelFillCellMergeStrategyUtils 行合并參數(shù)
     * @throws IOException 異常錯誤
     */
    public static void exportToTemplate(HttpServletResponse response, String excelPath, String excelFileName, Map<String, Object> outerMap, List<Map<String, Object>> 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();
    }

    /**
     * 驗證并獲取excel單元格內(nèi)的值
     *
     * @param columnData   列值,object類型
     * @param rowIndex     行號
     * @param columnIndex  列號
     * @param fieldName    字段名稱
     * @param lengthLimit  限制長度數(shù)（null時不做判斷）
     * @param ifJudgeEmpty 是否需要判空（默認(rèn)是）
     * @return 字符串格式值
     * @throws Exception 邏輯異常
     */
    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;
    }

    /**
     * String => LocalDate
     * 入?yún)tr和pattern格式需要對應(yīng)
     *
     * @param str
     * @return LocalDate
     */
    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();
        }
    }

    /**
     * 獲取String類型的值
     *
     * @param columnData 列值,object類型
     * @return 字符串格式值
     */
    public static String getStringValue(Object columnData) {
        if (columnData == null) {
            return null;
        } else {
            String res = columnData.toString().replace("[\\t\\n\\r]", "").trim();
            return res;
//            //判斷是否是科學(xué)計數(shù)法  true是科學(xué)計數(shù)法,false不是科學(xué)計數(shù)法
//            boolean isMache=SCIENTIFIC_COUNTING_METHOD_PATTERN.matcher(res).matches();
//            if(isMache){
//                BigDecimal resDecimal = new BigDecimal(res);
//                return resDecimal.toPlainString();
//            }else {
//                return res;
//            }
        }
    }
}

?上面的Excel中牽扯合并相關(guān)類，下一個帖子到時候會講述合并相關(guān)用法。

4.?控制層用法

4.1?導(dǎo)出模板

/**
     * 導(dǎo)出模板
     *
     * @param response HttpServletResponse對象
     * @throws Exception 導(dǎo)出異常
     */
    @GetMapping(value = "/exportTemplate", produces = "application/octet-stream")
    public void exportTemplate(HttpServletResponse response) throws Exception {
        ExcelUtils.exportToTemplate(response, "excel/某某管理導(dǎo)入模板.xlsx", "某某管理導(dǎo)入模板", null, null, null, null);
    }

4.2?導(dǎo)出數(shù)據(jù)

接收導(dǎo)出數(shù)據(jù)的入?yún)?，可以單個單個的接收，也可以直接定義一個對象去接收，此處采用對象來接收的，如需對參數(shù)進行校驗，可以通過注解的方式進行數(shù)據(jù)校驗

UserExcelParam.java

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;

/**
 * 某某管理導(dǎo)出入?yún)? *
 * @author DaiHaijiao
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserExcelParam implements Serializable {
    private static final long serialVersionUID = 0L;

    /**
     * 主鍵id
     */
    private String dataIds;

    /**
     * 所屬單位(組織機構(gòu)表id)
     */
    private String departmentId;

    /**
     * 人員名
     */
    private String userName;

    /**
     * 證書狀態(tài)（-1已過期 0即將過期 1正常）
     */
    private Integer status;

}

/**
     * 導(dǎo)出數(shù)據(jù)
     *
     * @param excelParam 導(dǎo)出入?yún)?     * @param response   HttpServletResponse對象
     * @throws Exception 導(dǎo)出異常
     */
    @GetMapping(value = "/export", produces = "application/octet-stream")
    public void export(@RequestBody @Validated UserExcelParam excelParam, HttpServletResponse response) throws Exception {
        // excelParam為入?yún)ο?，也可拆成單個參數(shù)來接收
        // 根據(jù)入?yún)⒉樵冇脩魯?shù)據(jù)集合
        List<User> resultList = userService.list(excelParam);

        String title = "這個是Excel導(dǎo)出后Excel里面顯示的標(biāo)題";
        Map<String, Object> outerMap = new HashMap<>(2);
        outerMap.put("title", title);

        List<Map<String, Object>> innerMapList = new ArrayList<>();
        User item;
        String startDate, endDate;
        for (int i = 0; i < resultList.size(); ++i) {
            item = resultList.get(i);
            Map<String, Object> innerMap = new HashMap<>(16);
            innerMap.put("index", i + 1);
            innerMap.put("name", item.getName());
            innerMap.put("departmentName", item.getDepartmentName());
            innerMap.put("userName", item.getUserName());
            // 注意：時間需要轉(zhuǎn)換成字符串形式
            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/某某管理導(dǎo)出模板.xlsx", title, outerMap, innerMapList, null, null);
    }

如果導(dǎo)出模板中字段太多，可以在上述代碼的for循環(huán)中直接對象轉(zhuǎn)map，對于個別特殊字段手動在處理一次。

4.3?導(dǎo)入數(shù)據(jù)

這個稍微有點麻煩，首先我們需要創(chuàng)建監(jiān)聽類。監(jiān)聽類中需要對導(dǎo)入的Excel中的每一條數(shù)據(jù)進行校驗，當(dāng)發(fā)現(xiàn)有數(shù)據(jù)異常時會拋出異常，終止后面的數(shù)據(jù)校驗。數(shù)據(jù)全部校驗完成后會產(chǎn)生一個數(shù)據(jù)集合，最后執(zhí)行的批量插入。

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;

/**
 * 某某管理數(shù)據(jù)導(dǎo)入監(jiān)聽
 *
 * @author DaiHaijiao
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@Slf4j
public class UserReadExcelListener extends AnalysisEventListener {

    /**
     * 解析的數(shù)據(jù)集合
     */
    List<User> dataList = new ArrayList<>();

    /**
     * 資質(zhì)類型字典數(shù)據(jù)
     */
    static List<DictData> dictDataList;

    /**
     * 所屬單位
     */
    static String departmentId;


    public static UserReadExcelListener newBean(List<DictData> dictDatas, String deptId) {
        dictDataList = dictDatas;
        departmentId = deptId;
        return new UserReadExcelListener();
    }

    /**
     * 讀取excel每一行都會觸發(fā)本方法
     *
     * @param data    行數(shù)據(jù)
     * @param context AnalysisContext
     */
    @SneakyThrows
    @Override
    public void invoke(Object data, AnalysisContext context) {
        User excelData = new User();
        // 當(dāng)前數(shù)據(jù)行號,從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);
        }

        // 解析完一行數(shù)據(jù)后,添加到集合中
        excelData.setDepartmentId(departmentId);
        dataList.add(excelData);
    }

    /**
     * 解析列值
     *
     * @param rowIndex    行號
     * @param columnIndex 列號
     * @param columnData  列值,object類型
     * @param excelData   實例對象
     * @throws Exception 邏輯異常
     */
    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) {
            //有效期之結(jié)束時間
            String value = ExcelUtils.checkValue(columnData, rowIndex, columnIndex, "有效期之結(jié)束時間", null, false);
            LocalDate endValidity = null;
            if (StringUtils.isNotEmpty(value)) {
                try {
                    endValidity = ExcelUtils.str2LocalDate(value);
                } catch (Exception e) {
                    throw new Exception("第" + (rowIndex + 1) + "行，第" + (columnIndex + 1) + "列，有效期之結(jié)束時間格式有誤");
                }
                long start = DateUtils.localDate2Date(excelData.getStartValidity()).getTime();
                if (DateUtils.localDate2Date(endValidity).getTime() <= start) {
                    throw new Exception("第" + (rowIndex + 1) + "行，第" + (columnIndex + 1) + "列，有效期之結(jié)束時間必須大于開始時間");
                }
            }
            excelData.setEndValidity(endValidity);
        }
    }

    /**
     * excel解析后置處理器,在excel解析完成后執(zhí)行一次
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println("excel解析后置處理器");
    }
}

/**
     * 導(dǎo)入數(shù)據(jù)
     *
     * @param file excel文件
     * @return 是否成功
     */
    @PostMapping("/import")
    @ResponseBody
//    @Transactional(rollbackFor = Exception.class)
    public Object importExcel(@RequestParam("file") @NotNull MultipartFile file) {
        Map<String, String> map = new HashMap<>(8);

        // 類型字典數(shù)據(jù)
        List<DictData> dictDataList = dictDataService.queryDataByType("A_TYPE");
        // 獲取當(dāng)前公司id
        String departmentId = departmentService.getNowUserDepartmentsId();
        // 將參數(shù)傳遞到讀取監(jiān)聽類中
        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<User> dataList = excelListener.getDataList();
        if (dataList.size() == 0) {
            map.put("code", "500");
            map.put("msg", "導(dǎo)入數(shù)據(jù)不能為空");
            return map;
        }

        boolean result = true;
        // 批量插入基數(shù)（插入數(shù)據(jù)量為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", "數(shù)據(jù)導(dǎo)入失敗");
                return map;
            }
        }
        map.put("code", "200");
        map.put("msg", "請求成功");
        return map;
    }

監(jiān)聽類中的校驗可能用用到其他的一些數(shù)據(jù)，而這些數(shù)據(jù)可以在實例化監(jiān)聽類時通過構(gòu)造方法進行傳遞操作，如不需要就不多說了。文章來源地址http://www.zghlxwxcb.cn/news/detail-780645.html

到了這里，關(guān)于Java導(dǎo)出Excel模板，導(dǎo)出數(shù)據(jù)到指定模板，通過模板導(dǎo)入數(shù)據(jù)（一）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！