1 import java.lang.reflect.Field; 2 import java.lang.reflect.Method; 3 import java.util.Iterator; 4 import java.util.List; 5 6 import org.apache.poi.hssf.usermodel.HSSFCell; 7 import org.apache.poi.hssf.usermodel.HSSFCellStyle; 8 import org.apache.poi.hssf.usermodel.HSSFRow; 9 import org.apache.poi.hssf.usermodel.HSSFSheet; 10 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 11 12 /** 13 * Excel通用工具类 14 * @param sheetName 表格名字 15 * @param headers 表头 16 * @param columns 列(对象属性名)对应表头 17 * @param lists 数据源 18 */ 19 public class ExcelUtil<T> { 20 public HSSFWorkbook export(String sheetName, String[] headers, String[] columns, List<T> lists) throws Exception { 21 HSSFWorkbook wb = new HSSFWorkbook(); 22 HSSFSheet sheet = wb.createSheet(sheetName); 23 sheet.setDefaultColumnWidth(11); 24 HSSFCellStyle style = wb.createCellStyle(); 25 HSSFRow row = sheet.createRow(0); 26 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 27 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 28 for (int i = 0; i < headers.length; i++) { 29 HSSFCell headerCell = row.createCell(i); 30 headerCell.setCellValue(headers[i]); 31 headerCell.setCellStyle(style); 32 } 33 Iterator<T> it = lists.iterator(); 34 int rowIndex = 0; 35 while (it.hasNext()) { 36 rowIndex++; 37 row = sheet.createRow(rowIndex); 38 T t = it.next(); 39 Field[] fields = t.getClass().getDeclaredFields(); 40 for (int i = 0; i < fields.length; i++) { 41 Field field = fields[i]; 42 String fieldName = field.getName(); 43 for (int j = 0; j < columns.length; j++) { 44 if (fieldName.equals(columns[j])) { 45 String getMethodName ="get"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1); 46 Class<? extends Object> cls = t.getClass(); 47 Method getMethod = cls.getMethod(getMethodName, new Class[] {}); 48 Object val = getMethod.invoke(t, new Object[] {}); 49 String textVal = null; 50 if (null != val) { 51 textVal = val.toString(); 52 } else { 53 textVal = null; 54 } 55 HSSFCell hssfCell=row.createCell(j); 56 hssfCell.setCellValue(textVal); 57 hssfCell.setCellStyle(style); 58 } 59 } 60 } 61 } 62 return wb; 63 } 64 }
1 Server层 2 3 /** 4 * 导出数据 5 * @throws Exception 6 */ 7 public HSSFWorkbook outputExcel() throws Exception { 8 List<Distribution> lists = mapper.selectByExample(new DistributionExample()); 9 String sheetName = "债券分销表"; 10 String[] headers = {"指令日期","组合代码","组合名字","业务类型","缴款金额","缴款日期", 11 "O32状态","是否加急","出款状态","主承状态","交收状态"}; //表头 12 String[] columns = {"instructionDate","compositeCode","combinationName","interestType", 13 "paymentAmount","paymentDate","o32Status","urgentStatus","cashFlowStatus", 14 "underwritingStatus","settlementState"}; //bean 属性名 需对应表头 15 ExcelUtil<Distribution> excelUtil = new ExcelUtil<Distribution>(); 16 HSSFWorkbook wb=excelUtil.export(sheetName, headers, columns, lists); 17 //FileOutputStream fos= new FileOutputStream("E://dis.xls"); 18 //wb.write(fos); 19 return wb; 20 } 21
1 @RequestMapping(produces="application/vnd.ms-excel;charset=UTF-8",value="qwangxiao.com /downloadExcel",method = RequestMethod.GET) 2 public void downloadExcel(HttpServletResponse response){ 3 response.addHeader("Content-Disposition","attachment; filename=\"债券分销列表.xls\"" ); 4 HSSFWorkbook wk=null; 5 OutputStream oStream=null; 6 try { 7 oStream = response.getOutputStream(); 8 wk = distributionService.outputExcel(); 9 wk.write(oStream); 10 }catch (Exception e) { 11 e.printStackTrace(); 12 logger.info("导出Excel表格出现异常"+e); 13 }finally { 14 if(oStream!=null){ 15 try { 16 oStream.close(); 17 } catch (IOException e) { 18 e.printStackTrace(); 19 } 20 } 21 } 22 }