POI导出Excel(用户自己选择路径)

项目开发的后台管理系统需要对一些数据进行统计，此时也有可能导出一些报表信息等。本人开发中遇到了一些统计信息的Excel导出。
之前在网上查到一些写好的POI导出Excel导出工具类，但是好多不能使用或者是在项目开发中的借鉴版本（例如：导出路径写死等）。当然，我的也是借鉴版本，少了对导出后Excel表格等的渲染，用户简单的数据导出，但是用户可以自己选择路径。

1.POI导出Excel，用到的依赖

 <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.0.1</version>
    </dependency>
      <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>3.9</version>
    </dependency>

2.excel工具类

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 javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
public class ExportExcel {
private ExportExcel() {
}
/***
 * 工作簿
 */
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 String excelExport(List<?> dataList, Map<String, String> titleMap, String sheetName, HttpServletRequest request, HttpServletResponse response)throws Exception {
    Date date = new Date();

    String message = "fail";
    String dateString = DateUtils.dateToString(date);
    // 初始化workbook
    initHSSFWorkbook(sheetName);
    // 标题行
    createTitleRow(titleMap, sheetName);
    // 时间行
    createDateHeadRow(titleMap);
    // 表头行
    createHeadRow(titleMap);
    // 文本行
    createContentRow(dataList, titleMap);
    //设置自动伸缩
    //autoSizeColumn(titleMap.size());
    // 写入处理结果
    String dir = request.getSession().getServletContext().getRealPath("/output");
    File fileLocation = new File(dir);
    if (!fileLocation.exists()) {
        boolean isCreated = fileLocation.mkdir();
        if (!isCreated) {
        }
    }
    String webUrl = request.getSession().getServletContext().getRealPath("/output");
    String createExcelname =sheetName+"_"+dateString+".xls";
    String outputFile = webUrl + File.separator + createExcelname;
    FileOutputStream fOut = new FileOutputStream(outputFile);
    workbook.write(fOut);
    fOut.flush();
    fOut.close();
    File f = new File(outputFile);
    if (f.exists() && f.isFile()) {
        try {
            FileInputStream fis = new FileInputStream(f);
            URLEncoder.encode(f.getName(), "utf-8");
            byte[] b = new byte[fis.available()];
            fis.read(b);
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(createExcelname, "utf-8"));
            ServletOutputStream out = response.getOutputStream();
            out.write(b);
            out.flush();
            out.close();
            if (fis != null) {
                fis.close();
            }
            f.delete();
            message = "success";
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    return message;
}

/***
 *
 * @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) {
    // 第1行创建
    HSSFRow headRow = sheet.createRow(HEAD_START_POSITION);
    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);
            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);
                Object value = m.invoke(obj, null);
                HSSFCell textcell = textRow.createCell(j);
                if(ObjectUtils.isNotEmpty(value)){
                    textcell.setCellValue(value.toString());
                }else {
                    textcell.setCellValue("");
                }
                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);
    }
}
}

3.时间工具类

public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

/**
 * 字符串格式化为日期
 * @throws ParseException 
 */
public static Date stringToDate(String date) throws ParseException {
	return sdf.parse(date);
}

/**
 * 日期格式化为字符串:2019-05-07
 */
public static String dateToString(Date date) {
	return sdf.format(date);
}

4.Excel工具类的使用

调用excelExport(List<?> dataList, Map<String, String> titleMap, String sheetName, HttpServletRequest request, HttpServletResponse response)方法
serviceImpl层
   public String findEarnSortList(HttpServletRequest request,HttpServletResponse response) throws Exception {
    List  list=new ArrayList<>();(根据实际  拿到自己的结果集)
    String sheetName = "收益排名";
    Map<String, String> titleMap = new LinkedHashMap<>();
    titleMap.put("sort", "排名");
    titleMap.put("phone", "电话");
    titleMap.put("nickname", "昵称");
    titleMap.put("earn", "金额");
    String message = ExportExcel.excelExport(list, titleMap, sheetName, request, response);
    return message;
}
Controller层
public String poiList(ModelMap modelMap, HttpServletRequest request, HttpServletResponse response) throws ParseException, Exception {
    request.setCharacterEncoding("UTF-8");
    response.setCharacterEncoding("UTF-8");
    String message = userService.findEarnSortList(request, response);
    ServletOutputStream  out = response.getOutputStream();
    if (message.equals("fail")) {
        message = "导出失败，请重试";
    }else {
        message = "导出成功";
    }
    String s = "<!DOCTYPE HTML><html><head><script>alert('" + message + "');</script></head><body></body></html>";
    OutputStreamWriter ow = new OutputStreamWriter(out,"UTF-8");
    ow.write(s);
    ow.flush();
    ow.close();
    return null;
}

以上代码可以简单的解决Excel导出信息（用户可以自己选择导出路径），需要其他的例如合并单元格等自己可以适当添加。