Read Excel file without changing its content

Question

I am trying to upload and read contents of an Excel file. It works fine if the content is in String format. But my content in the excel file is as follows:

02:02:02
03:03:03
04:04:04

All I want to do is retrieve the content as it is. But when I pull this data over, I end up getting the content as follows:

8.4745370370370374E-2
0.12711805555555555
0.169490740740741

Is there any way around to not change the content?

while (rowIterator.hasNext())
    {
        Row row = rowIterator.next();
        //For each row, iterate through all the columns
        Iterator<Cell> cellIterator = row.cellIterator();

        while (cellIterator.hasNext())
        {
            Cell cell = cellIterator.next();
            cell.setCellType(Cell.CELL_TYPE_STRING);
            //Check the cell type and format accordingly
            switch (cell.getCellType())
            {
                case Cell.CELL_TYPE_NUMERIC:
                System.out.print(cell.getNumericCellValue());
                break;

                case Cell.CELL_TYPE_STRING:
                System.out.print(cell.getStringCellValue());
                break;
            }
        }
    }

you are not altering the content on read, it's just that Excel is "formatting" it when you look at it in Excel. See also this question: http://stackoverflow.com/questions/3014803/apache-poi-time-cell — , Mar 23 '16 at 19:15
@RC. I don't have any formulas ongoing on that file. Just plain data typed in as above. So I should resolve this on the excel sheet itself before even reading it? Based on that link, seems POI assumes that is some sort of date format but in my case it is 02:03:04 ie: 2 mins 3 seconds 4 milisecs. — kar, Mar 23 '16 at 19:21
I never told anything about formulas; formatted time is a number in excel, try this: in the next cell type `=+0` (and read the linked question answer). If you can modify the Excel file you can force Excel to "use text" by using `'02:03:04` (note the leading quote) — , Mar 23 '16 at 19:24
@RC. The leading quote resolves the issue. I can modify the excel. But ends up being an additional process just to add that quote. Trying to avoid it. Getting head around the link answer. — kar, Mar 23 '16 at 19:36

score 2 · Accepted Answer · edited May 23 '17 at 12:23

2

Your cell format is probably not set to Text. Check this out:

As you can see, when you do not explicit set the cell type to text, excel tries to guess it. So when you are importing it, it is probably importing as Time. You may read it as Text, by explicit setting the cell type to Text and then typing the time 02:02:02, or you may read it as excel Time format and then convert it. Find out more about reading excel dates: How to read Excel cell having Date with Apache POI? Reading date values from excel cell using POI HSSF API

edited May 23 '17 at 12:23

Community

1
1

answered Mar 23 '16 at 19:36

João Menighin

3,083
6
38
80

Tried with text and Number. Same issues. – kar Mar 23 '16 at 19:43
Did you change the cell type to text, typed the value again and saved the file before importing it? – João Menighin Mar 23 '16 at 19:49
1

My bad, you were right. I happened to have made a single cell text instead of all cells. – kar Mar 23 '16 at 19:53
1

No problem. I do recomend you to read it as time/date though. When the user is filling the excel sheet he probably won't care about these types and expect your program to deal with it. Cheers. – João Menighin Mar 23 '16 at 20:00

Read Excel file without changing its content

1 Answers1