Java调用POI导出数据库信息

数据库链接包：链接：https://pan.baidu.com/s/18VhAHNoDF-gacq04AnBr4w 密码：vjlr

POI调用包：链接：https://pan.baidu.com/s/1Jhz6HlJc9gutRBwWE4KU-g 密码：dnzq

java代码：

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;

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

public class WriteExcel {

    Connection ct = null;
    Statement stat = null;
    ResultSet rs = null;

    public void writeExcel(String finalXlsxPath) throws SQLException {
        OutputStream out = null;

        //第一步创建workbook
        HSSFWorkbook wb = new HSSFWorkbook();

        //第二步创建sheet
        HSSFSheet sheet = wb.createSheet("身份证错误信息");

        //第三步创建行row:添加表头0行
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  //居中

        //第四步创建单元格
        HSSFCell cell = row.createCell(0);         //第一个单元格
        cell.setCellValue("账户");                  //设定值
        cell.setCellStyle(style);                   //内容居中

        cell = row.createCell(1);                   //第二个单元格   
        cell.setCellValue("密码");
        cell.setCellStyle(style);

        cell = row.createCell(2);                   //第三个单元格  
        cell.setCellValue("学号");
        cell.setCellStyle(style);

        cell = row.createCell(3);                   //第四个单元格  
        cell.setCellValue("姓名");
        cell.setCellStyle(style);

        cell = row.createCell(4);                   //第四个单元格  
        cell.setCellValue("学院");
        cell.setCellStyle(style);

        try {
            /**
             * 往Excel中写新数据
             */
            ResultSet rs = init("");

            int j = 1;
            while (rs.next()) {
                row = sheet.createRow(j++);
                //创建单元格并且添加数据
                row.createCell(0).setCellValue(rs.getString(1));
                row.createCell(1).setCellValue(rs.getString(2));
                row.createCell(2).setCellValue(rs.getString(3));
                row.createCell(3).setCellValue(rs.getString(4));
                row.createCell(4).setCellValue(rs.getString(5));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                    out = null;
                }
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (stat != null) {
                    stat.close();
                    stat = null;
                }
                if (ct != null) {
                    ct.close();
                    ct = null;
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        //第六步将生成excel文件保存到指定路径下
        try {
            FileOutputStream fout = new FileOutputStream(finalXlsxPath);
            wb.write(fout);
            fout.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

        System.out.println("Excel文件生成成功...");
    }

    /**
     * 判断Excel的版本,获取Workbook
     *
     * @return
     * @throws IOException
     */
    public Workbook getWorkbok(File file) throws IOException {
        Workbook wb = null;
        FileInputStream in = new FileInputStream(file);
        wb = new HSSFWorkbook(in);
        return wb;
    }

    //初始化
    public ResultSet init(String sql) {
        if (sql.equals("")) {
            sql = "select * from login_student";
        }
        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("加载成功");
            //2.连接数据库
            //定义几个常量
            String url = "jdbc:mysql://localhost:3306/system_test";
            String user = "root";
            String passwd = "next123456";

            ct = DriverManager.getConnection(url, user, passwd);
            stat = ct.createStatement();//创建stat对象
            rs = stat.executeQuery(sql);//查询结果

            return rs;

        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

}