记录Selenium,Spring,Maven的集成使用

·测试方法：

Method:1)　手动打开seleniumserver，然后用junit运行BaseExcelSheetTestCase类。

Method:2)　打开cmd，进入pom.xml所在文件夹，运行mvnclean integration-test命令。

·工作流程：(入口程序BaseExcelSheetTestCase)

1)根据Applicationcontext.xml，spring完成对象的初始化和注入工作。（如初始化SeleniumManager，Executor，Functions，ReportProcessor，BaseDaoImpl等）

2)SeleniumManager注入完成后根据参数启动selenium并打开测试网页。

3)Executor根据传入excel每行相应的action，调用Functions或者Selenium中的方法进行对应测试工作。

4)ReportProcessor将测试的结果记录下来并生成报表

5)BaseDaoImpl将每条测试结果插入数据库保存

6)CloseSelenium.

·　文件及路径介绍：（maven项目目录结构）

1)user.dir/src/main/java　　à 包含所有逻辑的源代码

2)user.dir/src/test/java     à  测试代码路径

3)user.dir/src/test/resourcesà Applicationcontext.xml、testcates/、jrxml/、reports/

4)user.dir/                          à  pom.xml

5)user.dir/bin                      à  class 文件

关于测试工具可以用TestNG或者JUnit.

source code:

---------------------------------------Maven中的测试类------------------------

package com.citivelocity.junit;

import junit.framework.TestCase;

import org.springframework.context.ApplicationContext;
importorg.springframework.context.support.ClassPathXmlApplicationContext;
import org.testng.annotations.Test;

import com.citivelocity.functions.SeleniumManager;

public class BaseExcelSheetTestCase extends TestCase {
 // Port is not currently in use
 private ApplicationContext context = newClassPathXmlApplicationContext(
   new String[]{ "applicationContext.xml" });

 @Override
 protected void setUp() {
 }

 @Override
 protected void tearDown() throws Exception{
  super.tearDown();
 }

 @Test
 public void testExcelSheet() {
  SeleniumManager testManager =(SeleniumManager) context
    .getBean("seleniumManager");
  testManager.execute();
  
 }
}

---------------------------------------------------------------------------------------

packagecom.citivelocity.functions;

importjava.io.FileNotFoundException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.TreeMap;

importorg.apache.commons.lang.StringUtils;

importcom.citivelocity.springJDBC.BaseDaoImpl;
import com.citivelocity.utils.ExcelTestCasesParser;
import com.citivelocity.utils.TestCaseResultEntity;
import com.citivelocity.utils.TestCaseStepEntity;
import com.thoughtworks.selenium.Selenium;
import com.thoughtworks.selenium.SeleniumException;

public class Executor {
 private final String LOCATOR_REGEX =".*\\(.*\\)";
 private final String RUNWHEN_BOTH = "BOTH";
 private String report;
 private String jrxml;
 private Functions functions;
 private Map<Integer,TestCaseStepEntity> testCaseStepEntityMap;
 private Map<String,String> locatorActionMap;
 private SeleniumManager seleniumManager;
 private ReportProcessor reportProcessor;
 private BaseDaoImpl baseDaoImpl;
 private Map<String,Method> seleniumMethodMap = newTreeMap<String, Method>();
 private Map<String,Method> functionMethodMap = newTreeMap<String, Method>();
 privateList<TestCaseResultEntity> dataList =newArrayList<TestCaseResultEntity>();

 publicExecutor() {
  functionMethodMap =getMethodMap(Functions.class);
  seleniumMethodMap =getMethodMap(Selenium.class);
  printAllMethods();
 }

 protected voidexecute(String strTestCaseFile) {

  testCaseStepEntityMap= ExcelTestCasesParser.generate(strTestCaseFile);
  System.out.println("Retreivingtestcases with size "
    +testCaseStepEntityMap.size());
  Iterator<Integer>it = testCaseStepEntityMap.keySet().iterator();
  long beforeTestRun =System.currentTimeMillis();
  while (it.hasNext()) {
   runTestCase(testCaseStepEntityMap.get(it.next()));
  }
  long endTestRun =System.currentTimeMillis();
  String duration =getDuration(beforeTestRun, endTestRun);
  try {
   // Generatereport
   Map params =new HashMap();
   StringexcelName = getReportName(seleniumManager
     .getStrTestCaseFile());
   params.put("Duration",duration);
   report =String.format(report, excelName);
   reportProcessor.generate(params,dataList, jrxml, report);
  } catch (FileNotFoundExceptione) {
   e.printStackTrace();
  }
  seleniumManager.seleniumClose();
 }

 private voidrunTestCase(TestCaseStepEntity testCase) {
  //System.out.println(testCase.toString());
  if(testCase.getRunWhen().equalsIgnoreCase(RUNWHEN_BOTH)
    ||seleniumManager.getIntExt().equalsIgnoreCase(
      testCase.getRunWhen())){
   StringelementLocator = fetchLocator(testCase.getElementLocator());
   String action= standardize(testCase.getAction().toLowerCase());
   System.out.println("-----------------elementLocator:"
     +elementLocator);
   System.out.println("-----------------action:" + action);
   String value= testCase.getValue();
   Method method= null;
   TestCaseResultEntityresultEntity;
   // *data forreport*
   String status= "Pass";
   String remark= "NA";
   try {
    if(functionMethodMap.containsKey(action)) {
     method= functionMethodMap.get(action);
     method.invoke(functions,
       createParameters(method,elementLocator, value));
    }else if (seleniumMethodMap.containsKey(action)) {
     method= seleniumMethodMap.get(action);
     method.invoke(seleniumManager.getSelenium(),
       createParameters(method,elementLocator, value));
    }else {
     status= "Fail";
     remark= "Invalid Action.";
     System.out.println("Error:invalid action for test step"
       +testCase.getStepNum() + ", " + action);
    }
   } catch(SeleniumException e) {
    //*data for report
    System.out
      .println("Iget the SeleniumException.----------------->"
        +testCase.getStepNum());
    status= "Fail";
    remark= e.getMessage();
   } catch(InvocationTargetException e) {
    System.out
      .println("Iget theInvocationTargetException.----------------->"
        +testCase.getStepNum()
        +"message: --"
        +e.getTargetException().getMessage());
    status= "Fail";
    remark= e.getTargetException().getMessage();
   } catch(Exception e) {
    System.out.println("Iget the Exception.----------------->"
      +testCase.getStepNum());
    status= "Fail";
    remark= e.getMessage();
   } finally{
    //preparedata for report
    resultEntity= new TestCaseResultEntity(testCase, status,
      remark);
    dataList.add(resultEntity);
    //Insert data into database
    
   }
  } else {
   System.out.println("Ignoretestcase for it is only used in "
     +testCase.getRunWhen() + " testing.");
  }

 }

 private StringfetchLocator(String s) {
  String elementLocator =s.trim();
  // if it is a function
  if(elementLocator.matches(LOCATOR_REGEX)) {
   String[]split = elementLocator.split("\\(|\\)");
   Stringfunction = split[0].toLowerCase();
   Stringlocator = split[1];
   locator =removeStartAndEnd(locator, new String[] { "'", """ });
   if(locatorActionMap.containsKey(function)) {
    elementLocator= String.format(locatorActionMap.get(function),
      locator);
    System.out.println("Findproper locator for " + elementLocator);
   } else{
    System.out.println("Cannotfind proper locator for "
      +elementLocator);
   }
  }
  return elementLocator;
 }

 private Object[]createParameters(Method method, String elementLocator,
   String value){
  Object[] parameters =null;
  if(value.equalsIgnoreCase("n/a")) {
   value =null;
  }
  switch(method.getParameterTypes().length) {
  case 0:
   returnnull;
  case 1:
   parameters =new String[] { elementLocator };
   break;
  case 2:
   parameters =new String[] { elementLocator, value };
   break;
  }
  return parameters;
 }

 private StringremoveStartAndEnd(String str, String... removes) {
  for (String remove : removes){
   str =StringUtils.removeStart(str, remove);
   str =StringUtils.removeEnd(str, remove);
  }
  return str;
 }

 private voidprintAllMethods() {
  Map<String,Method> methodMap = newTreeMap<String, Method>();
  methodMap.putAll(seleniumMethodMap);
  methodMap.putAll(functionMethodMap);
  Iterator<Entry<String,Method>> it =methodMap.entrySet().iterator();
  System.out.println("Availableactions are: ");
  while (it.hasNext()) {
   System.out.print(it.next().getKey()+ ",");
  }
  System.out.println();

 }

 privateMap<String, Method>getMethodMap(Class clazz) {
  Map<String,Method> methodMap = newTreeMap<String, Method>();
  Method[] functionMethods =clazz.getDeclaredMethods();
  for (Method method :functionMethods) {
   if(Modifier.isPublic(method.getModifiers())
     &&!Modifier.isStatic(method.getModifiers())) {
    methodMap.put(method.getName().toLowerCase(),method);
   }
  }
  return methodMap;
 }

 publicString standardize(String strAction) {
  String[] temp =strAction.split(" ");
  String action = "";
  for (int i = 0; i< temp.length; i++) {
   action =action + temp[i];
  }
  return action;
 }

 publicString getReportName(String strTestCaseFile) {
  Date today = new Date();
  String format ="yyyyMMMddHHmmss";
  SimpleDateFormat dateFormat =new SimpleDateFormat(format);
  String currentTime =dateFormat.format(today);
  // String folderSeperator =System.getProperty("file.separator");
  String[] temp =strTestCaseFile.split("\\\\");
  String excelName = "";
  if (temp.length> 1) {
   excelName =temp[temp.length - 1];
   temp =excelName.split(".xls");
   if(temp.length > 0) {
    excelName= temp[0];
   }
  }
  String testEnv =this.getSeleniumManager().getStrEnvironment();
  return excelName + "_" +testEnv + "_" + currentTime;
 }

 publicString getDuration(long start, long end) {
  int duration = (int) ((end -start) / 1000);
  int hour = duration /3600;
  if (hour > 0){
   duration =duration - 3600 * hour;
  }
  int min = duration / 60;
  if (min > 0){
   duration =duration - 60 * min;
  }
  int sec = duration;
  return hour + "hh:" + min +"mm:" + sec + "ss.";
 }

 publicMap<String, String>getLocatorActionMap() {
  return locatorActionMap;
 }

 publicvoid setLocatorActionMap(Map<String,String> locatorActionMap) {
  this.locatorActionMap =locatorActionMap;
 }

 publicFunctions getFunctions() {
  return functions;
 }

 publicvoid setFunctions(Functions functions) {
  this.functions =functions;
  functions.setExecutor(this);
 }

 publicSeleniumManager getSeleniumManager() {
  return seleniumManager;
 }

 publicvoid setSeleniumManager(SeleniumManager seleniumManager) {
  this.seleniumManager =seleniumManager;
 }

 publicList<TestCaseResultEntity>getDataList() {
  return dataList;
 }

 publicReportProcessor getReportProcessor() {
  return reportProcessor;
 }

 publicvoid setReportProcessor(ReportProcessor reportProcessor) {
  this.reportProcessor =reportProcessor;
 }

 publicString getReport() {
  return report;
 }

 publicvoid setReport(String report) {
  this.report = report;
 }

 publicString getJrxml() {
  return jrxml;
 }

 publicvoid setJrxml(String jrxml) {
  this.jrxml = jrxml;
 }

 publicBaseDaoImpl getBaseDaoImpl() {
  return baseDaoImpl;
 }

 publicvoid setBaseDaoImpl(BaseDaoImpl baseDaoImpl) {
  this.baseDaoImpl =baseDaoImpl;
 }

}

------------------------------------------------------------------------------

packagecom.citivelocity.functions;

importjava.util.Map;

importorg.springframework.beans.factory.InitializingBean;

importcom.thoughtworks.selenium.DefaultSelenium;
import com.thoughtworks.selenium.Selenium;
import com.thoughtworks.selenium.SeleniumException;

public class SeleniumManager implements InitializingBean {
 private Selenium selenium;
 //
 private boolean reportToDb;
 private String strBrowser;
 private String strTestSet;
 private String strTestMachine;
 private int intTestPort;
 private String strEnvironment;
 private String intExt;
 private String strTestCaseFile;
 private String strUserName;
 private String strPassWord;
 private Executor executor;
 private Map<String,String> testUrlMap;
 private Map<String,String> browserMap;
 private Map<String,String> itemCheckForMap;
 //
 private String testUrl;
 private String browserToUse;
 private String itemCheckFor;

 //

 
 public void afterPropertiesSet() throws Exception{
  testUrl = (null ==testUrlMap.get(strEnvironment)) ? strEnvironment
    :testUrlMap.get(strEnvironment);
  browserToUse =browserMap.get(strBrowser);
  itemCheckFor =itemCheckForMap.get(intExt);
  System.out.println("testUrl : "+ testUrl + ", env : " + strEnvironment
    +", browserToUse : " + browserToUse + ", itemCheckFor: "
    +itemCheckFor);
  initialiseSelenium();
 }

 private voidinitialiseSelenium() {
  for (int i = 0; i< 10; i++) {
   if(seleniumInstantiateSelenium(strTestMachine, intTestPort,
     browserToUse,testUrl)) {
//    seleniumSetTimeout("0");
    selenium.start();
    selenium.open("/");
    waitForTime(20000);
    if(selenium.isElementPresent(itemCheckFor)) {
     System.out.println("Foundthe item to check for "
       +itemCheckFor);
     break;
    }
   }
  }
  seleniumSetTimeout("30000"); //Set timeout back to default value
 }

 public void seleniumClose(){
  selenium.close();
 }
 private boolean seleniumSetTimeout(Stringtimeout) {
  boolean result;
  try {
   selenium.setTimeout(timeout);
   result =true;
  } catch (SeleniumException e){
   System.out.println("Exceptioncaught in selenium.setTimeout - "
     +e.getMessage());
   result =false;
  }
  return result;
 }

 publicvoid execute() {
  executor.execute(strTestCaseFile);
 }
 
 // TODO
 public void addResultsRow(String stepdescr,String expectedResult,
   StringactualResult, String status) {

  try{
   System.out.println(stepdescr+ expectedResult + ": " + actualResult
     +"---" + status);
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

 private booleanseleniumInstantiateSelenium(String host, int port,
   StringbrowserCode, String url) {
  boolean result=false;
  try {
   selenium =new DefaultSelenium(host, port, browserCode, url);
   result =true;
  } catch (SeleniumException e){
   System.out.println("Exceptioncaught in initializeSelenium - "
     +e.getMessage());
   result =false;
  }finally{
   if(selenium!=null && result==false){
    selenium.close();
   }
  }
  return result;
 }

 publicvoid waitForTime(long milliSecs) {
  try {
   Thread.sleep(milliSecs);
  } catch (InterruptedExceptione) {
   e.printStackTrace();
  }
 }
  
 public Executor getExecutor() {
  return executor;
 }

 publicvoid setExecutor(Executor executor) {
  this.executor = executor;
 }

 publicMap<String, String>getItemCheckForMap() {
  return itemCheckForMap;
 }

 publicvoid setItemCheckForMap(Map<String,String> itemCheckForMap) {
  this.itemCheckForMap =itemCheckForMap;
 }

 publicboolean getReportToDb() {
  return reportToDb;
 }

 publicvoid setReportToDb(boolean reportToDb) {
  this.reportToDb =reportToDb;
 }

 publicString getStrBrowser() {
  return strBrowser;
 }

 publicvoid setStrBrowser(String strBrowser) {
  this.strBrowser =strBrowser;
 }

 publicString getStrTestSet() {
  return strTestSet;
 }

 publicvoid setStrTestSet(String strTestSet) {
  this.strTestSet =strTestSet;
 }

 publicString getStrTestMachine() {
  return strTestMachine;
 }

 publicvoid setStrTestMachine(String strTestMachine) {
  this.strTestMachine =strTestMachine;
 }

 publicint getIntTestPort() {
  return intTestPort;
 }

 publicvoid setIntTestPort(int intTestPort) {
  this.intTestPort =intTestPort;
 }

 publicString getStrEnvironment() {
  return strEnvironment;
 }

 publicvoid setStrEnvironment(String strEnvironment) {
  this.strEnvironment =strEnvironment;
 }

 publicString getIntExt() {
  return intExt;
 }

 publicvoid setIntExt(String intExt) {
  this.intExt = intExt;
 }

 publicString getStrTestCaseFile() {
  return strTestCaseFile;
 }

 publicvoid setStrTestCaseFile(String strTestCaseFile) {
  this.strTestCaseFile =strTestCaseFile;
 }

 publicString getStrUserName() {
  return strUserName;
 }

 publicvoid setStrUserName(String strUserName) {
  this.strUserName =strUserName;
 }

 publicString getStrPassWord() {
  return strPassWord;
 }

 publicvoid setStrPassWord(String strPassWord) {
  this.strPassWord =strPassWord;
 }

 publicMap<String, String> getTestUrlMap(){
  return testUrlMap;
 }

 publicvoid setTestUrlMap(Map<String,String> testUrlMap) {
  this.testUrlMap =testUrlMap;
 }

 publicMap<String, String> getBrowserMap(){
  return browserMap;
 }

 publicvoid setBrowserMap(Map<String,String> browserMap) {
  this.browserMap =browserMap;
 }

 publicSelenium getSelenium() {
  return selenium;
 }

 publicvoid setSelenium(Selenium selenium) {
  this.selenium = selenium;
 }

}

--------------------------------------------------------------------

packagecom.citivelocity.functions;

importjava.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

importnet.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JRExporterParameter;
import net.sf.jasperreports.engine.JasperCompileManager;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
importnet.sf.jasperreports.engine.data.JRBeanCollectionDataSource;
import net.sf.jasperreports.engine.design.JasperDesign;
import net.sf.jasperreports.engine.export.JRHtmlExporter;
importnet.sf.jasperreports.engine.export.JRHtmlExporterParameter;
import net.sf.jasperreports.engine.xml.JRXmlLoader;

public classReportProcessor {

 publicvoid generate(Map params, List dataList, String jrxml, StringreportName)
   throwsFileNotFoundException {
  InputStream ins;
  // this file is used to definethe report's style
  String basePath =System.getProperty("user.dir");
  jrxml = basePath + jrxml;
  reportName = basePath +reportName;
  ins = newFileInputStream(jrxml);
  JRBeanCollectionDataSourcecolDataSource = new JRBeanCollectionDataSource(
    dataList);
  File destFile = newFile(reportName);

  JasperDesignjasperDesign;
  try {
   jasperDesign= JRXmlLoader.load(ins);
   JasperReportjasperReport = JasperCompileManager
     .compileReport(jasperDesign);
   JasperPrintjasperPrint = JasperFillManager.fillReport(
     jasperReport,params, colDataSource);
   // export PDFfile
   // PDF
   //JasperExportManager.exportReportToPdfFile(jasperPrint,report);
//   JasperExportManager.exportReportToHtmlFile(jasperPrint,report);
   // Html
   JRHtmlExporter  exporter = new JRHtmlExporter();
//   Map  imagesMap  =  new   HashMap();
   exporter.setParameter(JRExporterParameter.JASPER_PRINT,jasperPrint);
   exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME,reportName);
   exporter.setParameter(JRExporterParameter.CHARACTER_ENCODING,"UTF-8");
   exporter.setParameter(JRHtmlExporterParameter.SIZE_UNIT,"pt");//解决生成HTML报表缩小问题
   // 导出
   exporter.exportReport();
   System.out.println("successto generate report.");
  } catch (JRException e) {
   e.printStackTrace();
  }

 }

}
------------------------------------------------------------------------

packagecom.citivelocity.functions;

importjava.util.Date;

importcom.citivelocity.utils.TestCaseResultEntity;
import com.thoughtworks.selenium.Selenium;
import com.thoughtworks.selenium.SeleniumException;

public class Functions {
 private Executor executor;

 
 protected void setExecutor(Executor executor){
  this.executor = executor;
  executor.getSeleniumManager().getSelenium();
 }

 //TODO here goes the extra api
 public void click(String locator) {
  String strAlert = null;
  String ifNotFoundStatus ="Fail";
  Selenium selenium =executor.getSeleniumManager().getSelenium();

  if(selenium.isElementPresent(locator)) {

   booleantargetPresent = true;
   String target= "";
   try {
    selenium.click(locator);
    System.out.println("Elementwas clicked successfully"
      +"--Pass");
   } catch(SeleniumException e) {

    System.out.println("Exceptioncaught- " + e.getMessage());
    e= null;
   }
   if(selenium.isAlertPresent()) {
    strAlert= selenium.getAlert();
   }
  }
 }

 publicvoid checkTextExists(String locator) throwsSeleniumException{
  String returnText = "";
  Selenium selenium =executor.getSeleniumManager().getSelenium();
  if(selenium.isElementPresent(locator) == true) {
    returnText= selenium.getText(locator);
    System.out
    .println("checkTextExists--returnText:" + returnText);
  } else {
//   System.out.println("Thelocator not found.");
   SeleniumExceptione = new SeleniumException("The locator not found.");
   throwe;
  }
 }

 publicvoid waitForElement(String locator, String value) {
  System.out.println("--waitForElement:locator-->" + locator);
  System.out.println("--waitForElement:value-->" + value);
  long timeoutValue = 0;
  String strTimeValue = "";
  String strExpected = "";
  boolean expectedResult;
  String[] section = newString[1];
  String delimiter =",";

  if(value.contains(delimiter)) {
   section =value.split(delimiter);
   strExpected =section[0];
   strTimeValue= section[1];
  } else {
   strExpected =value;
   strTimeValue= "60";
  }

  expectedResult= Boolean.parseBoolean(strExpected);

  timeoutValue= Integer.parseInt(strTimeValue) * 1000;

  if(waitUntil(locator, timeoutValue)) {

  }else {
   System.out.println("waittime out.");
  }
 }

 publicboolean waitUntil(String locator, long timeout) {

  longloopStartTime = new Date().getTime();
  long loopEndTime, loopDifTime =0;
  boolean elementExists =false;
  Selenium selenium =executor.getSeleniumManager().getSelenium();
  while (!elementExists) {
   elementExists= selenium.isElementPresent(locator);
   if(elementExists == true) {
    elementExists= true;
    break;
   } else{
    elementExists= false;
   }
   waitForTime(1000,0);
   loopEndTime =new Date().getTime();
   loopDifTime =loopEndTime - loopStartTime;
   if(loopDifTime >= timeout) {
    System.out.println("waitForElementtime out.");
    break;
   }
  }
  return elementExists;
 }

 //TODO
 public void waitForTime(long milliSecs, intstrTimeFlag) {
  try {
   Thread.sleep(milliSecs);
  } catch (InterruptedExceptione) {
   e.printStackTrace();
  }
 }

}
----------------------------------------------------------------

packagecom.citivelocity.springJDBC;

importjava.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;

importorg.apache.commons.beanutils.BeanUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.StringUtils;

public class BaseDaoImplimplements IDao {
 private JdbcTemplate jdbcTemplate;

 publicint insert(Object obj, String tbName) {
  String sql =generateSQLForResultEntity(obj, tbName);
  returnjdbcTemplate.update(sql);
 }

 @SuppressWarnings("unchecked")
 private String generateSQLForResultEntity(Objecttc, String tbName) {
  String sql = "Insert into";
  StringBuffer col = newStringBuffer();
  StringBuffer cValue = newStringBuffer(" values(");
  String key = "";
  String value = "";

  try{
   Map m =BeanUtils.describe(tc);
   Iterator it =m.entrySet().iterator();
   while(it.hasNext()) {
    Map.Entry<String,String> entry = (Entry<String,String>) it
      .next();
    key= entry.getKey();
    value= entry.getValue();
    if(!(key.equalsIgnoreCase("class"))) {
     col.append(key+ ",");
     cValue.append("'"+ value + "',");
    }
   }
   Stringcolumns  = "(" +StringUtils.trimTrailingCharacter(col.toString(), ',') +")";  
   String values= StringUtils.trimTrailingCharacter(cValue.toString(), ',') +")";
   cValue.append(");");
   sql = sql +tbName + columns + values;
  } catch (Exception e) {
   e.printStackTrace();
  }
  return sql;
 }

}

--------------------------------------------------------------------
package com.citivelocity.springJDBC;

public interface IDao{
 public int insert(Object obj,StringtbName);
}
--------------------------------------------------------------------------------

packagecom.citivelocity.springJDBC;

public classTestCaseResultEntity {

 private StringstepNum;
 private String page;
 private String portlet;
 private String element;
 private String action;
 private String status;
 private String remark;
 
 public TestCaseResultEntity(String stepNum,Stringpage,String portlet,String element,String action,Stringstatus,String remark){
  this.stepNum = stepNum;
  this.page = page;
  this.portlet = portlet;
  this.element = element;
  this.action = action;
  this.status = status;
  this.remark = remark;
 }
 public String getStepNum() {
  return stepNum;
 }
 public void setStepNum(String stepNum) {
  this.stepNum = stepNum;
 }
 public String getPage() {
  return page;
 }
 public void setPage(String page) {
  this.page = page;
 }
 public String getPortlet() {
  return portlet;
 }
 public void setPortlet(String portlet) {
  this.portlet = portlet;
 }
 public String getElement() {
  return element;
 }
 public void setElement(String element) {
  this.element = element;
 }
 public String getAction() {
  return action;
 }
 public void setAction(String action) {
  this.action = action;
 }
 public String getStatus() {
  return status;
 }
 public void setStatus(String status) {
  this.status = status;
 }
 public String getRemark() {
  return remark;
 }
 public void setRemark(String remark) {
  this.remark = remark;
 }
 
}
------------------------------------------------------------

packagecom.citivelocity.utils;

importjava.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Map;
import java.util.TreeMap;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public classExcelTestCasesParser {

 
 public static Map<Integer,TestCaseStepEntity> generate(String path){  
  InputStream is =ExcelTestCasesParser.class.getClassLoader()
    .getResourceAsStream(path);
  Map<Integer,TestCaseStepEntity> testcase = newTreeMap<Integer,TestCaseStepEntity>();
  try {
   // read .xlsfile
   Workbookworkbook = Workbook.getWorkbook(is);
   Sheet[]sheets = workbook.getSheets();
   // sheet 1contains all the test steps
   Sheet sheet =sheets[0];
   intintNumRows = sheet.getRows() - 1;
   for (int i =1; i <= intNumRows; i++) {
    TestCaseStepEntitystep = new TestCaseStepEntity();
    //Pull in data from spreadsheet and store as javabeen    
    //set each row into a java been
    StringrptStepNumber = sheet.getCell(0, i).getContents();
    step.setStepNum(rptStepNumber);
    step.setPageName(sheet.getCell(1,i).getContents());
    step.setPortlet(sheet.getCell(2,i).getContents());
    step.setElement(sheet.getCell(3,i).getContents());
    step.setElementLocator(sheet.getCell(4,i).getContents());
    step.setRunWhen(sheet.getCell(5,i).getContents());
    step.setAction(sheet.getCell(6,i).getContents());
    step.setValue(sheet.getCell(7,i).getContents());
    step.setIeFlg(sheet.getCell(8,i).getContents());
    step.setFirefoxFlg(sheet.getCell(9,i).getContents());
    step.setSafariFlg(sheet.getCell(10,i).getContents());
    step.setChromeFlg(sheet.getCell(11,i).getContents());
    step.setComment(sheet.getCell(12,i).getContents());
    //a map shows the test case process
    testcase.put(Integer.parseInt(rptStepNumber),step);
    //testcase.setTestCaseName(xlsName);
    //testcase.setTestCaseSteps(steps);
   }
   workbook.close();
  } catch (FileNotFoundExceptione) {
   e.printStackTrace();
  } catch (BiffException e){
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  }
  return testcase;
 }

}
额，偷个懒，没有啥注释也没啥解释，以后补充吧。