Apache POI是基于Office Open XML标准（OOXML）和Microsoft的OLE 2复合文档格式（OLE2）处理各种文件格式的开源项目。简而言之，您可以使用Java读写MS Excel文件，可以使用Java读写MS Word和MS PowerPoint文件
导出excel涉及模块
①XSSF － 提供读写Microsoft Excel OOXML XLSX格式(Microsoft Excel XML (2007+))档案的功能。
②HSSF － 提供读写Microsoft Excel XLS格式(Microsoft Excel 97 (-2003))档案的功能。涉及到的jar包，下面是完整的，每个依赖的作用都有注释，用不到的可以删除。
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.3.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.ningl</groupId>
<artifactId>nlredis</artifactId>
<packaging>jar</packaging>
<name>nlredis</name>
<description>nlredis project for Spring Boot</description>
<properties>
<docker.image.prefix>anxpp</docker.image.prefix>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<mysql.version>8.0.11</mysql.version>
<druid.version>1.1.10</druid.version>
<gugua.version>28.1-jre</gugua.version>
<log4j.version>1.3.8.RELEASE</log4j.version>
<mybatisPlus.version>2.1.9</mybatisPlus.version>
<lombok.version>1.18.4</lombok.version>
<fastJson.version>1.2.38</fastJson.version>
<jodate.version>2.9.9</jodate.version>
<commonsLang.version>3.4</commonsLang.version>
<fileupload.version>1.3.3</fileupload.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<dependency>
<groupId>redis.clients</groupId>
<artifactId>jedis</artifactId>
<version>2.9.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.webjars</groupId>
<artifactId>jquery</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.9.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-core -->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
<version>2.9.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-annotations -->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-annotations</artifactId>
<version>2.9.3</version>
</dependency>
<dependency>
<groupId>net.sf.json-lib</groupId>
<artifactId>json-lib</artifactId>
<version>2.4</version>
<classifier>jdk15</classifier>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-lang/commons-lang -->
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>2.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-beanutils/commons-beanutils -->
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.9.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-collections/commons-collections -->
<dependency>
<groupId>commons-collections</groupId>
<artifactId>commons-collections</artifactId>
<version>3.2.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/net.sf.ezmorph/ezmorph -->
<dependency>
<groupId>net.sf.ezmorph</groupId>
<artifactId>ezmorph</artifactId>
<version>1.0.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/xom/xom -->
<dependency>
<groupId>xom</groupId>
<artifactId>xom</artifactId>
<version>1.2.5</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
</dependency>
<!--集成mybatiesplus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
<version>${mybatisPlus.version}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.baomidou/mybatisplus-spring-boot-starter -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatisplus-spring-boot-starter</artifactId>
<version>1.0.5</version>
</dependency>
<!-- MySql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<!-- 连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<!--google 工具包-->
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>${gugua.version}</version>
</dependency>
<!--日志-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>${lombok.version}</version>
<scope>provided</scope>
</dependency>
<!--json序列化-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>${fastJson.version}</version>
</dependency>
<!--日期处理-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>${jodate.version}</version>
</dependency>
<!--文件上传-->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>${commonsLang.version}</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>${fileupload.version}</version>
</dependency>
<!-- poi 相关-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
</dependencies>
<!--easyexcel-->
<!--<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>-->
<build>
<finalName>nlredis</finalName>
<plugins>
<!--SpringCloud的打成可执行的jar包插件-->
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<executions>
<execution>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
<!--docker的操作-->
<plugin>
<groupId>com.spotify</groupId>
<artifactId>docker-maven-plugin</artifactId>
<version>1.0.0</version>
<configuration>
<dockerDirectory>src/main/docker</dockerDirectory>
<resources>
<resource>
<targetPath>/</targetPath>
<directory>${project.build.directory}</directory>
<include>${project.build.finalName}.jar</include>
</resource>
</resources>
</configuration>
</plugin>
<!--docker需要的jar的复制操作-->
<plugin>
<artifactId>maven-antrun-plugin</artifactId>
<executions>
<execution>
<phase>package</phase>
<configuration>
<tasks>
<copy todir = "src/main/docker"
file = "target/${project.artifactId}.${project.packaging}"></copy>
</tasks>
</configuration>
<goals>
<goal>run</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
- 第一个需求，get请求生成一个简单的excel文件，并且按照顺序为单元格赋值
public static void createExcelLevel0(HttpServletResponse response, JSONObject json) throws IOException {
setExcelResponse(response);
try(OutputStream os = response.getOutputStream();
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();) {
//创建新的excel,07 之后XSSF,之前HSSF
Workbook xssfWork = new XSSFWorkbook();
//创建一个工作簿
Sheet sheet = xssfWork.createSheet("sheet1");
//创建3行
for (int i = 0; i < 3; i++) {
Row row = sheet.createRow(i);
//创建4列
for (int j = 0; j < 4; j++) {
Cell cell = row.createCell(j);
//单元格赋值
cell.setCellValue("第"+(i+1)+"行"+(j+1)+"列");
}
}
//ByteArrayOutputStream的输出目标是一个byte数组，这个数组的长度是根据数据内容动态扩展的
xssfWork.write(outputStream);
//转给response.getOutputStream()
outputStream.writeTo(os);
//输出流文件
os.flush();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @discription excel header信息添加
*/
private static void setExcelResponse(HttpServletResponse response) {
response.setHeader("content-disposition", "attachment; filename=mytest.xlsx");
response.setCharacterEncoding("utf-8");
response.setContentType("application/msexcel");
}
- 第二步增加点难度，给指定单元格写内容、合并单元格、给单元格设置样式、格式、给文档设置作者、描述等信息
/**
* @discription 创建excel,并设置格式
*/
public static void createExcelLevel1(HttpServletResponse response, JSONObject jsonObject) {
setExcelResponse(response);
try(OutputStream os = response.getOutputStream();
ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
XSSFWorkbook workbook = new XSSFWorkbook();
addExcelInfo(workbook);
Sheet sheet = workbook.createSheet("基金仓位控制表");
Row row = sheet.createRow(8);
//创建标题title
//合并单元格firstRow 区域中第一个单元格的行号,lastRow 区域中最后一个单元格的行号
// firstCol 区域中第一个单元格的列号,lastCol 区域中最后一个单元格的列号
Cell cell = row.createCell(7);
CellRangeAddress cellRangeAddress = new CellRangeAddress(8, 9, 7, 11);
sheet.addMergedRegion(cellRangeAddress);
cell.setCellValue("基金仓位控制表");
cell.setCellStyle(cellStyle(workbook));
//创建表头信息
List<String> headerList = initHeader();
Row headRow = sheet.createRow(11);
int rowtHeader = 11;
int colHeader = 5;
for (int i = 0; i < headerList.size() ; i++) {
Cell headCell = headRow.createCell(colHeader);
CellRangeAddress headRegion = new CellRangeAddress(rowtHeader, rowtHeader+1, colHeader,colHeader+1);
colHeader = colHeader+2;
sheet.addMergedRegion(headRegion);
headCell.setCellValue(headerList.get(i));
}
//数据赋值
int rowData = 13;
JSONArray dataArr = initJsonArry();
for (int i = 0; i < dataArr.size(); i++) {
//创建行
Row dataRow = sheet.createRow(rowData);
JSONObject jos = dataArr.getJSONObject(i);
Set<String> keySet = jos.keySet();
int colData = 5;
for (String key: keySet) {
CellRangeAddress dataRegion = new CellRangeAddress(rowData, rowData+1,
colData,colData+1);
//添加合并区域
sheet.addMergedRegion(dataRegion);
//创建列
Cell dataCell = dataRow.createCell(colData);
//合并单元格列 自增
colData = colData+2;
//临时判断日期类型
if(key.indexOf("-") > -1){
dataCell.setCellValue(Date8Util.strToDate(jos.getString(key)));
}else{
dataCell.setCellValue(jos.getString(key));
}
}
//行自增
rowData = rowData+2;
}
//保存工作表
workbook.write(baos);
baos.writeTo(os);
os.flush();
}catch (Exception e){
}
}
private static void addExcelInfo(XSSFWorkbook workbook){
//创建文档属性信息
POIXMLProperties poiDocument = workbook.getProperties();
POIXMLProperties.CoreProperties properties = poiDocument.getCoreProperties();
properties.setCreated("2020-09-01");
//备注
properties.setDescription("about my funds control");
//标题
properties.setTitle("about my funds control");
//创建者
properties.setCreator("ngLee");
properties.setRevision("revion");
//文档属性自定义信息
POIXMLProperties.CustomProperties customProperties = poiDocument.getCustomProperties();
customProperties.addProperty("description", "描述信息");
}
/**
* @discription 边框
*/
private static CellStyle cellStyle(XSSFWorkbook workbook) {
XSSFCellStyle style=workbook.createCellStyle();
style.setBorderBottom(BorderStyle.THICK);//下边框
style.setBottomBorderColor(new XSSFColor(Color.BLUE));//下边框颜色
//水平居中
style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
//设置字体
XSSFFont xssfFont = workbook.createFont();
//加粗
xssfFont.setBold(true);
//蓝色
xssfFont.setColor(XSSFColor.toXSSFColor((org.apache.poi.ss.usermodel.Color) Color.BLUE));
//字体大小
xssfFont.setFontHeight(22.0d);
style.setFont(xssfFont);
return style;
}
/**
* @discription header
*/
private static List<String> initHeader() {
List list = new ArrayList();
list.add("所属板块");
list.add("基金名称");
list.add("基金代码");
list.add("总投入");
list.add("操作日期");
return list;
}
/**
* @discription 创建数据
*/
private static JSONArray initJsonArry() {
JSONArray jsonArray = new JSONArray();
for (int i = 0; i < 4; i++) {
JSONObject jsonObject = initJson(i);
jsonArray.add(jsonObject);
}
return jsonArray;
}
/**
* @discription 创建数据
*/
private static JSONObject initJson(int i) {
JSONObject json = new JSONObject();
String bkmc = "";
String fundName = "";
String code = "000000";
String date = Date8Util.nowDate();
String ztrje = "20000.00";
switch (i){
case 0:bkmc = "芯片半导体";
fundName = "诺安成长混合";
code = "320007";break;
case 1:bkmc = "消费";
fundName = "富国消费主题混合";
code = "519915";break;
case 2:bkmc = "科技";
fundName = "华宝科技ETF";
code = "007874";break;
case 3:bkmc="医药医疗";
fundName = "中欧医疗创新股票";
code="006229";break;
default: break;
}
if(!Strings.isNullOrEmpty(bkmc)){
json.put("bkmc",bkmc);
json.put("fundName",fundName);
json.put("code",code);
json.put("ztrje",ztrje);
json.put("now", date);
}
return json;
}
- 继续增加难度，根据一个excel模版，将数据内容按照定义名称写到excel，例如我的模板如下，然后我选中motto、author、time 单元格定义名称为usser（对象），选中order、sshy、fundsName、ytrbj、dtrbj、ljsy定义名称为funds_kz(备注_kz标识为是扩展行，可能有多条数据)。查看所有定义名称快捷键是ctrl+f3
/**
* @discription 读取模版内容信息
* @author: ngLee
* @date: 2020/9/6
* @param: [response, jsonObject]
* @return: void
**/
public static void createExcelLevel2(HttpServletResponse response, JSONObject jsonObject) {
setExcelResponse(response);
String path = "E:\\excelMB\\demo.xlsx";
File file = new File(path);
JSONObject object = initJsonLevel2();
try(OutputStream os = response.getOutputStream();
ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
XSSFWorkbook workbook = new XSSFWorkbook(file);
addExcelInfo(workbook);
//获取所有名称
List<XSSFName> nameList = workbook.getAllNames();
for (int i = 0; i < nameList.size(); i++) {
Name name = nameList.get(i);
//自定义名称
String custName = name.getNameName();
//获取关联单元格
String aboutCell = name.getRefersToFormula();
System.out.println(aboutCell);
//userInfo--基金仓位控制!$M$9:$N$9,基金仓位控制!$M$10:$N$10
//funds_kz--基金仓位控制!$E$14:$O$15
if(custName.indexOf("_kz") < 0){
//非连续的命名范围
AreaReference[] arefs = AreaReference.generateContiguous(SpreadsheetVersion.EXCEL2007,name.getRefersToFormula());
for (int j = 0; j < arefs.length; j++) {
CellReference[] crefs = arefs[j].getAllReferencedCells();
for (int k = 0; k < crefs.length; k++) {
//获取所在sheet
Sheet sheet = workbook.getSheet(crefs[k].getSheetName());
//获取行
Row row = sheet.getRow(crefs[k].getRow());
//获取单元格
Cell cell = row.getCell(crefs[k].getCol());
//模版里的值作为key
String value = cell.getStringCellValue();
if(!Strings.isNullOrEmpty(value)){
JSONObject obj = object.getJSONObject(custName);
cell.setCellValue(obj.getString(value));
}
}
}
}else {//多条数据扩展行
AreaReference aref = new AreaReference(name.getRefersToFormula(),SpreadsheetVersion.EXCEL2007);
JSONArray kzList = object.getJSONArray(custName);
CellReference[] crefs = aref.getAllReferencedCells();
int rowNum = crefs[0].getRow();
int kzListLen = kzList.size();
for (int j = 0; j < kzListLen; j++) {
//拷贝当前行到下一行
JSONObject obj = kzList.getJSONObject(j);
Sheet sheet = workbook.getSheet(crefs[0].getSheetName());
Row sourceRow = sheet.getRow(rowNum);
rowNum = rowNum+2;
Row distRow = sheet.createRow(rowNum);
copyCustRow(workbook,sheet,sourceRow,distRow,j+1 == kzListLen ,obj);
}
}
}
workbook.write(baos);
baos.writeTo(os);
os.flush();
}catch (Exception e){
}
}
/**
* @discription 复制行
* @author: ngLee
* @date: 2020/9/6
* @param: [workbook, sourceRow, distRow, b]
* @return: void
**/
private static void copyCustRow(XSSFWorkbook workbook,Sheet sheet, Row sourceRow, Row distRow, boolean b,JSONObject obj) {
distRow.setHeight(sourceRow.getHeight());
//复制单元格的内容
for(int i=0; i < sourceRow.getLastCellNum(); i++){
//单元格样式
CellStyle distStyle = workbook.createCellStyle();
Cell sourceCell = sourceRow.getCell(i);
Cell distCell = distRow.createCell(i);
Cell distCell2 = distRow.createCell(i+1);
if(sourceCell != null ){
CellStyle sourceStyle = sourceCell.getCellStyle();
sourceStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
distStyle.cloneStyleFrom(sourceStyle);
if(!b){
distCell.setCellStyle(distStyle);
distCell2.setCellStyle(distStyle);
}
//复制内容
if(!Strings.isNullOrEmpty(sourceCell.getStringCellValue())){
//目标行复制key
if(b == false){
distCell.setCellValue(sourceCell.getStringCellValue());
}
//源赋值
sourceCell.setCellValue(obj.getString(sourceCell.getStringCellValue()));
}
}
}
//复制合并的单元格
for(int i=0; i < sheet.getNumMergedRegions(); i++){
CellRangeAddress sourceRange = sheet.getMergedRegion(i);
int rowCnt = sourceRange.getLastRow()-sourceRange.getFirstRow();
if(sourceRange.getFirstRow() == sourceRow.getRowNum()){
CellRangeAddress distRange = new CellRangeAddress(distRow.getRowNum(),
distRow.getRowNum()+rowCnt,
sourceRange.getFirstColumn(), sourceRange.getLastColumn());
if(!b){
RegionUtil.setBorderLeft(BorderStyle.THIN, distRange, sheet); // 左边框
RegionUtil.setBorderRight(BorderStyle.THIN, distRange, sheet); // 有边框
RegionUtil.setBorderTop(BorderStyle.THIN, distRange, sheet); // 上边框
RegionUtil.setBorderBottom(BorderStyle.THIN,distRange,sheet);
sheet.addMergedRegion(distRange);
}
}
}
}
private static JSONObject initJsonLevel2() {
JSONObject json = new JSONObject();
JSONObject usser = new JSONObject();
usser.put("motto", "让利润奔跑");
usser.put("author", "ngLee");
usser.put("time", "2020-09-06");
json.put("usser", usser);
JSONArray fundArr = new JSONArray();
for (int i = 0; i < 4 ; i++) {
JSONObject jsonObject = new JSONObject();
jsonObject.put("order", i+1);
jsonObject.put("sshy", i%2 == 0 ? "消费":"科技");
jsonObject.put("fundsName","华宝券商ETF");
jsonObject.put("ytrbj", "20000");
jsonObject.put("dtrbj", "10000");
jsonObject.put("ljsy", "1000");
fundArr.add(jsonObject);
}
json.put("funds_kz", fundArr);
return json;
}
- 导入的包展示
import com.google.common.base.Strings;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.ooxml.POIXMLProperties;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
public class ExportUtil｛
.......方法同上
｝
- controller层很简单，需要注意下，设置返回值类型为void,否则会出现下面这个问题
java.lang.IllegalStateException: getOutputStream() has already been called for this response
@GetMapping("/exportExcel")
public void exportExcel(HttpServletResponse response, HttpServletRequest request) throws IOException {
JSONObject jsonObject = new JSONObject();
//下载excel 1.1 入门
//ExportUtil.createExcelLevel0(response,jsonObject);
//设置单元格格式 1.2 合并单元格
//ExportUtil.createExcelLevel1(response,jsonObject);
//读取模版的定义名称 1.3
ExportUtil.createExcelLevel2(response,jsonObject);
}
结语：以上就是关于POI导出excel可能出现的需求实例，只是简单实现了功能，各位小伙伴感兴趣的话，可以多加优化下~~~嘿嘿