Java poi 导出excel

POI依赖：

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency>

HTML：

<button type="button" id="exportSite" style="background-color: #00D2FF" class="layui-btn">导出Excel</button>

JS：

//导出 js用的构建虚拟的form表单提交 $('#exportSite').on("click", function () { $('#exexcelfrom').remove(); var url = perfix + '/exportExcel';//后台controller路径 var form = $("<form>");//定义一个form表单 form.attr("style", "display:none"); form.attr("target", ""); form.attr("id", "exexcelfrom"); form.attr("method", "post");//请求类型 form.attr("action", url);//请求地址 $("body").append(form);//将表单放置在web中 var param = getParam(); param.page = 1; //由于后台原因给的分页参数 用于查询数据 param.limit = 99999999; //由于后台原因给的分页参数 用于查询数据 for (var i in param) { var temp = $("<input>"); temp.attr("type", "hidden"); temp.attr("name", i); temp.attr("value", param[i]); form.append(temp); } form.submit();//表单提交 }); //需要的参数 或者条件导出 function getParam() { return { siteName: $("#siteName").val(), detailAddress: $("#detailAddress").val() } }

controller：

/** * * @param params //请求的参数 条件 分页参数都放在里面 * @param request //请求 * @param response //响应 */ @ResponseBody @PostMapping("/exportExcel") public void exportExcel(@RequestParam Map<String, Object> params, HttpServletRequest request, HttpServletResponse response) { try { ExportExcel exportExcel = baseSiteService.exportExcel(new Query(params));//Query自己封装的对象 继承的HashMap 可以直接用map //调service层方法 String userAgent = request.getHeader("User-Agent"); String filename = String.format("xxxx.xlsx");//导出的文件名 // 针对IE或者以IE为内核的浏览器： if (userAgent.contains("MSIE") || userAgent.contains("Trident")) { filename = java.net.URLEncoder.encode(filename, "UTF-8"); } else { // 非IE浏览器的处理： filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1"); } response.reset(); response.setContentType("application/octet-stream; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename=" + filename); exportExcel.write(response.getOutputStream()); } catch (Exception e) { e.printStackTrace(); } }

service：

public ExportExcel exportExcel(Query query) { // PageInfo<SiteVO> siteVOPageInfo = listData(query); List<SiteVO> list = siteVOPageInfo.getList();//获取需要导出的数据 return new ExportExcel("", SiteVO.class).setDataList(list); }

domain（实体类）： @ExcelField(title = "站点名称", sort = 0)  需要加上这个注解  getter setter由@Data提供

@Data public class SiteVO implements Serializable { private static final long serialVersionUID = 7702178102602705686L; //id private String id; //站点名称 @ExcelField(title = "站点名称", sort = 0) private String siteName; //站点编码 @ExcelField(title = "站点编码", sort = 1) private String siteCode; //省市区 @ExcelField(title = "省/市/区", sort = 2) private String area; //站点详细地址 @ExcelField(title = "详细地址", sort = 3) private String detailAddress; //禁用车型 @ExcelField(title = "禁用车型", sort = 4) private Integer disableCarTypeNum; //最大可进车型名字 @ExcelField(title = "最大可进车型", sort = 5) private String maxCarTypeName; //经度 @ExcelField(title = "经度", sort = 6) private String longitude; //纬度 @ExcelField(title = "纬度", sort = 7) private String latitude; }

@ExcelField :

package com.ysailine.utils.excel.annotation; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * Excel注解定义 */ @Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE}) @Retention(RetentionPolicy.RUNTIME) public @interface ExcelField { /** * 导出字段名（默认调用当前字段的“get”方法，如指定导出字段为对象，请填写“对象名.对象属性”，例：“area.name”、“office.name”） */ String value() default ""; /** * 导出字段标题（需要添加批注请用“**”分隔，标题**批注，仅对导出模板有效） */ String title(); /** * 字段类型（0：导出导入；1：仅导出；2：仅导入） */ int type() default 0; /** * 导出字段对齐方式（0：自动；1：靠左；2：居中；3：靠右） */ int align() default 0; /** * 导出字段字段排序（升序） */ int sort() default 0; /** * 如果是字典类型，请设置字典的type值 */ String dictType() default ""; /** * 反射类型 */ Class<?> fieldType() default Class.class; /** * 字段归属组（根据分组导出导入） */ int[] groups() default {}; } ExportExcel工具类： /** * Copyright &copy; 2012-2016 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved. */ package com.ysailine.utils.excel; import com.google.common.collect.Lists; import com.ysailine.common.service.DictService; import com.ysailine.common.service.impl.DictServiceImpl; import com.ysailine.utils.Encodes; import com.ysailine.utils.ReflectUtils; import com.ysailine.utils.excel.annotation.ExcelField; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFClientAnchor; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.servlet.http.HttpServletResponse; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.math.BigDecimal; import java.util.*; /** * 导出Excel文件（导出“XLSX”格式，支持大数据量导出 @see org.apache.poi.ss.SpreadsheetVersion） * * @author ThinkGem * @version 2013-04-21 */ public class ExportExcel { DictService dictService = new DictServiceImpl(); private static Logger log = LoggerFactory.getLogger(ExportExcel.class); /** * 工作薄对象 */ private SXSSFWorkbook wb; /** * 工作表对象 */ private Sheet sheet; public Sheet getSheet() { return sheet; } /** * 样式列表 */ private Map<String, CellStyle> styles; /** * 当前行号 */ private int rownum; /** * 注解列表（Object[]{ ExcelField, Field/Method }） */ List<Object[]> annotationList = Lists.newArrayList(); /** * 构造函数 * * @param title 表格标题，传“空值”，表示无标题 * @param cls 实体对象，通过annotation.ExportField获取标题 */ public ExportExcel(String title, Class<?> cls) { this(title, cls, 1); } /** * 构造函数 * * @param title 表格标题，传“空值”，表示无标题 * @param cls 实体对象，通过annotation.ExportField获取标题 * @param type 导出类型（1:导出数据；2：导出模板） * @param groups 导入分组 */ public ExportExcel(String title, Class<?> cls, int type, int... groups) { setAnnotationList(cls, type, groups); // Initialize List<String> headerList = Lists.newArrayList(); for (Object[] os : annotationList) { String t = ((ExcelField) os[0]).title(); // 如果是导出，则去掉注释 if (type == 1) { String[] ss = StringUtils.split(t, "**", 2); if (ss.length == 2) { t = ss[0]; } } headerList.add(t); } initialize(title, headerList); } /** * 构造函数 * * @param title 表格标题，传“空值”，表示无标题 * @param headers 表头数组 */ public ExportExcel(String title, String[] headers) { initialize(title, Lists.newArrayList(headers)); } /** * 构造函数 * * @param title 表格标题，传“空值”，表示无标题 * @param headerList 表头列表 */ public ExportExcel(String title, List<String> headerList) { initialize(title, headerList); } /** * 构造函数 */ public ExportExcel() { initialize(null, null); } /** * 初始化函数 * * @param title 表格标题，传“空值”，表示无标题 * @param headerList 表头列表 */ private void initialize(String title, List<String> headerList) { this.wb = new SXSSFWorkbook(500); if (StringUtils.isNotBlank(title)) { this.sheet = wb.createSheet(title); } else { this.sheet = wb.createSheet(); } this.styles = createStyles(wb); // Create title if (StringUtils.isNotBlank(title)) { Row titleRow = sheet.createRow(rownum++); titleRow.setHeightInPoints(30); Cell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styles.get("title")); titleCell.setCellValue(title); sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), headerList.size() - 1)); } // Create header if (headerList != null && headerList.size() > 0) { Row headerRow = sheet.createRow(rownum++); headerRow.setHeightInPoints(16); for (int i = 0; i < headerList.size(); i++) { Cell cell = headerRow.createCell(i); cell.setCellStyle(styles.get("header")); String[] ss = StringUtils.split(headerList.get(i), "**", 2); if (ss.length == 2) { cell.setCellValue(ss[0]); Comment comment = this.sheet.createDrawingPatriarch() .createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6)); comment.setString(new XSSFRichTextString(ss[1])); cell.setCellComment(comment); } else { cell.setCellValue(headerList.get(i)); } sheet.autoSizeColumn(i); } for (int i = 0; i < headerList.size(); i++) { int colWidth = sheet.getColumnWidth(i) * 2; sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth); } } log.debug("Initialize success."); } /** * 创建表格样式 * * @param wb 工作薄对象 * @return 样式列表 */ private Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); Font titleFont = wb.createFont(); titleFont.setFontName("Arial"); titleFont.setFontHeightInPoints((short) 16); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(titleFont); styles.put("title", style); style = wb.createCellStyle(); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); Font dataFont = wb.createFont(); dataFont.setFontName("Arial"); dataFont.setFontHeightInPoints((short) 10); style.setFont(dataFont); styles.put("data", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_LEFT); styles.put("data1", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_CENTER); styles.put("data2", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_RIGHT); styles.put("data3", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); // style.setWrapText(true); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); Font headerFont = wb.createFont(); headerFont.setFontName("Arial"); headerFont.setFontHeightInPoints((short) 10); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); style.setFont(headerFont); styles.put("header", style); return styles; } /** * 添加一行 * * @return 行对象 */ public Row addRow() { return sheet.createRow(rownum++); } /** * 添加一个单元格 * * @param row 添加的行 * @param column 添加列号 * @param val 添加值 * @return 单元格对象 */ public Cell addCell(Row row, int column, Object val) { return this.addCell(row, column, val, 0, Class.class); } /** * 添加一个单元格 * * @param row 添加的行 * @param column 添加列号 * @param val 添加值 * @param align 对齐方式（1：靠左；2：居中；3：靠右） * @return 单元格对象 */ public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType) { Cell cell = row.createCell(column); String cellFormatString = "@"; val = val == null ? "" : val; try { if (val == null) { cell.setCellValue(""); } else if (fieldType != Class.class) { cell.setCellValue((String) fieldType.getMethod("setValue", Object.class).invoke(null, val)); } else { if (val instanceof String) { cell.setCellValue((String) val); } else if (val instanceof Integer) { cell.setCellValue((Integer) val); cellFormatString = "0"; } else if (val instanceof Long) { cell.setCellValue((Long) val); cellFormatString = "0"; } else if (val instanceof Double) { cell.setCellValue((Double) val); cellFormatString = "0.00"; } else if (val instanceof Float) { cell.setCellValue((Float) val); cellFormatString = "0.00"; } else if (val instanceof Date) { cell.setCellValue((Date) val); cellFormatString = "yyyy-MM-dd HH:mm"; } else { cell.setCellValue((String) Class .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), "fieldtype." + val.getClass().getSimpleName() + "Type")) .getMethod("setValue", Object.class).invoke(null, val)); } } if (val != null) { CellStyle style = styles.get("data_column_" + column); if (style == null) { style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data" + (align >= 1 && align <= 3 ? align : ""))); style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString)); styles.put("data_column_" + column, style); } cell.setCellStyle(style); } } catch (Exception ex) { log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString()); cell.setCellValue(val.toString()); } return cell; } /** * 添加一个单元格 * * @param row 添加的行 * @param column 添加列号 * @param val 添加值 * @param align 对齐方式（1：靠左；2：居中；3：靠右） * @return 单元格对象 */ public Cell addCell(Row row, int column, Object val, int align) { Cell cell = row.createCell(column); String cellFormatString = "@"; boolean isFotmat = true; try { if (val == null) { cell.setCellValue(""); isFotmat = false; } else { if (val instanceof Integer) { cell.setCellValue((Integer) val); cellFormatString = "0"; } else if (val instanceof Long) { cell.setCellValue((Long) val); cellFormatString = "0"; } else if (val instanceof Double) { cell.setCellValue((Double) val); cellFormatString = "0.00"; } else if (val instanceof Float) { cell.setCellValue((Float) val); cellFormatString = "0.00"; } else if (val instanceof Date) { cell.setCellValue((Date) val); cellFormatString = "yyyy-MM-dd HH:mm"; } else if (val instanceof BigDecimal) { cell.setCellValue(val.toString()); } else if (val instanceof String) { cell.setCellValue((String) val); } else { cell.setCellValue((String) Class .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), "fieldtype." + val.getClass().getSimpleName() + "Type")) .getMethod("setValue", Object.class).invoke(null, val)); isFotmat = false; } } } catch (Exception ex) { log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString()); cell.setCellValue(val.toString()); } try { CellStyle style = styles.get("data_column_" + column); if (style == null) { style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data" + (align >= 1 && align <= 3 ? align : ""))); styles.put("data_column_" + column, style); } if (isFotmat) { style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString)); } cell.setCellStyle(style); } catch (Exception ex) { ex.printStackTrace(); } return cell; } /** * 添加一个单元格 * * @param row 添加的行 * @param column 添加列号 * @param val 添加值 * @param align 对齐方式（1：靠左；2：居中；3：靠右） * @param color * @return 单元格对象 */ public Cell addCell(Row row, int column, Object val, int align, Short color) { Cell cell = row.createCell(column); String cellFormatString = "@"; boolean isFotmat = true; try { if (val == null) { cell.setCellValue(""); isFotmat = false; } else { if (val instanceof Integer) { cell.setCellValue((Integer) val); cellFormatString = "0"; } else if (val instanceof Long) { cell.setCellValue((Long) val); cellFormatString = "0"; } else if (val instanceof Double) { cell.setCellValue((Double) val); cellFormatString = "0.00"; } else if (val instanceof Float) { cell.setCellValue((Float) val); cellFormatString = "0.00"; } else if (val instanceof Date) { cell.setCellValue((Date) val); cellFormatString = "yyyy-MM-dd HH:mm"; } else if (val instanceof BigDecimal) { cell.setCellValue(val.toString()); } else if (val instanceof String) { cell.setCellValue((String) val); } else { cell.setCellValue((String) Class .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), "fieldtype." + val.getClass().getSimpleName() + "Type")) .getMethod("setValue", Object.class).invoke(null, val)); isFotmat = false; } } } catch (Exception ex) { log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString()); cell.setCellValue(val.toString()); } try { CellStyle style = styles.get("data_column_" + column); Font font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示 style.setFont(font);//选择需要用到的字体格式 if (style == null) { style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data" + (align >= 1 && align <= 3 ? align : ""))); styles.put("data_column_" + column, style); } else { style.cloneStyleFrom(styles.get("data" + (align >= 1 && align <= 3 ? align : ""))); } if (isFotmat) { style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString)); } if (color != null) { style.setFillForegroundColor(color); style.setFillPattern(CellStyle.SOLID_FOREGROUND); } cell.setCellStyle(style); } catch (Exception ex) { ex.printStackTrace(); } return cell; } /** * 设置 数据拦截列表 * * @param cls */ public ExportExcel setAnnotationList(Class<?> cls, int type, int... groups) { if (annotationList != null) { annotationList.clear(); } // Get annotation field Field[] fs = cls.getDeclaredFields(); for (Field f : fs) { ExcelField ef = f.getAnnotation(ExcelField.class); if (ef != null && (ef.type() == 0 || ef.type() == type)) { if (groups != null && groups.length > 0) { boolean inGroup = false; for (int g : groups) { if (inGroup) { break; } for (int efg : ef.groups()) { if (g == efg) { inGroup = true; annotationList.add(new Object[]{ef, f}); break; } } } } else { annotationList.add(new Object[]{ef, f}); } } } // Get annotation method Method[] ms = cls.getDeclaredMethods(); for (Method m : ms) { ExcelField ef = m.getAnnotation(ExcelField.class); if (ef != null && (ef.type() == 0 || ef.type() == type)) { if (groups != null && groups.length > 0) { boolean inGroup = false; for (int g : groups) { if (inGroup) { break; } for (int efg : ef.groups()) { if (g == efg) { inGroup = true; annotationList.add(new Object[]{ef, m}); break; } } } } else { annotationList.add(new Object[]{ef, m}); } } } // Field sorting Collections.sort(annotationList, new Comparator<Object[]>() { public int compare(Object[] o1, Object[] o2) { return new Integer(((ExcelField) o1[0]).sort()).compareTo(new Integer(((ExcelField) o2[0]).sort())); } ; }); return this; } /** * 设置 数据拦截列表 * * @param cls */ public ExportExcel setAnnotationList(Class<?> cls) { return setAnnotationList(cls, 1); } /** * 添加数据（通过annotation.ExportField添加数据） * * @return list 数据列表 */ public <E> ExportExcel setDataList(List<E> list) { for (E e : list) { int colunm = 0; Row row = this.addRow(); StringBuilder sb = new StringBuilder(); for (Object[] os : annotationList) { ExcelField ef = (ExcelField) os[0]; Object val = null; // Get entity value try { if (StringUtils.isNotBlank(ef.value())) { val = ReflectUtils.invokeGetter(e, ef.value()); } else { if (os[1] instanceof Field) { val = ReflectUtils.invokeGetter(e, ((Field) os[1]).getName()); } else if (os[1] instanceof Method) { val = ReflectUtils.invokeMethod(e, ((Method) os[1]).getName(), new Class[]{}, new Object[]{}); } } // If is dict, get dict label if (StringUtils.isNotBlank(ef.dictType())) { val = dictService.getName(val == null ? "" : val.toString(), ef.dictType()); } } catch (Exception ex) { // Failure to ignore log.info(ex.toString()); val = ""; } this.addCell(row, colunm++, val, ef.align(), ef.fieldType()); sb.append(val + ", "); } log.debug("Write success: [" + row.getRowNum() + "] " + sb.toString()); } return this; } /** * 输出数据流 * * @param os 输出数据流 */ public ExportExcel write(OutputStream os) throws IOException { wb.write(os); return this; } /** * 输出到客户端 * * @param fileName 输出文件名 */ public ExportExcel write(HttpServletResponse response, String fileName) throws IOException { response.reset(); response.setContentType("application/octet-stream; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename=" + Encodes.urlEncode(fileName)); write(response.getOutputStream()); return this; } /** * 输出到文件 * * @param fileName 输出文件名 */ public ExportExcel writeFile(String name) throws FileNotFoundException, IOException { FileOutputStream os = new FileOutputStream(name); this.write(os); return this; } /** * 清理临时文件 */ public ExportExcel dispose() { wb.dispose(); return this; } // /** // * 导出测试 // */ // public static void main(String[] args) throws Throwable { // // List<String> headerList = Lists.newArrayList(); // for (int i = 1; i <= 10; i++) { // headerList.add("表头"+i); // } // // List<String> dataRowList = Lists.newArrayList(); // for (int i = 1; i <= headerList.size(); i++) { // dataRowList.add("数据"+i); // } // // List<List<String>> dataList = Lists.newArrayList(); // for (int i = 1; i <=1000000; i++) { // dataList.add(dataRowList); // } // // ExportExcel ee = new ExportExcel("表格标题", headerList); // // for (int i = 0; i < dataList.size(); i++) { // Row row = ee.addRow(); // for (int j = 0; j < dataList.get(i).size(); j++) { // ee.addCell(row, j, dataList.get(i).get(j)); // } // } // // ee.writeFile("target/export.xlsx"); // // ee.dispose(); // // log.debug("Export success."); // // } }