【poi 看这一篇就够了！！！】使用poi导出定制excle表格

1、使用准备

1.1、导入maven

<dependency>
  <groupId>org.apache.poigroupId>
  <artifactId>poiartifactId>
  <version>5.2.2version>
dependency>
1
2
3
4
5

1.2、poi介绍

Apache POI api 的一个主要用途是用于文本提取应用程序
5.0x JavaDoc地址：文档

1.3、简单使用

如果你不想看后面的内容，看看下面的几个方法就可以开始使用了。

		//1.创建一个excel
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建excel中的sheet
        HSSFSheet sheet = workbook.createSheet("工作表1");
        // 设置下载时客户端Excel的名称
        String filename = new Date().getTime()+".xls";
        //设置样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        //设置居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置水平居中
        //设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
        //设置填充样式
        backgroundStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置背景色
        backgroundStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());

		//如果你需要设置列宽 .setColumnWidth(要设置的列的index, 宽度);
		//设置1,2列列宽
        sheet.setColumnWidth(0, 30 * 256);
        sheet.setColumnWidth(1, 30 * 256);
        
        //!!!!!!!!!!!!!!!注意：不能在一个单元格上使用多个HSSFCellStyle 样式，不能同时生效，会覆盖
        
        //创建行 sheet.createRow(0);  createRow(index)的参数是行的下标，从0开始自上向下
        HSSFRow row1 = sheet.createRow(0);
        //创建一行中的列 row1.createCell(0); 使用行创建列， createCell(index)的参数是列的下标，从0开始自左向右
        HSSFCell cel1_1 = row1.createCell(0);// 创建第一行第一列
        HSSFCell cel1_2 = row1.createCell(1);// 创建第一行第二列

		//如果你需要合并单元格,那么......
		//new CellRangeAddress(要合并的行的起始下标,要合并的行的结束下标,要合并的列的起始下标,要合并的列的结束下标) 。(0,0,0,1)表示合并第一行的1,2列
		sheet.addMergedRegion(new CellRangeAddress(0,0,0,1));
		//拿到合并后的单元格。我合并的是第一行的1,2 列，合并以后拿第一行的第一列就可以拿到。getRow(0)是拿第一行，getCell(0)是拿第一列
		HSSFCell cell0_0 = sheet.getRow(0).getCell(0);
		//注意，如果要对合并后的单元格进行样式设置（尤其是设置边框），需要设置没合并时的单元格，即上面的cel1_1 和cel1_2 
		cel1_1.setCellStyle(cellStyle);
        cel1_2.setCellStyle(cellStyle);
        //第一行1,2列合并单元格后添加数据
        cell0_0.setCellValue("测试数据");

		//后面要创建几行创建几列往后拼就行
		
		//获取最后一行表格下标
		int lastRowNum = sheet.getLastRowNum();

		//设置响应内容类型
		response.setContentType("application/octet-stream;charset=UTF-8");
		//设置响应头
        response.addHeader("Content-Disposition", "attachment;filename=" + filename);
		//获取输出流
        OutputStream ouputStream = response.getOutputStream();
        //写出输出流
        workbook.write(ouputStream);
        //清空流
        ouputStream.flush();
        //关闭流
        ouputStream.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
51
52
53
54
55
56
57
58
59
60
61
62

1.4 使用案例

controller层

package com..excelExport.controller;

import com.excelExport.service.ExcelExportService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
/**
 * @author mingshanlaoyao
 * @description: TODO
 * @date 2022/9/16 
 */
@RestController
@RequestMapping("/export")
public class CaseCountExportExcel {

    @Autowired
    private ExcelExportService excelExportService;

  
    @RequestMapping("/testExportExcel")
    public void testExportExcel(HttpServletResponse response){
        try {
            excelExportService.testExportExcel(response);
        } catch (Exception 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

service层

package com.excelExport.service.impl;

import com.excelExport.service.ExcelExportService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.util.*;

import static org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined.GREY_25_PERCENT;

/**
 * @author mingshanlaoyao
 * @description: TODO
 * @date 2022/9/16 
 */
@Service
public class ExcelExportServiceImpl implements ExcelExportService {

    public void testExportExcel(HttpServletResponse response) throws IOException {

        //创建一个excel
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("工作表1");//创建一个excel的sheet

        String filename = new Date().getTime()+".xls";// 设置下载时客户端Excel的名称
        //设置居中样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置水平居中
        cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        cellStyle.setBorderRight(BorderStyle.THIN);// 右边框

        //设置1,2列列宽
        sheet.setColumnWidth(0, 30 * 256);
        sheet.setColumnWidth(1, 30 * 256);

        //设置背景色样式
        HSSFCellStyle backgroundStyle = workbook.createCellStyle();
        //设置填充样式
        backgroundStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置背景色
        backgroundStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
        backgroundStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        backgroundStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        backgroundStyle.setBorderTop(BorderStyle.THIN);// 上边框
        backgroundStyle.setBorderRight(BorderStyle.THIN);// 右边框



        HSSFRow row1 = sheet.createRow(0);// 创建第一行
        HSSFCell cel1_1 = row1.createCell(0);// 创建第一行第一列
        HSSFCell cel1_2 = row1.createCell(1);// 创建第一行第二列
        //第一行1,2列  合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,1));
        HSSFCell cell0_0 = sheet.getRow(0).getCell(0);
        //设置合并后的style
        cel1_1.setCellStyle(cellStyle);
        cel1_2.setCellStyle(cellStyle);


        //第一行一二列合并单元格后添加数据
        cell0_0.setCellValue("学生信息");
        cell0_0.setCellStyle(cellStyle);//设置样式

        //创建第二行
        HSSFRow row2 = sheet.createRow(1);
        //创建第二行第一列
        HSSFCell cell2_1 = row2.createCell(0);
        HSSFCell cell2_2 = row2.createCell(1);
        cell2_1.setCellValue("姓名");
        cell2_2.setCellValue("性别");
        List<Map<String,Object>> studentList = new ArrayList<>();
        Map<String,Object> stu1 = new HashMap<>();
        stu1.put("name","张无忌");
        stu1.put("sex","男");
        Map<String,Object> stu2 = new HashMap<>();
        stu2.put("name","赵敏");
        stu2.put("sex","女");
        Map<String,Object> stu3 = new HashMap<>();
        stu3.put("name","周芷若");
        stu3.put("sex","女");
        Map<String,Object> stu4 = new HashMap<>();
        stu4.put("name","金毛狮王");
        stu4.put("sex","男");
        Map<String,Object> stu5 = new HashMap<>();
        stu5.put("name","殷素素");
        stu5.put("sex","女");
        studentList.add(stu1);
        studentList.add(stu2);
        studentList.add(stu3);
        studentList.add(stu4);
        studentList.add(stu5);
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0;i<studentList.size();i++){
            int index = i+lastRowNum;
            HSSFRow rowi = sheet.createRow(index);
            HSSFCell celli_1 = rowi.createCell(0);
            HSSFCell celli_2 = rowi.createCell(1);
            celli_1.setCellValue(studentList.get(i).get("name").toString());
            celli_2.setCellValue(studentList.get(i).get("sex").toString());
        }

        response.setContentType("application/octet-stream;charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + filename);

        OutputStream ouputStream = response.getOutputStream();
        workbook.write(ouputStream);
        ouputStream.flush();
        ouputStream.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
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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123

下载效果