Excel is more or less inseparable from the statistics of test cases, test progress and various test reports in everyone's work. We can use Excel formulas to achieve automated statistics, and of course, we can also use code to achieve statistics of Excel data.
Today, I share an open-source Excel parsing tool EasyExcel from Alibaba, and finally implement a project that automatically reads Excel test cases and performs interface testing in combination with interface automation.
EasyExcel is a Java-based, fast and concise Excel processing tool that solves the memory overflow of large files. It allows you to quickly complete Excel's read and write functions without considering performance, memory and other factors.
Why EasyExcel?
The more famous frameworks for Java parsing and Excel generation are Apache poi and jxl. But they all have a serious problem, that is, they consume a lot of memory. Poi has a set of SAX mode API s that can solve some memory overflow problems to a certain extent, but POI still has some defects, such as the 07 version of Excel decompression and storage after decompression. It is done in memory, and the memory consumption is still very large. easyexcel has rewritten poi's analysis of the 07 version of Excel. A 3M excel still needs about 100M memory to analyze with POI sax. Using easyexcel can reduce it to a few megabytes, and no matter how large Excel is, there will be no memory overflow, allowing users to Simpler and more convenient.
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
Official website: https://easyexcel.opensource.alibaba.com/
Github: https://github.com/alibaba/easyexcel
How to use EasyExcel?
Before introducing the reading of Excel, we need to understand the steps of EasyExcel to operate the file. Taking reading Excel as an example, you can refer to the following three steps:
- Create an entity object corresponding to excel
- Since excel is read line by line by default, you need to create a callback listener for excel line by line
- Just read it
If our automation framework design idea is to save our use case in Excel, then if the use case content includes the following.
We first create a new TestCase class with only four fields: no, api, request, and expect.
Note: The order of the property fields in the Java class is the same as the order of the header fields in Excel, you can also not write @ExcelProperty
package cn.qa.excelUtil.entity;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class TestCase {
// Read based on the specified column name or column index in Excel
@ExcelProperty(value = "no", index = 0)
private String no;
@ExcelProperty(value = "api", index = 1)
private String api;
@ExcelProperty(value = "request", index = 2)
private String request;
@ExcelProperty(value = "expect", index = 3)
private String expect;
}
In addition, write a TestFileUtil for the unified storage path of Excel files. We will put the test Excel files under a specific folder.
package cn.qa.excelUtil.util;
import java.io.File;
import java.io.InputStream;
public class TestFileUtil {
public static InputStream getResourcesFileInputStream(String fileName) {
return Thread.currentThread().getContextClassLoader().getResourceAsStream("" + fileName);
}
public static String getPath() {
return TestFileUtil.class.getResource("/").getPath();
}
public static File createNewFile(String pathName) {
File file = new File(getPath() + pathName);
if (file.exists()) {
file.delete();
} else {
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
}
return file;
}
public static File readFile(String pathName) {
return new File(getPath() + pathName);
}
public static File readUserHomeFile(String pathName) {
return new File(System.getProperty("user.home") + File.separator + pathName);
}
}
Read Excel
Writing method 1: simply read Excel
// since: 3.0.0-beta1
String fileName = TestFileUtil.getPath()+ "demo" + File.separator + "TestCase.xlsx";
// Here you need to specify which class to use to read, and then read the first sheet file stream will be automatically closed
// Here, 100 pieces of data will be read each time, and then come back and call the data directly.
EasyExcel.read(fileName, TestCase.class, new PageReadListener<TestCase>(dataList -> {
for (TestCase TestCase : dataList) {
log.info("read a piece of data{}", JSON.toJSONString(TestCase));
}
})).sheet().doRead();
Writing method 2: read Excel data and temporarily store the cache
String fileName = TestFileUtil.getPath() + "demo" + File.separator + "TestCase.xlsx";
// Here you need to specify which class to use to read, and then read the first sheet file stream will be automatically closed
EasyExcel.read(fileName, TestCase.class, new ReadListener<TestCase>() {
/**
* The amount of data cached at a time
*/
public static final int BATCH_COUNT = 100;
/**
*temporary storage
*/
private List<TestCase> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
@Override
public void invoke(TestCase data, AnalysisContext context) {
cachedDataList.add(data);
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// Store complete cleanup list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
}
private void saveData() {
log.info("{}Bar data, start storing database!", cachedDataList.size());
log.info("Storage database success!");
}
}).sheet().doRead();
Write Excel
Method 1: The simplest way to write one, write data in existing Excel
package cn.qa.WriteExcelUtil;
import cn.qa.excelUtil.entity.TestCase;
import cn.qa.excelUtil.util.TestFileUtil;
import com.alibaba.excel.EasyExcel;
import java.io.File;
import java.util.ArrayList;
import java.util.List;
public class WriteExcelUtil {
public static List<TestCase> getTestCaseData(){
List<TestCase> testCases = new ArrayList<>();
for (int i = 1; i <= 10; i++) {
TestCase testCase = TestCase.builder()
.no(String.valueOf(i))
.api("api_" + String.valueOf(i))
.request("testRequest")
.expect("success")
.build();
testCases.add(testCase);
}
return testCases;
}
public static void main(String[] args) {
String filename = TestFileUtil.getPath() + "demo" + File.separator + "TestCase.xlsx";
// Write data to Excel You can also specify a data template through head(Class<?>)
EasyExcel.write(filename, TestCase.class)
.sheet("Case")
.doWrite(getTestCaseData());
}
}
Method 2: The simplest way of writing 2, create a new Excel and write
public static void main(String[] args) {
String filename = TestFileUtil.getPath() + "demo" + File.separator + "TestCase.xlsx";
// Create an ExcelWriter object
ExcelWriter excelWriter = EasyExcel.write(filename, TestCase.class).build();
// Create a Sheet object
WriteSheet writeSheet = EasyExcel.writerSheet("Case").build();
// Write data to Excel
excelWriter.write(getTestCaseData(), writeSheet);
// close stream
excelWriter.finish();
}
Implement automated testing with automation frameworks
Implementation idea: read the test case in Excel, call the interface through the unified initiator of the interface, and then make an assertion on the return information of the interface.
1. The Invoke code of the interface initiator is as follows (changed from the previous code, that is, adding a return value to invokeProxy, because an assertion will be made later):
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
public class Invoke {
public Object invokeProxy(String serviceName, String api, String request) {
try {
Class<?> serviceClass = Class.forName(serviceName);
Object service = serviceClass.getDeclaredConstructor().newInstance();
Method method = serviceClass.getMethod(api, String.class);
return method.invoke(service, request);
} catch (ClassNotFoundException e){
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
return null;
}
}
Second, read the automation use case code
package cn.qa.excelUtil;
import cn.qa.excelUtil.entity.TestCase;
import cn.qa.excelUtil.util.TestFileUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import java.io.File;
import java.util.ArrayList;
import java.util.List;
public class ReadCaseUtil {
public static List<TestCase> readCase(String caseFile){
String fileName = TestFileUtil.getPath()+ "demo" + File.separator + caseFile;
List<TestCase> result = new ArrayList<>();
// Here you need to specify which class to use to read, and then read the first sheet file stream will be automatically closed
EasyExcel.read(fileName, TestCase.class, new PageReadListener<TestCase>(dataList -> {
for (TestCase TestCase : dataList) {
result.add(TestCase);
}
})).sheet().doRead();
return result;
}
}
3. Development use cases
@Test
public void testInvokeCase(){
String CreateServiceName = "cn.qa.reflect.demo.service.CreateService";
String QueryServiceName = "cn.qa.reflect.demo.service.QueryService";
// Step1: Read the automation use case
List<TestCase> testCaseList = ReadCaseUtil.readCase("TestCase.xlsx");
for (TestCase testCase: testCaseList){
if (testCase.getApi().equals("create")){
// Step2: Initiate a service call
Object response = invokeProxy(CreateServiceName, testCase.getApi(), testCase.getRequest());
// Step3: Assert
Assert.assertTrue(response.toString().contains(testCase.getExpect()));
}
if (testCase.getApi().equals("query")){
Object response = invokeProxy(QueryServiceName, testCase.getApi(), testCase.getRequest());
Assert.assertTrue(response.toString().contains(testCase.getExpect()));
}
}
}
Software test engineer self-study tutorial:
Interface performance test - software testers must be able to analyze 618 actual combat scenarios
Jmeter practical explanation case - software testers must know
I recommend a [Python automated testing exchange group: 746506216], where you can discuss and communicate software testing together, learn software testing techniques, interviews and other aspects of software testing, and help you quickly advance to advanced Python automated testing/test development, leading to a high salary.
Friends who like software testing, if my blog is helpful to you, if you like the content of my blog, please "Like", "Comment" and "Favorite" in one click!