How to manipulate Excel read data import DB2 database

Recent projects need to read Excel, the data into the database to save, and finally can use the report to read the database and display, relatively simple. Check the data of both, feel also indifferent, I need the function is relatively simple, as long as can read Excel data on line, finally decided to use POI, more trust Apache

The following code is a demo, the function point has two: one is POI read Excel, the second is DB2 database connection and SQL execution.

Now the problem is to do so the code is too dead, for an Excel table to write a special conversion class, too cumbersome to consider how to implement a common conversion class.

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 driver to connect to database
try {
Add 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 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 ();
}
Capture Load Driver exception
catch (ClassNotFoundException Cnfex) {
SYSTEM.ERR.PRINTLN ("Load JDBC driver failed.) ");
Cnfex.printstacktrace ();
return false;
}
Catching Connection Database 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.executeupdate (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 import succeeded");
Else
SYSTEM.OUT.PRINTLN ("Data import Failed");
}
else{
SYSTEM.OUT.PRINTLN ("Database connection failed");
}
}
}