Java导出报表

最新推荐文章于 2024-07-29 14:22:26 发布

搬砖工人1024

最新推荐文章于 2024-07-29 14:22:26 发布

阅读量738

点赞数

分类专栏： java 文章标签： java

本文链接：https://blog.csdn.net/qq_25378657/article/details/109497129

版权

java 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.场景描述

　　使用java实现导出excel报表。使用Apache POI。

　　Apache POI是Apache软件基金会的开源项目，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

2.Maven依赖


  
  
    
    
     
     
    
    
    
    
     
     
      
      <dependency>
     
     
    
    

    
    
     
     
    
    
    
    
     
         
      
      <groupId>org.apache.poi
      
      </groupId>
     
     
    
    

    
    
     
     
    
    
    
    
     
         
      
      <artifactId>poi
      
      </artifactId>
     
     
    
    

    
    
     
     
    
    
    
    
     
         
      
      <version>3.9
      
      </version>
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      </dependency>
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      <dependency>
     
     
    
    

    
    
     
     
    
    
    
    
     
          
      
      <groupId>org.apache.poi
      
      </groupId>
     
     
    
    

    
    
     
     
    
    
    
    
     
         
      
      <artifactId>poi-ooxml
      
      </artifactId>
     
     
    
    

    
    
     
     
    
    
    
    
     
         
      
      <version>3.9
      
      </version>
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      </dependency>
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      <dependency>
     
     
    
    

    
    
     
     
    
    
    
    
     
         
      
      <groupId>commons-lang
      
      </groupId>
     
     
    
    

    
    
     
     
    
    
    
    
     
         
      
      <artifactId>commons-lang
      
      </artifactId>
     
     
    
    

    
    
     
     
    
    
    
    
     
         
      
      <version>2.3
      
      </version>
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      </dependency>

3.代码编写

3.1导出报表类


  
  
    
    
     
     
    
    
    
    
     
     
      
      import com.nnkc.util.ExcelUtil;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import org.apache.commons.lang.StringUtils;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import org.apache.poi.hssf.usermodel.HSSFWorkbook;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import org.apache.poi.xssf.streaming.SXSSFWorkbook;
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import javax.servlet.http.HttpServletRequest;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import javax.servlet.http.HttpServletResponse;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import java.io.IOException;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import java.io.OutputStream;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import java.net.URLEncoder;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import java.util.List;
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      /**
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
       *功能描述：导出报表
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
       */
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      public class ReportExcel {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          /**
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * 功能: Excel导出公共方法
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * 记录条数大于50000时 导出.xlsx文件(excel07+)  小于等于50000时导出 .xls文件(excel97-03)
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * 开发：wangkecheng
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param list            需要导出的列表数据
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param title            导出文件的标题
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param className        导出对象的类名
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param exportType    针对同一个pojo可能有多个不同的导出模板时,可以通过此属性来决定导出哪一套模板，默认第一套
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param response         用来获取输出流
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param request       针对火狐浏览器导出时文件名乱码的问题,也可以不传入此值
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @throws IOException
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           */
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          public void excelExport(List list, String title, Class className, Integer exportType, HttpServletResponse response, HttpServletRequest request) throws IOException {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              OutputStream out = response.getOutputStream();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              try {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  ExcelUtil excel = new ExcelUtil();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  if(list!=null && list.size()>ExcelUtil.EXPORT_07_LEAST_SIZE){
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      dealBigNumber(list, title, className, exportType, response, request, out, excel);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  }else{
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      HSSFWorkbook hss = new HSSFWorkbook();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      if(exportType==null){
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          hss = excel.exportExcel(list,title,className,0);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      }else{
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          hss = excel.exportExcel(list, title, className, exportType);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      String disposition = "attachment;filename=";
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      if(request!=null&&request.getHeader("USER-AGENT")!=null&& StringUtils.contains(request.getHeader("USER-AGENT"), "Firefox")){
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          disposition += new String((title+".xls").getBytes(),"ISO8859-1");
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      }else{
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          disposition += URLEncoder.encode(title+".xls", "UTF-8");
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      response.setContentType("application/vnd.ms-excel;charset=UTF-8");
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      response.setHeader("Content-disposition", disposition);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      hss.write(out);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              } catch (Exception e) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  System.out.println(e.getMessage());
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              } finally {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  out.close();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          private void dealBigNumber(List list, String title, Class className, Integer exportType,
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                                     HttpServletResponse response, HttpServletRequest request, OutputStream out, ExcelUtil excel)
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  throws Exception{
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              SXSSFWorkbook hss;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              if(exportType==null){
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  hss = excel.exportExcel07S(list,title,className,0);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              }else{
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  hss = excel.exportExcel07S(list, title, className, exportType);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              String disposition = "attachment;filename=";
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              if(request!=null && request.getHeader("USER-AGENT") != null && StringUtils.contains(request.getHeader("USER-AGENT"), "Firefox")){
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  disposition += new String((title+".xlsx").getBytes(),"ISO8859-1");
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              }else{
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  disposition += URLEncoder.encode(title+".xlsx", "UTF-8");
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              response.setHeader("Content-disposition", disposition);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              hss.write(out);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      }

3.2工具类


  
  
    
    
     
     
    
    
    
    
     
     
      
      import org.apache.poi.hssf.usermodel.*;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import org.apache.poi.hssf.util.HSSFColor;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import org.apache.poi.ss.usermodel.*;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import org.apache.poi.ss.util.CellRangeAddress;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import org.apache.poi.xssf.streaming.SXSSFWorkbook;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import org.apache.poi.xssf.usermodel.XSSFRichTextString;
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import java.lang.reflect.Field;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import java.lang.reflect.InvocationTargetException;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import java.lang.reflect.Method;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import java.text.SimpleDateFormat;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import java.util.*;
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      /**
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
       * excel工具类
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
       * @author wangkecheng
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
       * @param 
      
      <T>
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
       */
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      public class ExcelUtil
      
      <T> {
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          public static final int EXPORT_07_LEAST_SIZE = 50000;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          /**
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * 功能 :获取表单导出数据
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * 开发：wangkecheng
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param list        数据列表
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param title     首行标题
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param className 实体对象class
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param exportType         模板标号
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @return
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @throws Exception
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           */
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          public HSSFWorkbook exportExcel(List
      
      <T> list, String title, Class className,Integer exportType) throws Exception {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 获取属性
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              Field[] fields = className.getDeclaredFields();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              List
      
      <Field> fieldList = new ArrayList
      
      <Field>();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              for (Field fie : fields) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  if (fie.isAnnotationPresent(ExcelAnnotation.class)) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      fieldList.add(fie);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 按照id进行排序
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              Collections.sort(fieldList, new Comparator
      
      <Field>() {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  public int compare(Field f1, Field f2) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      return f1.getAnnotation(ExcelAnnotation.class).id() - f2.getAnnotation(ExcelAnnotation.class).id();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              });
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              int columnsize = fieldList.size(), rowindex = 0;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 创建一个HSSFWorbook对象（excel的文档对象）
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              HSSFWorkbook hWorkbook = new HSSFWorkbook();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 创建一个HSSFSheet对象（excll的表单）
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              HSSFSheet hSheet = hWorkbook.createSheet();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 创建行（excel的行）
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              HSSFRow hRow = hSheet.createRow(rowindex++);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              //设置行高度
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              hRow.setHeight((short)380);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 创建单元格（从0开始）
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              HSSFCell hCell = hRow.createCell((short) 0);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              //样式对象
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              HSSFCellStyle cellStyle = getCellStyle(hWorkbook, (short) 300, (short) 500);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 将上面获得的样式对象给对应单元格
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              hCell.setCellStyle(cellStyle);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              //设置标题行
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              hCell.setCellValue(title);
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              if (getHuoResult(fieldList.isEmpty(),list == null,list.isEmpty())) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  return hWorkbook;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              //创建第二行，代表列名
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              hRow = hSheet.createRow(rowindex++);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle = getCellStyle(hWorkbook, (short) 270, (short) 500);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              generateTitle(exportType, fieldList, columnsize, hSheet, hRow, cellStyle);
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              //组装excel的数据
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle = getCellStyle(hWorkbook, (short) 220, (short) 500);// 设置单元格格式
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              generateData(list, fieldList, columnsize, rowindex, hSheet, cellStyle);
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              /**
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
               * 第1个参数：从哪一行开始
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
               * 第2个参数：到哪一行结束
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
               * 第3个参数：从哪一列开始
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
               * 第4个参数：到哪一列结束
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
               */
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              hSheet.addMergedRegion(new CellRangeAddress(0,0,0,columnsize-1));
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 固定表头（前一个参数代表列，后一个参数单表行）
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              hSheet.createFreezePane(0, 1);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              return hWorkbook;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          /**
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * 功能：組裝列明
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param exportType 模板编号
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param fieldList 列名
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param columnsize 列数
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param hSheet sheet页
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param hRow  行
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param cellStyle 样式
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           */
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          private void generateTitle(Integer exportType, List
      
      <Field> fieldList, int columnsize, HSSFSheet hSheet, HSSFRow hRow,
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  HSSFCellStyle cellStyle) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              HSSFCell hCell;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              for (int i = 0; i 
      
      < columnsize; i++) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  Field field = fieldList.get(i);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  if (field.isAnnotationPresent(ExcelAnnotation.class)) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      // 获取该字段的注解对象
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      ExcelAnnotation anno = field.getAnnotation(ExcelAnnotation.class);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      hCell = hRow.createCell((short) i);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      String colName = field.getAnnotation(ExcelAnnotation.class).name().length>exportType
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                                      ?field.getAnnotation(ExcelAnnotation.class).name()[exportType]
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                                      :field.getAnnotation(ExcelAnnotation.class).name()[0];
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      hCell.setCellValue(colName);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      hCell.setCellStyle(cellStyle);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      hSheet.setColumnWidth((short) i, (short) anno.width());
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          /**
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * 组装excel的数据
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param list 具体数据
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param fieldList 列名
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param columnsize 列数
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param rowindex 行数计数
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param hSheet sheet页
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param cellStyle 样式
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @return
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @throws NoSuchMethodException
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @throws IllegalAccessException
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @throws InvocationTargetException
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           */
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          private int generateData(List
      
      <T> list, List
      
      <Field> fieldList, int columnsize, int rowindex, HSSFSheet hSheet,
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  HSSFCellStyle cellStyle) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              HSSFRow hRow;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              HSSFCell hCell;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              for (Object model : list) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  hRow = hSheet.createRow(rowindex++);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  //获取该类
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  Class clazz = model.getClass();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  for (int i = 0; i 
      
      < columnsize; i++) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      Field field =fieldList.get(i);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      //获取方法名
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      String methodName = "get" + field.getName().substring(0, 1).toUpperCase()+field.getName().substring(1);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      Method method = clazz.getMethod(methodName);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      try {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          // 获取该字段的注解对象
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          Object result = method.invoke(model);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          hCell = hRow.createCell((short) i);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          if (result != null) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                              if (result.getClass().isAssignableFrom(Date.class)) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                                  SimpleDateFormat format = new SimpleDateFormat("yyyy年MM月dd日");
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                                  result = format.format(result);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                              }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                              hCell.setCellValue(new HSSFRichTextString(result.toString()));
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          } else {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                              hCell.setCellValue(new HSSFRichTextString("-"));
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          hCell.setCellStyle(cellStyle);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      } catch (IllegalArgumentException e) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          System.out.println(e.getMessage());
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              return rowindex;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          /**
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * 生成07格式的excel对象 使用流方式防止内存溢出
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param list
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param title
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param className
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param exportType
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @return
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @throws Exception
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           */
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          public SXSSFWorkbook exportExcel07S(List<T> list, String title, Class className,Integer exportType) throws Exception {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 获取属性
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              Field[] fields = className.getDeclaredFields();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              List
      
      <Field> fieldList = new ArrayList
      
      <Field>();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              for (Field fie : fields) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  if (fie.isAnnotationPresent(ExcelAnnotation.class)){
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      fieldList.add(fie);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 按照id进行排序
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              Collections.sort(fieldList, new Comparator
      
      <Field>() {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  public int compare(Field f1, Field f2) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      return f1.getAnnotation(ExcelAnnotation.class).id() - f2.getAnnotation(ExcelAnnotation.class).id();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              });
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              int columnsize = fieldList.size(), rowindex = 0;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 创建一个HSSFWorbook对象s
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              SXSSFWorkbook hWorkbook = new SXSSFWorkbook();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 创建一个HSSFSheet对象（sheet页）
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              Sheet hSheet =  hWorkbook.createSheet();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 创建第一行(此行作为头)
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              Row hRow = hSheet.createRow(rowindex++);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              hRow.setHeight((short)380);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 创建单元格（第一（0）个）
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              Cell hCell = hRow.createCell((short) 0);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 设置样式
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              CellStyle cellStyle = getCellStyle07S(hWorkbook, (short) 300, (short) 500);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 将上面获得的样式对象给对应单元格
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              hCell.setCellStyle(cellStyle);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              //设置标题行
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              hCell.setCellValue(title);
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              if (getHuoResult(fieldList.isEmpty(),list == null,list.isEmpty())) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  return hWorkbook;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 创建第二列，列名
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              hRow = hSheet.createRow(rowindex++);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle = getCellStyle07S(hWorkbook, (short) 270, (short) 500);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              createTitle07S(exportType, fieldList, columnsize, hSheet, hRow, cellStyle);
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              //生成数据
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle = getCellStyle07S(hWorkbook, (short) 220, (short) 500);// 设置单元格格式
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              dealCreateRow07S(list, fieldList, columnsize, rowindex, hSheet, cellStyle);
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              /**
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
               * 第1个参数：从哪一行开始
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
               * 第2个参数：到哪一行结束
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
               * 第3个参数：从哪一列开始
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
               * 第4个参数：到哪一列结束
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
               */
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              hSheet.addMergedRegion(new CellRangeAddress(0,0,0,columnsize-1));
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              // 固定表头（前一个参数代表列，后一个参数单表行）
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              hSheet.createFreezePane(0, 1);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              return hWorkbook;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          private int dealCreateRow07S(List
      
      <T> list, List
      
      <Field> fieldList, int columnsize, int rowindex, Sheet hSheet,
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  CellStyle cellStyle) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              Row hRow;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              Cell hCell;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              for (Object model : list) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  hRow = hSheet.createRow(rowindex++);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  // 获取该类 并获取自身方法
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  Class clazz = model.getClass();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  for (int i = 0; i 
      
      < columnsize; i++) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      Field field = fieldList.get(i);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      String methodName = "get" + field.getName().substring(0, 1).toUpperCase()
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      + field.getName().substring(1);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      Method method = clazz.getMethod(methodName);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      try {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          // 获取该字段的注解对象
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          Object result = method.invoke(model);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          hCell = hRow.createCell((short) i);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          if (result != null) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                              if (result.getClass().isAssignableFrom(Date.class)) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                                  SimpleDateFormat format = new SimpleDateFormat("yyyy年MM月dd日");
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                                  result = format.format(result);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                              }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                              hCell.setCellValue(new XSSFRichTextString(result.toString()));
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          } else {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                              hCell.setCellValue(new XSSFRichTextString("-"));
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          hCell.setCellStyle(cellStyle);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      } catch (IllegalArgumentException e) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                          System.out.println(e.getMessage());
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              return rowindex;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          /**
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * 生成列名
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param exportType 模板编号
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param fieldList 列名
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param columnsize 列数
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param hSheet
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param hRow
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param cellStyle
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           */
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          private void createTitle07S(Integer exportType, List<Field> fieldList, int columnsize, Sheet hSheet, Row hRow,
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  CellStyle cellStyle) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              Cell hCell;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              for (int i = 0; i 
      
      < columnsize; i++) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  Field field = (Field) fieldList.get(i);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  if (field.isAnnotationPresent(ExcelAnnotation.class)) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      // 获取该字段的注解对象
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      ExcelAnnotation anno = field.getAnnotation(ExcelAnnotation.class);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      hCell = hRow.createCell((short) i);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      String colName = field.getAnnotation(ExcelAnnotation.class).name().length>exportType
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      ?field.getAnnotation(ExcelAnnotation.class).name()[exportType]
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                                                                         :field.getAnnotation(ExcelAnnotation.class).name()[0];
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      hCell.setCellValue(colName);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      hCell.setCellStyle(cellStyle);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      hSheet.setColumnWidth((short) i, (short) anno.width());
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          /**
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * 功能 :设置excel表格默认样式
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param hWorkbook     需导出Excel数据
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param fontHeight     字体粗度
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param boldWeight    表格线的粗度
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @return
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           */
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          public HSSFCellStyle getCellStyle(HSSFWorkbook hWorkbook, short fontHeight, short boldWeight) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              HSSFCellStyle cellStyle;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              HSSFFont font;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle = hWorkbook.createCellStyle();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              font = hWorkbook.createFont();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              font.setFontHeight(fontHeight);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              font.setBoldweight(boldWeight);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              font.setFontName("宋体");
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setFont(font);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setWrapText(true);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              return cellStyle;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          /**
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * 功能 :设置excel 07表格默认样式
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param hWorkbook     需导出Excel数据
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param fontHeight     字体粗度
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @param boldWeight    表格线的粗度
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * @return
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           */
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          public CellStyle getCellStyle07S(SXSSFWorkbook hWorkbook, short fontHeight, short boldWeight) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              CellStyle cellStyle;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              Font font;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle = hWorkbook.createCellStyle();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              font = hWorkbook.createFont();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              font.setFontHeight(fontHeight);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              font.setBoldweight(boldWeight);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              font.setFontName("宋体");
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setFont(font);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setWrapText(true);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              return cellStyle;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          /*
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           * 获取或运算结果
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           */
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          private static boolean getHuoResult(Boolean... bs){
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              for(boolean b:bs){
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  if(b){
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                      return b;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              return false;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
           }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      }

复制代码

3.3注解类


  
  
    
    
     
     
    
    
    
    
     
     
      
      import java.lang.annotation.*;
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      /**
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
       * 
      
      <p>
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
       *  功能：excel模板设置
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
       * 
      
      </p>
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
       */
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      @Documented  
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      @Retention(RetentionPolicy.RUNTIME)  
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      @Target(ElementType.FIELD)
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      public @interface ExcelAnnotation {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          //Excel列ID(Excel列排序序号)
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          int id();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          //Excel列名
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          String[] name();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          //Excel列宽
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          int width() default 5000;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      }

3.4pojo对象


  
  
    
    
     
     
    
    
    
    
     
     
      
      import com.nnkc.util.ExcelAnnotation;
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import java.util.Date;
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      /**
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
       * Created by chengcheng on 2017/12/5.
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
       */
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      public class Product {
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          @ExcelAnnotation(id=1,name={"产品名称","商品名称"},width = 5000)
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          private String name;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          @ExcelAnnotation(id=2,name={"产品价格","商品价格"},width = 5000)
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          private double price;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          @ExcelAnnotation(id=3,name={"生产日期"},width = 5000)
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          private Date date;
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          public String getName() {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              return name;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          public void setName(String name) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              this.name = name;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          public double getPrice() {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              return price;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          public void setPrice(double price) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              this.price = price;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          public Date getDate() {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              return date;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          public void setDate(Date date) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              this.date = date;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      }

3.5测试类


  
  
    
    
     
     
    
    
    
    
     
     
      
      import com.nnkc.pojo.Product;
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import javax.servlet.ServletException;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import javax.servlet.http.HttpServlet;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import javax.servlet.http.HttpServletRequest;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import javax.servlet.http.HttpServletResponse;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import java.io.IOException;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import java.util.ArrayList;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import java.util.Date;
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      import java.util.List;
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      /**
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
       * Created by chengcheng on 2017/12/5.
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
       */
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      public class ReportExcelTest extends HttpServlet {
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          @Override
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              System.out.println("调用成功");
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          @Override
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              List
      
      <Product> list = new ArrayList
      
      <Product>();
     
     
    
    

    
    
     
     
    
    
    
    
     
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              for (int i = 0 ; i
      
      <60000 ; i++) {
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  //组装测试数据
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  Product product = new Product();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  product.setName("爱奇艺会员"+i);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  product.setPrice(9.99);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  product.setDate(new Date());
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
                  list.add(product);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              ReportExcel reportExcel = new ReportExcel();
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
              reportExcel.excelExport(list,"测试",Product.class,1,resp,req);
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
          }
     
     
    
    

    
    
     
     
    
    
    
    
     
     
      
      }

3.5测试结果

搬砖工人1024

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Java导出报表

1.场景描述　　使用java实现导出excel报表。使用Apache POI。　　Apache POI是Apache软件基金会的开源项目，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。 2.Maven依赖   <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</a...
复制链接

扫一扫

专栏目录