追枫狼

导航

< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

统计

Java中使用easyexcel导入导出数据

  工作中常常遇到导入导出Excel数据,还需要设置表格边框、合并单元格、字体居中等等各种样式,尝试了多种方式觉得阿里的easyexcel挺好使,记录一下使用、表格样式如下:

  导入依赖

1
2
3
4
5
<dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>2.2.3</version>
</dependency>

  创建导出实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
@HeadRowHeight(38)  //设置标题行高
@Data
public class SwhcBdcVo implements Serializable {
 
    @ExcelProperty(value = { "基本信息", "序号" }, index = 0//需要在表格显示的列
    @ColumnWidth(6)   //每列占用的宽度,列宽
    private String xh;
 
    @ExcelProperty(value = { "基本信息", "姓名" }, index = 1)
    @ColumnWidth(8)
    private String qlrmc;
 
    @ExcelProperty(value = { "基本信息", "性别" }, index = 2)
    @ColumnWidth(6)
    private String xb;
 
    @ExcelProperty(value = { "基本信息", "身份证号" }, index = 3)
    @ColumnWidth(16)
    private String qlrzjh;
 
    @ExcelProperty(value = {"不动产信息", "房产来源(去向)" }, index = 4)
    @ColumnWidth(30)
    private String qx;
 
    @ExcelProperty(value = {"不动产信息", "具体地址" }, index = 5)
    @ColumnWidth(30)
    private String dz;
 
    @ExcelProperty(value = {"不动产信息", "建筑面积(m²)" }, index = 6)
    @ColumnWidth(11)
    private BigDecimal jzmj;
 
    @ExcelProperty(value = {"不动产信息", "产权性质" }, index = 7)
    @ColumnWidth(11)
    private String cqxz;
 
    @ExcelProperty(value = {"不动产信息", "登记时间(年月)" }, index = 8)
    @ColumnWidth(11)
    private String djsj;
 
    @ExcelProperty(value = {"不动产信息", "交易价格(万元)" }, index = 9)
    @ColumnWidth(11)
    private BigDecimal jyjg;
 
    @ExcelProperty(value = "备注", index = 10)
    @ColumnWidth(24)
    private String bz;
 
    @ExcelIgnore  //不需要在表格展示的字段
    private String ywh;
 
    @ExcelIgnore
    private Date compareDjsj;
 
}

  生成Excel模板

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
public void generateTemplate(HttpServletResponse response) {
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMddHHmmss");
        String fileName = URLEncoder.encode(String.format("%s.xlsx", sdf2.format(new Date())),
                StandardCharsets.UTF_8.toString());
        response.setContentType("application/force-download");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
 
        // 这里 需要指定写用哪个class去写
        ExcelWriter writer = new ExcelWriterBuilder()
                .autoCloseStream(true)
                .excelType(ExcelTypeEnum.XLSX)
                .file(response.getOutputStream())
                .head(SwhcBdcVo.class)
                // 核心代码:表头和正文的样式在此
                .registerWriteHandler(setConfigure())
                .build();
        WriteSheet writeSheetfw = new WriteSheet();
        writeSheetfw.setSheetName("Sheet1");
        writer.write(null, writeSheetfw);
        // 关闭流
        writer.finish();
    }

  数据导入读取

1
2
3
4
5
6
7
8
9
public R dr(MultipartFile file) {
        if (!StrUtil.endWithAny(file.getOriginalFilename(), ".xls", ".xlsx")) {
            return R.failed("上传的文件不是excel");
        }
        List<SwhcBdcVo> drList = EasyExcelUtil.readExcel(file, "Sheet1", SwhcBdcVo.class, 2);    //2为表格标题占用的行数,读取跳过标题
        if (CollUtil.isEmpty(drList )) {
            return R.failed("未获取到导入表格数据");
        }
}

  EasyExcelUtil工具类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import org.springframework.web.multipart.MultipartFile;
 
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
 
public class EasyExcelUtil {
 
    public static <T> List<T> readExcel(MultipartFile excel, String sheetName, Class<T> clazz, int headLineNum)
            throws IOException {
        ExcelReaderBuilder readerBuilder = getReader(excel);
        if (readerBuilder == null) {
            return null;
        }
        ExcelReaderSheetBuilder sheet = readerBuilder.sheet(sheetName);
        if (sheet == null) {
            return null;
        }
        sheet.head(clazz).headRowNumber(headLineNum);
 
        return sheet.doReadSync();
    }
 
    private static ExcelReaderBuilder getReader(MultipartFile excel) throws IOException {
        String filename = excel.getOriginalFilename();
        if (filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))) {
            InputStream is = new BufferedInputStream(excel.getInputStream());
            return EasyExcel.read(is);
        } else {
            return null;
        }
    }
}

  数据生成到表格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
log.info("-------开始数据写表操作------------------");
        //查询到的数据写入Excel表格,表格文件传入minio
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        //需要合并的列
        int[] mergeColumeIndex = {0,1,2,3};
        //从第三行后开始合并
        int mergeRowIndex = 2;
        // 调用合并单元格工具类,此工具类是根据工程名称相同则合并后面数据
        ExcelFillCellMergeStrategy excelFillCellMergeStrategy = new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex);
        EasyExcel.write(out, SwhcBdcVo.class)
                .autoCloseStream(true)
                .excelType(ExcelTypeEnum.XLSX)
                .head(SwhcBdcVo.class)
                // 核心代码:表头和正文的样式在此
                .registerWriteHandler(setConfigure())
                .registerWriteHandler(excelFillCellMergeStrategy)
                .sheet("Sheet1")
                .doWrite(totalPersonBdcList);
        byte[] bs = out.toByteArray();
        out.close();
        try (XSSFWorkbook workbook = new XSSFWorkbook(new ByteArrayInputStream(bs));
             OutputStream out1 = new FileOutputStream("./测试文件.xlsx")) {
            workbook.write(out1);
            log.info("-----------写入文件结束------------");
        }

  设置标题样式和内容样setConfigure()方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
//配置字体,表头背景等
    private static HorizontalCellStyleStrategy setConfigure() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex()); // 白色背景
        WriteFont headWriteFont = new WriteFont();
 
        // 加粗
//        headWriteFont.setBold(true);
//        headWriteFont.setFontHeightInPoints((short) 14); // 设置行高,不重要
        headWriteCellStyle.setWriteFont(headWriteFont);
 
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
//        contentWriteFont.setFontHeightInPoints((short) 14);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //导出数据垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //导出数据水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 设置边框
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
 
        //设置 自动换行
        //contentWriteCellStyle.setWrapped(true);
 
        //设置
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

  设置内容合并策略,指定列的内容相同,单元格合并

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
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 lombok.Data;
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;
 
@Data
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
 
    private int[] mergeColumnIndex;
    private int mergeRowIndex;
 
    public ExcelFillCellMergeStrategy() {
    }
 
    public ExcelFillCellMergeStrategy(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) {
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();
 
        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }
 
 
    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        //获取当前行的当前列的数据和上一行的当前列列数据,通过上一行数据是否相同进行合并
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
 
        // 比较当前行的第一列的单元格与上一行是否相同,相同合并当前单元格与上一行
        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);
                // 若上一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元
                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);
            }
        }
    }
}

  

posted on   追枫狼  阅读(1715)  评论(0编辑  收藏  举报

相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示