需求.提供公共的可以按照一定条件查询出结果，并提供将查询结果全部下载功能(Excel、CSV、TXT)，由于一次性查出结果放到内存会占用大量内存.需要支持分页模式查询出所有数据。
实现思路
1.在公共的controller（BaseController）中增加下载方法
2.支持自定义分页查询方式、自定义表头和查询结果属性对应
@ResponseBody @RequestMapping("/exportExcel.json") public void exportExcel(HttpServletRequest request, HttpServletResponse response, final DeductCurrentsQry qry) throws Exception { // 从零行开始导出 qry.setStart(0); // 分页设置大一点，提高导出效率 qry.setLimit(50); //开始导出数据 DownloadDataLoader<DeductCurrentVo> loader = new DownloadDataLoader<DeductCurrentVo>() { @Override protected List<DeductCurrentVo> getDownloadData(Integer pageNum) { // pageNum 初始值为0，在下载工具类中，通过对pageNum的自加，达到分页查询效果 qry.setStart(pageNum * qry.getLimit()); PageDataList<DeductCurrentVo> pageBean = getPageDataList(qry); return pageBean.getRows(); } }; String[] hearders = new String[] {"客户ID", "抵用券编号", "抵用券类型", "起投限制", "抵用券面值", "投资金额", "产品代码", "抵用券状态", "抵用券兑换码", "发放时间", "使用时间" }; String[] fields = new String[] { "customerId", "id", "deductType", "minInvestAmount", "faceValueFormat", "investAmount", "productCode", "deductStatus", "deductSn", "createDatetimeFormat", "usedDatetimeFormat" }; this.download(response,String.format("抵用券记录_%S.xls", DateUtil.dateStr(new Date())), Arrays.asList(hearders), null, loader, Arrays.asList(fields)); }
package com.wjs.common.web; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.net.URLEncoder; import java.util.ArrayList; import java.util.Collection; import java.util.Date; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.commons.beanutils.BeanUtils; import org.apache.commons.collections.CollectionUtils; import org.apache.commons.httpclient.util.DateUtil; import org.apache.commons.io.FilenameUtils; import org.apache.commons.io.IOUtils; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Controller; import org.springframework.web.bind.ServletRequestDataBinder; import org.springframework.web.bind.annotation.InitBinder; import com.wjs.common.util.StringEscapeEditor; import com.wjs.common.util.excel.ExcelUtils; /** * 基础控制器 * * 其他控制器继承此控制器获得日期字段类型转换和防止XSS攻击的功能 * * @author Moon * */ @Controller public class BaseController { private static final Logger LOGGER = LoggerFactory.getLogger(BaseController.class); @InitBinder public void initBinder(ServletRequestDataBinder binder) { /** * 自动转换日期类型的字段格式 */ // binder.registerCustomEditor(Date.class, new CustomDateEditor( // new SimpleDateFormat("yyyy-MM-dd"), true)); /** * 防止XSS攻击 */ binder.registerCustomEditor(String.class, new StringEscapeEditor(true, false)); } /** * http请求成功时调用 * * @return * * @author chenchunhui */ protected <T> JsonResult<T> success() { return this.success("操作成功", null); } /** * http请求成功时调用 * * @param data * 返回给前台的数据 * @return 返回给前台的标准json对象 */ protected <T> JsonResult<T> success(T data) { return this.success("操作成功", data); } /** * http请求成功时调用 * * @param msg * 信息说明 * @param data * 返回给前端的数据 * @param <T> * @return 返回给前台的标准json对象 * * @author chenchunhui */ protected <T> JsonResult<T> success(String msg, T data) { JsonResult<T> result = new JsonResult<T>(JsonResult.Status.SUCCESS, msg, data); if (LOGGER.isDebugEnabled()) { String logString = result.toString(); if (logString.length() > 1024) { logString = logString.substring(0, 1024); } LOGGER.debug(logString); } return result; } /** * http请求失败时调用 * * @return 返回给前台的标准json对象 * * @author chenchunhui */ protected <T> JsonResult<T> error() { return this.error("系统错误"); } /** * http请求失败时调用 * * @param msg * 信息说明 * @return 返回给前台的标准json对象 * * @author chenchunhui */ protected <T> JsonResult<T> error(String msg) { JsonResult<T> result = new JsonResult<T>(JsonResult.Status.ERROR, msg); if (LOGGER.isInfoEnabled()) { String logString = result.toString(); if (logString.length() > 1024) { logString = logString.substring(0, 1024); } LOGGER.info(logString); } return result; } /** * 分页下载数据获取类 * @author Silver * @date 2017年3月16日 上午11:45:13 * * @param <T> * */ protected abstract class DownloadDataLoader<T> { /** * 分页下载属性值控制操作类 * @param bean * @param propertyName * @param property * @return * @author Silver * @date 2017年3月16日 上午11:45:45 */ protected String convertProperty(T bean, String propertyName, Object property) { return property == null ? "" : property.toString(); } /** * 分页下载属性赋值 * @param bean * @param propertyName * @return * @author Silver * @date 2017年3月16日 上午11:46:37 */ protected Object getProperty(T bean, String propertyName) { try { return BeanUtils.getProperty(bean, propertyName); } catch (Throwable e) { LOGGER.info("bean:" + bean + ",Property:" + propertyName + e.getMessage(), e); return null; } } /** * 数据获取接口 * @param pageNum -- 从0计数 * @return * @throws Exception * @author Silver * @date 2017年3月16日 上午11:47:07 */ protected abstract List<T> getDownloadData(Integer pageNum) throws Exception; }; protected static interface Writer { public void write(Collection<String> row) throws IOException; } /** * Web下载文件 * @param response httpResponse信息 * @param fileName 文件名称，如果文件名称为空的情况默认【 日期.csv】格式 * @param header 表头名称 * @param columnWidth 列宽 * @param loader 数据加载类 * @param queryParam 如果有查询条件的传递 * @param propertyNames * @throws Exception * @author Silver * @date 2017年3月16日 上午11:47:31 */ protected <T> void download(HttpServletResponse response, String fileName, List<String> header, List<Integer> columnWidth, DownloadDataLoader<T> loader, List<String> propertyNames) throws Exception { if (StringUtils.isEmpty(fileName) || loader == null || CollectionUtils.isEmpty(propertyNames)) { throw new RuntimeException("参数错误。FileName:" + fileName + ",DataLoader:" + loader + ",PropertyName:" + propertyNames); } // 获取输出流,设置content-type等头域 final OutputStream out = getResponseStream(response ,fileName); try { Writer writer = null; // 获取文件后缀名 String extension = FilenameUtils.getExtension(fileName); // 如果是excel的后缀 if ("xls".equalsIgnoreCase(extension) || "xlsx".equalsIgnoreCase(extension)) { Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("sheet1"); final List<Collection<String>> rows = new ArrayList<Collection<String>>(); writer = new Writer() { @Override public void write(Collection<String> row) { rows.add(row); } }; writeOutputStream(loader, propertyNames, writer); // 写入excel if (!ExcelUtils.setExcelInfo(sheet, columnWidth, header, rows)) { throw new IOException("设置导出文件内容失败。"); } workbook.write(out); } else if("csv".equalsIgnoreCase(extension)) { writer = new Writer() { @Override public void write(Collection<String> row) throws IOException { String str = ExcelUtils.collectionToCsvString(row); byte[] content = org.apache.commons.codec.binary.StringUtils.getBytesUnchecked(str + " ", "GBK"); IOUtils.write(content, out); out.flush(); } }; // 写文件头 writer.write(header); // 写文件 writeOutputStream(loader, propertyNames, writer); }else{ writer = new Writer() { @Override public void write(Collection<String> row) throws IOException { IOUtils.write(org.apache.commons.codec.binary.StringUtils.getBytesUnchecked(row + " ", "GBK"), out); out.flush(); } }; // 写文件头 writer.write(header); // 写文件 writeOutputStream(loader, propertyNames, writer); } out.flush(); } finally { IOUtils.closeQuietly(out); } } /** * 获得输出流 * * @return * @throws IOException */ protected OutputStream getResponseStream(HttpServletResponse response, String fileName) throws IOException { if (StringUtils.isEmpty(fileName)) { fileName = DateUtil.formatDate(new Date(), "yyyy-MM-dd_HH-mm-ss") + ".csv"; } response.reset(); String extension = FilenameUtils.getExtension(fileName); if("xlsx".equalsIgnoreCase(extension)){ // 部分window版本生成后的xlsx打不开，默认改成xls打开 fileName = fileName.substring(0, fileName.length() -1); } //设置响应编码 response.setCharacterEncoding("UTF-8"); //设置对应的contentType response.setContentType("application/x-download;charset=UTF-8"); // response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("gb2312"), "ISO-8859-1")); OutputStream out = response.getOutputStream(); return out; } protected <T> void writeOutputStream(DownloadDataLoader<T> loader, List<String> propertyNames, Writer writer) throws Exception { int pageNum = 0; int maxLenth = 102400; while (maxLenth-- > 0) { // 分页获取数据 List<T> objList = null; try { objList = loader.getDownloadData(pageNum++); } catch (Exception e) { LOGGER.error("获得到处数据异常:{}",e.getMessage(), e); } if (CollectionUtils.isEmpty(objList)) { break; } for (T bean : objList) { if (bean == null) { continue; } Collection<String> result = new ArrayList<String>(); // 遍历指定属性 for (String name : propertyNames) { // 获得属性值 Object property = loader.getProperty(bean, name); // 将属性值转换成字符串 String convertValue = loader.convertProperty(bean, name, property); // 组装成row result.add(convertValue); } if (CollectionUtils.isEmpty(result)) { continue; } writer.write(result); } } } }
package com.wjs.common.util.excel; import java.io.BufferedInputStream; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Collection; import java.util.Date; import java.util.Iterator; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletResponse; import org.apache.commons.beanutils.BeanMap; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.springframework.util.CollectionUtils; /** * * @author liqiang05 * */ public class ExcelUtils { /** * logger */ private static final Logger logger = Logger.getLogger(ExcelUtils.class); /** * 设置列格式 * * @param sheet * @param columnWidthList */ public static boolean setSheetStyle(Sheet sheet, List<Integer> columnWidthList) { if (sheet == null || columnWidthList == null) { return false; } // 设置所有列的宽度 for (int indx = 0, iMax = columnWidthList.size(); indx < iMax; indx++) { Integer columnWidth = columnWidthList.get(indx); if (columnWidth == null) { continue; } sheet.setColumnWidth(indx, columnWidth.intValue() * 256); } return true; } /** * 设置行信息 * * @param row * @param rowObj * @return */ public static boolean setRowInfo(Row row, Collection<Object> rowObj) { if (row == null || rowObj == null) { if (logger.isInfoEnabled()) { logger.info("Row:" + row + ",rowObj" + rowObj); } return false; } // 填充每一列数据 int indxColumn = 0; for (Object object : rowObj) { Cell cell = row.createCell(indxColumn++); if (object == null) { if (logger.isDebugEnabled()) { logger.debug("Row:" + row + ",Column:" + indxColumn + ",is empty"); } continue; } String columnValue = object.toString(); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(columnValue); } return true; } /** * 设置行信息 * * @param row * @param rowObj * @param convert */ @SuppressWarnings({ "rawtypes", "unchecked" }) public static boolean setRowInfo(Row row, Object rowObj, IExcelConvert convert) { if (row == null || rowObj == null) { if (logger.isInfoEnabled()) { logger.info("Row:" + row + ",rowObj" + rowObj); } return false; } try { Collection<Object> rowContent = null; if (convert != null) { rowContent = convert.convert(rowObj); } else if (rowObj instanceof Map) { rowContent = ((Map) rowObj).values(); } else if (rowObj instanceof Collection) { rowContent = (Collection) rowObj; } else { rowContent = (new BeanMap(rowObj)).values(); } if (rowContent == null || rowContent.isEmpty()) { if (logger.isDebugEnabled()) { logger.debug("Row:" + row + ",is empty"); } return false; } return setRowInfo(row, rowContent); } catch (Throwable e) { logger.info(rowObj + "convertFailed,row:" + row, e); return false; } } /** * 将数据写入excel * * @param sheet * @param columnWidth * @param header * @param content * @return */ public static boolean setExcelInfo(Sheet sheet, List<Integer> columnWidth, List<String> header, List<?> rows) { return setExcelInfo(sheet, columnWidth, header, rows, null); } /** * 将数据写入excel * * @param sheet * @param columnWidth * @param header * @param content * @param converter * @return */ public static boolean setExcelInfo(Sheet sheet, List<Integer> columnWidth, List<String> header, List<?> content, IExcelConvert converter) { if (sheet == null) { logger.info("sheet is null"); return false; } // 设置sheet格式 setSheetStyle(sheet, columnWidth); // 设置头信息 int indxRow = 0; Row row = sheet.createRow(indxRow++); setRowInfo(row, header, null); // 如果内容为空 则退出 if (content == null || content.isEmpty()) { logger.info("content is null,cannot write excel"); return true; } for (Object rowContent : content) { row = sheet.createRow(indxRow++); setRowInfo(row, rowContent, converter); } return true; } /** * 導出到excel * * @param title * sheet Title * @param columnWidthList * 所有列的寬度,可以不指定 * @param content * 內容, 每一項為一行,每一行內是List代表所有列 * @return */ public static Workbook setupXls(String title, List<Integer> columnWidthList, List<List<String>> content) { Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet(title); if (columnWidthList != null) { // 设置所有列的宽度 for (int indx = 0, iMax = columnWidthList.size(); indx < iMax; indx++) { Integer columnWidth = columnWidthList.get(indx); if (columnWidth == null) { continue; } sheet.setColumnWidth(indx, columnWidth.intValue() * 256); } } if (content == null || content.isEmpty()) { if (logger.isInfoEnabled()) { logger.info("content is null,cannot write excel,title:" + title); } return wb; } // 遍歷一行 for (int indxRow = 0, iMaxRow = content.size(); indxRow < iMaxRow; indxRow++) { Row row = sheet.createRow(indxRow); List<String> rowContent = content.get(indxRow); if (rowContent == null || rowContent.isEmpty()) { if (logger.isDebugEnabled()) { logger.debug("Row:" + indxRow + ",is empty,title:" + title); } continue; } // 填充每一列数据 for (int indxColumn = 0, iMaxColumn = rowContent.size(); indxColumn < iMaxColumn; indxColumn++) { Cell cell = row.createCell(indxColumn); String columnValue = rowContent.get(indxColumn); if (columnValue == null || columnValue.length() == 0) { if (logger.isDebugEnabled()) { logger.debug("Row:" + indxRow + ",Column:" + indxColumn + ",is empty,title:" + title); } continue; } cell.setCellValue(columnValue); } } return wb; } /** * 加載Excel 默認實現方式 * * @param wb * @return */ public static List<List<String>> loadXls(Workbook wb) { // 默認 只讀第一個sheet, 且從第二行開始遍歷,默认读取到最大列 return loadXls(wb, 0, 1, 0); } /** * 加载excel * * @param wb * @param sheetIndx * 要加载excel的sheet页的index * @param startRowIndx * 要加载Row的index * @param iMaxColumn * 最大读到Cloumn的index * @return List<List<>> */ public static List<List<String>> loadXls(Workbook wb, int sheetIndx, int startRowIndx, int iMaxColumn) { List<List<String>> resList = new ArrayList<List<String>>(); if (wb == null || sheetIndx < 0 || startRowIndx < 0 || iMaxColumn < 0) { logger.error("param error,return empty list,Workbook:" + wb + ",sheetIndex:" + sheetIndx + ",startRowNo:" + startRowIndx + ",iMaxColumn:" + iMaxColumn); return resList; } Sheet sheet = wb.getSheetAt(sheetIndx); if (sheet == null) { logger.error("sheet is null,return empty list,Workbook:" + wb + ",sheetIndex:" + sheetIndx + ",startRowNo:" + startRowIndx); return resList; } // 從指定行開始遍歷 for (int indxRow = startRowIndx, iMaxRow = sheet.getLastRowNum(); indxRow <= iMaxRow; indxRow++) { Row row = sheet.getRow(indxRow); if (row == null) { if (logger.isDebugEnabled()) { logger.debug("Row is null,sheetIndex:" + sheetIndx + ",RowNo:" + indxRow); } continue; } List<String> rowContent = new ArrayList<String>(); // 當最大列為0時 讀取最大CellNum if (iMaxColumn == 0) { iMaxColumn = row.getLastCellNum(); } boolean hasContent = false; for (int indxColumn = 0; indxColumn < iMaxColumn; indxColumn++) { String cellValue = null; Cell cell = row.getCell(indxColumn); if (cell == null) { if (logger.isDebugEnabled()) { logger.debug("Cell is null,sheetIndex:" + sheetIndx + ",RowNo:" + indxRow + ",CellNo:" + indxColumn); } } else { cellValue = getCellStrValue(cell); } // 如果 读到的内容不是空 代表这行有数据 if (cellValue != null && cellValue.length() > 0) { hasContent = true; } // 不论当前格是否有数据都加入. rowContent.add(cellValue); } // 这一行有内容 则加入 if (hasContent) { resList.add(rowContent); } } return resList; } public static String getCellStrValue(Cell cell) { String res = ""; try { res = cell.getStringCellValue(); } catch (Exception e) { DecimalFormat df = new DecimalFormat("#"); res = df.format(cell.getNumericCellValue()) + ""; } return res; } /** * * @Description: 将集合转换成字符串输出 * @param coll * @return 设定文件 * @throws 异常说明 * @author albert.su suzy@malam.com * @date 2014年5月7日 下午12:35:55 */ public static String collectionToCsvString(Collection<?> coll) { if (CollectionUtils.isEmpty(coll)) { return ""; } StringBuilder sb = new StringBuilder(); Iterator<?> it = coll.iterator(); while (it.hasNext()) { Object object = it.next(); if (String.valueOf(object).matches("[0-9,\.]+") || String.valueOf(object).contains(",")) { sb.append("" "); sb.append(object); sb.append("""); } else { sb.append(" "); sb.append(object); } if (it.hasNext()) { sb.append(","); } } return sb.toString(); } // 以下为糯米的代码, 建议少用 /** * 从InputStream读取Excel workbook * * @param ins * @return * @throws IOException * @throws FileNotFoundException */ public static HSSFWorkbook readWorkbook(InputStream ins) throws IOException, FileNotFoundException { ByteArrayOutputStream byteOS = new ByteArrayOutputStream(); BufferedInputStream bis = new BufferedInputStream(ins); byte[] by = new byte[512]; int t = bis.read(by, 0, by.length); while (t > 0) { byteOS.write(by, 0, 512); // read 512 t = bis.read(by, 0, by.length); } byteOS.close(); InputStream byteIS = new ByteArrayInputStream(byteOS.toByteArray()); HSSFWorkbook wbDest = new HSSFWorkbook(byteIS); return wbDest; } public static void writeToResponse(HttpServletResponse response, HSSFWorkbook wb, String fileName) throws IOException { response.setContentType("application/ms-download"); response.setCharacterEncoding("gb2312"); response.setHeader("Content-Disposition", "filename=" + fileName); OutputStream out = response.getOutputStream(); wb.write(out); out.flush(); out.close(); } /** * 判断单元格的格式 * * @param cell * 单元格 * @return String 将excel各种单元格的类型转换为String类型 */ public static String getCellStringValue(HSSFCell cell) { // 转换后单元格的值 String value = ""; if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: value = cell.getRichStringCellValue().getString(); break; case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); value = sdf.format(date); } else { DecimalFormat formatter = new DecimalFormat("########"); value = formatter.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_FORMULA: cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); value = String.valueOf(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_BOOLEAN: break; case HSSFCell.CELL_TYPE_ERROR: break; default: break; } } return value; } /** * 到出excel的Helper类 * * @author Administrator * */ public static class ExcelRowBuilder { private HSSFRow row; private short index = 0; public ExcelRowBuilder(HSSFRow row) { this.row = row; } @SuppressWarnings("deprecation") public ExcelRowBuilder addCell(String str) { HSSFCell cell = row.createCell(index++); cell.setCellValue(new HSSFRichTextString(str)); return this; } @SuppressWarnings("deprecation") public ExcelRowBuilder addCell(long value) { HSSFCell cell = row.createCell(index++); cell.setCellValue(value); return this; } @SuppressWarnings("deprecation") public ExcelRowBuilder addCell(double value) { HSSFCell cell = row.createCell(index++); cell.setCellValue(value); return this; } } }