Java 利用POI实现将数据库中内容导入到EXcel中

 

最近做了一些Excel的导入导出的东西，在从数据库导出到Excel时，参考的这个博客，挺实用的，所以转帖过来

效果:

使用时先把poi包导入工程的path,注意只需要导入poi包即可,下载后有三个jar包

核心代码:

连接数据库:DBConnection.java

package org.xg.db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class DBConnection {private final String DBUrl = "jdbc:mysql://localhost:3306/notebook" ;private final String DBDriver = "com.mysql.jdbc.Driver" ;private final String username = "root" ;private final String password = "riskfitfeng" ;private Connection con ;public DBConnection(){try {Class.forName(DBDriver) ;con = DriverManager.getConnection(DBUrl,username,password) ;} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}public Connection getDB(){return con ;}public void closeDb(ResultSet rs,PreparedStatement ps){if(rs!=null){try {rs.close() ;} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(ps!=null){try {ps.close() ;} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}

导入excel类:MySql2Excel.java

package org.xg.db;import java.io.FileOutputStream;import java.io.OutputStream;import java.sql.Connection;import java.sql.ResultSet;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;public class MySql2Excel {public MySql2Excel() throws Exception{Connection con = null ;DBConnection db = new DBConnection() ;con = db.getDB() ;String sql = "select * from students" ;ResultSet rs = con.createStatement().executeQuery(sql) ;// 获取总列数int CountColumnNum = rs.getMetaData().getColumnCount() ;int i = 1 ;// 创建Excel文档HSSFWorkbook wb = new HSSFWorkbook() ;// sheet 对应一个工作页HSSFSheet sheet = wb.createSheet("student表中的数据") ;HSSFRow firstrow = sheet.createRow(0); //下标为0的行开始HSSFCell[] firstcell = new HSSFCell[CountColumnNum];String[] names = new String[CountColumnNum];names[0] = "ID";names[1] = "学号";names[2] = "姓名";names[3] = "性别";names[4] = "班级";for(int j= 0 ;j<CountColumnNum; j++){firstcell[j] = firstrow.createCell((short)j);firstcell[j].setCellValue(new HSSFRichTextString(names[j]));}while(rs.next()){// 创建电子表格的一行HSSFRow row = sheet.createRow(i) ; // 下标为1的行开始for(int j=0;j<CountColumnNum;j++){// 在一行内循环HSSFCell cell = row.createCell((short) j) ;// 设置表格的编码集，使支持中文//// 先判断数据库中的数据类型// 将结果集里的值放入电子表格中cell.setCellValue(new HSSFRichTextString(rs.getString(j+1))) ;}i++ ;}// 创建文件输出流，准备输出电子表格OutputStream out = new FileOutputStream("E:\\person.xls") ;wb.write(out) ;out.close() ;System.out.println("数据库导出成功") ;rs.close() ;con.close() ;}public static void main(String[] args){try {@SuppressWarnings("unused")MySql2Excel excel = new MySql2Excel() ;} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}}

例如你可以在前端jsp中这样调用:

<a href="ReportServlet" onclick="return confirm('确认数据导出到E:/下?');">导出数据到Excel</a>

后台servlet写上上面的代码,注意最后需要response.sendRedirect("") 返回前端