From Excel to importing MYSQL database
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@]
