package com.tree.autotest.demo;
import com.alibaba.fastjson.JSON;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
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.junit.Test;
import java.io.*;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* 规则包：
* 规则编码：
* 规则名称：
* 规则条件：
* <p/>
*/
public class ExcelHandle {
/**
* 将时间毫秒数转换成：yyyy-MM-dd HH:mm:ss 格式的字符串
* @param longtime
* @return
*/
public static String getTimeByLongTime(long longtime){
Date dat=new Date(longtime);
GregorianCalendar gc = new GregorianCalendar();
gc.setTime(dat);
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String sb=format.format(gc.getTime());
return sb;
}
/**
*
* @param timeStr 类似于：Tue Mar 03 00:00:00 CST 2015 这样的时间字符串
* @return
*/
public static String getTimeByLongTime(String timeStr){
SimpleDateFormat sdf = new SimpleDateFormat("EEE MMM dd HH:mm:ss Z yyyy", Locale.UK);
Date time = null;
try {
time = sdf.parse (timeStr);
} catch (ParseException e) {
e.printStackTrace();
}
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String sb=format.format(time);
return sb;
}
/**
* 通过map转成
* @param testcaselist 所有TC
* @param componentName
* @param excelIndex
* @return
*/
public static String requestJsonStrByMap(Map<String, ArrayList<String>> testcaselist,String componentName,int excelIndex){
String requestJsonStr = null;
try{
Map<String, String> paramMap= getTestCaseMap(testcaselist,excelIndex);
Map<String, Object> reqMap = new HashMap<String, Object>();
reqMap.put("name", componentName);
reqMap.put("session", paramMap);
requestJsonStr = JSON.toJSONString(reqMap, true);
}catch(Exception e){
}
return requestJsonStr;
}
/**
* 通过map转成
* @param componentName
* @param excelPath
* @param excelIndex
* @return
*/
public static String requestJsonStrByMap(String excelPath,String componentName,int excelIndex){
String requestJsonStr = null;
try{
Map<String, String> paramMap= getTestCaseMap(excelPath,excelIndex);
Map<String, Object> reqMap = new HashMap<String, Object>();
reqMap.put("name", componentName);
reqMap.put("session", paramMap);
requestJsonStr = JSON.toJSONString(reqMap, true);
}catch(Exception e){
}
return requestJsonStr;
}
/**
* 通过字符串拼接的方式组合成json
* @param componentName
* @param excelPath
* @param excelIndex
* @return
*/
public static String requestJsonStrByStr(String excelPath,String componentName,int excelIndex){
StringBuilder sb = new StringBuilder("{"name":""+componentName+"",");
String tcJsonStr = getTestCaseJsonStr(excelPath,excelIndex);
sb.append(""session":");
sb.append(tcJsonStr);
return sb.append("}").toString();
}
/**
* 将TC的入参以Map形式返回（不包含预期值）
* @param testcaselist 所有TC
* @param excelIndex
* @return
*/
private static Map<String, String> getTestCaseMap(Map<String, ArrayList<String>> testcaselist,int excelIndex){
try{
if(excelIndex <= 1) throw new Exception("输入的excel行号必须大于1");
ArrayList<String> clNames = testcaselist.get("1");// 列名
ArrayList<String> paramValues = new ArrayList<String>();
paramValues = testcaselist.get(excelIndex + "");
Map<String, String> paramMap= new HashMap<String, String>();
for (int i = 1; i < clNames.size(); i++) {//除去首例：用例名称
paramMap.put(clNames.get(i), paramValues.get(i));
}
paramMap.remove("预期值");//除去最后一列：预期值
paramMap.remove("实际值");//除去最后一列：实际值
return paramMap;
}catch(Exception e){
return null;
}
}
/**
* 将TC的入参以Map形式返回（不包含预期值）
* @param excelPath
* @param excelIndex
* @return
*/
private static Map<String, String> getTestCaseMap(String excelPath,int excelIndex){
try{
if(excelIndex <= 1) throw new Exception("输入的excel行号必须大于1");
Map<String, ArrayList<String>> testcaselist = ReadXlsx_2(excelPath);
ArrayList<String> clNames = testcaselist.get("1");// 列名
ArrayList<String> paramValues = new ArrayList<String>();
paramValues = testcaselist.get(excelIndex + "");
Map<String, String> paramMap= new HashMap<String, String>();
for (int i = 1; i < clNames.size(); i++) {//除去首例：用例名称
paramMap.put(clNames.get(i), paramValues.get(i));
}
paramMap.remove("预期值");//除去最后一列：预期值
return paramMap;
}catch(Exception e){
return null;
}
}
/**
* 将TC的入参以JsonStr形式返回（不包含预期值）
* @param excelPath excelpath文件路径
* @param excelIndex excel行号 PS：要大于1，因为1是列名
* @return
*/
public static String getTestCaseJsonStr(String excelPath,int excelIndex){
String jsonText = null;
try {
Map<String, String> paramMap= getTestCaseMap(excelPath, excelIndex);
jsonText = JSON.toJSONString(paramMap, true);
} catch (Exception e) {
e.printStackTrace();
}
return jsonText;
}
/**
* 获取某个TC的预期值，默认预期值的列名是：预期值
* @param testcaselist 所有TC
* @param excelIndex
* @return
*/
public static String getExpectedValue(Map<String, ArrayList<String>> testcaselist,int excelIndex){
String expectedValue = null;
try {
if(excelIndex <= 1) throw new Exception("输入的excel行号必须大于1");
ArrayList<String> clNames = testcaselist.get("1");// 列名
ArrayList<String> paramValues = new ArrayList<String>();
paramValues = testcaselist.get(excelIndex + "");
Map<String, String> paramMap= new HashMap<String, String>();
for (int i = 1; i < clNames.size(); i++) {//除去用例名称
paramMap.put(clNames.get(i), paramValues.get(i));
}
expectedValue = paramMap.get("预期值");
} catch (Exception e) {
e.printStackTrace();
}
return expectedValue;
}
/**
* 获取某个TC的预期值，默认预期值的列名是：预期值
* @param excelPath
* @param excelIndex
* @return
*/
public static String getExpectedValue(String excelPath,int excelIndex){
String expectedValue = null;
try {
if(excelIndex <= 1) throw new Exception("输入的excel行号必须大于1");
Map<String, ArrayList<String>> testcaselist = ReadXlsx_2(excelPath);
ArrayList<String> clNames = testcaselist.get("1");// 列名
ArrayList<String> paramValues = new ArrayList<String>();
paramValues = testcaselist.get(excelIndex + "");
Map<String, String> paramMap= new HashMap<String, String>();
for (int i = 1; i < clNames.size(); i++) {//除去用例名称
paramMap.put(clNames.get(i), paramValues.get(i));
}
expectedValue = paramMap.get("预期值");
} catch (Exception e) {
e.printStackTrace();
}
return expectedValue;
}
/**
* 获取某个TC的预期值，默认预期值的列名是：预期值
* @param excelPath
* @param excelIndex
* @param expectedValueColName 预期值的列名
* @return
*/
public static String getExpectedValue(String excelPath,int excelIndex,String expectedValueColName){
String expectedValue = null;
try {
if(excelIndex <= 1) throw new Exception("输入的excel行号必须大于1");
Map<String, ArrayList<String>> testcaselist = ReadXlsx_2(excelPath);
ArrayList<String> clNames = testcaselist.get("1");// 列名
ArrayList<String> paramValues = new ArrayList<String>();
paramValues = testcaselist.get(excelIndex + "");
Map<String, String> paramMap= new HashMap<String, String>();
for (int i = 1; i < clNames.size(); i++) {//除去用例名称
paramMap.put(clNames.get(i), paramValues.get(i));
}
expectedValue = paramMap.get(expectedValueColName);
} catch (Exception e) {
e.printStackTrace();
}
return expectedValue;
}
/**
* 获取第一个Sheet
*
* @param filePath
* @return
*/
private static XSSFSheet getSheet(String filePath) {
File file = new File(filePath);
XSSFSheet xssfSheet = null;
try {
OPCPackage opcPackage = OPCPackage.open(file);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(opcPackage);
xssfSheet = xssfWorkbook.getSheetAt(0);
} catch (Exception e) {
e.printStackTrace();
}
return xssfSheet;
}
/**
* 获每列的列名（即sheet的第一列）
*
* @param xssfSheet
* @return
*/
private static ArrayList<String> getColumnName(XSSFSheet xssfSheet) {
XSSFRow row_1 = xssfSheet.getRow(0);
ArrayList<String> colsName = new ArrayList<String>();
int lieNum = row_1.getPhysicalNumberOfCells();
for (int t = 0; t < lieNum; t++) {
colsName.add(row_1.getCell(t).getStringCellValue());
}
return colsName;
}
/**
* 读取测试用例excel文件，将所有的case保存在map中，以行号为key，从1开始包括第一行
* @param filePath
* @return
* @throws InvalidFormatException
* @throws IOException
*/
public static Map<String, ArrayList<String>> ReadXlsx_2(String filePath)
throws InvalidFormatException, IOException {
// XSSFSheet xssfSheet = getSheet(filePath);
//
// int totalRows = xssfSheet.getPhysicalNumberOfRows();// 得到总行数
// int rowstart = xssfSheet.getFirstRowNum();
// int rowEnd = xssfSheet.getLastRowNum();
// ////////////// 获取每列名称//////////////
// ArrayList<String> lieName = getColumnName(xssfSheet);
// int lieNum = lieName.size();
// ////////////////////////////
// Map<String, ArrayList<String>> testcaselist = new HashMap<String, ArrayList<String>>();
// for (int i = rowstart; i <= rowEnd; i++)// 获取每一行即一个TC（包括第一行）
// {
// XSSFRow row = xssfSheet.getRow(i);
// if (null == row)
// continue;
// ArrayList<String> paramValues = new ArrayList<String>();
// for (int t = 0; t < lieNum; t++) { // 获取每一列的值
//// String cellValue = "";
//// try {
//// cellValue = row.getCell(t).getStringCellValue();
//// } catch (Exception e) {
//// if ("Cannot get a text value from a numeric cell".equals(e.getMessage())) {
//// cellValue = (int)row.getCell(t).getNumericCellValue() + "";
//// }
//// }
//// paramValues.add(cellValue);
//
// String cellValue = "";
// XSSFCell cell = row.getCell(t);
// if (null == cell){
// paramValues.add("");
// continue;
// }
// switch (cell.getCellType()) {
// case HSSFCell.CELL_TYPE_NUMERIC: // 数字
//
// if (HSSFDateUtil.isCellDateFormatted(cell)) {
// long longtime = Math.round(cell.getNumericCellValue());
// cellValue = getTimeByLongTime(longtime);//将时间毫秒数转换成：yyyy-MM-dd HH:mm:ss 格式的字符串
// } else {
// double tempValue = cell.getNumericCellValue();
// long longVal = Math.round(tempValue);
// if (Double.parseDouble(longVal + ".0") == tempValue)
// cellValue = longVal + ""; //整数
// else
// cellValue = tempValue + "";//小数
// }
// paramValues.add(cellValue);
// break;
// case HSSFCell.CELL_TYPE_STRING: // 字符串
// cellValue = cell.getStringCellValue();
// paramValues.add(cellValue);
// break;
// case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
// cellValue = cell.getBooleanCellValue()+"";
// paramValues.add(cellValue);
// break;
// case HSSFCell.CELL_TYPE_FORMULA: // 公式
// cellValue = cell.getCellFormula()+"";
// paramValues.add(cellValue);
// break;
// case HSSFCell.CELL_TYPE_BLANK: // 空值
// cellValue = cell.getCellFormula()+"";
// paramValues.add(cellValue);
// break;
// case HSSFCell.CELL_TYPE_ERROR: // 故障
// cellValue = "";
// paramValues.add(cellValue);
// break;
// default:
// System.out.print("未知类型 ");
// cellValue = "";
// paramValues.add(cellValue);
// break;
// }// switch结束
// }
// String key = (i + 1) + "";// 以行号为key，从0开始
// testcaselist.put(key, paramValues);
// }
// return testcaselist;
Map<String, ArrayList<String>> testcaselist = new HashMap<String, ArrayList<String>>();
File file = new File(filePath);
try {
OPCPackage opcPackage = OPCPackage.open(file);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(opcPackage);
testcaselist = ReadXlsx_2(xssfWorkbook);
} catch (Exception e) {
e.printStackTrace();
}
return testcaselist;
}
/**
* 读取测试用例excel文件，将所有的case保存在map中，以行号为key，从1开始包括第一行
* @param wb excel工作簿
* @return
* @throws InvalidFormatException
* @throws IOException
*/
public static Map<String, ArrayList<String>> ReadXlsx_2(XSSFWorkbook wb)
throws InvalidFormatException, IOException {
XSSFSheet xssfSheet = wb.getSheetAt(0);//getSheet(filePath);
int totalRows = xssfSheet.getPhysicalNumberOfRows();// 得到总行数
int rowstart = xssfSheet.getFirstRowNum();
int rowEnd = xssfSheet.getLastRowNum();
////////////// 获取每列名称//////////////
ArrayList<String> lieName = getColumnName(xssfSheet);
int lieNum = lieName.size();
////////////////////////////
Map<String, ArrayList<String>> testcaselist = new HashMap<String, ArrayList<String>>();
for (int i = rowstart; i <= rowEnd; i++)// 获取每一行即一个TC（包括第一行）
{
XSSFRow row = xssfSheet.getRow(i);
if (null == row)
continue;
ArrayList<String> paramValues = new ArrayList<String>();
for (int t = 0; t < lieNum; t++) { // 获取每一列的值
// String cellValue = "";
// try {
// cellValue = row.getCell(t).getStringCellValue();
// } catch (Exception e) {
// if ("Cannot get a text value from a numeric cell".equals(e.getMessage())) {
// cellValue = (int)row.getCell(t).getNumericCellValue() + "";
// }
// }
// paramValues.add(cellValue);
String cellValue = "";
XSSFCell cell = row.getCell(t);
if (null == cell){
paramValues.add("");
continue;
}
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
if (HSSFDateUtil.isCellDateFormatted(cell)) {
String timeStr =cell.getDateCellValue().toString();
long longtime = Math.round(cell.getNumericCellValue());
cellValue = getTimeByLongTime(timeStr);//将时间毫秒数转换成：yyyy-MM-dd HH:mm:ss 格式的字符串
} else {
double tempValue = cell.getNumericCellValue();
long longVal = Math.round(tempValue);
if (Double.parseDouble(longVal + ".0") == tempValue)
cellValue = longVal + ""; //整数
else
cellValue = tempValue + "";//小数
}
paramValues.add(cellValue);
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
cellValue = cell.getStringCellValue();
paramValues.add(cellValue);
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue()+"";
paramValues.add(cellValue);
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
cellValue = cell.getCellFormula()+"";
paramValues.add(cellValue);
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
// cellValue = cell.getCellFormula()+"";
cell.setCellValue("");
// cellValue = cell.getCellFormula()+"";
paramValues.add("");
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
cellValue = "";
paramValues.add(cellValue);
break;
default:
System.out.print("未知类型 ");
cellValue = "";
paramValues.add(cellValue);
break;
}// switch结束
}
String key = (i + 1) + "";// 以行号为key，从0开始
testcaselist.put(key, paramValues);
}
return testcaselist;
}
public static InputStream loadExcel(String filePath){
InputStream myxlsx = null;
try {
myxlsx = new FileInputStream(filePath);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
return myxlsx;
}
public static XSSFWorkbook getWorkBook(String myxlsName){
XSSFWorkbook wb = null;
try {
wb = new XSSFWorkbook(loadExcel(myxlsName));
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
/**
* 修改指定单元格的内容
* @param myxls 以流的形式，先读excel的内容
* @param rowNum 单元格的行号，从0开始
* @param colNum 单元格的列号，从0开始
* @param cellContext 向指定单元格写入的内容
* @return
*/
public static boolean writeExcel(String myxls,int rowNum,int colNum,String cellContext){
XSSFWorkbook wb = getWorkBook(myxls);
XSSFSheet xssfSheet = null;
if(xssfSheet == null) xssfSheet = wb.getSheetAt(0);
try {
XSSFRow row = xssfSheet.getRow(rowNum);
if(row == null) throw new Exception("该sheet中不存在"+rowNum+"行");
XSSFCell cell = row.getCell(colNum);
if(cell == null) cell = row.createCell(colNum);//throw new Exception("该sheet中不存在"+rowNum+"行,"+colNum+"列");
cell.setCellValue("");
cell.setCellValue(cellContext);
FileOutputStream fileOut = null;
try{
fileOut = new FileOutputStream(myxls);
wb.write(fileOut);
}finally {
try {
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
return false;
}
}
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
/*
++++++++++++++++++++++++++++++++++++++++++++数据驱动模块相关+++++++++++++++++++++++++++++++
*/
/**
* TestNG 参数化方法，
* @param fileName excel文件路径
* @param sheetName sheet名称
* @return：返回二维数组对象，HashMap类型，列名(参数名称)为Key值，每行参数值为value。
*/
public static Object[][] readXlsx(String fileName, String sheetName) throws IOException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileName);
XSSFSheet xssfSheet = xssfWorkbook.getSheet(sheetName);
int totalRow = xssfSheet.getLastRowNum(); //文件最大行数
int totalColumn = xssfSheet.getRow(0).getLastCellNum(); //表头最大列数
// 为了返回值是Object[][],定义一个多行单列的二维数组
HashMap<String, String>[][] arr = new HashMap[totalRow][1];
// 对数组中所有元素hashmap进行初始化，除去header那行
if (totalRow > 1) {
for (int i = 0; i < totalRow; i++) {
arr[i][0] = new HashMap<String, String>();
}
} else {
System.out.println("excel中没有数据");
}
//获取表头内容
ArrayList<String> header = new ArrayList<String>();
for (int c = 0; c < totalColumn; c++) {
String cellValue = ExcelHandle.getCellValue(xssfSheet.getRow(0).getCell(c));
header.add(cellValue);
// System.out.println("###" + cellValue);
}
// 循环行Row
for (int rowNum = 1; rowNum <=totalRow; rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow == null) {
continue;
}
// 循环列Column
for (int columnNum = 0; columnNum < totalColumn; columnNum++) {
XSSFCell xssfCell = xssfRow.getCell(columnNum);
if (xssfCell != null) {
String cellValue = ExcelHandle.getCellValue(xssfSheet.getRow(rowNum).getCell(columnNum));
arr[rowNum - 1][0].put(header.get(columnNum), cellValue);
System.out.print(" " + getCellValue(xssfSheet.getRow(rowNum).getCell(columnNum)));
continue;
}
}
}
return arr;
}
/**
* 取表格值
* @param xssfCell XSSFCell类型对象，表示单元格。
* @return：返回单元格数值
*/
public static String getCellValue(XSSFCell xssfCell) {
if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfCell.getBooleanCellValue());
} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
return new BigDecimal(xssfCell.getNumericCellValue()).toPlainString();
// return String.valueOf(xssfCell.getNumericCellValue());
} else {
return String.valueOf(xssfCell.getStringCellValue());
}
}
@Test
public void testExcelReader() throws IOException {
String file = "src/main/resources/case/UserEmailService/TestSelectEmailBillCountByUserId.xlsx";;
Object a[][]=readXlsx(file, "工作表1");
}
}