基本信息
源码名称：java excel操作示例代码
源码大小：0.07M
文件格式：.rar
开发语言：Java
更新时间：2015-04-09
友情提示：（无需注册或充值，赞助后即可获取资源下载链接）
嘿，亲！知识可是无价之宝呢，但咱这精心整理的资料也耗费了不少心血呀。小小地破费一下，绝对物超所值哦！如有下载和支付问题，请联系我们QQ(微信同号)：813200300
本次赞助数额为： 2 元×
微信扫码支付：2 元
×
请留下您的邮箱，我们将在2小时内将文件发到您的邮箱
源码介绍
package ExecutionEngine;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import org.apache.log4j.xml.DOMConfigurator;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.impl.xb.xmlconfig.ConfigDocument.Config;
import Utils.ExcelUtils;
import Config.ActionKeywords;
import Config.Constants;
public class DriverScript {
public static ActionKeywords actionKeywords;
public static String sPageObject;
public static String method[];
public static int iTestStep;
public static int iTestLastStep;
public static int iTestDataRow;
public static String sTestCaseID;
public static String sRunMode;
public static String sData;
public static boolean bResult=true;
public static String queryresult;
public static String sTestStep;
public static String sDesctipt;
public static String sTestName;
public static String sFindByType;
public static String sTestObject;
public static String sOperation;
public static String sexpected;
public static String TestSuiteName;
public static String TestAction_Value;
public static void main(String[] args) throws Exception {
//results_statistical();
//Clean_Data ();
//Run_Test_Suite("配資1000审核成功");
Task_scheduling();
//Record_TestResult() ;
}
/**
* 备份文件并移除Sheet Reports
* @throws IOException
*/
public static void Clean_Data () throws IOException {
SimpleDateFormat formatter = new SimpleDateFormat("yyyy_MM_dd_HH_mm");
//生成时间
String dateString = formatter.format(new Date());
String FileName_TestData=System.getProperty("user.dir") "\\src\\Reports\\Reports.xlsx";
String dst=System.getProperty("user.dir") "\\src\\Reports\\" dateString ".xlsx";
FileInputStream ExcelFile3;
ExcelFile3 = new FileInputStream(FileName_TestData);
XSSFWorkbook ExcelWBook3 = new XSSFWorkbook(ExcelFile3);
XSSFSheet ReportSheet=ExcelWBook3.getSheet("Report");
if (ReportSheet!=null){
ExcelUtils.copyFile(FileName_TestData,dst);
for (int i = 0; i < ExcelWBook3.getNumberOfSheets(); i ) {
String sheetname=ExcelWBook3.getSheetAt(i).getSheetName();
if (sheetname.equals("Report")){
ExcelWBook3.removeSheetAt(i);
FileOutputStream fileOut = new FileOutputStream(FileName_TestData);
ExcelWBook3.write(fileOut);
fileOut.close();
ExcelFile3.close();
}
}
}
}
public static void results_statistical2() throws IOException {
String FileName_TestData=System.getProperty("user.dir") "\\src\\Reports\\Reports.xlsx";
FileInputStream ExcelFile3;
ExcelFile3 = new FileInputStream(FileName_TestData);
XSSFWorkbook ExcelWBook3 = new XSSFWorkbook(ExcelFile3);
XSSFSheet ReportSheet=ExcelWBook3.getSheet("Report");
XSSFSheet ResultSheet=ExcelWBook3.getSheet("首页---数据统计");
XSSFRow Row;
boolean Result=true;
int PASSNum=0;
int FAILNum=0;
if (ReportSheet!=null){
int Rows=ReportSheet.getLastRowNum();
for (int i=1;i<Rows;i ){
String TestCaseName=ReportSheet.getRow(i).getCell(1).getStringCellValue();
String TestCaseName_Temp = "";
if(ReportSheet.getRow(i 1)!=null) {
TestCaseName_Temp=ReportSheet.getRow(i 1).getCell(1).getStringCellValue();
if (!TestCaseName.equals(TestCaseName_Temp)){
Result=ReportSheet.getRow(i).getCell(9).getBooleanCellValue();
if (Result==true){
PASSNum=PASSNum 1;
}
else {
FAILNum=FAILNum 1;
}
}
}
}
System.out.println("PASSNum: " PASSNum "FAILNum: " FAILNum);
Row=ResultSheet.getRow(1);
if (Row==null) {
Row=ResultSheet.createRow(1);
}
if (Row.getCell(0)!=null){
Row.getCell(0).setCellValue(PASSNum);
Row.getCell(1).setCellValue(FAILNum);
}
else {
Row.createCell(0).setCellValue(PASSNum);
Row.createCell(1).setCellValue(FAILNum);
}
}
System.out.println("PASSNum : " PASSNum "FAILNum : " FAILNum);
FileOutputStream fileOut = new FileOutputStream(FileName_TestData);
ExcelWBook3.write(fileOut);
fileOut.close();
ExcelFile3.close();
}
public static void results_statistical() throws IOException {
String FileName_TestData=System.getProperty("user.dir") "\\src\\Reports\\Reports.xlsx";
FileInputStream ExcelFile3;
ExcelFile3 = new FileInputStream(FileName_TestData);
XSSFWorkbook ExcelWBook3 = new XSSFWorkbook(ExcelFile3);
XSSFSheet ReportSheet=ExcelWBook3.getSheet("Report");
XSSFSheet ResultSheet=ExcelWBook3.getSheet("首页---数据统计");
XSSFRow Row;
int iTestCasePass=0;
int iTestCaseFail=0;
int iPass=0;
int iFail=0;
int iTestCase=0;
//取出最大行号
int Rows=ReportSheet.getLastRowNum();
for (int i=1;i<Rows 1;i ){
boolean result= ReportSheet.getRow(i).getCell(9).getBooleanCellValue();
if (result==true){
iPass=iPass 1;
}
else {
iFail=iFail 1;
}
// System.out.println("iPass: " iPass "iFail : " iFail);
}
for (int j=0;j<Rows;j ){
String TestCaseName=ReportSheet.getRow(j).getCell(1).getStringCellValue();
String Temp=ReportSheet.getRow(j 1).getCell(1).getStringCellValue();
if (!TestCaseName.equals(Temp)){
iTestCase=iTestCase 1;
// System.out.println("统计TestCase 数量" iTestCase);
}
}
iTestCasePass=iTestCase-iFail;
iTestCaseFail=iFail;
Row=ResultSheet.getRow(1);
if (Row!=null){
Row.getCell(0).setCellValue(iTestCasePass);
Row.getCell(1).setCellValue(iTestCaseFail);
Row.createCell(10).setCellValue(iPass);
Row.createCell(11).setCellValue(iFail);
}
else {
Row=ResultSheet.createRow(1);
Row.createCell(0).setCellValue(iTestCasePass);
Row.createCell(1).setCellValue(iTestCaseFail);
Row.createCell(10).setCellValue(iPass);
Row.createCell(11).setCellValue(iFail);
}
FileOutputStream fileOut = new FileOutputStream(FileName_TestData);
ExcelWBook3.write(fileOut);
fileOut.close();
ExcelFile3.close();
}
public static void Record_TestResult() throws IOException {
String FileName_TestData=System.getProperty("user.dir") "\\src\\Reports\\Reports.xlsx";
FileInputStream ExcelFile3;
ExcelFile3 = new FileInputStream(FileName_TestData);
XSSFWorkbook ExcelWBook3 = new XSSFWorkbook(ExcelFile3);
XSSFSheet ReportSheet=ExcelWBook3.getSheet("Report");
XSSFRow Row;
if (ReportSheet==null) {
ReportSheet=ExcelWBook3.createSheet("Report");
Row=ReportSheet.createRow(0);
Row.createCell(0).setCellValue("测试套件名称");
Row.createCell(1).setCellValue("测试用例");
Row.createCell(2).setCellValue("测试步骤");
Row.createCell(3).setCellValue("测试描述");
Row.createCell(4).setCellValue("定位方式");
Row.createCell(5).setCellValue("测试对象名称");
Row.createCell(6).setCellValue("测试对象实休");
Row.createCell(7).setCellValue("执行操作");
Row.createCell(8).setCellValue("测试数据");
Row.createCell(9).setCellValue("测试结果");
Row.createCell(10).setCellValue("测试结际结果");
FileOutputStream fileOut = new FileOutputStream(FileName_TestData);
ExcelWBook3.write(fileOut);
fileOut.close();
}
int LastNum2=ReportSheet.getLastRowNum();
Row=ReportSheet.createRow(LastNum2 1);
Row.createCell(0).setCellValue(TestSuiteName);
Row.createCell(1).setCellValue(sTestCaseID);
Row.createCell(2).setCellValue(sTestStep);
Row.createCell(3).setCellValue(sDesctipt);
Row.createCell(4).setCellValue(sFindByType);
Row.createCell(5).setCellValue(sTestName);
Row.createCell(6).setCellValue(sTestObject);
Row.createCell(7).setCellValue(sOperation);
if (sData!=null){
Row.createCell(8).setCellValue(sData);
}
Row.createCell(9).setCellValue(bResult);
if (sOperation!=null && sOperation.contains("check")){
Row.createCell(10).setCellValue(TestAction_Value);
}
FileOutputStream fileOut = new FileOutputStream(FileName_TestData);
ExcelWBook3.write(fileOut);
fileOut.close();
ExcelFile3.close();
}
public static void Task_scheduling() throws Exception {
Clean_Data ();
String FileName_TestData=System.getProperty("user.dir") "\\src\\TestSuite.xlsx";
FileInputStream ExcelFile2;
ExcelFile2 = new FileInputStream(FileName_TestData);
XSSFWorkbook ExcelWBook2 = new XSSFWorkbook(ExcelFile2);
XSSFSheet ExcelWSheet2=ExcelWBook2.getSheet("任务执行计划");
int LastNum2=ExcelWSheet2.getLastRowNum();
for (int j=1;j<LastNum2 1;j ){
XSSFRow Row =ExcelWSheet2.getRow(j);
String TestSuite=Row.getCell(0).getStringCellValue();
String TestSuiteMode=Row.getCell(1).getStringCellValue();
if (TestSuiteMode.equals("Yes")) {
Run_Test_Suite(TestSuite);
}
}
results_statistical();
}
public static void Run_Test_Suite(String SuiteName) throws Exception {
String FileName_TestSuite=System.getProperty("user.dir") "\\src\\TestSuite.xlsx";
FileInputStream ExcelFile;
try {
ExcelFile = new FileInputStream(FileName_TestSuite);
XSSFWorkbook ExcelWBook = new XSSFWorkbook(ExcelFile);
XSSFSheet TestSuiteSheet=ExcelWBook.getSheet("测试套件 ");
int LastNum=TestSuiteSheet.getLastRowNum();
for (int i=1;i<LastNum 1;i ){
//获取测试套件中最大行号
XSSFRow Row =TestSuiteSheet.getRow(i);
TestSuiteName=Row.getCell(0).getStringCellValue();
//获取测试数据索引值
String TestData=Row.getCell(1).getStringCellValue();
int TestDataIndex=Integer.parseInt(TestData);
//获测试用例名称
sTestCaseID=Row.getCell(2).getStringCellValue();
//运行测试用例（根据测试套件指定的用例名称和数据索引值执行）
if (SuiteName.equals(TestSuiteName)){
Run_Test_Case(sTestCaseID,TestDataIndex);
System.out.println("测试用例名称" sTestCaseID "测试套件名称 ==" TestSuiteName);
}
}
ExcelFile.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
public static void Run_Test_Case (String SheetName,int SetDataIndex) throws Exception {
//设置用例文件名称
String FileName_TestCase=System.getProperty("user.dir") "\\src\\TestCase\\TestCase.xlsx";
//打开用例
Utils.ExcelUtils.setExcelFile(FileName_TestCase);
//加载配置文件
DOMConfigurator.configure("log4j.xml");
//统计用例行数
int iTeseCaseRows=Utils.ExcelUtils.getRowCount(SheetName);
//依次读取各单元格中的内容
for (iTestStep=1;iTestStep<iTeseCaseRows;iTestStep ) {
sTestStep=Utils.ExcelUtils.getCellData(iTestStep, Constants.Col_TestStep_ID, SheetName);
sDesctipt=Utils.ExcelUtils.getCellData(iTestStep, Constants.Col_TestStep_Descript, SheetName);
sTestName=Utils.ExcelUtils.getCellData(iTestStep, Constants.Col_TestStep_TestContrl, SheetName);
sFindByType=Utils.ExcelUtils.getCellData(iTestStep,Constants.Col_TestStep_FindByType, SheetName);
sTestObject=Utils.ExcelUtils.getCellData(iTestStep,Constants.Col_TestStep_TestObject, SheetName);
sOperation=Utils.ExcelUtils.getCellData(iTestStep, Constants.Col_TestStep_Operation, SheetName);
sData=Utils.ExcelUtils.getCellData(iTestStep, Constants.Col_TestStep_Data, SheetName);
if (sData.contains("数化_")){
//Split sData;
String tem[]=sData.split("_");
String temp_cell=tem[1];
int cell= Integer.parseInt(temp_cell);
String FileName_TestData=System.getProperty("user.dir") "\\src\\TestData\\TestData.xlsx";
FileInputStream ExcelFile = new FileInputStream(FileName_TestData);
XSSFWorkbook ExcelWBook = new XSSFWorkbook(ExcelFile);
XSSFSheet ExcelWSheet=ExcelWBook.getSheet(SheetName);
XSSFRow Row =ExcelWSheet.getRow(SetDataIndex);
sData=Row.getCell(cell).getStringCellValue();
ExcelFile.close();
}
System.out.println("测试用例名称==" sTestCaseID "测试步骤 =" sTestStep "测试对象 =" sTestName "操作方法 =" sOperation "测试数据 =" sData );
execute_Actions(sOperation);
Record_TestResult();
if (bResult==false){
ActionKeywords.Screenshot("",TestSuiteName "--" sTestCaseID "-----" sTestStep );
break;
}
}
}
private static void execute_Actions(String OperationName) throws Exception {
Thread.sleep(500);
switch (OperationName) {
case "scrollIntoWebElement":
ActionKeywords.scrollIntoWebElement(sFindByType, sTestObject, sData);
break;
case "click":
ActionKeywords.click(sFindByType, sTestObject, sData);
break;
case "contextclick":
ActionKeywords.contextclick(sFindByType, sTestObject, sData);
break;
case "doubleClick":
ActionKeywords.doubleClick(sFindByType, sTestObject, sData);
break;
case "openBrowser":
ActionKeywords.openBrowser(sFindByType, sTestObject, sData);
break;
case "switchtowindows":
ActionKeywords.switchtowindows(sFindByType, sTestObject, sData);
break;
case "navigate":
ActionKeywords.navigate(sFindByType, sTestObject, sData);
break;
case "alert":
ActionKeywords.alert(sFindByType, sTestObject, sData);
break;
case "click_table":
ActionKeywords.click_table(sFindByType, sTestObject, sData);
break;
case "select":
ActionKeywords.select(sFindByType, sTestObject, sData);
break;
case "input":
ActionKeywords.input(sFindByType, sTestObject, sData);
break;
case "closeBrowser":
ActionKeywords.closeBrowser(sFindByType, sTestObject, sData);
break;
case "checkText":
ActionKeywords.checkText(sFindByType, sTestObject, sData);
break;
case "check_table_text":
ActionKeywords.check_table_text(sFindByType, sTestObject, sData);
break;
case "querySQL":
ActionKeywords.querySQL(sFindByType, sTestObject, sData);
break;
default:
break;
}
}
}