NỘI DUNG BÀI HỌC

✅ Đọc File Excel để lấy data test
✅ Ghi data mới vào lại File Excel


Cài đặt thư viện Apache Poi

Sử dụng Maven để cài thư viện Apache POI vào pom.xml của dự án Maven của bạn. Hiện tại Anh Tester đang sử dụng Apache Poi phiên bản 5.x.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.2.2</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.2</version>
    </dependency>

(Cập nhật ngày: 20/03/2022)

Bản Apache POI Common mới này cần có thư viện Commons IO bổ trợ mới chạy êm được:

<!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.11.0</version>
</dependency>


Tiếp theo tạo class để lưu các hàm xử lý Excel. Cụ thể An đặt là ExcelHelpers. Bỏ trong package helpers chung với PropertiesHelper ở bài trước.

Hàm set Excel file theo Sheet cần dùng

Tại đây đặt tên hàm là setExcelFile với 2 đối số là đường dẫn đến Excel PathSheet Name

package excel;

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());
        }
    }
}

 

Hàm get Cell data từ Excel File

Hàm getCellData(String columnName, int rownum) là hàm chúng ta sẽ gọi ra để dùng. Còn hàm getCellData(int rownum, int colnum) để xử lý thôi. Kiểu viết lại cho nó gọn ý mà.

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));
}


🔆 Gọi ra dùng lại thì gọi hàm setExcelFile trước để khai báo File Excel cần dùng xong đến gọi getCellData để lấy data theo từng ô


Chuẩn bị file Excel như sau:

[Selenium Java] Bài 24: Đọc File Excel để lấy data test | Anh Tester

Ví dụ đọc từ file testData.xlsx tại Sheet1 với các giá trị bên dưới (nhớ chú ý link kẻo sai)

  • cột username dòng 1 (dòng và cột bắt đầu từ 0) nghĩa là thứ tự 2 của excel á
  • cột password dòng 1
  • cột pin dòng 1

public static void main(String []args) throws Exception {
    ExcelHelpers excel = new ExcelHelpers();
    excel.setExcelFile("./testData.xlsx", "Sheet1");
    
    System.out.println(excel.getCellData("userName", 1));
    System.out.println(excel.getCellData("password", 1));
    System.out.println(excel.getCellData("pin", 1));
}


Hàm ghi data vào lại File Excel theo từng Cell

Bạn chỉ cần truyền tham số tuần tự cho hàm setCellData(String text, int rownum, int column)
(giá trị cần ghi - vị trí dòng - vị trí cột)

Hoặc hàm setCellData(String text, int rownum, String columnName) lấy theo tên cột

    // Write data to excel sheet

    //set by column index
    public void setCellData(String text, int rowNumber, int colNumber) {
        try {
            row = sheet.getRow(rowNumber);
            if (row == null) {
                row = sheet.createRow(rowNumber);
            }
            cell = row.getCell(colNumber);

            if (cell == null) {
                cell = row.createCell(colNumber);
            }
            cell.setCellValue(text);

            XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
            text = text.trim().toLowerCase();
            if (text == "pass" || text == "passed") {
                style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
            }
            if (text == "fail" || text == "passed") {
                style.setFillForegroundColor(IndexedColors.RED.getIndex());
            }
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);

            cell.setCellStyle(style);

            fileOut = new FileOutputStream(excelFilePath);
            workbook.write(fileOut);
            fileOut.flush();
            fileOut.close();
        } catch (Exception e) {
            e.getMessage();
        }
    }

    //set by column name
    public void setCellData(String text, int rowNumber, String columnName) {
        try {
            row = sheet.getRow(rowNumber);
            if (row == null) {
                row = sheet.createRow(rowNumber);
            }
            cell = row.getCell(columns.get(columnName));

            if (cell == null) {
                cell = row.createCell(columns.get(columnName));
            }
            cell.setCellValue(text);

            XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
            text = text.trim().toLowerCase();
            if (text == "pass" || text == "passed") {
                style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
            }
            if (text == "fail" || text == "passed") {
                style.setFillForegroundColor(IndexedColors.RED.getIndex());
            }

            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);

            cell.setCellStyle(style);

            fileOut = new FileOutputStream(excelFilePath);
            workbook.write(fileOut);
            fileOut.flush();
            fileOut.close();
        } catch (Exception e) {
            e.getMessage();
        }
    }


🔆 Gọi hàm setCellData ra dùng lại:

public static void main(String []args) throws Exception {
    ExcelHelpers excel = new ExcelHelpers();
    excel.setExcelFile("./testData.xlsx", "Sheet1");

    //Ghi giá trị "pass" vào dòng 1 cột 3 (dòng và cột bắt đầu tính từ 0)
    excel.setCellData("pass", 1, 3);
}


🔆 Kết quả như bên dưới:

[Selenium Java] Bài 24: Đọc File Excel để lấy data test | Anh Tester


Yeah xong rồi đó. Mở rộng hơn thì chúng ra có thể lấy ra nhiều dòng thì sẽ dùng vòng lặp FORDataProvider của TestNG cung cấp.

DataProvider sẽ học tiếp theo ở bài sau nhé !!

Teacher

Teacher

Anh Tester

Software 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

Cộng đồng Automation Testing Việt Nam:

🌱 Telegram Automation Testing:   Cộng đồng Automation Testing
🌱 
Facebook Group Automation: Cộng đồng Automation Testing Việt Nam
🌱 
Facebook Fanpage: Cộng đồng Automation Testing Việt Nam - Selenium
🌱 Telegram
Manual Testing:   Cộng đồng Manual Testing
🌱 
Facebook Group Manual: Cộng đồng Manual Testing Việt Nam

Chia sẻ khóa học lên trang

Bạn có thể đăng khóa học của chính bạn lên trang Anh Tester để kiếm tiền

Danh sách bài học