Sử dụng Maven để cài thưu viện POI apache trong pom.xml của dự án maven của bạn. Hiện Anh Tester đang sử dụng java poi phiên bản 5.x, tất cả các phiên bản mới nhất có thể được tìm thấy tại đây
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
Tạo class để lưu các hàm xử lý Excel. Cụ thể An đặt là ExcelHelpers.
package anhtester.common.helpers;
import java.awt.Color;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.ss.usermodel.*;
public class ExcelHelpers {
private FileInputStream fis;
private FileOutputStream fileOut;
private Workbook wb;
private Sheet sh;
private Cell cell;
private Row row;
private CellStyle cellstyle;
private Color mycolor;
private String excelFilePath;
private Map<String, Integer> columns = new HashMap<>();
public void setExcelFile(String ExcelPath, String SheetName) throws Exception {
try {
File f = new File(ExcelPath);
if (!f.exists()) {
f.createNewFile();
System.out.println("File doesn't exist, so created!");
}
fis = new FileInputStream(ExcelPath);
wb = WorkbookFactory.create(fis);
sh = wb.getSheet(SheetName);
//sh = wb.getSheetAt(0); //0 - index of 1st sheet
if (sh == null) {
sh = wb.createSheet(SheetName);
}
this.excelFilePath = ExcelPath;
//adding all the column header names to the map 'columns'
sh.getRow(0).forEach(cell ->{
columns.put(cell.getStringCellValue(), cell.getColumnIndex());
});
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
public String getCellData(int rownum, int colnum) throws Exception{
try{
cell = sh.getRow(rownum).getCell(colnum);
String CellData = null;
switch (cell.getCellType()){
case STRING:
CellData = cell.getStringCellValue();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell))
{
CellData = String.valueOf(cell.getDateCellValue());
}
else
{
CellData = String.valueOf((long)cell.getNumericCellValue());
}
break;
case BOOLEAN:
CellData = Boolean.toString(cell.getBooleanCellValue());
break;
case BLANK:
CellData = "";
break;
}
return CellData;
}catch (Exception e){
return"";
}
}
//Gọi ra hàm này nè
public String getCellData(String columnName, int rownum) throws Exception {
return getCellData(rownum, columns.get(columnName));
}
public void setCellData(String text, int rownum, int colnum) throws Exception {
try{
row = sh.getRow(rownum);
if(row ==null)
{
row = sh.createRow(rownum);
}
cell = row.getCell(colnum);
if (cell == null) {
cell = row.createCell(colnum);
}
cell.setCellValue(text);
fileOut = new FileOutputStream(excelFilePath);
wb.write(fileOut);
fileOut.flush();
fileOut.close();
}catch(Exception e){
throw (e);
}
}
}
@Test
public void signInPage() throws Exception {
// Setup đường dẫn của file excel
excel.setExcelFile("src/test/resources/Book1.xlsx", "Sheet1");
signInPage = new SignInPage(driver);
driver.get("https://crm.anhtester.com");
// Đọc data từ file excel
signInPage.signIn(excel.getCellData("username", 2), excel.getCellData("password", 2));
//Ghi data vào file excel
excel.setCellData("anhtester.com", 5, 0);
// Chú ý: dòng và cột trong code nó hiểu bắt đầu từ 0
Thread.sleep(2000);
}
@Test
public void signInPageReadExcelDynamic() throws Exception {
//Setup đường dẫn của file excel
excel.setExcelFile("src/test/resources/Book1.xlsx", "Sheet1");
signInPage = new SignInPage(driver);
driver.get("https://crm.anhtester.com");
for (int i = 0; i < 6; i++) {
signInPage.signIn(excel.getCellData("username", i), excel.getCellData("password", i));
Thread.sleep(1000);
}
// Ghi nhiều dòng vào file
for (int i = 0; i < 6; i++) {
excel.setCellData("AN01", i, 3);
}
}
Severity: Notice
Message: Undefined variable: new
Filename: post/post_detail.php
Line Number: 382
Backtrace:
File: /home/anhtest2/public_html/application/views/frontend/post/post_detail.php
Line: 382
Function: _error_handler
File: /home/anhtest2/public_html/application/views/frontend/layout/layout_view.php
Line: 361
Function: view
File: /home/anhtest2/public_html/application/core/MY_Controller.php
Line: 34
Function: view
File: /home/anhtest2/public_html/application/controllers/frontend/Post.php
Line: 59
Function: render
File: /home/anhtest2/public_html/index.php
Line: 315
Function: require_once
Severity: Notice
Message: Trying to get property 'slug' of non-object
Filename: post/post_detail.php
Line Number: 382
Backtrace:
File: /home/anhtest2/public_html/application/views/frontend/post/post_detail.php
Line: 382
Function: _error_handler
File: /home/anhtest2/public_html/application/views/frontend/layout/layout_view.php
Line: 361
Function: view
File: /home/anhtest2/public_html/application/core/MY_Controller.php
Line: 34
Function: view
File: /home/anhtest2/public_html/application/controllers/frontend/Post.php
Line: 59
Function: render
File: /home/anhtest2/public_html/index.php
Line: 315
Function: require_once
Anh Tester
Đường dẫu khó chân vẫn cần bước đi
Đời dẫu khổ tâm vẫn cần nghĩ thấu