CODEWITHSUNDEEP
javaexcelapache-poi
Harpreet
Harpreet

Reputation: 63

Apache POI Data Formatter unable to apply custom format "0.0 p.p.;(0.0 p.p.)"

In my Excel file that I am trying to convert using Apache POI, I have a cell that has numeric value as -3.97819466831428 and Custom format as "0.0 p.p.;(0.0 p.p.)". So, in Excel the value that is displayed is "(4.0 p.p.)"

When I convert the same using POI library, I get the output as: "(4.0 p" How can I get the same value as in Excel: (4.0 p.p.) ?

The way I am using DataFormatter is:

val = dataFormatter.formatRawCellContents(cell.getNumericCellValue(), style.getDataFormat(), style.getDataFormatString());

I believe the problem is coming from the usage of "p.p." in the data format string, especially the dots. When I print the data format string from POI using style.getDataFormatString(), I get the format as "0.0\ \p.\p.;(0.0\ \p.\p.\)".

Even if I manually change the format string to use "0.0;(0.0\ \p\.\p\.\)", still its the same result. So, I am out of ideas now. How can I get the full result back from data-formatter like in Excel as "(4.0 p.p.)" ?

Another question that I have is: Is it possible using Apache POI to get the actual displayed value in Excel file? Like in this case, is it possible to get the value "(4.0 p.p.)" directly from Excel without having to apply any data formatting in POI?

Upvotes: 0

Views: 840

Answers (1)

Axel Richter
Axel Richter

Reputation: 61852

This is an error in apache poi's DataFormatter while translating the Excel number format 0.0\ \p.\p.;\(0.0\ \p.\p.\) into a java.text.Format. The correct corresponding java.text.Format would be a new java.text.DecimalFormat("0.0' p.p.';(0.0' p.p.')"). But apache poi's DataFormatter fails to translate this properly.

You should file a bug report to apache poi about this. In that bug report you should provide a working example (full Java code and a sample Excel file) to reproduce the issue.

As a workaround one can tell DataFormatter how single special Excel number formats shall be translated. For this use the method DataFormatter.addFormat.

Example:

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

import java.io.FileInputStream;

class DataFormatterAddFormat {

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

  Workbook workbook = WorkbookFactory.create(new FileInputStream("ExcelExample.xlsx"));

  DataFormatter dataFormatter = new DataFormatter();
  FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

  dataFormatter.addFormat("0.0 p.p.;(0.0 p.p.)", 
   new java.text.DecimalFormat("0.0' p.p.';(0.0' p.p.')"));
  dataFormatter.addFormat("0.0\" p.p.\";\\(0.0\" p.p.\"\\)", 
   new java.text.DecimalFormat("0.0' p.p.';(0.0' p.p.')"));
  dataFormatter.addFormat("0.0\\ \\p.\\p.;\\(0.0\\ \\p.\\p.\\)", 
   new java.text.DecimalFormat("0.0' p.p.';(0.0' p.p.')"));

  Sheet sheet = workbook.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {

     String value = dataFormatter.formatCellValue(cell, formulaEvaluator);
     System.out.println(value);

   }
  }
  workbook.close();
 }
}

This is now able correct translating the added special number formats. Of course this is not really the final solution since the need here is to catch all possible Excel formats which have to be translated. That's why the hint to file a bug report to apache poi.

Btw.: The Excel format 0.0" p.p.";\(0.0" p.p."\) would be more general for this. It avoids confusing the dot (.) in p.p. with the decimal separator.

To your question about getting the formatted value directly from the Excel file: This is not possible. All Excel versions store values and styles separate. Numeric values are always stored as floating point values in double precision. Number formats for those values are stored in a separate styles section of the file. So best practice to get cell values styled as in Excel using apache poi is using DataFormatter.formatCellValue as shown in my code sample.

Upvotes: 1

Related Questions