用testNG读取Excel中的excel数据

Question

我是新来的硒，仍在学习。我试图用两个测试在TestNG中编写一个程序，以便从Excel中读取数据并使用Apache POI编写代码。我在同一个文件夹中有一个XLS和一个XLSX文件，并试图使用这两个文件。我期待在testng.xml中配置excels的路径和名称。当我执行我的第一个测试来读取数据时，表名正在被读为xml。以下是我的testng.xml

<?xml version="1.0" encoding="UTF-8"?> 
<!DOCTYPE suite SYSTEM "http://testng.org/testng-1.0.dtd"> 
<suite name="Suite" parallel="tests" thread-count="2"> 
<parameter name="filePath" value="C:\\Technologies\\Selenium\\WebDriver\\DataFiles\\ExcelFiles"></parameter> 
    <test name="Xlsx"> 
    <parameter name="fileName" value="12142015_sx.xlsx"></parameter> 
    <parameter name="sheetName" value="xlsx_1"></parameter> 
    <classes> 
     <class name="handlefiles.handleExcel"/> 
    </classes> 
    </test> 
    <test name="Xls"> 
    <parameter name="fileName" value="12142015_s.xls"></parameter> 
    <parameter name="sheetName" value="xls_1"></parameter> 
    <classes> 
     <class name="handlefiles.handleExcel"/> 
    </classes> 
    </test> <!-- Test --> 
</suite> <!-- Suite --> 

下面是java类代码。

package handlefiles; 

import java.io.*; 

import org.apache.poi.hssf.usermodel.HSSFWorkbook; 
import org.apache.poi.ss.usermodel.Row; 
import org.apache.poi.ss.usermodel.Sheet; 
import org.apache.poi.ss.usermodel.Workbook; 
import org.apache.poi.xssf.usermodel.XSSFWorkbook; 
import org.testng.annotations.*; 

public class handleExcel { 

    @BeforeTest 
    @Parameters({"filePath", "fileName", "sheetName"}) 
    public void readExcel(String filePath, String fileName, String sheetName) throws IOException{ 
     // set the file path 
     File eFile = new File(filePath+"\\"+fileName); 
     //print the file path 
     System.out.println("File is at: "+eFile); 

     FileInputStream inputstream = new FileInputStream(eFile); 
     Workbook wb = null; 

     // Read and publish extension 
     String extensionName = fileName.substring(fileName.indexOf(".")); 
     System.out.println("File type is: "+extensionName); 

     //Read the file 
     if(extensionName.equalsIgnoreCase(".xlsx")){ 
      wb = new XSSFWorkbook(inputstream); 
     }else if(extensionName.equalsIgnoreCase(".xls")){ 
      wb = new HSSFWorkbook(inputstream); 
     } 
     //Read the sheet name 
     Sheet wbSheet = wb.getSheet(sheetName); 
     System.out.println("Sheet Name is: "+wbSheet); 

    } 

    @Test(priority=1) 
    public void readData(Sheet wbSheet) { 

    // Get the row count 
      int rowCount = wbSheet.getLastRowNum() - wbSheet.getFirstRowNum(); 

      // print data from excel 
      for (int i=0; i<rowCount-1; i++){ 
       Row row = wbSheet.getRow(i); 
       for (int j=0; j<row.getLastCellNum(); j++){ 
        System.out.println(row.getCell(j).getStringCellValue()+"||"); 
       } 
      } 
    } 
} 

当我在Eclipse中运行这个，我看到下面exception-

org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF) 
    at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:128) 
    at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:112) 
    at org.apache.poi.poifs.filesystem.NPOIFSFileSystem.<init>(NPOIFSFileSystem.java:300) 
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:400) 
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:381) 
    at handlefiles.handleExcel.readExcel(handleExcel.java:36) 
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) 
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) 
    at java.lang.reflect.Method.invoke(Unknown Source) 
    at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:86) 
    at org.testng.internal.Invoker.invokeConfigurationMethod(Invoker.java:514) 
    at org.testng.internal.Invoker.invokeConfigurations(Invoker.java:215) 
    at org.testng.internal.Invoker.invokeConfigurations(Invoker.java:142) 
    at org.testng.TestRunner.beforeRun(TestRunner.java:656) 
    at org.testng.TestRunner.run(TestRunner.java:624) 
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:366) 
    at org.testng.SuiteRunner.access$000(SuiteRunner.java:39) 
    at org.testng.SuiteRunner$SuiteWorker.run(SuiteRunner.java:400) 
    at org.testng.internal.thread.ThreadUtil$2.call(ThreadUtil.java:64) 
    at java.util.concurrent.FutureTask.run(Unknown Source) 
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) 
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) 
    at java.lang.Thread.run(Unknown Source) 

Answer 1

根据堆栈跟踪POI认为您正尝试使用POI的XLS代码（而不是XLSX代码）打开XLSX文件。也许文件扩展名是错误的，该文件是“.xls”，但应该是“.xlsx”或文件已损坏等（或者您可能找到了一个错误）。

我建议采用POI的WorkbookFactory代替：

厂用于创建适当的种类的工作簿（它是 HSSFWorkbook或XSSFWorkbook），通过自动检测从供给 输入。

Answer 2

删除以下IF条件和

if(extensionName.equalsIgnoreCase(".xlsx")){ 
      wb = new XSSFWorkbook(inputstream); 
     }else if(extensionName.equalsIgnoreCase(".xls")){ 
      wb = new HSSFWorkbook(inputstream); 
     } 

添加这段代码

wb = new XSSFWorkbook(inputstream); 

我曾经用过，我从来没有告诉我正在使用哪个扩展。