Struts2导出Excel步骤及问题汇总 poi分页

最新推荐文章于 2018-11-06 14:57:32 发布

杨庆磊

最新推荐文章于 2018-11-06 14:57:32 发布

阅读量687

点赞数

分类专栏： java

java 专栏收录该内容

41 篇文章 0 订阅

订阅专栏

最近开发项目中遇到了很多问题,终于可以在空闲的时候总结一下,今天要汇总的是Excel下载导出等相关问题,主要涉及到问题,如下:

1. 生成Excel
2. 大数据Excel导出(支持多个sheet,亲测可以导出30W数据,再大程序报内存溢出)
3. 下载文件,及中文文件名下载乱码,为空等问题

工具/原料

1、poi-3.8.jar

2、poi-ooxml-3.8.jar

方法/步骤

1、添加Excel标题对象

/**
* 用来存储Excel标题的对象，通过该对象可以获取标题和方法的对应关系
* 
* @author monkey
* 
*/
public class ExcelHeader implements Comparable<ExcelHeader> {
   
   /**
    * excel的标题名称
    */
   private String title;
   /**
    * 每一个标题的顺序
    */
   private int order;
   /**
    * 说对应方法名称
    */
   private String methodName;

   public String getTitle() {
       return title;
   }

   public void setTitle(String title) {
       this.title = title;
   }

   public int getOrder() {
       return order;
   }

   public void setOrder(int order) {
       this.order = order;
   }

   public String getMethodName() {
       return methodName;
   }

   public void setMethodName(String methodName) {
       this.methodName = methodName;
   }

   public int compareTo(ExcelHeader o) {
       return order > o.order ? 1 : (order < o.order ? -1 : 0);
   }

   public ExcelHeader(String title, int order, String methodName) {
       super();
       this.title = title;
       this.order = order;
       this.methodName = methodName;
   }

   @Override
   public String toString() {
       return "ExcelHeader [title=" + title + ", order=" + order
               + ", methodName=" + methodName + "]";
   }

}

定义annotation类，可以通过该类设置导出相应的属性，标题及排序

import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;


/**
* 用来在对象的get方法上加入的annotation，通过该annotation说明某个属性所对应的标题
* @author Administrator
*
*/
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelResources {
   /**
    * 属性的标题名称
    * @return
    */
   String title();
   /**
    * 在excel的顺序
    * @return
    */
   int order() default 9999;
}

操作Excel，设置Excel标题样式，sheet名称，sheet每页显示条数等信息

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Workbook;

/**
* Excel 操作类
* 
* @author houzhiqing
* 
*/
@SuppressWarnings("unchecked")
public class ExcelUtil {

   private static final String POSITION_TITLE = "title";

   private static final String POSITION_BODY = "body";

   private static ExcelUtil eu = new ExcelUtil();

   private ExcelUtil() {
   }

   public static ExcelUtil getInstance() {
       return eu;
   }

   /**
    * 导出对象到Excel，不是基于模板的，直接新建一个Excel完成导出，基于路径的导出
    * 
    * @param outPath
    *            输出路径
    * @param objs
    *            数据源
    * @param clz
    *            类
    * @param sheetName
    *            分sheet导出是sheet的名字 ， 如 “sheet” -> sheet1,sheet2...
    * @param pageSize
    *            每个sheet要显示多少条数据
    */
   public void exportObj2Excel(String outPath, List objs, Class clz,
           String sheetName, int pageSize) {
       Workbook wb = handleObj2Excel(objs, clz, sheetName, pageSize);
       FileOutputStream fos = null;
       try {
           fos = new FileOutputStream(outPath);
           wb.write(fos);
       } catch (FileNotFoundException e) {
           e.printStackTrace();
       } catch (IOException e) {
           e.printStackTrace();
       } finally {
           try {
               if (fos != null)
                   fos.close();
           } catch (IOException e) {
               e.printStackTrace();
           }
       }
   }

   /**
    * 导出对象到Excel，不是基于模板的，直接新建一个Excel完成导出，基于路径的导出
    * 
    * @param outPath
    *            输出路径
    * @param objs
    *            数据源
    * @param clz
    *            类
    * @param sheetName
    *            分sheet导出是sheet的名字 ， 如 “sheet” -> sheet1,sheet2...
    * @param pageSize
    *            每个sheet要显示多少条数据
    */
   public HSSFWorkbook handleObj2Excel(List objs, Class clz, String sheetName,
           int pageSize) {
       HSSFWorkbook wb = null;
       try {
           wb = new HSSFWorkbook();
           // TODO 获取表头
           List<ExcelHeader> headers = getHeaderList(clz);
           Collections.sort(headers);

           if (null != objs && objs.size() > 0) {

               int sheetCount = objs.size() % pageSize == 0 ? objs.size()
                       / pageSize : objs.size() / pageSize + 1;

               for (int i = 1; i <= sheetCount; i++) {

                   HSSFSheet sheet = null;
                   if(!StringUtils.isEmpty(sheetName)) {
                       sheet = wb.createSheet(sheetName + i);
                   } else {
                       sheet = wb.createSheet();
                   }
                   
                   HSSFRow row = sheet.createRow(0);

                   // 写标题
                   CellStyle titleStyle = setCellStyle(wb, POSITION_TITLE);
                   for (int m = 0; m < headers.size(); m++) {
                       HSSFCell cell = row.createCell(m);
                       cell.setCellStyle(titleStyle);
                       cell.setCellValue(headers.get(m).getTitle());
                       sheet.setColumnWidth(m, 5000); // 设置每列的宽度
                   }

                   // 写数据
                   Object obj = null;
                   CellStyle bodyStyle = setCellStyle(wb, POSITION_BODY);
                   int begin = (i - 1) * pageSize;
                   int end = (begin + pageSize) > objs.size() ? objs.size()
                           : (begin + pageSize);

                   System.out.println("begin:" + begin + ",end=" + end);

                   int rowCount = 1;
                   for (int n = begin; n < end; n++) {
                       row = sheet.createRow(rowCount);
                       rowCount++;
                       obj = objs.get(n);
                       for (int x = 0; x < headers.size(); x++) {
                           Cell cell = row.createCell(x);
                           cell.setCellStyle(bodyStyle);
                           cell.setCellValue(BeanUtils.getProperty(obj,
                                   getMethodName(headers.get(x))));
                       }
                   }

               }
           }

       } catch (Exception e) {
           e.printStackTrace();
           throw new RuntimeException("生成excel失败");
       }
       return wb;
   }

   /**
    * 根据标题获取相应的方法名称
    * 
    * @param eh
    * @return
    */
   private String getMethodName(ExcelHeader eh) {
       String mn = eh.getMethodName().substring(3);
       mn = mn.substring(0, 1).toLowerCase() + mn.substring(1);
       return mn;
   }

   /**
    * 获取excel标题列表
    * 
    * @param clz
    * @return
    */
   private List<ExcelHeader> getHeaderList(Class clz) {
       List<ExcelHeader> headers = new ArrayList<ExcelHeader>();
       Method[] ms = clz.getDeclaredMethods();
       for (Method m : ms) {
           String mn = m.getName();
           if (mn.startsWith("get")) {
               if (m.isAnnotationPresent(ExcelResources.class)) {
                   ExcelResources er = m.getAnnotation(ExcelResources.class);
                   headers.add(new ExcelHeader(er.title(), er.order(), mn));
               }
           }
       }
       return headers;
   }

   /**
    * 设置单元格样式
    * 
    * @param position
    *            ["body","title"]
    */
   private static CellStyle setCellStyle(Workbook workBook, String position) {

       CellStyle cellStyle = workBook.createCellStyle();
       cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
       cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
       // 设置单元格字体
       Font headerFont = workBook.createFont(); // 字体
       if (POSITION_TITLE.equals(position)) {
           headerFont.setFontHeightInPoints((short) 12);
       } else {
           headerFont.setFontHeightInPoints((short) 10);
       }
       headerFont.setFontName("宋体");
       if (POSITION_TITLE.equals(position))
           headerFont.setBoldweight((short) 10);
       cellStyle.setFont(headerFont);
       cellStyle.setWrapText(true);

       cellStyle.setFillBackgroundColor(HSSFCellStyle.THICK_FORWARD_DIAG);
       // 设置单元格边框及颜色
       cellStyle.setBorderBottom((short) 1);
       cellStyle.setBorderLeft((short) 1);
       cellStyle.setBorderRight((short) 1);
       cellStyle.setBorderTop((short) 1);
       cellStyle.setWrapText(true);

       cellStyle.setLeftBorderColor(HSSFColor.BLACK.index); // 设置边框颜色
       cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
       cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
       cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);

       return cellStyle;
   }
}

导出对象实体类，实际项目中这个类可以有更多的属性

import java.io.Serializable;
import java.util.Date;

import com.monkey.poi.util.ExcelResources;


public class ProductCard implements Serializable {

   private static final long serialVersionUID = -70571478472359104L;
   
   private Integer id;
   
   private String code;
   
   private String codePwd;
   
   public Integer getId() {
       return id;
   }

   public void setId(Integer id) {
       this.id = id;
   }
   
   @ExcelResources(title="卡号", order=1)
   public String getCode() {
       return code;
   }

   public void setCode(String code) {
       this.code = code;
   }
   
   @ExcelResources(title="密码", order=2)
   public String getCodePwd() {
       return codePwd;
   }

   public void setCodePwd(String codePwd) {
       this.codePwd = codePwd;
   }
   
}

junit测试

@Test
   public void test002() {
       List<ProductCard> pcList = new ArrayList<ProductCard>();
       for (int i = 0; i < 300000; i++) {
           ProductCard p = new ProductCard();
           p.setCode(DateUtils.formatDate(DateUtils.DATE_PATTERN_PLAIN,new Date()));
           p.setCodePwd("123456" + i);
           
           pcList.add(p);
       }
       
       ExcelUtil.getInstance().exportObj2Excel("d:/product-1.xls", pcList,
               ProductCard.class, "我的sheet", 10000);
   }