栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

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

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

使用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 foods = findAll();
        for(Food food:foods){
            System.out.println(food);
        }
    }

    public static List 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 foodList = new ArrayList();
        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表操作依赖包

    org.apache.poi
    poi
    3.17


    org.apache.poi
    poi-ooxml
    3.17

4、将实体列表转化为HSSFWorkbook工作簿对象
public static HSSFWorkbook export(List 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 foods = findAll();
        for(Food food:foods){
            System.out.println(food);
        }

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

    public static List 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 foodList = new ArrayList();
        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 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();
    }
输出文件结果

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/983865.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号