在线一区二区视频,五月激情久久久,中文字幕综合在线

excel文件導入數據庫

/***************************************************************************************************
*類表述信息:針對用戶對象操作進行的一系列操作分派
*@author 利安寧
*@version 1.0 2008.5.20
*@since jdk1.4.0_06
***************************************************************************************************/
package org.bussiness.utility.subjectByformula;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;

import jxl.*;
import jxl.read.biff.BiffException;
import jxl.Sheet;

public class FormulaLoad2Db {

private static final String filePath = "d:\\健康保險統計制度統計指標.xls";

/**
* 根據給定的路徑讀入excel文件
* @param path是文件路徑
* @return List是對bean的封裝
* @author 利安寧
* @version 1.0 2008.5.26
*/
private List readExcelContext(String path) {
  List list=new ArrayList();
  Map markermap=new HashMap();
  File file = new File(path);
  Workbook wb = null;
  Sheet arySheet=null;
  try {
   wb = Workbook.getWorkbook(file);
   Sheet[] arySheets = wb.getSheets();
   for(int i=0; i<arySheets.length; i++){
    arySheet = arySheets[i];
    int startRow=1000;
    int endRow=0;
    int startColumn=0;
    for(int ii=0;ii<arySheet.getRows();ii++){
     for(int j=0;j<arySheet.getColumns();j++){
      String strCell=arySheet.getCell(j, ii).getContents().trim();
      if(strCell.length()>0 && strCell.matches("[a-z]?\\d{8,10}")){
       if(startRow>ii){
        startRow=ii;
       }
       endRow=ii;
       startColumn=j;
      }
     }
    }
    endRow++;
//    System.out.println(startRow+" "+endRow+" "+startColumn);
    if(startRow>endRow){
     System.out.println("此為空sheet");
    }else{
//     System.out.println(excel2List(arySheet,startRow,endRow,startColumn).size());
     list=Excel2List(arySheet,startRow,endRow,startColumn);
     loadDataToDb(list);
     insertByName("all");
    }
   }
  } catch (BiffException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   wb.close();
  }
  return list;
}

/**
* 根據給定的Sheet,解析excel,封裝成bean放到List中
* @param arySheet是給定的Sheet
* @param startColumns是讀取Sheet的開始行
* @param endColumns是讀取Sheet的結束行
* @return List是對bean的封裝
* @author 利安寧
* @version 1.0 2008.5.26
*/
private List Excel2List(Sheet arySheet,int startColumns,int endColumns,int columns ){
  List list=new ArrayList();
  String matchStr="acegikmo";
  IndexBean ib = null;
  Cell[] cell1=arySheet.getColumn(1);
  int endRows = arySheet.getRows();
  String roleId="0" ;
  String pp;
  String s="-1";
  for (int startRow =startColumns; startRow <endColumns; startRow++) {
   String a=arySheet.getCell(columns, startRow).getContents().trim();
   String b=arySheet.getCell(columns+1, startRow).getContents().trim();
   String c=arySheet.getCell(columns+2, startRow).getContents().trim();
   String d=arySheet.getCell(columns+3, startRow).getContents().trim().equals("產")?"1":"-1";

   String e=arySheet.getCell(columns+4, startRow).getContents().trim().equals("壽")?"1":"-1";
   String f=arySheet.getCell(columns+5, startRow).getContents().trim().equals("再")?"1":"-1";
   String g=arySheet.getCell(columns+6, startRow).getContents().trim().equals("集")?"1":"-1";
   String h=arySheet.getCell(columns+7, startRow).getContents().trim().equals("資")?"1":"-1";

   String i=arySheet.getCell(columns+8, startRow).getContents().trim().equals("快")?"1":"-1";
   String j=arySheet.getCell(columns+9, startRow).getContents().trim().equals("月")?"1":"-1";
   String k=arySheet.getCell(columns+10, startRow).getContents().trim().equals("季")?"1":"-1";
   String l=arySheet.getCell(columns+11, startRow).getContents().trim().equals("半年")?"1":"-1";

   String m=arySheet.getCell(columns+12, startRow).getContents().trim().equals("年")?"1":"-1";
   String n=arySheet.getCell(columns+13, startRow).getContents().trim().equals("年度")?"1":"-1";
   String o=arySheet.getCell(columns+14, startRow).getContents().trim().equals("1")?"1":"-1";
   String p=arySheet.getCell(columns+15, startRow).getContents().trim();

   String q=arySheet.getCell(columns+16, startRow).getContents().trim();
   String r=arySheet.getCell(columns+17, startRow).getContents().trim();
   if(arySheet.getColumns()>=19){
     s=arySheet.getCell(columns+18, startRow).getContents().trim().equals("1")?"1":"-1";
   }
   if(arySheet.getCell(0, startRow).getType()==CellType.EMPTY||!arySheet.getCell(0, startRow).getContents().trim().matches("[a-z]?\\d{8,10}")||!a.matches("[u4e00-u9fa5]+")){
    continue;
   }

   if(c.equals("負債")){
    c="1";
    roleId="1002";
   }
   if(c.equals("權益")){
    c="2";
    roleId="1002";
   }
   if(c.equals("損益")){
    c="3";
    roleId="1002";
   }
   if(c.equals("現金流")){
    c="4";
    roleId="1002";
   }
   if(c.equals("資產")){
    c="5";
    roleId="1002";
   }
   if(c.equals("資金")){
    c="6";
    roleId="1002";
   }
   if(c.equals("統計")){
    c="7";
    roleId="1003";
    if(f.equals("再")){
     roleId="1005";
    }
    if(a.substring(0, 4).matches("\\d{4}")){
     if(6118<=Integer.parseInt(a.substring(0, 4))&&Integer.parseInt(a.substring(0, 4))>=6121&&Integer.parseInt(a.substring(0, 4))==6151){
      roleId="1004";
     }
    }
   }
   if(roleId.equals("0")){
    System.out.println(startRow+"驗證錯誤!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
   }
   if(p.equals("0")||p.equals("1")||p.equals("2")||p.equals("9")){
    pp=p;
   }else{
    pp="-1";
   }

   if(r.equals("總公司報送")){
    r="1";
   }
   if(r.length()==0){
    r="3";
   }

   ib=new IndexBean();
   ib.setInfoID(a);
   ib.setDefName(b);
   ib.setDefType(c);
   ib.setRole_Id( Integer.parseInt(roleId));
   ib.setCompanyWealth(d);
   ib.setCompanyLife(e);
   ib.setCompanyReinsurance(f);
   ib.setCompanyGroup(g);
   ib.setCompanyAssets(h);
   ib.setReportF(i);
   ib.setReportM(j);
   ib.setReportQ(k);
   ib.setReportH(l);
   ib.setReportY(m);
   ib.setReportN(n);
   ib.setIfCollect(Integer.parseInt(o));
   ib.setIfGroup(Integer.parseInt(pp));
   ib.setRptCode(q);
   ib.setLevels(r);
   ib.setIfReport(s);
   ib.setOperate("利安寧");
   ib.setOprDate( new Date( new java.util.Date().getTime()));
   ib.setRemark("待寫");

   int cou=b.lastIndexOf("－");
   if(cou!=-1){
    String paterstring=b.substring(0, cou).trim();
   L2: for(int ii=4;ii<cell1.length;ii++){
     if(paterstring.equalsIgnoreCase(cell1[ii].getContents().trim())){
      String scell=arySheet.getCell(0, cell1[ii].getRow()).getContents().trim();
      ib.setParentId(scell);
      break L2;
     }
     else{
      ib.setParentId(a);
     }
    }
      }else{
      ib.setParentId(a);
      }
   list.add(ib);
  }
  return list;
}
/**
*把list中的bean插入到數據庫
* @param list
* @return beanloon是判斷是否全都讀入到數據庫中
* @author 利安寧
* @version 1.0 2008.5.20
* @throws Exception
*/
private boolean loadDataToDb(List list) throws Exception {
  Connection conn = null;
  PreparedStatement pstm = null;
  int [] r ;
  int count=0;
  try {
   conn = JdbcUtil.getConnection();
   String sql = "insert into ins_info_def_temp(info_id,def_name,parent_id,role_id ,def_type ,company_wealth ,company_life,company_reinsurance,company_group ,company_assets ,report_f,report_m,report_q,report_h,report_n ,report_a,if_collect,if_group,rpt_code,levels,if_report,def_flag,operate,opr_date,remark) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
   pstm = conn.prepareStatement(sql);
   Iterator it = list.iterator();
   while (it.hasNext()) {
    count++;
    IndexBean ib = (IndexBean) it.next();
    pstm.setString(1,ib.getInfoID());
    pstm.setString(2,ib.getDefName());
    pstm.setString(3,ib.getParentId());
    pstm.setInt(4,ib.getRoleId());
    pstm.setString(5, ib.getDefType());
    pstm.setString(6,ib.getCompanyWealth());
    pstm.setString(7,ib.getCompanyLife());
    pstm.setString(8,ib.getCompanyReinsurance());
    pstm.setString(9,ib.getCompanyGroup());
    pstm.setString(10,ib.getCompanyAssets());
    pstm.setString(11,ib.getReportF());
    pstm.setString(12,ib.getReportM());
    pstm.setString(13,ib.getReportQ());
    pstm.setString(14,ib.getReportH());
    pstm.setString(15,ib.getReportY());
    pstm.setString(16,ib.getReportN());
    pstm.setInt(17,ib.getIfCollect());
    pstm.setInt(18,ib.getIfGroup());
    pstm.setString(19,ib.getRptCode());
    pstm.setString(20,ib.getLevels());
    pstm.setString(21,ib.getIfReport());
    pstm.setString(22,"1");
    pstm.setString(23,ib.getOperate());
    pstm.setDate(24,ib.getOprDate());
    pstm.setString(25,ib.getRemark());
//    System.out.println(ib.getInfoID());
    pstm.executeUpdate();
   }
   conn.commit();
  } catch (SQLException e) {
   e.printStackTrace();
   conn.rollback();
  } finally {
   try {
    conn.close();
   } catch (SQLException e) {
    e.printStackTrace();
    return false;
   }
  }
  return true;
}
/**
*把用戶要求的記錄插入到數據庫中，如：產、壽、再、集、資。
* @param str
* @return beanloon是判斷是否全都讀入到數據庫中
* @author 利安寧
* @version 1.0 2008.6.6
* @throws Exception
*/
public boolean insertByName(String str) throws Exception{
  Connection conn = null;
  Statement pstm = null;
  Statement pstm1 = null;
  String delsql=null;
  String insertsql=null;
  String deletesql=null;
  try {
   conn = JdbcUtil.getConnection();
   if(str.equals("all")){
    delsql="delete from ins_info_def where info_id in (select info_id from ins_info_def_temp ) ";
    insertsql = "insert into ins_info_def select * from ins_info_def_temp";
   }
   if(str.equals("產")){
    delsql="delete from ins_info_def where info_id in (select info_id from ins_info_def_temp where COMPANY_WEALTH='1') ";
    insertsql = "insert into ins_info_def select * from ins_info_def_temp where COMPANY_WEALTH='1' ";
   }
   if(str.equals("壽")){
    delsql="delete from ins_info_def where info_id in (select info_id from ins_info_def_temp where COMPANY_LIFE='1') ";
    insertsql = "insert into ins_info_def select * from ins_info_def_temp where COMPANY_LIFE='1' ";
   }
   if(str.equals("再")){
    delsql="delete from ins_info_def where info_id in (select info_id from ins_info_def_temp where COMPANY_REINSURANCE='1') ";
    insertsql = "insert into ins_info_def select * from ins_info_def_temp where COMPANY_REINSURANCE='1' ";
   }
   if(str.equals("集")){
    delsql="delete from ins_info_def where info_id in (select info_id from ins_info_def_temp where COMPANY_GROUP='1') ";
    insertsql = "insert into ins_info_def select * from ins_info_def_temp where COMPANY_GROUP='1'";
   }
   if(str.equals("資")){
    delsql="delete from ins_info_def where info_id in (select info_id from ins_info_def_temp where COMPANY_ASSETS='1') ";
    insertsql = "insert into ins_info_def select * from ins_info_def_temp where COMPANY_ASSETS='1'";
   }
   pstm = conn.createStatement();
   pstm.executeUpdate(delsql);
   pstm.executeUpdate(insertsql);
   deletesql="delete ins_info_def_temp";
   pstm1=conn.createStatement();
   pstm1.executeUpdate(deletesql);
   conn.commit();
  } catch (SQLException e) {
   conn.rollback();
   e.printStackTrace();
  } finally {
   try {
    conn.close();
   } catch (SQLException e) {
    e.printStackTrace();
    return false;
   }
  }
  return true;
}

//測試代碼
public static void main(String args[]){
   FormulaLoad2Db fl=new FormulaLoad2Db();
   System.out.println(new java.util.Date());
   List list=fl.readExcelContext(filePath);
   System.out.println(list.size());
   System.out.println(new java.util.Date());
   System.out.println("完");
}
}