Question

如何操作Excel读取数据导入DB2数据库

Answer 1

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("数据库连接失败"); } } }