解析excel数据,并存入数据库
解析excel数据,并存入数据库
朱小宝 发表于1年前
解析excel数据,并存入数据库
  • 发表于 1年前
  • 阅读 38
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 技术升级10大核心产品年终让利>>>   

/**
 *
 */
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());
        }
    }*/
}

共有 人打赏支持
粉丝 2
博文 2
码字总数 2388
×
朱小宝
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: