一、抽象基类,传入类对象,即可自动生成Excel import java.io.IOException; import java.io.OutputStream; import java.util.List; public abstract class AbstractExcelBeanExporter<T> { protected List<String> keys; List<T> tList; OutputStream outputStream; byte[] buffer; public AbstractExcelBeanExporter(List<String> keys, List<T> tList) { this.keys = keys; this.tList = tList; } abstract void resolveT() throws NoSuchFieldException, IllegalAccessException, IOException; public byte[] getExcelT() throws IllegalAccessException, NoSuchFieldException, IOException { resolveT(); return buffer; } }
二、抽象类实现,用反射生成Excel workbook
package com.taoche.im.utility.excel; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import java.io.*; import java.lang.reflect.Field; import java.util.List; public class HssfExcelBeanExporter<T> extends AbstractExcelBeanExporter<T> { public HssfExcelBeanExporter(List<String> keys, List<T> tList) { super(keys, tList); } @Override void resolveT() throws NoSuchFieldException, IllegalAccessException, IOException { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("sheet1"); HSSFRow row = sheet.createRow(0); for (int i = 0; i < keys.size(); i++) { row.createCell(i).setCellValue(keys.get(i)); } for (int j = 1; j < tList.size(); j++) { Row r = sheet.createRow(j); T t = tList.get(j); for (int i = 0; i < keys.size(); i++) { Field field = t.getClass().getDeclaredField(keys.get(i)); setCellValue(t, field, r.createCell(i)); } } ByteArrayOutputStream baos = new ByteArrayOutputStream(); workbook.write(baos); this.buffer = baos.toByteArray(); } private void setCellValue(T t, Field field, Cell cell) throws IllegalAccessException { field.setAccessible(true); switch (field.getGenericType().getTypeName()) { case "java.lang.String": cell.setCellValue((String) field.get(t)); break; case "java.lang.Boolean": case "boolean": cell.setCellValue((Boolean) field.get(t)); break; case "java.lang.Byte": case "byte": cell.setCellValue((Byte) field.get(t)); break; case "java.lang.Long": case "long": cell.setCellValue((Long) field.get(t)); break; case "java.lang.Short": case "short": cell.setCellValue((Short) field.get(t)); break; case "java.lang.Integer": case "int": cell.setCellValue((Integer) field.get(t)); break; case "java.lang.Double": case "double": cell.setCellValue((Double) field.get(t)); break; case "java.lang.Float": case "floag": cell.setCellValue((Float) field.get(t)); break; case "java.lang.Character": case "char": cell.setCellValue((Character) field.get(t)); break; default: break; } } }
三、service层,传入实体类,返回数组对象
@Override public byte[] exportExcel(XXXDto model) throws Exception { XXXDto imMessageModelsDto = this.XXXDao.select(model); AbstractExcelBeanExporter<XXXModel> excelBeanExporter = new HssfExcelBeanExporter<>(keys, XXXDto.getPageItems()); return excelBeanExporter.getExcelT(); }
四、controller层,设置HttpServletResponse参数,返回值
@GetMapping("excel") public void exportExcel(XXXDto model, HttpServletResponse response) throws Exception { if (model.getCcid() == null) { return; } byte[] buffer = this.XXXService.exportExcel(model); response.setContentType("application/exporter.xls;" + "charset = UTF-8"); String name = "导出信息"; name = new String(name.getBytes(), "ISO-8859-1"); response.setHeader("Content-Disposition", "attachment; filename=" + name + "..xls"); OutputStream toClient = new BufferedOutputStream(response.getOutputStream()); toClient.write(buffer); toClient.flush(); toClient.close(); }