Shulou(Shulou.com)06/01 Report--

In order to import Excel into the database and write this program, I would like to explain some ideas.

Because the field type and length, as well as the number of fields are unknown when importing the database, a general field type is used for import. Here, text is used, and you can customize the field name and type as needed. This is just a simple example. If you often encounter such problems, I suggest writing a configuration file that imports the properties of the table you want to see (xml or property file is fine), which makes it more flexible.

There is a better way to save the Excel file to be imported as a xml file

It will become xml to database, and it will be easy to import.

If you don't understand anything, you can refer to: fully mining the usage of Java Excel API

Sample code for importing msyql from an Excel table

Import java.io.*

Import java.sql.Connection

Import java.sql.DriverManager

Import java.sql.PreparedStatement

Import java.sql.ResultSet

Import java.sql.SQLException

Import java.sql.Statement

Import jxl.*

Public class test {

Static String createTableSql= ""; / / create the sql of the database

Static String colType= "TEXT"; / / Field type

Static String key= "id"; / / Primary key

Static String charSet= "utf8"; / / Table character type

Static String ENGINE= "InnoDB"; / / Table type

Static String tableName= "tempExcelToMysql"; / / Table name

Static String colName= "col"; default field name

Static Connection conn = null

Public static void main (String args []) {

Try

{

/ / build Workbook object, read-only Workbook object

/ / create a Workbook directly from a local file

/ / create a Workbook from the input stream

System.out.println ("start load file-")

InputStream is = new FileInputStream ("E:/users.xls"); / / create input

Jxl.Workbook rwb = Workbook.getWorkbook (is)

Sheet rs = rwb.getSheet (0); / / read the first sheet

Int colNum=rs.getColumns (); / / number of columns

Int rowNum=rs.getRows (); / / rows

System.out.println ("colNum rowNum-" + rowNum+ "," + colNum)

System.out.println ("start create base-")

GetConntion ()

String tableSql=getCreateTableSql (rowNum,colNum)

Statement st=conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE)

St.execute (tableSql)

St.close ()

System.out.println ("create base end -")

String sql=getColName (rowNum,colNum)

PreparedStatement ps=null

String strValue= ""

Ps=conn.prepareStatement (sql)

For (int iTuno Bandi)

StrValue= ""

For (int juni0witj

Cell c = rs.getCell (j, I)

StrValue=c.getContents ()

Ps.setString (jacks 1 ~ (?)

}

Ps.addBatch ()

}

Ps.executeBatch ()

Conn.commit ()

If (psorimetric null) {

Ps.close ()

}

System.out.println ("insert end-")

Close ()

}

Catch (Exception e)

{

E.printStackTrace ()

}

}

Static String getCreateTableSql (int rowNum,int colNum)

{

/ / can be made into a configurable file

CreateTableSql= "create table" + tableName+ "(`" + key+ "` bigint (12) NOT NULL auto_increment,"

String temp= ""

For (int juni0witj

Temp=temp+ "`" + colName+j+ "`" + colType+ "DEFAULT NULL,"

}

CreateTableSql=createTableSql+ "" + temp+ "PRIMARY KEY (`" + key+ "`)" +

") ENGINE=" + ENGINE+ "DEFAULT CHARSET=" + charSet+ ";"

Return createTableSql

}

Static String getColName (int rowNum,int colNum)

{

/ / can be made into a configurable file

String colSql= ""

String colValue= ""

For (int juni0witj

ColSql=colSql+ "`" + colName+j+ "`,"

ColValue=colValue+ "+"?

}

Return "insert into" + tableName+ "(" + colSql.substring (0rect colSql.lastIndexOf (",")) + ") values (" + colValue.substring (0rect Value.lastIndexOf (",")) + ")"

}

Static void getConntion ()

{

Try {

String driver_class = "com.mysql.jdbc.Driver"

String connection_url = "jdbc:mysql://localhost:3306/webserve?useUnicode=true&characterEncoding=utf-8"

String user_name = "root"

String db_password = "123"

Class.forName (driver_class)

Conn = DriverManager.getConnection (connection_url, user_name,db_password)

} catch (Exception e) {

E.printStackTrace ()

}

}

Static void close ()

{

If (connexual null) {

Try {

Conn.close ()

} catch (SQLException e) {

/ / TODO Auto-generated catch block

E.printStackTrace ()

}

}

}

}

[@ more@]

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.