excel下载

项目里用的excel下载功能.记下以后有用.

主要是一个xlsCreator

public class MyXlsCreator {
	@SuppressWarnings("unused")
	private static Logger logger = Logger.getLogger(MyXlsCreator.class);
	private SimpleDateFormat sdf=new SimpleDateFormat("yyyy/MM/dd");
	private HSSFWorkbook wb;
	private CellStyle alignCenter;
	private CellStyle alignRight;	
	private CellStyle dateStyle;
	private CellStyle topBorder;	
	private CellStyle totalTopBorder;
	private CellStyle money;
	private CellStyle fontStyle;
	private CellStyle bigTitleStyle;
	private MySheet[] mySheets;
	
	
	public MyXlsCreator(MySheet ...mySheets){
		 
		this.wb= new HSSFWorkbook();
		
		this.mySheets=mySheets;
		HSSFFont font = wb.createFont();
		font.setFontName("宋体");
	    font.setFontHeightInPoints((short) 12);// 设置字体大小
		alignCenter=wb.createCellStyle();//居中对齐的样式
		alignCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中对齐的样式
		alignCenter.setFont(font);
		alignRight=wb.createCellStyle();
		alignRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		alignRight.setFont(font);
		dateStyle=wb.createCellStyle();
		HSSFDataFormat format = wb.createDataFormat();  
		dateStyle.setDataFormat(format.getFormat("yyyy/MM/dd"));
		dateStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		dateStyle.setFont(font);
		topBorder=wb.createCellStyle();
		topBorder.setBorderTop(HSSFCellStyle.BORDER_THICK);
		topBorder.setFont(font);
		totalTopBorder=wb.createCellStyle();
		totalTopBorder.setBorderTop(HSSFCellStyle.BORDER_THICK);
		totalTopBorder.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		totalTopBorder.setDataFormat(format.getFormat("#,##0.00"));
		totalTopBorder.setFont(font);
		money=wb.createCellStyle();
		money.setDataFormat(format.getFormat("#,##0.00"));
		money.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		money.setFont(font);
		fontStyle=wb.createCellStyle();
		fontStyle.setFont(font);
		
		HSSFFont bigTitleFont = wb.createFont();
		bigTitleFont.setFontName("宋体");
		bigTitleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		bigTitleFont.setFontHeightInPoints((short)22);
		
		
		bigTitleStyle=wb.createCellStyle();
		bigTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		bigTitleStyle.setFont(bigTitleFont);	
		init();
	}
	@SuppressWarnings("deprecation")
	private void init(){
		for(MySheet each:mySheets){
			List<MyTab> tabs=each.getTabs();
			HSSFSheet sheet=wb.createSheet(each.getSheetName());
			sheet.setDefaultColumnWidth(15);
			int col=6;//默认列数
			try {//找出第一个表的列数
				col=each.getTabs().get(0).getHeads().length;				
			} catch (Exception e) {
			}			
			
			int totalrow=0;
			if(each.getBigTitle()!=null){
				HSSFRow row=sheet.createRow(totalrow);
				sheet.addMergedRegion(new Region(totalrow,(short)0,totalrow,(short)(col-1))); 
				row.setHeightInPoints(26);
				Cell cell=row.createCell(0);	
				cell.setCellValue(each.getBigTitle());
				cell.setCellStyle(bigTitleStyle);				
				totalrow++;
			}
			
			
			for(MyTab eachTab:tabs){
				
				//写标题
				if(eachTab.getTitleList()!=null){
					for(int ti=0;ti<eachTab.getTitleList().length;ti++){
						HSSFRow row=sheet.createRow(totalrow);						
						sheet.addMergedRegion(new Region(totalrow,(short)0,totalrow,(short)(col-1))); 
						totalrow++;
						Cell cell=row.createCell(0);
						cell.setCellValue(eachTab.getTitleList()[ti]);
						cell.setCellStyle(fontStyle);
					}
				}
				if(eachTab.getHeads()!=null){
					//写表头
					HSSFRow row=sheet.createRow(totalrow);
					totalrow++;
					
					for(int hi=0;hi<eachTab.getHeads().length;hi++){
						HSSFCell cell=	row.createCell(hi);
						cell.setCellStyle(alignCenter);
						cell.setCellValue(eachTab.getHeads()[hi]);
					}
				}
				if(eachTab.getItems()!=null){
					//写数据
					for(Object[] rowData:eachTab.getItems()){
						
						HSSFRow row1=sheet.createRow(totalrow);
						totalrow++;
						for(int di=0;di<rowData.length;di++){
							
							Object data=rowData[di];
							if(data==null){
								continue;
							}
							Cell cell=row1.createCell(di);						
							if(data instanceof Integer){
								cell.setCellValue((Integer)data);
								cell.setCellStyle(alignCenter);
							}
							if(data instanceof Double){
								cell.setCellValue((Double)data);
								cell.setCellStyle(money);
							}
							if(data instanceof String){
								cell.setCellValue((String)data);
								cell.setCellStyle(alignCenter);
								
							}
							if(data instanceof Date){
								
									Date date = null;
									try {
										date = sdf.parse(sdf.format((Date)data));
									} catch (ParseException e) {
										// TODO Auto-generated catch block
										e.printStackTrace();
									}
							
								cell.setCellValue(date);
								cell.setCellStyle(dateStyle);
							}
							
						}					
						
					}
				}
						
				
			}	
			
		}
		
	}
	public void write(String path) throws IOException{
		File outPutFile=new File(path);
		if(!outPutFile.exists()){
			outPutFile.createNewFile();
		}
		
		FileOutputStream os = new FileOutputStream(outPutFile);  
	    wb.write(os);  
	    os.close(); 
	}
	
	public void write(OutputStream outputStream) throws IOException{
		wb.write(outputStream);
		outputStream.close();
	}
	
}

  具体实现的时候用于创建sheet,在xlsCreator构造函数中调用,初始化表

public MySheet getPersonalInfo(){
	MySheet mySheet= new MySheet();
	mySheet.setSheetName("个人信息");
	MyTab tab=new MyTab();
	tab.setTitleList(new String[]{我的个人信息});
	tab.setHeads(new String[]{"姓名","手机号码","身份证号码","年龄","性别"});
	Object[][] datas=new Object[xls.size()][5];
	if(xls.size()>0){
	    for(int i=0;i<xls.size();i++){
 	        datas[i][0]="吴星";
		datas[i][1]="135****2682";
		datas[i][2]="360***3412";
		datas[i][3]="18";
		datas[i][4]="男";

             }
	}
	tab.setItems(datas);
        mySheet.addTab(tab);
        return mySheet;			
}


public class MySheet {
	@SuppressWarnings("unused")
	private static Logger logger = Logger.getLogger(MySheet.class);
	private String sheetName;
	private String bigTitle;
	private List<MyTab> tabs;	
	public MySheet(){
		tabs=new ArrayList<MyTab>();
	}
	public String getSheetName() {
		return sheetName;
	}
	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}	
	public String getBigTitle() {
		return bigTitle;
	}
	public void setBigTitle(String bigTitle) {
		this.bigTitle = bigTitle;
	}
	public List<MyTab> getTabs() {
		return tabs;
	}
	public void setTabs(List<MyTab> tabs) {
		this.tabs = tabs;
	}
	public void addTab(MyTab tab){
		tabs.add(tab);
	}
	
	public void addTabs(Collection<MyTab> tabs){
		this.tabs.addAll(tabs);		
	}
	
	
	
}

demo 如下

public void DLInvestDetail(HttpServletResponse response){
	MyXlsCreator creator=new MyXlsCreator(xlsServiceImpl.getPersonalInfo());	
	String fileName = sdf.format(new Date())+"个人信息详情.xls";
	try {
		fileName = URLEncoder.encode(fileName, "utf-8");
	} catch (UnsupportedEncodingException e1) {
		logger.error(e1.getMessage(),e1.fillInStackTrace());
	}
	response.setContentType("application/x-download;charset=utf-8");  
	response.addHeader("Content-Disposition","attachment;filename=" + fileName);   
	OutputStream outputStream;
	try {
		outputStream = response.getOutputStream();
		creator.write(outputStream);
	} catch (IOException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}	
}