Read International Currency symbols from excel sheet in java poi 4.1.2

Question

I am unable to read international currency and accounting number format from excel sheet into java standalone application using poi 4.1.2 (XSSF). Only able to read US locale symbol but unable to read other currency symbols in java. Using currency symbol only some formats cell value is displayed in java DataFormatter, other formats are displayed with ?(Eg: I/P: $10.00 O/P: ?10.00).

Accounting Number format unable to read Euro currency symbol(Exception: Illegal Argument Exception) and some currency symbols displaying cell data(currency symbol and value). code:

for(int i=1;i<=rows;i++){

             String ExcelFilename = sheet.getRow(i).getCell(<cell no of file>).getRichStringCellValue().getString().trim();  
         
             if(ExcelFilename.equals("<file name>")) {
             
                 for(int j=0;j<columns;j++) {

                      DataFormatter formatter = new DataFormatter();
                     cell = sheet.getRow(i).getCell(j,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

                     String  check= "";                      
                      switch (cell.getCellType()) {
                         case BLANK:
                             check=  formatter.formatCellValue(cell);
                             break;
                          case NUMERIC:
                             check=  formatter.formatCellValue(cell);                                
                             break;
                        case BOOLEAN:
                             check =formatter.formatCellValue(cell);
                             break;
                        case STRING:
                             check=formatter.formatCellValue(cell);
                             break;                        
                        case FORMULA:
                             check=  formatter.formatCellValue(cell);                                                       
                             break;                         
                        default:                              
                             break;
                    }       
                }
            }

}

Exception:

1. € 100.00 - Euro
   from Excel sheet Accounting-> currency type as Euro -> after running class I am getting below exception.

org.apache.poi.ss.format.CellFormat WARNING: Invalid format: "_ [$€-2] * #,##0.00_ ;" java.lang.IllegalArgumentException: Unsupported [] format block '[' in '_ [$€-2] * #,##0.00_' with c2: null

Similarly, I am getting exception for some other Accounting format currency symbols also. Input (Excel sheet)- output(Java) - 100

Excel sheet(info.xlsx):

Output should display without exception and with cell data(symbol and numeric value) in java.

question from:https://stackoverflow.com/questions/65599926/read-international-currency-symbols-from-excel-sheet-in-java-poi-4-1-2

Answer 1

There really seems to be a bug in DataFormatter when currency € (EURO) is used with € sign placed in front of the value like € 1,234.56. All default Euro using states normally don't do so. They are writing currency like 1,234.56 €, so € sign placed behind the value.

If the Number format Currency or Accounting using symbol € Euro (€ 123) is used in Excel, then Excel creates a number format like [$€-2] #,##0.00. That means currency symbol € placed in front of the value. But apache poi interprets that 2 as a country code like in [$￡-809]#,##0.00 where the 809 means Great Britain. So because of that misinterpretation it fails since it does not find a country for code 2.

A workaround could be replacing all "[$u20AC-2]" in data-format string with "u20AC". That is replacing all [$€-2] with only the € sign.

Example:

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.PrintWriter;

class ReadExcelUsingDataFormatter {

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

  Workbook workbook = WorkbookFactory.create(new FileInputStream("ExcelExample.xlsx"));
  
  DataFormat format = workbook.createDataFormat();
  
  DataFormatter dataFormatter = new DataFormatter();
    
  PrintWriter writer = new PrintWriter("Result.txt", "UTF-8");

  Sheet sheet = workbook.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {
     try {
         String dataFormatString = cell.getCellStyle().getDataFormatString();
         if (dataFormatString.contains("[$u20AC-2]")) {
             System.out.println(dataFormatString);
             dataFormatString = dataFormatString.replace("[$u20AC-2]", "u20AC");
             System.out.println(dataFormatString);
             cell.getCellStyle().setDataFormat(format.getFormat(dataFormatString));
         }
         String value = dataFormatter.formatCellValue(cell);
         System.out.println(value); //This might not be printed correctly because of unicode deficiency of `System.out`. But `Result.txt` should contain it corrcetly.
         writer.print(value + "");
     } catch (Exception ex) {
         ex.printStackTrace();
     }

   }
   writer.println();
  }
  writer.close();
  workbook.close();
 }
}