java实现把对象数组通过excel方式导出的功能

java实现把对象数组通过excel方式导出的功能

一、导入相关jar包，pom依赖如下：

org.apache.poi

poi

RELEASE

二、开始撸代码

1.如果导出功能使用的比较多，可以将其做成一个工具类，对我下面贴出的代码进行改造

//结果返回的是写入的记录数（以下用的是自己业务场景数据）

　　public int downLoadToExcel(OutputStream outputStream,List 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 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.gethttp://Logger(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.setFillPquATrSavattern(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 Result export(List titles, Field[] fields, Class clazz, Collection 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

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 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 Result export(List titles,List 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 fieldList = new ArrayList();

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。

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 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 Result export(List titles,List 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 fieldList = new ArrayList();

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。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。