spring mvc 导出excel
pom.xml
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version> </dependency>
@Override public String exportOrders(Map<String, Object> queryParams) { SXSSFWorkbook wb = new SXSSFWorkbook(); Sheet sheet = wb.createSheet("订单"); // 表头 int cellIndex = 0; Row rowHead = sheet.createRow(0); Cell cell0 = rowHead.createCell(cellIndex++); cell0.setCellValue("用户名"); Cell cell1 = rowHead.createCell(cellIndex++); cell1.setCellValue("订单号"); Cell cell2 = rowHead.createCell(cellIndex++); cell2.setCellValue("支付方订单号"); Cell cell3 = rowHead.createCell(cellIndex++); cell3.setCellValue("商品id"); Cell cell4 = rowHead.createCell(cellIndex++); cell4.setCellValue("商品名称"); Cell cell5 = rowHead.createCell(cellIndex++); cell5.setCellValue("支付数目"); Cell cell6 = rowHead.createCell(cellIndex++); cell6.setCellValue("支付金额"); Cell cell7 = rowHead.createCell(cellIndex++); cell7.setCellValue("支付方式"); Cell cell8 = rowHead.createCell(cellIndex++); cell8.setCellValue("订单状态"); Cell cell9 = rowHead.createCell(cellIndex++); cell9.setCellValue("有效时间"); Cell cell10 = rowHead.createCell(cellIndex++); cell10.setCellValue("商品类型"); Cell cell11 = rowHead.createCell(cellIndex++); cell11.setCellValue("来源"); Cell cell12 = rowHead.createCell(cellIndex++); cell12.setCellValue("订单创建时间"); // 表头样式 ToolPoi.setHeadStyle(wb, rowHead.cellIterator()); // 表头宽度 sheet.setColumnWidth(0, 10 * 256); sheet.setColumnWidth(1, 25 * 256); sheet.setColumnWidth(2, 30 * 256); sheet.setColumnWidth(3, 15 * 256); sheet.setColumnWidth(4, 20 * 256); sheet.setColumnWidth(5, 15 * 256); sheet.setColumnWidth(6, 25 * 256); sheet.setColumnWidth(7, 15 * 256); sheet.setColumnWidth(8, 15 * 256); sheet.setColumnWidth(9, 15 * 256); sheet.setColumnWidth(10, 15 * 256); sheet.setColumnWidth(11, 15 * 256); sheet.setColumnWidth(12, 25 * 256); // 基本样式 XSSFColor color = new XSSFColor(new java.awt.Color(255, 255, 255)); Font font = ToolPoi.createFont(wb, Font.BOLDWEIGHT_NORMAL, Font.COLOR_NORMAL, (short) 10); CellStyle style = ToolPoi.createBorderCellStyle(wb, HSSFColor.WHITE.index, color, CellStyle.ALIGN_CENTER, font); // 样式换行 CellStyle wrap = wb.createCellStyle(); wrap.cloneStyleFrom(style); wrap.setWrapText(true); // 绿色 XSSFCellStyle blue = (XSSFCellStyle)wb.createCellStyle(); blue.cloneStyleFrom(style); blue.setFillForegroundColor(new XSSFColor(new java.awt.Color(0, 164, 137))); // 红色 XSSFCellStyle red = (XSSFCellStyle)wb.createCellStyle(); red.cloneStyleFrom(style); red.setFillForegroundColor(new XSSFColor(new java.awt.Color(243, 123, 83))); List<Order> list = orderMapper.listOrderByParams(queryParams); // 处理监控点数据 for (int i = 0,j=list.size();i < j; i++) { Order order =list.get(i); // 创建行 Row row = sheet.createRow(i + 1); Cell c0 = row.createCell(0); c0.setCellStyle(style); c0.setCellValue(order.getUserAccount()==null?"":order.getUserAccount()); Cell c1 = row.createCell(1); c1.setCellStyle(style); c1.setCellValue(order.getOrderId()==null?"":order.getOrderId()); Cell c2 = row.createCell(2); c2.setCellStyle(wrap); c2.setCellValue(order.getPayOrderId()==null?"":order.getPayOrderId()); Cell c3 = row.createCell(3); c3.setCellStyle(wrap); c3.setCellValue(order.getProductId()==null?"":order.getProductId()); Cell c4 = row.createCell(4); // c4.setCellStyle(record.getStr("status").equals("1") ? blue : red); c4.setCellStyle(wrap); c4.setCellValue(order.getProductName()==null?"":order.getProductName()); Cell c5 = row.createCell(5); c5.setCellStyle(style); c5.setCellValue(order.getPayCount()==null?"":order.getPayCount().toString()); Cell c6 = row.createCell(6); c6.setCellStyle(style); // c6.setCellValue(ToolDateTime.format(record.getTimestamp("startdate"), ToolDateTime.pattern_ymd_hms_s)); c6.setCellValue(order.getPayAmount()==null?"":order.getPayAmount().toString()); Cell c7 = row.createCell(7); c7.setCellStyle(style); c7.setCellValue(order.getPayWay()==null?"":order.getPayWay()); Cell c8 = row.createCell(8); c8.setCellStyle(style); c8.setCellValue(order.getStatus()==null?"":order.getStatus().toString()); Cell c9 = row.createCell(9); // int haoshi = record.getNumber("haoshi").intValue(); c9.setCellStyle(wrap); c9.setCellValue(order.getValiddate()==null?"":order.getValiddate().toString()); Cell c10 = row.createCell(10); c10.setCellStyle(style); c10.setCellValue(order.getProductType()==null?"":order.getProductType().toString()); Cell c11 = row.createCell(11); c11.setCellStyle(style); c11.setCellValue(order.getFromChannel()==null?"":order.getFromChannel().toString()); Cell c12 = row.createCell(12); c12.setCellStyle(style); String createTime =order.getCreateTime()==null?"":(ToolDateTime.format(order.getCreateTime(), ToolDateTime.pattern_ymd_hms_s)); c12.setCellValue(createTime); } String path = ToolPoi.writeExcel(wb, "订单列表"); return path; }
ToolPoi.java
package com.pptv.ucm.common.util; import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.Date; import java.util.Iterator; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.RegionUtil; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.pptv.ucm.controller.base.BaseController; /** * poi工具类 * * @author 董华健 dongcb678@163.com * * 描述：建议导出规则是，先定义好excel模板，然后填充数据，这样避免编写很多不必要的样式代码 * */ public abstract class ToolPoi { private static Logger log = LoggerFactory.getLogger(BaseController.class); /** * excel导出 * * @param templatePath 模板路径 */ @SuppressWarnings("unused") public static String export(String templatePath) { // 导出文件存放目录 String filePath = PathKit.getWebRootPath() + File.separator + "exportFile"; File fileDir = new File(filePath); if (!fileDir.exists()) { fileDir.mkdir(); } // 导出文件路径 String path = filePath + File.separator + ToolDateTime.format(new Date(), "yyyyMMddHHmmssSSS") + ".xlsx"; XSSFWorkbook wb = null; SXSSFWorkbook swb = null; FileOutputStream os = null; try { // 1.载入模板 wb = new XSSFWorkbook(new File(templatePath)); // 初始化HSSFWorkbook对象 wb.setSheetName(0, "用户信息导出"); Sheet sheet = wb.getSheetAt(0); // wb.createSheet("监控点资源状态"); // 2.读取模板处理好样式 // 3.转换成大数据读取模式 swb = new SXSSFWorkbook(wb, 1000); // 用于大文件导出 sheet = swb.getSheetAt(0); // 4.大批量写入数据 // 5.保存到本地文件夹 os = new FileOutputStream(new File(path)); swb.write(os); return path; } catch (IOException e) { log.error("导出失败：" + e.getMessage()); e.printStackTrace(); return null; } catch (InvalidFormatException e) { log.error("导出失败：" + e.getMessage()); e.printStackTrace(); return null; } finally { close(os, swb, wb); } } /** * 资源关闭 * * @param os * @param wb * @param swb */ public static void close(FileOutputStream os, SXSSFWorkbook swb, XSSFWorkbook wb) { if (null != os) { try { os.close(); } catch (IOException e) { e.printStackTrace(); } } if (null != swb) { try { swb.close(); } catch (IOException e) { e.printStackTrace(); } } if (null != wb) { try { wb.close(); } catch (IOException e) { e.printStackTrace(); } } } /** * 将文档写入文件 * * @param wb * @param name */ public static String writeExcel(SXSSFWorkbook wb, String name) { String filePath = PathKit.getWebRootPath() + File.separator + "WEB-INF" + File.separator + "files" + File.separator + "export"; File f = new File(filePath); if (!f.exists()) { f.mkdir(); } String path = filePath + File.separator + name + ToolDateTime.format(new Date(), "_yyyy_MM_dd_HH_mm_ss_SSS") + ".xlsx"; FileOutputStream os = null; try { File file = new File(path); os = new FileOutputStream(file); wb.write(os); os.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { if (null != os) { try { os.close(); } catch (IOException e) { e.printStackTrace(); } } } return path; } /** * 导出标题样式 */ public static void setTitleFont(SXSSFWorkbook wb, Iterator<Cell> it) { XSSFColor color = new XSSFColor(new java.awt.Color(219, 229, 241)); Font font = createFont(wb, Font.BOLDWEIGHT_BOLD, Font.COLOR_NORMAL, (short) 11); CellStyle style = createCellStyle(wb, color, CellStyle.ALIGN_CENTER, font); while (it.hasNext()) { it.next().setCellStyle(style); } } /** * 导出表头样式 */ public static void setHeadStyle(SXSSFWorkbook wb, Iterator<Cell> it) { XSSFColor color = new XSSFColor(new java.awt.Color(79, 129, 189)); Font font = createFont(wb, Font.BOLDWEIGHT_NORMAL, HSSFColor.WHITE.index, (short) 11); CellStyle style = createCellStyle(wb, color, CellStyle.ALIGN_CENTER, font); while (it.hasNext()) { it.next().setCellStyle(style); } } /** * 导出表数据样式 默认居中 */ public static void setContentStyle(SXSSFWorkbook wb, Iterator<Cell> it) { XSSFColor color = new XSSFColor(new java.awt.Color(255, 255, 255)); Font font = createFont(wb, Font.BOLDWEIGHT_NORMAL, Font.COLOR_NORMAL, (short) 10); CellStyle style = createBorderCellStyle(wb, HSSFColor.WHITE.index, color, CellStyle.ALIGN_CENTER, font); while (it.hasNext()) { it.next().setCellStyle(style); } } /** * 导出表数据样式 左对齐 */ public static void setContentLeftStyle(SXSSFWorkbook wb, Cell cell) { XSSFColor color = new XSSFColor(new java.awt.Color(255, 255, 255)); Font font = createFont(wb, Font.BOLDWEIGHT_NORMAL, Font.COLOR_NORMAL, (short) 10); CellStyle style = createBorderCellStyle(wb, HSSFColor.WHITE.index, color, CellStyle.ALIGN_LEFT, font); style.setWrapText(true); // 实现换行 cell.setCellStyle(style); } /** * 设置合并单元格边框 */ public static void setBorderStyle(Workbook wb, Sheet sheet, CellRangeAddress cra) { int border = HSSFColor.WHITE.index; RegionUtil.setBorderBottom(border, cra, sheet, wb); RegionUtil.setBorderLeft(border, cra, sheet, wb); RegionUtil.setBorderRight(border, cra, sheet, wb); RegionUtil.setBorderTop(border, cra, sheet, wb); } /** * 功能：创建HSSFSheet工作簿 * * @param wb SXSSFWorkbook * @param sheetName String * @return HSSFSheet */ public static Sheet createSheet(SXSSFWorkbook wb, String sheetName) { Sheet sheet = wb.createSheet(sheetName); sheet.setDefaultColumnWidth(30); sheet.setColumnWidth(0, 7 * 256); sheet.setDefaultRowHeight((short) 400); sheet.setDisplayGridlines(true); return sheet; } /** * 功能：创建CellStyle样式 * * @param wb SXSSFWorkbook * @param color 背景色 * @param align 前置色 * @param font 字体 * @return CellStyle */ public static CellStyle createCellStyle(SXSSFWorkbook wb, XSSFColor color, short align, Font font) { XSSFCellStyle cs = (XSSFCellStyle) wb.createCellStyle(); cs.setAlignment(align); // 水平居中 cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cs.setFillForegroundColor(color); cs.setFillPattern(CellStyle.SOLID_FOREGROUND); cs.setFont(font); return cs; } /** * 功能：创建带边框的CellStyle样式 * * @param wb SXSSFWorkbook * @param backgroundColor 背景色 * @param foregroundColor 前置色 * @param font 字体 * @return CellStyle */ public static CellStyle createBorderCellStyle(SXSSFWorkbook wb, short backgroundColor, XSSFColor foregroundColor, short halign, Font font) { XSSFCellStyle cs = (XSSFCellStyle) wb.createCellStyle(); cs.setAlignment(halign); cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cs.setFillBackgroundColor(backgroundColor); cs.setFillForegroundColor(foregroundColor); cs.setFillPattern(CellStyle.SOLID_FOREGROUND); cs.setFont(font); cs.setBorderLeft(CellStyle.BORDER_DASHED); cs.setLeftBorderColor(HSSFColor.GREY_80_PERCENT.index); cs.setBorderRight(CellStyle.BORDER_DASHED); cs.setRightBorderColor(HSSFColor.GREY_80_PERCENT.index); cs.setBorderTop(CellStyle.BORDER_DASHED); cs.setTopBorderColor(HSSFColor.GREY_80_PERCENT.index); cs.setBorderBottom(CellStyle.BORDER_DASHED); cs.setBottomBorderColor(HSSFColor.GREY_80_PERCENT.index); return cs; } /** * 功能：创建字体 * * @param wb HSSFWorkbook * @param boldweight short * @param color short * @return Font */ public static Font createFont(SXSSFWorkbook wb, short boldweight, short color, short size) { Font font = wb.createFont(); font.setBoldweight(boldweight); font.setColor(color); font.setFontHeightInPoints(size); return font; } /** * 功能：合并单元格 * * @param sheet Sheet * @param firstRow int * @param lastRow int * @param firstColumn int * @param lastColumn int * @return int 合并区域号码 */ public static int mergeCell(Sheet sheet, int firstRow, int lastRow, int firstColumn, int lastColumn) { return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn)); } /** * 功能：创建Row * * @param sheet Sheet * @param rowNum int * @param height int * @return HSSFRow */ public static Row createRow(Sheet sheet, int rowNum, int height) { Row row = sheet.createRow(rowNum); row.setHeight((short) height); return row; } // public static String nullVal(Object val){ // if(val == null){ // return ""; // } // return val; // } }
PathKit.java
package com.pptv.ucm.common.util; import java.io.File; /** * new File("..\path\abc.txt") 中的三个方法获取路径的方法 * 1： getPath() 获取相对路径，例如 ..\path\abc.txt * 2： getAbslutlyPath() 获取绝对路径，但可能包含 ".." 或 "." 字符，例如 D:\otherPath\..\path\abc.txt * 3： getCanonicalPath() 获取绝对路径，但不包含 ".." 或 "." 字符，例如 D:\path\abc.txt */ public class PathKit { private static String webRootPath; private static String rootClassPath; @SuppressWarnings("rawtypes") public static String getPath(Class clazz) { String path = clazz.getResource("").getPath(); return new File(path).getAbsolutePath(); } public static String getPath(Object object) { String path = object.getClass().getResource("").getPath(); return new File(path).getAbsolutePath(); } public static String getRootClassPath() { if (rootClassPath == null) { try { String path = PathKit.class.getClassLoader().getResource("").toURI().getPath(); rootClassPath = new File(path).getAbsolutePath(); } catch (Exception e) { String path = PathKit.class.getClassLoader().getResource("").getPath(); rootClassPath = new File(path).getAbsolutePath(); } } return rootClassPath; } public void setRootClassPath(String rootClassPath) { PathKit.rootClassPath = rootClassPath; } public static String getPackagePath(Object object) { Package p = object.getClass().getPackage(); return p != null ? p.getName().replaceAll("\\.", "/") : ""; } public static File getFileFromJar(String file) { throw new RuntimeException("Not finish. Do not use this method."); } public static String getWebRootPath() { if (webRootPath == null) webRootPath = detectWebRootPath(); return webRootPath; } public static void setWebRootPath(String webRootPath) { if (webRootPath == null) return ; if (webRootPath.endsWith(File.separator)) webRootPath = webRootPath.substring(0, webRootPath.length() - 1); PathKit.webRootPath = webRootPath; } private static String detectWebRootPath() { try { String path = PathKit.class.getResource("/").toURI().getPath(); return new File(path).getParentFile().getParentFile().getCanonicalPath(); } catch (Exception e) { throw new RuntimeException(e); } } public static boolean isAbsolutelyPath(String path) { return path.startsWith("/") || path.indexOf(":") == 1; } /* private static String detectWebRootPath() { try { String path = PathKit.class.getResource("/").getFile(); return new File(path).getParentFile().getParentFile().getCanonicalPath(); } catch (IOException e) { throw new RuntimeException(e); } } */ }
