我要评论java poi实现excel多级表头导出(多级表头,复杂表头)
最近碰到一个导出，比较繁琐，也查询了许多博客，在其中一篇博客的基础上修改，实现了最终想要的效果。
话不多说，直接上效果图：
上代码
1.主代码
list<map<string, object>> convertlistmap = tolistconvertlistmap(tabletitlelist, list);
// 第一步，创建一个workbook，对应一个excel文件
xssfworkbook wb = new xssfworkbook();
// 第二步，在workbook中添加一个sheet,对应excel文件中的sheet
xssfsheet sheet = wb.createsheet("sheet");
// 第三步，设置样式以及字体样式
xssfcellstyle titlestyle = createtitlecellstyle(wb);
xssfcellstyle headerstyle = createheadcellstyle(wb);
xssfcellstyle contentstyle = createcontentcellstyle(wb);
// 行号
int rownum = 0;
// 创建第一页的第一行，索引从0开始
xssfrow row0 = sheet.createrow(rownum++);
row0.setheight((short) 600);// 设置行高
sheet.setcolumnwidth(2, 8000);
sheet.setcolumnwidth(3, 8000);
//第二行
xssfrow row2 = sheet.createrow(rownum++);
row2.setheight((short) 700);
// todo: 第三行
xssfrow row3 = sheet.createrow(rownum++);
row3.setheight((short) 700);
string[] row_one = null;
string[] row_two = null;
string[] row_three = null;
int cellsize = 0;
if (tabletitlelist.size() == 2) { // 全部
row_one = new string[]{"序号", "所属模块", "单位名称", "情况统计", "合计", "", "",
"测试类型a", "", "", "", "", "", "", "", "", "", "", "",
"测试类型b", "", "", "", "", "", "", "", "", "", "", "",};
row_two = new string[]{"", "", "", "", "合计a", "合计b", "合计c",
"测试类型a1", "", "", "",
"测试类型a2", "", "", "",
"测试类型a3", "", "", "",
"测试类型b1", "", "", "",
"测试类型b2", "", "", "",
"测试类型b3", "", "", ""};
row_three = new string[]{"", "", "", "", "", "", "",
"起床", "洗漱", "吃饭", "上班",
"起床", "洗漱", "吃饭", "上班",
"起床", "洗漱", "吃饭", "上班",
"起床", "洗漱", "吃饭", "上班",
"起床", "洗漱", "吃饭", "上班",
"起床", "洗漱", "吃饭", "上班",};
// 合并单元格
extracted(sheet);
}
// 创建第一行单元格
for (int i = 0; i < row_one.length; i++) {
xssfcell c00 = row0.createcell(i);
c00.setcellvalue(row_one[i]);
c00.setcellstyle(headerstyle);
}
// 创建第二行单元格
for (int i = 0; i < row_two.length; i++) {
xssfcell tempcell = row2.createcell(i);
tempcell.setcellvalue(row_two[i]);
tempcell.setcellstyle(headerstyle);
}
// 创建第三行单元格
for (int i = 0; i < row_three.length; i++) {
xssfcell tempcell = row3.createcell(i);
tempcell.setcellvalue(row_three[i]);
tempcell.setcellstyle(headerstyle);
}
// 设置表头单元格的宽度
tabletitlestylecolumnwidth(sheet);
// todo： 填充数据
approvalmethodfillindata(convertlistmap, sheet, contentstyle, rownum, row_three, tabletitlelist);
// approvalmethodfillindata(convertlistmap, sheet, contentstyle, rownum, cellsize, tabletitlelist);
//导出到浏览器下载
buildexceldocument("监测数据.xlsx", wb, response);2.合并单元格
private static void extracted(xssfsheet sheet) {
// todo: 合并参数分别为： 起始行，结束行，起始列，结束列
// 将第一列 的 第一行到第三行合并
sheet.addmergedregion(new cellrangeaddress(0, 2, 0, 0));
// 将第二列的 第一行到第三行合并
sheet.addmergedregion(new cellrangeaddress(0, 2, 1, 1));
// 将第三列的 第一行到第三行合并
sheet.addmergedregion(new cellrangeaddress(0, 2, 2, 2));
sheet.addmergedregion(new cellrangeaddress(0, 2, 3, 3));
// 合计
sheet.addmergedregion(new cellrangeaddress(0, 0, 4, 6));
// 合计下的单元格
sheet.addmergedregion(new cellrangeaddress(1, 2, 4, 4));
sheet.addmergedregion(new cellrangeaddress(1, 2, 5, 5));
sheet.addmergedregion(new cellrangeaddress(1, 2, 6, 6));
sheet.addmergedregion(new cellrangeaddress(0, 0, 7, 18));
sheet.addmergedregion(new cellrangeaddress(0, 0, 19, 30));
sheet.addmergedregion(new cellrangeaddress(1, 1, 7, 10));
sheet.addmergedregion(new cellrangeaddress(1, 1, 11, 14));
sheet.addmergedregion(new cellrangeaddress(1, 1, 15, 18));
sheet.addmergedregion(new cellrangeaddress(1, 1, 19, 22));
sheet.addmergedregion(new cellrangeaddress(1, 1, 23, 26));
sheet.addmergedregion(new cellrangeaddress(1, 1, 27, 30));
}3.设置表头单元格的宽度
private static void tabletitlestylecolumnwidth(xssfsheet sheet) {
sheet.setcolumnwidth(2, 8000);
sheet.setcolumnwidth(3, 8000);
sheet.setcolumnwidth(7, 3000);
sheet.setcolumnwidth(8, 3000);
sheet.setcolumnwidth(9, 3000);
sheet.setcolumnwidth(10, 3000);
sheet.setcolumnwidth(11, 3000);
sheet.setcolumnwidth(12, 3000);
sheet.setcolumnwidth(13, 3000);
sheet.setcolumnwidth(14, 3000);
sheet.setcolumnwidth(15, 3000);
sheet.setcolumnwidth(16, 3000);
sheet.setcolumnwidth(17, 3000);
sheet.setcolumnwidth(18, 3000);
sheet.setcolumnwidth(19, 3000);
sheet.setcolumnwidth(20, 3000);
sheet.setcolumnwidth(21, 3000);
sheet.setcolumnwidth(22, 3000);
sheet.setcolumnwidth(23, 3000);
sheet.setcolumnwidth(24, 3000);
sheet.setcolumnwidth(25, 3000);
sheet.setcolumnwidth(26, 3000);
sheet.setcolumnwidth(27, 3000);
sheet.setcolumnwidth(28, 3000);
sheet.setcolumnwidth(29, 3000);
sheet.setcolumnwidth(30, 3000);
}4.填充数据
（注：我这里的数据格式是list<map<string, object>>类型，可以根据自己的实际情况来封装数据）
private static void approvalmethodfillindata(list<map<string, object>> convertlistmap, xssfsheet sheet, xssfcellstyle contentstyle, int rownum, string[] cellsize, list<object> tabletitlelist) {
for (map<string, object> map : convertlistmap) {
xssfrow temprow = sheet.createrow(rownum++);
temprow.setheight((short) 500);
// 判断选择类型
if (tabletitlelist.size() == 2 || "enterprise".equals(tabletitlelist.get(0).tostring())) {
// 循环单元格填入数据
for (int i = 0; i < cellsize.length; i++) {
//列宽自适应，j为自适应的列，true就是自适应，false就是不自适应，默认不自适应
sheet.autosizecolumn(i, true);
xssfcell tempcell = temprow.createcell(i);
tempcell.setcellstyle(contentstyle);
string tempvalue = "";
switch (i) {
case 0:
tempvalue = map.get("indexnum").tostring();
break;
case 1:
tempvalue = map.get("platname").tostring();
break;
case 2:
tempvalue = map.get("deptname").tostring();
break;
case 3:
tempvalue = map.get("type").tostring();
break;
case 4:
tempvalue = map.get("totalpronum").tostring();
break;
case 5:
tempvalue = map.get("totalbidnum").tostring();
break;
case 6:
tempvalue = map.get("totalamount").tostring();
break;
case 7:
tempvalue = map.get("enterpriseengineeringpronum").tostring();
break;
case 8:
tempvalue = map.get("enterpriseengineeringbidnum").tostring();
break;
case 9:
tempvalue = map.get("enterprisepurchaseamount").tostring();
break;
case 10:
tempvalue = map.get("planenterprisepurchaseamount").tostring();
break;
case 11:
tempvalue = map.get("enterprisepurchasepronum").tostring();
break;
case 12:
tempvalue = map.get("enterprisepurchasebidnum").tostring();
break;
case 13:
tempvalue = map.get("enterprisepurchaseamount").tostring();
break;
case 14:
tempvalue = map.get("planenterprisepurchaseamount").tostring();
break;
case 15:
tempvalue = map.get("enterpriseservicepronum").tostring();
break;
case 16:
tempvalue = map.get("enterpriseservicebidnum").tostring();
break;
case 17:
tempvalue = map.get("enterpriseserviceamount").tostring();
break;
case 18:
tempvalue = map.get("planenterpriseserviceamount").tostring();
break;
case 19:
tempvalue = map.get("groupengineeringpronum").tostring();
break;
case 20:
tempvalue = map.get("grouppurchasebidnum").tostring();
break;
case 21:
tempvalue = map.get("grouppurchaseamount").tostring();
break;
case 22:
tempvalue = map.get("palngrouppurchaseamount").tostring();
break;
case 23:
tempvalue = map.get("grouppurchasepronum").tostring();
break;
case 24:
tempvalue = map.get("groupengineeringbidnum").tostring();
break;
case 25:
tempvalue = map.get("groupengineeringamount").tostring();
break;
case 26:
tempvalue = map.get("plangroupengineeringamount").tostring();
break;
case 27:
tempvalue = map.get("groupservicepronum").tostring();
break;
case 28:
tempvalue = map.get("groupservicebidnum").tostring();
break;
case 29:
tempvalue = map.get("groupserviceamount").tostring();
break;
case 30:
tempvalue = map.get("plangroupserviceamount").tostring();
break;
}
tempcell.setcellvalue(tempvalue);
}
}
}5.web下载，导出到浏览器
private static void buildexceldocument(string filename, workbook wb, httpservletresponse response) {
try {
response.setcontenttype("application/octet-stream");
// 可自行定义编码格式
response.setheader("content-disposition", "attachment;filename=" + urlencoder.encode(filename, "utf-8"));
//清除jsp编译html文件的空白,防止excel出现空行
response.flushbuffer();
//写出
wb.write(response.getoutputstream());
} catch (ioexception e) {
e.printstacktrace();
} finally {
ioutils.closequietly(wb);
}
}6.提供表头样式，内容样式以及标题样式
/**
* 创建标题样式
*
* @param wb
* @return
*/
private static xssfcellstyle createtitlecellstyle(xssfworkbook wb) {
xssfcellstyle cellstyle = wb.createcellstyle();
cellstyle.setalignment(horizontalalignment.center);//水平居中
cellstyle.setverticalalignment(verticalalignment.center);//垂直对齐
cellstyle.setfillpattern(fillpatterntype.solid_foreground);
// cellstyle.setfillforegroundcolor(indexedcolors.grey_40_percent.getindex());//背景颜色
xssffont headerfont1 = (xssffont) wb.createfont(); // 创建字体样式
headerfont1.setbold(true); //字体加粗
headerfont1.setfontname("黑体"); // 设置字体类型
headerfont1.setfontheightinpoints((short) 15); // 设置字体大小
cellstyle.setfont(headerfont1); // 为标题样式设置字体样式
return cellstyle;
}
/**
* 创建表头样式
*
* @param wb
* @return
*/
private static xssfcellstyle createheadcellstyle(xssfworkbook wb) {
xssfcellstyle cellstyle = wb.createcellstyle();
cellstyle.setwraptext(true);// 设置自动换行
cellstyle.setfillforegroundcolor(indexedcolors.grey_25_percent.getindex());//背景颜色
cellstyle.setalignment(horizontalalignment.center); //水平居中
cellstyle.setverticalalignment(verticalalignment.center); //垂直对齐
cellstyle.setfillpattern(fillpatterntype.solid_foreground);
// cellstyle.setbottombordercolor(indexedcolors.black.index);
cellstyle.setborderbottom(borderstyle.thin); //下边框
cellstyle.setborderleft(borderstyle.thin); //左边框
cellstyle.setborderright(borderstyle.thin); //右边框
cellstyle.setbordertop(borderstyle.thin); //上边框
xssffont headerfont = (xssffont) wb.createfont(); // 创建字体样式
headerfont.setbold(true); //字体加粗
headerfont.setfontname("黑体"); // 设置字体类型
headerfont.setfontheightinpoints((short) 12); // 设置字体大小
cellstyle.setfont(headerfont); // 为标题样式设置字体样式
return cellstyle;
}
/**
* 创建内容样式
*
* @param wb
* @return
*/
private static xssfcellstyle createcontentcellstyle(xssfworkbook wb) {
xssfcellstyle cellstyle = wb.createcellstyle();
cellstyle.setverticalalignment(verticalalignment.center);// 垂直居中
cellstyle.setalignment(horizontalalignment.center);// 水平居中
cellstyle.setwraptext(true);// 设置自动换行
cellstyle.setborderbottom(borderstyle.thin); //下边框
cellstyle.setborderleft(borderstyle.thin); //左边框
cellstyle.setborderright(borderstyle.thin); //右边框
cellstyle.setbordertop(borderstyle.thin); //上边框
// 生成12号字体
xssffont font = wb.createfont();
font.setcolor((short) 8);
font.setfontheightinpoints((short) 12);
cellstyle.setfont(font);
return cellstyle;
}到此，大功告成！
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论