poi导出excel工具类

poi版本

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi-ooxml</artifactId>    <version>3.17</version></dependency>

/** * Excel导出工具类 * @author zsc * @datetime 2017年12月14日 下午8:01:32 */public class ExcelUtil {// 将需要的单元格式样式放到Map集合中,使用时直接从Map中获取，如果在使用时创建，那当数据量很大时严重影响性能public static ThreadLocal<Map<String, XSSFCellStyle>> styles = new ThreadLocal<>();/** 构造方法私有，禁止用户new对象 */private ExcelUtil() {super();}/** * 导出工作簿，将工作簿写响应(response)输出流实现浏览器下载 * @param response * @param workbook * @param fileName * @throws Exception */public static void exportExcel(HttpServletResponse response, XSSFWorkbook workbook, String fileName) throws Exception {OutputStream os = null;try {os = response.getOutputStream();response.reset();            response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");            response.setContentType("application/msexcel");workbook.write(os);} finally {if(null != workbook) {workbook.close();}if(null != os) {os.flush();os.close();}}}/** * 创建工作簿 * @param sheetNames * @param headNames * @param titles * @param contents * @return * @throws Exception */public static XSSFWorkbook createExcel(String[] sheetNames, String[] headNames, List<String[]> titles, List<List<Object[]>> contents) throws Exception {XSSFWorkbook workbook = new XSSFWorkbook();int sheetCount = sheetNames.length;XSSFSheet sheet = null;String headName = "";for(int i = 0; i < sheetCount; i++) {sheet = workbook.createSheet(sheetNames[i]);headName = (null != headNames && StringUtils.isNotBlank(headNames[i])) ? headNames[i] : sheetNames[i];createExcel(workbook, sheet, headName, titles.get(i), contents.get(i));}return workbook;}public static XSSFWorkbook createExcel(String sheetName, String headName, String[] titles, List<Object[]> contents) throws Exception {XSSFWorkbook workbook = new XSSFWorkbook();XSSFSheet sheet = workbook.createSheet(sheetName);createExcel(workbook, sheet, headName, titles, contents);return workbook;}/** * 创建工作簿 * @param workbook 导出工作簿 * @param sheet 导出工作表 * @param titles 标题列表 * @param contents 数据列表 * @throws Exception */public static void createExcel(XSSFWorkbook workbook, XSSFSheet sheet, String[] titles, List<Object[]> contents) throws Exception {createExcel(workbook, sheet, sheet.getSheetName(), titles, contents);}/** * 导出Excel * @param os 输出流 * @param workbook 导出工作簿 * @param sheet 导出工作表 * @param headName 表头名 * @param titles 标题列表 * @param contents 数据列表 * @throws Exception */public static void createExcel(XSSFWorkbook workbook, XSSFSheet sheet, String headName, String[] titles, List<Object[]> contents) throws Exception {try {if(null == contents || contents.size() <= 0) {return;}// 创建单元格式样式集合styles.set(styleMap(workbook));// 创建工作表头createSheetHead(workbook, sheet, titles, headName);// 填充工作表数据createSheetData(sheet, contents, 2);} finally {if(null != styles.get()) {styles.get().clear();}styles.remove();}}/** * 构建sheet表头 * @param sheet * @param heads */private static void createSheetHead(XSSFWorkbook workbook, XSSFSheet sheet, String[] heads, String headName) {sheet.createFreezePane(0, 2, 0, 2);// 冻结前2行sheet.setDefaultColumnWidth((short) 20);// 设置表格默认列宽度为20个字节XSSFCellStyle tilteStyle = styles.get().get("head");XSSFCell cell = null;XSSFRow rowFirst = sheet.createRow(0);// 创建第一行（报表名称）cell = rowFirst.createCell(0);cell.setCellValue(StringUtils.isBlank(headName) ? sheet.getSheetName() : headName);cell.setCellStyle(tilteStyle);sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, heads.length -1));XSSFRow row = sheet.createRow(1);// 创建第二行（列名）if (heads != null && heads.length > 0) {XSSFCellStyle cellStyle = styles.get().get("title");for (int i = 0; i < heads.length; i++) {cell = row.createCell(i);if (heads[i] != null) {cell.setCellValue(heads[i]);cell.setCellStyle(cellStyle);}}}}/** * 构建sheet数据内容 * @param sheet * @param contents * @param index */private static void createSheetData(XSSFSheet sheet, List<Object[]> contents, int index) {XSSFCellStyle contentStyle = ExcelUtil.styles.get().get("content");XSSFCellStyle integerStyle = ExcelUtil.styles.get().get("integer");XSSFCellStyle doubleStyle = ExcelUtil.styles.get().get("double");Iterator<Object[]> it = contents.iterator();Row nextrow;Cell cell2;Object[] obj;// 遍历数据while (it.hasNext()) {nextrow = sheet.createRow(index++);obj = it.next();if (obj != null) {int objLen = obj.length;for (int i = 0; i < objLen; i++) {cell2 = nextrow.createCell(i);if (obj[i] != null) {if(obj[i] instanceof Float || obj[i] instanceof Double || StringUtil.isNumeric1(obj[i].toString())){cell2.setCellValue(Double.parseDouble(obj[i].toString()));cell2.setCellStyle(doubleStyle);}else if(obj[i] instanceof Integer || (obj[i] instanceof Long && obj[i].toString().length() <=10)|| (StringUtil.isNumeric(obj[i].toString()) && obj[i].toString().length() <= 10)){cell2.setCellValue(Integer.parseInt(obj[i].toString()));cell2.setCellStyle(integerStyle);}else{cell2.setCellValue(obj[i].toString());cell2.setCellStyle(contentStyle);}} else {cell2.setCellValue("");cell2.setCellStyle(contentStyle);}}}}}/**     * 创建单元格表头样式     *     * @param workbook 工作薄     */    private static XSSFCellStyle createCellHeadStyle(XSSFWorkbook workbook) {    XSSFCellStyle style = workbook.createCellStyle();     // 设置边框样式        style.setBorderBottom(BorderStyle.THIN);        style.setBorderLeft(BorderStyle.THIN);        style.setBorderRight(BorderStyle.THIN);        style.setBorderTop(BorderStyle.THIN);        //设置对齐样式        style.setAlignment(HorizontalAlignment.CENTER);        // 生成字体        XSSFFont font = workbook.createFont();        font.setFontHeightInPoints((short) 20);        font.setBold(true);        // 把字体应用到当前的样式        style.setFont(font);        return style;    }        /**     * 创建单元格表头标题样式     *     * @param workbook 工作薄     */    private static XSSFCellStyle createCellTitleStyle(XSSFWorkbook workbook) {    XSSFCellStyle style = workbook.createCellStyle();     // 设置边框样式        style.setBorderBottom(BorderStyle.THIN);        style.setBorderLeft(BorderStyle.THIN);        style.setBorderRight(BorderStyle.THIN);        style.setBorderTop(BorderStyle.THIN);        //设置对齐样式        style.setAlignment(HorizontalAlignment.CENTER);        // 生成字体        XSSFFont font = workbook.createFont();        // 表头样式        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);        style.setFillForegroundColor(new XSSFColor(Color.CYAN));        font.setBold(true);        // 把字体应用到当前的样式        style.setFont(font);        return style;    }    /**     * 创建单元格正文样式     *     * @param workbook 工作薄     */    private static XSSFCellStyle createCellContentStyle(XSSFWorkbook workbook) {    XSSFCellStyle style = workbook.createCellStyle();        // 设置边框样式        style.setBorderBottom(BorderStyle.THIN );        style.setBorderLeft(BorderStyle.THIN);        style.setBorderRight(BorderStyle.THIN);        style.setBorderTop(BorderStyle.THIN);        // 生成字体        XSSFFont font = workbook.createFont();        // 正文样式        style.setVerticalAlignment(VerticalAlignment.CENTER);        font.setBold(false);        // 把字体应用到当前的样式        style.setFont(font);        return style;    }    /**     * 单元格样式(Integer)列表     */    private static XSSFCellStyle createCellContent4IntegerStyle(XSSFWorkbook workbook) {    XSSFCellStyle style = workbook.createCellStyle();        // 设置边框样式        style.setBorderBottom(BorderStyle.THIN );        style.setBorderLeft(BorderStyle.THIN);        style.setBorderRight(BorderStyle.THIN);        style.setBorderTop(BorderStyle.THIN);        // 生成字体        XSSFFont font = workbook.createFont();        // 正文样式        style.setVerticalAlignment(VerticalAlignment.CENTER);        font.setBold(false);        // 把字体应用到当前的样式        style.setFont(font);        style.setDataFormat(workbook.createDataFormat().getFormat("#,##0"));//数据格式只显示整数        return style;    }    /**     * 单元格样式(Double)列表     */    private static XSSFCellStyle createCellContent4DoubleStyle(XSSFWorkbook workbook) {    XSSFCellStyle style = workbook.createCellStyle();        // 设置边框样式        style.setBorderBottom(BorderStyle.THIN );        style.setBorderLeft(BorderStyle.THIN);        style.setBorderRight(BorderStyle.THIN);        style.setBorderTop(BorderStyle.THIN);        // 生成字体        XSSFFont font = workbook.createFont();        // 正文样式        style.setVerticalAlignment(VerticalAlignment.CENTER);        font.setBold(false);        // 把字体应用到当前的样式        style.setFont(font);        style.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00"));//保留两位小数点        return style;    }/**     * 单元格样式列表     */    private static Map<String, XSSFCellStyle> styleMap(XSSFWorkbook workbook) {        Map<String, XSSFCellStyle> styleMap = new LinkedHashMap<>();        styleMap.put("head", createCellHeadStyle(workbook));        styleMap.put("title", createCellTitleStyle(workbook));        styleMap.put("content", createCellContentStyle(workbook));        styleMap.put("integer", createCellContent4IntegerStyle(workbook));        styleMap.put("double", createCellContent4DoubleStyle(workbook));        return styleMap;    }}