How to solve the problem of POI importing pure numbers and other formats through java?

Poi imports pure numbers and other problems

When using poi to export excel, if the cell is set to pure numbers, once the input data is too large, it will automatically be displayed in scientific notation, resulting in errors in the imported data. The solution is to obtain the export file in the background and force-convert the cell attributes, which can be perfectly solved. It is also suitable for data import exceptions caused by other cell formats

Cell cellCode = r.getCell(1);
cellCode.setCellType(CellType.STRING);  
info.setCode(r.getCell(1).getStringCellValue());

poi Get Cell content: Number formatting

Today, I received a request from the business side, saying that the amounts imported by excel were all rounded off.

Then I checked the code: I was dazzled at the beginning, and I really couldn’t stand it. But think about it, as a programmer, you cannot refuse to serve the people. So, I debugged it. I was really lazy and didn’t want to chase the code directly, so I went to debug...

REASON

I found the specific reason:

if (xssfRow.getCellType() == Cell.CELL_TYPE_NUMERIC) {
    DecimalFormat format = new DecimalFormat("#");
    double num = xssfRow.getNumericCellValue();
    String res = format.format(num);
    //……
}

In the above code, Numbers are formatted as integers. Of course, there will be no problem if you get the value directly.

is as follows:

if (xssfRow.getCellType() == Cell.CELL_TYPE_NUMERIC) {
    DecimalFormat format = new DecimalFormat("#");
    double num = xssfRow.getNumericCellValue();
    String res = format.format(num);
    // num 和 res 的取值差不多。 如： 50.00 : num 为 50.00，res 为 50； 123.23， num 为123.23， res为123.23
    System.err.println(num + "--" + res);
    //……
}

DecimalFormat

DecimalFormat is a concrete subclass of NumberFormat, used to format decimal numbers. Help you format numbers into what you need as quickly as possible. DecimalFormat contains a pattern and a set of symbols.

The DecimalFormat class mainly relies on two placeholder symbols, # and 0, to specify the number length. Symbols like "

#.000". This mode means that there are four digits before the decimal point. If there are not enough, leave them blank. There are three digits after the decimal point. If there are not enough, fill them with 0.

Symbol meaning:

• 0 A number

• # A number, excluding 0

• . Decimal separator placeholder

• , group separator placeholder

• - Default negative prefix.

• % Multiply by 100 and display as a percentage

Example: ###

    public static void main(String[] args) {
        double pi=3.1415927;//圆周率
        //取一位整数
        System.out.println(new DecimalFormat("0").format(pi));//3
        //取一位整数和两位小数
        System.out.println(new DecimalFormat("0.00").format(pi));//3.14
        //取两位整数和三位小数，整数不足部分以0填补。
        System.out.println(new DecimalFormat("00.000").format(pi));//03.142
        //取所有整数部分
        System.out.println(new DecimalFormat("#").format(pi));//3
        //以百分比方式计数，并取两位小数
        System.out.println(new DecimalFormat("#.##%").format(pi));//314.16%
 
        long c=299792458;//光速
        //显示为科学计数法，并取五位小数
        System.out.println(new DecimalFormat("#.#####E0").format(c));//2.99792E8
        //显示为两位整数的科学计数法，并取四位小数
        System.out.println(new DecimalFormat("00.####E0").format(c));//29.9792E7
        //每三位以逗号进行分隔。
        System.out.println(new DecimalFormat(",###").format(c));//299,792,458
 
        System.out.println(new DecimalFormat("-###").format(c));//299,792,458
        System.out.println(new DecimalFormat("#.##?").format(c));//299,792,458
        //将格式嵌入文本
        System.out.println(new DecimalFormat("光速大小为每秒,###米").format(c)); //光速大小为每秒299,792,458米
    }

The above is the detailed content of How to solve the problem of POI importing pure numbers and other formats through java?. For more information, please follow other related articles on the PHP Chinese website!