基于maven+restAssured+Excel（jxl）+testng+extentreports的接口自动化-Excel读取和写入（一）

1.引入jxl.jar包

2.详细代码

package base;

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

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class BaseExcel {

public static void main(String[] args) {
// TODO 自动生成的方法存根
//readExcel("D:\ME\workplace\jiekou.xls");
//outData();
}

/*
* 读取Excel，返回二维数组
*/
public static String[][] readExcel(String filepath,int a){

String[][] requestData = null;
try {
//创建输入流
InputStream stream = new FileInputStream(filepath);
//获取文件对象
Workbook rwb = Workbook.getWorkbook(stream);
//获取文件的指定工作表，默认是第一个
Sheet sheet = rwb.getSheet(a);
//初始化二维数组
requestData = new String[sheet.getRows()][sheet.getColumns()];
//行数 sheet.getRows()/列数sheet.getColumns()
for(int i = 0 ; i < sheet.getRows() ; i++){
for(int j = 0;j < sheet.getColumns();j++){
requestData[i][j] = sheet.getCell(j,i).getContents();
//System.out.print(i+"-"+j+"=="+requestData[i][j]+" ");
}
//System.out.println(" ");

}
} catch (FileNotFoundException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
} catch (BiffException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
} catch (IOException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}

return requestData;

}


/*
* 读取Excel，返回list
*/
public static List<Map<String, String>> readExcelList(String filepath,int a){

List<Map<String, String>> list = new ArrayList<Map<String, String>>();
try {
//创建输入流
InputStream stream = new FileInputStream(filepath);
//获取文件对象
Workbook rwb = Workbook.getWorkbook(stream);
//获取文件的指定工作表，默认是第一个
Sheet sheet = rwb.getSheet(a);

//行数 sheet.getRows()/列数sheet.getColumns()
for(int i = 0 ; i < sheet.getRows() ; i++){

Map<String, String> map = new HashMap<String, String>();

for(int j = 0;j < sheet.getColumns();j++){
map.put(sheet.getCell(j,0).getContents(), sheet.getCell(j,i).getContents());
//System.out.println(sheet.getCell(j,0).getContents());
//System.out.println("=======");
//System.out.println(sheet.getCell(j,i).getContents());
//System.out.print(i+"-"+j+"=="+requestData[i][j]+" ");
}
//System.out.println(" ");
list.add(map);
}
} catch (FileNotFoundException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
} catch (BiffException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
} catch (IOException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}

return list;

}

/*
* 将数据写入Excel
* xlsFilename是Excel地址
* a是Excel里面的表的序列号，从0开始
* column是Excel的列数
* Row是Excel的行数
* result是要写入的数据
*/
public static void writeExcel(String xlsFilename,int a,int column,int Row,String result){

try {

Workbook wb=Workbook.getWorkbook(new File(xlsFilename));

WritableWorkbook book=
Workbook.createWorkbook(new File(xlsFilename),wb);
//WritableSheet sheet0=book.createSheet("First Sheet",0);
WritableSheet sheet = book.getSheet(a);
//sheet.addCell(new Label(5,0,"mytest---------ok"));
Label filename1 = new Label(column,Row,result);
//System.out.println(filename1.getContents());
sheet.addCell(filename1);

book.write();
book.close();


} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();

}
catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();

} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();

} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();

}

}




}