Java 导出Excel范例

SpringMvc Contorller

@SuppressWarnings("resource")@RequestMapping("/exportDailyExcel")public void exportDailyExcel(HttpServletRequest request,HttpServletResponse response){HSSFWorkbook wb = new HSSFWorkbook();          HSSFSheet sheet1 = wb.createSheet("日常费用主表信息");          processDailyTableHead(sheet1);                  Map<String,String> params = this.processDaily(request);    List<DailyExpense> lst = this.expenseService.findDailyExpenseByCondition(params,-1,-1);        this.expenseService.processTableData(sheet1, lst, getDailyExcelModel());        String fileName = "dailyExpense"+ DateTimeUtils.getNowTimeStr(null) +".xls";          response.setContentType("application/vnd.ms-excel");          response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");          try {              wb.write(response.getOutputStream());          } catch (IOException e) {              e.printStackTrace();          }           }

处理表头的方法 processDailyTableHead

 public void processDailyTableHead(HSSFSheet sheet){          List<ExcelModel> list = getDailyExcelModel();          HSSFRow row = sheet.createRow(0);          for(int i=0; i<list.size(); i++){              HSSFCell cell = row.createCell(i);              cell.setCellValue(list.get(i).getColumName());          }   }  

处理表格数据的方法processTableData，此处采用的是泛型，较为通用

public <T> void processTableData(HSSFSheet sheet, List<T> rList,List<ExcelModel> list) {for (int i = 0; i < rList.size(); i++) {HSSFRow row = sheet.createRow(i + 1);T t = rList.get(i);for (int m = 0; m < list.size(); m++) {try {HSSFCell cell = row.createCell(m);String fieldValueStr = "";String fieldName = list.get(m).getFieldName();Object fieldValue = null;if(fieldName.contains(".")){String str = fieldName.substring(0,fieldName.indexOf('.'));fieldValue = PropertyUtils.getProperty(t, str);if(fieldValue == null){fieldValueStr="";cell.setCellValue(fieldValueStr);continue;}}fieldValue = PropertyUtils.getProperty(t, list.get(m).getFieldName());if (fieldValue != null) {fieldValueStr = fieldValue.toString();if(fieldValueStr.equals("true")){fieldValueStr="是";}else if(fieldValueStr.equals("false")){fieldValueStr="否";}}cell.setCellValue(fieldValueStr);} catch (IllegalAccessException e) {e.printStackTrace();} catch (InvocationTargetException e) {e.printStackTrace();} catch (NoSuchMethodException e) {e.printStackTrace();}}}}

Excel 单元格的抽象类  ExcelModel

public class ExcelModel{private int index=0;private String columName;private String fieldName;public ExcelModel(){}public ExcelModel(String columName,String fieldName){this.setIndex(index);this.setColumName(columName);this.setFieldName(fieldName);}public ExcelModel(int index,String columName,String fieldName){this.setIndex(index);this.setColumName(columName);this.setFieldName(fieldName);}public int getIndex() {return index;}public void setIndex(int index) {this.index = index;}public String getColumName() {return columName;}public void setColumName(String columName) {this.columName = columName;}public String getFieldName() {return fieldName;}public void setFieldName(String fieldName) {this.fieldName = fieldName;}}

拿到单元格头部的方法getDailyExcelModel

 public List<ExcelModel> getDailyExcelModel(){          List<ExcelModel> list = new ArrayList<ExcelModel>();          list.add(new ExcelModel(list.size(),"单据编号","expenseCode"));          list.add(new ExcelModel(list.size(),"申请人","applicantName"));          list.add(new ExcelModel(list.size(),"费用承担部门","requestByDeparmentName"));                  list.add(new ExcelModel(list.size(),"专项","specialProject.name"));          list.add(new ExcelModel(list.size(),"申请日期","applicationDate"));          list.add(new ExcelModel(list.size(),"支付方式","paymentTypeName"));                 list.add(new ExcelModel(list.size(),"报销金额","requestAmount"));          list.add(new ExcelModel(list.size(),"是否冲销款","payedByLoanOption"));          list.add(new ExcelModel(list.size(),"冲销款金额","payedByLoanAmount"));                 return list;   }

采用的POI版本

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