domain层：
@SuppressWarnings("serial")public class ExportRecords implements java.io.Serializable{ private String excelfile = ""; private String fieldnames = "";public String getFieldnames() {return fieldnames;}public void setFieldnames(String fieldnames) {this.fieldnames = fieldnames;}public String getExcelfile() {return excelfile;}public void setExcelfile(String excelfile) {this.excelfile = excelfile;}private List<String> colnames=new Vector<String>();private List<String> records=new Vector<String>();public List<String> getColnames() {return colnames;}public void setColnames(List<String> colnames) {this.colnames = colnames;}public List<String> getRecords() {return records;}public void setRecords(List<String> records) {this.records = records;}}
webservice层
@Component@Transactionalpublic class ExcelExport {private static Logger logger = Logger.getLogger(ExcelExport.class);private static String[] exportFieldsName = null;/*导出的EXCEL标题名称*/private static int MAX_SHEET_ROWS = 10000; /*每个sheet最多的记录数*/private static List<ExportRecords> results = new Vector<ExportRecords>();private static String filename = "";public ExcelExport(){}public static String getFileName(SimpleJdbcTemplate jdbcTemplate,String sql,String prename) throws Exception{if(StringHelper.isNotNull(prename)){filename = prename + DateHelper.getNowTime("yyyyMMDDHHmmSS");}else{filename = BusiHelper.getSn(jdbcTemplate, "B", "E");}query( jdbcTemplate, sql);return execute();}public static void query(SimpleJdbcTemplate jdbcTemplate,String sql){logger.info("查询==========================="+sql);ParameterizedRowMapper<ExportRecords> map = new ParameterizedRowMapper<ExportRecords>(){@Overridepublic ExportRecords mapRow(ResultSet rs, int rowNum)throws SQLException {ExportRecords mapdata = new ExportRecords();ResultSetMetaData metaData = rs.getMetaData(); int colum = metaData.getColumnCount(); String columNames = "";List<String> values = new Vector<String>();String columName = ""; for (int i = 1; i <= colum; i++) { //获取列名 columName = metaData.getColumnLabel(i); columNames += columName+",";values.add(rs.getString(columName));} columNames = columNames.substring(0,columNames.length()-1);mapdata.setFieldnames(columNames);mapdata.setRecords(values);return mapdata;}};results= jdbcTemplate.query(sql, map);if(results.size()>0){exportFieldsName = results.get(0).getFieldnames().split(",");}}public static String execute() throws Exception{String exportName = filename+".xls";File dir = new File(Constans.WEBAPP_HOME+Constans.DOWNLOAD_DIR);if(!dir.isDirectory()){dir.mkdirs();}File f = new File(dir+File.separator+exportName);WritableWorkbook wwb = null;try{wwb = Workbook.createWorkbook(f);int cols = 0;if(exportFieldsName!=null){cols = exportFieldsName.length;}Label label = null;WritableSheet sheet = null;int sheetnum = results.size()/MAX_SHEET_ROWS+1;sheet = wwb.createSheet("导出数据",0);if(results.isEmpty()){label = new Label(0,1,"无数据");sheet.addCell(label);}for(int i=0;i<sheetnum;i++){sheet = wwb.createSheet("第"+(i+1)+"页",i);for(int j = 0; j < cols; j++){label = new Label(j,0,exportFieldsName[j]);//第一行录入列名sheet.addCell(label);label = null;}int curRow = 1;//第二行开始录入记录for(int k=MAX_SHEET_ROWS*i;k<MAX_SHEET_ROWS*(i+1)&&k<results.size();k++){List<String> values = results.get(k).getRecords();for(int index = 0; index < cols; index++){label = new Label(index,curRow,values.get(index));sheet.addCell(label);label = null;}curRow++;}}wwb.write();wwb.close();wwb = null;return filename;}catch(Exception e){logger.error(e.toString());if(wwb != null){wwb.write();wwb.close();wwb = null;}throw new BusiException("保存Excel失败:"+e.toString());}}}
