我要评论java导出excel(非模板)可导出多个sheet
当初为了写导出也是废了不少劲，今天突然想起来就将此方法上传，之前也写过模板导出所以这个是非模板的。
方法
- 1.导出excel（单标题，单sheet）
- 2.含多个sheet 每个sheet中的标题一致
- 3.含多个sheet 每个sheet中的标题不一致
- 并且还有两个其他导出格式。
注意事项
将pagedata换成map即可！！！
<!--依赖-->
<dependency>
<groupid>javax.servlet</groupid>
<artifactid>javax.servlet-api</artifactid>
<version>3.0.1</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupid>org.apache.poi</groupid>
<artifactid>poi</artifactid>
<version>3.17</version>
</dependency>
<dependency>
<groupid>org.apache.poi</groupid>
<artifactid>poi-ooxml</artifactid>
<version>3.17</version>
</dependency>
<dependency>
<groupid> org.apache.httpcomponents </groupid>
<artifactid> httpclient </artifactid>
<version>4.5.9</version>
</dependency>
<!--json-lib -->
<dependency>
<groupid>net.sf.json-lib</groupid>
<artifactid>json-lib</artifactid>
<version>2.4</version>
<classifier>jdk15</classifier>
</dependency>
<dependency>
<groupid>net.sf.jxls</groupid>
<artifactid>jxls-core</artifactid>
<version>1.0-rc-1</version>
</dependency>
import net.sf.jxls.transformer.xlstransformer;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.cellrangeaddress;
import org.apache.poi.xssf.usermodel.xssfcellstyle;
import org.apache.poi.xssf.usermodel.xssfworkbook;
import javax.servlet.http.httpservletrequest;
import javax.servlet.http.httpservletresponse;
import java.io.*;
import java.net.urlencoder;
import java.text.simpledateformat;
import java.util.*;
/**
* @auther: sjj
* @date: 2019/5/29 16:50
* @classname: excelutil
* @description: 导出excel
*/
public class excelutil {
/**
* 导出excel
* @param pds 需要导出的数据
* @param pd 导出excel公用数据，需包含title（标题）、headers（逗号隔开，为导出表头），filename(文件名，只须写上名字，这里会进行处理拼接)
* @param request
* @param response
* @return
*/
public static void exportdata(list<linkedhashmap<object,object>> pds, pagedata pd, httpservletrequest request, httpservletresponse response) {
if(pds.size()<1){
// return statuscode.is_null;
return;
}
try {
hssfworkbook workbook = new hssfworkbook();
//设置标题
hssfsheet sheet = workbook.createsheet(pd.getstring("title"));
//headers表示excel表中第一行的表头
string[] headers=pd.getstring("headers").split(",");
//表头字体
font headerfont = workbook.createfont();
headerfont.setfontname("微软雅黑");
headerfont.setcolor(hssffont.color_normal);
headerfont.setbold(true);
//创建单元格，并设置值表头 设置表头居中
hssfcellstyle stylemain = workbook.createcellstyle();
//水平居中
stylemain.setalignment(horizontalalignment.center);
stylemain.setfont(headerfont);
//导出样式
hssfcellstyle cellstyle_c = workbook.createcellstyle();
// 自动换行
cellstyle_c.setwraptext(true);
cellstyle_c.setalignment(horizontalalignment.center);
//创建标题行
hssfrow row = sheet.createrow(0);
//在excel表中添加表头
for(int i=0;i<headers.length;i++){
hssfcell cell = row.createcell(i);
hssfrichtextstring text = new hssfrichtextstring(headers[i]);
cell.setcellvalue(text);
cell.setcellstyle(stylemain);
}
int rownum = 1;
//遍历集合数据
for(linkedhashmap<object,object> p :pds){
//依次创建行
hssfrow row1 = sheet.createrow(rownum);
int i=0;
//遍历数据
for (object key: p.keyset()){
//创建行中列 并放入数据
hssfcell cell = row1.createcell(i);
string value=string.valueof(p.get(key));
cell.setcellvalue(value!=null?value:"");
if("null".equals(value)){
cell.setcellvalue("");
}
cell.setcellstyle(cellstyle_c);
i++;
}
row1.setheight((short) (26*10));
rownum++;
}
//自动调整列宽
for (int i = 0;i <headers.length;i++){
if(i==headers.length-2){
sheet.setcolumnwidth(i, 20 * 256);
break;
}
if(i==3){
sheet.setcolumnwidth(i, 20 * 256);
continue;
}
sheet.autosizecolumn(i, true);
}
//取得输出流
outputstream out = response.getoutputstream();
simpledateformat dateformat = new simpledateformat("yyyy年mm月dd日hh时mm分ss秒");
//清空缓存
response.reset();
//设置相应内容的编码格式
response.setcharacterencoding("utf-8");
//1.导出名称
string filename =pd.getstring("filename")+dateformat.format(new date());
//文件名乱码
//获得浏览器信息并转换为大写
string agent = request.getheader("user-agent").touppercase();
//ie浏览器和edge浏览器
if (agent.indexof("msie") > 0 || (agent.indexof("gecko")>0 && agent.indexof("rv:11")>0)) {
filename = urlencoder.encode(filename, "utf-8");
} else { //其他浏览器
filename = new string(filename.getbytes("utf-8"), "iso-8859-1");
}
response.setheader("content-disposition", "attachment;filename=" + filename + ".xls");
//定义输出类型
response.setcontenttype("application/msexcel");
//保存excel文件
workbook.write(out);
//关闭文件流
out.close();
} catch (exception e) {
e.printstacktrace();
// return statuscode.error;
}
// return statuscode.success;
}
/**
* 导出excel 含多个sheet 每个sheet中的标题一致
* @param pds 需要导出的数据
* @param titles 导出excel公用数据，需包含title（标题）、headers（逗号隔开，为导出表头）
* @param filename (文件名，只须写上名字，这里会进行处理拼接)
* @param request
* @param response
* 注意 titles里面的数据顺序要与pds中一致 不然导出时会出现标题与内容不符
* @return
*/
public static void exportsheetdata(map<string,list<list<linkedhashmap<object,object>>>> pds, list<pagedata> titles,
string filename,httpservletrequest request, httpservletresponse response) throws ioexception {
if(pds.size()<1){
// return statuscode.is_null;
return;
}
hssfworkbook workbook = new hssfworkbook();
//取得输出流
outputstream out = response.getoutputstream();
simpledateformat dateformat = new simpledateformat("yyyy年mm月dd日hh时mm分ss秒");
//重命名
filename+=dateformat.format(new date());;
//清空缓存
response.reset();
//设置相应内容的编码格式
response.setcharacterencoding("utf-8");
//文件名乱码
//获得浏览器信息并转换为大写
string agent = request.getheader("user-agent").touppercase();
//ie浏览器和edge浏览器
if (agent.indexof("msie") > 0 || (agent.indexof("gecko")>0 && agent.indexof("rv:11")>0)) {
filename = urlencoder.encode(filename, "utf-8");
} else { //其他浏览器
filename = new string(filename.getbytes("utf-8"), "iso-8859-1");
}
response.setheader("content-disposition", "attachment;filename=" + filename + ".xls");
//定义输出类型
response.setcontenttype("application/msexcel");
int sheetnum=0;
for(string namekey:pds.keyset()){
list<list<linkedhashmap<object,object>>> data=pds.get(namekey);
//设置标题
hssfsheet sheet = workbook.createsheet();
//工地/消纳场名作为sheet名
workbook.setsheetname(sheetnum,namekey);
//表头字体
font headerfont = workbook.createfont();
headerfont.setfontname("微软雅黑");
headerfont.setcolor(hssffont.color_normal);
headerfont.setbold(true);
//创建单元格，并设置值表头 设置表头居中
hssfcellstyle stylemain = workbook.createcellstyle();
//水平居中
stylemain.setalignment(horizontalalignment.center);
stylemain.setfont(headerfont);
//导出样式
hssfcellstyle cellstyle_c = workbook.createcellstyle();
// 自动换行
cellstyle_c.setwraptext(true);
cellstyle_c.setalignment(horizontalalignment.center);
int titlenum=0;
int rownum = 1;
int startrow=0;
for(list<linkedhashmap<object,object>> list:data){
//放入数据
//创建标题行
//主标题
hssfrow title = null;
//行标题
hssfrow row = null;
//为了避免标题对内容进行覆盖
if(rownum==1){
title=sheet.createrow(0);
row = sheet.createrow(rownum);
rownum++;
}else{
//让标题与上一行内容存在两个空行
startrow=rownum+2;
title=sheet.createrow(startrow);
row = sheet.createrow(rownum+3);
rownum+=4;
}
pagedata pd=titles.get(titlenum);
//headers表示excel表中第一行的表头
string[] headers=pd.getstring("headers").split(",");
//创建主标题
hssfcell tielecell = title.createcell(0);
tielecell.setcellvalue(pd.getstring("title"));
tielecell.setcellstyle(stylemain);
for(int i=1;i<=headers.length;i++){
hssfcell cell = title.createcell(i);
// hssfrichtextstring text = new hssfrichtextstring(headers[i]);
cell.setcellvalue("");
cell.setcellstyle(stylemain);
}
// 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
// 行和列都是从0开始计数，且起始结束都会合并
// 这里是合并excel中日期的两行为一行
cellrangeaddress region = new cellrangeaddress(startrow, startrow, 0, headers.length-1);
sheet.addmergedregion(region);
//主标题创建结束
//在excel表中添加表头
for(int i=0;i<headers.length;i++){
hssfcell cell = row.createcell(i);
hssfrichtextstring text = new hssfrichtextstring(headers[i]);
cell.setcellvalue(text);
cell.setcellstyle(stylemain);
}
//表头添加结束
//遍历集合数据
for(linkedhashmap<object,object> p :list){
//依次创建行
hssfrow row1 = sheet.createrow(rownum);
int i=0;
//遍历数据
for (object key: p.keyset()){
//创建行中列 并放入数据
hssfcell cell = row1.createcell(i);
string value=string.valueof(p.get(key));
cell.setcellvalue(value!=null?value:"");
if("null".equals(value)){
cell.setcellvalue("");
}
cell.setcellstyle(cellstyle_c);
i++;
}
row1.setheight((short) (26*10));
rownum++;
}
//自动调整列宽
for (int i = 0;i <headers.length;i++){
if(i==headers.length-2){
sheet.setcolumnwidth(i, 20 * 256);
break;
}
if(i==3){
sheet.setcolumnwidth(i, 20 * 256);
continue;
}
sheet.autosizecolumn(i, true);
}
titlenum++;
// }
}
sheetnum++;
}
//保存excel文件
workbook.write(out);
//关闭文件流
out.close();
}
/**
* 导出excel 含多个sheet 每个sheet中的标题不一致
* @param pds 需要导出的数据
* @param titles 导出excel公用数据，需包含title（标题）、headers（逗号隔开，为导出表头）,可以list<pagedata>不一致
* @param filename (文件名，只须写上名字，这里会进行处理拼接)
* @param request
* @param response
* 注意 titles里面的数据顺序要与pds中一致 不然导出时会出现标题与内容不符
* @return
*/
public static void exportsheettitle(linkedhashmap<string,list<list<linkedhashmap<object,object>>>> pds, linkedlist<list<pagedata>> titles,
string filename,httpservletrequest request, httpservletresponse response) throws ioexception {
if(pds.size()<1){
// return statuscode.is_null;
return;
}
hssfworkbook workbook = new hssfworkbook();
//取得输出流
outputstream out = response.getoutputstream();
simpledateformat dateformat = new simpledateformat("yyyy年mm月dd日hh时mm分ss秒");
//重命名
filename+=dateformat.format(new date());;
//清空缓存
response.reset();
//设置相应内容的编码格式
response.setcharacterencoding("utf-8");
//文件名乱码
//获得浏览器信息并转换为大写
string agent = request.getheader("user-agent").touppercase();
//ie浏览器和edge浏览器
if (agent.indexof("msie") > 0 || (agent.indexof("gecko")>0 && agent.indexof("rv:11")>0)) {
filename = urlencoder.encode(filename, "utf-8");
} else { //其他浏览器
filename = new string(filename.getbytes("utf-8"), "iso-8859-1");
}
response.setheader("content-disposition", "attachment;filename=" + filename + ".xls");
//定义输出类型
response.setcontenttype("application/msexcel");
int sheetnum=0;
for(string namekey:pds.keyset()){
list<list<linkedhashmap<object,object>>> data=pds.get(namekey);
//设置标题
hssfsheet sheet = workbook.createsheet();
//工地/消纳场名作为sheet名
workbook.setsheetname(sheetnum,namekey);
//表头字体
font headerfont = workbook.createfont();
headerfont.setfontname("微软雅黑");
headerfont.setcolor(hssffont.color_normal);
headerfont.setbold(true);
//创建单元格，并设置值表头 设置表头居中
hssfcellstyle stylemain = workbook.createcellstyle();
//水平居中
stylemain.setalignment(horizontalalignment.center);
stylemain.setfont(headerfont);
//导出样式
hssfcellstyle cellstyle_c = workbook.createcellstyle();
// 自动换行
cellstyle_c.setwraptext(true);
cellstyle_c.setalignment(horizontalalignment.center);
int titlenum=0;
int rownum = 1;
int startrow=0;
for(list<linkedhashmap<object,object>> list:data){
//放入数据
//创建标题行
//主标题
hssfrow title = null;
//行标题
hssfrow row = null;
//为了避免标题对内容进行覆盖
if(rownum==1){
title=sheet.createrow(0);
row = sheet.createrow(rownum);
rownum++;
}else{
//让标题与上一行内容存在两个空行
startrow=rownum+2;
title=sheet.createrow(startrow);
row = sheet.createrow(rownum+3);
rownum+=4;
}
//遍历每个sheet中的标题
for(pagedata pd:titles.get(sheetnum)){
//headers表示excel表中第一行的表头
string[] headers=pd.getstring("headers").split(",");
//创建主标题
hssfcell tielecell = title.createcell(0);
tielecell.setcellvalue(pd.getstring("title"));
tielecell.setcellstyle(stylemain);
for(int i=1;i<=headers.length;i++){
hssfcell cell = title.createcell(i);
// hssfrichtextstring text = new hssfrichtextstring(headers[i]);
cell.setcellvalue("");
cell.setcellstyle(stylemain);
}
// 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
// 行和列都是从0开始计数，且起始结束都会合并
// 这里是合并excel中日期的两行为一行
cellrangeaddress region = new cellrangeaddress(startrow, startrow, 0, headers.length-1);
sheet.addmergedregion(region);
//主标题创建结束
//在excel表中添加表头
for(int i=0;i<headers.length;i++){
hssfcell cell = row.createcell(i);
hssfrichtextstring text = new hssfrichtextstring(headers[i]);
cell.setcellvalue(text);
cell.setcellstyle(stylemain);
}
//表头添加结束
//遍历集合数据
for(linkedhashmap<object,object> p :list){
//依次创建行
hssfrow row1 = sheet.createrow(rownum);
int i=0;
//遍历数据
for (object key: p.keyset()){
//创建行中列 并放入数据
hssfcell cell = row1.createcell(i);
string value=string.valueof(p.get(key));
cell.setcellvalue(value!=null?value:"");
if("null".equals(value)){
cell.setcellvalue("");
}
cell.setcellstyle(cellstyle_c);
i++;
}
row1.setheight((short) (26*10));
rownum++;
}
//自动调整列宽
for (int i = 0;i <headers.length;i++){
if(i==headers.length-2){
sheet.setcolumnwidth(i, 20 * 256);
break;
}
if(i==3){
sheet.setcolumnwidth(i, 20 * 256);
continue;
}
sheet.autosizecolumn(i, true);
}
}
titlenum++;
// }
}
sheetnum++;
}
//保存excel文件
workbook.write(out);
//关闭文件流
out.close();
}
/**
* 导出excel
* @qkp
*
* @param pds 需要导出的数据
* @param pd 导出excel公用数据，需包含templatefile（模板文件），filename(文件名，只须写上名字，这里会进行处理拼接)
* @param mergecells 合并单元格数组【firstrow,lastrow,firstcol,lastcol】
* @param request
* @param response
* @return
*/
public static void exportdata(map<object,object> pds, pagedata pd,int []mergecells, httpservletrequest request, httpservletresponse response) {
if(pds.size()<1){
return;
}
try {
xlstransformer transformer = new xlstransformer();
inputstream in = new fileinputstream(new file(pd.getstring("templatefile")));
xssfworkbook workbook =(xssfworkbook)transformer.transformxls(in, pds);
sheet sheet = workbook.getsheetat(0);
//合并单元格需要的逻辑
sheet.addmergedregion(new cellrangeaddress(mergecells[0], mergecells[1], mergecells[2], mergecells[3]));//合并单元格的函数
//表头字体
font headerfont = workbook.createfont();
headerfont.setfontname("微软雅黑");
headerfont.setcolor(hssffont.color_normal);
headerfont.setbold(true);
//创建单元格，并设置值表头 设置表头居中
//导出样式
xssfcellstyle cellstyle_c = workbook.createcellstyle();
// 自动换行
cellstyle_c.setwraptext(true);
cellstyle_c.setalignment(horizontalalignment.center);
//取得输出流
outputstream out = response.getoutputstream();
//清空缓存
response.reset();
//设置相应内容的编码格式
response.setcharacterencoding("utf-8");
//1.导出名称
string filename =pd.getstring("filename");
//文件名乱码
//获得浏览器信息并转换为大写
string agent = request.getheader("user-agent").touppercase();
//ie浏览器和edge浏览器
if (agent.indexof("msie") > 0 || (agent.indexof("gecko")>0 && agent.indexof("rv:11")>0)) {
filename = urlencoder.encode(filename, "utf-8");
} else { //其他浏览器
filename = new string(filename.getbytes("utf-8"), "iso-8859-1");
}
response.setheader("content-disposition", "attachment;filename=" + filename + ".xlsx");
//定义输出类型
//response.setcontenttype("application/msexcel");
response.setcontenttype("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//保存excel文件
workbook.write(out);
//关闭文件流
out.close();
} catch (exception e) {
e.printstacktrace();
}
}
/**
* 导出excel
* @author qkp
* @param pds 需要导出的数据
* @param pd 导出excel公用数据，需包含title（标题）、headers（逗号隔开，为导出表头），filename(文件名，只须写上名字，这里会进行处理拼接)
* @param request
* @param response
* @return
*/
public static void exportdata2(list<linkedhashmap<object,object>> pds, pagedata pd, httpservletrequest request, httpservletresponse response) {
if(pds.size()<1){
return;
}
try {
hssfworkbook workbook = new hssfworkbook();
//设置标题
hssfsheet sheet = workbook.createsheet();
//headers表示excel表中第一行的表头
string[] headers=pd.getstring("headers").split(",");
//表头字体
font headerfont = workbook.createfont();
headerfont.setfontname("微软雅黑");
headerfont.setcolor(hssffont.color_normal);
headerfont.setbold(true);
//创建单元格，并设置值表头 设置表头居中
hssfcellstyle stylemain = workbook.createcellstyle();
//水平居中
stylemain.setalignment(horizontalalignment.center);
stylemain.setfont(headerfont);
stylemain.setborderbottom(borderstyle.thin);
stylemain.setbottombordercolor(indexedcolors.black.getindex());
stylemain.setbordertop(borderstyle.thin);
stylemain.settopbordercolor(indexedcolors.black.getindex());
stylemain.setborderleft(borderstyle.thin);
stylemain.setleftbordercolor(indexedcolors.black.getindex());
stylemain.setborderright(borderstyle.thin);
stylemain.setrightbordercolor(indexedcolors.black.getindex());
//导出样式
hssfcellstyle cellstyle_c = workbook.createcellstyle();
// 自动换行
cellstyle_c.setwraptext(true);
cellstyle_c.setalignment(horizontalalignment.center);
cellstyle_c.setborderbottom(borderstyle.thin);
cellstyle_c.setbottombordercolor(indexedcolors.black.getindex());
cellstyle_c.setbordertop(borderstyle.thin);
cellstyle_c.settopbordercolor(indexedcolors.black.getindex());
cellstyle_c.setborderleft(borderstyle.thin);
cellstyle_c.setleftbordercolor(indexedcolors.black.getindex());
cellstyle_c.setborderright(borderstyle.thin);
cellstyle_c.setrightbordercolor(indexedcolors.black.getindex());
//创建列标题行
hssfrow row = sheet.createrow(0);
//标题行样式
hssfcellstyle titlestyle = workbook.createcellstyle();
//表头字体
font titlefont = workbook.createfont();
titlefont.setfontname("微软雅黑");
titlefont.setcolor(hssffont.color_normal);
titlefont.setbold(true);
titlefont.setfontheightinpoints((short)18);
titlestyle.setfont(titlefont);
titlestyle.setalignment(horizontalalignment.center);
hssfcell titlecell =row.createcell(0);
titlecell.setcellvalue(pd.getstring("title"));
titlecell.setcellstyle(titlestyle);
//合并单元格需要的逻辑
sheet.addmergedregion(new cellrangeaddress(0, 0, 0,headers.length-1 ));//合并单元格的函数
row.setrowstyle(titlestyle);
row.setheightinpoints(56);
//列表题
row = sheet.createrow(1);
row.setheightinpoints(39);
//在excel表中添加表头
for(int i=0;i<headers.length;i++){
hssfcell cell = row.createcell(i);
hssfrichtextstring text = new hssfrichtextstring(headers[i]);
cell.setcellvalue(text);
cell.setcellstyle(stylemain);
}
int rownum = 2;
//遍历集合数据
for(linkedhashmap<object,object> p :pds){
//依次创建行
hssfrow row1 = sheet.createrow(rownum);
int i=0;
//遍历数据
for (object key: p.keyset()){
//创建行中列 并放入数据
hssfcell cell = row1.createcell(i);
string value=string.valueof(p.get(key));
cell.setcellvalue(value!=null?value:"");
if("null".equals(value)){
cell.setcellvalue("");
}
cell.setcellstyle(cellstyle_c);
i++;
}
//row1.setheight((short) (26*10));
rownum++;
}
//自动调整列宽
for (int i = 0;i <headers.length;i++){
if(i==headers.length-2){
sheet.setcolumnwidth(i, 20 * 256);
break;
}
if(i==3){
sheet.setcolumnwidth(i, 20 * 256);
continue;
}
sheet.autosizecolumn(i);
//sheet.autosizecolumn(i, true);
}
//取得输出流
outputstream out = response.getoutputstream();
simpledateformat dateformat = new simpledateformat("yyyy年mm月dd日hh时mm分ss秒");
//清空缓存
response.reset();
//设置相应内容的编码格式
response.setcharacterencoding("utf-8");
//1.导出名称
string filename =pd.getstring("filename")+dateformat.format(new date());
//文件名乱码
//获得浏览器信息并转换为大写
string agent = request.getheader("user-agent").touppercase();
//ie浏览器和edge浏览器
if (agent.indexof("msie") > 0 || (agent.indexof("gecko")>0 && agent.indexof("rv:11")>0)) {
filename = urlencoder.encode(filename, "utf-8");
} else { //其他浏览器
filename = new string(filename.getbytes("utf-8"), "iso-8859-1");
}
response.setheader("content-disposition", "attachment;filename=" + filename + ".xls");
//定义输出类型
response.setcontenttype("application/msexcel");
//保存excel文件
workbook.write(out);
//关闭文件流
out.close();
} catch (exception e) {
e.printstacktrace();
// return statuscode.error;
}
// return statuscode.success;
}
}总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论