我在测试的时候经常需要去改动case,如果把所有case直接写在单元测试中,非常冗余,我希望可以直接从excel去读取我的测试数据,经过几次试验终于成功了,读取excel主要借助poi jar包,源码如下所示。poi jar包下载地址http://down.51cto.com/data/2012838
ps:因为测试数据仅string类型已够用,所以代码只支持string类型的cell 内容,如果是想支持其他数据类型,需要进一步对cell内容进行判断,此处略过~package p1;
import java.io.BufferedInputStream;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class readExcelIntoTxt {
public static void main(String[] args) throws Exception
{
String filePath = "f:\\ReadExcel.xlsx";
File file = new File(filePath);
int startRowIndex = 0;//从第二行开始读取,第一行默认为列名
String[][] content = getData(file, startRowIndex);//从excel读取数据放到“行*列”的二维数组中
BufferedWriter writer = new BufferedWriter(new FileWriter(new File("F:\\WriteTxt.txt"))); //将生成的二维数组写入txt
int rowLength = content.length;
for(int i=0;i
{
for(int j=0;j
{
System.out.print(content[i][j]+"\t");
writer.write(content[i][j]+"\t");
}
writer.write("\r\n");
System.out.println();
}
writer.close();
}
public static String[][] getData(File file, int startRowIndex)throws FileNotFoundException, IOException
{
// 打开文件
Workbook wb;
Sheet st;
Row row;
Cell cell;
FileInputStream fis = new FileInputStream(file);
BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));
try
{
wb = new HSSFWorkbook(fis); //.xls读取
}
catch(Exception e)
{
wb = new XSSFWorkbook(bis);//.xlsx读取
}
List rowArray = new ArrayList();//所有行组成的数组
int maxColumnSize = 0;//二维数组的列最大值
for (int sheetIndex = 0; sheetIndex
{
st = wb.getSheetAt(sheetIndex);
for (int rowIndex = startRowIndex; rowIndex <= st.getLastRowNum(); rowIndex++)//遍历行
{
row = st.getRow(rowIndex);
boolean isCellValueNull = true;
if (row == null) //空行跳过
{
continue;
}
int columnSize = row.getLastCellNum(); //每行的列数
if(columnSize>maxColumnSize)//为确保数组的列容量
{
maxColumnSize = columnSize;
}
String[] rowValues = new String[columnSize];//每行的值,一维数组
Arrays.fill(rowValues, "");//填充默认空值
for (short columnIndex = 0; columnIndex
{
String value = "";
cell = row.getCell(columnIndex);
if (cell != null && cell.getCellType()==XSSFCell.CELL_TYPE_STRING)
{
value = cell.getStringCellValue();
}
if (cell != null && cell.getCellType()==XSSFCell.CELL_TYPE_NUMERIC)
{
value = new DecimalFormat("0").format(cell.getNumericCellValue());
}
if (value.trim().equals("")) //单元格内容为空则跳过
{
continue;
}
rowValues[columnIndex] = value;
isCellValueNull = false;
}
if (!isCellValueNull)//空行则跳过,包括仅有空格的行
{
rowArray.add(rowValues);
}
}
}
bis.close();
fis.close();
String[][] rowColumnArray = new String[rowArray.size()][maxColumnSize];
for (int i = 0; i
{
rowColumnArray[i] = (String[]) rowArray.get(i);
}
return rowColumnArray;
}
}