JAVA读取excel例子（可直接用）

package com.abc.server.testcase; import java.io.File; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.List; import java.util.Properties; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import java.util.ArrayList; import com.abc.server.database.SQLExecute; import com.abc.server.properties.LogUtils; import org.apache.log4j.Logger; /** * <p>Description: [从excel中读取数据并插入到表中的测试用例]</p> * <p>Copyright:CopyRight(C) 2009</p> * <p>Time:2009-6-26</p> * @author <a href="mailto: [email protected]">冯建孟</a> * @version 4.0 */ public class TestTransCase { private static Logger logger; static { logger = LogUtils.getLogger(com.abc.server.testcase.TestTransCase.class .getName()); } /** * * <p>Discription:[连接数据库]</p> * @return * @throws SQLException * @author:[冯建孟] * @update:[日期2009-6-26] [冯建孟][无变更] */ public static Connection getConnection() throws SQLException { Connection conn = null; try { String DBDriver = "com.sybase.jdbc3.jdbc.SybDriver"; String DBUser = "amlsdbo"; String DBPassword = "amlsdbop"; String DBUrl = "jdbc:sybase:Tds:10.232.26.18:4110/Ts_AMLS_IQSRV"; Class.forName(DBDriver); Properties myprop = System.getProperties(); myprop.setProperty("user", DBUser); myprop.setProperty("password", DBPassword); conn = DriverManager.getConnection(DBUrl, myprop); conn.setTransactionIsolation(1); } catch (SQLException e) { e.printStackTrace(); throw e; } catch (Exception e) { e.printStackTrace(); } return conn; } /** * * <p>Discription:读取EXCEL文件的内容</p> * @param strFilePathAndFileName * @author:冯建孟 * @update:[日期2009-06-25] [冯建孟][无变更] */ public List readFile(String strFilePathAndFileName) throws Exception { Workbook workBook = null; /** 保存行数据的列表 */ List listRows = new ArrayList(); /** 保存列数据的列表 */ List listCols = null; boolean isNull = false; try { /** 根据文件的名称,获取文件对象 */ workBook = Workbook.getWorkbook(new File(strFilePathAndFileName)); /** 取得每一个sheet中的数据 */ Sheet sheet = workBook.getSheet(0); /** 网格对象 */ Cell cell = null; /** 获取列总数 */ int columnCount = sheet.getColumns(); /** 获取行总数 */ int rowCount = sheet.getRows(); for (int i = 1; i < rowCount; i++) { /** 保存列值 */ listCols = new ArrayList(); for (int j = 0; j < columnCount; j++) { /** * 这里的两个参数，第一个表示列的，第二个表示行. */ cell = sheet.getCell(j, i); String strColumnValue = cell.getContents(); listCols.add(strColumnValue); } if (!isNull) { /** 将每一行的所有列值保存到行列表(List)中 */ listRows.add(listCols); } isNull = false; } /** 关闭文件 */ workBook.close(); } catch (Exception e) { e.printStackTrace(); throw new Exception("没有找到要导入的文件!"); } return listRows; } /** * * <p>Discription:[向数据库表QTBR_TESTCASE_TRANS中插入数据]</p> * @param conn * @param params * @return * @throws SQLException * @author:[冯建孟] * @update:[日期2009-6-26] [冯建孟][无变更] */ public void insertData(Connection conn, Object[] params) throws SQLException { String stringParamBefore = ""; String stringParamAfter = ""; stringParamBefore = "TRANSKEY,TRANS_DT,PARTY_ID,PARTY_NAME,ACCT_NUM,OVERAREA_IND,CREDIT_TYP,CREDIT_DT,CASH_TRANS_FLAG," + "GAT_PAY_FLAG,CURR_CD,AMT,USD_AMT,PARTY_CLASS_CD,ACCT_CORE_NO,ACCT_ORGAN_NO,SUBJECT,ACCT_STATUS_CD," + "CLOSE_DT,LAST_DT,OPP_PARTY_ID,OPP_PARTY_CLASS_CD,OPP_ORG_COUNTRY,OPP_ORG_AREAR,CURRENCY_CD"; //拼SQL语句中的条件 for (int i = 0; i <= 24; i++) { if (i == 11 || i == 12) { stringParamAfter += (String) params[i] + ","; } else if (i == 24) { stringParamAfter += "'" + (String) params[i] + "' "; } else { stringParamAfter += "'" + (String) params[i] + "',"; } } String stringInsertSql = "INSERT INTO QTBP_STD_TRANSACTION_XA(" + stringParamBefore + ") VALUES(" + stringParamAfter + ")"; logger.debug(stringInsertSql); try { SQLExecute.exeSql(conn, stringInsertSql); conn.commit(); } catch (SQLException sqle) { sqle.printStackTrace(); throw sqle; } } /** * * <p>Discription:[主函数入口]</p> * @param args * @author:[冯建孟] * @update:[日期2009-6-26] [冯建孟][无变更] */ public static void main(String args[]) { TestTransCase tc = new TestTransCase(); List readDataList = new ArrayList(); Connection conn = null; File file = new File("."); // 获取excel文件中每一列的值 try { conn = getConnection(); String stringPath = file.getCanonicalPath(); System.out.println(stringPath); readDataList = tc.readFile(stringPath + "\\标准交易流水表测试用例.xls"); //参数组 Object[] params = new Object[25]; for (int i = 0; i < readDataList.size(); i++) { List colList = (List) readDataList.get(i); for (int j = 0; j <= 24; j++) { params[j] = ((String) colList.get(j + 2) == null) ? "" : (String) colList.get(j + 2); } //调用方法向数据库中插入数据 tc.insertData(conn, params); } } catch (Exception e) { e.printStackTrace(); } } }