前端页面下载服务端DB数据以EXCEL格式下载到本地

航帆远洋

2013-06-19

简单描述过程：访问jsp页面，调用后台url(.do);调用相应*.java,再调用DB，返回数据；最终下载到本地，存为.xls（EXCEL）文件。

一、JSP页面

<%@ page language="java" contentType="text/html; charset=utf-8" %>
<html>
<body onload="init();">
	<div>		
<form name="form" action="download.do" method="get">
		</form>
	</div>

<script type="text/javascript">

	/**
	 * function：页面初始化加载
	 */
	function init(){
		document.form.submit();
	}

</script>
</body>
</html>

二、java代码（*.java）先配一个.do和testDownLoad.java文件的映射

<bean name="/downLoad/download.do" class="com.paic.cm.ClientManage.web.controller.downLoad.testDownLoad">
   </bean>

testDownLoadjava代码：

......//此为调用DB获取数据代码，省略
String fileName = ""+System.currentTimeMillis()+".xls";//定义文件名称
		//设置response
		response.setContentType("application/vnd.ms-excel");//设置文件格式
		response.setHeader("Content-Disposition","attachment;filename="+fileName);
		OutputStream os = response.getOutputStream();
List<HomePageInfosDTO> retList = (List<HomePageInfosDTO>)retMap.get("APP_INFO");//DB返回的数据     
WritableWorkbook wwb=null;
WritableSheet ws=null;
		        try {
		            wwb = Workbook.createWorkbook(os);
		            ws=wwb.createSheet("sheet_01",0);
		            ws.getSettings().setDefaultColumnWidth(15);
		            //创建表头
		            logger.info("开始创建表头~~~~~~~~~~");
		            WritableFont wfc = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
		            WritableCellFormat wcfFC = new WritableCellFormat(wfc);
		            wcfFC.setBackground(jxl.format.Colour.GREY_25_PERCENT);//设置表头背景颜色
		            Label appIDHeadLabel 		= new Label(0,0,"应用名称",wcfFC);
		            Label AppConsoleHeadLabel 	= new Label(1,0,"应用平台",wcfFC);
		            Label appNameHeadLabel 		= new Label(2,0,"最新版本",wcfFC);
		            Label cerNameHeadLabel 		= new Label(3,0,"应用状态",wcfFC);
		            Label cerPswHeadLabel 		= new Label(4,0,"今日新增用户",wcfFC);
		            Label summaryUserHeadLabel 	= new Label(5,0,"累计用户",wcfFC);
		            ws.setColumnView(0, 30);
		            ws.addCell(appIDHeadLabel);
		            ws.addCell(AppConsoleHeadLabel);
		            ws.addCell(appNameHeadLabel);
		            ws.addCell(cerNameHeadLabel);
		            ws.addCell(cerPswHeadLabel);
		            ws.addCell(summaryUserHeadLabel);
		            logger.info("创建表头完成~~~~~~~~~~");
		            Label appIdLabel = null;
		            Label appConsoleLabel = null;
		            Label appNameLabel = null;
		            Label cerNameLabel = null;
		            Label cerPswLabel = null;
		            Label summaryUserLabel = null;
		            logger.info("开始写表体~~~~~~~~~~");
		            for(int i=1;i<retList.size();i++) {
		            	HomePageInfosDTO dto = (HomePageInfosDTO)retList.get(i);
		                String appId = dto.getAppName(); 			//应用名称
		                String appConsole = dto.getAppConsole(); 	//应用平台
		                String appName = dto.getNewestVersion();	//最新版本
		                String cerName = dto.getAppState();			//应用状态
		                String cerPsw = dto.getTodayNewUser();		//今日新增用户
		                String summaryUser = dto.getTotalUser();    //累计用户
		                appIdLabel = new Label(0,i,appId);
		                appConsoleLabel = new Label(1,i,appConsole);
		                appNameLabel = new Label(2,i,appName);
		                cerNameLabel = new Label(3,i,cerName);
		                cerPswLabel = new Label(4,i,cerPsw);
		                summaryUserLabel = new Label(5,i,summaryUser);
		                ws.addCell(appIdLabel);
		                ws.addCell(appConsoleLabel);
		                ws.addCell(appNameLabel);
		                ws.addCell(cerNameLabel);
		                ws.addCell(cerPswLabel);
		                ws.addCell(summaryUserLabel);
		            }
		            logger.info("表体写入完成~~~~~~~~~~");
		        } catch (Exception e) {
		            logger.error("输出Excel失败:"+e.getMessage(),e);
		        } finally {
		            try {
		                wwb.write();
		                wwb.close();
		            } catch (WriteException e) {
		            	logger.error("关闭WritableWorkbook出错:"+e.getMessage(),e);
		            } catch (IOException e) {
		            	logger.error("关闭WritableWorkbook出错:"+e.getMessage(),e);
		            }
		        }
		        logger.info("下载成功~~~~~");

大数据前端电子表格 jsp html文件