EasyExce简单使用案例

Nuyoahll-_-ll

已于 2023-03-19 21:31:10 修改

阅读量387

点赞数 1

分类专栏： java 文章标签： java

于 2023-03-16 21:16:03 首次发布

本文链接：https://blog.csdn.net/qq_43566782/article/details/129599734

版权

java 专栏收录该内容

38 篇文章 0 订阅

订阅专栏

EasyExce简单使用案例
 Easyexcel针对导出的订单进行合并

案例

1、处理表格重新生成新的表格

需求：产品给的数据，需要将参数给绑定到一个链接上，数据量较多，写个demo处理

文档:传送门

映射表格的实体Model

@Data
public class BookModel {

    @ExcelProperty(index = 0,value = "书籍id")
    String bookId;

    @ExcelProperty(index = 1,value = "书籍名")
    String title;

    @ExcelProperty(index = 2,value = "书源id")
    String bookSourceId;

    @ExcelProperty(index = 3,value = "XXX链接")
    String directHapLink;

    @ExcelProperty(index = 4,value = "XXX链接")
    String directHttpLink;

    @ExcelProperty(index = 5,value = "XXX链接")
    String directMonitorLink;

    @ExcelProperty(index = 6,value = "XXX链接")
    String unionHapLink;

    @ExcelProperty(index = 7,value = "XXX链接")
    String unionHttpLink;

    @ExcelProperty(index = 8,value = "XXX链接")
    String unionMonitorLink;

}

直接写个Demo跑数据，由于涉及到查询数据库，所以还需要用@SpringBootTest

@SpringBootTest
public class SpliceStringTest {
    @Autowired
    private BookDao bookDao;

    @Test
    void test() throws FileNotFoundException {

        File file = new File("/Users/xxx/Downloads/old.xlsx");
        File quickApp = new File("/Users/xxx/Downloads/new.xlsx");
        EasyExcel.read(new FileInputStream(file), BookModel.class, new AnalysisEventListener<BookModel>(){

            private List<BookModel>bookModels=new ArrayList<>();

            @Override
            public void invoke(BookModel data, AnalysisContext context) {

                Long bookId = Long.valueOf(data.getBookId());
                String title="";
                if(bookId!=null){
                    BookEntity bookEntity = bookDao.findById(bookId).get();
                    data.setBookSourceId(bookEntity.getSource().getId().toString());
                }
                // format
                String directHapLink=String.format("hap://xxx?book=%s&source=%s&title=%s"
                ,data.getBookId(),data.getBookSourceId(),data.getTitle());
                data.setDirectHapLink(directHapLink);

                String directHttpLink=String.format("https://xxx?book=%s&source=%s&title=%s",
                       data.getBookId(),data.getBookSourceId(),data.getTitle());
                data.setDirectHttpLink(directHttpLink);

                String directMonitorLink=String.format("https://xxx?book=%s",
                        data.getBookId());
                data.setDirectMonitorLink(directMonitorLink);

                String unionHapLink=String.format("hap://xxx?book=%s&source=%s&title=%s",
                        data.getBookId(),data.getBookSourceId(),data.getTitle());
                data.setUnionHapLink(unionHapLink);

                String unionHttpLink=String.format("https://xxx?book=%s&source=%s&title=%s",
                        data.getBookId(),data.getBookSourceId(),data.getTitle());
                data.setUnionHttpLink(unionHttpLink);

                String unionMonitorLink=String.format("https://xxx?book=%s",
                       data.getBookId() );
                data.setUnionMonitorLink(unionMonitorLink);


                bookModels.add(data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                save();
            }

            private void save() {
                EasyExcel.write(quickApp,BookModel.class).sheet().doWrite(bookModels);
            }
        }).sheet().doRead();


    }
}

针对上个案例对表格进行优化样式

上面表格样式太难看了，链接也没有设置单元格为链接。

映射单元格实体

@Data
@ContentRowHeight(value = 90)//设置 row 高度，不包含表头标记在 类上
@HeadRowHeight(100)//设置表头高度（与 @ContentRowHeight 相反）标记在类上
public class BookModel {

    @ExcelProperty(value = "类型")
    @ColumnWidth(value = 10)
    @ContentStyle(horizontalAlignment= HorizontalAlignmentEnum.CENTER,verticalAlignment= VerticalAlignmentEnum.CENTER)
    String type;

    @ExcelProperty(value = "书籍id")
    @ColumnWidth(value = 30)
    @ContentStyle(horizontalAlignment= HorizontalAlignmentEnum.CENTER,verticalAlignment= VerticalAlignmentEnum.CENTER)
    String bookId;

    @ExcelProperty(value = "书籍名")
    @ColumnWidth(value = 30)
    @ContentStyle(horizontalAlignment= HorizontalAlignmentEnum.CENTER,verticalAlignment= VerticalAlignmentEnum.CENTER)
    String title;

    @ExcelProperty(value = "作者")
    @ColumnWidth(value = 20)
    @ContentStyle(horizontalAlignment= HorizontalAlignmentEnum.CENTER,verticalAlignment= VerticalAlignmentEnum.CENTER)
    String author;

    @ExcelProperty(value = "书源id")
    @ColumnWidth(value = 20)
    @ContentStyle(horizontalAlignment= HorizontalAlignmentEnum.CENTER,verticalAlignment= VerticalAlignmentEnum.CENTER)
    String bookSourceId;

    @ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND,
            fillForegroundColor=12,horizontalAlignment = HorizontalAlignmentEnum.DISTRIBUTED,
            verticalAlignment = VerticalAlignmentEnum.CENTER)
    @ExcelProperty(value = "直投hap链接")
    @ColumnWidth(50)
    String directHapLink;

    @ExcelProperty(value = "直投http链接")
    @ColumnWidth(50)
    @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.DISTRIBUTED,
            verticalAlignment = VerticalAlignmentEnum.CENTER)
    String directHttpLink;

    @ExcelProperty(value = "直投监测链接")
    @ColumnWidth(40)
    @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.DISTRIBUTED,
            verticalAlignment = VerticalAlignmentEnum.CENTER)
    String directMonitorLink;

    @ExcelProperty(value = "联盟hap链接")
    @ColumnWidth(40)
    @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.DISTRIBUTED,
            verticalAlignment = VerticalAlignmentEnum.CENTER)
    String unionHapLink;

    @ExcelProperty(value = "联盟http链接")
    @ColumnWidth(40)
    @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.DISTRIBUTED,
            verticalAlignment = VerticalAlignmentEnum.CENTER)
    String unionHttpLink;

    @ExcelProperty(value = "联盟监测链接")
    @ColumnWidth(40)
    @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.DISTRIBUTED,
            verticalAlignment = VerticalAlignmentEnum.CENTER)
    String unionMonitorLink;

    @ExcelProperty("测试数字")
    @ContentStyle(dataFormat = 2)
    Double testNumber;

}

1、对单元格进行了水品和垂直居中
2、对链接进行了设置单元格为链接，点击内容即可跳转到响应地址
3、由于链接内容过长，比较难看设置对齐方式为自动换行并且设置行高来适应数据
4、对于最后一行数字进行设置保留两位

注册一个写表格添加链接的handler

public class LinkWriteHandler implements CellWriteHandler {

    // 添加超链接的列下标
    List<Integer>linkColumnIndex;

    //从第几行开始添加 0 代表标题行
    int mergeRowIndex;


    public LinkWriteHandler(List<Integer> linkColumnIndex, int mergeRowIndex) {
        this.linkColumnIndex = linkColumnIndex;
        this.mergeRowIndex = mergeRowIndex;
    }

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

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

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        CellWriteHandler.super.afterCellDataConverted(writeSheetHolder, writeTableHolder, cellData, cell, head, relativeRowIndex, isHead);
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        int curRowIndex = cell.getRowIndex();
        int curColumnIndex = cell.getColumnIndex();

        if(curRowIndex>=mergeRowIndex && linkColumnIndex.contains(curColumnIndex)){
            addLinkCell(writeSheetHolder,cell,curRowIndex,curColumnIndex);
        }


    }


    private void addLinkCell(WriteSheetHolder writeSheetHolder,Cell cell, int curRowIndex, int curColumnIndex) {
        String cellStringValue=cell.getCellType()== CellType.STRING?cell.getStringCellValue():null;
        if(StringUtils.isNotBlank(cellStringValue)){
            try {
                Sheet sheet = writeSheetHolder.getSheet();
                CreationHelper creationHelper = sheet.getWorkbook().getCreationHelper();
                Hyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.URL);
                hyperlink.setAddress(cellStringValue);
                cell.setHyperlink(hyperlink);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }


}

读取表格保存数据处理写表格

public class EasyExcelTest {

    File oldFile = new File("/Users/xxx/Downloads/xxx1.xlsx");
    File newFile = new File("/Users/xxx/Downloads/xxx2.xlsx");


    @Test
    public void test(){
        EasyExcel.read(oldFile, BookModel.class, new AnalysisEventListener<BookModel>() {

            List<BookModel>saveList=new ArrayList<>();
            @Override
            public void invoke(BookModel data, AnalysisContext context) {
                data.setTestNumber(RandomUtils.nextDouble(1.0,100.0));
                saveList.add(data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                changeNewExcel(saveList);
            }

            private void changeNewExcel(List<BookModel> saveList) {
                EasyExcel.write(newFile,BookModel.class)
                        .registerWriteHandler(new LinkWriteHandler(ListUtils.newArrayList(5,6,7,9,10),1))
                        .sheet("xxx")
                        .doWrite(saveList);
            }
        }).sheet().doRead();
    }

}