How to understand the core api of manipulating excel in the series of test-driven technologies

This article mainly explains the "test-driven technology series of how to understand the core api of manipulating excel". The content of the article is simple and clear, and it is easy to learn and understand. please follow the editor's train of thought to study and learn how to understand the core api of manipulating excel.

The test data format is shown below (similar to junit4):

@ DataProvider public Object [] [] dp1 () {return new Object [] [] {new Object [] {1jue 1,0}, new Object [] {2,1jue 1}, new Object [] {2,1Magne2},};}

As you can see, if the amount of data is small, this form is OK, but if the amount of test data is large, then it is obviously not efficient to maintain test data in the code! In data-driven work, the test data is usually saved in excel, and then the excel is read and written. Here, take java as an example. I'll familiarize you with the use of the poi-ooxml jar package and explain to you how this jar package manipulates the core api of excel.

Poi-ooxml jar introduction

Jar package Import

The pom configuration information in Maven is as follows

Org.apache.poi poi-ooxml 4.1.2

First, several concepts are introduced. The whole excel file is called workbook, each worksheet is called Sheet, and each cell is called cell.

Api detailed explanation

XSSFWorkbookworkbook = new XSSFWorkbook (new FileInputStream ("E:\\ test.xlsx"))

Open a document in xls format

HSSFWorkbookworkbook = new HSSFWorkbook (newFileInputStream ("E:\\ test.xls"))

Close the document

Workbook.close ()

Get the worksheet through id. 0 represents the first worksheet.

Sheet sheet = workbook.getSheetAt (0)

Get the worksheet by its name

Sheet sheet = workbook.getSheet ("test1")

Create a sheet named test2

Workbook.createSheet ("test2")

Create a sheet, named test3, and copy the values from the first sheet

Workbook.cloneSheet (0, "test3")

Get the number of valid data rows in sheet

Int iRowNum=sheet.getPhysicalNumberOfRows ()

Get the number of valid data columns in sheet

Int iColumnNum=sheet.getRow (0) .getPhysicalNumberOfCells ()

Get the data type of the first row of the first column

CellTypetype=sheet.getRow (1) .getCell (0) .getCellType ()

The data types of Cell include:

NUMERIC, numerical type

STRING, string type

BLANK, null

BOOLEAN, Boolean type

String value=sheet.getRow (1) .getCell (0) .getStringCellValue ()

Set the data of the first row of the first column to kevin1

Sheet.getRow (1) .getCell (0) .setCellValue ("kevin1")

Note: if writing to excel or creating sheet tables are involved, for example:

Workbook.createSheet ("test2"); workbook.cloneSheet (0, "test3"); sheet.getRow (1) .getCell (0) .setCellValue ("kevin1")

You must add the following three statements

FileOutputStreamis = new FileOutputStream ("E:\\ test.xls"); workbook.write (is); is.close ()

Complete code

Import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook Stringvalue= sheet.getRow (1) .getCell (0) .getStringCellValue (); / / get the data value of the first row of the first column System.out.println (value); sheet.getRow (1) .getCell (0) .setCellValue ("kevin1") / / set the data in the first row of the first column to kevin1 / / if writing to excel or creating an sheet table is involved, you must add the following three statements: FileOutputStreamis = new FileOutputStream ("E:\\ test.xls"); workbook.write (is); is.close (); workbook.close () }} Thank you for your reading. the above is the content of "how to understand the core api of manipulating excel". After the study of this article, I believe you have a deeper understanding of how to understand the core api of manipulating excel in the series of test-driven technology, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!