Recently, the project needs to read the Excel file, import the data to the database, and save the data. In the end, you can use the report to read the database and display the data, which is relatively simple. I checked the information about the two, and it didn't matter. I needed a simple function. As long as I could read the Excel Data, I decided to use POI and trust apache. It was really easy to use, I found a source code on the Internet, and copied it and changed it slightly to use it.
The following code is a Demo with two functions: POI reading Excel, and DB2 database connection and SQL Execution.
Import Java. io. FileInputStream;
Import java. io. IOException;
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. HSSFRow;
Import org. apache. poi. hssf. usermodel. HSSFSheet;
Import org. apache. poi. hssf. usermodel. HSSFWorkbook;
Import org. apache. poi. poifs. filesystem. POIFSFileSystem; Public class POITest {
Private static Connection conn = null;
Private static Statement stmt = null;
Private static boolean connectDB2 (){ String url = "";
String username = "username ";
String password = "password "; // Load the driver to connect to the database
Try {
// Add the class library driver package db2jcc. jar and db2jcc_license_cu.jar
Class. forName ("com. ibm. db2.jcc. DB2Driver ");
Url = "JDBC: db2: // 192.168.0.1: 50000/dbname ";
// Add the class library driver package db2java. jar
// Class. forName ("com. ibm. db2.jdbc. app. DB2Driver"). newInstance ();
// Url = "jdbc: db2: njtcdata ";
Conn = DriverManager. getConnection (url, username, password );
Stmt = conn. createStatement ();
}
// Catch exceptions in the driver Loading
Catch (ClassNotFoundException cnfex ){
System. err. println ("failed to load the JDBC driver. ");
Cnfex. printStackTrace ();
Return false;
}
// Catch database connection exceptions
Catch (SQLException sqlex ){
System. err. println ("unable to connect to Database ");
Sqlex. printStackTrace ();
// System. exit (1); // terminate program
Return false;
}
Return true;
} Private static boolean readExcelToDB2 (){
POIFSFileSystem fs = null;
HSSFWorkbook wb = null;
Try {
Fs = new POIFSFileSystem (new FileInputStream ("c: \ test.xls "));
Wb = new HSSFWorkbook (fs );
} Catch (IOException e ){
E. printStackTrace ();
Return false;
}
HSSFSheet sheet = wb. getSheetAt (0 );
HSSFRow row = null;
HSSFCell cell = null;
String name = "";
Int id = 0;
Int rowNum, cellNum;
Int I;
RowNum = sheet. getLastRowNum ();
For (I = 0; I <= rowNum; I ++ ){
Row = sheet. getRow (I );
// CellNum = row. getLastCellNum ();
Cell = row. getCell (short) 0 );
Name = cell. getStringCellValue ();
Cell = row. getCell (short) 1 );
Id = (int) cell. getNumericCellValue ();
String SQL = "insert into TEST (ID, NAME) values (" + id + ", '" + name + "')";
Try {
Stmt.exe cuteUpdate (SQL );
} Catch (SQLException e1 ){
E1.printStackTrace ();
Return false;
}
}
Return true;
} Public static void main (String [] args ){
If (connectDB2 () = true ){
If (readExcelToDB2 () = true)
System. out. println ("data imported successfully ");
Else
System. out. println ("data import failed ");
}
Else {
System. out. println ("database connection failed ");
}
}
}
|
