Springboot导出excel,合并单元格示例

以下用一个示例来说明springboot如何导出数据到excel。

首先引入Maven依赖:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
		<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.17</version>
        </dependency>
        <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.17</version>
        </dependency>
        <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>3.17</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
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
@RequestMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response, HttpServletRequest request) throws Exception {
        //接收参数
        String ids = request.getParameter("ids");
        List<ProjectInfoVo> result = projectInfoService.getProByIds(ids);
 
        //查询数据,实际可通过传过来的参数当条件去数据库查询,在此我就用空集合(数据)来替代
        List<ProjectInfoVo> list = new ArrayList<>();
 
        //创建poi导出数据对象
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
 
        //创建sheet页
        SXSSFSheet sheet = sxssfWorkbook.createSheet("开复工项目");
 
        CellRangeAddress region1 = new CellRangeAddress(0, 1, (short) 0, (short) 12);
        //参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列
        sheet.addMergedRegion(region1);
        SXSSFRow headTitle = sheet.createRow(0);
        headTitle.createCell(0).setCellValue("重点工程项目计划表");
 
        //创建表头
        SXSSFRow headRow = sheet.createRow(4);
        //设置表头信息
        headRow.createCell(0).setCellValue("序号");
        headRow.createCell(1).setCellValue("项目分类");
        headRow.createCell(2).setCellValue("投资分类");
 
        headRow.createCell(3).setCellValue("牵头单位");
        headRow.createCell(4).setCellValue("工程名称");
        headRow.createCell(5).setCellValue("建设单位");
 
        headRow.createCell(6).setCellValue("建设地点");
        headRow.createCell(7).setCellValue("建设内容");
        headRow.createCell(8).setCellValue("总投资");
 
        headRow.createCell(9).setCellValue("手续进展及工程现状");
        headRow.createCell(10).setCellValue("是否XXXX项目");
 
        headRow.createCell(11).setCellValue("开工时间");
        headRow.createCell(12).setCellValue("完工时间");
        String yihui = null;
 
        // 遍历上面数据库查到的数据
        for (ProjectInfoVo pm : result) {
            if (pm.getPrjType3() == 0) {
                yihui = "是";
            } else {
                yihui = "否";
            }
            SXSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
            dataRow.createCell(0).setCellValue(pm.getPrjId());
            dataRow.createCell(1).setCellValue(pm.getProjectTypeName());
            dataRow.createCell(2).setCellValue(pm.getPrjType4Name());
 
            dataRow.createCell(3).setCellValue(pm.getLeadOrgId());
            dataRow.createCell(4).setCellValue(pm.getPrjName());
            dataRow.createCell(5).setCellValue(pm.getDevOrgId());
 
            dataRow.createCell(6).setCellValue(pm.getDetailAddr());
            dataRow.createCell(7).setCellValue(pm.getPrjIntro());
            dataRow.createCell(8).setCellValue(pm.getPlanTotalInvest() + "");
 
            dataRow.createCell(9).setCellValue("XXXXX");
            dataRow.createCell(10).setCellValue(yihui);
 
            dataRow.createCell(11).setCellValue(pm.getRealStartDate());
            dataRow.createCell(12).setCellValue(pm.getRealEndDate());
 
        }
        // 下载导出
        String filename = "XXXXXXX平台工程信息表";
        // 设置头信息
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        //一定要设置成xlsx格式
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename + ".xlsx", "UTF-8"));
        //创建一个输出流
        ServletOutputStream outputStream = response.getOutputStream();
        //写入数据
        sxssfWorkbook.write(outputStream);
 
        // 关闭
        outputStream.close();
        sxssfWorkbook.close();
    }

//查询数据,实际可通过传过来的参数当条件去数据库查询,在此我就用空集合(数据)来替代
List<ProjectInfoVo> list = new ArrayList<>();

//创建poi导出数据对象
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();

//创建sheet页
SXSSFSheet sheet = sxssfWorkbook.createSheet(“开复工项目”);

CellRangeAddress region1 = new CellRangeAddress(0, 1, (short) 0, (short) 12);
//参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列
sheet.addMergedRegion(region1);
SXSSFRow headTitle = sheet.createRow(0);
headTitle.createCell(0).setCellValue(“重点工程项目计划表”);

//创建表头
SXSSFRow headRow = sheet.createRow(4);
//设置表头信息
headRow.createCell(0).setCellValue(“序号”);
headRow.createCell(1).setCellValue(“项目分类”);
headRow.createCell(2).setCellValue(“投资分类”);

headRow.createCell(3).setCellValue(“牵头单位”);
headRow.createCell(4).setCellValue(“工程名称”);
headRow.createCell(5).setCellValue(“建设单位”);

headRow.createCell(6).setCellValue(“建设地点”);
headRow.createCell(7).setCellValue(“建设内容”);
headRow.createCell(8).setCellValue(“总投资”);

headRow.createCell(9).setCellValue(“手续进展及工程现状”);
headRow.createCell(10).setCellValue(“是否XXXX项目”);

headRow.createCell(11).setCellValue(“开工时间”);
headRow.createCell(12).setCellValue(“完工时间”);
String yihui = null;

// 遍历上面数据库查到的数据
for (ProjectInfoVo pm : result) {
if (pm.getPrjType3() == 0) {
yihui = “是”;
} else {
yihui = “否”;
}
SXSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
dataRow.createCell(0).setCellValue(pm.getPrjId());
dataRow.createCell(1).setCellValue(pm.getProjectTypeName());
dataRow.createCell(2).setCellValue(pm.getPrjType4Name());

dataRow.createCell(3).setCellValue(pm.getLeadOrgId());
dataRow.createCell(4).setCellValue(pm.getPrjName());
dataRow.createCell(5).setCellValue(pm.getDevOrgId());

dataRow.createCell(6).setCellValue(pm.getDetailAddr());
dataRow.createCell(7).setCellValue(pm.getPrjIntro());
dataRow.createCell(8).setCellValue(pm.getPlanTotalInvest() + “”);

dataRow.createCell(9).setCellValue(“XXXXX”);
dataRow.createCell(10).setCellValue(yihui);

dataRow.createCell(11).setCellValue(pm.getRealStartDate());
dataRow.createCell(12).setCellValue(pm.getRealEndDate());

}
// 下载导出
String filename = “XXXXXXX平台工程信息表”;
// 设置头信息
response.setCharacterEncoding(“UTF-8”);
response.setContentType(“application/vnd.ms-excel”);
//一定要设置成xlsx格式
response.setHeader(“Content-Disposition”, “attachment;filename=” + URLEncoder.encode(filename + “.xlsx”, “UTF-8”));
//创建一个输出流
ServletOutputStream outputStream = response.getOutputStream();
//写入数据
sxssfWorkbook.write(outputStream);

// 关闭
outputStream.close();
sxssfWorkbook.close();
}

前台请求接口方法:

1
2
3
function bbdc() {
    window.location.href= 'xxxxxxxx/front/exportExcel';
}

合并Excel单元格

方法:

1
2
3
CellRangeAddress region1 = new CellRangeAddress(rowNumber, rowNumber, (short) 0, (short) 11); 
//参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列 
sheet.addMergedRegion(region1);

设置样式

演示文字居中方法

1
2
3
4
5
6
7
//合并的单元格样式
HSSFCellStyle boderStyle = wb.createCellStyle();
//垂直居中
boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
//设置一个边框
boderStyle.setBorderTop(HSSFBorderFormatting.BORDER_THICK);

更具体、更详细的操作,当然是官方文档啦:http://poi.apache.org/apidocs/dev/org/apache/poi

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注

关注我们