package com.wp.poi;

import java.sql.Connection;

import java.sql.DriverManager;

//连接数据库类

public class DbUtil {

    private String dbUrl = "jdbc:mysql://localhost:3306/db_easyui";

    private String dbUserName = "root";

    private String dbPassword = "root";

    private String jdbcName = "com.mysql.jdbc.Driver";

    public Connection getCon() throws Exception {

        Class.forName(jdbcName);

        Connection con = DriverManager.getConnection(dbUrl, dbUserName,

                dbPassword);

        return con;

    }

    public void closeCon(Connection con) throws Exception {

        if (con != null) {

            con.close();

        }

    }

}

package com.wp.poi;

import java.io.IOException;

import java.io.OutputStream;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

/**

 * 普通导出数据

 *

 * @author admin

 *

 */

public class POIout extends HttpServlet {

    public POIout() {

        super();

    }

    public void init() throws ServletException {

    }

    public void destroy() {

        super.destroy();

    }

    public void doGet(HttpServletRequest request, HttpServletResponse response)

            throws ServletException, IOException {

        doPost(request, response);

    }

    public void doPost(HttpServletRequest request, HttpServletResponse response)

            throws ServletException, IOException {

        response.setContentType("text/html;charset=utf-8");

        DbUtil dbUtil = new DbUtil();

        Connection conn = null;

        try {

            conn = dbUtil.getCon();// 获得数据库连接

            Workbook wb = new HSSFWorkbook();

            String headers[] = { "编号", "姓名", "电话", "Email", "QQ" };// 标题

            ResultSet rs = userList(conn);// 得到结果集

            fillExcelData(rs, wb, headers);

            export(response, wb, "导出数据.xls");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            try {

                dbUtil.closeCon(conn);

            } catch (Exception e) {

                e.printStackTrace();

            }

        }

    }

    /**

     * 查询数据库

     *

     * @param con

     * @return ResultSet 返回结果集

     * @throws Exception

     */

    public ResultSet userList(Connection con) throws Exception {

        StringBuffer sb = new StringBuffer("select * from t_user");

        PreparedStatement pstmt = con.prepareStatement(sb.toString());

        return pstmt.executeQuery();

    }

    /**

     * 导出用户

     *

     * @throws Exception

     */

    public void fillExcelData(ResultSet rs, Workbook wb, String[] headers)

            throws Exception {

        int rowIndex = 0; // 第一行

        Sheet sheet = wb.createSheet(); // 创建sheet页

        Row row = sheet.createRow(rowIndex++);

        // 创建标题

        for (int i = 0; i < headers.length; i++) {

            row.createCell(i).setCellValue(headers[i]);

        }

        // 导出数据库中的数据

        while (rs.next()) {

            row = sheet.createRow(rowIndex++);

            for (int i = 0; i < headers.length; i++) {

                row.createCell(i).setCellValue(rs.getObject(i + 1).toString());

                //rs.getObject(i + 1)得到一个对象，即数据库中一行的结果，每一列就是属性凑成一行变成对象。因为id是从1开始，所以要+1。

            }

        }

    }

    /**

     * 把数据放入到.xls文件中并下载到本地

     *

     * @param response

     * @param wb

     * @param fileName

     * @throws Exception

     */

    public void export(HttpServletResponse response, Workbook wb,

            String fileName) throws Exception {

        response.setHeader("Content-Disposition", "attachment;filename="

                + new String(fileName.getBytes("utf-8"), "iso8859-1"));// 设置头信息

        response.setContentType("application/ynd.ms-excel;charset=UTF-8");

        OutputStream out = response.getOutputStream();

        wb.write(out);// 进行输出，下载到本地

        out.flush();

        out.close();

    }

}

package com.wp.poi;

import java.io.IOException;

import java.io.InputStream;

import java.io.OutputStream;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

/**

 * 利用模板导出数据

 *

 * @author admin

 *

 */

public class POIoutTemplate extends HttpServlet {

    public POIoutTemplate() {

        super();

    }

    public void init() throws ServletException {

    }

    public void destroy() {

        super.destroy();

    }

    public void doGet(HttpServletRequest request, HttpServletResponse response)

            throws ServletException, IOException {

        doPost(request, response);

    }

    public void doPost(HttpServletRequest request, HttpServletResponse response)

            throws ServletException, IOException {

        response.setContentType("text/html;charset=utf-8");

        DbUtil dbUtil = new DbUtil();

        Connection conn = null;

        try {

            conn = dbUtil.getCon();

            Workbook wb = fillExcelDataWithTemplate(userList(conn),

                    "Template.xls");

            export(response, wb, "利用模板导出的数据.xls");

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            try {

                dbUtil.closeCon(conn);

            } catch (Exception e) {

                e.printStackTrace();

            }

        }

    }

    /**

     * 查询数据库

     *

     * @param con

     * @return

     * @throws Exception

     */

    public ResultSet userList(Connection con) throws Exception {

        StringBuffer sb = new StringBuffer("select * from t_user");

        PreparedStatement pstmt = con.prepareStatement(sb.toString());

        return pstmt.executeQuery();

    }

    /**

     * 根据模板导出用户

     *

     * @throws Exception

     */

    public Workbook fillExcelDataWithTemplate(ResultSet rs,

            String templateFileName) throws Exception {

        InputStream in = POIoutTemplate.class

                .getResourceAsStream("/com/wp/poi/" + templateFileName);

        POIFSFileSystem fs = new POIFSFileSystem(in);// 解析Excel文件

        Workbook wb = new HSSFWorkbook(fs);// 以解析的excel文件格式进行创建

        Sheet sheet = wb.getSheetAt(0);

        // 获取列数

        int cellNums = sheet.getRow(0).getLastCellNum();

        int rowIndex = 1;// 从1开始的原因，模板的第一行固定了，所以从下一行开始

        while (rs.next()) {

            Row row = sheet.createRow(rowIndex++);

            for (int i = 0; i < cellNums; i++) {

                row.createCell(i).setCellValue(rs.getObject(i + 1).toString());

            }

        }

        return wb;

    }

    /**

     * 导出

     *

     * @param response

     * @param wb

     * @param fileName

     * @throws Exception

     */

    public static void export(HttpServletResponse response, Workbook wb,

            String fileName) throws Exception {

        response.setHeader("Content-Disposition", "attachment;filename="

                + new String(fileName.getBytes("utf-8"), "iso8859-1"));

        response.setContentType("application/ynd.ms-excel;charset=UTF-8");

        OutputStream out = response.getOutputStream();

        wb.write(out);

        out.flush();

        out.close();

    }

}