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包,下面是完整的,每个依赖的作用都有注释,用不到的可以删除。
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
4.0.0
org.springframework.boot
spring-boot-starter-parent
2.1.3.RELEASE
com.ningl
nlredis
jar
nlredis
nlredis project for Spring Boot
anxpp
UTF-8
UTF-8
1.8
8.0.11
1.1.10
28.1-jre
1.3.8.RELEASE
2.1.9
1.18.4
1.2.38
2.9.9
3.4
1.3.3
org.springframework.boot
spring-boot-starter-data-redis
redis.clients
jedis
2.9.1
org.springframework.boot
spring-boot-starter-web
org.springframework.boot
spring-boot-starter-thymeleaf
org.webjars
jquery
3.5.1
com.fasterxml.jackson.core
jackson-databind
2.9.3
com.fasterxml.jackson.core
jackson-core
2.9.3
com.fasterxml.jackson.core
jackson-annotations
2.9.3
net.sf.json-lib
json-lib
2.4
jdk15
commons-lang
commons-lang
2.6
commons-logging
commons-logging
1.2
commons-beanutils
commons-beanutils
1.9.3
commons-collections
commons-collections
3.2.1
net.sf.ezmorph
ezmorph
1.0.6
xom
xom
1.2.5
junit
junit
4.13
com.baomidou
mybatis-plus
${mybatisPlus.version}
com.baomidou
mybatisplus-spring-boot-starter
1.0.5
mysql
mysql-connector-java
${mysql.version}
com.alibaba
druid
${druid.version}
com.alibaba
druid-spring-boot-starter
1.1.10
com.google.guava
guava
${gugua.version}
org.projectlombok
lombok
${lombok.version}
provided
com.alibaba
fastjson
${fastJson.version}
joda-time
joda-time
${jodate.version}
org.apache.commons
commons-lang3
${commonsLang.version}
commons-fileupload
commons-fileupload
${fileupload.version}
org.apache.poi
poi
4.1.2
org.apache.poi
poi-ooxml-schemas
4.1.2
org.apache.poi
poi-ooxml
4.1.2
nlredis
org.springframework.boot
spring-boot-maven-plugin
repackage
com.spotify
docker-maven-plugin
1.0.0
src/main/docker
/
${project.build.directory}
${project.build.finalName}.jar
maven-antrun-plugin
package
file = "target/${project.artifactId}.${project.packaging}">
run
第一个需求,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 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 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 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 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可能出现的需求实例,只是简单实现了功能,各位小伙伴感兴趣的话,可以多加优化下~~~嘿嘿