jxl读取Excel表格数据

调用jxl包实现Excel表格数据的读取，代码如下：

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;

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

/**
 * @Description: Excel数据处理（使用中）
 * @author 
 * @date 创建时间：2016年10月11日下午12:49:46
 * @version 1.0
 */
public class ExcelData {
    private Workbook workbook;
    private Sheet sheet;
    private int rows;
    private int columns;
    private String fileName;
    private String caseName;
    private ArrayList<String> arrkey = new ArrayList<String>();
    private String sourceFile;
    
    private Log log = new Log(this.getClass());
    /**
     * @param fileName excel文件名
     * @param caseName sheet名
     */
    public ExcelData(String fileName, String caseName) {
        super();
        this.fileName = fileName;
        this.caseName = caseName;
    }

    /**
     * 获得excel表中的数据
     */
    public Object[][] getExcelData() throws BiffException, IOException {
        workbook = Workbook.getWorkbook(new File(getPath()));
        sheet = workbook.getSheet(caseName);
        rows = sheet.getRows();
        columns = sheet.getColumns();
        // 为了返回值是Object[][],定义一个多行单列的二维数组
        HashMap<String, String>[][] arrmap = new HashMap[rows - 1][1];
        // 对数组中所有元素hashmap进行初始化
        if (rows > 1) {
            for (int i = 0; i < rows - 1; i++) {
                arrmap[i][0] = new HashMap<String , String>();
            }
        } else {
            log.error("excel中没有数据");
        }

        // 获得首行的列名，作为hashmap的key值
        for (int c = 0; c < columns; c++) {
            String cellvalue = sheet.getCell(c, 0).getContents();
            arrkey.add(cellvalue);
        }
        // 遍历所有的单元格的值添加到hashmap中
        for (int r = 1; r < rows; r++) {
            for (int c = 0; c < columns; c++) {
                String cellvalue = sheet.getCell(c, r).getContents();
                arrmap[r - 1][0].put(arrkey.get(c), cellvalue);
            }
        }
        return arrmap;
    }
    
    /**
     * 获得excel文件的路径
     * @return
     * @throws java.io.IOException
     */

    public String getPath() throws IOException {       
        sourceFile = "test-data/" + fileName + ".xls"; 
        log.info("sourceFile = "+sourceFile);
        return sourceFile;
    }

}

调用方式如下：

    /**
     * 从对应Excel表格读取数据
     * 
     * @param 
     * @return
     * @throws Exception 
     */
    @DataProvider(name = "componentMessageQueryCase")
    private Object[][] QueryComponentMessageNumbers() throws Exception {
        // 获取Excel数据，得到一个map，依次传给test
        ExcelData e = new ExcelData("ComponentMessageTestCase", "componentMessageQueryCase");
        log.info("componentMessageQueryCase e.getExcelData() = " + e.getExcelData());
        return e.getExcelData();
    }    
    /**
     * 测试配件管理页面的查询功能
     * 
     * @param data 从对应Excel获取的测试数据
     * @return
     * @throws Exception 
     */
    @Test(dataProvider = "componentMessageQueryCase")
    private void componentMessageQueryCase(HashMap<String, String> data) throws Exception{
        log.info("测试配件查询功能");
        
        String OEMCode = data.get("OEMCode");
        String brandName = data.get("brandName");
        String seriesId = data.get("seriesId");
        String modelId = data.get("modelId");
        String componentNo = data.get("componentNo");
        String componentName = data.get("componentName");
        ComponentManageQuery.componentMessageQuery( OEMCode, brandName, seriesId, modelId, componentNo, componentName);
   }