教你如何读取Excel数据并导入DB2数据库

最近项目需要读取Excel，将数据导入数据库保存，最终可以用报表读取数据库并显示，比较简单。查了一下两者的资料，觉的也无所谓，我需要的功能比较简单，只要能读取Excel数据就行，最后决定使用POI，比较信任Apache。

　　下面付的代码是一个Demo，功能点有两个：一就是POI读取Excel，二是DB2数据库的连接和SQL执行。

　　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"; //加载驱动程序以连接数据库 try { //添加类库驱动包db2jcc.jar和db2jcc_license_cu.jar Class.forName("com.ibm.db2.jcc.DB2Driver"); url = "JDBC:db2://192.168.0.1:50000/dbname"; //添加类库驱动包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 (ClassNotFoundException cnfex) { System.err.println("装载JDBC驱动程序失败。"); cnfex.printStackTrace(); return false; } //捕获连接数据库异常 catch (SQLException sqlex) { System.err.println("无法连接数据库"); 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("数据导入成功"); else System.out.println("数据导入失败"); } else{ System.out.println("数据库连接失败"); } } }