我是靠谱客的博主 伶俐小兔子，最近开发中收集的这篇文章主要介绍使用java将数据库表导出为Excel表1、建立数据库表的实体类2、通过JDBC查询数据库表中的数据3、导入Excel表操作依赖包4、将实体列表转化为HSSFWorkbook工作簿对象5、将HSSFWorkbook以流写入文件完整代码输出文件结果，觉得挺不错的，现在分享给大家，希望可以做个参考。
概述
文章目录
- 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();
}
输出文件结果
最后
以上就是伶俐小兔子为你收集整理的使用java将数据库表导出为Excel表1、建立数据库表的实体类2、通过JDBC查询数据库表中的数据3、导入Excel表操作依赖包4、将实体列表转化为HSSFWorkbook工作簿对象5、将HSSFWorkbook以流写入文件完整代码输出文件结果的全部内容，希望文章能够帮你解决使用java将数据库表导出为Excel表1、建立数据库表的实体类2、通过JDBC查询数据库表中的数据3、导入Excel表操作依赖包4、将实体列表转化为HSSFWorkbook工作簿对象5、将HSSFWorkbook以流写入文件完整代码输出文件结果所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复