?
之前写了一篇poi读写excel的简单demo,但考虑到实际项目中,主要还是对将数据库中的数据进行写入excel,所以练习了一个也是比较简单的demo。
?
首先:
?
数据库:
?
创建一个数据库连接的Util类
?
DBConnectionUtil.java
?
class="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。
?
注:以上所写部分来自网络以及自己学习时所记录,若有高人指点,必虚心学习。
?
原文地址:http://blog.csdn.net/u014011236/article/details/39204591