java----excel导出的3种方法_java导出excel的三种方法

1.huttol

  <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.14</version>
        </dependency>

1
2
3
4
5
6

  @SneakyThrows
    @PostMapping("/excelTest")
    public void ss() {
        TestBean bean1 = new TestBean();
        bean1.setName("张三");
        bean1.setAge(22);
        bean1.setPass(true);
        bean1.setScore(66.30);
        bean1.setExamDate(DateUtil.date());

        TestBean bean2 = new TestBean();
        bean2.setName("李四2");
        bean2.setAge(28);
        bean2.setPass(false);
        bean2.setScore(38.50);
        bean2.setExamDate(DateUtil.date());

        TestBean bean3 = new TestBean();
        bean3.setName("李四3");
        bean3.setAge(28);
        bean3.setPass(false);
        bean3.setScore(38.50);
        bean3.setExamDate(DateUtil.date());

        List<TestBean> ls=new ArrayList<>();
        ls.add(bean1);
        ls.add(bean2);
        List<TestBean> rows = CollUtil.newArrayList(ls);

        // 通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter("E:/writeBeanTest.xlsx");
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(4, "一班成绩单");
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(rows,true);
        // 关闭writer，释放内存
        writer.close();
    }

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

2.原始POI

    /*
    * 原始POI
    * */
    @RequestMapping(value = "/getExcel")
    @ResponseBody
    public void getExcel( HttpServletResponse response) throws IOException {
        //查询全部User信息
        Date date = new Date();
        List<User> list = new ArrayList<>(); //userService.findAllUser();
        list.add(new User(1, "zhang", 1, date, "1111"));
        list.add(new User(2, "li", 2, date, "2222"));

        //创建HSSFWorkbook对象-
        //HSSFWorkbook wb = new HSSFWorkbook(file.getInputStream());
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建HSSFSheet对象
        HSSFSheet sheet = wb.createSheet("sheet0");
        //创建HSSFRow对象
        HSSFRow row = sheet.createRow(0);
        //创建HSSFCell对象
        HSSFCell cell = row.createCell(0);
        //设置单元格的值
        cell.setCellValue("用户信息表");

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
        //遍历userList

        int i = 1;
        for (User user : list) {
            //在sheet里创建第i行
            HSSFRow irow = sheet.createRow(i);
            //创建单元格并设置单元格内容
            irow.createCell(0).setCellValue(user.getId());
            irow.createCell(1).setCellValue(user.getUserName());
            irow.createCell(2).setCellValue(user.getPassword());

            i++;
        }
        //输出Excel文件
        //OutputStream output=response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=details.xls");
        response.setContentType("application/msexcel");

        FileOutputStream output = new FileOutputStream("E:\\workbook.xlsx");

        wb.write();
        output.close();
        
    }
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

3.easyPOI–注解的方式

   <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>ooxml-schemas</artifactId>
            <version>1.4</version>
        </dependency>

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.0</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

@Data
public class User {

    @Excel(name = "学生id", height = 20, width = 30, isImportField = "true_st")
    private Integer id;

    @Excel(name = "学生姓名", height = 20, width = 30, isImportField = "true_st")
    private String userName;

    @Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生", isImportField = "true_st")
    private   int   sex;

    @Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isImportField = "true_st", width = 20)
    private Date birthday;

    @Excel(name = "学生密码", height = 20, width = 30, isImportField = "true_st")
    private String password;

    public User(Integer id, String userName, int sex, Date birthday, String password) {
        this.id = id;
        this.userName = userName;
        this.sex = sex;
        this.birthday = birthday;
        this.password = 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
28

 /*
     *
     * 简单导出excel--easyPOI
     * */
    @SneakyThrows
    @RequestMapping(path = "/importEmployee", method = RequestMethod.POST)
    @ResponseBody
    public void importEmployee(@RequestParam("excelName") String excelName) {
       /*  File savefile = new File("E:/excel/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }*/
        File f = new File(this.getClass().getResource("/").getPath() + excelName + ".xlsx");
        //File f = new File(ResourceUtils.getFile(ResourceUtils.CLASSPATH_URL_PREFIX).toPath()+excelName+".xlsx");
        //查询list
        List<User> list = new ArrayList<>(); //userService.findAllUser();
        Date date = new Date();
        list.add(new User(1, "zhang", 1, date, "1111"));
        list.add(new User(2, "li", 2, date, "2222"));
        list.add(new User(3, "yu", 2, date, "3333"));
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("计算机一班学生", "学生"), User.class, list);
        FileOutputStream fos = new FileOutputStream(f);
        workbook.write(fos);
        fos.close();
    }

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

  /*
     *含图片导出excel---easyPOI
     *
     * */
    @SneakyThrows
    @RequestMapping(path = "/importEmployee2", method = RequestMethod.POST)
    @ResponseBody
    public void importEmployee2(@RequestParam("excelName") String excelName) {
        List<CompanyHasImgModel> list = list = new ArrayList<CompanyHasImgModel>();
        list.add(new CompanyHasImgModel("科密", "E:\\excel\\baidu.png", "北京市海淀区西北旺东路10号院百度科技园1号楼"));
        list.add(new CompanyHasImgModel("淘宝", "E:\\excel\\baidu.png", "北京市海淀区西北旺东路10号院百度科技园1号楼"));
        list.add(new CompanyHasImgModel("字节跳动", "E:\\excel\\baidu.png", "亚马逊热带雨林"));
        list.add(new CompanyHasImgModel("360", "E:\\excel\\baidu.png", "山东济宁俺家"));
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), CompanyHasImgModel.class, list);
        FileOutputStream fos = new FileOutputStream(this.getClass().getResource("/").getPath() + excelName + ".xlsx");
        workbook.write(fos);
        fos.close();


    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

@Data
public class CompanyHasImgModel {

    @Excel(name = "公司名",width = 20 , height = 20, isImportField = "true_st")
    private  String name;
    @Excel(name = "公司LOGO", type = 2 ,width = 40 , height = 20,imageType = 1)
    private String companyLogo;
    @Excel(name = "公司地址" ,width = 20 , height = 20, isImportField = "true_st")
    private  String address;

    public CompanyHasImgModel(String name, String companyLogo, String address) {
        this.name = name;
        this.companyLogo = companyLogo;
        this.address = address;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

easyPOI
easyPOI
hutool