1. Excel解析工具easyexcel全面探索

1.1. 簡介

之前我們想到Excel解析一般是使用POI宣旱，但POI存在一個(gè)嚴(yán)重的問題，就是非常消耗內(nèi)存副签。所以阿里人員對它進(jìn)行了重寫從而誕生了easyexcel焚虱，它解決了過于消耗內(nèi)存問題箭跳，也對它進(jìn)行了封裝讓使用者使用更加便利

接下來我先一一介紹它所有的功能細(xì)節(jié)、如何使用及部分源碼解析

1.2. Excel讀

1.2.1. 例子

    /**
     * 最簡單的讀
     * <p>1. 創(chuàng)建excel對應(yīng)的實(shí)體對象 參照{(diào)@link DemoData}
     * <p>2. 由于默認(rèn)異步讀取excel兜叨，所以需要?jiǎng)?chuàng)建excel一行一行的回調(diào)監(jiān)聽器穿扳，參照{(diào)@link DemoDataListener}
     * <p>3. 直接讀即可
     */
    @Test
    public void simpleRead() {
        String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // 這里 需要指定讀用哪個(gè)class去讀衩侥，然后讀取第一個(gè)sheet 文件流會(huì)自動(dòng)關(guān)閉
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }

• 官方說明也比較明確，使用簡單fileName是路徑+文件名矛物，DemoData是Excel數(shù)據(jù)對應(yīng)的實(shí)體類茫死，DemoDataListener這看名字就是監(jiān)聽器，用來監(jiān)聽處理讀取到的每一條數(shù)據(jù)

1.2.2. 源碼解析

1.2.2.1. 核心源碼XlsxSaxAnalyser

• 它核心的Excel解析我認(rèn)為是這個(gè)類XlsxSaxAnalyser履羞，在它的構(gòu)造方法中做了很多事

    public XlsxSaxAnalyser(AnalysisContext analysisContext, InputStream decryptedStream) throws Exception {
        ...
        //從這開始將數(shù)據(jù)讀取成inputStream流峦萎，緩存到了sheetMap
        XSSFReader xssfReader = new XSSFReader(pkg);
        analysisUse1904WindowDate(xssfReader, readWorkbookHolder);

        stylesTable = xssfReader.getStylesTable();
        sheetList = new ArrayList<ReadSheet>();
        sheetMap = new HashMap<Integer, InputStream>();
        XSSFReader.SheetIterator ite = (XSSFReader.SheetIterator)xssfReader.getSheetsData();
        int index = 0;
        if (!ite.hasNext()) {
            throw new ExcelAnalysisException("Can not find any sheet!");
        }
        while (ite.hasNext()) {
            InputStream inputStream = ite.next();
            sheetList.add(new ReadSheet(index, ite.getSheetName()));
            sheetMap.put(index, inputStream);
            index++;
        }
    }

1.2.2.2. doRead

• 例子中真正開始做解析任務(wù)的是doRead方法，不斷進(jìn)入此方法忆首，會(huì)看到真正執(zhí)行的最后方法就是XlsxSaxAnalyser類的execute方法爱榔；可以看到如下方法中parseXmlSource解析的就是sheetMap緩存的真正數(shù)據(jù)

    @Override
    public void execute(List<ReadSheet> readSheetList, Boolean readAll) {
        for (ReadSheet readSheet : sheetList) {
            readSheet = SheetUtils.match(readSheet, readSheetList, readAll,
                analysisContext.readWorkbookHolder().getGlobalConfiguration());
            if (readSheet != null) {
                analysisContext.currentSheet(readSheet);
                parseXmlSource(sheetMap.get(readSheet.getSheetNo()), new XlsxRowHandler(analysisContext, stylesTable));
                // The last sheet is read
                analysisContext.readSheetHolder().notifyAfterAllAnalysed(analysisContext);
            }
        }
    }

1.2.2.3. 概述DemoDataListener實(shí)現(xiàn)

• 對應(yīng)我們用戶需要手寫的代碼，我們的監(jiān)聽器DemoDataListener中有兩個(gè)實(shí)現(xiàn)方法如下糙及，invoke就對應(yīng)了上述代碼中的parseXmlSource而doAfterAllAnalysed對應(yīng)了上述方法中的notifyAfterAllAnalysed详幽，分別表示了先解析每一條數(shù)據(jù)和當(dāng)最后一頁讀取完畢通知所有監(jiān)聽器

    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        LOGGER.info("解析到一條數(shù)據(jù):{}", JSON.toJSONString(data));
        list.add(data);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        LOGGER.info("所有數(shù)據(jù)解析完成！");
    }

1.2.2.4. parseXmlSource具體實(shí)現(xiàn)

• 看標(biāo)識(shí)重點(diǎn)的地方浸锨，這是最核心的解析地

    private void parseXmlSource(InputStream inputStream, ContentHandler handler) {
        InputSource inputSource = new InputSource(inputStream);
        try {
            SAXParserFactory saxFactory = SAXParserFactory.newInstance();
            saxFactory.setFeature("http://apache.org/xml/features/disallow-doctype-decl", true);
            saxFactory.setFeature("http://xml.org/sax/features/external-general-entities", false);
            saxFactory.setFeature("http://xml.org/sax/features/external-parameter-entities", false);
            SAXParser saxParser = saxFactory.newSAXParser();
            XMLReader xmlReader = saxParser.getXMLReader();
            xmlReader.setContentHandler(handler);
            //重點(diǎn)
            xmlReader.parse(inputSource);
            inputStream.close();
        } catch (ExcelAnalysisException e) {
            throw e;
        } catch (Exception e) {
            throw new ExcelAnalysisException(e);
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    throw new ExcelAnalysisException("Can not close 'inputStream'!");
                }
            }
        }
    }

• 由于這層層深入非常多唇聘，我用一張截圖來表現(xiàn)它的調(diào)用形式

1.2.2.5. notifyAfterAllAnalysed具體實(shí)現(xiàn)

• 具體看notifyAfterAllAnalysed的代碼，我們實(shí)現(xiàn)的DemoDataListener監(jiān)聽器繼承AnalysisEventListener柱搜，而AnalysisEventListener實(shí)現(xiàn)ReadListener接口

    @Override
    public void notifyAfterAllAnalysed(AnalysisContext analysisContext) {
        for (ReadListener readListener : readListenerList) {
            readListener.doAfterAllAnalysed(analysisContext);
        }
    }

1.3. Excel寫

1.3.1. 例子

• 如下例子迟郎，使用還是簡單的，和讀比較類似

    /**
     * 最簡單的寫
     * <p>1. 創(chuàng)建excel對應(yīng)的實(shí)體對象 參照{(diào)@link com.alibaba.easyexcel.test.demo.write.DemoData}
     * <p>2. 直接寫即可
     */
    @Test
    public void simpleWrite() {
        String fileName = TestFileUtil.getPath() + "write" + System.currentTimeMillis() + ".xlsx";
        // 這里 需要指定寫用哪個(gè)class去讀冯凹，然后寫到第一個(gè)sheet谎亩，名字為模板 然后文件流會(huì)自動(dòng)關(guān)閉
        // 如果這里想使用03 則 傳入excelType參數(shù)即可
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
    }
    
    private List<DemoData> data() {
        List<DemoData> list = new ArrayList<DemoData>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

1.3.2. 源碼解析

1.3.2.1. doWrite

• 和讀一樣doWrite才是實(shí)際做事的，這次我們從這個(gè)入口跟進(jìn)

    public void doWrite(List data) {
        if (excelWriter == null) {
            throw new ExcelGenerateException("Must use 'EasyExcelFactory.write().sheet()' to call this method");
        }
        excelWriter.write(data, build());
        excelWriter.finish();
    }

1.3.2.2. write

• 很明顯宇姚，write是核心匈庭，繼續(xù)進(jìn)入ExcelWriter類，看名字addContent就是添加數(shù)據(jù)了浑劳，由excelBuilderExcel建造者來添加阱持，這是ExcelBuilderImpl類

    public ExcelWriter write(List data, WriteSheet writeSheet, WriteTable writeTable) {
        excelBuilder.addContent(data, writeSheet, writeTable);
        return this;
    }

1.3.2.3. addContent

• 可以看到如下，顯示封裝和實(shí)例化一些數(shù)據(jù)魔熏，創(chuàng)建了ExcelWriteAddExecutor寫數(shù)據(jù)執(zhí)行器衷咽，核心就是add方法了

    @Override
    public void addContent(List data, WriteSheet writeSheet, WriteTable writeTable) {
        try {
            if (data == null) {
                return;
            }
            context.currentSheet(writeSheet, WriteTypeEnum.ADD);
            context.currentTable(writeTable);
            if (excelWriteAddExecutor == null) {
                excelWriteAddExecutor = new ExcelWriteAddExecutor(context);
            }
            //核心
            excelWriteAddExecutor.add(data);
        } catch (RuntimeException e) {
            finish();
            throw e;
        } catch (Throwable e) {
            finish();
            throw new ExcelGenerateException(e);
        }
    }

1.3.2.4. add

• 可以看到很明顯在遍歷數(shù)據(jù)addOneRowOfDataToExcel插入到Excel表了

    public void add(List data) {
        if (CollectionUtils.isEmpty(data)) {
            return;
        }
        WriteSheetHolder writeSheetHolder = writeContext.writeSheetHolder();
        int newRowIndex = writeSheetHolder.getNewRowIndexAndStartDoWrite();
        if (writeSheetHolder.isNew() && !writeSheetHolder.getExcelWriteHeadProperty().hasHead()) {
            newRowIndex += writeContext.currentWriteHolder().relativeHeadRowIndex();
        }
        // BeanMap is out of order,so use fieldList
        List<Field> fieldList = new ArrayList<Field>();
        for (int relativeRowIndex = 0; relativeRowIndex < data.size(); relativeRowIndex++) {
            int n = relativeRowIndex + newRowIndex;
            addOneRowOfDataToExcel(data.get(relativeRowIndex), n, relativeRowIndex, fieldList);
        }
    }

1.3.2.5. addOneRowOfDataToExcel

• 這里先是做創(chuàng)建Excel行的準(zhǔn)備，包括行的一些屬性處理器需不需要處理蒜绽，之后我們的例子是插入java對象镶骗，進(jìn)入addJavaObjectToExcel方法

    private void addOneRowOfDataToExcel(Object oneRowData, int n, int relativeRowIndex, List<Field> fieldList) {
        if (oneRowData == null) {
            return;
        }
        WriteHandlerUtils.beforeRowCreate(writeContext, n, relativeRowIndex, Boolean.FALSE);
        Row row = WorkBookUtil.createRow(writeContext.writeSheetHolder().getSheet(), n);
        WriteHandlerUtils.afterRowCreate(writeContext, row, relativeRowIndex, Boolean.FALSE);
        if (oneRowData instanceof List) {
            addBasicTypeToExcel((List)oneRowData, row, relativeRowIndex);
        } else {
            addJavaObjectToExcel(oneRowData, row, relativeRowIndex, fieldList);
        }
        WriteHandlerUtils.afterRowDispose(writeContext, row, relativeRowIndex, Boolean.FALSE);
    }

1.3.2.6. addJavaObjectToExcel

• 在ExcelWriteAddExecutor執(zhí)行器類中執(zhí)行addJavaObjectToExcel，在這里進(jìn)行了數(shù)據(jù)的解析躲雅，將數(shù)據(jù)解析成標(biāo)題和內(nèi)容鼎姊，封裝成適合Excel的格式CellData，數(shù)據(jù)類型等，經(jīng)過這步我們還沒看到文件流的生成相寇，那么下一步了

    private void addJavaObjectToExcel(Object oneRowData, Row row, int relativeRowIndex, List<Field> fieldList) {
        WriteHolder currentWriteHolder = writeContext.currentWriteHolder();
        BeanMap beanMap = BeanMap.create(oneRowData);
        Set<String> beanMapHandledSet = new HashSet<String>();
        int cellIndex = 0;
        // If it's a class it needs to be cast by type
        if (HeadKindEnum.CLASS.equals(writeContext.currentWriteHolder().excelWriteHeadProperty().getHeadKind())) {
            Map<Integer, Head> headMap = writeContext.currentWriteHolder().excelWriteHeadProperty().getHeadMap();
            Map<Integer, ExcelContentProperty> contentPropertyMap =
                writeContext.currentWriteHolder().excelWriteHeadProperty().getContentPropertyMap();
            for (Map.Entry<Integer, ExcelContentProperty> entry : contentPropertyMap.entrySet()) {
                cellIndex = entry.getKey();
                ExcelContentProperty excelContentProperty = entry.getValue();
                String name = excelContentProperty.getField().getName();
                if (writeContext.currentWriteHolder().ignore(name, cellIndex)) {
                    continue;
                }
                if (!beanMap.containsKey(name)) {
                    continue;
                }
                Head head = headMap.get(cellIndex);
                WriteHandlerUtils.beforeCellCreate(writeContext, row, head, cellIndex, relativeRowIndex, Boolean.FALSE);
                Cell cell = WorkBookUtil.createCell(row, cellIndex);
                WriteHandlerUtils.afterCellCreate(writeContext, cell, head, relativeRowIndex, Boolean.FALSE);
                Object value = beanMap.get(name);
                CellData cellData = converterAndSet(currentWriteHolder, excelContentProperty.getField().getType(), cell,
                    value, excelContentProperty);
                WriteHandlerUtils.afterCellDispose(writeContext, cellData, cell, head, relativeRowIndex, Boolean.FALSE);
                beanMapHandledSet.add(name);
            }
        }
        // Finish
        if (beanMapHandledSet.size() == beanMap.size()) {
            return;
        }
        if (cellIndex != 0) {
            cellIndex++;
        }
        Map<String, Field> ignoreMap = writeContext.currentWriteHolder().excelWriteHeadProperty().getIgnoreMap();
        initFieldList(oneRowData.getClass(), fieldList);
        for (Field field : fieldList) {
            String filedName = field.getName();
            boolean uselessData = !beanMap.containsKey(filedName) || beanMapHandledSet.contains(filedName)
                || ignoreMap.containsKey(filedName) || writeContext.currentWriteHolder().ignore(filedName, cellIndex);
            if (uselessData) {
                continue;
            }
            Object value = beanMap.get(filedName);
            if (value == null) {
                continue;
            }
            WriteHandlerUtils.beforeCellCreate(writeContext, row, null, cellIndex, relativeRowIndex, Boolean.FALSE);
            Cell cell = WorkBookUtil.createCell(row, cellIndex++);
            WriteHandlerUtils.afterCellCreate(writeContext, cell, null, relativeRowIndex, Boolean.FALSE);
            CellData cellData = converterAndSet(currentWriteHolder, value.getClass(), cell, value, null);
            WriteHandlerUtils.afterCellDispose(writeContext, cellData, cell, null, relativeRowIndex, Boolean.FALSE);
        }
    }

1.3.2.7. finish

• doWrite中之后還有一步finish

    public void finish() {
        excelBuilder.finish();
    }

• 深入ExcelBuilderImpl類

    @Override
    public void finish() {
        if (context != null) {
            context.finish();
        }
    }

• 到WriteContextImpl寫內(nèi)容實(shí)現(xiàn)類的finish方法中慰于，我們可以看到writeWorkbookHolder.getWorkbook().write(writeWorkbookHolder.getOutputStream()); 這句是重點(diǎn)，將寫Excel持有容器中的內(nèi)容流輸出唤衫；之后就是關(guān)閉流婆赠，刪除臨時(shí)文件的過程

    @Override
    public void finish() {
        WriteHandlerUtils.afterWorkbookDispose(this);
        if (writeWorkbookHolder == null) {
            return;
        }
        Throwable throwable = null;

        boolean isOutputStreamEncrypt = false;
        try {
            isOutputStreamEncrypt = doOutputStreamEncrypt07();
        } catch (Throwable t) {
            throwable = t;
        }

        if (!isOutputStreamEncrypt) {
            try {
                // 重點(diǎn)
                writeWorkbookHolder.getWorkbook().write(writeWorkbookHolder.getOutputStream());
                writeWorkbookHolder.getWorkbook().close();
            } catch (Throwable t) {
                throwable = t;
            }
        }

        try {
            Workbook workbook = writeWorkbookHolder.getWorkbook();
            if (workbook instanceof SXSSFWorkbook) {
                ((SXSSFWorkbook)workbook).dispose();
            }
        } catch (Throwable t) {
            throwable = t;
        }

        try {
            if (writeWorkbookHolder.getAutoCloseStream() && writeWorkbookHolder.getOutputStream() != null) {
                writeWorkbookHolder.getOutputStream().close();
            }
        } catch (Throwable t) {
            throwable = t;
        }

        if (!isOutputStreamEncrypt) {
            try {
                doFileEncrypt07();
            } catch (Throwable t) {
                throwable = t;
            }
        }

        try {
            if (writeWorkbookHolder.getTempTemplateInputStream() != null) {
                writeWorkbookHolder.getTempTemplateInputStream().close();
            }
        } catch (Throwable t) {
            throwable = t;
        }

        clearEncrypt03();

        if (throwable != null) {
            throw new ExcelGenerateException("Can not close IO", throwable);
        }

        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("Finished write.");
        }
    }

1.4. 文件上傳

• 它提供了一個(gè)接收InputStream的參數(shù)，之后和Excel讀沒多大區(qū)別

    /**
     * 文件上傳
     * <p>
     * 1. 創(chuàng)建excel對應(yīng)的實(shí)體對象 參照{(diào)@link UploadData}
     * <p>
     * 2. 由于默認(rèn)異步讀取excel佳励，所以需要?jiǎng)?chuàng)建excel一行一行的回調(diào)監(jiān)聽器休里，參照{(diào)@link UploadDataListener}
     * <p>
     * 3. 直接讀即可
     */
    @PostMapping("upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), UploadData.class, new UploadDataListener()).sheet().doRead();
        return "success";
    }

1.5. 文件下載

• 寫入提供參數(shù)OutputStream，其它和文件寫入差不多

    /**
     * 文件下載
     * <p>
     * 1. 創(chuàng)建excel對應(yīng)的實(shí)體對象 參照{(diào)@link DownloadData}
     * <p>
     * 2. 設(shè)置返回的 參數(shù)
     * <p>
     * 3. 直接寫植兰，這里注意份帐，finish的時(shí)候會(huì)自動(dòng)關(guān)閉OutputStream,當(dāng)然你外面再關(guān)閉流問題不大
     */
    @GetMapping("download")
    public void download(HttpServletResponse response) throws IOException {
        // 這里注意 有同學(xué)反應(yīng)使用swagger 會(huì)導(dǎo)致各種問題，請直接用瀏覽器或者用postman
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 這里URLEncoder.encode可以防止中文亂碼 當(dāng)然和easyexcel沒有關(guān)系
        String fileName = URLEncoder.encode("測試", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), DownloadData.class).sheet("模板").doWrite(data());
    }

1.6. 讀取技巧

1.6.1. Excel讀取多頁

• 以上都是最基礎(chǔ)的單頁讀寫楣导，在我們調(diào)用sheet()方法時(shí)废境，實(shí)際上都是默認(rèn)第1頁，那么如何讀取多頁筒繁？

    /**
     * 讀多個(gè)或者全部sheet,這里注意一個(gè)sheet不能讀取多次噩凹，多次讀取需要重新讀取文件
     * <p>
     * 1. 創(chuàng)建excel對應(yīng)的實(shí)體對象 參照{(diào)@link DemoData}
     * <p>
     * 2. 由于默認(rèn)異步讀取excel，所以需要?jiǎng)?chuàng)建excel一行一行的回調(diào)監(jiān)聽器毡咏，參照{(diào)@link DemoDataListener}
     * <p>
     * 3. 直接讀即可
     */
    @Test
    public void repeatedRead() {
        String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // 讀取全部sheet
        // 這里需要注意 DemoDataListener的doAfterAllAnalysed 會(huì)在每個(gè)sheet讀取完畢后調(diào)用一次驮宴。然后所有sheet都會(huì)往同一個(gè)DemoDataListener里面寫
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).doReadAll();

        // 讀取部分sheet
        fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        ExcelReader excelReader = EasyExcel.read(fileName).build();
        // 這里為了簡單 所以注冊了 同樣的head 和Listener 自己使用功能必須不同的Listener
        ReadSheet readSheet1 =
            EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
        ReadSheet readSheet2 =
            EasyExcel.readSheet(1).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
        // 這里注意 一定要把sheet1 sheet2 一起傳進(jìn)去，不然有個(gè)問題就是03版的excel 會(huì)讀取多次呕缭，浪費(fèi)性能
        excelReader.read(readSheet1, readSheet2);
        // 這里千萬別忘記關(guān)閉堵泽，讀的時(shí)候會(huì)創(chuàng)建臨時(shí)文件，到時(shí)磁盤會(huì)崩的
        excelReader.finish();
    }

• 可以看到doReadAll方法可以讀取所有sheet頁面
• 若要讀取單獨(dú)的頁面恢总，用第二種方式readSheet(index)迎罗，index為頁面位置，從0開始計(jì)數(shù)

1.6.2. 自定義字段轉(zhuǎn)換

• 在讀取寫入的時(shí)候片仿，我們可能會(huì)有這樣的需求：比如日期格式轉(zhuǎn)換纹安，字符串添加固定前綴后綴等等，此時(shí)我們可以進(jìn)行自定義編寫

@Data
public class ConverterData {
    /**
     * 我自定義 轉(zhuǎn)換器砂豌，不管數(shù)據(jù)庫傳過來什么 厢岂。我給他加上“自定義：”
     */
    @ExcelProperty(converter = CustomStringStringConverter.class)
    private String string;
    /**
     * 這里用string 去接日期才能格式化。我想接收年月日格式
     */
    @DateTimeFormat("yyyy年MM月dd日HH時(shí)mm分ss秒")
    private String date;
    /**
     * 我想接收百分比的數(shù)字
     */
    @NumberFormat("#.##%")
    private String doubleData;
}

• 如上面的CustomStringStringConverter類為自定義轉(zhuǎn)換器阳距，可以對字符串進(jìn)行一定修改塔粒，而日期數(shù)字的格式化，它已經(jīng)有提供注解了DateTimeFormat和NumberFormat
• 轉(zhuǎn)換器如下筐摘，實(shí)現(xiàn)Converter接口后即可使用supportExcelTypeKey這是判斷單元格類型卒茬，convertToJavaData這是讀取轉(zhuǎn)換映跟，convertToExcelData這是寫入轉(zhuǎn)換

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

public class CustomStringStringConverter implements Converter<String> {
    @Override
    public Class supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 這里讀的時(shí)候會(huì)調(diào)用
     */
    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
        GlobalConfiguration globalConfiguration) {
        return "自定義：" + cellData.getStringValue();
    }

    /**
     * 這里是寫的時(shí)候會(huì)調(diào)用 不用管
     */
    @Override
    public CellData convertToExcelData(String value, ExcelContentProperty contentProperty,
        GlobalConfiguration globalConfiguration) {
        return new CellData(value);
    }

}

• 這里解析結(jié)果截取部分如下，原數(shù)據(jù)是字符串0 2020/1/1 1:01 1

解析到一條數(shù)據(jù):{"date":"2020年01月01日01時(shí)01分01秒","doubleData":"100%","string":"自定義：字符串0"}

1.6.3. 指定表頭行數(shù)

        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet()
            // 這里可以設(shè)置1扬虚，因?yàn)轭^就是一行。如果多行頭球恤，可以設(shè)置其他值辜昵。不傳入也可以，因?yàn)槟J(rèn)會(huì)根據(jù)DemoData 來解析咽斧，他沒有指定頭堪置，也就是默認(rèn)1行
            .headRowNumber(1).doRead();

1.6.4. 讀取表頭數(shù)據(jù)

• 只要在實(shí)現(xiàn)了AnalysisEventListener接口的監(jiān)聽器中，重寫invokeHeadMap方法即可

    /**
     * 這里會(huì)一行行的返回頭
     *
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        LOGGER.info("解析到一條頭數(shù)據(jù):{}", JSON.toJSONString(headMap));
    }

1.6.5. 轉(zhuǎn)換異常處理

• 只要在實(shí)現(xiàn)了AnalysisEventListener接口的監(jiān)聽器中张惹，重寫onException方法即可

    @Override
    public void onException(Exception exception, AnalysisContext context) {
        LOGGER.error("解析失敗舀锨，但是繼續(xù)解析下一行:{}", exception.getMessage());
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
            LOGGER.error("第{}行，第{}列解析異常", excelDataConvertException.getRowIndex(),
                excelDataConvertException.getColumnIndex());
        }
    }

1.6.6. 讀取單元格參數(shù)和類型

• 將類屬性用CellData封裝起來

@Data
public class CellDataReadDemoData {
    private CellData<String> string;
    // 這里注意 雖然是日期 但是 類型 存儲(chǔ)的是number 因?yàn)閑xcel 存儲(chǔ)的就是number
    private CellData<Date> date;
    private CellData<Double> doubleData;
    // 這里并不一定能完美的獲取 有些公式是依賴性的 可能會(huì)讀不到 這個(gè)問題后續(xù)會(huì)修復(fù)
    private CellData<String> formulaValue;
}

• 這樣讀取到的數(shù)據(jù)如下宛逗，會(huì)包含單元格數(shù)據(jù)類型

解析到一條數(shù)據(jù):{"date":{"data":1577811661000,"dataFormat":22,"dataFormatString":"m/d/yy h:mm","formula":false,"numberValue":43831.0423726852,"type":"NUMBER"},"doubleData":{"data":1.0,"formula":false,"numberValue":1,"type":"NUMBER"},"formulaValue":{"data":"字符串01","formula":true,"formulaValue":"_xlfn.CONCAT(A2,C2)","stringValue":"字符串01","type":"STRING"},"string":{"data":"字符串0","dataFormat":0,"dataFormatString":"General","formula":false,"stringValue":"字符串0","type":"STRING"}}

1.6.7. 同步返回

• 不推薦使用坎匿，但如果特定情況一定要用，可以如下雷激，主要為doReadSync方法替蔬，直接返回List

    /**
     * 同步的返回，不推薦使用屎暇，如果數(shù)據(jù)量大會(huì)把數(shù)據(jù)放到內(nèi)存里面
     */
    @Test
    public void synchronousRead() {
        String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // 這里 需要指定讀用哪個(gè)class去讀承桥，然后讀取第一個(gè)sheet 同步讀取會(huì)自動(dòng)finish
        List<Object> list = EasyExcel.read(fileName).head(DemoData.class).sheet().doReadSync();
        for (Object obj : list) {
            DemoData data = (DemoData)obj;
            LOGGER.info("讀取到數(shù)據(jù):{}", JSON.toJSONString(data));
        }

        // 這里 也可以不指定class，返回一個(gè)list根悼，然后讀取第一個(gè)sheet 同步讀取會(huì)自動(dòng)finish
        list = EasyExcel.read(fileName).sheet().doReadSync();
        for (Object obj : list) {
            // 返回每條數(shù)據(jù)的鍵值對 表示所在的列 和所在列的值
            Map<Integer, String> data = (Map<Integer, String>)obj;
            LOGGER.info("讀取到數(shù)據(jù):{}", JSON.toJSONString(data));
        }
    }

1.6.8. 無對象的讀

• 顧名思義凶异，不創(chuàng)建實(shí)體對象來讀取Excel數(shù)據(jù)，那么我們就用Map接收挤巡，但這種對日期不友好剩彬，對于簡單字段的讀取可以使用
• 其它都一樣，監(jiān)聽器的繼承中泛型參數(shù)變?yōu)镸ap即可

public class NoModleDataListener extends AnalysisEventListener<Map<Integer, String>> {
    ...
}

• 結(jié)果截取如下

解析到一條數(shù)據(jù):{0:"字符串0",1:"2020-01-01 01:01:01",2:"1"}

1.7. 寫入技巧

1.7.1. 排除特定字段和只寫入特定字段

• 使用excludeColumnFiledNames來排除特定字段寫入玄柏，用includeColumnFiledNames表示只寫入特定字段

    /**
     * 根據(jù)參數(shù)只導(dǎo)出指定列
     * <p>
     * 1. 創(chuàng)建excel對應(yīng)的實(shí)體對象 參照{(diào)@link DemoData}
     * <p>
     * 2. 根據(jù)自己或者排除自己需要的列
     * <p>
     * 3. 直接寫即可
     */
    @Test
    public void excludeOrIncludeWrite() {
        String fileName = TestFileUtil.getPath() + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";

        // 根據(jù)用戶傳入字段 假設(shè)我們要忽略 date
        Set<String> excludeColumnFiledNames = new HashSet<String>();
        excludeColumnFiledNames.add("date");
        // 這里 需要指定寫用哪個(gè)class去讀襟衰，然后寫到第一個(gè)sheet，名字為模板 然后文件流會(huì)自動(dòng)關(guān)閉
        EasyExcel.write(fileName, DemoData.class).excludeColumnFiledNames(excludeColumnFiledNames).sheet("模板")
            .doWrite(data());

        fileName = TestFileUtil.getPath() + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";
        // 根據(jù)用戶傳入字段 假設(shè)我們只要導(dǎo)出 date
        Set<String> includeColumnFiledNames = new HashSet<String>();
        includeColumnFiledNames.add("date");
        // 這里 需要指定寫用哪個(gè)class去讀粪摘，然后寫到第一個(gè)sheet瀑晒，名字為模板 然后文件流會(huì)自動(dòng)關(guān)閉
        EasyExcel.write(fileName, DemoData.class).includeColumnFiledNames(includeColumnFiledNames).sheet("模板")
            .doWrite(data());
    }

1.7.2. 指定寫入列

• 寫入列的順序可以進(jìn)行指定，在實(shí)體類注解上指定index徘意，從小到大苔悦，從左到右排列

@Data
public class IndexData {
    @ExcelProperty(value = "字符串標(biāo)題", index = 0)
    private String string;
    @ExcelProperty(value = "日期標(biāo)題", index = 1)
    private Date date;
    /**
     * 這里設(shè)置3 會(huì)導(dǎo)致第二列空的
     */
    @ExcelProperty(value = "數(shù)字標(biāo)題", index = 3)
    private Double doubleData;
}

1.7.3. 復(fù)雜頭寫入

• 如下圖這種復(fù)雜頭

• 我們可以通過修改實(shí)體類注解實(shí)現(xiàn)

@Data
public class ComplexHeadData {
    @ExcelProperty({"主標(biāo)題", "字符串標(biāo)題"})
    private String string;
    @ExcelProperty({"主標(biāo)題", "日期標(biāo)題"})
    private Date date;
    @ExcelProperty({"主標(biāo)題", "數(shù)字標(biāo)題"})
    private Double doubleData;
}

1.7.4. 重復(fù)多次寫入

• 分為三種：1. 重復(fù)寫入同一個(gè)sheet编饺；2. 同一個(gè)對象寫入不同sheet前联；3. 不同的對象寫入不同的sheet

    /**
     * 重復(fù)多次寫入
     * <p>
     * 1. 創(chuàng)建excel對應(yīng)的實(shí)體對象 參照{(diào)@link ComplexHeadData}
     * <p>
     * 2. 使用{@link ExcelProperty}注解指定復(fù)雜的頭
     * <p>
     * 3. 直接調(diào)用二次寫入即可
     */
    @Test
    public void repeatedWrite() {
        // 方法1 如果寫到同一個(gè)sheet
        String fileName = TestFileUtil.getPath() + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";
        // 這里 需要指定寫用哪個(gè)class去讀
        ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build();
        // 這里注意 如果同一個(gè)sheet只要?jiǎng)?chuàng)建一次
        WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
        // 去調(diào)用寫入,這里我調(diào)用了五次敷鸦，實(shí)際使用時(shí)根據(jù)數(shù)據(jù)庫分頁的總的頁數(shù)來
        for (int i = 0; i < 5; i++) {
            // 分頁去數(shù)據(jù)庫查詢數(shù)據(jù) 這里可以去數(shù)據(jù)庫查詢每一頁的數(shù)據(jù)
            List<DemoData> data = data();
            writeSheet.setSheetName("模板");
            excelWriter.write(data, writeSheet);
        }
        /// 千萬別忘記finish 會(huì)幫忙關(guān)閉流
        excelWriter.finish();

        // 方法2 如果寫到不同的sheet 同一個(gè)對象
        fileName = TestFileUtil.getPath() + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";
        // 這里 指定文件
        excelWriter = EasyExcel.write(fileName, DemoData.class).build();
        // 去調(diào)用寫入,這里我調(diào)用了五次，實(shí)際使用時(shí)根據(jù)數(shù)據(jù)庫分頁的總的頁數(shù)來山卦。這里最終會(huì)寫到5個(gè)sheet里面
        for (int i = 0; i < 5; i++) {
            // 每次都要?jiǎng)?chuàng)建writeSheet 這里注意必須指定sheetNo
            writeSheet = EasyExcel.writerSheet(i, "模板"+i).build();
            // 分頁去數(shù)據(jù)庫查詢數(shù)據(jù) 這里可以去數(shù)據(jù)庫查詢每一頁的數(shù)據(jù)
            List<DemoData> data = data();
            excelWriter.write(data, writeSheet);
        }
        /// 千萬別忘記finish 會(huì)幫忙關(guān)閉流
        excelWriter.finish();

        // 方法3 如果寫到不同的sheet 不同的對象
        fileName = TestFileUtil.getPath() + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";
        // 這里 指定文件
        excelWriter = EasyExcel.write(fileName).build();
        // 去調(diào)用寫入,這里我調(diào)用了五次，實(shí)際使用時(shí)根據(jù)數(shù)據(jù)庫分頁的總的頁數(shù)來褐澎。這里最終會(huì)寫到5個(gè)sheet里面
        for (int i = 0; i < 5; i++) {
            // 每次都要?jiǎng)?chuàng)建writeSheet 這里注意必須指定sheetNo岩臣。這里注意DemoData.class 可以每次都變，我這里為了方便 所以用的同一個(gè)class 實(shí)際上可以一直變
            writeSheet = EasyExcel.writerSheet(i, "模板"+i).head(DemoData.class).build();
            // 分頁去數(shù)據(jù)庫查詢數(shù)據(jù) 這里可以去數(shù)據(jù)庫查詢每一頁的數(shù)據(jù)
            List<DemoData> data = data();
            excelWriter.write(data, writeSheet);
        }
        /// 千萬別忘記finish 會(huì)幫忙關(guān)閉流
        excelWriter.finish();
    }

1.7.5. 圖片導(dǎo)出

• 對圖片的導(dǎo)出向臀，可能會(huì)有這樣的需求巢墅，它提供了四種數(shù)據(jù)類型的導(dǎo)出，還是很豐富的

    @Test
    public void imageWrite() throws Exception {
        String fileName = TestFileUtil.getPath() + "imageWrite" + System.currentTimeMillis() + ".xlsx";
        // 如果使用流 記得關(guān)閉
        InputStream inputStream = null;
        try {
            List<ImageData> list = new ArrayList<ImageData>();
            ImageData imageData = new ImageData();
            list.add(imageData);
            String imagePath = TestFileUtil.getPath() + "converter" + File.separator + "img.jpg";
            // 放入四種類型的圖片 實(shí)際使用只要選一種即可
            imageData.setByteArray(FileUtils.readFileToByteArray(new File(imagePath)));
            imageData.setFile(new File(imagePath));
            imageData.setString(imagePath);
            inputStream = FileUtils.openInputStream(new File(imagePath));
            imageData.setInputStream(inputStream);
            EasyExcel.write(fileName, ImageData.class).sheet().doWrite(list);
        } finally {
            if (inputStream != null) {
                inputStream.close();
            }
        }
    }

• 圖片類為

@Data
@ContentRowHeight(100)
@ColumnWidth(100 / 8)
public class ImageData {
    private File file;
    private InputStream inputStream;
    /**
     * 如果string類型 必須指定轉(zhuǎn)換器券膀，string默認(rèn)轉(zhuǎn)換成string
     */
    @ExcelProperty(converter = StringImageConverter.class)
    private String string;
    private byte[] byteArray;
}

導(dǎo)出結(jié)果：兩行四列君纫，每列都對應(yīng)一張圖片，四種導(dǎo)出類型均可

image.png

• 其中StringImageConverter自定義轉(zhuǎn)換器為

public class StringImageConverter implements Converter<String> {
    @Override
    public Class supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.IMAGE;
    }

    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
        GlobalConfiguration globalConfiguration) {
        throw new UnsupportedOperationException("Cannot convert images to string");
    }

    @Override
    public CellData convertToExcelData(String value, ExcelContentProperty contentProperty,
        GlobalConfiguration globalConfiguration) throws IOException {
        return new CellData(FileUtils.readFileToByteArray(new File(value)));
    }

}

1.7.6. 字段寬高設(shè)置

• 設(shè)置實(shí)體類注解屬性即可

@Data
@ContentRowHeight(10)
@HeadRowHeight(20)
@ColumnWidth(25)
public class WidthAndHeightData {
    @ExcelProperty("字符串標(biāo)題")
    private String string;
    @ExcelProperty("日期標(biāo)題")
    private Date date;
    /**
     * 寬度為50
     */
    @ColumnWidth(50)
    @ExcelProperty("數(shù)字標(biāo)題")
    private Double doubleData;
}

1.7.7. 自定義樣式

• 實(shí)現(xiàn)會(huì)比較復(fù)雜芹彬，需要做頭策略蓄髓，內(nèi)容策略，字體大小等

    @Test
    public void styleWrite() {
        String fileName = TestFileUtil.getPath() + "styleWrite" + System.currentTimeMillis() + ".xlsx";
        // 頭的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景設(shè)置為紅色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)20);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 內(nèi)容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 這里需要指定 FillPatternType 為FillPatternType.SOLID_FOREGROUND 不然無法顯示背景顏色.頭默認(rèn)了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景綠色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // 字體大小
        contentWriteFont.setFontHeightInPoints((short)20);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 這個(gè)策略是 頭是頭的樣式 內(nèi)容是內(nèi)容的樣式 其他的策略可以自己實(shí)現(xiàn)
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
            new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        // 這里 需要指定寫用哪個(gè)class去讀舒帮，然后寫到第一個(gè)sheet会喝，名字為模板 然后文件流會(huì)自動(dòng)關(guān)閉
        EasyExcel.write(fileName, DemoData.class).registerWriteHandler(horizontalCellStyleStrategy).sheet("模板")
            .doWrite(data());
    }

• 效果如下

  
  
  image.png

1.7.8. 單元格合并

    @Test
    public void mergeWrite() {
        String fileName = TestFileUtil.getPath() + "mergeWrite" + System.currentTimeMillis() + ".xlsx";
        // 每隔2行會(huì)合并。當(dāng)然其他合并策略也可以自己寫
        LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);
        // 這里 需要指定寫用哪個(gè)class去讀玩郊，然后寫到第一個(gè)sheet好乐，名字為模板 然后文件流會(huì)自動(dòng)關(guān)閉
        EasyExcel.write(fileName, DemoData.class).registerWriteHandler(loopMergeStrategy).sheet("模板").doWrite(data());
    }

• 效果如下，第一列單元格數(shù)據(jù)瓦宜，2,3兩行合并

image.png

1.7.9. 自動(dòng)列寬

• 根據(jù)作者描述蔚万，POI對中文的自動(dòng)列寬適配不友好，easyexcel對數(shù)字也不能準(zhǔn)確適配列寬临庇，他提供的適配策略可以用反璃，但不能精確適配，可以自己重寫
• 想用就注冊處理器LongestMatchColumnWidthStyleStrategy

    @Test
    public void longestMatchColumnWidthWrite() {
        String fileName =
            TestFileUtil.getPath() + "longestMatchColumnWidthWrite" + System.currentTimeMillis() + ".xlsx";
        // 這里 需要指定寫用哪個(gè)class去讀假夺，然后寫到第一個(gè)sheet淮蜈，名字為模板 然后文件流會(huì)自動(dòng)關(guān)閉
        EasyExcel.write(fileName, LongestMatchColumnWidthData.class)
            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("模板").doWrite(dataLong());
    }

1.7.10. 下拉，超鏈接

• 下拉已卷，超鏈接等功能需要自定義實(shí)現(xiàn)

    @Test
    public void customHandlerWrite() {
        String fileName = TestFileUtil.getPath() + "customHandlerWrite" + System.currentTimeMillis() + ".xlsx";
        // 這里 需要指定寫用哪個(gè)class去讀梧田，然后寫到第一個(gè)sheet，名字為模板 然后文件流會(huì)自動(dòng)關(guān)閉
        EasyExcel.write(fileName, DemoData.class).registerWriteHandler(new CustomSheetWriteHandler())
            .registerWriteHandler(new CustomCellWriteHandler()).sheet("模板").doWrite(data());
    }

• 其中主要為處理器CustomCellWriteHandler類侧蘸，其實(shí)現(xiàn)CellWriteHandler接口裁眯，我們在后處理方法afterCellDispose做處理

public class CustomCellWriteHandler implements CellWriteHandler {

    private static final Logger LOGGER = LoggerFactory.getLogger(CustomCellWriteHandler.class);

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
        Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
        Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
        List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 這里可以對cell進(jìn)行任何操作
        LOGGER.info("第{}行，第{}列寫入完成讳癌。", cell.getRowIndex(), cell.getColumnIndex());
        if (isHead && cell.getColumnIndex() == 0) {
            CreationHelper createHelper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper();
            Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL);
            hyperlink.setAddress("https://github.com/alibaba/easyexcel");
            cell.setHyperlink(hyperlink);
        }
    }

}

1.7.11. 不創(chuàng)建對象的寫

• 在設(shè)置write的時(shí)候不設(shè)置對象類穿稳，在head里添加List<List<String>>的對象頭

    @Test
    public void noModleWrite() {
        // 寫法1
        String fileName = TestFileUtil.getPath() + "noModleWrite" + System.currentTimeMillis() + ".xlsx";
        // 這里 需要指定寫用哪個(gè)class去讀，然后寫到第一個(gè)sheet晌坤，名字為模板 然后文件流會(huì)自動(dòng)關(guān)閉
        EasyExcel.write(fileName).head(head()).sheet("模板").doWrite(dataList());
    }
    
    private List<List<String>> head() {
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        head0.add("字符串" + System.currentTimeMillis());
        List<String> head1 = new ArrayList<String>();
        head1.add("數(shù)字" + System.currentTimeMillis());
        List<String> head2 = new ArrayList<String>();
        head2.add("日期" + System.currentTimeMillis());
        list.add(head0);
        list.add(head1);
        list.add(head2);
        return list;
    }

1.8. 總結(jié)

• 不知不覺列出了這么多easyexcel的使用技巧和方式逢艘，這里應(yīng)該囊括了大部分我們工作中常用到的excel讀寫技巧旦袋，歡迎收藏查閱

easyexcel的github地址
歡迎訪問收藏作者知識(shí)點(diǎn)整理，沒注冊的請點(diǎn)擊這里