使用poi导出数据到Excel中

       最近做报表导出功能,网上找发现很多导出功能都是将固定类型作为参数,如果需要导出不同表格,不同数据库表的数据则代码有很大部分重复率,考虑将泛型作为参数进行导出.话不多说请看代码~

  maven 相关依赖

    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.15</version>
    </dependency>
    
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml-schemas</artifactId>
      <version>3.15</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.15</version>
    </dependency>

 工具类

注意:

1.此工具类并没有提供Excel样式,如有需求请自行设置.

package com.netcar.common;

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

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.List;

/**
 * Created by 小xiong on 2019/4/22 0022.
 */
public class ExportToExcel {
    private Logger logger = Logger.getLogger(this.getClass().getName());
    private static final int rownum = 10000;

    /**
     * @param response          设置header以及编码格式等
     * @param title              导出的Excel的标题
     * @param headers            要导出哪些字段到Excel里面,需要手动指定
     * @param list               导出哪些数据到Excel里面,为一个list集合
     * @param listColumn        此处是因为反射调用泛型的get方法获取数据设定的参数,参数为header中每个字段在po类中对应的属性名称,
     * @param <T>                泛型,可导出任何类型的数据到Excel里面
     * @return                   返回一个表格类型
     * @throws Exception
     */
    public static<T>  void  exportExcel(HttpServletResponse response,String title, String[] headers, List<T> list,List<String> listColumn) throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        for (int m = 0; m < (list.size() + rownum - 1) / rownum; m++){
            HSSFSheet sheet = wb.createSheet(title + (m + 1));
            // 第一列单元格宽度设置为20个字符宽度
/*            sheet.setColumnWidth(0, 20 * 256);
            sheet.setColumnWidth(1, 20 * 256);
            sheet.setColumnWidth(2, 30 * 256);*/
            HSSFRow row = sheet.createRow(0);
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //根据header设置第一行的内容
            for (int i = 0; i < headers.length; i++){
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(style);
            }
            //为每一行的表格赋值
            for (int i = m * rownum; i < (m + 1) * rownum && i < list.size(); i++){
                row = sheet.createRow(i % rownum + 1);
                T it = list.get(i);
                //根据字段名获取get方法
                for (int k = 0;k<listColumn.size();k++){
                    Field field = it.getClass().getDeclaredField(listColumn.get(k));
                    field.setAccessible(true);
                    String o = (String) field.get(it);
                    row.createCell(k).setCellValue(o);
                }
            }
        }
        //在浏览器打印表格
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        //attachment 和  inline
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(title + ".xls", "UTF8"));
        OutputStream ouputStream = response.getOutputStream();
        wb.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
        if (wb != null) {
            wb.close();
        }
    }

    }

 Controller 层代码

注意:

1.list为将要导出的数据集合,如要使用可将 Wqzryxx_Qk 替换为需要导出的数据类型.

2.headers 为表头内容,即在Excel表格第一行显示的内容,listColumn 为headers 中表头在实体类中对应的属性名的集合.严格区分大小写.

3.如果需要导出复杂的比如说经过计算之后的数据,统计平均或者总和,需要构建虚拟表并在实体类中定义属性名,并提供get,set方法.

@RequestMapping(value = "/daochuw", produces = "text/html;charset=UTF-8")
    @ResponseBody
    public String daochuw(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //获取要导出的数据
        List<Wqzryxx_Qk> list = (List<Wqzryxx_Qk>) request.getSession().getAttribute("wqzlist");
        //设置要导出的内容在po类中对应的属性
        List<String> listColumn = Arrays.asList("name", "sfzh", "phone", "pxzl", "pxqc", "bmrq", "ksrq", "cycj","zgzh","dqjd","qklx");
        String title = "有前科未取证人员";
        //设置要导出哪些字段
        String[] headers = {"姓名", "身份证号码", "电话号码", "培训种类", "培训期次", "报名日期", "考试日期", "从业成绩", "资格证号","当前节点","违法类别"};
        try {
            ExportToExcel.exportExcel(response,title, headers, list, listColumn);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }