Apache POI 4.0.1版本写入普通Excel文件(兼容 xls 和 xlsx)(四)
一般在公司写项目，写入Excel时会有这样的场景：前台页面上有一个导出按钮，点击后将后台某张表里的数据以Excel的形式导出，导出的Excel文件通过浏览器下载。
基于最新的Apache POI 4.0.1版本来总结一下写入Excel的过程。
代码前准备：
添加4.0.1 poi maven依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.springbootemaildemo</groupId>
<artifactId>springboot-email-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>springboot-email-demo</name>
<description>Demo project for Spring Boot</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.6.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-mail</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.7.0</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.7.0</version>
</dependency>
<dependency>
<groupId>org.jsoup</groupId>
<artifactId>jsoup</artifactId>
<version>1.11.3</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.41</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.10</version>
</dependency>
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.8.3</version>
</dependency>
<dependency>
<groupId>javax.persistence</groupId>
<artifactId>persistence-api</artifactId>
<version>1.0</version>
</dependency>
<!--poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
DataVo（数据封装类）
package com.springbootemaildemo.excel.b;
/**
* 读取Excel时，封装读取的每一行的数据
*/
public class DataVo {
/**
* 姓名
*/
private String name;
/**
* 年龄
*/
private Integer age;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
ExcelWriter（写入excel文件主要代码类）
package com.springbootemaildemo.excel.b;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
public class ExcelWriter {
private static List<String> CELL_HEADS; //列头
static {
// 类装载时就载入指定好的列头信息，如有需要，可以考虑做成动态生成的列头
CELL_HEADS = new ArrayList<>();
CELL_HEADS.add("姓名");
CELL_HEADS.add("年龄");
}
/**
* 生成Excel并写入数据信息
*
* @param dataList 数据列表
* @return 写入数据后的工作簿对象
*/
public static Workbook exportData(List<DataVo> dataList) {
// 生成xlsx的Excel
Workbook workbook = new SXSSFWorkbook();
// 如需生成xls的Excel，请使用下面的工作簿对象，注意后续输出时文件后缀名也需更改为xls
//Workbook workbook = new HSSFWorkbook();
// 生成Sheet表，写入第一行的列头
Sheet sheet = buildDataSheet(workbook);
//构建每行的数据内容
int rowNum = 1;
for (Iterator<DataVo> it = dataList.iterator(); it.hasNext(); ) {
DataVo data = it.next();
if (data == null) {
continue;
}
//输出行数据
Row row = sheet.createRow(rowNum++);
convertDataToRow(data, row);
}
return workbook;
}
/**
* 生成sheet表，并写入第一行数据（列头）
*
* @param workbook 工作簿对象
* @return 已经写入列头的Sheet
*/
private static Sheet buildDataSheet(Workbook workbook) {
Sheet sheet = workbook.createSheet();
// 设置列头宽度
for (int i = 0; i < CELL_HEADS.size(); i++) {
sheet.setColumnWidth(i, 4000);
}
// 设置默认行高
sheet.setDefaultRowHeight((short) 400);
// 构建头单元格样式
CellStyle cellStyle = buildHeadCellStyle(sheet.getWorkbook());
// 写入第一行各列的数据
Row head = sheet.createRow(0);
for (int i = 0; i < CELL_HEADS.size(); i++) {
Cell cell = head.createCell(i);
cell.setCellValue(CELL_HEADS.get(i));
cell.setCellStyle(cellStyle);
}
return sheet;
}
/**
* 设置第一行列头的样式
*
* @param workbook 工作簿对象
* @return 单元格样式对象
*/
private static CellStyle buildHeadCellStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
//对齐方式设置
style.setAlignment(HorizontalAlignment.CENTER);
//边框颜色和宽度设置
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框
//设置背景颜色
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//粗体字设置
Font font = workbook.createFont();
font.setBold(true);
style.setFont(font);
return style;
}
/**
* 将数据转换成行
*
* @param data 源数据
* @param row 行对象
* @return
*/
private static void convertDataToRow(DataVo data, Row row) {
int cellNum = 0;
Cell cell;
// 姓名
cell = row.createCell(cellNum++);
cell.setCellValue(null == data.getName() ? "" : data.getName());
// 年龄
cell = row.createCell(cellNum++);
if (null != data.getAge()) {
cell.setCellValue(data.getAge());
} else {
cell.setCellValue("");
}
}
}
Controller
package com.springbootemaildemo.excel.b; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import lombok.extern.slf4j.Slf4j; import org.apache.commons.collections4.CollectionUtils; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Workbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.http.HttpStatus; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.util.ArrayList; import java.util.Date; import java.util.List; @Slf4j @RestController @RequestMapping("/excel/b") @Api("导出Excel") public class ExcelFileController { private static final Logger logger =LoggerFactory.getLogger(ExcelFileController.class); @GetMapping("/exportExcel") public void exportExcel(HttpServletRequest request, HttpServletResponse response) { Workbook workbook = null; OutputStream out = null; try { // todo 根据业务需求获取需要写入Excel的数据列表 dataList List<DataVo> dataList = new ArrayList<>(); //把数据加载到dataList中即可 // 生成Excel工作簿对象并写入数据 workbook = ExcelWriter.exportData(dataList); // 写入Excel文件到前端 if (null != workbook) { String excelName = "export-01"; String fileName = excelName + System.currentTimeMillis() + ".xlsx"; fileName = new String(fileName.getBytes("UTF-8"), "iso8859-1"); response.setHeader("Content-Disposition", "attachment;filename=" + fileName); response.setContentType("application/x-download"); response.setCharacterEncoding("UTF-8"); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); response.flushBuffer(); out = response.getOutputStream(); workbook.write(out); out.flush(); } } catch (Exception e) { logger.warn("写入Excel过程出错！错误原因：" + e.getMessage()); } finally { try { if (null != workbook) { workbook.close(); } if (null != out) { out.close(); } } catch (IOException e) { logger.warn("关闭workbook或outputStream出错！"); } } } }
