excel 导出工具类

@PostMapping(value = "/exportAllDevice")
public void exportAllDevice(
        @RequestHeader @NotBlank(message = "authorization不能为空") String authorization,
        @RequestBody @Valid ListXXXReq input,
        HttpServletResponse response) {
    Result<String> res = new Result<>();
    //获取导出数据列表
    List<ListXXXVo> list = xxxService.exportAllInfo(input);
    String fileName = "dataList";
    try {
        synchronized (this) {
            Map<BaseExcelKey, String> dbMap = getExportExcelTitleMap();
            ExportPoiUtil.startDownload(response, fileName, list, dbMap);
        }
    } catch (IOException e) {
        log.debug("export exception: \r\n" + e);
    }
}

/**************************** 导出工具类 ***************************************/
@Slf4j
public class ExportPoiUtil {

    /**
     * 获取主标题样式
     *
     * @return CellStyle
     * @author 刘旭 (LiuXu)
     * <p>
     * Create time: 2017年4月5日下午2:45:47
     */
    private static CellStyle createMainTitleStyle(Workbook workbook) {
        // 主标题样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFont(createFont(workbook, "黑体", (short) 14, true));
        setBorder(titleStyle, true, true, true, true);//设置边框线
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        titleStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
//        titleStyle.setWrapText(true);//设置自动换行

        return titleStyle;
    }


    /**
     * 获取内容样式
     *
     * @return CellStyle
     * @author 刘旭 (LiuXu)
     * <p>
     * Create time: 2017年4月5日下午2:46:31
     */
    private static CellStyle createContentStyle(Workbook workbook) {
        //内容字体
        Font contentFont = createFont(workbook, "仿宋_GB2312", (short) 12, false);
        // 内容样式
        CellStyle contentStyle = workbook.createCellStyle();

        contentStyle.setFont(contentFont);//设置字体
        setBorder(contentStyle, true, true, true, true);//设置边框线
        contentStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
//        contentStyle.setWrapText(true);//设置自动换行

        return contentStyle;
    }

    /**
     * 设置边框线
     *
     * @param cellStyle POI样式对象
     * @param top       设置上边框
     * @param bottom    设置下边框
     * @param left      设置左边框
     * @param right     设置右边框
     * @author liuxu
     * @date 17-10-26下午5:08
     */
    private static void setBorder(CellStyle cellStyle, boolean top, boolean bottom, boolean left, boolean right) {
        //设置上边框
        if (top) {
            cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        }
        //设置下边框
        if (bottom) {
            cellStyle.setBorderBottom(BorderStyle.THIN);// 下边框
        }
        //设置左边框
        if (left) {
            cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        }
        //设置右边框
        if (right) {
            cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
        }
    }

    /**
     * 创建字体
     *
     * @param fontName 字体
     * @param fontSize 字号
     * @param bold     字体加粗
     * @author 魏胜斌
     * @date 17-10-13下午3:09
     */
    private static Font createFont(Workbook workbook, String fontName, short fontSize, boolean bold) {
        Font font = workbook.createFont();
        font.setFontName(fontName);//字体
        font.setFontHeightInPoints(fontSize);// 字号
        font.setBold(bold);//字体加粗
        return font;
    }


    //参数说明: fileName：文件名  projects：对象集合 columnNames： 列名  keys： map中的key
    public static void startDownload(HttpServletResponse response, String fileName, List<?> projects,
                                     Map<BaseExcelKey, String> dbMap) throws IOException {

        //将集合中对象的属性 对应到 List<Map<String,Object>>
        BaseExcelKey[] dbKeys = dbMap.keySet().toArray(new BaseExcelKey[0]);
        List<Map<String, Object>> list = createExcelRecord(projects, dbKeys);

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            //将转换成的Workbook对象通过流形式下载
            createWorkBook(list, dbMap).write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数，可以打开下载页面
        response.reset();

        response.setHeader("Access-Control-Allow-Origin", "*"); //  这里最好明确的写允许的域名
        response.setHeader("Access-Control-Allow-Methods", "POST, GET, OPTIONS, DELETE, PUT");
        response.setHeader("Access-Control-Max-Age", "3600");
//        response.setHeader("Access-Control-Allow-Headers", "Content-Disposition,Content-Type,Accept,Access-Token,Authorization");
        response.setHeader("Access-Control-Allow-Headers", "*");

        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
        ServletOutputStream out = response.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (final IOException e) {
            throw e;
        } finally {
            IOUtils.closeQuietly(bis);
            IOUtils.closeQuietly(bos);
            IOUtils.closeQuietly(out);
        }
    }

    private static List<Map<String, Object>> createExcelRecord(List<?> projects, BaseExcelKey[] keys) {
        List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("sheetName", "sheet"); // 加入sheet页名称
        listmap.add(map);
        Object project = null;
        // 从1开始后面是数据项
        for (int j = 0; j < projects.size(); j++) {
            project = projects.get(j);
            Map<String, Object> mapValue = new HashMap<String, Object>();
            for (int i = 0; i < keys.length; i++) {
                mapValue.put(keys[i].getKey(), getFieldValueByName(keys[i].getKey(), project));
            }

            listmap.add(mapValue);
        }
        return listmap;
    }

    /**
     * 利用反射 根据属性名获取属性值
     */
    private static Object getFieldValueByName(String fieldName, Object o) {
        try {
            String firstLetter = fieldName.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + fieldName.substring(1);
            Method method = o.getClass().getMethod(getter, new Class[]{});
            Object value = method.invoke(o, new Object[]{});
            if(value instanceof java.util.Date){
                return MyDateUtil.dateToStringStandardLong((java.util.Date)value);
            }

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

    /**
     * 创建excel文档对象
     */
    private static Workbook createWorkBook(List<Map<String, Object>> list, Map<BaseExcelKey, String> dbMap) {
        List<BaseExcelKey> sortKeys = dbMap.keySet().stream().sorted(Comparator.comparing(BaseExcelKey::getSort)).collect(Collectors.toList());
        // 创建excel工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        // 创建第一行
        Row row = sheet.createRow((short) 0);
        // 创建两种单元格格式
        CellStyle csTitle = createMainTitleStyle(wb);//wb.createCellStyle();
        CellStyle csBody = createContentStyle(wb);

        //设置列名
        for (int iCol = 0; iCol < sortKeys.size(); iCol++) {
            // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
            sheet.setColumnWidth((short) iCol, (short) (35.7 * 150));
            Cell cell = row.createCell(iCol);
            cell.setCellValue(dbMap.get(sortKeys.get(iCol)));
            cell.setCellStyle(csTitle);
        }
        //设置每行每列的值
        for (int iRow = 1; iRow < list.size(); iRow++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow((short) iRow);
            // 在row行上创建一个方格
            for (int iCol = 0; iCol < sortKeys.size(); iCol++) {
                Cell cell = row1.createCell(iCol);
                // 单元数据
                Object tmp = list.get(iRow).get(sortKeys.get(iCol).getKey());
                cell.setCellValue(tmp == null ? " " : tmp.toString());
                cell.setCellStyle(csBody);
            }
        }
        return wb;
    }
}