使用easypoi将数据导入excel中_easypoi导入excel

本文介绍如何使用easypoi将数据当如到excel中；
官方文档：http://easypoi.mydoc.io/
里面有介绍easypoi的注解使用等。
poi的依赖

 <!--easypoi的依赖-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

本文是将用户名和密码导入到excel中，所以封装一个用户对象

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.*;
import lombok.experimental.Accessors;

import java.io.Serializable;

/**
 * @program: bootshirojwt
 * @description:
 * @author: liu.chuanjiang
 * @create: 2022-05-11 20:55
 */
@Builder
@Data
@EqualsAndHashCode(callSuper = false)
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
public class UserInfo implements Serializable {
    private static final long serialVersionUID = 1L;
    @Excel(name = "用户名", width = 20, orderNum = "1")
    private String userName;
    @Excel(name = "密码", width = 20, orderNum = "2")
    private String password;


}
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

如果需要对excel的样式进行设置，那么就封装一个样式类

import cn.afterturn.easypoi.excel.export.styler.AbstractExcelExportStyler;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;

/**
 * @ClassName: ExcelExportMyStylerImpl
 * @Description: 自定义报表导出样式，可以修改表头颜色，高度等
 * @Author: sunt
 * @Date: 2019/8/29 21:39
 * @Version 1.0
 **/
public class ExcelExportMyStyler extends AbstractExcelExportStyler implements IExcelExportStyler {

    public ExcelExportMyStyler(Workbook workbook) {
        super.createStyles(workbook);
    }

    @Override
    public CellStyle getTitleStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);// 加粗
        titleStyle.setFont(font);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);// 居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        titleStyle.setFillForegroundColor(IndexedColors.AQUA.index);// 设置颜色
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setWrapText(true);
        return titleStyle;
    }

    @SuppressWarnings("deprecation")
    @Override
    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }

    @Override
    public CellStyle getHeaderStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);// 加粗
        font.setColor(IndexedColors.RED.index);
        font.setFontHeightInPoints((short) 11);
        titleStyle.setFont(font);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);// 居中
        titleStyle.setFillForegroundColor(IndexedColors.WHITE.index);// 设置颜色
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setWrapText(true);
        return titleStyle;
    }

    @SuppressWarnings("deprecation")
    @Override
    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }
}
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

接下就是excel导出的核心之处了，excel工具类

package com.lcj.easypoi;


import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

/**
 * @ClassName: ExcelExportUtil
 * @Description: Exceld导出工具类
 * @Author: sunt
 * @Date: 2019/8/30 14:49
 * @Version 1.0
 **/
@Slf4j
public class MyExcelExportUtil {

    /**
     * Excel文件导出,导出的文件名默认为:headTitle+当前系统时间
     *
     * @param listData  要导出的list数据
     * @param pojoClass 定义excel属性信息
     * @param headTitle Excel文件头信息
     * @param sheetName Excel文件sheet名称
     * @param response
     */
    public static void exportExcel(Collection<?> listData, Class<?> pojoClass, String headTitle, String sheetName, HttpServletResponse response) {
        log.info("进入到exportExcel");
        ExportParams params = new ExportParams(headTitle, sheetName);
        params.setHeight((short) 8);
        params.setStyle(ExcelExportMyStyler.class);
        try {
            Workbook workbook = ExcelExportUtil.exportExcel(params, pojoClass, listData);
            String fileName = headTitle + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
            fileName = URLEncoder.encode(fileName, "UTF8");
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel;chartset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            ServletOutputStream out = response.getOutputStream();
            workbook.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void exportExcel1(Collection<?> listData, Class<?> pojoClass, String headTitle, String sheetName) {
        log.info("进入到exportExcel");
        FileOutputStream outStream=null;
        try {
            String fileName = "C:\\Users\\Administrator.DESKTOP-JMAFGHR\\Desktop\\工作\\test.xlsx";
            outStream = new FileOutputStream(fileName);
            ExportParams params = new ExportParams(headTitle, sheetName);
            params.setHeight((short) 8);
            params.setStyle(ExcelExportMyStyler.class);
            Workbook workbook = ExcelExportUtil.exportExcel(params, pojoClass, listData);
            workbook.write(outStream);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                if (outStream != null) {
                    outStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}
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

测试类

/**
 * @program: test_pay
 * @description:
 * @author: liu.chuanjiang
 * @create: 2022-05-11 22:19
 */
public class test {
    public static void main(String[] args) {
        List<UserInfo> list=new ArrayList<>();
        UserInfo userInfo1 = new UserInfo().setUserName("张三").setPassword("123");
        list.add(userInfo1);
        UserInfo userInfo2 = new UserInfo().setUserName("李四").setPassword("456");
        list.add(userInfo2);

        MyExcelExportUtil.exportExcel1(list,UserInfo.class,"用户信息","用户号信息");
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17