java实现excel和数据的交互

于 2017-09-23 21:16:00 发布
阅读量134 收藏 2
点赞数
文章标签： java 数据库
原文链接：http://www.cnblogs.com/steven158/p/7582592.html
版权

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);
    }
}

转载于:https://www.cnblogs.com/steven158/p/7582592.html

weixin_30834783
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值