Excel import database oracle-PHP source code

Excel import database oracle

1. read execl(only the execlof .xlscan be read, that is, only the version 03's can be read. if it is of the .xlsx type, manually convert it.

Import java. io. fileInputStream; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. SQLException; import java. SQL. statement; import org. apache. poi. hssf. usermodel. HSSFCell; import org. apache. poi. hssf. usermodel. HSSFSheet; import org. apache. poi. hssf. usermodel. HSSFWorkbook;/*** @ author: gzh * @ version creation Time: 11:26:13 * class description: * 1. read execl(only .xls's execlcan be read, that is, only version 03's can be read. if it is a. xlsx type You can manually convert it to * 2. the jar package of the application is the jar package of the apache poi series class and the database connection package of * 3. ojdbc14.jar. **/Public class ExeclOperateMain {/*** method description: * @ param args * @ return void */public static void main (String [] args) throws Exception {ExeclOperateMain e = new ExeclOperateMain (); e. getExcel (); System. out. println ("Execl import completed! ");}/*** Method for connecting to the oracle database * you only need to modify the getConnection (" url "," username "," password ") parameter "); */public Connection conn () {try {// Step 1: load the JDBC driver Class. forName ("oracle. jdbc. driver. oracleDriver "); // Step 2: create a database Connection con = DriverManager. getConnection ("jdbc: oracle: thin: @ 192.168.1.202: 1521: orcl", "mdb", "mdb"); return con;} catch (ClassNotFoundException cnf) {System. out. println ("driver not found!: "+ Cnf); return null;} catch (SQLException sqle) {System. out. println ("cannot connect to the database:" + sqle); return null;} catch (Exception e) {System. out. println ("loading failed JDBC/ODBC driver. "); return null ;}}/*** read an excel table * @ throws Exception */public void getExcel () throws Exception {// create reference to an Excel workbook file HSSFWorkbook workbook = new HSSFWorkbook (new FileInputStream ("E: \ Workspaces \ pt-zt-v1.0 \ src \ test.xls ")); // create a reference to the worksheet. // In the Excel document, the default index of the first worksheet is 0, // read the upper left unit for (int I = 0; I <workbook. getNumberOfSheets (); I ++) {// cyclic sheet, 3 columns of if (workbook. getSheetAt (I ). getPhysicalNumberOfRows ()> 0) {// row, 6 rows HSSFSheet childSheet = workbook. getSheetAt (I); for (int rowi = 1; rowi <40000; rowi ++) {// | childSheet. getRow (rowi ). getCell (0 ). equals ("") | childSheet. getRow (rowi ). getCell (0) = null if (childSheet. getRow (rowi) = null) break; String cel L1 = this. publicExcel (childSheet. getRow (rowi ). getCell (0); if (cell1 = null) break; // for data of the double type, replace it with a string type, and take only an integer for string truncation. Cell1 = cell1.substring (0, cell1.length ()-2); // 2 String cell2 = this. publicExcel (childSheet. getRow (rowi ). getCell (1); // 111 String cell3 = this. publicExcel (childSheet. getRow (rowi ). getCell (2); // 422 // SQL String insert = "insert into pt_zt values ('" + cell3 + "', '"+ cell2 +"', '"+ cell1 +"') "; System. out. println ("SQL:" + insert); insert (insert );}}}} /*** execl data format conversion * @ param cell * @ return String */public String publicExcel (HSSFCell cell) {String value = null; switch (cell. getCellType () {case HSSFCell. CELL_TYPE_NUMERIC: value = "" + cell. getNumericCellValue (); break; case HSSFCell. CELL_TYPE_STRING: value = cell. getStringCellValue (); break; case HSSFCell. CELL_TYPE_BLANK:; break; default:} return value;}/*** you only need to input the insert SQL statement to insert data. * Example of SQL statement insertion: insert into pt_zt values ('sys _ users', 'user table'); * @ param insert statement * @ return int * @ throws SQLException */public int insert (String insert) throws SQLException {Connection conn = this. conn (); int re = 0; try {conn. setAutoCommit (false); // starts Statement sm = conn. createStatement (); re = sm.exe cuteUpdate (insert); // execute if (re <0) {// insert failed conn. rollback (); // roll back sm. close (); // close smconn. close (); // close the connection return re;} conn. commit (); // Insert and submit normally System. out. println ("A row has been inserted normally, SQL =" + insert); sm. close (); conn. close (); return re;} catch (Exception e) {e. printStackTrace ();} conn. close (); return 0 ;}}