TransWikia.com

Reading Excel cell values containing special (UTF-8) characters - Selenium/Java

Stack Overflow Asked by Vitesze on January 29, 2021

I have the following code:

    @Test(groups = {"customer"}, dataProvider = "customerData", dataProviderClass = Testdata.class, priority = 0)
    public void createCustomer(String cName, String cAddress, String cAddress2, String cCity, String cState, String cZip, String cContact, 
        String cPhone) throws InterruptedException {        
        NAV.customer.create(driver, wait, js);
        NAV.customer.edit(driver, wait, js, cName, cAddress, cAddress2, cCity, cState, cZip, cContact, cPhone);
        NAV.windowClose(driver, wait, js);
    }

and use the following code to read my Excel file:

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.DataFormatter;
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;

public class Testdata {
    public static Object[][] getExcel(String filepath, String sheetName) throws InvalidFormatException, IOException {
        FileInputStream file = new FileInputStream(filepath);
        XSSFWorkbook wb = new XSSFWorkbook(file);
        XSSFSheet sheet = wb.getSheet(sheetName);
        int rowCount = sheet.getLastRowNum();
        int column = sheet.getRow(0).getLastCellNum();
        Object[][] data = new Object[rowCount][column];
        for (int i = 1; i <= rowCount; i++) {
            XSSFRow row = sheet.getRow(i);
            for (int j = 0; j < column; j++) {
                XSSFCell cell = row.getCell(j);
                DataFormatter formatter = new DataFormatter();
                String val = formatter.formatCellValue(cell);
                data[i - 1][j] = val;
            }
        }
        wb.close();
        return data;
    }
}

I use Selenium 3.14 (UTF-8 text file encoding) and Apache POI 4.1.0. My Excel file is UTF-8 encoded and all cells are formatted as Text.

Unfortunately, certain special characters (in my case: é,ç and Î, but I’m sure there’s more) are not read correctly. Any help would be much appreciated.

One Answer

I'm not sure for your specific characters, but when I read in a sheet that has / [ ] * ? or : in it's name, for example, it replaces those invalid characters with a "_"

Try replacing the sheet name with "_" anywhere you have a special character.

Answered by rbmuhl on January 29, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP