excel工具类

根据传入的list和表头Map 生成excel文件。

package com.suning.frms.utils;

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

import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @Author: chenxuan
 * @Date: 2019/9/11 15:00
 */
public class ExcelUtils {

    /**
     * xls 文件生成
     */
    public static <T> HSSFWorkbook exportXls(List<T> paramList, Map<String, Object> beanMap) {
        //
        List<Map<String, Object>> mapList = BeanMapUtils.objectsToMaps(paramList);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        // 创建sheet页
        HSSFSheet sheet = hssfWorkbook.createSheet("统计表");
        // 创建表头
        createXlsTitle(sheet, beanMap);
        // 设置日期格式
        HSSFCellStyle dateStyle = hssfWorkbook.createCellStyle();
        dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy/mm/dd hh:mm"));
        // 创建各行数据
        for (int i = 0; i < mapList.size(); i++) {
            Map<String, Object> map = mapList.get(i);
            if (map == null) {
                continue;
            }
            HSSFRow row = sheet.createRow(i + 1);

            Set<String> set = beanMap.keySet();
            Iterator it = set.iterator();
            int j = 0;
            while (it.hasNext()) {
                String title = (String) it.next();
                row.createCell(j).setCellValue(String.valueOf(map.get(title)));
                j++;
            }
        }
        return hssfWorkbook;
    }

    /**
     * xls 创建表头
     *
     * @param sheet
     * @param beanMap
     */
    public static void createXlsTitle(HSSFSheet sheet, Map<String, Object> beanMap) {
        Set<String> set = beanMap.keySet();
        HSSFRow row = sheet.createRow(0);
        Iterator it = set.iterator();
        int i = 0;
        while (it.hasNext()) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString((String) it.next());
            cell.setCellValue(text);
            i++;
        }
    }

    /**
     * xlsx 文件生成
     * @param paramList
     * @param beanMap
     * @param <T>
     * @return
     */
    public static <T> XSSFWorkbook exportXlsx(List<T> paramList, Map<String, Object> beanMap) {
        List<Map<String, Object>> mapList = BeanMapUtils.objectsToMaps(paramList);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        // 创建sheet页
        XSSFSheet sheet = xssfWorkbook.createSheet();
        // 创建表头
        createXlsxTitle(sheet, beanMap);
        // 创建各行数据
        for (int i = 0; i < mapList.size(); i++) {
            Map<String, Object> map = mapList.get(i);
            if (map == null) {
                continue;
            }
            XSSFRow row = sheet.createRow(i + 1);

            Set<String> set = beanMap.keySet();
            Iterator it = set.iterator();
            int j = 0;
            while (it.hasNext()) {
                String title = (String) it.next();
                row.createCell(j).setCellValue(String.valueOf(map.get(title)));
                j++;
            }
        }
        return xssfWorkbook;
    }

    /**
     * xlsx 文件头
     * @param sheet
     * @param beanMap
     */
    public static void createXlsxTitle(XSSFSheet sheet, Map<String, Object> beanMap) {
        Set<String> set = beanMap.keySet();
        XSSFRow row = sheet.createRow(0);
        Iterator it = set.iterator();
        int i = 0;
        while (it.hasNext()) {
            XSSFCell cell = row.createCell(i);
            XSSFRichTextString text = new XSSFRichTextString((String) it.next());
            cell.setCellValue(text);
            i++;
        }
    }

用到的beanMap 

/**
 * 将List<T>转换为List<Map<String, Object>>
 *
 * @param objList
 * @return
 */
public static <T> List<Map<String, Object>> objectsToMaps(List<T> objList) {
    List<Map<String, Object>> list = new ArrayList<>();
    if (objList != null && objList.size() > 0) {
        Map<String, Object> map = null;
        T bean = null;
        for (int i = 0, size = objList.size(); i < size; i++) {
            bean = objList.get(i);
            map = beanToMap(bean);
            list.add(map);
        }
    }
    return list;
}

/**
 * 将对象装换为map
 *
 * @param bean
 * @return
 */
public static <T> Map<String, Object> beanToMap(T bean) {
    Map<String, Object> map = new HashMap<>();
    if (bean != null) {
        BeanMap beanMap = BeanMap.create(bean);
        for (Object key : beanMap.keySet()) {
            map.put(key + "", beanMap.get(key));
        }
    }
    return map;
}

后来map要求传value值为中文名称，第一行构造修改为 

/**
* xls 创建表头
*
* @param sheet
* @param beanMap
*/
public static void createXlsTitle(HSSFSheet sheet, Map<String, Object> beanMap) {
HSSFRow row = sheet.createRow(0);
int i = 0;
for(Map.Entry<String,Object> entry:beanMap.entrySet()){
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString((String) entry.getValue());
cell.setCellValue(text);
i++;
}
}/**
* xlsx 文件头
* @param sheet
* @param beanMap
*/
public static void createXlsxTitle(XSSFSheet sheet, Map<String, Object> beanMap) {
XSSFRow row = sheet.createRow(0);
int i = 0;
for(Map.Entry<String,Object> entry:beanMap.entrySet()){
XSSFCell cell = row.createCell(i);
XSSFRichTextString text = new XSSFRichTextString((String) entry.getValue());
cell.setCellValue(text);
i++;
}
}

测试用例

Map<String, Object> param = new HashMap<>();
param.put("startIndex",0);
param.put("pageSize",100);
List<SnSystem> list = getList(param);//从数据库取出的信息

Map<String, Object> map = new HashMap<>();
map.put("systemId", "主键id");
map.put("systemNum", "系统编码");

XSSFWorkbook xssfWorkbook = ExcelUtils.exportXlsx(list,map);
try {
    response.setCharacterEncoding("UTF-8");
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-Disposition", "attachment;fileName=" + System.currentTimeMillis() + ".xls");
    OutputStream outputStream = response.getOutputStream();
    xssfWorkbook.write(outputStream);
    outputStream.close();
}catch (Exception e){
    e.printStackTrace();
}