Table content in Word BULK INSERT Database

I. SUMMARY OF CONTENTS

1, download Jacob.jar, put Jacob's dll into the SYSTEM32, make sure the computer installed Word program;

2, by: file[] Traverse a specific folder, Jacob reads the contents of the table in Word, encapsulates it into ArrayList, and then inserts the database in bulk

Second, the core code

1,words.java; traverse the folder to remove the contents of the Word table to ArrayList

package main.java.utils;import java.io.file;//is used to traverse all files under the folder Import java.util.arraylist;import  java.util.iterator;import java.util.list;import main.java.cus;//a Pojo general class import  com.jacob.activex.activexcomponent;import com.jacob.com.dispatch;import com.jacob.com.variant;// Import test.wordbean;public class words { // word Document  private static  dispatch doc; // word Running Program Objects  private ActiveXComponent word; //  All Word document collections  private Dispatch documents; //  selected ranges or insertion points  private Dispatch  selection; private boolean saveonexit = true;  private arraylist  alist=null; private arraylist<cus> al1=null; private cus cus=null;   public words ()  throws Exception {  if  (word == null)  {   word = new&nbsP Activexcomponent ("Word.Application");   word.setproperty ("Visible",  new variant (false));  //  Invisible Open Word  word.setproperty ("AutomationSecurity",  new variant (3));  //   Disable Macros   }  if  (documents == null)   documents =  Word.getproperty ("Documents"). Todispatch ();  }//Create a new Word document  public void createnewdocument ( )  {  doc = dispatch.call (documents,  "Add"). Todispatch ();   selection  = dispatch.get (word,  "Selection"). Todispatch ();  } //open an existing document  public void  opendocument (String docpath)  {  createnewdocument ();  doc =  Dispatch.call (documents,  "Open",  docpath). Todispatch ();  selection =  Dispatch.get (word,  "Selection"). Todispatch ()  } //get the specified cell data  public String  Gettxtfromcell (int tableindex,  int cellrowidx, int cellcolidx)  {  //  All Forms   dispatch tables  = dispatch.get (doc,  "Tables"). Todispatch ();   //  table to fill    Dispatch table = dispatch.call (tables,  "Item",  new variant (TableIndex)). Todispatch ();   dispatch rows = dispatch.call (table,  "Rows"). ToDispatch ();   dispatch columns = dispatch.call (table,  "columns"). Todispatch ();   Dispatch  cell = dispatch.call (table,  "Cell",  new variant (CELLROWIDX), New Variant ( CELLCOLIDX). Todispatch ();   dispatch range=dispatch.get (cell, "Range"). Todispatch ();//   system.out.println (Dispatch.get (Range, "Text"). toString ());   dispatch.call (cell,  "select") ;  string ret =  "";   ret = dispatch.get (selection,  "Text"). ToString ();   ret = ret.substring (0, ret.length ()  - 2); //  remove the last carriage return;  return ret;  } //off  public void closedocumentwithoutsave ()  {  if  (doc !=  null)  {   dispatch.call (doc,  "Close",  new variant (false));    doc = null;  } }  //Close all Apps  public void close ()  { //closedocument (); if  (word != null)  { dispatch.call (word,  " Quit "); word = null; } selection = null; documents = null;  }  //traverse all file paths under the folder  public  arraylist getfilepath (File filedir)   Throws exception{  file[] file = filedir.listfiles ();  alist=new  ArrayList ();   for (int i=0; i<file.length; i++) {           Get absolute path    string filepath=file[i].getabsolutepath ();//    System.out.println (File[i].getabsolutepath ());    alist.add (filepath);//    System.out.println (Alist.get (i));    if (File[i].isdirectory ()) {    try{     getfilepath (File[i]);     }catch (exception e) {}    }  }  return alist; }  //to traverse all filenames under folder  public   Arraylist getfilename (File filedir)  throws exception{  alist=new arraylist ( );   file[] file = filedir.listfiles ();   for (int i=0; i< file.length; i++) {   //Gets the file name    string filename=file[i].getname (). SUBSTRING (0, file[i].getname (). IndexOf ("."));    alist.add (filename);//   system.out.println (Alist.get (i));    if (file[I].isdirectory ()) {    try{    getfilename (file[i]);     }catch (exception e) {}   }  }  return alist; } // Get the number of files under Folder  public  int getfilecount (File filedir)  throws Exception{   file[] file = filedir.listfiles ();  int filecount=file.length;   return filecount; }  //get all the valid content in the table  public ArrayList<Cus>  Getfilecontent ()  throws exception{   file myfiledir = new file ("C : \\list ");   file[] file = myfiledir.listfiles ();    al1=new  Arraylist<cus> ();   cus=new cus ();    for  (int i =  0; i < file.length; i++)  {  system.out.println (file.length);   if  (!file[i].ishidDen ())  {    string filepaths=getfilepath (Myfiledir). get (i). toString ();   string filenames=getfilename (Myfiledir). get (i). toString ();   opendocument (filepaths);   //  All Forms   dispatch tables = dispatch.get (doc,  "Tables"). Todispatch ();   //  Gets the 1th table   dispatch table = dispatch.call (tables ,  "Item",  new variant (1)). Todispatch ();  //  Get table values (fixed 10 rows)        cus=new cus ();       cus.setid (filenames);       cus.setprovince (Gettxtfromcell (1, 1, 2));       Cus.setprovince_manager (Gettxtfromcell (1, 2, 2));       cus.setcustom ( Gettxtfromcell (1, 3, 2));       cus.setbusiness (GetTxtFromCell (1,  4,&NBSP;2));     &Nbsp; cus.setreceiver (Gettxtfromcell (1, 5, 2));       cus.setbill_ Info (Gettxtfromcell (1, 6, 2));       cus.setinvoice_info (GetTxtFromCell (1, 7, 2));       cus.setcus_info (Gettxtfromcell (1, 8, 2));       cus.setgoods (Gettxtfromcell (1, 9, 2));       Cus.setrequirement (Gettxtfromcell (1, 10, 2));       Closedocumentwithoutsave ();} Else{  system.out.println ("Please close all Word documents and end the process!") ");   break;}       al1.add (cus);    }           close ();          return al1;  } //test Method// public static void main (String[] args) throws Exception{//   jtest word&nBsp;= new jtest (); //  arraylist<cus> list=new arraylist<cus> () ;//  list=word.getfilecontent ();//  iterator<cus> it=list.iterator ();//   while  (It.hasnext ())  {//system.out.println (It.next ());////}//} }

2, inserting data DAO

package main.java.dao;import java.util.arraylist;import java.util.iterator;import  Main.java.cus;import main.java.utils.words;public class dao {private words words =null;private arraylist<cus> al=null;private cus cus=null;//Creating a database Public void  init ()  {dbaccess db = new dbaccess (); if (Db.createconn ())  {    String sql =   create table CUSTOM  (   +             " id varchar  ( 30 )  primary  key,  " +"  Province varchar  ( 50 ),  "+"  province_manager  varchar,  ( 50 ) " +"  Custom varchar  ( 50 ),  "+"   business varchar  ( 50 ),  "+"  Receiver varchar  ( 50 ),  " + " Bill_info varchar  ( 200),  "+"  Invoice_info varchar  ( 200 ),  "+"  Cus_info  varchar  ( 50 ),  "+"  Goods varchar  ( 50 ),  "+"   requirement varchar  ( 100 )) ";d b.update (SQL);d b.closestm ();d b.closeconn ();}} BULK Insert a Word table into a database Public void insertwordtodb ()  throws exception{dbaccess db =  new dbaccess (); Words=new words ();al=new arraylist<cus> (); Al=words.getFileContent () ; Iterator<cus> it=al.iterator ();while  (It.hasnext ())  {cus=new cus (); Cus=it.next (); if (Db.createconn ())  {    String sql =  "Insert into custom  values (  '   '                + cus.getid ()  +  "', '"                + cus.getprovince ()  +  "', ' "              + cus.getprovince_ Manager ()  +  "', '"                + cus.getcustom ()  +  "', '"                + cus.getbusiness ()  +  "', '"                + cus.getreceiver ()  +  "', '"                + cus.getbill_info ()  +  "', '"                + cus.getinvoice_info ()  +  "', '"               +  Cus.getcus_info ()  +  "', '"                + cus.getgoods ()  +  "', '"               +  Cus.getrequirement ()  +  "')";d b.update (SQL);d b.closestm ();d b.closeconn ();}}}

3, additions and deletions to seal the installation

package main.java.dao;import java.sql.connection;import java.sql.drivermanager;import  java.sql.resultset;import java.sql.sqlexception;import java.sql.statement;public class  dbaccess {private string drv =  "Org.h2.Driver";p rivate string url =   "Jdbc:h2:file:~/cus; Auto_server=true ";p rivate string usr = " sa ";p rivate string pwd = " "; Private connection conn = null;private statement stm = null;private  resultset rs = null;public boolean createconn ()  {boolean b =  false;try {class.forname (DRV). newinstance (); Conn = drivermanager.getconnection (url,  USR, PWD); b = true;}  catch  (sqlexception e)  {} catch  (classnotfoundexception e)  {}  catch  (instantiationexception e)  {} catch  (illegalaccessexception e)  {}return b;} Public boolean update (String sql)  {boolean b = false;try {stm =  conn.createstatement (); Stm.execute (sql); b = true;}  catch  (exception e)  {system.out.println (e.tostring ());} Return b;} Public void query (String sql)  {try {stm = conn.createstatement (); rs =  stm.executequery (SQL);}  catch  (exception e)  {}}public boolean next ()  {boolean b =  False;try {if (Rs.next ()) b = true;}  catch  (Exception e)  {}return b;} Public string getvalue (String field)  {string value = null;try {if (rs!= NULL) value = rs.getstring (field);}  catch  (Exception e)  {}return value;} Public void closeconn ()  {try {if  (conn != null) Conn.close ();}  catch  (Sqlexception e)  {}}public void closestm ()  {try {if  (stm != null) stm.close () ;}  catch  (sqlexception e)  {}}public void closers ()  {try {if  (RS  != null) Rs.close ();}  catch  (sqlexception e)  {}}public connection getconn ()  {return conn;} Public void setconn (Connection conn)  {this.conn = conn;} Public string getdrv ()  {return drv;} Public void setdrv (String drv)  {this.drv = drv;} Public string getpwd ()  {return pwd;} Public void setpwd (STRING PWD)  {this.pwd = pwd;} Public resultset getrs ()  {return rs;} Public void setrs (Resultset rs)  {this.rs = rs;} Public statement getstm ()  {return stm;} Public void setstm (statement stm)  {this.stm = stm;} Public stRing geturl ()  {return url;} Public void seturl (String url)  {this.url = url;} PUBLIC STRING GETUSR ()  {return usr;} PUBLIC VOID SETUSR (STRING USR)  {this.usr = usr;}}

4,pojo class

package main.java;public class cus {private string id;private string  province;private string province_manager;private string custom;private string  Business;private string receiver;private string bill_info;private string invoice _info;private string cus_info;private string goods;private string requirement; Public string getid ()  {return id;} Public void setid (String id)  {id = id;} Public string getprovince ()  {return province;} Public void setprovince (string province)  {province = province;} Public string getprovince_manager ()  {return province_manager;} Public void setprovince_manager (String provincemanager)  {Province_manager =  Provincemanager;} Public string getcustom ()  {return custom;} Public void setcustom (String custom)  {custom = custom;} Public string getbusiness ()  {return business;} Public void setbusiness (string business)  {business = business;} Public string getreceiver ()  {return receiver;} Public void setreceiver (String receiver)  {receiver = receiver;} Public string getbill_info ()  {return bill_info;} Public void setbill_info (String billinfo)  {bill_info = billinfo;} Public string getinvoice_info ()  {return invoice_info;} Public void setinvoice_info (String invoiceinfo)  {invoice_info = invoiceinfo;} Public string getcus_info ()  {return cus_info;} Public void setcus_info (String cusinfo)  {cus_info = cusinfo;} Public string getgoods ()  {return goods;} Public void setgoods (string goods) &NBSp {goods = goods;} Public string getrequirement ()  {return requirement;} Public void setrequirement (string requirement)  {requirement = requirement;}}

Table content in Word BULK INSERT Database