Java 从ORACLE导出数据到EXECL

叁歲伎倆 2022-04-17 01:25 265阅读 0赞

<%@ page language="java" contentType="text/html; charset=utf-8"
 	pageEncoding="utf-8"%>
 <%@ page import="java.io.*,java.sql.*,jxl.*,jxl.write.*" %>
 <%@ page
 	import="java.util.*,java.sql.*,com.abin.comm.util.Common"%>
 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 <html>
 	<head>
 		<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
 		<title>审核管理</title>
 	</head>
 	<body>
 	<%
 		out.println("团购后台审核管理系统");
 		out.println(" ");
 		String username=request.getParameter("login");
 		out.println(" ");
 		if(null==username||"".equals(username)){
 
 			response.sendRedirect("login.jsp");
 		}
 		out.println("欢迎您:"+username);
 		out.println(" ");
 	 %>
 	<a href="index.jsp?login=<%=username %>">返回团购页面</a>
 		<%
 		out.println(" ");
 		out.println("导出数据到Execl(默认在D盘),Execl名称是系统自动生成的！");
 		Connection conn = null;
 		PreparedStatement ps = null;
 		ResultSet rs = null;
 		int i = 0;
 	 int j = 0;
 	 String[] title = { "ID", "时间", "电话号码", "预定内容", "团购名称"};
 	 String sql = "select t.id,t.createtime,t.mobile,t.content,t.tuanname from hd_axiulog t where t.sign=4 order by t.createtime desc";
 	 Calendar cal=Calendar.getInstance();
 	 String path="d:\\阿秀嫂团购.xls";
 	 try {
 
 	 // 建立与数据库的连接
 	 conn = DBClassLocal.getConnection();
 	 ps = conn.prepareStatement(sql);
 	 rs = ps.executeQuery();
 	 //下面五句清除缓存
 	 ResultSetMetaData md = rs.getMetaData();
 		int columnCount = md.getColumnCount();
 		out.clear();
 		out=pageContext.pushBody();
 		response.reset();
 		//上面五句清除缓存
 	 response.setContentType("application/vnd.ms-excel");
 	 // 构建Workbook对象, 只读Workbook对象
 	 // ：创建可写入的Excel工作薄
 	 WritableWorkbook wwb = Workbook.createWorkbook(response.getOutputStream());
 	 WritableSheet ws = wwb.createSheet("sheet1", 0);
 
 	 for (i = 0; i < title.length; i++) {
 
 	 WritableFont wf = new WritableFont(WritableFont.TIMES, 12,
 	 WritableFont.BOLD, false);
 	 WritableCellFormat wcfF = new WritableCellFormat(wf);
 	 Label labelCF = new Label(i, 0, title[i], wcfF);
 	 ws.addCell(labelCF);
 	 }
 
 	 i = 1;
 	 while (rs.next()) {
 
 	 for (j = 1; j <= title.length; j++) {
 
 	 Label labelC = new Label(j - 1, i, rs.getString(j));
 	 ws.addCell(labelC);
 	 }
 	 i++;
 	 }
 	 // 写入Exel工作表
 	 wwb.write();
 	 // 关闭Excel工作薄对象
 	 wwb.close();
 
 	// rs.close();
 	// ps.close();
 	 // conn.close();
 
 	 } catch (Exception e) {
 
 	 System.out.println(e.toString());
 	 }finally{
 
 	 		if(rs!=null){
 
 	 			rs.close();
 	 		}
 	 		if(ps!=null){
 
 	 			ps.close();
 	 		}
 	 		if(conn!=null){
 
 	 			conn.close();
 	 		}
 	 }
 
 
 
 %>
 		 
 		 
 
 	</body>
 </html>