对对象的list集合以excel表格导出
1首先引入接口类
package com.hailian.util; import javax.servlet.http.HttpServletResponse; /** * 将数据导出到excel接口定义 * @author WangXuzheng * */ public interface ExcelExportTemplate<T> { /** * 将数据导出为excel * @param outputStream 文件输出流 * @param parameters 参数 */ public void doExport(HttpServletResponse response,String fileName)throws Exception; /** * 要创建的excel文件的sheet名称 * @return */ public String[] getSheetNames(); /** * 要创建的excel表格中的表头内容. * list中存放的是多个sheet的表头内容 * @return */ public String[][] getTitles(); /** * 要创建的excel表格的每个sheet的表头 * @return */ public String[] getCaptions(); /** * 控制文件在内存中最多占用多少条 * @return */ public int getRowAccessWindowSize(); }
2表格导出的实现类
package com.hailian.util; import java.io.*; import java.lang.reflect.Method; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletResponse; import org.apache.commons.lang.StringUtils; import org.apache.commons.lang.Validate; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.util.HSSFColor; 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.IndexedColors; 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.xssf.streaming.SXSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * @author WangXuzheng * */ public abstract class SettlrExcelExportTemplate<T> implements ExcelExportTemplate<T> { Logger logger=LoggerFactory.getLogger(SettlrExcelExportTemplate.class); /** * 默认表格宽度 */ private static final int DEFAULT_COLUMN_WIDTH = 5000; /** * excel文件对象 */ protected Workbook workbook; /** * excel sheet列表 */ protected List<Sheet> sheets = new ArrayList<Sheet>(); /** * 标题栏 */ protected String[][] titles; protected CellStyle captionRowSytle; /** * 默认标题行样式 */ protected CellStyle titleRowStyle; /** * 默认内容行样式 */ protected CellStyle bodyRowStyle; /** * 各个sheet是否包含抬头,key:sheet坐标，value:包含true,否则false */ protected Map<Integer, Boolean> hasCaptionMap = new HashMap<Integer, Boolean>(); /** * 默认单元格宽度 */ protected int columnWidth = DEFAULT_COLUMN_WIDTH; /** * 参数列表 */ protected T parameters; /* (non-Javadoc) * @see com.haier.openplatform.excel.ExcelExportService#doExport(java.io.OutputStream) */ @Override public void doExport(HttpServletResponse response,String fileName) throws IOException { String[] sheetNames = this.getSheetNames(); Validate.notEmpty(sheetNames); this.workbook = new SXSSFWorkbook(getRowAccessWindowSize()); this.titles = this.getTitles(); this.captionRowSytle = crateCaptionCellStyle(); this.titleRowStyle = crateTitleCellStyle(); this.bodyRowStyle = crateBodyCellStyle(); this.afterCreateWorkBook(); for (int i = 0; i < sheetNames.length; i++) { Sheet sheet = workbook.createSheet(sheetNames[i]); this.sheets.add(sheet); afterBuildSheet(i); buildCaption(i); buildTitle(i); afterBuildTitle(i); buildBody(i); afterBuildBody(i); } response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=" + fileName); OutputStream ouputStream = response.getOutputStream(); workbook.write(ouputStream); ouputStream.flush(); //workbook.close(); ouputStream.close(); } /** * 下载excel到指定目录 * @throws IOException */ public void downloadExcel(String path) throws IOException{ File file = new File(path); String[] sheetNames = this.getSheetNames(); Validate.notEmpty(sheetNames); this.workbook = new SXSSFWorkbook(getRowAccessWindowSize()); this.titles = this.getTitles(); this.captionRowSytle = crateCaptionCellStyle(); this.titleRowStyle = crateTitleCellStyle(); this.bodyRowStyle = crateBodyCellStyle(); this.afterCreateWorkBook(); for (int i = 0; i < sheetNames.length; i++) { Sheet sheet = workbook.createSheet(sheetNames[i]); this.sheets.add(sheet); afterBuildSheet(i); buildCaption(i); buildTitle(i); afterBuildTitle(i); buildBody(i); afterBuildBody(i); } BufferedOutputStream ouputStream = new BufferedOutputStream(new FileOutputStream(file)); workbook.write(ouputStream); ouputStream.flush(); ouputStream.close(); } /** * 创建单元格 * @param row * @param index * @param cellValue * @param cellStyle */ protected void createStyledCell(Row row,int index,String cellValue,CellStyle cellStyle){ Cell cell = row.createCell(index); cell.setCellValue(cellValue==null?"":cellValue); cell.setCellStyle(cellStyle); } /** * 创建单元格（数字类型） * @param row * @param index * @param cellValue * @param cellStyle * @throws Exception */ protected <E extends Number> void createNumStyledCell(Row row,int index,E cellValue,CellStyle cellStyle){ Cell cell = row.createCell(index); cell.setCellValue(numParseDouble(cellValue)); cell.setCellStyle(cellStyle); } /** * 数字类型转double * @param e * @return */ private <E extends Number> Double numParseDouble(E e){ double value=0; if(e!=null){ Method m; try { m = e.getClass().getMethod("doubleValue"); value=(Double)m.invoke(e); } catch (Exception e1) { logger.error(e1.getMessage(),e1); } } return value; } /** * 创建单元格 * @param row * @param index * @param cellValue * @param cellStyle */ protected void createStyledCell(Row row,int index,Double cellValue,CellStyle cellStyle){ Cell cell = row.createCell(index); if(cellValue==null){ cell.setCellValue(""); }else{ cell.setCellValue(cellValue); } cell.setCellStyle(cellStyle); } /** * 创建Integer单元格 * @param row * @param index * @param cellValue * @param cellStyle */ protected void createStyledCell(Row row,int index,Integer cellValue,CellStyle cellStyle){ Cell cell = row.createCell(index); if(cellValue==null){ cell.setCellValue(""); }else{ cell.setCellValue(cellValue); } cell.setCellStyle(cellStyle); } /** * @description 创建double类型单元格 * @author lau * @version 2016-7-13上午11:36:06 * @param */ protected void createStyledCell(Row row,int index,Long cellValue,CellStyle cellStyle){ Cell cell = row.createCell(index); if(cellValue==null){ cell.setCellValue(""); }else{ cell.setCellValue(cellValue); } cell.setCellStyle(cellStyle); } /** * 在创建完毕HSSFWorkBook对象和样式对象后作的处理操作，通常用来对默认的样式进行重新定义 */ protected void afterCreateWorkBook(){ } /** * 获取excel抬头样式 * @return */ protected CellStyle crateCaptionCellStyle() { Font font = workbook.createFont(); font.setColor(Font.COLOR_NORMAL); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setWrapText(false); font.setFontHeight((short)250); cellStyle.setFont(font); cellStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.index); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); return cellStyle; } /** * 获取excel表头样式 * @return */ protected CellStyle crateTitleCellStyle() { Font font = workbook.createFont(); font.setFontHeightInPoints((short) 9);// 字体大小 font.setColor(HSSFColor.WHITE.index);// 字体颜色 font.setFontName("微软雅黑"); // font.setColor(Font.COLOR_NORMAL); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setWrapText(false); font.setFontHeight((short)250); cellStyle.setFont(font); cellStyle.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); short border = 1; setCellBorder(cellStyle,border,border,border,border); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return cellStyle; } /** * 设置单元格的border * @param cellStyle * @param top * @param bottom * @param left * @param right */ protected void setCellBorder(CellStyle cellStyle,short top,short bottom,short left,short right){ cellStyle.setBorderBottom(bottom); cellStyle.setBorderLeft(left); cellStyle.setBorderRight(right); cellStyle.setBorderTop(top); } /** * 获取excel内容样式 * @return */ protected CellStyle crateBodyCellStyle() { Font font = workbook.createFont(); //font.setColor(HSSFColor.BLUE_GREY.index); font.setFontHeightInPoints((short) 9);// 字体大小 font.setColor(HSSFColor.BLACK.index);// 字体颜色 font.setFontName("微软雅黑"); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setWrapText(false); cellStyle.setFont(font); cellStyle.setFillForegroundColor(HSSFColor.WHITE.index); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(CellStyle.ALIGN_LEFT); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); short border = 1; setCellBorder(cellStyle,border,border,border,border); return cellStyle; } /** * 获取第n个excel sheet * @param sheetIndex * @return */ protected Sheet getSheet(int sheetIndex) { return this.sheets.get(sheetIndex); } /** * 创建sheet完毕后做的操作 * @param sheetIndex */ protected void afterBuildSheet(int sheetIndex) { } /** * 在sheet的第一行插入标题 * @param sheetIndex */ protected void buildCaption(int sheetIndex){ Sheet sheet = getSheet(sheetIndex); String[] captions = this.getCaptions(); hasCaptionMap.put(sheetIndex, false); if(captions != null && captions.length >=sheetIndex +1){ String caption = captions[sheetIndex]; if(StringUtils.isNotBlank(caption)){ Row row = sheet.createRow(0); int lastColumn = calculateLastColumn(sheetIndex); CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, lastColumn); sheet.addMergedRegion(cellRangeAddress); createStyledCell(row, 0, caption, this.captionRowSytle); hasCaptionMap.put(sheetIndex, true); } } } /** * 计算最后一列数据数据的 * @param sheetIndex * @return */ protected int calculateLastColumn(int sheetIndex){ if(this.titles != null && sheetIndex <= this.titles.length -1 && this.titles[sheetIndex] != null){ return this.titles[sheetIndex].length - 1; }else{ return 1; } } /** * 创建sheet中数据的标题 * @param sheetIndex */ protected void buildTitle(int sheetIndex){ // TODO Auto-generated method stub if(this.titles.length < sheetIndex + 1){ return; } String[] ts = this.titles[sheetIndex]; if(ts == null){ return; } //表头样式创建 titleRowStyle=crateTitleCellStyle(); //表头数填充 Sheet sheet=this.getSheet(sheetIndex); int titleStartRow=this.getTitleStartIndex(sheetIndex); for(int i=titleStartRow;i<this.titles.length+titleStartRow;i++){ Row title=sheet.createRow(i); for(int j=0;j<this.titles[i].length;j++){ sheet.setColumnWidth(j, columnWidth); createStyledCell(title, j, this.titles[i][j], titleRowStyle); } } } /** * 获取各个sheet内容部分起始行index,默认为从第一行开始 * @param sheetIndex sheet的index * @return */ protected int getBodyStartIndex(int sheetIndex){ int captionRow = getTitleStartIndex(sheetIndex);; int titleRow = 0; if(this.titles != null && this.titles.length >= sheetIndex + 1){ if(titles[sheetIndex] != null && titles[sheetIndex].length >0){ titleRow = 1; } } return captionRow + titleRow; } /** * 获取各个sheet内容部分起始行index,默认为从第一行开始，支持三行以上动态表头 * @param sheetIndex sheet的index * @return */ protected int getBodyStartIndex1(int sheetIndex){ int captionRow = getTitleStartIndex(sheetIndex);; int titleRow = 0; if(this.titles != null && this.titles.length >= sheetIndex + 1){ if(titles[sheetIndex] != null && titles.length >0){ titleRow = titles.length; } } return captionRow + titleRow; } protected int getTitleStartIndex(int sheetIndex){ return this.hasCaptionMap.get(sheetIndex) ? 1 : 0; } /** * 创建sheet中数据的标题之后做的操作 * @param sheetIndex */ protected void afterBuildTitle(int sheetIndex) { } /** * 创建sheet中数据的内容 * @param sheetIndex */ protected abstract void buildBody(int sheetIndex); /** * 创建sheet中数据的内容之后做的操作 * @param sheetIndex */ protected void afterBuildBody(int sheetIndex) { } @Override public String[] getCaptions() { return new String[]{}; } @Override public int getRowAccessWindowSize() { return 200; } }
3针对list设置表头，前两个类可以直接引入。这个类需要针对自己情况做设置
package com.hailian.util; import com.hailian.modules.admin.ordermanager.model.CreditOrderInfo; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.ss.usermodel.*; import java.util.ArrayList; import java.util.List; public class SettleExport extends SettlrExcelExportTemplate { private List<CreditOrderInfo> list = new ArrayList<CreditOrderInfo>(); public SettleExport(List<CreditOrderInfo> list) { super(); this.list = list; } @Override public String[] getSheetNames() { return new String[] { "订单结算" }; } @Override public String[][] getTitles() { return new String[][] { {"订单号","报告价格","报告价格单位","代理价格","报告价格单位","订单日期","到期日期","客户代码","订单公司名称","公司中文名称"}, }; } @Override public String[] getCaptions() { return null; } @Override protected void buildBody(int sheetIndex) { bodyRowStyle=crateBodyCellStyle(); bodyRowStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); CellStyle bodyLeftStyle=crateBodyCellStyle(); CellStyle bodyRightStyle=crateBodyCellStyle(); bodyRightStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); bodyLeftStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); bodyRightStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00")); CellStyle bodyCenterStyle=crateBodyCellStyle(); bodyCenterStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置为文本格式，防止身份证号变成科学计数法 DataFormat format = workbook.createDataFormat(); bodyLeftStyle.setDataFormat(format.getFormat("@")); Sheet sheet = getSheet(sheetIndex); int startIndex = this.getBodyStartIndex(sheetIndex); // SimpleDateFormat dateFm = new SimpleDateFormat("yyyy-MM-dd"); //格式化当前系统日期 for (int i = 0; i < list.size(); i++) { int index = 0; Row row1 = sheet.createRow(i + startIndex ); Cell cell1 = row1.createCell((short) 1); cell1.setCellStyle(bodyLeftStyle); row1.setHeight((short) 300); CreditOrderInfo searchIndex = list.get(i); createStyledCell(row1, index++, searchIndex.get("num")==null? "":searchIndex.get("num").toString(),bodyRowStyle); createStyledCell(row1, index++, searchIndex.get("pprise")==null? "":searchIndex.get("pprise").toString(),bodyLeftStyle); createStyledCell(row1, index++, searchIndex.get("pcurrency")==null? "":searchIndex.get("pcurrency").toString(),bodyLeftStyle); createStyledCell(row1, index++, searchIndex.get("aprice")==null? "":searchIndex.get("aprice").toString(),bodyRowStyle); createStyledCell(row1, index++, searchIndex.get("acurrency")==null? "":searchIndex.get("acurrency").toString(),bodyRowStyle); createStyledCell(row1, index++, searchIndex.get("receiver_date")==null? "":searchIndex.get("receiver_date").toString(),bodyRowStyle); createStyledCell(row1, index++, searchIndex.get("end_date")==null? "":searchIndex.get("end_date").toString(),bodyRowStyle); createStyledCell(row1, index++, searchIndex.get("custom_id")==null? "":searchIndex.get("custom_id").toString(),bodyRowStyle); createStyledCell(row1, index++, searchIndex.get("cname")==null? "":searchIndex.get("cname").toString(),bodyRowStyle); createStyledCell(row1, index++, searchIndex.get("ordername")==null? "":searchIndex.get("ordername").toString(),bodyRowStyle); } sheet.setColumnWidth(0, 7000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 3000); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 4000); sheet.setColumnWidth(6, 4000); sheet.setColumnWidth(7, 4000); sheet.setColumnWidth(8, 4000); sheet.setColumnWidth(9, 4000); sheet.setColumnWidth(10, 4000); } }
4调用实现
List<CreditOrderInfo> infos = OrderManagerService.service.exportSettle(customerId, agentId, time);//需要导出的集合 com.hailian.util.SettleExport export=new com.hailian.util.SettleExport(infos);//导出 try { fileName=new String(fileName.getBytes("GBK"), "ISO-8859-1"); export.doExport(getResponse(), fileName); renderJson("导出成功"); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); renderJson("导出失败"); }
第二种方法使用设计表结构字段注释做表头
@ApiOperation(value = "后台管理-导出安全岛信息", response = JsonResult.class) @RequestMapping(value = "/exportAqd", method = RequestMethod.GET) public JsonResult exportAqd(HttpServletRequest request, HttpServletResponse response) { AqdDto aqdDto = this.getExportParameter(request); List<SysAqd> records = aqdService.getAqd(aqdDto.getPage(), aqdDto.getStart(), aqdDto.getEnd(), aqdDto.getOrgid()).getRecords(); List<SysAqd> sysAqds = this.getSysAqdListXh(records); SysOrg sysOrg = this.orgMapper.selectById(aqdDto.getOrgid()); //导出操作 ExcelUtil.exportExcel(sysAqds, sysOrg.getMc()+"注册码" , sysOrg.getMc()+"注册码", SysAqd.class, sysOrg.getMc()+"注册码.xlsx", response); return ResultUtil.success(null); }
工具类
package ax.tst.common.util; import ax.tst.exception.TstCommonException; import cn.afterturn.easypoi.excel.ExcelExportUtil; import cn.afterturn.easypoi.excel.ExcelImportUtil; import cn.afterturn.easypoi.excel.entity.ExportParams; import cn.afterturn.easypoi.excel.entity.ImportParams; import cn.afterturn.easypoi.excel.entity.enmus.ExcelType; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.Workbook; import org.springframework.web.multipart.MultipartFile; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.IOException; import java.io.OutputStream; import java.net.URLEncoder; import java.util.List; import java.util.Map; import java.util.NoSuchElementException; /** * @Auther：sy * @Date：2018/11/18 20:51 */ public class ExcelUtil { public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) { ExportParams exportParams = new ExportParams(title, sheetName); exportParams.setCreateHeadRows(isCreateHeader); defaultExport(list, pojoClass, fileName, response, exportParams); } public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) { if (fileName.endsWith(".xlsx")) { defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF)); } else { defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName)); } } public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) { defaultExport(list, fileName, response); } private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) { Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list); if (workbook != null); downLoadExcel(fileName, response, workbook); } private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) { try { response.setCharacterEncoding("UTF-8"); response.setHeader("content-Type" , "application/vnd.ms-excel;charset=utf-8"); response.setHeader("Content-Disposition" , "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") ); ServletOutputStream stream = response.getOutputStream(); workbook.write(stream); } catch (IOException e) { throw new TstCommonException(e.getMessage()); } } private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) { Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF); if (workbook != null) ; downLoadExcel(fileName, response, workbook); } public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) { if (StringUtils.isBlank(filePath)) { return null; } ImportParams params = new ImportParams(); params.setTitleRows(titleRows); params.setHeadRows(headerRows); List<T> list = null; try { list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params); } catch (NoSuchElementException e) { throw new TstCommonException("模板不能为空"); } catch (Exception e) { e.printStackTrace(); throw new TstCommonException(e.getMessage()); } return list; } public static <T> List<T> importExcel(MultipartFile file, Integer headerRows, Class<T> pojoClass) { if (file == null) { return null; } ImportParams params = new ImportParams(); params.setHeadRows(headerRows); List<T> list = null; try { list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params); } catch (NoSuchElementException e) { throw new TstCommonException("excel文件不能为空"); } catch (Exception e) { throw new TstCommonException(e.getMessage()); } return list; } }
