java mysql 导出excel文件怎么打开文件_JAVA读取数据库数据导出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配置：

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">

index.jsp

excel

com.daochu.excel.DaoChuServlet

excel

/excel

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