Sharing sample code for java to implement the function of exporting object arrays through excel

This article mainly introduces the relevant knowledge of java to implement the function of exporting objectsarray through excel. It has a very good reference value. Let’s take a look at it with the editor.

1. Import the relevant jar package. The pom dependencies are as follows:

  <dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>RELEASE</version>
  </dependency>

2 , Start coding

1. If the export function is used a lot, you can make it into a tool class and modify the code I posted below

//结果返回的是写入的记录数（以下用的是自己业务场景数据）
　　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. After the "Tool Class" is written, we will start using it. From the function parameters above, we can see that we need Pass two objects, one is the output stream OutPutStream, and the excel is sent to the browser through the stream.

The other is the array of objects we need to export. Okay, no Too many explanations, look at the code. (The following method is written in the action layer, and can be downloaded through struts.xml configuration access)

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);
  }
 }

3. Expansion (detailed tool development)

If you feel that what is written above is too simple, you can continue reading below. I have sorted it out into "universal" tool categories for your reference.

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;
   }
  }
 }
}

rrree

The above is the detailed content of Sharing sample code for java to implement the function of exporting object arrays through excel. For more information, please follow other related articles on the PHP Chinese website!