利用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生成的表格为

a
b	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<object> sheetiter = 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.createdata(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(fontboole.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<object> headiter = sheethead.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<arraylist<string>> headvaluelist = new arraylist<arraylist<string>>();
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<string>());
}
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<arraylist<string>> a = headvaluelist.iterator();
jsonarray headmerge = new jsonarray();
string prev = "";
string curent = null;
int lrowindex = 0;
int startcol = 0;
int mergecol = 0;
arraylist<string> columninfo = null;
while (a.hasnext()) {
startcol = 0;
mergecol = 0;
prev = "";
columninfo = a.next();
// 第三列才能知道，第一列和第二列是否合并
columninfo.add("");
iterator<string> 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.getrow(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共享样式。

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