你是否遇到过需要从Excel中读取数据呢?将数据导入数据库保存,最终可以用报表读取数据库并显示,比较简单。查了一下两者的资料,觉的也无所谓,我需要的功能比较简单,只要能读取Excel数据就行,最后决定使用POI,比较信任apache,使用果然很简单,上网找了个源码,Copy过来稍微改改就能用了。
下面附的代码是一个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.jarClass.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 programreturn 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("数据导入成功"); elseSystem.out.println("数据导入失败"); }else{System.out.println("数据库连接失败");}}} |