将mysql查询集成到excel里_利用mysql将查询结果生成excel并通过servelt传递到前端进行下载...

该方法利用的mysql自带生成文件的功能，而不是通过java各种工具包又生成表头这么复杂。

一、连接数据库

连接数据库用到的是C3P0,这里自定义一个工具类用于连接和关闭连接池。

import java.sql.Connection;

import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Jdbcutils {

private ComboPooledDataSource cpds=null;

public Jdbcutils() {

cpds=new ComboPooledDataSource();

}

public ComboPooledDataSource getDataSource() {

return cpds;

}

public Connection getConnection() throws SQLException {

return cpds.getConnection();

}

public void Close() {

cpds.close();

}

}

C3P0的配置文件c3p0-config.xml一定要配置好，这里就不多说了。

在servlet中使用dbutils.QueryRunner工具类查询

import java.io.File;

import java.io.IOException;

import java.io.InputStream;

import javax.servlet.ServletException;

import javax.servlet.ServletOutputStream;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.dbutils.QueryRunner;

@WebServlet("/Testexcel")

public class Testexcel extends HttpServlet {

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

Jdbcutils jdbcutils=new Jdbcutils();//工具类。用于连接mysql

QueryRunner q=new QueryRunner(jdbcutils.getDataSource());//获得QueryRunner对象可以直接执行CRUD操作

String sql="select * from(select 'id' ,'name' ,'gender' ,'phone' ,'birthday' ,'hobby' ,'info' UNION all select * from stu)wkp into OUTFILE '/Program Files/apache-tomcat-8.5.23/apache-tomcat-8.5.23/webapps/excel/excel/wkp.xls' CHARACTER set gbk";

//重点，这段代码意思是将查询结果生成xls文件，并放到excel文件夹下。

try {

q.execute(sql);//执行sql语句

jdbcutils.Close();//关闭连接池

InputStream in=getServletContext().getResourceAsStream("/excel/wkp.xls");//获得输入流

byte[]bs=new byte[1024*1024*8];

int len=0;

response.setHeader("content-disposition","attachment;filename=wkp.xls");//告诉浏览器，以下载的形式下载

ServletOutputStream out= response.getOutputStream();//获得输出流

while((len=in.read(bs))!=-1) {

out.write(bs, 0, len);

}

out.close();

in.close();

new File(getServletContext().getRealPath("/excel/wkp.xls")).delete();//把文件删除

}

catch (Exception e) {

}

}

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

doGet(request, response);

}

}

重点是sql语句:"select * from(select 'id' ,'name' ,'gender' ,'phone' ,'birthday' ,'hobby' ,'info' UNION all select * from stu)wkp into OUTFILE '/Program Files/apache-tomcat-8.5.23/apache-tomcat-8.5.23/webapps/excel/excel/wkp.xls' CHARACTER set gbk"

select 'id' ,'name' ,'gender' ,'phone' ,'birthday' ,'hobby' ,'info' UNION all 这句是给excel加上表头的，mysql生成文件默认是没有表头的，这里就把表头也查询出来作为第一行，和后面的查询结果拼凑成一张表。在()外一定要加别名，虽然没什么用，但语法如此，否则会报错。有人说先转txt，再转xls，我觉得麻烦。

接下来就是into outfile

into outfile '文件路径加文件名'。如果要生成excel这种文件，有三种格式:xls、xlsx、和csv。亲测选择xls，不知道为什么excel2016不能打开xlsx，csv要加各种转义符和都逗号，麻烦。注意，如果是中文乱码，就将character设置成gbk。

两个容易出错的地方：

1.生成的文件路径最好和mysql安装位置(我的在H盘)在一个盘，我试过在桌面(C盘)生成excel，显示没有权限。

2.文件路径不要放在eclipse的工作空间项目下，要写道tomcat里，因为eclipse一启动，就会将所有资源文件打包放到tomcat里，你在执行的时候动态的在eclipse生成了excel，tomcat访问不到，一定要放到tomcat里。

最后就这么多，第一次写，也是才入门，哈哈。