How to remove / replace characters in a string in JAVA using Apache POI

Question:

I have an exel file with 2 columns: phone number and names. For example 380677777055 – OLEG And there are errors in the phone number when instead of "0" there is the letter "O" or a comma. How can I output all values ​​to a .txt file without errors? It does not work due to the fact that it changes characters in the string with names.

public class ExelToTxt {

public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd");
public static DataFormatter ndt = new DataFormatter();

public static void main(String[] args) throws IOException {

    FileInputStream fisStat = new FileInputStream("C:/Users/ё/Desktop/ТРА.xls");

    FileOutputStream fos = new FileOutputStream("my4.txt");

    Workbook wbStat = new HSSFWorkbook(fisStat);

    fisStat.close();

    Sheet sheetInputStat = wbStat.getSheetAt(0);

    StringBuffer sb = new StringBuffer();

    int i = 0;

    for (Row row : sheetInputStat) {

        Cell cellNumber = row.getCell(i);
        Cell cellInquiry = row.getCell(i + 1);
        Cell cellInquiry1 = row.getCell(i + 2);
        Cell cellInquiry2 = row.getCell(i + 3);
        Cell cellInquiry3 = row.getCell(i + 4);
        Cell cellInquiry4 = row.getCell(i + 5);
        Cell cellInquiry5 = row.getCell(i + 6);
        Cell cellInquiry6 = row.getCell(i + 7);
        Cell cellInquiry7 = row.getCell(i + 8);
        Cell cellInquiry8 = row.getCell(i + 9);
        Cell cellInquiry9 = row.getCell(i + 10);

        NullVerify(cellNumber,sb);
        NullVerify(cellInquiry,sb);
        NullVerify(cellInquiry1,sb);
        NullVerify(cellInquiry2,sb);
        NullVerify(cellInquiry3,sb);
        NullVerify(cellInquiry4,sb);
        NullVerify(cellInquiry5,sb);
        NullVerify(cellInquiry6,sb);
        NullVerify(cellInquiry7,sb);
        NullVerify(cellInquiry8,sb);
        NullVerify(cellInquiry9,sb);

        sb.append(System.getProperty("line.separator"));

    }

    fos.flush();
    fos.write(sb.toString().getBytes());
    fos.close();
}

static void replace(char[] arr, char find, char replace) {
    for (int i = 0; i < arr.length; i++) {
        while (arr[i] == find) {
            arr[i] = replace;
        }
        while (arr[i] == ',') {
            arr[i] =  '!';
        }
    }
}

static void NullVerify(Cell CellName,StringBuffer stb) {
if (CellName != null) {
    stb.append(getCellText(CellName));
    stb.append("\t");

}
}

// берем строковое значение из любой ячейки
public static String getCellText(Cell cell) {

    String result = "";


    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        result = cell.getRichStringCellValue().getString();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            result = sdf.format(cell.getDateCellValue());
        } else {
            result = ndt.formatCellValue(cell);
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        result = Boolean.toString(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        result = cell.getCellFormula().toString();
        break;
    case Cell.CELL_TYPE_BLANK:
        break;
    default:
        break;
    }
    return result;
}

Answer:

HSSFWorkbook wb = null;
try (InputStream in = new FileInputStream(args[0])) {
    wb = new HSSFWorkbook(in);
}

DataFormatter formatter = new DataFormatter();
Sheet sheet = wb.getSheetAt(0);
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
    Cell cell = sheet.getRow(i).getCell(0);
    String value = formatter.formatCellValue(cell);
    cell.setCellValue(value.replace("O", "0"));
}

try (OutputStream out = new FileOutputStream(args[0])) {
    wb.write(out);
}
Scroll to Top