java导出excel表格_java 输出电子表格-优快云博客

本文链接：https://blog.youkuaiyun.com/wangyi201212/article/details/50540333

在生产过程中经常涉及到报表的导出，一般是将数据库数据导出到excel表格中，下面讲解两种办法导出：

1.导出到某个固定的目录下（例如：E:/)，这种比较死板；

2.导出时，弹出对话框，可以选择保存的目录，这种比较灵活；

下面具体讲下1和2如何实现；

1.导出到具体的目录下：

jsp页面上采用ajax请求：

       <span style="color:#000000;"> $.ajax({
 		type: "post",
		url: "exportExcel.do",
		dataType: "json",
 		success: function(data){
		    alert("E:/teminal_state.xsl");
	        }
	    });  </span>

       <span style="color:#3333FF;"> <span style="font-size:14px;">java代码处理</span></span>

         HSSFWorkbook wb = new HSSFWorkbook();// 第一步，创建一个webbook，对应一个Excel文件    
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet("终端在线表");  
        sheet.setColumnWidth(1, 20 * 256);  
        sheet.setColumnWidth(2, 20 * 256);
        sheet.setColumnWidth(3, 20 * 256);  
        sheet.setColumnWidth(6, 20 * 256);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        HSSFRow row = sheet.createRow((int) 0);  
        // 第四步，创建单元格，并设置值表头 设置表头居中  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
        HSSFCell cell = row.createCell((short) 0);  
        cell.setCellValue("id");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 1);  
        cell.setCellValue("SN账号");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 2); 
        cell.setCellValue("服务器ip");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 3);  
        cell.setCellValue("终端类型");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 4);  
        cell.setCellValue("播流状态");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 5);  
        cell.setCellValue("心跳状态");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 6);  
        cell.setCellValue("登录时间");  
        cell.setCellStyle(style); 
        cell = row.createCell((short) 7);
        cell.setCellValue("机顶盒状态");  
        cell.setCellStyle(style); 
        cell = row.createCell((short) 8);  
        cell.setCellValue("WIFI MAC");  
        cell.setCellStyle(style);  
        
        for (int i = 0; i < list.size(); i++){
            row = sheet.createRow((int) i + 1);  
            TerminalStateBean tsb = (TerminalStateBean) list.get(i);  
            // 第四步，创建单元格，并设置值  
            row.createCell((short) 0).setCellValue((double) tsb.getId());  
            row.createCell((short) 1).setCellValue(tsb.getUserid());  
            row.createCell((short) 2).setCellValue(tsb.getSlaveip());
            String type = "";
            if(tsb.getTerminaltype() == 6){
            	type = "AndroidStb";
			}else if(tsb.getTerminaltype() == 3){
				type =  "iPad";
			}else{
				type =  "PC";
			}
            row.createCell((short) 3).setCellValue(type);
            String status = "";
            if(tsb.getStreamstatus() == 0){
            	status = "空闲";
			}else{
				status = "在播";
			}
            row.createCell((short) 4).setCellValue(status);
            String heart = "";
            if(tsb.getHeartstatus() ==0){
            	heart = "中断";
			}else{
				heart = "正常";
			}
            row.createCell((short) 5).setCellValue(heart);
            SimpleDateFormat time=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String login = time.format(tsb.getLogintime()); 
            row.createCell((short) 6).setCellValue(login);
            String stb = "";
            if(tsb.getStbstate() == 0){
            	stb = "正常";
			}else if(tsb.getStbstate() == 1){
				stb = "锁定";
			}else{
				stb = "重启";
			}
            row.createCell((short) 7).setCellValue(stb);
            row.createCell((short) 8).setCellValue(tsb.getApptype());
           <span style="color:#3333FF;"> try  
            {  
                FileOutputStream fout = new FileOutputStream("E:/termianlstate.xls");  
                wb.write(fout);  
                fout.close();  
                wb.close();
            }  
            catch (Exception e)  
            {  
                e.printStackTrace();  
            }</span>
        }

2.导出时，弹出对话框，可以选择保存的目录

jsp页面上采用ajax请求：

        <span style="color:#000000;">function exportExcel(){
              var url = "exportExcel.do";
              window.open(url);//采用browserWindow.js，定位到弹出保存窗口
          }</span>

java代码：

<span style="color:#000000;">public void exportDataToExcel(List<TerminalStateBean> list, HttpServletResponse response){
        <span style="color:#3333FF;">OutputStream os = null;
	try{
		os = response.getOutputStream();// 取得输出流
 		response.reset();// 清空输出流
		response.setHeader("Content-disposition","attachment; filename="+ "terminal" + ".xls");// 设定输出文件头
		response.setContentType("application/msexcel");// 定义输出类型
	}catch(Exception e){
		e.printStackTrace();
	}</span>
	// 第一步，创建一个webbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook();  
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet("终端在线表");  
        sheet.setColumnWidth(1, 20 * 256);  
        sheet.setColumnWidth(2, 20 * 256);
        sheet.setColumnWidth(3, 20 * 256);  
        sheet.setColumnWidth(6, 20 * 256);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        HSSFRow row = sheet.createRow((int) 0);  
        // 第四步，创建单元格，并设置值表头 设置表头居中  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
        HSSFCell cell = row.createCell((short) 0);  
        cell.setCellValue("id");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 1);  
        cell.setCellValue("SN账号");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 2); 
        cell.setCellValue("服务器ip");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 3);  
        cell.setCellValue("终端类型");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 4);  
        cell.setCellValue("播流状态");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 5);  
        cell.setCellValue("心跳状态");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 6);  
        cell.setCellValue("登录时间");  
        cell.setCellStyle(style); 
        cell = row.createCell((short) 7);
        cell.setCellValue("机顶盒状态");  
        cell.setCellStyle(style); 
        cell = row.createCell((short) 8);  
        cell.setCellValue("WIFI MAC");  
        cell.setCellStyle(style);  
        
        for (int i = 0; i < list.size(); i++){
            row = sheet.createRow((int) i + 1);  
            TerminalStateBean tsb = (TerminalStateBean) list.get(i);  
            // 第四步，创建单元格，并设置值  
            row.createCell((short) 0).setCellValue((double) tsb.getId());  
            row.createCell((short) 1).setCellValue(tsb.getUserid());  
            row.createCell((short) 2).setCellValue(tsb.getSlaveip());
            String type = "";
            if(tsb.getTerminaltype() == 6){
            	type = "AndroidStb";
 	    }else if(tsb.getTerminaltype() == 3){
 		type =  "iPad";
 	    }else{
 		type =  "PC";
 	    }
            row.createCell((short) 3).setCellValue(type);
            String status = "";
            if(tsb.getStreamstatus() == 0){
            	status = "空闲";
	    }else{
		status = "在播";
	    }
            row.createCell((short) 4).setCellValue(status);
            String heart = "";
            if(tsb.getHeartstatus() ==0){
            	heart = "中断";
	    }else{
		heart = "正常";
	    }
            row.createCell((short) 5).setCellValue(heart);
            SimpleDateFormat time=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String login = time.format(tsb.getLogintime()); 
            row.createCell((short) 6).setCellValue(login);
            String stb = "";
            if(tsb.getStbstate() == 0){
            	stb = "正常";
	    }else if(tsb.getStbstate() == 1){
		stb = "锁定";
	   }else{
		stb = "重启";
			}
          row.createCell((short) 7).setCellValue(stb);
          row.createCell((short) 8).setCellValue(tsb.getApptype());
        }  
        <span style="color:#3333FF;">try {//放在循环之外
		wb.write(os);
		os.flush();
		os.close();
	} catch (Exception e) {
		e.printStackTrace();
	}</span>
}</span>