2

I am new for Apache Poi. I have to read the excel with multi columns. i did something but i got unexpected result. please find the sample input and code with output.

I have to read particular column and get that column values was presented in excel.

Sample Input :

enter image description here

Code :

FileInputStream file = new FileInputStream(new File(path));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        DataFormatter dataFormatter = new DataFormatter();

        for(Row row : sheet){
            Cell cell = row.getCell(6);
            String s = dataFormatter.formatCellValue(cell);
            System.out.println(s + "\n");
             }

Output : 

Product ID

835,885

891,875,895,897,888

924,894

897,895,898,869

897,899,893,888,890

897,891

897,869

.......

967,617,691,688,976,1,366,128,136

But, i want below output,

8,35,885
89,18,75,89,58,97,888
9,24,894
8,97,89,58,98,869
89,78,99,89,38,88,890
8,97,891
8,97,869
......
967,617,691,688,976,1,366,128,136

I am googled the solution but i can't able to find out. And also i tried (POI, JEXCEL, Aspose) some logic but it was give different output. So, please help me to solve the problem.

Kannan Arumugam
  • 1,119
  • 2
  • 18
  • 27
  • 1
    What is the format string both in Excel and POI? – jmarkmurphy Oct 23 '17 at 19:40
  • for "Product ID" -> General and "8,35,885,..." -> #,##0 – Kannan Arumugam Oct 23 '17 at 19:56
  • What version of Apache POI are you using? And if not the latest, what happens when you upgrade? – Gagravarr Oct 23 '17 at 20:00
  • poi-3.9.jar, poi-ooxml-3.9.jar, poi-ooxml-schemas-3.9.jar, dom4j-1.6.1.jar. i am not upgrade with latest version. versrion is problem? – Kannan Arumugam Oct 23 '17 at 20:05
  • upgrade with latest version of jar file like 3.17 but it was return same result. – Kannan Arumugam Oct 23 '17 at 20:23
  • 1
    If in `Excel` the number format `#,##0` leads to `8,35,885` for a number 835885, then your `Excel` runs in a locale where the `Windows` locale settings have set this special digit group setting. See [Number Formatting](https://learn.microsoft.com/en-us/globalization/locale/number-formatting) - Digit grouping. But `apache poi` cannot know in which locale your `Excel` is running. And even if it could know, `Java`' s [DecimalFormat](https://docs.oracle.com/javase/7/docs/api/java/text/DecimalFormat.html) is not able to produce such a special digit grouping. – Axel Richter Oct 24 '17 at 03:34
  • Try to save those values as String in the EXCEL file and then, retrieve them using cell.getStringCellValue(); – ervidio Oct 24 '17 at 10:20
  • With Aspose.Cells, you may get your desired output using Cell.getStringValue() method. – Amjad Sahi Oct 24 '17 at 17:57
  • What Locale did you set Apache POI to? POI needs to know to use Indian number formatting, as this isn't its default behavior. https://en.wikipedia.org/wiki/Indian_numbering_system – IceArdor Oct 26 '17 at 07:53

0 Answers0