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

Question

I have an exel file in which there are 2 columns: a phone number and names. For example 380677777055 - OLEG And in the phone number there are errors when instead of "0" there is a letter "O" or a comma. How can I output all values to the .txt file without errors? It does not work because 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; }

I wrote a method by which I try to change the situation, but it changes all the letters "O" to zeros
Why write a method if the String already implements replace?
In any case, it will change all the values, and I need only in the column of phone numbers.

Accepted Answer · 2018-07-23T07:16:12

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

@KonstantinConstantin This complicates the task of just two lines.
does not work, look at my code, it displays all values from the Exel file, but does not edit it.
Your code is bad and redundant, it needs to be rewritten completely, not edited.
In addition, there is not a single line responsible for changing data in cells or writing a modified table to a file.

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

1 answer 1

More articles: