java导出EXCEL表格

• Controller

     @PostMapping("/exportInfo")
   	@ResponseBody
       public Rest exportInfo(HttpServletRequest request, HttpServletResponse response,
   							  String queryRequest) {
   		QueryCondition query = JSON.parseObject(queryRequest, QueryCondition.class);
   		List<Info> pageResponse = infoService.findPageToReport(query);
   		ReportHandle handle = new ReportHandle();
   		TwoTuple<List<ExportColBean>, List<List<ExportCellBean>>> report = handle.getReport(pageResponse);
   		HSSFWorkbook hssfWorkbook = PoiUtils.createWorkBook(report.getLeft(), report.getRight());
   		try {
   			PoiUtils.exportExcel(response,hssfWorkbook,"EXCEL详情");
   		} catch (Exception e) {
   			e.printStackTrace();
   		}
   		return Rest.ok();
       }
  

• JAS库存导出列的配置

    public class reportHandle {
   	 private List<ExportColBean> getReportTitle() {
       List<ExportColBean> title = new ArrayList<>();
       title.add(new ExportColBean("expiryDate","到期日","expiryDate",
               "到期日",false,"",false,ExportColBean.STRING));
  
       title.add(new ExportColBean("itemType","类别","itemType",
               "itemType",false,"",false,ExportColBean.STRING));
  
       title.add(new ExportColBean("memberPrice","会员价","memberPrice",
               "memberPrice",false,"",false,ExportColBean.DATA_FORMAT,"#,##0"));
       return title;
   }
  
   public TwoTuple<List<ExportColBean>,List<List<ExportCellBean>>> getReport(List<Info> jas) {
       List<ExportColBean> jasTitle = getJasReportTitle();
       List<List<ExportCellBean>> values = new ArrayList<>();
       jas.forEach(o ->{
           List<ExportCellBean> value = jasTitle.stream().map(title -> {
               ExportCellBean bean = new ExportCellBean();
               bean.setIsDelicate(title.getIsDelicate());
               bean.setType(title.getType());
               bean.setFormula(title.getFormula());
               bean.setDataFormat(title.getDataFormat());
               bean.setValue(String.valueOf(ReflectionUtils.getFieldValue(o, title.getFieldName())));
               return bean;
           }).collect(Collectors.toList());
           values.add(value);
       });
       return new TwoTuple(jasTitle,values);
   }
   /**
    * 直接读取对象属性值, 无视private/protected修饰符, 不经过getter函数.
    */
   public static Object getFieldValue(final Object obj, final String fieldName) {
       Field field = getAccessibleField(obj, fieldName);
  
       if (field == null) {
           //throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + obj + "]");
           return null;
       }
  
       Object result = null;
       try {
           result = field.get(obj);
       } catch (IllegalAccessException e) {
           logger.error("不可能抛出的异常{}", e.getMessage());
       }
       return result;
   }
  

• 表格和文档的操作

   public class PoiUtils {
       public static HSSFWorkbook createWorkBook(List<ExportColBean> title,List<List<ExportCellBean>> values) {
           HSSFWorkbook wb = new HSSFWorkbook();
           HSSFSheet sheet = wb.createSheet("sheet1");
           HSSFRow row = sheet.createRow((int) 0);
           for (int i = 0; i < title.size(); i++) {
               row.createCell(i).setCellValue(title.get(i).getTitle2());
           }
           HSSFCellStyle numStyle = ExcelUtil.getCellNumStyle(wb);
           HSSFCellStyle numRed = ExcelUtil.getCellRedStyle(wb, ExcelUtil.getCellNumStyle(wb));
           HSSFCellStyle eurStyle = ExcelUtil.getCellEurStyle(wb);
           HSSFCellStyle numStyle1 = ExcelUtil.getCell100NumStyle(wb);
   
           HSSFCellStyle red = ExcelUtil.getCellRedStyle(wb, null);
           for (int i = 0; i < values.size(); i++) {
               Integer rowIndex = 1 + i;
               setCellValueMethod(wb,sheet,values,rowIndex,i,red,numRed,eurStyle,numStyle1);
           }
           wb.setForceFormulaRecalculation(true);
           return wb;
       }
   	/**
        * 导出Excel
        * @param response
        * @param workbook
        * @param wbName
        * @throws Exception
        */
       public static void exportExcel(HttpServletResponse response,HSSFWorkbook workbook,String wbName) throws Exception {
           response.setContentType("application/msexcel; charset=utf-8");
           response.setHeader("Content-Disposition", "attachment; filename=\"" + new String((wbName + ".xls").getBytes("GBK"),"ISO-8859-1") +"\"");
           OutputStream ouputStream = response.getOutputStream();
           workbook.write(ouputStream);
           ouputStream.flush();
           ouputStream.close();
       }
    }
  

• 实体类

     @Data			
     public class TwoTuple<A,B> {
   
       private A left;
       private B right;
     }
  
   /**
    * 用来接收需要导出的列属性的对象
    */
   @Data
   public class ExportColBean {
       public static String NUM = "num";
       public static String DATE = "date";
       public static String STRING = "String";
       public static String NUM100 = "num100";
       public static String FORMULA = "Formula";
       public static String DATA_FORMAT = "dataFormat";
       public static String EUR_FORMAT = "eurFormat";
       public static String NUM100_FORMAT = "num100Format";
       /**
        * 标题1
        */
       private String title1;
       /**
        * 标题2
        */
       private String title2;
       /**
        * 字段名称
        */
       private String fieldName;
       /**
        * 表名
        */
       private String tableName;
       /**
        * 排序字段
        */
       private Boolean sortable;
       /**
        * 排序字段
        */
       private String order;
       /**
        * 类型 日期，字符串，公式
        */
       private String type;
       /**
        * 是否敏感
        */
       private Boolean isDelicate;
       /**
        * 假如是公式列的话启用的公式
        */
       private AbstractFormula formula;
       /**
        * 数据格式化
        */
       private String dataFormat;
   
       public ExportColBean(String title1, String title2, String fieldName, String tableName, Boolean sortable,
                            String order, Boolean isDelicate,String type) {
           this.title1 = title1;
           this.title2 = title2;
           this.fieldName = fieldName;
           this.tableName = tableName;
           this.sortable = sortable;
           this.order = order;
           this.isDelicate = isDelicate;
           this.type = type;
       }
       public ExportColBean(String title1, String title2, String fieldName, String tableName, Boolean sortable,
                            String order, Boolean isDelicate,String type,String dataFormat) {
           this.title1 = title1;
           this.title2 = title2;
           this.fieldName = fieldName;
           this.tableName = tableName;
           this.sortable = sortable;
           this.order = order;
           this.isDelicate = isDelicate;
           this.type = type;
           this.dataFormat = dataFormat;
       }
       public ExportColBean() {
       }
       public ExportColBean setFormula(AbstractFormula formula) {
           this.formula = formula;
           return this;
       }
  
  
   @Data
   public class ExportCellBean {
   	 /**
   	  * 类别
   	  */
   	 private String type;
   	 /**
   	  * 值
   	  */
   	 private String value;
   	 /**
   	  * 是否敏感
   	  */
   	 private Boolean isDelicate;
   	 /**
   	  * 假如是公式列的话启用的公式
   	  */
   	 private AbstractFormula formula;
   	 /**
   	  * 数据格式化
   	  */
   	 private String dataFormat;
   }
  

• 依赖

   <dependency>
           <groupId>org.apache.poi</groupId>
           <artifactId>poi</artifactId>
           <version>3.17</version>
       </dependency>
       <dependency>
           <groupId>org.apache.poi</groupId>
           <artifactId>poi-ooxml</artifactId>
           <version>3.17</version>
       </dependency>
  
       <dependency>
           <groupId>fr.opensagres.xdocreport</groupId>
           <artifactId>org.apache.poi.xwpf.converter.core</artifactId>
           <version>1.0.5</version>
       </dependency>
       <dependency>
           <groupId>fr.opensagres.xdocreport</groupId>
           <artifactId>org.apache.poi.xwpf.converter.pdf</artifactId>
           <version>1.0.5</version>
       </dependency>