利用POI生成EXCEL文件的方法实例-eolink官网

利用POI生成EXCEL文件的方法实例

一、背景

Apache POI 是创建和维护操作各种符合Office Open XML（OOXML）标准和微软的OLE 2复合文档格式（OLE2）的java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案（适用于Excel97-2008）。

根据指定格式的jsON文件生成对应的excel文件，需求如下

支持多sheet

支持单元格合并

支持插入图片

支持单元格样式可定制

需要标题(title)，表头(head)，数据(data) ，表尾(foot) 明确区分

二、效果预览

三、数据格式

由于是生成Excel文件，这里值考虑生成xlsx格式的Excel文件，数据多表头默认考虑使用 | 表示，不在使用colspan rowspan作为。如需要表示两列两行，第一列合并表头格式为: A|B,A|C生成的表格为

前端通过post的方式将需要生成的数据构造成符合要求的JSON文件提交跟后台。根据以上需求定义JSON格式如下

{

"saveName": "生成Excel的文件名.xlsx",

"userStyles": [{

"id": "1", //不能出现重复，在需要设置单元样式的地方，可以直接将style赋值为此值

"style": {

"font": { //设置字体基本格式

"blod": true,//是否加粗

"italic": true, //是否倾斜

"color": "#FF0000",//字体颜色

"name": "微软雅黑", //字体名称

"height": 20 //大小

"fmtStr": "", //单元格格式，#,##0.00_);#,##0.00;0 千分位

"align": "",//水平对齐方式 left right center

"valign": "",//垂直对齐方式 top center bottom

"borderColor": "", //设置边框颜色如 #FF0000

"bgColor": "" //设置单元格填充颜色

}

}],

"sheets": [{

"sheetName": "", //sheet名称

"title": [], // 对应Sheet标题区域数据

"titleMerge": [], //对应Sheet标题区域合并信息

"head": [{}], //表头信息

"data": [], //数据信息

"dataMerge": [], //数据合并信息

"foot": [], //表尾信息

"footMerge": [], //表尾合并信息

"img": [] //图片信息，需要将图片转换base64

}]

}

简要说明

head 数组中为JSON对象格式为

{

"name": "A|B", //表头名称，多表头用|分割

"type": "str", //此列数据类型 str num ,在excel中日期也是数字类型，通过fmtStr,显示为日期格式

"field": "F_FIELD1", //备用字段，可不用

"style": { //此列数据为列默认样式，可以是Style对象，也可以是在userStyles中定义的id值

"align": "center"

}

在数组 title data foot 中，列表中的数据，可以是一个单独的值如 1,”a”，也可以是一个对象，当为对象时，格式为

{

"value": "", //单元格具体的值

"type": "", //单元格类型，默认str

"style": {} //单元格样式可以是Style对象，也可以是在userStyles中定义的id值，如果没设置，默认取head总此列对应的style

}

titleMerge、dataMerge、footMerge数组值为逗号分隔的字符串，其含义为 "开始行，结束行，开始列，结束列"，索引从0开始。如在title中有两行三列数据，现在需要合并一行两列数据对应的值为"0，0，0，1"

img数组中值为对象，格式

{

"col": 1, //图片开始列

"row": 0, //开始行

"colSpan": 1,//列跨度，最小值1

"rowSpan": 2, //行跨度，最小值1

"data": "" //base64图片数据如: "data:image/png;base64,iVBO...ggg=="

}

四、关键实现

07以后的Excle文件，其实是一个压缩包，里边是一个个的xml文件，其中每一个sheet是一个xml文件，样式是一个xml文件，图片是对应的图片文件，放在media文件夹中，所以，代码思路依次为

构建 XSSFWorkbook 对象

生成样式

依次生成，title head data foot 行数据

依次处理合并信息 titlemerge datamerge footmerge

添加图片信息

输出文件流

功能入口如下

@Override

public void buildOutputStream() throws FileProducerException {

// 处理传入的JSON数据

sheets = this.jsonData.getJSONArray(this.SHEETS);

Iterator

if (sheets.isEmpty()) {

this.responseData.setErrcode(1001);

this.responseData.setSuccess(false);

this.responseData.setErrmsg("无数据可生成");

throw new FileProducerException();

}

wb = new XSSFWorkbook();

// 建立全局格式

JSONArray userStyles = this.jsonData.getJSONArray(this.USERSTYLES);

this.initUserStyles(userStyles);

this.initDefaultHeadStyle();

XSSFSheet ws;

JSONObject sheet;

JSONArray sheetData;

JSONArray sheetTitle;

JSONArray sheetHead;

JSONArray sheetFoot;

JSONArray sheetImgs;

String sheetName;

int sheetIndex = 0;

while (sheetIter.hasNext()) {

sheet = (JSONObject) sheetIter.next();

// 获取sheet名称

sheetName = sheet.getString(this.SHEET_NAME);

ws = wb.createSheet();

if (StringUtils.isNotBlank(sheetName)) {

wb.setSheetName(sheetIndex, sheetName);

}

int sheetRowIndex = 0;

sheetTitle = sheet.getJSONArray(this.SHEET_TITLE);

this.setMergeCells(ws, sheet.getJSONArray(this.SHEET_TITLE_MERGE),

sheetRowIndex);

sheetRowIndex = this.createRandom(ws, sheetTitle, sheetRowIndex);

sheetHead = sheet.getJSONArray(this.SHEET_HEAD);

sheetRowIndex = this.createHeadColumn(ws, sheetHead, sheetRowIndex);

this.setMergeCells(ws, sheet.getJSONArray(this.SHEET_DATA_MERGE),

sheetRowIndex);

sheetData = sheet.getJSONArray(this.SHEET_DATA);

sheetRowIndex = this.creqppLkateData(ws, sheetData, sheetRowIndex);

sheetFoot = sheet.getJSONArray(this.SHEET_FOOT);

this.setMergeCells(ws, sheet.getJSONArray(this.SHEET_FOOT_MERGE),

sheetRowIndex);

sheetRowIndex = this.createRandom(ws, sheetFoot, sheetRowIndex);

sheetImgs = sheet.getJSONArray(this.SHEET_IMG);

this.setSheetImages(ws, sheetImgs);

}

// 返回输出流

try {

ByteArrayOutputStream os = new ByteArrayOutputStream();

wb.write(os);

this.outStreams.add(os);

} catch (IOException e) {

throw new FileProducerException(e.getMessage(), e.getCause());

}

生成单元格样式对象，包括字体边框背景对齐方式

private XSSFCellStyle createCellStyle(JSONObject style) {

XSSFCellStyle cellStyle = wb.createCellStyle();

// 设置字体

JSONObject font = style.getJSONObject(this.STYLE_FONT);

Font excelFont = this.createFont(font);

if (excelFont != null) {

cellStyle.setFont(excelFont);

}

// border统一黑色

cellStyle.setBorderBottom(BorderStyle.THIN);

cellStyle.setBorderTop(BorderStyle.THIN);

cellStyle.setBorderLeft(BorderStyle.THIN);

cellStyle.setBorderRight(BorderStyle.THIN);

String borderColor = style.getString(this.BORDER_COLOR);

if (StringUtils.isNotBlank(borderColor)) {

XSSFColor xfBorderColor = new XSSFColor(new Color(Integer.parseInt(

borderColor.substring(1), 16)));

cellStyle.setBorderColor(BorderSide.BOTTOM, xfBorderColor);

cellStyle.setBorderColor(BorderSide.TOP, xfBorderColor);

cellStyle.setBorderColor(BorderSide.LEFT, xfBorderColor);

cellStyle.setBorderColor(BorderSide.RIGHT, xfBorderColor);

}

// 背景色

String bgColor = style.getString(this.BACKGROUND_COLOR);

if (StringUtils.isNotBlank(bgColor)) {

XSSFColor cellBgColor = new XSSFColor(new Color(Integer.parseInt(

bgColor.substring(1), 16)));

cellStyle.setFillForegroundColor(cellBgColor);

cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

}

// 对齐方式

String hAlignment = style.getString(this.HALIGNMENT);

if (StringUtils.isNotBlank(hAlignment))

cellStyle.setAlignment(HorizontalAlignment.valueOf(hAlignment

.toUpperCase()));

String vAlignment = style.getString(this.VALIGNMENT);

if (StringUtils.isNotBlank(vAlignment))

cellStyle.setVerticalAlignment(VerticalAlignment.valueOf(vAlignment

.toUpperCase()));

// 自动换行TRUE

cellStyle.setWrapText(true);

// 格式

String fmt = style.getString(this.FMTSTRING);

if (StringUtils.isNotBlank(fmt))

cellStyle.setDataFormat(wb.createDataFormat().getFormat(fmt));

return cellStyle;

}

创建字体样式

private Font createFont(JSONObject fontCfg) {

if (fontCfg == null)

return null;

XSSFFont font = wb.createFont();

font.setFontName(fontCfg.getString(this.FONT_NAME));

Boolean fontBoole = fontCfg.getBoolean(FONT_BLOD);

if (fontBoole != null)

font.setBold(fontBoole.booleanValue());

fontBoole = fontCfg.getBoolean(this.FONT_ITALIC);

if (fontBoole != null)

font.setItalic(fontBqppLkoole.booleanValue());

fontBoole = fontCfg.getBoolean(this.FONT_UNDERLINE);

if (fontBoole != null && fontBoole.booleanValue() == true)

font.setUnderline(FontUnderline.SINGLE.getByteValue());

Short fontHeight = fontCfg.getShort(this.FONT_HEIGHT);

if (fontHeight != null)

font.setFontHeightInPoints(fontHeight);

String colorStr = fontCfg.getString(this.FONT_COLOR);

if (colorStr != null) {

font.setColor(new XSSFColor(new Color(Integer.parseInt(

colorStr.substring(1), 16))));

}

return font;

}

处理表头,表过多表头处理，采用 | 分割的方式，传入head长度为列数据，name中有几个 | 就知道表头有几行。所以针对表头处理有以下几个步骤

生成默认列样式

填充所有列数据，求出最大行数

横向合并内容相同的单元

纵向合并空白的单元格

private int createHeadColumn(XSSFSheet ws, JSONArray sheetHead,

int sheetRowIndex) {

if (sheetHead == null)

return sheetRowIndex;

Iterator

JSONObject curHead = null;

int colIndex = 0;

Object colStyle = null;

int colSize = sheetHead.size();

headTypes = new String[colSize];

headCellStyleKeys = new String[colSize];

int[] headColLevel = new int[colSize];

String colName = null;

String[] colNameAry = null;

int maxLevel = 0;

int colLevel = 0;

XSSFCell headCell = null;

ArrayList> headValueList = new ArrayList>();

while (headIter.hasNext()) {

curHead = (JSONObject) headIter.next();

// 处理默认样式

if (curHead.containsKey(this.COLUMN_STYLE)) {

colStyle = curHead.get(this.COLUMN_STYLE);

if (colStyle instanceof JSONObject) {

headCellStyleKeys[colIndex] = this.COLUMNSTYLE_PREV

+ colIndex;

this.userStyles.put(headCellStyleKeys[colIndex],

this.createCellStyle((JSONObject) colStyle));

} else if (this.userStyles.containsKey(colStyle)) {

headCellStyleKeys[colIndex] = (String) colStyle;

}

// 处理默认列宽

if (curHead.containsKey(this.COLUMN_WIDTH)) {

ws.setDefaultColumnWidth(pixToExcelWdith(curHead

.getIntValue(this.COLUMN_WIDTH)));

}

// 保存列样式

if (curHead.containsKey(this.COLUMN_TYPE)) {

headTypes[colIndex] = curHead.getString(this.COLUMN_TYPE);

} else {

headTypes[colIndex] = this.CELLTYPESTRING;

}

// 处理多表头

colName = curHead.getString(this.COLUMN_NAME);

colNameAry = colName.split("\\|");

colLevel = colNameAry.length;

headColLevel[colIndex] = colLevel;

if (colLevel > maxLevel) {

maxLevel = colLevel;

}

for (int i = 0; i < colLevel; i++) {

if (headValueList.size() <= i) {

headValueList.add(new ArrayList());

}

headValueList.get(i).add(colIndex, colNameAry[i]);

XSSFRow row = ws.getRow(sheetRowIndex + i);

if (row == null) {

row = ws.createRow(sheetRowIndex + i);

}

headCell = row.createCell(colIndex);

headCell.setCellValue(colNameAry[i]);

headCell.setCellStyle(this.userStyles.get(this.HEADSTYLE_KEY));

}

colIndex++;

}

// 横向合并

Iterator> a = headValueList.iterator();

JSONArray headMerge = new JSONArray();

String prev = "";

String curent = null;

int lRowIndex = 0;

int startCol = 0;

int mergeCol = 0;

ArrayList columnInfo = null;

while (a.hasNext()) {

startCol = 0;

mergeCol = 0;

prev = "";

columnInfo = a.next();

// 第三列才能知道，第一列和第二列是否合并

columnInfo.add("");

Iterator b = columnInfo.iterator();

XSSFCell lastRowCell = null;

while (b.hasNext()) {

curent = b.next();

if (lRowIndex > 0) {

lastRowCell = ws.getRow(sheetRowIndex + lRowIndex - 1)

.getCell(startCol);

}

if (prev.equalsIgnoreCase(curent) && lRowIndex == 0) {

ws.getRow(sheetRowIndex + lRowIndex).getCell(startCol)

.setCellType(Cell.CELL_TYPE_BLANK);

mergeCol++;

} else if (prev.equalsIgnoreCase(curent)

&& lRowIndex > 0

&& StringUtils

.isBlank(lastRowCell.getStringCellValue())) {

ws.getRow(sheetRowIndex + lRowIndex).getCell(startCol)

.setCellType(Cell.CELL_TYPE_BLANK);

mergeCol++;

} else {

if (mergeCol > 0 && startCol > 0) {

headMerge.add(String.format("%d,%d,%d,%d", lRowIndex,

lRowIndex, startCol - mergeCol - 1,

startCol - 1));

mergeCol = 0;

}

startCol++;

prev = curent;

}

lRowIndex++;

}

for (int i = 0; i < colSize; i++) {

if (headColLevel[i] < maxLevel) { // 存在列合并

headMerge.add(String.format("%d,%d,%d,%d", headColLevel[i] - 1,

maxLevel - 1, i, i));

for (int r = headColLevel[i]; r < maxLevel; r++) {

ws.ghttp://etRow(sheetRowIndex + r)

.createCell(i)

.setCellStyle(

this.userStyles.get(this.HEADSTYLE_KEY));

}

this.setMergeCells(ws, headMerge, sheetRowIndex);

return sheetRowIndex + maxLevel;

}

添加图片,默认采用单元格描点方式，将图片固定指定的单元格区域内

private void addImg(XSSFSheet ws, JSONObject img, XSSFCreationHelper cHelper) {

String imgBase64 = img.getString(this.SHEET_IMG_DATA);

if (StringUtils.isBlank(imgBase64))

return;

String[] imgary = imgBase64.split(",");

System.out.println(imgary[0]);

byte[] imgByte = Base64.decodeBase64(imgary[1]);

int imgIdx = wb.addPicture(imgByte, Workbook.PICTURE_TYPE_JPEG);

XSSFDrawing drawImg = ws.createDrawingPatriarch();

XSSFClientAnchor anchor = cHelper.createClientAnchor();

int col = img.getIntValue(this.SHEET_IMG_COL);

int row = img.getIntValue(this.SHEET_IMG_ROW);

anchor.setCol1(col);

anchor.setRow1(row);

XSSFPicture pict = drawImg.createPicture(anchor, imgIdx);

Integer colSpan = img.getInteger(this.SHEET_IMG_COLSPAN);

if (colSpan == null)

colSpan = 1;

Integer rowSpan = img.getInteger(this.SHEET_IMG_ROWSPAN);

if (rowSpan == null)

rowSpan = 1;

pict.resize(colSpan, rowSpan);

}

五、总结

这次通过传入JSON对象生成样式丰富的excel文件，对于POI操作office文档又更加熟悉一些。相对于解析excel文档，生成就不用考虑文件格式，如：兼容2003格式，考虑大文件sax方式解析。相对于js前端生成excel文件，增加了对生成后文件二次加工的可能性，所以在功能入口中，采用了生成二进制流的方式。文件生成好后，可以继续发送邮件，上传ftp等操作。

重点说明

对于各数据区域数据，保持区域数据独立性（数据索引值）

对于图片开始行和开始列，索引值是针对一个完整的sheet

对于表头区域，多表头采用 | 分割，减少部分传输数据

excel中style为所有sheet共享样式。

好了，以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对我们的支持。

Flask接口签名sign原理与实例代码浅析

341 2023-01-27

利用POI生成EXCEL文件的方法实例

Flask接口签名sign原理与实例代码浅析

java中的接口是类吗

vue项目接口域名动态的获取方法

推荐文章

接口调用是什么意思？几种常用接口调用方式

接口设计原则

8款在线 API 接口文档管理工具

api管理系统是什么？

什么是接口调试？接口调试的步骤有哪些？

api 接口管理系统有哪些？

接口测试有几种测试方法

API文档生成工具有哪些？

微服务和api网关区别

交换机配置步骤

最近发表

热评文章

在线接口文档管理工具推荐，支持在线测试，HTTP接口

开源的在线接口文档wiki工具Mindoc的介绍与使

如何优雅的进行接口设计？接口设计的六大原则是什么？

什么是API测试,api检测公司

软件接口设计怎么做？前后端分离软件接口设计思路

接口管理平台推荐，几大接口管理平台总有一款适合你！