批量导出Excel（.xlsx格式）

直接上代码：
poi版本大于3.8
public class ExcelUtil {
    //声明一个模板工作薄(写入流式数据)
    private Workbook writeDataWorkBook;
    //样式列表
    private Map<String, CellStyle> cellStyleMap;
    //Excel当前数据行数(将要写入数据的索引数)
    private int currentRowNum = 0;
    //数据输出流
    private OutputStream outputStream;
    /**
     * 释放资源
     */
    public void dispose() {
        try {
            if (writeDataWorkBook != null) {
                writeDataWorkBook.write(outputStream);
            }
            if (outputStream != null) {
                outputStream.flush();
                outputStream.close();
            }
            if (cellStyleMap != null) {
                cellStyleMap.clear();
            }
            cellStyleMap = null;
            outputStream = null;
            writeDataWorkBook = null;
        } catch (IOException e) {
        }
    }

    /**
     * 导出字符串数据
     *
     * @param file        文件名
     * @param columnNames 表头
     * @param sheetTitle  sheet页Title
     */
    public void exportExcelTitle(HttpServletResponse response, String sheetName, List<String> columnNames,
                                  String sheetTitle, List<List<Object>> objects)  {
    //创建xlsx格式的Excel
        XSSFWorkbook wb = new XSSFWorkbook();
        /**
         * SXSSFWorkbook被写入的表格，可进行海量写入
         * 参数：
         * @wb2007版本Excel
         * @100批量写入条数
         */
        SXSSFWorkbook tplWorkBook = new SXSSFWorkbook(wb, 100);
        Map<String, CellStyle> cellStyleMap = styleMap(tplWorkBook);
        // 表头样式
        CellStyle headStyle = cellStyleMap.get("head");
        // 生成一个表格
        Sheet sheet = tplWorkBook.getSheet(sheetName);
        if (sheet == null) {
            sheet = tplWorkBook.createSheet(sheetName);
        }
        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(currentRowNum, currentRowNum, 0, columnNames.size() - 1));
        // 产生表格标题行
        Row rowMerged = sheet.createRow(currentRowNum);
        Cell mergedCell = rowMerged.createCell(0);
        mergedCell.setCellStyle(headStyle);
        mergedCell.setCellValue(new XSSFRichTextString(sheetTitle));
        //写入成功一行数据递增行数
        currentRowNum = currentRowNum + 1;
        // 产生表格表头列标题行
        Row row = sheet.createRow(currentRowNum);
        for (int i = 0; i < columnNames.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellStyle(headStyle);
            RichTextString text = new XSSFRichTextString(columnNames.get(i));
            cell.setCellValue(text);
        }
        //写入成功一行数据递增行数
        currentRowNum = currentRowNum + 1;
        //内容样式
        CellStyle contentStyle = cellStyleMap.get("content");
        //正文整数样式
        CellStyle contentIntegerStyle = cellStyleMap.get("integer");
        //正文带小数整数样式
        CellStyle contentDoubleStyle = cellStyleMap.get("double");
        for (List<Object> dataRow : objects) {
            Row row1 = sheet.createRow(currentRowNum);
            for (int j = 0; j < dataRow.size(); j++) {
                Cell contentCell = row1.createCell(j);
               
                Object dataObject = dataRow.get(j);
                contentCell.setCellStyle(contentStyle);
                String str = dataObject.toString();
                if(str.contains("-") && str.contains(":") && str.contains(".0")){
                str.trim();
                str = str.substring(0, str.lastIndexOf(" "));
                contentCell.setCellStyle(contentStyle);
                contentCell.setCellValue(str);
                }else if(this.isNum(str)){
                if(str.contains(".")){
                contentCell.setCellStyle(contentDoubleStyle);
                contentCell.setCellValue(Double.parseDouble(str));
                }else{
                contentCell.setCellStyle(contentIntegerStyle);
                contentCell.setCellValue(Integer.parseInt(str));
                }
                }else{
                contentCell.setCellStyle(contentStyle);
                contentCell.setCellValue(str);
                }
               
            }
            //写入成功一行数据递增行数
            currentRowNum = currentRowNum + 1;
        }
        for(int i = 0 ; i<columnNames.size() ; i ++){
        sheet.autoSizeColumn((short)i); //调整宽度
        }
        try {
        this.setResponseHeader(response, sheetName);
        OutputStream ops = response.getOutputStream();
            tplWorkBook.write(ops);
            ops.flush();
            ops.close();
        } catch (IOException e) {
        }
    }
   
    public void exportExcelCompare(HttpServletResponse response, String sheetName, List<String> columnNames,
            String sheetTitle, List<List<Object>> objects,int length)  {
Workbook tplWorkBook = new XSSFWorkbook();
Map<String, CellStyle> cellStyleMap = styleMap(tplWorkBook);
// 表头样式
CellStyle headStyle = cellStyleMap.get("head");
CellStyle contentStyle = cellStyleMap.get("content");
// 生成一个表格
Sheet sheet = tplWorkBook.getSheet(sheetName);
if (sheet == null) {
sheet = tplWorkBook.createSheet(sheetName);
}
// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(currentRowNum, currentRowNum, 0, columnNames.size() - 1));
// 产生表格标题行
Row rowMerged = sheet.createRow(currentRowNum);
Cell mergedCell = rowMerged.createCell(0);
mergedCell.setCellStyle(headStyle);
mergedCell.setCellValue(new XSSFRichTextString(sheetTitle));
//写入成功一行数据递增行数
currentRowNum = currentRowNum + 1;
// 产生表格表头列标题行
Row row = sheet.createRow(currentRowNum);
sheet.addMergedRegion(new CellRangeAddress(currentRowNum, currentRowNum, 0,length));
sheet.addMergedRegion(new CellRangeAddress(currentRowNum, currentRowNum, length+1,length*2+1));
Cell headerCell = row.createCell(0);
headerCell.setCellStyle(contentStyle);
headerCell.setCellValue("民航局");
Cell headerCells = row.createCell(8);
headerCells.setCellStyle(contentStyle);
headerCells.setCellValue("空管局");
//写入成功一行数据递增行数
currentRowNum = currentRowNum + 1;
//正文整数样式
CellStyle contentIntegerStyle = cellStyleMap.get("integer");
//正文带小数整数样式
CellStyle contentDoubleStyle = cellStyleMap.get("double");
for (List<Object> dataRow : objects) {
Row row1 = sheet.createRow(currentRowNum);
for (int j = 0; j < dataRow.size(); j++) {
Cell contentCell = row1.createCell(j);
Object dataObject = dataRow.get(j);
contentCell.setCellStyle(contentStyle);
String str = dataObject.toString();
if(str.contains("-") && str.contains(":") && str.contains(".0")){
str.trim();
str = str.substring(0, str.lastIndexOf(" "));
contentCell.setCellStyle(contentStyle);
contentCell.setCellValue(str);
}else if(this.isNum(str)){
if(str.contains(".")){
contentCell.setCellStyle(contentDoubleStyle);
contentCell.setCellValue(Double.parseDouble(str));
}else{
contentCell.setCellStyle(contentIntegerStyle);
contentCell.setCellValue(Integer.parseInt(str));
}
}else{
contentCell.setCellStyle(contentStyle);
contentCell.setCellValue(str);
}

}
//写入成功一行数据递增行数
currentRowNum = currentRowNum + 1;
}
for(int i = 0 ; i<columnNames.size() ; i ++){
sheet.autoSizeColumn((short)i); //调整宽度
}
try {
this.setResponseHeader(response, sheetName);
OutputStream ops = response.getOutputStream();
tplWorkBook.write(ops);
ops.flush();
ops.close();
} catch (IOException e) {
}
}
   
   

    /**
     * 设置响应头
     * @param response
     * @param fileName
     */
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
             fileName = new String(fileName.getBytes(),"ISO8859-1");
             response.setContentType("application/octet-stream;charset=ISO8859-1");
             response.setHeader("Content-Disposition", "attachment;filename="+ fileName+".xlsx");
             response.addHeader("Pargam", "no-cache");
             response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
             ex.printStackTrace();
        }
   }
    /**
     * 判断是否是数值型数据
     */
    public boolean isNum(String str) {
 
        try {
            new BigDecimal(str);
            if(str.contains("E")){
            return false;
            }
            return true;
        } catch (Exception e) {
            return false;
        }
    }
    /**
     * 单元格样式(Integer)列表
     */
    private CellStyle createCellContent4IntegerStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 设置边框样式
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        //设置对齐样式
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 生成字体
        Font font = workbook.createFont();
        // 正文样式
        style.setFillPattern(XSSFCellStyle.NO_FILL);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style.setFont(font);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));//数据格式只显示整数
        return style;
    }

    /**
     * 单元格样式(Double)列表
     */
    private CellStyle createCellContent4DoubleStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 设置边框样式
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        //设置对齐样式
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 生成字体
        Font font = workbook.createFont();
        // 正文样式
        style.setFillPattern(XSSFCellStyle.NO_FILL);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style.setFont(font);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));//保留两位小数点
        return style;
    }
    /**
     * 创建单元格表头样式
     *
     * @param workbook 工作薄
     */
    private CellStyle createCellHeadStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 设置边框样式
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        //设置对齐样式
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 生成字体
        Font font = workbook.createFont();
        // 表头样式
        style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        return style;
    }

    /**
     * 创建单元格正文样式
     *
     * @param workbook 工作薄
     */
    private CellStyle createCellContentStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 设置边框样式
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        //设置对齐样式
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 生成字体
        Font font = workbook.createFont();
        // 正文样式
        style.setFillPattern(XSSFCellStyle.NO_FILL);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style.setFont(font);
        return style;
    }
    /**
     * 单元格样式列表
     */
    private Map<String, CellStyle> styleMap(Workbook workbook) {
        Map<String, CellStyle> styleMap = new LinkedHashMap<String, CellStyle>();
        styleMap.put("head", createCellHeadStyle(workbook));
        styleMap.put("content", createCellContentStyle(workbook));
        styleMap.put("integer", createCellContent4IntegerStyle(workbook));
        styleMap.put("double", createCellContent4DoubleStyle(workbook));
        return styleMap;
    }
}