项目中经常会使用到导出各种报表之类的操作,java中POI能够帮助我们操作Excel表格,实现对数据的写入和计算操作,最终导出到用户本地机器上。下面就给大家介绍一下SpringBoot中导出Excel的具体实现!
首先导入POI依赖
<!-- 导入POI,操作excel需要的依赖 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
导出Excel帮助类
public class ExportHelper {
/**
* 导出浏览器
*
* @param fileName 文件名
* @param workbook HSSFWorkbook对象
*/
public void WriteResponse(String fileName, HSSFWorkbook workbook,
HttpServletRequest request, HttpServletResponse response) throws IOException {
SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式
fileName = fileName + df.format(new Date());//获取当前系统时间
fileName += ".xls";
//解决下载文件时文件名乱码问题 给名称加上编码格式UTF-8
byte[] fileNameBytes = fileName.getBytes(StandardCharsets.UTF_8);
fileName = new String(fileNameBytes, 0, fileNameBytes.length, StandardCharsets.ISO_8859_1);
//下载文件的默认名称
response.setHeader("content-Type", "application/vnd.ms-excel");
//告诉浏览器用什么软件可以打开此文件
response.setHeader("content-disposition", "attachment; filename=" + fileName);
workbook.write(response.getOutputStream());
}
/// <summary>
/// 导出Excel 将数据写入到workbook中
/// </summary>
/// <returns></returns>
public HSSFWorkbook ExportExcel(List<UserInfo> list) {
try {
//Excel实例
HSSFWorkbook workbook = new HSSFWorkbook();
//表实例
Sheet sheet1 = workbook.createSheet("Sheet1");
//创建行 标题行 下标(索引) 0
Row row0 = sheet1.createRow(0);
row0.createCell(0).setCellValue("序号");
row0.createCell(1).setCellValue("姓名");
row0.createCell(2).setCellValue("性别");
row0.createCell(3).setCellValue("年龄");
row0.createCell(4).setCellValue("身高");
row0.createCell(5).setCellValue("爱好");
//每一行数据
for (int i = 0; i < list.size(); i++) {//行
//创建行 i+1是因为第一行已经被标题占了,所以这里从第二行开始,对应的下标为1
Row row = sheet1.createRow(i + 1);
//创建单元格
row.createCell(0).setCellValue(i+1);
row.createCell(1).setCellValue(list.get(i).getUserName);
row.createCell(2).setCellValue(list.get(i).getUserSex);
row.createCell(3).setCellValue(list.get(i).getUserAge);
row.createCell(4).setCellValue(list.get(i).getUserRise);
row.createCell(5).setCellValue(list.get(i).getUserLike);
}
return workbook;
} catch (Exception e) {
e.printStackTrace();
System.out.println("导出Excel错误信息:" + e.getMessage());
throw e;
}
}
}
Controller调用 Excel导出实例
/**
* PanSatImage导出所有页数据
* @param request
* @param response
* @throws IOException
*/
@GetMapping(value = "/ExportPages")
public void ExportPages(HttpServletRequest request,HttpServletResponse response) throws IOException {
try
{
List<UserInfo> list = new ArrayList<>();
UserInfo userInfo=new UserInfo();
userInfo.setUserName("张三");
userInfo.setUserSex("男");
userInfo.setUserAge("20");
userInfo.setUserRise("176");
userInfo.setUserLike("唱歌");
list.add(userInfo);
UserInfo userInfo2=new UserInfo();
userInfo2.setUserName("李四");
userInfo.setUserSex("女");
userInfo22.setUserAge("21");
userInfo2.setUserRise("170");
userInfo2.setUserLike("跳舞");
list.add(userInfo2);
UserInfo userInfo3=new UserInfo();
userInfo3.setUserName("王五");
userInfo3.setUserSex("男");
userInfo3.setUserAge("23");
userInfo3.setUserRise("178");
userInfo3.setUserLike("打篮球");
list.add(userInfo3);
ExportHelper exportHelper=new ExportHelper();
exportHelper.WriteResponse("文件名", exportHelper.ExportExcel(list),
request,response);
}
catch (Exception e)
{
e.printStackTrace();
throw e;
}
}