利用POI高效导出excel
package com.ronglian.bms.commons.excel; import com.ronglian.bms.commons.utils.Maps; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFDataFormat; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Method; import java.net.URLEncoder; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import java.util.Map; /** * 导出excel工具类,支持导出List<List<String>>,List<javabean>,List<Map<String,Object>>数据, * 可以指定导出数据的格式及需要翻译的内容,使用Map<String,Object>将此类指定数据传入,详见方法调用说明 * @Author zli */ public class ExportExcelUtil { private static final Logger LOG = LoggerFactory.getLogger(ExportExcelUtil.class); private static final int COLUMN_WIDTH = 6; private static SXSSFWorkbook workbook = null;// 工作簿 private static XSSFCellStyle headerStyle = null;// 表头样式 private static XSSFCellStyle oddStyle = null;// 奇数行样式 private static XSSFCellStyle evenStyle = null;// 偶数行样式 /** * 初始化工作簿 */ private static void initWorkBook() { try { if (workbook == null) { workbook = new SXSSFWorkbook(500); } if (headerStyle == null) { headerStyle = createHeaderStyle(workbook); } if (evenStyle == null) { evenStyle = createEvenStyle(workbook); } if (oddStyle == null) { oddStyle = createOddStyle(workbook); } } catch (Exception e) { LOG.error("初始化工作簿失败", e); } } /** * 创建表头单元格样式 * @param workbook * @return */ private static XSSFCellStyle createHeaderStyle(Workbook workbook) { XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle(); Font font = workbook.createFont(); // 字体大小 font.setFontHeightInPoints((short) 14); // 字体粗细 font.setBoldweight((short) 20); // 将字体应用到样式上面 cellStyle.setFont(font); // 是否自动换行 cellStyle.setWrapText(false); // 水平居中 cellStyle.setAlignment(HorizontalAlignment.CENTER); // 垂直居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); return cellStyle; } /** * 创建偶数行样式 * @param workbook * @return */ private static XSSFCellStyle createEvenStyle(Workbook workbook) { XSSFCellStyle xssfCellStyle = (XSSFCellStyle) workbook.createCellStyle(); XSSFDataFormat format = (XSSFDataFormat)workbook.createDataFormat(); Font font = workbook.createFont(); // 字体大小 font.setFontHeightInPoints((short) 11); // 将字体应用到样式上面 xssfCellStyle.setFont(font); // 是否自动换行 xssfCellStyle.setWrapText(false); // 水平居中 xssfCellStyle.setAlignment(HorizontalAlignment.CENTER); // 边框 xssfCellStyle.setBorderBottom(BorderStyle.THIN); xssfCellStyle.setBorderRight(BorderStyle.THIN); xssfCellStyle.setBorderTop(BorderStyle.THIN); xssfCellStyle.setBorderLeft(BorderStyle.THIN); xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 垂直居中 xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12 xssfCellStyle.setDataFormat(format.getFormat("0")); return xssfCellStyle; } /** * 创建奇数行样式 * @param workbook * @return */ private static XSSFCellStyle createOddStyle(Workbook workbook) { XSSFCellStyle xssfCellStyle = (XSSFCellStyle) workbook.createCellStyle(); XSSFDataFormat format = (XSSFDataFormat)workbook.createDataFormat(); Font font = workbook.createFont(); // 字体大小 font.setFontHeightInPoints((short) 11); // 将字体应用到样式上面 xssfCellStyle.setFont(font); // 是否自动换行 xssfCellStyle.setWrapText(false); // 水平居中 xssfCellStyle.setAlignment(HorizontalAlignment.CENTER); // 垂直居中 xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 前景颜色 xssfCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); xssfCellStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex()); // 边框 xssfCellStyle.setBorderBottom(BorderStyle.THIN); xssfCellStyle.setBorderRight(BorderStyle.THIN); xssfCellStyle.setBorderTop(BorderStyle.THIN); xssfCellStyle.setBorderLeft(BorderStyle.THIN); xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12 xssfCellStyle.setDataFormat(format.getFormat("0")); return xssfCellStyle; } /** * 创建工作表并写表头 * @param title * @param tableHeads * @param out * @param widthRow * @return */ private static SXSSFSheet createSheetAndWriteTableHead(String title, List<List<String>> tableHeads, OutputStream out, Integer widthRow) { if (out == null) { LOG.info("导出excel数据时,输出流不存在"); return null; } // 创建表 initWorkBook(); SXSSFSheet sheet = null; try { if (StringUtils.isBlank(title)) { sheet = (SXSSFSheet) workbook.createSheet(); } else { sheet = (SXSSFSheet)workbook.createSheet(title); } } catch (Exception e) { LOG.error("创建excel工作簿失败", e); } if (sheet == null) { return null; } // 写表头 writeTableHead(sheet, headerStyle, tableHeads, widthRow); return sheet; } /** * 写表头 * @param sheet * @param headerStyle * @param tableHeads * @param widthRow */ private static void writeTableHead(SXSSFSheet sheet, XSSFCellStyle headerStyle, List<List<String>> tableHeads, Integer widthRow) { if (widthRow == null || widthRow > tableHeads.size() - 1) { widthRow = tableHeads.size() - 1; } // 定义合并单元格 List<CellRangeAddress> mergList = new ArrayList<CellRangeAddress>(); for (int i = 0; i < tableHeads.size(); i++) { int merindex = 0;//定义合并单元格到某一列 Row row = sheet.createRow(i); List<String> haderList = tableHeads.get(i); for (int j = 0; j < haderList.size(); j++) { String value = haderList.get(j); if (i == widthRow) { // 设置列宽自适应,较好的支持中文 sheet.setColumnWidth(j, StringUtils.isNotBlank(value) ? value.getBytes().length*2*256 : COLUMN_WIDTH*2*256); } Cell cell = row.createCell(j); cell.setCellStyle(headerStyle); cell.setCellValue(value); // 计算合并项 if (StringUtils.isBlank(value)) { // 检查再下一个值是否为空,不为空则合并当前,若有值则暂时不合并 if ((j + 1) < haderList.size()) {// 防止数组下标越界 String valueNext = haderList.get(j + 1); if (StringUtils.isNotBlank(valueNext)) { CellRangeAddress crd = new CellRangeAddress(i , i, merindex, j); mergList.add(crd); merindex = j + 1; } } //检查自己是不是最后一列,若果是,则和前面的合并 if (j == haderList.size() -1) { CellRangeAddress crd = new CellRangeAddress(i , i, merindex, j); mergList.add(crd); } } } } // 将表头空的部分合并单元格 for (CellRangeAddress cr: mergList) { sheet.addMergedRegion(cr); } } /** * 写数据 * @param sheet * @param oddStyle * @param evenStyle * @param dataList * @param startRow */ private static void writeTableBody(SXSSFSheet sheet, XSSFCellStyle oddStyle, XSSFCellStyle evenStyle, List<List<String>> dataList, int startRow) { for (int i = 0; i < dataList.size(); i++) { Row row = sheet.createRow(i + startRow); List<String> data = dataList.get(i); for (int j = 0; j < data.size(); j++) { String value = data.get(j) != null ? data.get(j) : ""; Cell cell = row.createCell(j); if (i%2 == 0) { cell.setCellStyle(evenStyle); } else { cell.setCellStyle(oddStyle); } cell.setCellValue(value); } } } /** * 写javabean数据 * @param sheet * @param oddStyle * @param evenStyle * @param columns * @param dataList * @param clazz * @param translation 字段对应的翻译值 * @param startRow */ private static void writeTableBody(SXSSFSheet sheet, XSSFCellStyle oddStyle, XSSFCellStyle evenStyle, List<String> columns, List<?> dataList, Class<?> clazz, Map<String, Object> translation, int startRow) { Map<String, Method> methodMap = Maps.newHashMap(); // 如果是javabean类,初始化javabean类的get方法 if (!clazz.isInstance(java.util.Map.class)) { for (String s: columns) { if (StringUtils.isBlank(s)) {continue;} String methodName = "get" + s.substring(0,1).toUpperCase() + s.substring(1); try { Method method = clazz.getDeclaredMethod(methodName); methodMap.put(s, method); } catch (NoSuchMethodException e) { } } } for (int i = 0; i < dataList.size(); i++) { Row row = sheet.createRow(i + startRow); Object obj = dataList.get(i); if (obj == null) { continue; } for (int j = 0; j < columns.size(); j++) { Cell cell = row.createCell(j); if (i%2 == 0) { cell.setCellStyle(evenStyle); } else { cell.setCellStyle(oddStyle); } String attr = columns.get(j); if (StringUtils.isBlank(attr)) { // 为空的单元格 continue; } String value = ""; if (obj instanceof java.util.Map) { // 从Map获取数据 Map<String, Object> map = (Map<String, Object>) obj; Object objVal = map.get(attr); value = getValue(objVal, attr, translation); } else { try { Method method = methodMap.get(attr); if (method != null) { Object reflectVal = method.invoke(obj); value = getValue(reflectVal, attr, translation); } } catch (Exception e) { } } cell.setCellValue(value); } } } /** * 获取String类型的值,并做翻译和格式化 * @param reflectVal 值 * @param key 键 * @param translation 内容翻译 * @return */ private static String getValue(Object reflectVal, String key, Map<String,Object> translation) { String value = ""; if (reflectVal == null) { return ""; } if (translation == null) { return String.valueOf(reflectVal); } Object trans = translation.get(key); if (trans == null) { return String.valueOf(reflectVal); } if (trans instanceof java.lang.String) { String tranStr = trans.toString(); switch (tranStr) { case "yyyy-MM-dd": if (reflectVal instanceof java.util.Date) { return String.format("%tF", (java.util.Date)reflectVal); } case "HH:mm:ss": if (reflectVal instanceof java.util.Date) { return String.format("%tT", (java.util.Date)reflectVal); } case "yyyy-MM-dd HH:mm:ss": if (reflectVal instanceof java.util.Date) { return String.format("%tF", (java.util.Date)reflectVal) + " " + String.format("%tT", (java.util.Date)reflectVal); } default: if (tranStr.startsWith("#")) { DecimalFormat df = new DecimalFormat(tranStr); try { return df.format(reflectVal); } catch (Exception e) {} } break; } } if (trans instanceof java.util.Map) { Map<String, Object> transMap = (Map<String, Object>) trans; return transMap.get(reflectVal.toString()) != null ? String.valueOf(transMap.get(reflectVal.toString())) : ""; } return value; } /** * 将数据流输出后关闭资源 * @param out */ private static void finishWriteAndrelease(OutputStream out) { try { workbook.write(out); } catch (Exception e) { LOG.error("写excel文件失败:", e.getMessage()); } finally { // 释放资源 try { if(workbook != null) { // dispose of temporary files backing this workbook on disk -> 处理SXSSFWorkbook导出excel时,产生的临时文件 workbook.dispose(); } if(out != null) { out.close(); } } catch (Exception e) { LOG.error("释放写文件资源失败:", e.getMessage()); } } } /** * 从网络请求获取输出流 * @param response * @param fileName * @return */ private static OutputStream getFromResponse(HttpServletResponse response, String fileName) { OutputStream out = null; if (response == null) { return null; } try { response.reset(); response.setContentType("application/octet-stream; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "utf-8")); out = response.getOutputStream(); } catch (IOException e) { LOG.error("网络请求获取输出流失败:", e); } return out; } /** * 从文件获取输出流 * @param fileFullName * @return */ private static OutputStream getFromFile(String fileFullName) { OutputStream out = null; if (StringUtils.isBlank(fileFullName)) { return null; } File file = new File(fileFullName); if (!file.exists()) { try { file.createNewFile(); out = new FileOutputStream(file); } catch (IOException e) { LOG.error("创建文件失败:", e); } } return out; } /** * 统一导出Excel文件标准方法 * @param title 表格标题,可以为空 * @param tableHeads 表头集合,支持多行表头 * @param dataList String数据集合,二维list,将按顺序写出数据 * @param out 输出流 * @param widthRow 以表头中哪一行为列宽自适应标准,不指定或者指定的行超过表头行,将以表头最后一行为标准,表头内容为空时将采用系统默认列宽 */ public static void exportStringData(String title, List<List<String>> tableHeads, List<List<String>> dataList, OutputStream out, Integer widthRow) { SXSSFSheet sheet = createSheetAndWriteTableHead(title, tableHeads, out, widthRow); if (sheet == null) {return;} // 写表数据 int startRow = tableHeads.size(); writeTableBody(sheet, oddStyle, evenStyle, dataList, startRow); // 输出流后关闭资源 finishWriteAndrelease(out); } /** * 导出数据到到指定excel文件 * @param title 表标题头 * @param tableHeads 表头 * @param dataList 数据String集 * @param fileFullName 指定文件全名 * @param widthRow 以表头中哪一行为列宽自适应标准,不指定或者指定的行超过表头行,将以表头最后一行为标准,表头内容为空时将采用系统默认列宽 */ public static void exportStringData2File(String title, List<List<String>> tableHeads, List<List<String>> dataList, String fileFullName, Integer widthRow) { OutputStream out = getFromFile(fileFullName); if (out != null) { exportStringData(title, tableHeads, dataList, out, widthRow); } } /** * 下载String数据集到excel文件 * @param title 表标题头 * @param tableHeads 表头 * @param dataList 数据String集 * @param response 网络响应对象 * @fileName 文件名 * @param widthRow 以表头中哪一行为列宽自适应标准,不指定或者指定的行超过表头行,将以表头最后一行为标准,表头内容为空时将采用系统默认列宽 */ public static void downLoadStringData(String title, List<List<String>> tableHeads, List<List<String>> dataList, HttpServletResponse response, String fileName, Integer widthRow) { OutputStream out = getFromResponse(response, fileName); if (out != null) { exportStringData(title, tableHeads, dataList, out, widthRow); } } /** * 将javabean对象集合导出excel文件 * @param title excel文件标题 可为空 * @param tableHeads 表头集合 不可为空 * @param columns bean属性顺序集合(将按此顺序导出) 不可为空 * @param dataList 数据集 可为空,结果为空文件 * @param clazz javabean类或者Map<String, Object> 不可为空 * @param translation Map<String, Map<String,Object>>内容翻译,支持 * java.util.Date的日期格式翻译 yyyy-MM-dd,HH:mm:ss,yyyy-MM-dd HH:mm:ss, * Double/Float/BigDecimal保留小数翻译 #,#.#,#.##,#.####等 * 具体内容翻译Map<String, Object> 可为空 * 例如:{coulumn0="yyyy-MM-dd",column2=".##",column3={01="微信",02="支付宝"}}等 * @param out 输出流 不可为空 * @param widthRow 以表头中哪一行为列宽自适应标准,不指定或者指定的行超过表头行,将以表头最后一行为标准,表头内容为空时将采用系统默认列宽,可为空 */ public static void exportObjectData(String title, List<List<String>> tableHeads, List<String> columns, List<?> dataList, Class<?> clazz, Map<String, Object> translation, OutputStream out, Integer widthRow) { SXSSFSheet sheet = createSheetAndWriteTableHead(title, tableHeads, out, widthRow); if (sheet == null) {return;} // 写表数据 int startRow = tableHeads.size(); writeTableBody(sheet, oddStyle, evenStyle, columns, dataList, clazz, translation, startRow); // 输出流后关闭资源 finishWriteAndrelease(out); } /** * 将javabean对象集合导出excel文件 * @param title excel文件标题 可为空 * @param tableHeads 表头集合 不可为空 * @param columns bean属性顺序集合(将按此顺序导出) 不可为空 * @param dataList 数据集 可为空,结果为空文件 * @param clazz javabean类或者Map<String, Object> 不可为空 * @param translation Map<String, Map<String,Object>>内容翻译,支持 * java.util.Date的日期格式翻译 yyyy-MM-dd,HH:mm:ss,yyyy-MM-dd HH:mm:ss, * Double/Float/BigDecimal保留小数翻译 #,#.#,#.##,#.####等 * 具体内容翻译Map<String, Object> 可为空 * 例如:{coulumn0="yyyy-MM-dd",column2=".##",column3={01="微信",02="支付宝"}}等 * @param fileFullName 文件名 * @param widthRow 以表头中哪一行为列宽自适应标准,不指定或者指定的行超过表头行,将以表头最后一行为标准,表头内容为空时将采用系统默认列宽,可为空 */ public static void exportObjectData2File(String title, List<List<String>> tableHeads, List<String> columns, List<?> dataList, Class<?> clazz, Map<String, Object> translation, String fileFullName, Integer widthRow) { OutputStream out = getFromFile(fileFullName); if (out != null) { exportObjectData(title, tableHeads, columns, dataList, clazz, translation, out, widthRow); } } /** * 将javabean对象集合导出excel文件 * @param title excel文件标题 可为空 * @param tableHeads 表头集合 不可为空 * @param columns bean属性顺序集合(将按此顺序导出) 不可为空 * @param dataList 数据集 可为空,结果为空文件 * @param clazz javabean类或者Map<String, Object> 不可为空 * @param translation Map<String, Map<String,Object>>内容翻译,支持 * java.util.Date的日期格式翻译 yyyy-MM-dd,HH:mm:ss,yyyy-MM-dd HH:mm:ss, * Double/Float/BigDecimal保留小数翻译 #,#.#,#.##,#.####等 * 具体内容翻译Map<String, Object> 可为空 * 例如:{coulumn0="yyyy-MM-dd",column2=".##",column3={01="微信",02="支付宝"}}等 * @param response * @param fileName 文件名 * @param widthRow 以表头中哪一行为列宽自适应标准,不指定或者指定的行超过表头行,将以表头最后一行为标准,表头内容为空时将采用系统默认列宽,可为空 */ public static void downloadObjectData(String title, List<List<String>> tableHeads, List<String> columns, List<?> dataList, Class<?> clazz, Map<String, Object> translation, HttpServletResponse response, String fileName, Integer widthRow) { OutputStream out = getFromResponse(response, fileName); if (out != null) { exportObjectData(title, tableHeads, columns, dataList, clazz, translation, out, widthRow); } } }