easyexcel导入导出+动态列+自定义样式

目录

1、引用maven依赖

2、模板文件template1.xlsx

3、导出效果

4、导入效果

5、导出用EasyWriteHandler

6、测试工具类 ExcelTest

---

1、引用maven依赖

<!-- easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.2.1</version>
</dependency>
<!-- java 造数据用 -->
<dependency>
    <groupId>com.github.javafaker</groupId>
    <artifactId>javafaker</artifactId>
    <version>0.17.2</version>
</dependency>

2、模板文件template1.xlsx

3、导出效果

4、导入效果

5、导出用EasyWriteHandler

import com.alibaba.excel.util.WorkBookUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.WorkbookWriteHandler;
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;

public class EasyWriteHandler implements WorkbookWriteHandler, SheetWriteHandler, RowWriteHandler, CellWriteHandler {

    private Integer startRowIndex;
    private Integer lastColumnIndex;



    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                Integer relativeRowIndex, Boolean isHead) {
        if(startRowIndex == null){
            startRowIndex = row.getRowNum();
            lastColumnIndex = Integer.valueOf(row.getLastCellNum());
        }
    }

    public EasyWriteHandler(){}
    public Cell cell(Sheet sheet ,int rowIndex,int cellIndex){
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            row = WorkBookUtil.createRow(sheet, rowIndex);
        }
        Cell cell = row.getCell(cellIndex);
        if (cell == null) {
            cell = WorkBookUtil.createCell(row, cellIndex);
        }
        return cell;
    }

    public Integer getStartRowIndex() {
        return startRowIndex;
    }

    public Integer getLastColumnIndex() {
        return lastColumnIndex;
    }
}

6、测试工具类 ExcelTest

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.handler.context.WorkbookWriteHandlerContext;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.github.javafaker.Faker;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.junit.Test;
import xiao.utils.FakerUtil;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.Stream;


public class ExcelTest {
    @Test
    public void testExport() throws Exception {
        String fileName = "D:/export.xlsx";
        String templateFile = "D:/template1.xlsx";

        List<List<String>> headList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            List<String> head = new ArrayList<>();
            head.add("code"+i);
            head.add("标题"+i);
            headList.add(head);
        }

        Faker faker = FakerUtil.FAKER;
        List<List<String>> dataList = Stream.generate(() -> {
            List<String> data=new ArrayList<>();
            for (int i = 0; i < 10; i++) {
                data.add(faker.name().fullName());
            }
            return data;
        }).limit(50).collect(Collectors.toList());


        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)14);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short)10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);


        WriteCellStyle headBlueWriteCellStyle=new WriteCellStyle();
        headBlueWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());


        //添加自定义样式和修改固定单元格内容时需要加上 .inMemory(true)
        EasyExcel.write(fileName)
                .withTemplate(templateFile).head(headList).inMemory(true)
                .registerWriteHandler(horizontalCellStyleStrategy)
                .registerWriteHandler(new EasyWriteHandler() {
                    @Override
                    public void afterCellDispose(CellWriteHandlerContext context) {
                        if (context.getHead()) {
                            Cell cell = context.getCell();
                            if(cell.getColumnIndex()%2 == 0){
                                WriteCellStyle.merge(headBlueWriteCellStyle,
                                        context.getFirstCellData().getOrCreateStyle());
                            }
                        }
                    }

                    @Override
                    public void afterWorkbookDispose(WorkbookWriteHandlerContext context) {
                        Sheet sheet = context.getWriteContext().writeSheetHolder().getSheet();
                        for (int columnIndex = 0; columnIndex < getLastColumnIndex() ; columnIndex++) {
                            //设置自适应列宽
                            sheet.autoSizeColumn(columnIndex);
                        }
                        //设置部门名称
                        cell(sheet,1,1).setCellValue("开发部门");

                    }
                }).sheet().doWrite(dataList);
    }
    @Test
    public void testUpload() throws Exception {
        String fileName = "D:/export.xlsx";
        List<Map<Integer, String>> resultList = EasyExcel.read(fileName).sheet().headRowNumber(2).doReadSync();
        System.out.println(JSON.toJSON(resultList));
    }
}