java实现把对象数组通过excel方式导出的功能
程序员文章站
2024-03-02 20:19:16
一、导入相关jar包，pom依赖如下：
org.apache.poi
一、导入相关jar包，pom依赖如下：
<dependency> <groupid>org.apache.poi</groupid> <artifactid>poi</artifactid> <version>release</version> </dependency>
二、开始撸代码
1.如果导出功能使用的比较多，可以将其做成一个工具类，对我下面贴出的代码进行改造
//结果返回的是写入的记录数（以下用的是自己业务场景数据）
public int downloadtoexcel(outputstream outputstream,list<paimaimoneyvo> paimaimoneyvolist) {
//文档对象
hssfworkbook wb = new hssfworkbook();
int rownum = 0;
sheet sheet = wb.createsheet("excel的标题");
row row0 = sheet.createrow(rownum++);
//因为场景不同，titil不同，可以在外面写成数组当参数传进来
row0.createcell(0).setcellvalue("第一列属性名");
row0.createcell(1).setcellvalue("第二列属性名");
row0.createcell(2).setcellvalue("第三列属性名");
row0.createcell(3).setcellvalue("第四列属性名");
row0.createcell(4).setcellvalue("第五列属性名");
row0.createcell(5).setcellvalue("第六列属性名");
if (paimaimoneyvolist != null && paimaimoneyvolist.size() > 0) {
for (paimaimoneyvo paimaimoneyvo : paimaimoneyvolist) {
row row = sheet.createrow(rownum++);
row.createcell(0).setcellvalue(paimaimoneyvo.getpaimaiid());
row.createcell(1).setcellvalue(paimaimoneyvo.gettitle());
row.createcell(2).setcellvalue(paimaimoneyvo.getusername());
row.createcell(3).setcellvalue(paimaimoneyvo.getmoney()+"元");
row.createcell(4).setcellvalue("升价拍");
row.createcell(5).setcellvalue(bidder);
}
}
try {
wb.write(outputstream);
logenum.law_ware.info("表数据写入到excel表成功,一共写入了"+(rownum - 1)+"条数据");
outputstream.close();
} catch (ioexception e) {
logenum.law_ware.error("流关闭异常！", e);
} finally {
if (outputstream != null) {
try {
outputstream.close();
} catch (ioexception e) {
logenum.law_ware.error("流关闭异常！", e);
}
}
}
return rownum - 1;
}
2.“工具类”写好后，下面就开始使用它了，从上面的函数参数可以看到，我们需要传过去两个对象，一个是输出流outputstream，通过流的方式把excel想要到浏览器，
另外一个就是我们需要导出的对象数组，好了，不解释太多，看代码。（下面的方法写在action层，通过struts.xml配置访问即可实现下载）
public void exportbail(){
this.filename = "excel文件名";
try {
list<paimaimoneyvo> paimaimoneyvolist = new arraylist<>();
//下面是我的业务场景获取对象数组
if(paimaimoneysearchparam!=null){
paimaimoneysearchparam.setvendorid(webhelper.getvenderid());
paimaimoneysearchparam.setpagesize(constants.auction_ware_page_size);
paimaimoneysearchparam.setpage(page);
paimaimoneydto paimaimoneydto = auctionwareservice1.searchpoppaimaimoneylist(paimaimoneysearchparam);
if(paimaimoneydto!=null){
int count = paimaimoneydto.getcount();
int totalpage = count/ constants.auction_ware_page_size + (count% constants.auction_ware_page_size > 0?1:0);
for(int i=1;i<=totalpage;i++){
paimaimoneysearchparam.setpage(i);
paimaimoneydto paimaimoneyresultresult = auctionwareservice1.searchpoppaimaimoneylist(paimaimoneysearchparam);
if(paimaimoneyresultresult!=null){
paimaimoneyvolist.addall(paimaimoneyresultresult.getlist());
}
}
}
}
outputstream outputstream = response.getoutputstream();
response.reset();//清空输出流
//下面是对中文文件名的处理
response.setcharacterencoding("utf-8");//设置相应内容的编码格式
//解析浏览器
final string useragent = request.getheader("user-agent").tolowercase();
if(useragent.contains("firefox")){ //火狐浏览器
filename = new string(filename.getbytes(), "iso8859-1");
}else{
filename = urlencoder.encode(filename, "utf-8"); //其他浏览器
filename = filename.replace("+", "%20"); //encode后替换，解决空格问题（其中%20是空格在utf-8下的编码 ，如果不这么写，浏览器会用+代替空格）
}
response.setheader("content-disposition", "attachment;filename=" +filename + ".xls");//指定输出文件名
response.setcontenttype("application/msexcel");//定义输出类型
int rounum = ensurepricelisttoexcel(outputstream,paimaimoneyvolist);
logenum.law_ware.info("【riseauctionaction.downloadensurepriceexcel】导出成功,一共更新了{"+rounum+"}条记录");
} catch (exception e) {
logenum.law_ware.error("【riseauctionaction.downloadensurepriceexcel】导出失败,error is {}", e);
}
}
三、拓展（详细的工具类开发）
如果你觉得上面写的太简单了，可以继续往下看，我把它整理出了“万能”的工具类，供大家参考。
package com.jd.pop.auction.util.excel;
import com.jd.common.web.result.result;
import com.jd.pop.auction.util.excel.annotations.excelcolumn;
import com.jd.pop.auction.util.excel.annotations.excelmapping;
import com.jd.pop.auction.util.excel.annotations.apt.excelcolumnapt;
import com.jd.pop.auction.util.excel.annotations.apt.excelmappingapt;
import org.apache.log4j.logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.hssfcolor;
import org.apache.poi.ss.util.cellrangeaddress;
import java.io.ioexception;
import java.io.outputstream;
import java.lang.reflect.field;
import java.lang.reflect.invocationtargetexception;
import java.util.collection;
import java.util.iterator;
import java.util.list;
public class generateexcel {
private final static logger log = logger.getlogger(generateexcel.class);
private hssfworkbook workbook;
private hssfcellstyle headstyle;
private hssffont headcellfont;
private hssfcellstyle theadstyle;
private hssffont theadcellfont;
private hssfcellstyle tbodystyle;
private hssffont tbodycellfont;
private hssffont stringfont;
private static final short column_width = 15;
private static final short row_height = 400;
public generateexcel() {
this.workbook = new hssfworkbook();
//标题
this.headstyle = workbook.createcellstyle();
headstyle.setfillforegroundcolor(hssfcolor.grey_50_percent.index);
headstyle.setfillpattern(hssfcellstyle.solid_foreground);
// headstyle.setborderbottom(hssfcellstyle.border_thin);
// headstyle.setborderleft(hssfcellstyle.border_thin);
// headstyle.setborderright(hssfcellstyle.border_thin);
// headstyle.setbordertop(hssfcellstyle.border_thin);
headstyle.setalignment(hssfcellstyle.align_center);
// headstyle.setwraptext(true);
this.headcellfont = workbook.createfont();
headcellfont.setfontheightinpoints((short)13);
headcellfont.setboldweight(hssffont.boldweight_bold);
headstyle.setfont(headcellfont);
this.theadstyle = workbook.createcellstyle();
theadstyle.setfillforegroundcolor(hssfcolor.white.index);
theadstyle.setfillpattern(hssfcellstyle.solid_foreground);
theadstyle.setborderbottom(hssfcellstyle.border_thin);
theadstyle.setborderleft(hssfcellstyle.border_thin);
theadstyle.setborderright(hssfcellstyle.border_thin);
theadstyle.setbordertop(hssfcellstyle.border_thin);
theadstyle.setalignment(hssfcellstyle.align_center);
theadcellfont = workbook.createfont();
theadcellfont.setcolor(hssfcolor.black.index);
theadcellfont.setfontheightinpoints((short) 12);
theadcellfont.setboldweight(hssffont.boldweight_bold);
theadstyle.setfont(theadcellfont);
tbodystyle = workbook.createcellstyle();
tbodystyle.setfillforegroundcolor(hssfcolor.white.index);
tbodystyle.setfillpattern(hssfcellstyle.solid_foreground);
tbodystyle.setborderbottom(hssfcellstyle.border_thin);
tbodystyle.setborderleft(hssfcellstyle.border_thin);
tbodystyle.setborderright(hssfcellstyle.border_thin);
tbodystyle.setbordertop(hssfcellstyle.border_thin);
tbodystyle.setalignment(hssfcellstyle.align_center);
tbodystyle.setverticalalignment(hssfcellstyle.vertical_center);
tbodycellfont = workbook.createfont();
tbodycellfont.setboldweight(hssffont.boldweight_normal);
tbodystyle.setfont(tbodycellfont);
stringfont = workbook.createfont();
stringfont.setcolor(hssfcolor.black.index);
}
public <t> result export(list<string> titles, field[] fields, class clazz, collection<t> dataset, outputstream out, boolean pager) {
result result = new result(false);
if(pager){
}else{
hssfsheet sheet = workbook.createsheet( "第一页");
sheet.setdefaultcolumnwidth(column_width);
sheet.setdefaultrowheight(row_height);
//标题
for (int i = 0; i <titles.size(); i++) {
hssfrow titlerow = sheet.createrow(i);
titlerow.setheightinpoints(20f);
sheet.addmergedregion(new cellrangeaddress(i,i,0,fields.length-1));
hssfcell titlecell =titlerow.createcell(0);
titlecell.setcellvalue(titles.get(i));
titlecell.setcellstyle(headstyle);
}
//列名
hssfrow row = sheet.createrow(titles.size());
for (short i = 0; i < fields.length; i++) {
hssfcell cell = row.createcell(i);
cell.setcellstyle(theadstyle);
if(fields[i].isannotationpresent(excelcolumn.class)){
excelcolumn an_1 = fields[i].getannotation(excelcolumn.class);
hssfrichtextstring text = new hssfrichtextstring(an_1.name());
cell.setcellvalue(text);
}else if(fields[i].isannotationpresent(excelmapping.class)){
excelmapping an_1 = fields[i].getannotation(excelmapping.class);
hssfrichtextstring text = new hssfrichtextstring(an_1.name());
cell.setcellvalue(text);
}
}
iterator<t> it = dataset.iterator();
int index = titles.size();
while (it.hasnext()) {
index++;
row = sheet.createrow(index);
t t = (t) it.next();
for (short i = 0; i < fields.length; i++) {
hssfcell cell = row.createcell(i);
cell.setcellstyle(tbodystyle);
field field = fields[i];
try {
string textvalue;
if(field.isannotationpresent(excelmapping.class)){
textvalue = new excelmappingapt().getcolumnvalue(field,t,clazz);
}else{
textvalue = new excelcolumnapt().getcolumnvalue(field,t,clazz);
}
cell.setcellvalue(textvalue);
} catch (nosuchmethodexception e) {
string errormsg = field.getname() +"字段，第"+ index+ "条数据， nosuchmethodexception 反射错误！";
log.error(errormsg,e);
result.adddefaultmodel(errormsg);
return result;
} catch (illegalaccessexception e) {
string errormsg = field.getname() +"字段，第"+ index+ "条数据， illegalaccessexception ";
log.error(errormsg,e);
result.adddefaultmodel(errormsg);
return result;
} catch (invocationtargetexception e) {
string errormsg = field.getname() +"字段，第"+ index+ "条数据， invocationtargetexception ";
log.error(errormsg,e);
result.adddefaultmodel(errormsg);
return result;
}
}
}
}
try {
workbook.write(out);
result.setsuccess(true);
return result;
} catch (ioexception e) {
string errormsg = "将导出数据写入输出流失败!";
log.error("将导出数据写入输出流失败！ ",e);
result.adddefaultmodel(errormsg);
return result;
}finally {
try {
out.close();
} catch (ioexception e) {
string errormsg = "关闭输出流异常!";
log.error("关闭输出流异常！ ",e);
result.adddefaultmodel(errormsg);
return result;
}
}
}
}
public class exportexcelutils {
private final static logger log = logger.getlogger(exportexcelutils.class);
public static <t> result export(list<string> titles,list<t> sourcelist, outputstream out, boolean pager){
result result = new result(false);
if(collectionutils.isempty(sourcelist)){
result.adddefaultmodel("exportexcelutils's param sourcelist is empty!");
log.error("exportexcelutils's param sourcelist is empty!");
return result;
}
if( out == null){
log.error("exportexcelutils's param outputstream is null!");
result.adddefaultmodel("exportexcelutils's param outputstream is null!");
return result;
}
class clazz = null;
field[] fieldarr = null;
try{
//得到需要转换的列名
clazz = sourcelist.get(0).getclass();
field[] fields = clazz.getdeclaredfields();
list<field> fieldlist = new arraylist<field>();
for(field field:arrays.aslist(fields)){
field.setaccessible(true);
if(field.isannotationpresent(excelcolumn.class)){
fieldlist.add(field);
}else if(field.isannotationpresent(excelmapping.class)){
fieldlist.add(field);
}
}
if(collectionutils.isempty(fieldlist)){
log.error("实体类中无需要导出的字段!");
result.adddefaultmodel("实体类中无需要导出的字段！");
return result;
}
fieldarr = fieldlist.toarray(new field[fieldlist.size()]);
}catch(exception e){
log.error("数据拼装异常!");
result.adddefaultmodel("数据拼装异常!");
return result;
}
//生成excel
generateexcel ge = new generateexcel();
return ge.export(titles,fieldarr,clazz,sourcelist,out,false);
}
}
这一部分写的比较粗糙，但是实现的比较详细，仅供参考，大家可以稍微改造成为自己独有的utils。
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持！