前言

excel文件導(dǎo)入及導(dǎo)出，是日常開發(fā)中經(jīng)常遇到的需求筋搏。本次筆者以EasyExcel為例仆百，針對(duì)在項(xiàng)目中遇到的動(dòng)態(tài)表頭解析及導(dǎo)出的場(chǎng)景，詳細(xì)介紹具體的代碼實(shí)現(xiàn)過程奔脐。

參考地址

https://github.com/alibaba/easyexcel

前端下載

  const download = () => {
    axios({
      method: 'GET',
      url: config.http.baseUrl + '/templateDownload',
      responseType: 'blob',
    })
      .then(function (res) {
      const content = res.data
      const blob = new Blob([content], { type: "application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" })
      const downloadElement = document.createElement("a");
      const href = window.URL.createObjectURL(blob);
      downloadElement.href = href;
      downloadElement.download = decodeURI(res.headers['filename']);
      document.body.appendChild(downloadElement);
      downloadElement.click();
      document.body.removeChild(downloadElement); // 下載完成移除元素
      window.URL.revokeObjectURL(href); // 釋放掉blob對(duì)象
    })
  }

模板下載

excel文件導(dǎo)入功能俄周，常常需要進(jìn)行模板下載，在springboot項(xiàng)目中髓迎，程序是以jar包的形式運(yùn)行的峦朗，所以有很多小伙伴常常

遇到在本地開發(fā)中能夠?qū)崿F(xiàn)下載功能，但部署到服務(wù)器的時(shí)候排龄，找不到模板文件的問題波势。

@Override
public void templateDownload(HttpServletResponse response, HttpServletRequest request) {
    //獲取要下載的模板名稱
    String fileName = "批量導(dǎo)入模板.xlsx";
    //獲取文件下載路徑
    String filePath = "/template/template.xlsx";
    TemplateDownloadUtil.download(response, request, fileName, filePath);
}

 import lombok.extern.slf4j.Slf4j;
import org.springframework.core.io.ClassPathResource;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;

/**
 * 模板文件下載工具類
 * @author 
 * @date 2021/05/20 9:20
 */
@Slf4j
public class TemplateDownloadUtil {

    public static void download(HttpServletResponse response, HttpServletRequest request,String fileName,String filePath){
        try {
            response.setContentType("application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 這里URLEncoder.encode可以防止中文亂碼 當(dāng)然和easyexcel沒有關(guān)系
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setHeader("filename", URLEncoder.encode(fileName, "UTF-8"));
            response.setHeader("Access-Control-Expose-Headers", "filename,Content-Disposition");
          
            //獲取文件的路徑，此方式本地開發(fā)可以運(yùn)行橄维，服務(wù)器無法獲取文件
//            String filePath = getClass().getResource("/template/template.xlsx").getPath();
//            FileInputStream input = new FileInputStream(filePath);
          
            //在服務(wù)器中能夠讀取到模板文件
            ClassPathResource resource = new ClassPathResource(filePath);
            InputStream input = resource.getInputStream();
            OutputStream out = response.getOutputStream();
            byte[] b = new byte[2048];
            int len;
            while ((len = input.read(b)) != -1) {
                out.write(b, 0, len);
            }
            //修正 Excel在“xxx.xlsx”中發(fā)現(xiàn)不可讀取的內(nèi)容尺铣。是否恢復(fù)此工作薄的內(nèi)容？如果信任此工作簿的來源挣郭，請(qǐng)點(diǎn)擊"是"
//            response.setHeader("Content-Length", String.valueOf(input.getChannel().size()));
            input.close();
        } catch (Exception e) {
            log.error("下載模板失敗 :", e);
        }
    }

}

EasyExcel動(dòng)態(tài)表頭解析

EasyExcel簡(jiǎn)單的讀文件迄埃，官網(wǎng)中已經(jīng)有詳細(xì)的說明，本文不再贅述兑障，詳細(xì)操作參見

[官網(wǎng)] https://easyexcel.opensource.alibaba.com/docs/current/

侄非。

本文主要針對(duì)筆者遇到的復(fù)雜表頭及動(dòng)態(tài)表頭進(jìn)行講解。

模板示例

導(dǎo)入模板.png

解析

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 發(fā)薪單上傳excel讀取類
 *
 * @author yupf
 * @description Listener 不能被spring管理流译，要每次讀取excel都要new,然后里面用到spring可以構(gòu)造方法傳進(jìn)去
 */
@Slf4j
@Data
public class BatchReadListener extends AnalysisEventListener<Map<Integer, String>> {

    /**
     * 每隔500條存儲(chǔ)數(shù)據(jù)庫逞怨，然后清理list ，方便內(nèi)存回收
     */
    private static final int BATCH_COUNT = 500;
    //Excel數(shù)據(jù)緩存結(jié)構(gòu)
    private List<Map<Integer, Map<Integer, String>>> list = new ArrayList<>();
    //Excel表頭（列名）數(shù)據(jù)緩存結(jié)構(gòu)
    private Map<Integer, String> headTitleMap = new HashMap<>();


    /**
     * 假設(shè)這個(gè)是一個(gè)DAO福澡，當(dāng)然有業(yè)務(wù)邏輯這個(gè)也可以是一個(gè)service叠赦。當(dāng)然如果不用存儲(chǔ)這個(gè)對(duì)象沒用。
     */
    private DbFileBatchService dbFileBatchService;
    private DbFileContentService dbFileContentService;
    private FileBatch fileBatch;
    private int total = 0;

    /**
     * 如果使用了spring,請(qǐng)使用這個(gè)構(gòu)造方法。每次創(chuàng)建Listener的時(shí)候需要把spring管理的類傳進(jìn)來
     */
    public BatchReadListener(DbFileBatchService dbFileBatchService, DbFileContentService dbFileContentService, FileBatch fileBatch) {
        this.dbFileBatchService = dbFileBatchService;
        this.dbFileContentService = dbFileContentService;
        this.fileBatch = fileBatch;
    }

    /**
     * 這個(gè)每一條數(shù)據(jù)解析都會(huì)來調(diào)用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        log.info("解析到一條數(shù)據(jù):{}", JSON.toJSONString(data));
        total++;
        Map<Integer, Map<Integer, String>> map = new HashMap<>();
        map.put(context.readRowHolder().getRowIndex(), data);
        list.add(map);
        // 達(dá)到BATCH_COUNT了除秀，需要去存儲(chǔ)一次數(shù)據(jù)庫糯累，防止數(shù)據(jù)幾萬條數(shù)據(jù)在內(nèi)存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存儲(chǔ)完成清理 list
            list.clear();
        }
    }

    /**
     * 所有數(shù)據(jù)解析完成了 都會(huì)來調(diào)用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 這里也要保存數(shù)據(jù)册踩，確保最后遺留的數(shù)據(jù)也存儲(chǔ)到數(shù)據(jù)庫
        saveData();
        log.info("所有數(shù)據(jù)解析完成泳姐！");
    }

    /**
     * 解析表頭數(shù)據(jù)
     **/
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        log.info("表頭數(shù)據(jù)：{}", JSONObject.toJSONString(headMap));
        headTitleMap = headMap;
    }

    /**
     * 加上存儲(chǔ)數(shù)據(jù)庫
     */
    private void saveData() {
        log.info("{}條數(shù)據(jù)，開始存儲(chǔ)數(shù)據(jù)庫暂吉！", list.size());
        FileContent fileContent = null;
        List<FileContent> fileContentList = list.stream().flatMap(
            integerMap -> integerMap.entrySet().stream().map(entrySet -> {
                //entrySet.getKey()獲取的是內(nèi)容的RowIndex,實(shí)際的行數(shù)需要根據(jù)表頭數(shù)進(jìn)行處理
                Integer rowIndex = entrySet.getKey();
                Map<Integer, String> value = entrySet.getValue();
                log.info(JSONObject.toJSONString(value));
                fileContent = new FileContent();
                fileContent.setBatchId(fileBatch.getId());
                fileContent.setBatchNo(fileBatch.getBatchNo());
                //固定字段入庫
                fileContent.setName(value.get(0) != null ? value.get(0).trim() : "");
                fileContent.setCertNo(value.get(1) != null ? value.get(1).trim() : "");
                fileContent.setRealAmount(value.get(2) != null ? value.get(2).trim() : "");
                //所有動(dòng)態(tài)表頭數(shù)據(jù)轉(zhuǎn)為JSON串入庫
                fileContent.setFieldsValue(JSONObject.toJSONString(value));
                //取實(shí)際的內(nèi)容rowIndex
                fileContent.setRowNum(rowIndex + 1);
                fileContent.setCreateTime(LocalDateTime.now());
                return xcSalaryFileContent;
            }
        )).collect(Collectors.toList());
        log.info(JSONObject.toJSONString(fileContentList));
        dbFileContentService.saveBatch(fileContentList);
        log.info("存儲(chǔ)數(shù)據(jù)庫成功胖秒！");
    }

}

BatchReadListener listener = new BatchReadListener(dbFileBatchService, dbFileContentService, fileBatch);
    try {
        //注：headRowNumber默認(rèn)為1，現(xiàn)賦值為2慕的，即從第三行開始讀取內(nèi)容
        EasyExcel.read(fileInputStream, listener).headRowNumber(2).sheet().doRead();
    } catch (Exception e) {
        log.info("EasyExcel解析文件失敗阎肝，{}", e);
        throw new CustomException("文件解析失敗，請(qǐng)重新上傳");
    }
    //獲取表頭信息進(jìn)行處理
    Map<Integer, String> headTitleMap = listener.getHeadTitleMap();
    //獲取動(dòng)態(tài)表頭信息
    List<String> headList = headTitleMap.keySet().stream().map(key -> {
        String head = headTitleMap.get(key);
        log.info(head);
        return head == null ? "" : head.replace("*", "");
    }).collect(Collectors.toList());
    //可以對(duì)表頭進(jìn)行入庫保存肮街，方便后續(xù)導(dǎo)出

綜上风题，動(dòng)態(tài)表頭即可完成解析。

EasyExcel動(dòng)態(tài)表頭導(dǎo)出

導(dǎo)出示例

失敗信息導(dǎo)出.png

獲取動(dòng)態(tài)頭

     private List<List<String>> getFileHeadList( FileBatch fileBatch) {
         String head = fileBatch.getFileHead();
         List<String> headList = Arrays.asList(head.split(","));
         List<List<String>> fileHead = headList.stream().map(item ->    concatHead(Lists.newArrayList(item))).collect(Collectors.toList());
         fileHead.add(concatHead(Lists.newArrayList("備注")));
         return fileHead;
     }

    /**
     * 填寫須知
     * @param headContent
     * @return
     */
    private List<String> concatHead(List<String> headContent) {
        String remake = "填寫須知：                                                                                                \n" +
                "1.系統(tǒng)自動(dòng)識(shí)別Excel表格嫉父，表頭必須含有“企業(yè)賬戶號(hào)”俯邓、“企業(yè)賬戶名”、“實(shí)發(fā)金額”熔号；\n" +
                "2.帶 “*” 為必填字段，填寫后才能上傳成功鸟整；\n" +
                "3.若需上傳其他表頭引镊，可自行在“實(shí)發(fā)金額”后添加表頭，表頭最多可添加20個(gè)篮条，表頭名稱請(qǐng)控制在8個(gè)字以內(nèi)弟头；\n" +
                "4.填寫的表頭內(nèi)容不可超過30個(gè)字；\n" +
                "5.實(shí)發(fā)金額支持填寫到2位小數(shù)涉茧；\n" +
                "6.每次導(dǎo)入數(shù)據(jù)不超過5000條赴恨。\n" +
                "\n" +
                "注：請(qǐng)勿刪除填寫須知，刪除后將導(dǎo)致文件上傳失敗\n" +
                "\n" +
                "表頭示例：";
        headContent.add(0, remake);
        return headContent;
    }

獲取數(shù)據(jù)

    List<FileContent> fileContentList = dbFileContentService.list(
        Wrappers.<FileContent>lambdaQuery()
        .eq(FileContent::getBatchId, fileBatch.getId())
        .orderByAsc(FileContent::getRowNum)
    );
    List<List<Object>> contentList = fileContentList.stream().map(fileContent -> {
        List<Object> rowList = new ArrayList<>();
        String fieldsValue = fileContent.getFieldsValue();
        JSONObject contentObj = JSONObject.parseObject(fieldsValue);
        for (int columnIndex = 0 , length = headList.size(); columnIndex < length; columnIndex++) {
            Object content = contentObj.get(columnIndex);
            rowList.add(content == null ? "" : content);
        }
        rowList.add(fileContent.getCheckMessage());
        return rowList;
    }).collect(Collectors.toList());

單元格格式設(shè)置

import com.alibaba.excel.metadata.data.DataFormatData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;

import java.util.List;

/**
 * 設(shè)置表頭和填充內(nèi)容的樣式
 */
public class CellStyleStrategy extends HorizontalCellStyleStrategy {

    private final WriteCellStyle headWriteCellStyle;
    private final WriteCellStyle contentWriteCellStyle;

    /**
     * 操作列
     */
    private final List<Integer> columnIndexes;

    public CellStyleStrategy(List<Integer> columnIndexes,WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {
        this.columnIndexes = columnIndexes;
        this.headWriteCellStyle = headWriteCellStyle;
        this.contentWriteCellStyle = contentWriteCellStyle;
    }

    //設(shè)置頭樣式
    @Override
    protected void setHeadCellStyle( CellWriteHandlerContext context) {
        // 獲取字體實(shí)例
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("宋體");
        //表頭不同處理
        if (columnIndexes.get(0).equals(context.getRowIndex())) {
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
            headWriteFont.setFontHeightInPoints((short) 12);
            headWriteFont.setBold(false);
            headWriteFont.setFontName("宋體");
        }else{
            headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            headWriteFont.setFontHeightInPoints((short) 11);
            headWriteFont.setBold(false);
            headWriteFont.setFontName("微軟雅黑");
        }
        headWriteCellStyle.setWriteFont(headWriteFont);
        DataFormatData dataFormatData = new DataFormatData();
        dataFormatData.setIndex((short)49);
        headWriteCellStyle.setDataFormatData(dataFormatData);
        if (stopProcessing(context)) {
            return;
        }
        WriteCellData<?> cellData = context.getFirstCellData();
        WriteCellStyle.merge(headWriteCellStyle, cellData.getOrCreateStyle());
    }

    //設(shè)置填充數(shù)據(jù)樣式
    @Override
    protected void setContentCellStyle(CellWriteHandlerContext context) {
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("宋體");
        contentWriteFont.setFontHeightInPoints((short) 11);
        //設(shè)置數(shù)據(jù)填充后的實(shí)線邊框
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        DataFormatData dataFormatData = new DataFormatData();
        dataFormatData.setIndex((short)49);
        contentWriteCellStyle.setDataFormatData(dataFormatData);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        WriteCellData<?> cellData = context.getFirstCellData();
        WriteCellStyle.merge(contentWriteCellStyle, cellData.getOrCreateStyle());
    }
}

行高設(shè)置

import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Row;

/**
 * 設(shè)置表頭的自動(dòng)調(diào)整行高策略
 */
public class CellRowHeightStyleStrategy extends AbstractRowHeightStyleStrategy {

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
        //設(shè)置主標(biāo)題行高為17.7
        if(relativeRowIndex == 0){
            //如果excel需要顯示行高為15伴栓，那這里就要設(shè)置為15*20=300
            row.setHeight((short) 3240);
        }
    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
    }
}

列寬度自適應(yīng)

如果是簡(jiǎn)單表頭伦连，可以使用EasyExcel中的LongestMatchColumnWidthStyleStrategy()來實(shí)現(xiàn)。

EasyExcel.write(fileName, LongestMatchColumnWidthData.class)
    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("模板").doWrite(dataLong());

如果是復(fù)雜表頭钳垮，就需要自己來實(shí)現(xiàn)惑淳，代碼如下：

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author yupf
 * @description
 * @date 2022/9/7 18:48
 */
@Slf4j
public class CellWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
        if (maxColumnWidthMap == null) {
            maxColumnWidthMap = new HashMap<>();
            CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
        }
        if (isHead) {
            if(relativeRowIndex.intValue() == 1){
                Integer length = cell.getStringCellValue().getBytes().length;
                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || length > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), length);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), length * 300);
                }
            }
        }else{
            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }
                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }
            }
        }
    }

    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

寫入文件

EasyExcel.write(response.getOutputStream())
    .head(head)
    .registerWriteHandler(new CellRowHeightStyleStrategy())   //設(shè)置行高的策略
    .registerWriteHandler(new CellStyleStrategy(Arrays.asList(0,1),new WriteCellStyle(), new WriteCellStyle()))
    .registerWriteHandler(new CellWidthStyleStrategy())
    .sheet(sheetName)
    .doWrite(list);

總結(jié)

以上便是EasyExcel解析動(dòng)態(tài)表頭及導(dǎo)出的整個(gè)過程。
在使用過程中椿息，筆者的感受是蜕便，上手難度很低惦辛，很適合新手去做簡(jiǎn)單的表格解析咬腋，當(dāng)然绢馍，如果你的需求有復(fù)雜的格式向瓷，EasyExcel也提供了api，能夠很好的滿足需要舰涌。