Why, when reading an excel file, the number instead of "." is written ","?

Question

Java

Hi all. I read the xls file, there is a column (numerical format), in which the values ​​are separated by a dot.


Then I load the file into groovy (in other words, the same java, who is unfamiliar) and the value is entered separated by commas, after which the whole program crashes, since I need to get data through a dot, which are then processed. How to fix?

Code snippet:

import groovy.sql.Sql
import java.sql.Connection
import java.sql.DriverManager

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

class XM_PARSE_XLS {

    def execute(Connection conn, InputStream p_file, String p_filename) {

        Sql sql = new Sql(conn);

        HSSFWorkbook wb = new HSSFWorkbook(p_file);

        ArrayList<HashMap<String, String>> arrAllData = new ArrayList<HashMap<String, String>>();

        String strsql
        Integer cntStr = 0

        // идем по листам в файле
        wb.each { HSSFSheet myExcelSheet ->
            DataFormatter formatter = new DataFormatter();

            // идем по страницам файла
            myExcelSheet.each{  Row myrow ->

                HashMap<String, String> hm = new HashMap<String, String>();

                if (cntStr >= 0) {
                    // идем по строкам с данными
                    Integer numCell = 0;
                    String typ = '';

                    myrow.each { Cell mycell ->
                        String value = ""; // приводит любые ячейки к строковому формату

                        // если тип Строка
                        if (mycell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            value = mycell.getStringCellValue();
                            typ = 'S'
                            // если тип число
                        }  else {
                            // а для числовых ячеек или ячеек даты значение будет отформатировано на основе правил форматирования / стиля, примененных к ячейке, а затем возвращено как строка
                            value = formatter.formatCellValue(mycell);
                            typ = 'N'
                        }
                        if (cntStr == 0) {
                            Boolean Err = false;
                            switch(numCell) {
                                case 0:
                                    Err=(value.toUpperCase()!='ELS');
                                    break;
                                case 1:
                                    Err=(value.toUpperCase()!='ST_CODE');
                                    break;
                                case 2:
                                    Err=(value.toUpperCase()!='SUMMA');
                                    break;
                                case 3:
                                    Err=(value.toUpperCase()!='REP_DATE');
                                    break;
                            }
                            numCell = numCell + 1;
                            if (Err) {
                                throw new Exception("Неверный заголовок столбца "+ value)
                            }
                        } else {
                            if((typ!='N')&&(numCell>0)&&(numCell<3)){
                                throw new Exception("Неверные данные для загрузки "+ value)
                            }
                            switch(numCell) {
                                case 0:
                                    hm.put("els", value);
                                    break;
                                case 1:
                                    hm.put("st_code", value);
                                    break;
                                case 2:
                                    hm.put("summa", value = 0.00);
                                    break;
                                case 3:
                                    hm.put("rep_date", value);
                                    break;
                            }
                            numCell = numCell + 1;

                        }

                    }
                }
                if(hm) arrAllData.add(hm);
                cntStr = cntStr + 1;
            }
        }

        for (int i = 1; i < arrAllData.size(); i++) {
            HashMap <String, String> hm = arrAllData.get(i);

Answer 1

DataFormatter formatter = new DataFormatter(Locale.US);

Answer 2

100% due to regional settings in Win, by default there is a comma

Answer 3

This is the most common mistake almost everywhere.
Here are the numbers in en_US and ru_RU
1,000.00 1,000.00
So
use the correct locale.
https://howtodoinjava.com/java/date-time/java-loca...
In addition, when sending to the database, also check in what format the data is, or you will be in for extremely unpleasant surprises like: And everything worked for me!

Answer 4

you go into the group policy settings, create a policy applied to groups or individual users ... go to the regional settings, everything will be highlighted in a red square, choose what you need to change (in your case, change the comma to a dot) press F5 and you can easily change. .. because if highlighted in red, the policy will not apply!