java 数据库 对象 导出Excel数据

工具类：封装了两个方法：非web项目方法和web项目方法，

package util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID;
/**
 * @Author： Lee
 * @Time： 2017/12/26 11:53
 * @Description： 导出List<Object>数据到excel（最多可导出65535行）
 */
public final class Obj2ExcelUtils {

    private static Logger log = LoggerFactory.getLogger(Obj2ExcelUtils.class);
    private static final String LOGPRE = "Obj2ExcelUtils:";

    /***
     * 构造方法
     */
    private Obj2ExcelUtils() {

    }

    /***
     * 工作簿
     */
    private static HSSFWorkbook workbook;

    /***
     * sheet
     */
    private static HSSFSheet sheet;
    /***
     * 标题行开始位置
     */
    private static final int TITLE_START_POSITION = 0;

    /***
     * 时间行开始位置
     */
    private static final int DATEHEAD_START_POSITION = 1;

    /***
     * 表头行开始位置
     */
    private static final int HEAD_START_POSITION = 2;

    /***
     * 文本行开始位置
     */
    private static final int CONTENT_START_POSITION = 3;


    /**
     * @param dataList  对象集合
     * @param titleMap  表头信息（对象属性名称->要显示的标题值)[按顺序添加]
     * @param sheetName sheet名称和表头值
     */
    public static void excelExport(List<?> dataList, Map<String, String> titleMap, String sheetName) {
        // 初始化workbook
        initHSSFWorkbook(sheetName);
        // 标题行
        createTitleRow(titleMap, sheetName);
        // 时间行
        createDateHeadRow(titleMap);
        // 表头行
        createHeadRow(titleMap);
        // 文本行
        createContentRow(dataList, titleMap);
        //设置自动伸缩
        //autoSizeColumn(titleMap.size());
        // 写入处理结果
        try {
            //生成UUID文件名称
            UUID uuid = UUID.randomUUID();
            String fileDisplay = uuid + ".xls";
            //如果web项目，1、设置下载框的弹出（设置response相关参数)；2、通过httpServletResponse.getOutputStream()获取
            OutputStream out = new FileOutputStream("D:\\" + fileDisplay);
            workbook.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * web项目下载
     * @param dataList  对象集合
     * @param titleMap  表头信息（对象属性名称->要显示的标题值)[按顺序添加]
     * @param sheetName sheet名称和表头值
     */
    public static InputStream excelExportWeb(List<?> dataList, Map<String, String> titleMap, String sheetName) {
        // 初始化workbook
        initHSSFWorkbook(sheetName);
        // 标题行
        // createTitleRow(titleMap, sheetName);
        // 时间行
        // createDateHeadRow(titleMap);
        // 表头行
        createHeadRow(titleMap);
        // 文本行
        createContentRow(dataList, titleMap);
        //设置自动伸缩
        // autoSizeColumn(titleMap.size());
        // 写入处理结果
        ByteArrayOutputStream os = null;
        try {
            if (workbook == null) {
                log.error(LOGPRE + "HSSFWorkbook为空");
                return null;
            }
            // 将文件存到流中
            os = new ByteArrayOutputStream();
            workbook.write(os);
            byte[] fileContent = os.toByteArray();
            return new ByteArrayInputStream(fileContent);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    log.error(LOGPRE + e.getMessage(), e);
                }
            }
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    log.error(LOGPRE + e.getMessage(), e);
                }
            }
            log.info("导出结束时间："+new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date(System.currentTimeMillis())));
        }
        return null;
    }

    /***
     *
     * @param sheetName
     *        sheetName
     */
    private static void initHSSFWorkbook(String sheetName) {
        workbook = new HSSFWorkbook();
        sheet = workbook.createSheet(sheetName);
    }

    /**
     * 生成标题（第零行创建）
     *
     * @param titleMap  对象属性名称->表头显示名称
     * @param sheetName sheet名称
     */
    private static void createTitleRow(Map<String, String> titleMap, String sheetName) {
        CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, titleMap.size() - 1);
        sheet.addMergedRegion(titleRange);
        HSSFRow titleRow = sheet.createRow(TITLE_START_POSITION);
        HSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(sheetName);
    }

    /**
     * 创建时间行（第一行创建）
     *
     * @param titleMap 对象属性名称->表头显示名称
     */
    private static void createDateHeadRow(Map<String, String> titleMap) {
        CellRangeAddress dateRange = new CellRangeAddress(1, 1, 0, titleMap.size() - 1);
        sheet.addMergedRegion(dateRange);
        HSSFRow dateRow = sheet.createRow(DATEHEAD_START_POSITION);
        HSSFCell dateCell = dateRow.createCell(0);
        dateCell.setCellValue(new SimpleDateFormat("yyyy年MM月dd日").format(new Date()));
    }

    /**
     * 创建表头行（第二行创建）
     *
     * @param titleMap 对象属性名称->表头显示名称
     */
    private static void createHeadRow(Map<String, String> titleMap) {
        // 第2行创建
        // HSSFRow headRow = sheet.createRow(HEAD_START_POSITION);

        //不加标题和时间行，第0行创建
        HSSFRow headRow = sheet.createRow(0);
        int i = 0;
        for (String entry : titleMap.keySet()) {
            HSSFCell headCell = headRow.createCell(i);
            headCell.setCellValue(titleMap.get(entry));
            i++;
        }
    }

    /**
     * @param dataList 对象数据集合
     * @param titleMap 表头信息
     */
    private static void createContentRow(List<?> dataList, Map<String, String> titleMap) {
        try {
            int i = 0;
            for (Object obj : dataList) {
                // HSSFRow textRow = sheet.createRow(CONTENT_START_POSITION + i);
                // 不加标题和时间行，第1行创建
                HSSFRow textRow = sheet.createRow(1 + i);
                int j = 0;
                for (String entry : titleMap.keySet()) {
                    String method = "get" + entry.substring(0, 1).toUpperCase() + entry.substring(1);
                    Method m = obj.getClass().getMethod(method, null);
                    String value = m.invoke(obj, null).toString();
                    HSSFCell textcell = textRow.createCell(j);
                    textcell.setCellValue(value);
                    j++;
                }
                i++;
            }

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

    /**
     * 自动伸缩列（如非必要，请勿打开此方法，耗内存）
     *
     * @param size 列数
     */
    private static void autoSizeColumn(Integer size) {
        for (int j = 0; j < size; j++) {
            sheet.autoSizeColumn(j);
        }
    }



}

测试案例1：

 public String exportApply() {
        return "exportApply";
    }

 public InputStream getExportApply() throws Exception {

        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        logger.info("导出开始时间："+sdf.format(new Date(System.currentTimeMillis())));

        StringBuffer sql = new StringBuffer();
        sql.append("select a.*,b.`name` AS provName,c.`name` AS cityName " +
                "from goods_business_apply a left JOIN city b on a.provId=b.id " +
                "LEFT JOIN city c on a.cityId=c.id where 1=1 ");
         /***********这里拼接自己的sql查寻************/
        sql.append(" order by a.logTime desc");

        Query query = new Query(sql.toString());

        List<Employee> list = GoodsService.employeeDao.listAllBySql(query);

        logger.info("数据查询结束时间："+sdf.format(new Date(System.currentTimeMillis())));

        fileName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        setFileName(fileName);

        Map<String,String> titleMap = new LinkedHashMap<String,String>();
            titleMap.put("name", "姓名");
            titleMap.put("clazz", "组号");
            titleMap.put("year", "年份");
            titleMap.put("month", "月份");
            titleMap.put("day", "天");
            titleMap.put("salary", "薪资");

        return Obj2ExcelUtils.excelExportWeb(list, titleMap, fileName);
    }

测试案例2：

mport java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;


public class CustomerExportTest {

  /**
   * @param args
   */
  public static void main(String[] args) {

    /**模拟数据开始*/
    List<Employee> staffs = new ArrayList<Employee>();
    for (int i = 0; i < 65532; i++) {
      Employee staff = new Employee(i, i+"group", 1900+i, 12, 25, 2500+i);
      staffs.add(staff);
    }
    Map<String,String> titleMap = new LinkedHashMap<String,String>();
    titleMap.put("name", "姓名");
    titleMap.put("clazz", "组号");
    titleMap.put("year", "年份");
    titleMap.put("month", "月份");
    titleMap.put("day", "天");
    titleMap.put("salary", "薪资");
    String sheetName = "信息导出";
    /**模拟数据结束*/

    System.out.println("start导出");
    long start = System.currentTimeMillis();
    ExportExcel.excelExport(staffs, titleMap, sheetName);
    long end = System.currentTimeMillis();
    System.out.println("end导出");
    System.out.println("耗时："+(end-start)+"ms");
  }

}

类对象：

public class Employee {

  private Integer name;

  private String clazz;

  private Integer year;

  private Integer month;

  private Integer day;

  private double salary;

  public Employee() {
  };

  public Employee(Integer name, String clazz, Integer year, Integer month, Integer day,
      double salary) {
    super();
    this.name = name;
    this.clazz = clazz;
    this.year = year;
    this.month = month;
    this.day = day;
    this.salary = salary;
  }

  /**
   * @return name
   */
  public Integer getName() {
    return name;
  }

  /**
   * @return year
   */
  public Integer getYear() {
    return year;
  }

  /**
   * @return month
   */
  public Integer getMonth() {
    return month;
  }

  /**
   * @return day
   */
  public Integer getDay() {
    return day;
  }

  /**
   * @return salary
   */
  public double getSalary() {
    return salary;
  }

  /**
   * @param name
   *        set name
   */
  public void setName(Integer name) {
    this.name = name;
  }

  /**
   * @param year
   *        set year
   */
  public void setYear(Integer year) {
    this.year = year;
  }

  /**
   * @param month
   *        set month
   */
  public void setMonth(Integer month) {
    this.month = month;
  }

  /**
   * @param day
   *        set day
   */
  public void setDay(Integer day) {
    this.day = day;
  }

  /**
   * @param salary
   *        set salary
   */
  public void setSalary(double salary) {
    this.salary = salary;
  }

  /**
   * @return clazz
   */
  public String getClazz() {
    return clazz;
  }

  /**
   * @param clazz
   *        set clazz
   */
  public void setClazz(String clazz) {
    this.clazz = clazz;
  }

  @Override
  public String toString() {
    return "Employee [name=" + name + ", clazz=" + clazz + ", year=" + year + ", month=" + month
        + ", day=" + day + ", salary=" + salary + "]";
  }

}