1. 环境要求
本文环境为: 数据库为oracle,jdk为jdk7,依赖jar包为ojdbc6-11.2.0.4.0.jar+poi-3.14.jar
2.POI 使用
1. 建立工作空间
2. 获取sheet
3. 使用row
4. 使用cell
3. 代码部分一
3.1 使用方法导出,代码如下
private static void test1() {
Connection con=null;
PreparedStatement preStatement=null;
ResultSet result=null;
try{
/**
* 数据库连接
*/
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("开始尝试连接数据库！");
String url = "jdbc:oracle:" + "thin:@127.0.0.1:1521:localOracle";
String user = "hsm";
String password = "1994713";
con = DriverManager.getConnection(url, user, password);
System.out.println("连接成功！开始查询数据");
/**
* 执行sql语句,将结果保存在result中
*/
String sql = "select dict_type,dict_value,dict_desc from frp_dict ";
preStatement = con.prepareStatement(sql);
result = preStatement.executeQuery();
/**
* 创建excel头
*/
List<String> headers=new ArrayList<String>();
headers.add("dict_type");
headers.add("dict_value");
headers.add("dict_desc");
//创建新工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//新建工作表
HSSFSheet sheet = workbook.createSheet("frp_dict");
//创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
HSSFRow row = sheet.createRow(0);
for(int i=0;i<headers.size();i++){
HSSFCell cell=row.createCell(i);
cell.setCellValue(headers.get(i));
}
int rowTemp=1;
while(result.next()){
row = sheet.createRow(rowTemp);
for(int i=0;i<headers.size();i++){
HSSFCell cell=row.createCell(i);
cell.setCellValue(result.getString(headers.get(i)));
}
rowTemp++;
}
FileOutputStream fos = new FileOutputStream(new File("C:\\Users\\hsm\\Desktop\\exportTest.xls"));
workbook.write(fos);
workbook.close();
fos.close();
}catch (Exception e){
e.printStackTrace();
}finally{
try{
// 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
// 注意关闭的顺序，最后使用的最先关闭
if (result != null)
result.close();
if (preStatement != null)
preStatement.close();
if (con != null)
con.close();
System.out.println("数据库连接已关闭！");
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
3.2 导出结果展示
4 代码部分二
4.1 调用方法导入,方法如下
private static void test3(){
Connection con=null;
PreparedStatement preStatement=null;
ResultSet result=null;
try{
/**
* 数据库连接
*/
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("开始尝试连接数据库！");
String url = "jdbc:oracle:" + "thin:@127.0.0.1:1521:localOracle";
String user = "hsm";
String password = "1994713";
con = DriverManager.getConnection(url, user, password);
System.out.println("连接成功！开始查询数据");
/**
* 执行sql语句,将结果保存在result中
*/
String sql = "";
FileInputStream fis = new FileInputStream(new File("C:\\Users\\hsm\\Desktop\\exportTest2.xls"));
//创建新工作簿
HSSFWorkbook workbook = new HSSFWorkbook(fis);
//获取第一个sheet
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFRow row=sheet.getRow(0);
StringBuffer columns=new StringBuffer(row.getCell(0).getStringCellValue());
StringBuffer param=new StringBuffer("?");
for(int i=1;i<row.getLastCellNum();i++){
columns.append(",").append(row.getCell(i).getStringCellValue());
param.append(",").append("?");
}
System.out.println(columns.toString());
sql="insert into frp_dict_test ("+columns+") values ("+param+")";
preStatement = con.prepareStatement(sql);
for(int i=1;i<sheet.getLastRowNum();i++){
row=sheet.getRow(i);
for(int j=0;j<row.getLastCellNum();j++){
preStatement.setString(j+1, row.getCell(j).getStringCellValue());
}
preStatement.addBatch();;
if(i%100==0){
preStatement.executeBatch();
}
}
preStatement.executeBatch();
con.commit();
fis.close();
workbook.close();
}catch (Exception e){
e.printStackTrace();
}finally{
try{
// 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
// 注意关闭的顺序，最后使用的最先关闭
if (result != null)
result.close();
if (preStatement != null)
preStatement.close();
if (con != null)
con.close();
System.out.println("数据库连接已关闭！");
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
4.2 导入结果显示
5.代码优化,形成工具类
5.1源代码
package com.hsm.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
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;
/**
* @author hsm
* excel相关的操作
*/
public class ExcelUtil {
private String driverType=null;
private String url=null;
private String userName=null;
private String password=null;
private ExcelUtil(){
}
/**
* 获取excel的实例
* @param driverType数据库类型
* @param url 数据库地址
* @param userName 用户名
* @param password 密码
* @return 当前实例
*/
public static ExcelUtil getInstance(String driverType,String url,String userName,String password){
ExcelUtil excelUtil=new ExcelUtil();
excelUtil.driverType=driverType;
excelUtil.url=url;
excelUtil.userName=userName;
excelUtil.password=password;
return excelUtil;
}
/**
* 下载excel<br/>
* 从数据库中导出相应表相关的字段的excel表格
* @param param 表格头部内容
* @param tableName 表名
* @param file 文件位置及文件名
*/
public void downloadSqlPara(List<String> param,String tableName,String file){
Connection con=null;
PreparedStatement preStatement=null;
ResultSet result=null;
try{
/**
* 数据库连接
*/
Class.forName(this.driverType);
System.out.println("================开始尝试连接数据库！===================");
String url = this.url;
String user = this.userName;
String password = this.password;
con = DriverManager.getConnection(url, user, password);
System.out.println("=================连接成功！开始查询数据================");
/**
* 执行sql语句,将结果保存在result中
*/
StringBuffer buffer=new StringBuffer(param.get(0));
for(int i=1;i<param.size();i++){
buffer.append(",").append(param.get(i));
}
String sql = "select "+buffer+" from "+tableName;
preStatement = con.prepareStatement(sql);
result = preStatement.executeQuery();
/**
* 创建excel工作区
*/
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(tableName);
HSSFRow row = sheet.createRow(0);
for(int i=0;i<param.size();i++){
HSSFCell cell=row.createCell(i);
cell.setCellValue(param.get(i));
}
int rowTemp=1;
System.out.println("================开始写入文件！===================");
while(result.next()){
row = sheet.createRow(rowTemp);
for(int i=0;i<param.size();i++){
HSSFCell cell=row.createCell(i);
cell.setCellValue(result.getString(param.get(i)));
}
rowTemp++;
}
System.out.println("================文件写入结束！===================");
FileOutputStream fos = new FileOutputStream(new File(file));
workbook.write(fos);
workbook.close();
fos.close();
}catch (Exception e){
e.printStackTrace();
}finally{
try{
// 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
// 注意关闭的顺序，最后使用的最先关闭
if (result != null)
result.close();
if (preStatement != null)
preStatement.close();
if (con != null)
con.close();
System.out.println("数据库连接已关闭！");
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
/**
* 导入excel表格内容
* @param headers
* @param tableName
* @param file
*/
public void importSqlExcel(List<String> headers,String tableName,String file){
Connection con=null;
PreparedStatement preStatement=null;
ResultSet result=null;
try{
/**
* 数据库连接
*/
Class.forName(this.driverType);
System.out.println("开始尝试连接数据库！");
String url = this.url;
String user = this.userName;
String password = this.password;
con = DriverManager.getConnection(url, user, password);
System.out.println("连接成功！开始查询数据");
/**
* 打开文件
*/
FileInputStream fis = new FileInputStream(new File(file));
/**
* 创建excel工作区
*/
//创建新工作簿
HSSFWorkbook workbook = new HSSFWorkbook(fis);
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFRow row=sheet.getRow(0);
/**
* 获取excel传入的字段,并对sql进行初始化
*/
StringBuffer columns=new StringBuffer(row.getCell(0).getStringCellValue());
StringBuffer param=new StringBuffer("?");
for(int i=1;i<row.getLastCellNum();i++){
columns.append(",").append(row.getCell(i).getStringCellValue());
param.append(",").append("?");
}
String sql = "";
sql="insert into "+tableName+" ("+columns+") values ("+param+")";
preStatement = con.prepareStatement(sql);
/**
* 遍历sheet中的内容,将内容批量插入数据库中
*/
for(int i=1;i<sheet.getLastRowNum();i++){
row=sheet.getRow(i);
for(int j=0;j<row.getLastCellNum();j++){
preStatement.setString(j+1, row.getCell(j).getStringCellValue());
}
preStatement.addBatch();;
if(i%100==0){
preStatement.executeBatch();
}
}
preStatement.executeBatch();
con.commit();
fis.close();
workbook.close();
}catch (Exception e){
e.printStackTrace();
}finally{
try{
// 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
// 注意关闭的顺序，最后使用的最先关闭
if (result != null)
result.close();
if (preStatement != null)
preStatement.close();
if (con != null)
con.close();
System.out.println("数据库连接已关闭！");
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
}
5.2优化代码测试
package com.hsm.test;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
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.hsm.util.ExcelUtil;
public class TestMain {
public static void main(String[] args) {
test4();
}
private static void test4(){
/**
* 数据库相关内容
*/
String driverType="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@127.0.0.1:1521:localOracle";
String userName="hsm";
String password="1994713";
ExcelUtil excelUtil=ExcelUtil.getInstance(driverType, url, userName, password);
/**
* excel导入
*/
List<String>param=new ArrayList<String>();
param.add("dict_type");
param.add("dict_value");
param.add("dict_desc");
String file="C:\\Users\\hsm\\Desktop\\exportTest2.xls";
excelUtil.importSqlExcel(param, "frp_dict_test", file);
}
private static void test2(){
/**
* 数据库相关内容
*/
String driverType="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@127.0.0.1:1521:localOracle";
String userName="hsm";
String password="1994713";
ExcelUtil excelUtil=ExcelUtil.getInstance(driverType, url, userName, password);
/**
* excel下载
*/
List<String>param=new ArrayList<String>();
param.add("dict_type");
param.add("dict_value");
param.add("dict_desc");
String file="C:\\Users\\hsm\\Desktop\\exportTest2.xls";
excelUtil.downloadSqlPara(param, "frp_dict", file);
}
}
