01.Java-POI-Excel报表导出-以文件流的形式返回页面供下载

最新推荐文章于 2022-03-21 22:15:59 发布

原创

最新推荐文章于 2022-03-21 22:15:59 发布 · 置顶 · 1.9k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Excel #Java POI #自定义注解 #报表导出 #文件流导出

本文介绍了如何使用Java的POI库以文件流的形式导出Excel报表，包括实体类的注解配置、报表实体类的创建、Action和Controller中调用工具类返回文件流，以及JSP页面的jQuery处理。该方法适用于带有继承关系的实体类，支持自定义注解来指定导出属性及其排序。

01.实体类注解

import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
* excel导出数据专用注解
* 如果在类上使用，导入的excel必须优先存在第一行标题属性，否则报错。
* @author z
*/
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.TYPE})
public @interface ExcelStatisticIdentifier {
public String value() default "";
}

02.实体类主键注解

import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
* excel用于分类的实体类属性注解。一个实体类中最多一个这个注解，该注解标注的属性值必须在excel中唯一
* 最多一个属性标注该注解
* 否则，出来的结果就不准咯
*
* ==========================
* * *wxcid hotelid 2018-01 2018-02 2018-03 2018-04...
* * * 001 600186 11 22 28 40
* * * 001 600184 12 23 24 8
* * * 002 200185 12 23 24 8
* * *===========================
* 标注在hotelid上。标注的属性值唯一
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@Inherited
public @interface VerticalAxisUnique {
public String value() default "";
}

03.实体类需要导出的属性注解

import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
* excel用于分类的实体类属性注解
* ==========================
* * * hotelid 2018-01 2018-02 2018-03 2018-04...
* * * **酒店 11 22 28 40
* * * **酒店 12 23 24 8
* * *===========================
*
* 标注到实体类的hotelid属性上
*
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@Inherited
public @interface VerticalAxis {
public String value() default "";
}

04.实体类需要导出的属性排序注解

import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
* excel用于标记实体类中参与报表统计的属性在报表表头中的顺序
*
* * ==========================
* * *wxcid hotelid .. . .. ..
* * * 001 600186 11 22 28 40
* * * 001 600184 12 23 24 8
* * * 002 200185 12 23 24 8
* * *===========================
*
* @VerticalAxisIndex(0)
* private String wxcid
* @VerticalAxisIndex(1)
* private String hotelid
*
* 表示wxcid排第一列，hotelid排第二列
* @author tk
*
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@Inherited
public @interface VerticalAxisIndex {

public String value() default "";
}

以上注解适用于所有实体类，包括带继承（报表中有些属性在父类中）的实体类

0.5.报表实体类

import com.**.ExcelStatisticIdentifier;
import com.**.VerticalAxis;
import com.**.VerticalAxisIndex;
import com.**.VerticalAxisUnique;

@ExcelStatisticIdentifier
public class ReportThird {

   public static final Integer TYPE_SCENEPLANB = 0;
   @VerticalAxisUnique("id")
   private Integer id;
   private String wxcid;
   private String hotelid;
   private Integer type;
   private Integer ref_id;
   private String sdate;
   @VerticalAxis("新增粉丝数")
   @VerticalAxisIndex("1")
   private Integer subscribe_count; //新增粉丝
   @VerticalAxis("取消关注数")
   @VerticalAxisIndex("2")
   private Integer unsubscribe_count;//取消关注
   @VerticalAxis("注册会员数")
   @VerticalAxisIndex("3")
   private Integer reguser_count;//注册
   @VerticalAxis("绑定会员数")
   @VerticalAxisIndex("4")
   private Integer binduser_count;//绑定
   @VerticalAxis("订单数")
   @VerticalAxisIndex("5")
   private Integer order_count;//订单数
   @VerticalAxis("订单总额")
   @VerticalAxisIndex("6")
   private Float order_totalmoney;//订单总额
   @VerticalAxis("首次订单数")
   @VerticalAxisIndex("7")
   private Integer first_order_count;//首次订单数
   @VerticalAxis("首次订单总额")
   @VerticalAxisIndex("8")
   private Float first_order_money;//首次订单总额
   @VerticalAxis("生成日期")
   @VerticalAxisIndex("9")
   private String writetime;
   private String hotelname;
   @VerticalAxis("场景名称")
   @VerticalAxisIndex("0")
   private String scenename;
   private Integer scenevalue;

   public Integer getScenevalue() {
       return scenevalue;
   }

   public void setScenevalue(Integer scenevalue) {
       this.scenevalue = scenevalue;
   }

   public ReportThird(){};

   public ReportThird(String wxcid, Integer type, Integer ref_id, String sdate) {
       super();
       this.wxcid = wxcid;
       this.type = type;
       this.ref_id = ref_id;
       this.sdate = sdate;
   }

set、get 方法省略。。。。。。。。。。。。
}

06.1.使用action调用工具类并返回文件流给页面自动下载Excel文件

public void downloadScenPlanreport(){

//这里是一个需要导出的数据集合，此处具体内容省略
       List<ReportThird> reportThirdList = getScenPlanData();

       Map<String, Object> map =new HashMap<String,Object>();
       if(reportThirdList == null || reportThirdList.size() <= 0) {
           map.put("code",HttpStatusCode.ERROR);
           map.put("data", "当前报表没有可供下载数据");
           Tools.writeJson(response,map);
           return ;
       }

       try {
           Excelkit.createExcelReport(reportThirdList,realmName ,report_create_path, ReportNames.SCENPLAN_NAME, sessionWxcid,0,false,false, null, null, response);
       } catch (ExcelException e) {
           e.printStackTrace();
       }
   }

06.2.使用controller调用工具类并返回文件流给页面自动下载Excel文件

1.controller层内容

@RequestMapping("/report/download")
   @ResponseBody
   public HttpServletResponse downloadReport(String wxcid,String sdate, String edate,Integer activityid,HttpServletResponse response){
       ResponseMessage report = cutPriceActivityService.report(wxcid,sdate,edate,activityid);
       List<CutPriceReport> cutPriceReportList = null;
if(report.getData() != null) {
   cutPriceReportList = (List<CutPriceReport>)report.getData().get("reportList");
}

if(cutPriceReportList != null && cutPriceReportList.size() > 0){
        Collections.sort(cutPriceReportList, new Comparator<CutPriceReport>() {
            DateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
            public int compare(CutPriceReport o1, CutPriceReport o2) {
                Long time = null;
                    Long time2 = null;
                    try {
                        time = sdf.parse(o1.getDate()).getTime();
                        time2 = sdf.parse(o2.getDate()).getTime();
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                return time.intValue() - time2.intValue();
            }
        });
    }

return excelReportService.downloadReport(cutPriceReportList, wxcid, ReportNames.CUTPRICE_DAY_NAME, 0, false, false, null, null,response);
}

2.service层

import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.**.ErrorCode;
import com.**.ResponseMessage;
import com.**.excel.service.ExcelReportService;
import com.**.excel.util.Excelkit;
import com.**.util.ExcelException;

//@Service
public class ExcelReportServiceImpl implements ExcelReportService {

   Logger log=Logger.getLogger(ExcelReportServiceImpl.class);

   //@Value("${report.url}")
   private String report_create_path;//生成报表文件的绝对目录路径
   //@Value("${report.realmName}")
   private String realmName;//生成报表的文件名（不需要后缀名）

   @Override
   public <T> HttpServletResponse downloadReport(List<T> reportlist,String wxcid,String reportNames, int titleRowNo, boolean ifTop, boolean ifEnd, String topTitle, String endTitle ,HttpServletResponse response) {
       if(reportlist == null || reportlist.size() <= 0) {
           //return ResponseMessage.failture(ErrorCode.REPORT_DOWNLOAD_NO_DATA);
       }
       String fileurl=null;
       HttpServletResponse createExcelReport =null;
       try {
           return Excelkit.createExcelReport(reportlist,realmName ,report_create_path, reportNames, wxcid,titleRowNo,ifTop,ifEnd, topTitle, endTitle,response);
       } catch (ExcelException e) {
           e.printStackTrace();
           //return ResponseMessage.failture(ErrorCode.REPORT_DOWNLOAD_SYS_ERR);
       }