JAVA Apache POI 自定义格式 [英] JAVA Apache POI Custom Format

问题描述

我使用的是 poi 版本:3.14

我访问一个 Excel (.xlsx) 文件

this.workbook = WorkbookFactory.create(new FileInputStream(f.getPath()));this.workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);this.sheet = workbook.getSheetAt(0);

这是在应用于单元格的 .xlsx 文件中找到的自定义格式.我想在我的代码中显示单元格值.

如您所见，文件中可见的单元格值为031642153283700100".同样，当我单击此特定单元格时，值会更改为42153283700100"(未应用自定义格式的数据).

编辑

原始单元格类型为 CELL_TYPE_NUMERIC.

==>

<小时>

如何在java代码中显示格式化的值031642153283700100"?

我试过:

• cell.toString() => "42153283700100"
• cell.getNumericCellValue() => "42153283700100"

使用之前的单元格类型转换:

cell.setCellType(Cell.CELL_TYPE_STRING);

• cell.getStringCellValue() => "42153283700100"

• cell.getRichStringCellValue() => "42153283700100"

• 带有 formatCellValue(cell) 方法的旧 HSSFDataFormatter => "421532837001000316"

解决方案

对于数字格式，您应该使用 CellGeneralFormatter 或 CellNumberFormatter.

示例:

import org.apache.poi.ss.usermodel.*;导入 org.apache.poi.openxml4j.exceptions.InvalidFormatException;导入 org.apache.poi.ss.format.*;导入 java.io.IOException;导入 java.io.FileNotFoundException;导入 java.io.InputStream;导入 java.io.FileInputStream;类 ReadExcelWithFormats {公共静态无效主(字符串 [] args){尝试 {InputStream inp = new FileInputStream("workbook.xlsx");工作簿 wb = WorkbookFactory.create(inp);Sheet sheet = wb.getSheetAt(0);for(行行:工作表){对于(单元格单元格:行){String formatstring = cell.getCellStyle().getDataFormatString();System.out.println(formatstring);开关(cell.getCellType()){//...案例 Cell.CELL_TYPE_NUMERIC:double cellvalue = cell.getNumericCellValue();System.out.println(cellvalue);字符串格式化值 = "";if ("general".equals(formatstring.toLowerCase())) {formattedvalue = new CellGeneralFormatter().format(cellvalue);} 别的 {formattedvalue = new CellNumberFormatter(formatstring).format(cellvalue);}System.out.println(格式化值);休息;//...默认:System.out.println();}}}} catch (InvalidFormatException ifex) {} catch (FileNotFoundException fnfex) {} 捕捉(IOException ioex){}}}

编辑

好的，让我们举一个更一般的例子.以上不适用于日期(我已经知道)，也不适用于所有数字格式.后者我认为 CellNumberFormatter 应该做但它没有.不幸的是，它甚至会产生一些正确的数字格式错误.

在 Excel 中，数字格式最多可以包含 4 个以分号分隔的部分.第一部分为大于 0 的数字，第二部分为小于 0 的数字，第三部分为等于 0 的数字，第四部分为文本.

<代码>格式>0;格式<0;format = 0;text

由于 CellNumberFormatter 没有正确处理这个问题，我们应该在使用 CellNumberFormatter 之前进行处理.

import org.apache.poi.ss.usermodel.*;导入 org.apache.poi.openxml4j.exceptions.InvalidFormatException;导入 org.apache.poi.ss.format.*;导入 java.io.IOException;导入 java.io.FileNotFoundException;导入 java.io.InputStream;导入 java.io.FileInputStream;导入 java.util.Date;类 ReadExcelWithFormats {公共静态无效主(字符串 [] args){尝试 {InputStream inp = new FileInputStream("workbook.xlsx");工作簿 wb = WorkbookFactory.create(inp);Sheet sheet = wb.getSheetAt(0);for(行行:工作表){对于(单元格单元格:行){String formatstring = cell.getCellStyle().getDataFormatString();System.out.println(formatstring);开关(cell.getCellType()){//...案例 Cell.CELL_TYPE_NUMERIC:字符串格式化值 = "";String[] formatstringparts = formatstring.split(";");如果(DateUtil.isCellDateFormatted(单元格)){日期日期 = cell.getDateCellValue();System.out.println(日期);字符串日期格式字符串 = "";if (cell.getCellStyle().getDataFormat() == 14) {//没有显式格式的默认短日期dateformatstring = "yyyy-MM-dd";//默认日期格式} else if (cell.getCellStyle().getDataFormat() == 22) {//没有显式格式的默认短日期时间dateformatstring = "yyyy-MM-dd hh:mm";//默认日期时间格式} else {//具有显式格式的其他数据格式dateformatstring = formatstringparts[0];}formattedvalue = new CellDateFormatter(dateformatstring).format(date);} 别的 {double cellvalue = cell.getNumericCellValue();System.out.println(cellvalue);开关(格式字符串部分.长度){案例4:案例3:如果(单元格值 > 0){if ("general".equals(formatstringparts[0].toLowerCase())) {formattedvalue = new CellGeneralFormatter().format(cellvalue);} 别的 {formattedvalue = new CellNumberFormatter(formatstringparts[0]).format(cellvalue);}}如果(单元格值<0){if ("general".equals(formatstringparts[1].toLowerCase())) {formattedvalue = new CellGeneralFormatter().format(cellvalue);} 别的 {formattedvalue = new CellNumberFormatter(formatstringparts[1]).format(cellvalue);}}如果(单元格值 == 0){if ("general".equals(formatstringparts[2].toLowerCase())) {formattedvalue = new CellGeneralFormatter().format(cellvalue);} 别的 {formattedvalue = new CellNumberFormatter(formatstringparts[2]).format(cellvalue);}}休息;案例2:如果(单元格值 >= 0){if ("general".equals(formatstringparts[0].toLowerCase())) {formattedvalue = new CellGeneralFormatter().format(cellvalue);} 别的 {formattedvalue = new CellNumberFormatter(formatstringparts[0]).format(cellvalue);}}如果(单元格值<0){if ("general".equals(formatstringparts[1].toLowerCase())) {formattedvalue = new CellGeneralFormatter().format(cellvalue);} 别的 {formattedvalue = new CellNumberFormatter(formatstringparts[1]).format(cellvalue);}}休息;默认:if ("general".equals(formatstringparts[0].toLowerCase())) {formattedvalue = new CellGeneralFormatter().format(cellvalue);} 别的 {formattedvalue = new CellNumberFormatter(formatstringparts[0]).format(cellvalue);}}}System.out.println(格式化值);休息;//...默认:System.out.println();}}}} catch (InvalidFormatException ifex) {} catch (FileNotFoundException fnfex) {} 捕捉(IOException ioex){}}}

<小时>

重要提示:

此代码示例来自 2016 年，适用于 apache poi 3.14.

从当前的 apache poi 版本 3.17 和 4.x.y 开始，不应再使用它.在当前版本中，这很简单:

<预><代码>...InputStream inp = new FileInputStream("workbook.xlsx");工作簿 wb = WorkbookFactory.create(inp);Sheet sheet = wb.getSheetAt(0);DataFormatter formatter = new DataFormatter();for(行行:工作表){对于(单元格单元格:行){String formattedvalue = formatter.formatCellValue(cell);System.out.println("使用DataFormatter:" + formattedvalue);}}...

I am Using poi version : 3.14

I access an Excel (.xlsx) file

this.workbook = WorkbookFactory.create(new FileInputStream(f.getPath()));
this.workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);
this.sheet = workbook.getSheetAt(0);

Here is a custom format found in .xlsx file applied to a cell. I want to display the cell value in my code.

As you can see the cell value visible in file is "031642153283700100". Also when i clic on this particular cell, value is changing to "42153283700100" (data without custom format applied on).

EDIT

Original cell type is CELL_TYPE_NUMERIC.

==>

---

How can i display the formatted value "031642153283700100" in java code ?

I tried :

• cell.toString() => "42153283700100"
• cell.getNumericCellValue() => "42153283700100"

With previous cell type conversion :

cell.setCellType(Cell.CELL_TYPE_STRING);

• cell.getStringCellValue() => "42153283700100"

• cell.getRichStringCellValue() => "42153283700100"

• Old HSSFDataFormatter with formatCellValue(cell) method => "421532837001000316"

解决方案

For number formats you should use CellGeneralFormatter or CellNumberFormatter.

Example:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

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

import java.io.IOException;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.io.FileInputStream;

class ReadExcelWithFormats {

 public static void main(String[] args) {
  try {

   InputStream inp = new FileInputStream("workbook.xlsx");
   Workbook wb = WorkbookFactory.create(inp);

   Sheet sheet = wb.getSheetAt(0);

   for (Row row : sheet) {

    for (Cell cell : row) {

     String formatstring = cell.getCellStyle().getDataFormatString();
     System.out.println(formatstring);

     switch (cell.getCellType()) {

      //...

      case Cell.CELL_TYPE_NUMERIC:
       double cellvalue = cell.getNumericCellValue();

       System.out.println(cellvalue);

       String formattedvalue = "";

       if ("general".equals(formatstring.toLowerCase())) {
        formattedvalue = new CellGeneralFormatter().format(cellvalue);
       } else {
        formattedvalue = new CellNumberFormatter(formatstring).format(cellvalue);
       }

       System.out.println(formattedvalue);

      break;

      //...

      default:
       System.out.println();
     }
    }
   }

  } catch (InvalidFormatException ifex) {
  } catch (FileNotFoundException fnfex) {
  } catch (IOException ioex) {
  }
 }
}

Edit

OK, let's have a more general example. The above will not work with dates (what I had known already) but also not with all number formats. The latter I thought the CellNumberFormatter should do but it does not. Unfortunately it will even produce errors with some proper number formats.

In Excel a number format can contain up to 4 parts delimited with semicolon. The first part is for numbers greater than 0, the second part is for numbers lower than 0, the third part is for numbers equal 0 and the fourth part is for text.

format > 0;format < 0;format = 0;text

Since the CellNumberFormatter does not handle this properly, we should do it before using the CellNumberFormatter.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

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

import java.io.IOException;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.io.FileInputStream;

import java.util.Date;

class ReadExcelWithFormats {

 public static void main(String[] args) {
  try {

   InputStream inp = new FileInputStream("workbook.xlsx");
   Workbook wb = WorkbookFactory.create(inp);

   Sheet sheet = wb.getSheetAt(0);

   for (Row row : sheet) {

    for (Cell cell : row) {

     String formatstring = cell.getCellStyle().getDataFormatString();
     System.out.println(formatstring);

     switch (cell.getCellType()) {

      //...

      case Cell.CELL_TYPE_NUMERIC:

       String formattedvalue = "";
       String[] formatstringparts = formatstring.split(";");

       if (DateUtil.isCellDateFormatted(cell)) {

        Date date = cell.getDateCellValue();
        System.out.println(date);

        String dateformatstring = "";
        if (cell.getCellStyle().getDataFormat() == 14) { //default short date without explicit formatting
         dateformatstring = "yyyy-MM-dd"; //default date format for this
        } else if (cell.getCellStyle().getDataFormat() == 22) { //default short datetime without explicit formatting
         dateformatstring = "yyyy-MM-dd hh:mm"; //default datetime format for this
        } else { //other data formats with explicit formatting
         dateformatstring = formatstringparts[0];
        }
        formattedvalue = new CellDateFormatter(dateformatstring).format(date);
       } else {

        double cellvalue = cell.getNumericCellValue();
        System.out.println(cellvalue);

        switch (formatstringparts.length) {
         case 4:
         case 3:
          if (cellvalue > 0) {
           if ("general".equals(formatstringparts[0].toLowerCase())) {
            formattedvalue = new CellGeneralFormatter().format(cellvalue);
           } else {
            formattedvalue = new CellNumberFormatter(formatstringparts[0]).format(cellvalue);
           }
          }
          if (cellvalue < 0) {
           if ("general".equals(formatstringparts[1].toLowerCase())) {
            formattedvalue = new CellGeneralFormatter().format(cellvalue);
           } else {
            formattedvalue = new CellNumberFormatter(formatstringparts[1]).format(cellvalue);
           }
          }
          if (cellvalue == 0) {
           if ("general".equals(formatstringparts[2].toLowerCase())) {
            formattedvalue = new CellGeneralFormatter().format(cellvalue);
           } else {
            formattedvalue = new CellNumberFormatter(formatstringparts[2]).format(cellvalue);
           }
          }
         break;
         case 2:
          if (cellvalue >= 0) {
           if ("general".equals(formatstringparts[0].toLowerCase())) {
            formattedvalue = new CellGeneralFormatter().format(cellvalue);
           } else {
            formattedvalue = new CellNumberFormatter(formatstringparts[0]).format(cellvalue);
           }
          }
          if (cellvalue < 0) {
           if ("general".equals(formatstringparts[1].toLowerCase())) {
            formattedvalue = new CellGeneralFormatter().format(cellvalue);
           } else {
            formattedvalue = new CellNumberFormatter(formatstringparts[1]).format(cellvalue);
           }
          }
         break;
         default:
          if ("general".equals(formatstringparts[0].toLowerCase())) {
           formattedvalue = new CellGeneralFormatter().format(cellvalue);
          } else {
           formattedvalue = new CellNumberFormatter(formatstringparts[0]).format(cellvalue);
          }    
         }

       }

       System.out.println(formattedvalue);

      break;

      //...

      default:
       System.out.println();
     }
    }
   }

  } catch (InvalidFormatException ifex) {
  } catch (FileNotFoundException fnfex) {
  } catch (IOException ioex) {
  }
 }
}

---

Important note:

This code sample is from 2016 and is for apache poi 3.14.

As of the current apache poi versions 3.17 and 4.x.y this should not be used anymore. In current versions this is as simple as:

...
   InputStream inp = new FileInputStream("workbook.xlsx");
   Workbook wb = WorkbookFactory.create(inp);

   Sheet sheet = wb.getSheetAt(0);

   DataFormatter formatter = new DataFormatter();

   for (Row row : sheet) {
    for (Cell cell : row) {
     String formattedvalue = formatter.formatCellValue(cell);
     System.out.println("Using DataFormatter: " + formattedvalue);
    }
   }
...

这篇关于JAVA Apache POI 自定义格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！