Apche poi 读取数据库中的数据并写入 Excel
之前写了一篇 poi 读写 excel 的简单demo,但考虑到实际项目中,主要还是对将数据库中的数据进行写入excel,所以练习了一个也是比较简单的demo。
首先:
数据库:
创建一个数据库连接的Util类
DBConnectionUtil.java
package com.poi.org;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBConnectionUtil {
private static Connection conn = null;
private static String Driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost/quechao?useUnicode=true&characterEncoding=utf8";
private static String userName = "root";
private static String password = "123456";
public static Connection getConnection(){
try {
Class.forName(Driver);
conn = DriverManager.getConnection(url, userName, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void closeDB(ResultSet rs, Statement st, Connection conn){
try {
if(rs != null) rs.close(); rs = null;
if(st != null) st.close(); st = null;
if(conn != null) conn.close(); conn = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
实体类 Hospital.java
package com.poi.model;
public class Hospital {
private Integer id;
private String hospitalOn;
private String province;
private String city;
private String title;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getHospitalOn() {
return hospitalOn;
}
public void setHospitalOn(String hospitalOn) {
this.hospitalOn = hospitalOn;
}
public String getProvince() {
return province;
}
public void setProvince(String province) {
this.province = province;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
}
测试类QueryHospital.java
package com.poi.test;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
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 com.poi.model.Hospital;
import com.poi.org.DBConnectionUtil;
public class QueryHospital {
public static List<Hospital> selectHospital()throws Exception{
Connection conn = null;
ResultSet rs = null;
Statement st = null;
String sql = "select * from hospital";
conn = DBConnectionUtil.getConnection();
List<Hospital> list = new ArrayList<Hospital>();
try {
st = conn.createStatement();
rs = st.executeQuery(sql);
while(rs.next()){
Hospital hospital = new Hospital();
//hospital.setId(rs.getInt("id"));
hospital.setId(rs.getInt(1));
//hospital.setHospitalOn(String.valueOf(rs.getInt("hospitalOn")));
hospital.setHospitalOn(rs.getString(2));
hospital.setProvince(rs.getString(3));
hospital.setCity(rs.getString(4));
//hospital.setTitle(String.valueOf(rs.getInt("title")));
hospital.setTitle(rs.getString(5));
list.add(hospital);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DBConnectionUtil.closeDB(rs, st, conn);
}
return list;
}
public void createExcel()throws Exception{
HSSFWorkbook workBook = new HSSFWorkbook();
HSSFSheet sheet = workBook.createSheet("第一页");
sheet.setColumnWidth(0, 2500);
sheet.setColumnWidth(1, 5000);
HSSFRow row = sheet.createRow(0);
HSSFCell cell[] = new HSSFCell[5];
for(int i = 0; i < 5; i++){
cell[i] = row.createCell(i);
}
cell[0].setCellValue("id");
cell[1].setCellValue("hospitalOn");
cell[2].setCellValue("province");
cell[3].setCellValue("city");
cell[4].setCellValue("title");
List<Hospital> list = QueryHospital.selectHospital();
if(list != null && list.size() > 0){
for(int i = 0; i < list.size(); i++){
Hospital hospital = list.get(i);
HSSFRow dataRow = sheet.createRow(i+1);
HSSFCell dataCell[] = new HSSFCell[5];
for(int j = 0; j < 5; j++){
dataCell[j] = dataRow.createCell(j);
}
dataCell[0].setCellValue(hospital.getId());
dataCell[1].setCellValue(hospital.getHospitalOn());
dataCell[2].setCellValue(hospital.getProvince());
dataCell[3].setCellValue(hospital.getCity());
dataCell[4].setCellValue(hospital.getTitle());
File file = new File("E:\\hospital.xls");
FileOutputStream fos = new FileOutputStream(file);
workBook.write(fos);
fos.close();
}
}
}
public static void main(String[] args)throws Exception {
QueryHospital queryHospital = new QueryHospital();
queryHospital.createExcel();
}
}
考虑到每个人项目中用的持久层技术(Hibernate、Mybatis等)会有不同,所以这里用的jdbc,后续抽时间会再写一个读取Excel并写入数据库的demo。
注:以上所写部分来自网络以及自己学习时所记录,若有高人指点,必虚心学习。