JAVA读取数据库数据导出excel文件

最新推荐文章于 2022-10-09 21:01:43 发布

beibei1022

最新推荐文章于 2022-10-09 21:01:43 发布

阅读量319

点赞数

CC 4.0 BY-SA版权

分类专栏： java servlet excel 文章标签： jxl.jar下载显示效果

本文链接：https://blog.youkuaiyun.com/beibei1022/article/details/84445410

java servlet excel 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一个使用Java实现从数据库查询数据并导出到Excel文件的Servlet示例。通过连接MySQL数据库，根据ID参数查询数据，并利用JXL库将查询结果写入Excel文件。

运行环境描述：
java工程
tomcat服务
mysql数据库
java导出servlet类：

package com.daochu.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.sql.ResultSet;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;

import com.broadway.db.ConnectionManager;
import com.broadway.db.ConnectionPooling;

public class DaoChuServlet extends HttpServlet {
	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	@SuppressWarnings("deprecation")
	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		ConnectionPooling.init(""); // 连接数据库初始化时
		ConnectionManager conn = new ConnectionManager(ConnectionPooling.getConnection());
		ResultSet rs = null;
		try {
			String id = request.getParameter("id") == null ?"" : request.getParameter("id");
			if(!id.equals("")){
				String sql = "select * from lotteryfenxi where id = '"+id+"'";
				rs = conn.executeQuery(sql);

				// 新建Excel文件
				String filePath = request.getRealPath("Test.xls");
				System.out.println(filePath);
				File myFilePath = new File(filePath);
				if (!myFilePath.exists())
					myFilePath.createNewFile();
				FileWriter resultFile = new FileWriter(myFilePath);
				PrintWriter myFile = new PrintWriter(resultFile);
				resultFile.close();

				// 用JXL向新建的文件中添加内容
				OutputStream outf = new FileOutputStream(filePath);
				jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(outf);
				//生成名为“sheettest”的工作表，参数0表示这是第一页
				jxl.write.WritableSheet ws = wwb.createSheet("sheettest", 0);

				int i = 0;
				int j = 0;

				for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) {
					ws.addCell(new Label(k, 0, rs.getMetaData().getColumnName(k + 1)));
				}
				//getMetaData() 获取此 ResultSet 对象的列的编号、类型和属性。
				//getColumnCount()返回此 ResultSet 对象中的列数。
				System.out.println("列数："+rs.getMetaData().getColumnCount());
				while (rs.next()) {
					for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) {
						ws.addCell(new Label(k, j + i + 1, rs.getString(k + 1)));
					}

					i++;
				}
				wwb.write();
				wwb.close();
			}
			rs.close();
			conn.closeRs();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.closeConn();
		}

		response.sendRedirect("Test.xls");
	}
}

3. web.xml配置：

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" 
	xmlns="http://java.sun.com/xml/ns/javaee" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
	http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
        <servlet>
  	   <servlet-name>excel</servlet-name>
  	   <servlet-class>com.daochu.excel.DaoChuServlet</servlet-class>
  	</servlet>
  	<servlet-mapping>
  		<servlet-name>excel</servlet-name>
  		<url-pattern>/excel</url-pattern>
  	</servlet-mapping>
</web-app>

4.访问方式:
http://ip地址:端口号/项目名称/excel?id=5
例：
http://localhost:8989/daochu/excel?id=5

5.连接数据库的方式多种多样，我只用了我自己的连接方式，大家可以采用自己连接数据库的方式，只要能正常访问就ok