DBUtil数据库连接工具类操作excel

DBUtil.java

package com.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DBUtil {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    private static Properties pro = null;
    static{
        pro = new Properties();  
        try {
            pro.load(DBUtil.class.getClassLoader().getResourceAsStream("db.properties"));
            driver = pro.getProperty("db.driver").trim();
            url = pro.getProperty("db.url").trim();
            username = pro.getProperty("db.username").trim();
            password = pro.getProperty("db.password").trim();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    public static Connection getConn(){
        try {
            Class.forName(driver);
            return DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    

    private static void closeConn(Connection conn) {
        closeConn(conn, null, null);
    }
    
    
    public static void closeConn(Connection conn,Statement stm,ResultSet rs){
        if(null != conn){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        if(null != stm){
            try {
                stm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(null != rs){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    public static void main(String[] args){
        Connection conn = DBUtil.getConn();
        System.out.println(conn);
        
        DBUtil.closeConn(conn);
        System.out.println("------over------");
    }
}

Province.java

package com.jdbc.entity;

public class Province {
    private int id;
    private int prov_id;
    private String prov_name;
    private String prov_type;
    private String prov_state;

    public Province(int id, int prov_id, String prov_name, String prov_type,
            String prov_state) {
        super();
        this.id = id;
        this.prov_id = prov_id;
        this.prov_name = prov_name;
        this.prov_type = prov_type;
        this.prov_state = prov_state;
    }

    public Province() {
        super();
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getProv_id() {
        return prov_id;
    }

    public void setProv_id(int prov_id) {
        this.prov_id = prov_id;
    }

    public String getProv_name() {
        return prov_name;
    }

    public void setProv_name(String prov_name) {
        this.prov_name = prov_name;
    }

    public String getProv_type() {
        return prov_type;
    }

    public void setProv_type(String prov_type) {
        this.prov_type = prov_type;
    }

    public String getProv_state() {
        return prov_state;
    }

    public void setProv_state(String prov_state) {
        this.prov_state = prov_state;
    }

}

MakeExcelProvince

package com.poi.excel;


import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.jdbc.entity.Province;
import com.test.excel.User;
import com.util.DBUtil;

/**
 * java to excel 导入appache的poi 3.15 jar 包
 */
public class MakeExcelProvince {

    // 产生要储存的集合
    protected static List<Province> getProvince() throws Exception {
        List<Province> provinces = new ArrayList<Province>();
        Connection conn = DBUtil.getConn();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from province");
        while(rs.next()){
            Province province = new Province(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5));
            provinces.add(province);
        }
        
        return provinces;
    }

    public static void main(String[] args) throws Exception {
        // 第一步，创建一个workbook对应一个excel文件
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 第二部，在workbook中创建一个sheet对应excel中的sheet
        XSSFSheet sheet = workbook.createSheet("省份表");
        // 第三部，在sheet表中添加表头第0行，老版本的poi对sheet的行列有限制
        XSSFRow row = sheet.createRow(0);
        // 第四步，创建单元格，设置表头
        XSSFCell cell = row.createCell(0);
        cell.setCellValue("id");
        
        cell = row.createCell(1);
        cell.setCellValue("prov_id");
        
        cell = row.createCell(2);
        cell.setCellValue("prov_name");
        
        cell = row.createCell(3);
        cell.setCellValue("prov_type");
        
        cell = row.createCell(4);
        cell.setCellValue("prov_state");
        

        // 第五步，写入实体数据，实际应用中这些数据从数据库得到,对象封装数据，集合包对象。对象的属性值对应表的每行的值
        List<Province> provinces = getProvince();
        for (int i = 0; i < provinces.size(); i++) {
            XSSFRow row1 = sheet.createRow(i + 1);
            Province province = provinces.get(i);
            // 创建单元格设值
            row1.createCell(0).setCellValue(province.getId());
            row1.createCell(1).setCellValue(province.getProv_id());
            row1.createCell(2).setCellValue(province.getProv_name());
            row1.createCell(3).setCellValue(province.getProv_type());
            row1.createCell(4).setCellValue(province.getProv_state());
        }

        // 将文件保存到指定的位置
        try {
            FileOutputStream fos = new FileOutputStream("f:\\test4.xlsx");
            workbook.write(fos);
            System.out.println("写入成功");
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

jdbc properties

#jdbc properties
db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql://xxx.xxx.xxx.xxx/test
db.username=xxx
db.password=xxxxx

注：需要修改具体数据库连接配置

jar包地址：https://www.apache.org/dyn/closer.lua/poi/release/bin/poi-bin-3.16-20170419.tar.gz
解压后，将所有文件夹下的jar包都添加到环境变量当中，