poi 学习之使用 Apche poi 读取数据库中的数据并写入Excel
来源:互联网 发布:苹果mac序列号官方查询 编辑:程序博客网 时间:2024/04/28 08:41
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。
注:以上所写部分来自网络以及自己学习时所记录,若有高人指点,必虚心学习。
0 0
- poi 学习之使用 Apche poi 读取数据库中的数据并写入Excel
- poi 学习之 使用 Apche poi 简单读写 Excel
- 使用Apache POI创建Excel,并从数据库中读取数据写入到Excel文件中
- poi读取数据写入excel
- java SpringMVC中 POI读取数据库数据并写入Excel表格中,并实现下载功能
- 使用POI操作Excel,读取、写入Excel
- poi 读取写入excel
- 使用POI读取EXCEL并保存到数据库
- itext poi 学习之旅 (3)读取数据库信息并由excel展现出来
- POI读取和写入Excel
- POI操作Excel,读取,写入
- java8 POI 读取写入EXCEL
- POI excel的读取,写入
- POI读取Excel数据
- POI 读取excel数据
- 使用POI 将数据库中的数据生成Excel文件并下载
- 【POI】读取Excel表中的数据
- 使用Apache POI读取Excel文件,将读取的数据转换成json写入文本文件
- Spring Security3源码分析(2)-http标签解析
- javascript基础语法
- Spring Security3源码分析(3)-authentication-manager标签解析
- 各种数据库对应的jar包、驱动类名和URL格式
- java中怎么让小数点变百分比
- poi 学习之使用 Apche poi 读取数据库中的数据并写入Excel
- 中水处理设备:中水回用技术简要说明
- Spring Security3源码分析(4)-FilterChainProxy执行过程分析
- ld: 7 duplicate symbols for architecture i386 clang: error: linker command failed with exit code 1 (
- LECTURES WILL BE POSTED AT THE END OF EACH WEEK (一)
- Nginx常用配置实例
- Spring Security3源码分析(5)-SecurityContextPersistenceFilter分析
- bu
- 将DataTable数据导出到Excel文件中(xls)