首页 文章

JAVA Apache POI自定义格式

提问于
浏览
0

我正在使用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文件中的自定义格式 . 我想在我的代码中显示单元格值 .

custom format

如您所见,文件中可见的单元格值为“031642153283700100” . 此外,当我在此特定单元格上显示时,值将更改为“42153283700100”(未应用自定义格式的数据) .

EDIT

原始单元格类型为CELL_TYPE_NUMERIC .

Original cell in .xlsx file
==>
Clicked Cell


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

我试过了 :

  • cell.toString()=> "42153283700100"

  • cell.getNumericCellValue()=> "42153283700100"

使用以前的单元格类型转换

cell.setCellType(Cell.CELL_TYPE_STRING);
  • cell.getStringCellValue()=> "42153283700100"

  • cell.getRichStringCellValue()=>“42153283700100”

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

2 回答

  • 0

    对于数字格式,您应该使用 CellGeneralFormatterCellNumberFormatter .

    例:

    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

    好的,让我们有一个更一般的例子 . 以上内容不适用于日期(我已经知道的),但也不适用于所有数字格式 . 后者我认为 CellNumberFormatter 应该这样做,但事实并非如此 . 不幸的是,它甚至会产生一些适当数字格式的错误 .

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

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

    由于 CellNumberFormatter 没有正确处理,我们应该在使用 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) {
      }
     }
    }
    
  • 2

    你可以试试这个

    row = sheet.getRow(RowNo);
               // RowNo on which Row cell should have appeared
               cell = row.getCell(CellNo);
               // CellNo on which Position cell should have appeared on Row
               cell.setCellValue("031642153283700100");
    
      // You could also get CellFormula by using cell.getCellFormula();
    

相关问题