Use Java technology to import the Excel sheet content to the MySQL database

1. Add the poi jar package to the lib directory of the project.

2. Excel file directory: D: // excel.xls

3. database field: num1 num2 num3 num4 num5 num6

4. Database Name: blog

5. Table Name: Test

6. Writing Class: string method for connecting to MySQL, insertion method, and entity class

Import java. Io. fileinputstream;
Import java. Io. filenotfoundexception;
Import java. Io. ioexception;
Import org. Apache. commons. Logging. log;
Import org. Apache. commons. Logging. logfactory;
Import org. Apache. Poi. hssf. usermodel. hssfcell;
Import org. Apache. Poi. hssf. usermodel. hssfrow;
Import org. Apache. Poi. hssf. usermodel. hssfsheet;
Import org. Apache. Poi. hssf. usermodel. hssfworkbook;
 
Public class testexcel {
// Output information of the record class
Static log = logfactory. getlog (testexcel. Class );
// Obtain the Excel document path
Public static string filepath = "D: // excel.xls ";
Public static void main (string [] ARGs ){
Try {
// Create a reference to an Excel Workbook File
Hssfworkbook wookbook = new hssfworkbook (New fileinputstream (filepath ));
// In the Excel document, the default index of the first worksheet is 0.
// The statement is: hssfsheet sheet = Workbook. getsheetat (0 );
Hssfsheet sheet = wookbook. getsheet ("sheet1 ");
// Obtain the number of all rows in the Excel file
Int rows = sheet. getphysicalnumberofrows ();
// Traverse rows
For (INT I = 0; I <rows; I ++ ){
// Read the upper left cell
Hssfrow ROW = sheet. getrow (I );
// The row is not empty.
If (row! = NULL ){
// Obtain all columns in the Excel file
Int cells = row. getphysicalnumberofcells ();
String value = "";
// Traverse Columns
For (Int J = 0; j <cells; j ++ ){
// Obtain the column Value
Hssfcell cell = row. getcell (j );
If (cell! = NULL ){
Switch (cell. getcelltype ()){
Case hssfcell. cell_type_formula:
Break;
Case hssfcell. cell_type_numeric:
Value + = cell. getnumericcellvalue () + ",";
Break;
Case hssfcell. cell_type_string:
Value + = cell. getstringcellvalue () + ",";
Break;
Default:
Value + = "0 ";
Break;
}
}
}
// Insert data into the MySQL database
String [] val = value. Split (",");
Testentity entity = new testentity ();
Entity. setnum1 (Val [0]);
Entity. setnum2 (Val [1]);
Entity. setnum3 (Val [2]);
Entity. setnum4 (Val [3]);
Entity. setnum5 (Val [4]);
Entity. setnum6 (Val [5]);
Testmethod method = new testmethod ();
Method. Add (entity );
}
}
} Catch (filenotfoundexception e ){
E. printstacktrace ();
} Catch (ioexception e ){
E. printstacktrace ();
}
}
}