spring-boot导出excel
spring-boot导出excel
本文使用Apache POL实现excel文档的导出
pom.xml 添加Apache POL依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
导出excel
导出excel比较重要的api有以下几个,其他的并无难点
- 创建一个excel文件工作薄;(HSSFWorkbook workbook = new HSSFWorkbook())
- 创建一张表;HSSFSheet sheet = workbook.createSheet("统计表")
- 创建一行;HSSFRow row = sheet.createRow(0)
- 填充一列数据; row.createCell(0).setCellValue("数据")
- 设置一个单元格样式;cell.setCellStyle(style)
package com.example.demo.controller;
import com.example.demo.domain.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
@RestController
@RequestMapping(value = "/excel")
public class ExcelController {
@Autowired
private UserService userService;
//创建表头
private void createTitle(HSSFWorkbook workbook,HSSFSheet sheet){
HSSFRow row = sheet.createRow(0);
//设置列宽,setColumnWidth的第二个参数要乘以256,这个参数的单位是1/256个字符宽度
sheet.setColumnWidth(1,12*256);
sheet.setColumnWidth(3,17*256);
//设置为居中加粗
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setBold(true);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFont(font);
HSSFCell cell;
cell = row.createCell(0);
cell.setCellValue("ID");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("显示名");
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("用户名");
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("创建时间");
cell.setCellStyle(style);
}
//生成user表excel
@GetMapping(value = "/getUser")
public String getUser(HttpServletResponse response) throws Exception{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("统计表");
createTitle(workbook,sheet);
List<User> rows = userService.getAll();
//设置日期格式
HSSFCellStyle style = workbook.createCellStyle();
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
//新增数据行,并且设置单元格数据
int rowNum=1;
for(User user:rows){
HSSFRow row = sheet.createRow(rowNum);
row.createCell(0).setCellValue(user.getId());
row.createCell(1).setCellValue(user.getName());
row.createCell(2).setCellValue(user.getUsername());
HSSFCell cell = row.createCell(3);
cell.setCellValue(user.getCreate_time());
cell.setCellStyle(style);
rowNum++;
}
String fileName = "导出excel例子.xls";
//生成excel文件
buildExcelFile(fileName, workbook);
//浏览器下载excel
buildExcelDocument(fileName,workbook,response);
return "download excel";
}
//生成excel文件
protected void buildExcelFile(String filename,HSSFWorkbook workbook) throws Exception{
FileOutputStream fos = new FileOutputStream(filename);
workbook.write(fos);
fos.flush();
fos.close();
}
//浏览器下载excel
protected void buildExcelDocument(String filename,HSSFWorkbook workbook,HttpServletResponse response) throws Exception{
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(filename, "utf-8"));
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
}
转载地址:https://my.oschina.net/tongjh/blog/1600000
相关推荐
- c++builder读取excel数据源,并列序和修改指定excel列的数据
- c++builder读取excel数据源,并排序跟修改指定excel列的数据
- 【Excel】如何将第二列的数据与第一列的匹配并将其数据放到与第一列对应的行(VLOOKUP)
- 关于linux下,tomcat应用中,利用jasper导出报表的异常
- Jasper编程模式实现报表导出
- 通过poi解析excel
- x01.Excel: 合计件数
- jsp中如何把数据从数据库导出在页面直接生成html格式?
- https连接的时候,如果不能导出私钥,那如何解密数据
- 解决一下这个错误,用poi操作excel的.xls没有关问题,xlsx有有关问题
- db2 命令
- 在shell脚本中调用另一个脚本的三种不同方法(fork, exec, source)