文档章节

解析excel数据,并存入数据库

 朱小宝
发布于 2016/11/22 11:20
字数 624
阅读 58
收藏 0

/**
 *
 */
package com.tydic.commons.readExcel;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

 

public class ReadExcel {
    
 

    /**
     * Read the Excel 2010
     * @param path the path of the excel file
     * @return
     * @throws IOException
     */
    public static List<MvnoInfo> readXlsx() throws IOException {
        String path = "E:\\123.xlsx";
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        MvnoInfo mvnoInfo = null;
        List<MvnoInfo> list = new ArrayList<MvnoInfo>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow.getCell(0) == null) {
                    break;
                }
                if (xssfRow != null) {
                    mvnoInfo = new MvnoInfo();
                    XSSFCell mvnofullname = xssfRow.getCell(0);
                    XSSFCell mvnobusinessname = xssfRow.getCell(1);
                    XSSFCell busitype = xssfRow.getCell(2);
                    XSSFCell busitypeopenstatus = xssfRow.getCell(3);
                    XSSFCell firstagreementsigntime = xssfRow.getCell(4);
                    XSSFCell firstapprovepapertime = xssfRow.getCell(5);
                    XSSFCell firstapproveexpirydate = xssfRow.getCell(6);
                    XSSFCell accesstestpasstime = xssfRow.getCell(7);
                    XSSFCell servicevalidatepasstime = xssfRow.getCell(8);
                    XSSFCell trialbusinesstime = xssfRow.getCell(9);
                    XSSFCell serviceassessapplytime = xssfRow.getCell(10);
                    XSSFCell serviceassesspasstime = xssfRow.getCell(11);
                    XSSFCell serviceassessresult = xssfRow.getCell(12);
                    XSSFCell formalbusitime = xssfRow.getCell(13);
                    XSSFCell suspendprotocoltime = xssfRow.getCell(14);
                    XSSFCell recoverprotocoltime = xssfRow.getCell(15);
                    XSSFCell exitprotocoltime = xssfRow.getCell(16);
                    mvnoInfo.setMvnofullname(getValue(mvnofullname));
                    mvnoInfo.setMvnobusinessname(getValue(mvnobusinessname));
                    mvnoInfo.setBusitype(getValue(busitype));
                    mvnoInfo.setBusitypeopenstatus(getValue(busitypeopenstatus));
                    mvnoInfo.setFirstagreementsigntime(getValue(firstagreementsigntime));
                    mvnoInfo.setFirstapprovepapertime(getValue(firstapprovepapertime));
                    mvnoInfo.setFirstapproveexpirydate(getValue(firstapproveexpirydate));
                    mvnoInfo.setAccesstestpasstime(getValue(accesstestpasstime));
                    mvnoInfo.setServicevaliStringpasstime(getValue(servicevalidatepasstime));
                    mvnoInfo.setTrialbusinesstime(getValue(trialbusinesstime));
                    mvnoInfo.setServiceassessapplytime(getValue(serviceassessapplytime));
                    mvnoInfo.setServiceassesspasstime(getValue(serviceassesspasstime));
                    mvnoInfo.setServiceassessresult(getValue(serviceassessresult));
                    mvnoInfo.setFormalbusitime(getValue(formalbusitime));
                    mvnoInfo.setSuspendprotocoltime(getValue(suspendprotocoltime));
                    mvnoInfo.setRecoverprotocoltime(getValue(recoverprotocoltime));
                    mvnoInfo.setExitprotocoltime(getValue(exitprotocoltime));
                    System.out.println(mvnoInfo.toString());
                    list.add(mvnoInfo);
                }
            }
        }
        return list;
    }

   
    @SuppressWarnings("static-access")
    private static String getValue(XSSFCell xssfRow) {
        if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(xssfRow)) {
                SimpleDateFormat df =new SimpleDateFormat("yyyy-MM-dd");
                return df.format(HSSFDateUtil.getJavaDate(xssfRow.getNumericCellValue())).toString();
            }
            DecimalFormat df = new DecimalFormat("####");
            return String.valueOf(df.format(xssfRow.getNumericCellValue()));
        }else {
            return String.valueOf(xssfRow.getStringCellValue());
        }
    }


    
    
    public static void main(String[] args) throws IOException, SQLException {
        List<MvnoInfo> list = readXlsx();
        Connection connection = null;
        String url = "jdbc:oracle:thin:@131.213.123.123:1521:SODF";
        String username = "dp";
        String password = "123";
        String sql1 = "123123123 "
        String sql2 = "123123123 "
        connection = DriverManager.getConnection(url, username,password);
        PreparedStatement ps1 = connection.prepareStatement(sql1);
        PreparedStatement ps2 = connection.prepareStatement(sql2);
        Long a = null;
        int n = 0;
        for (MvnoInfo mvnoInfo : list) {
            ps2.setString(1, mvnoInfo.getMvnobusinessname());
            ResultSet ex = ps2.executeQuery();
            while(ex.next()){
                if (ex.getString(1) != null && !ex.getString(1).equals("")) {
                    a = Long.parseLong(ex.getString(1));
                    //a=Integer.parseInt(ex.getString(1));
                }
                ps1.setLong(1, a);
                ps1.setString(2, mvnoInfo.getMvnofullname());
                ps1.setString(3, mvnoInfo.getMvnobusinessname());
                ps1.setString(4, mvnoInfo.getBusitype());
                ps1.setString(5, mvnoInfo.getBusitypeopenstatus());
                ps1.setString(6, mvnoInfo.getFirstagreementsigntime());
                ps1.setString(7, mvnoInfo.getFirstapprovepapertime());
                ps1.setString(8, mvnoInfo.getFirstapproveexpirydate());
                ps1.setString(9, mvnoInfo.getAccesstestpasstime());
                ps1.setString(10, mvnoInfo.getServicevaliStringpasstime());
                ps1.setString(11, mvnoInfo.getTrialbusinesstime());
                ps1.setString(12, mvnoInfo.getServiceassessapplytime());
                ps1.setString(13, mvnoInfo.getServiceassesspasstime());
                ps1.setString(14, mvnoInfo.getServiceassessresult());
                ps1.setString(15, mvnoInfo.getFormalbusitime());
                ps1.setString(16, mvnoInfo.getSuspendprotocoltime());
                ps1.setString(17, mvnoInfo.getRecoverprotocoltime());
                ps1.setString(18, mvnoInfo.getExitprotocoltime());
                ps1.executeUpdate();
                n++;
            }
        }
        if (connection != null) {
            connection.close();
        }
        System.out.println(n+"\n"+list.size());
    }
    
    
   
    /**
     * Read the Excel 2003-2007
     * @param path the path of the Excel
     * @return
     * @throws IOException
     */
   /* public static List<MvnoInfo> readXls() throws IOException {
        String path = "E:\\123.xls";
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        MvnoInfo mvnoInfo = null;
        List<MvnoInfo> list = new ArrayList<MvnoInfo>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    mvnoInfo = new MvnoInfo();
                    HSSFCell no = hssfRow.getCell(0);
                    HSSFCell name = hssfRow.getCell(1);
                    HSSFCell age = hssfRow.getCell(2);
                    HSSFCell score = hssfRow.getCell(3);
                    String v1 = getValue(no);
                    String v2 = getValue(name);
                    String v3 = getValue(age);
                    String v4 = getValue(score);
                    System.out.println(v1+"----"+v2+"----"+v3+"-----"+v4);
                    list.add(mvnoInfo);
                }
            }
        }
        return list;
    }*/

    /* @SuppressWarnings("static-access")
    private static String getValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }*/
}

© 著作权归作者所有

共有 人打赏支持
上一篇: 前端js相关校验
下一篇: 前端js相关校验
粉丝 1
博文 2
码字总数 2388
作品 0
海淀
程序员
私信 提问
请教各路神友,java中用poi对EXCEL操作的一些问题!

请教各路神友,我准备做一个页面配置EXCEL的表格式,然后生成excel表提供下载,excel表填好后再上传上来,解析数据后存入数据库,对EXCEL的生成、下载、上传、解析等操作准备用poi做,有没有...

昊南
2014/08/25
114
0
使用phpexcel解析Excel数据表并存储到数据库中全过程_PHP_thinkphp_cmf

分享经验是为了让你少走弯路————华伟君 使用PHPExcel解析Excel数据表并存储到数据库中全过程 --- 第一部分:thinkphp中实例化上传类获取from表单提交的文件(非thinkPHP用户可以忽略,直...

尘世如潮
2016/06/17
515
1
XmlReader解析Excel时界面上的浮点数值与xml中值不同,如何避免

使用XmlReader来解析是因为Excel数据量较大,而POI只支持写大数据量的excel,不能较好的支持解析读取大数据量excel。 使用后效率果然不错,但是在读取浮点数时遇到了一个棘手的问题,读取到的...

冷冷无情
2016/02/17
127
0
将Excel文件导入数据库(POI+Excel+MySQL+jsp页面导入)第一次优化

版权声明:本文为博主Lynn&Lingfei原创文章,未经博主允许不得转载。 https://blog.csdn.net/meng564764406/article/details/52444644 本篇文章是根据我的上篇博客,给出的改进版,由于时间有...

Lynn_Meng
2016/09/05
0
0
JavaWEB开发 把临时数据存入Session还是存到缓存中

用户故事:把经销商信息的EXCEL表格,导入到系统,在前端页面显示数据信息,数据超过50条需要分页;操作员确认信息无误后,点击导入到数据库; 我的问题是:把Excel导入系统中(还未插入到数...

YOTOO
2014/04/18
6.9K
7

没有更多内容

加载失败,请刷新页面

加载更多

求推广,德邦快递坑人!!!!

完全没想好怎么来吐槽自己这次苦逼的德邦物流过程了,只好来记一个流水账。 从寄快递开始: 2019年1月15日从 德邦物流 微信小app上下单,截图如下: 可笑的是什么,我预约的是17号上门收件,...

o0无忧亦无怖
昨天
4
0
Mac Vim配置

1.升级 vim   我自己 MacBook Pro 的系统还是 10.11 ,其自带的 vim 版本为 7.3 ,我们将其升至最新版: 使用 homebrew : brew install vim --with-lua --with-override-system-vim 这将下...

Pasenger
昨天
5
0
vmware安装Ubuntu上不了网?上网了安装不了net-tools,无法执行ifconfig?

1.重新设置网络适配器还是不行,如下指定nat 2.还需要指定共享网络,我是在无线环境下 3.无法执行ifconfig https://packages.ubuntu.com/bionic/net-tools到这个网站下载net-tools的deb文件...

noob_chr
昨天
2
0
解决SVN:E210007无法协商认证机制

svn:E210007 svn: Cannot negotiate authentication mechanism 执行下面代码即可 sudo yum install cyrus-sasl cyrus-sasl-plain cyrus-sasl-ldap...

临江仙卜算子
昨天
2
0
java8的时间和`Date`的对比

java8的时间和Date的对比 java8提供了新的时间接口。相对Date,Calendar,个人感觉最大的好处是对时间操作的学习成本很低,比Calendar低。 1. LocalDate,LocalTime,LocalDateTime LocalDate 代...

北风刮的不认真了
昨天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部