excel导出

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
@Transactional
public 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>(){
			@Override
			public 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());
		}
	}
	
}