使用java将数据库表导出为Excel表

文章目录

• ​​1、建立数据库表的实体类​​
• ​​2、通过JDBC查询数据库表中的数据​​
• ​​3、导入Excel表操作依赖包​​
• ​​4、将实体列表转化为HSSFWorkbook工作簿对象​​
• ​​5、将HSSFWorkbook以流写入文件​​
• ​​完整代码​​
• ​​输出文件结果​​

1、建立数据库表的实体类

public class Food implements Serializable {
    //食品id
    private int id;
    //菜名
    private String name;
    //菜品
    private String type;
    //价格
    private String pay;
    //所属食堂
    private String canteen;

    public Food() {
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getPay() {
        return pay;
    }

    public void setPay(String pay) {
        this.pay = pay;
    }

    public String getCanteen() {
        return canteen;
    }

    public void setCanteen(String canteen) {
        this.canteen = canteen;
    }

    @Override
    public String toString() {
        return "Food{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", type='" + type + '\'' +
                ", pay='" + pay + '\'' +
                ", canteen='" + canteen + '\'' +
                '}';
    }
}

2、通过JDBC查询数据库表中的数据

public static void main(String[] args) throws Exception {
        List<Food> foods = findAll();
        for(Food food:foods){
            System.out.println(food);
        }
    }

    public static List<Food> findAll() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");

        Connection conn = DriverManager.getConnection("jdbc:mysql:///food_consumption_manager", "root", "123456");
        String sql = "select * from food";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();

        List<Food> foodList = new ArrayList<Food>();
        while(rs.next()){
            Food food = new Food();
            food.setId(rs.getInt("id"));
            food.setName(rs.getString("name"));
            food.setType(rs.getString("type"));
            food.setPay(rs.getString("pay"));
            food.setCanteen(rs.getString("canteen"));

            foodList.add(food);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return foodList;
    }

3、导入Excel表操作依赖包

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

4、将实体列表转化为HSSFWorkbook工作簿对象

public static HSSFWorkbook export(List<Food> foods){
        System.out.println("进入HSSFWorkbook===========================");
        // 创建excel对象
        HSSFWorkbook wk = new HSSFWorkbook();
        // 创建一张食物信息表
        HSSFSheet sheet = wk.createSheet("食物信息表");
        // 创建标题行
        HSSFRow smallTitle = sheet.createRow(0);

        smallTitle.createCell(0).setCellValue("食品id");
        smallTitle.createCell(1).setCellValue("菜名");
        smallTitle.createCell(2).setCellValue("菜品");
        smallTitle.createCell(3).setCellValue("价格");
        smallTitle.createCell(4).setCellValue("食堂");

        int count=1;
        for(Food food:foods){
            HSSFRow row = sheet.createRow(count++);

            row.createCell(0).setCellValue(food.getId());
            row.createCell(1).setCellValue(food.getName());
            row.createCell(2).setCellValue(food.getType());
            row.createCell(3).setCellValue(food.getPay());
            row.createCell(4).setCellValue(food.getCanteen());
        }

        return wk;
    }

5、将HSSFWorkbook以流写入文件

public static void toExcel(HSSFWorkbook wk) throws Exception {
        OutputStream outputStream = new FileOutputStream(new File("food.xls"));
        wk.write(outputStream);
        wk.close();
    }

完整代码

public static void main(String[] args) throws Exception {
        List<Food> foods = findAll();
        for(Food food:foods){
            System.out.println(food);
        }

        HSSFWorkbook hw = export(foods);
        toExcel(hw);
    }

    public static List<Food> findAll() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");

        Connection conn = DriverManager.getConnection("jdbc:mysql:///food_consumption_manager", "root", "123456");
        String sql = "select * from food";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();

        List<Food> foodList = new ArrayList<Food>();
        while(rs.next()){
            Food food = new Food();
            food.setId(rs.getInt("id"));
            food.setName(rs.getString("name"));
            food.setType(rs.getString("type"));
            food.setPay(rs.getString("pay"));
            food.setCanteen(rs.getString("canteen"));

            foodList.add(food);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return foodList;
    }

    public static HSSFWorkbook export(List<Food> foods){
        System.out.println("进入HSSFWorkbook===========================");
        // 创建excel对象
        HSSFWorkbook wk = new HSSFWorkbook();
        // 创建一张食物信息表
        HSSFSheet sheet = wk.createSheet("食物信息表");
        // 创建标题行
        HSSFRow smallTitle = sheet.createRow(0);

        smallTitle.createCell(0).setCellValue("食品id");
        smallTitle.createCell(1).setCellValue("菜名");
        smallTitle.createCell(2).setCellValue("菜品");
        smallTitle.createCell(3).setCellValue("价格");
        smallTitle.createCell(4).setCellValue("食堂");

        int count=1;
        for(Food food:foods){
            HSSFRow row = sheet.createRow(count++);

            row.createCell(0).setCellValue(food.getId());
            row.createCell(1).setCellValue(food.getName());
            row.createCell(2).setCellValue(food.getType());
            row.createCell(3).setCellValue(food.getPay());
            row.createCell(4).setCellValue(food.getCanteen());
        }

        return wk;
    }

    public static void toExcel(HSSFWorkbook wk) throws Exception {
        OutputStream outputStream = new FileOutputStream(new File("food.xls"));
        wk.write(outputStream);
        wk.close();
    }

输出文件结果