02.Java-POI-Excel报表导出-以文件下载路径返回页面供下载

最新推荐文章于 2024-04-21 23:14:32 发布

置顶

卡斌引尖

最新推荐文章于 2024-04-21 23:14:32 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： Excel报表导出文章标签： Excel Java POI 自定义注解报表导出

本文链接：https://blog.youkuaiyun.com/TK305/article/details/89240464

本文介绍了如何使用Java的POI库来创建Excel报表，并通过注解自定义实体类属性，实现数据导出。内容涵盖了实体类的注解配置、导出属性的排序以及在Action和Controller中调用工具类返回文件流，让页面自动下载Excel文件。同时，展示了JSP页面使用Ajax处理下载请求的示例代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

01.实体类注解

package com.zhuzher.annotations;

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.实体类主键注解

package com.zhuzher.annotations;

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.实体类需要导出的属性注解

package com.zhuzher.annotations;

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.实体类需要导出的属性排序注解

package com.zhuzher.annotations;

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需要导出的报表实体类

package com.zhuzher.vxs.po;

import com.zhuzher.annotations.ExcelStatisticIdentifier;
import com.zhuzher.annotations.VerticalAxis;
import com.zhuzher.annotations.VerticalAxisIndex;
import com.zhuzher.annotations.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 ;
       }
       String fileurl=null;
       try {
           fileurl = Excelkit.createExcelReport(reportThirdList,realmName ,report_create_path, ReportNames.SCENPLAN_NAME, sessionWxcid,0,false,false, null, null);
           log.debug("8888888888999999999999----------------------------------------------------------------------");
       } catch (ExcelException e) {
           e.printStackTrace();
           map.put("code",HttpStatusCode.ERROR);
           map.put("data", "当前服务器繁忙，请稍后尝试");
           Tools.writeJson(response, map);
           return ;
       }
       log.debug("wxcid:"+sessionWxcid+";分享提取报表-下载文件路径："+fileurl);
       map.put("code",HttpStatusCode.SUCCESS);
       map.put("data", fileurl);
       Tools.writeJson(response, map);
   }

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

1.controller层

@RequestMapping("/report/download")
@ResponseBody
public ResponseMessage downloadReport(String wxcid,String sdate, String edate,Integer activityid,HttpServletResponse response){

//这里是一个需要导出的数据集合cutPriceReportList，此处具体内容省略

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

2.service层

package com.zhuzher.excel.service.impl;

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.zhuzher.ErrorCode;
import com.zhuzher.ResponseMessage;
import com.zhuzher.excel.service.ExcelReportService;
import com.zhuzher.excel.util.Excelkit;
import com.zhuzher.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> ResponseMessage 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;
       try {
           fileurl=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);
       }
       log.debug("wxcid:"+wxcid+";报表-下载文件路径："+fileurl);
       return ResponseMessage.success(fileurl);
   }

public String getReport_cre