XLSX JAVA Read file using Hashmap

Question

I was told to use hashmap in this code to allow a XLSX file to be uploaded and be able to be read on the website. The data I key into the XLSX has to be captured on the website too. As for now, there's a null exception error.
This is how my code looks like now.

     OPCPackage pkg = null;
     XSSFWorkbook workbook = null;
     XSSFSheet sheet = null;

    try {
        pkg = OPCPackage.open("C:\\Users\\....Load_AcctCntr_Template.xlsx");
        System.out.println(_LOC + "1.0 " + " pkg:" + pkg);
        workbook = new XSSFWorkbook(pkg);
        System.out.println(_LOC + "1.0 " + " workbook:" + workbook);
        sheet = workbook.getSheetAt(Sheetindex);
        System.out.println(_LOC + "1.0 " + " sheet:" + sheet);
        pkg.close();
    } catch (Exception e) {
        System.out.println(_LOC + "1.0 " + " Test:");
        return _m;
    }   

    System.out.println(_LOC + "1.0 " + " sheet.getRow(0):" + sheet.getRow(0));
    int _totalc = sheet.getRow(0).getLastCellNum();
    int _totalr = sheet.getLastRowNum();


           // Header r=0
           String[] st = new String[_totalc];
           for (int c = 0; c < _totalc; c++) {
              st[c] = sheet.getRow(0).getCell(0).getStringCellValue();

    }

        _m.put("HEADER", st);
        System.out.println(_LOC + "1.0 " + " _m:" + _m);

        // Data r=1 thereafter
        List list = new ArrayList();
        for (int r = 1; r < _totalr; r++) {
            Object[] o = new Object[_totalc];
            String strRow = null;

            for (int c = 0; c < _totalc; c++) {
                o[c] = sheet.getRow(r).getCell(c);
                String cn = o[c].getClass().getName();
                String strCell = null;

                if (!isEmptyNull(strCell)) {
                    strRow = "record_available";
                }
            }

            if ((o != null) && (o.length != 0)) {
                list.add(o);

            }
        }
        _m.put("DATA", list);
        System.out.println(_LOC + "1.0 " + " _m:" + _m);
        return _m;
    }
    return _m;
}

What is shown on my console:

   SystemOut     O [RedirectLogin: requiredRights]1.0/BelsizeWeb/faces/module/entity/en1102.xhtml
   SystemOut     O [RedirectLogin: requiredRights]1.0en1102.xhtml
   SystemOut     O [En1102: doEn1102_command_readfileAction]1.0
   SystemOut     O [PageCodeBase: getConstructJXLList]1.0  pkg:org.apache.poi.openxml4j.opc.ZipPackage@a6742ad
   SystemOut     O [PageCodeBase: getConstructJXLList]1.0  workbook:Name: /xl/workbook.xml - Content Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml
   SystemOut     O [PageCodeBase: getConstructJXLList]1.0  sheet:Name: /xl/worksheets/sheet1.xml - Content Type: application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml
   SystemOut     O [PageCodeBase: getConstructJXLList]1.0  sheet.getRow(0):<xml-fragment r="1" spans="1:7" x14ac:dyDescent="0.25" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

 Caused by: java.lang.NullPointerException
at pagecode.PageCodeBase.getConstructJXLList_xlsx(PageCodeBase.java:6527)
at pagecode.module.entity.En1102.doEn1102_command_readfileAction(En1102.java:166)

To start with, the methods `getCell`, `getRows` and `getColumns` are not implemented and don't return any meaningful values. Once implemented please do more investigation and describe what exactly doesn't work for you. Try to restrict your question to the specific problem rather than posting a large block of code which doesn't work for some reason. Also when using Java try to follow Java naming conventions: http://java.about.com/od/javasyntax/a/nameconventions.htm and use meaningful names for your variables (`_s_c` is almost as expressive as `_w_t_f`) — Norbert Radyk, May 29 '14 at 08:58
@NorbertRadyk because these codes are pass down by the previous batch and I am suppose to edit it. So sorry for that. Initially, there isn't any methods for `getCell`, `getRows` and `getColumns`. But I added it because I was told not to use jxl and when I change some codes to be non-jxl, red underline appeared on `getColumns`, `getRows` and `getCell` saying that "The method `getRows()`,`getColumns`, `getCell` is undefined for the type PageCodeBase." So I tried to get the "help"/"quick fix" given and that's how I ended up with those methods. — Cassie, May 29 '14 at 09:06
Cassie, I'm trying to understand your question. What is `PageCodeBase` and where are you getting the errors you describe? There is so much going on here that you haven't shown us - I don't think you'll ever get a reasonable answer to the question in this form. Also, _nobody_ prefixes variable names with underscores any more; it just makes everything much harder to read. — Dawood ibn Kareem, May 29 '14 at 21:03
@DavidWallace `PageCodeBase` is the name of the java file. I'll try to type it clearly again (Really sorry as I am not good in coding and not good in explaining too). These are the codes for xls previously. So I changed the codes to make it work for xlsx. Previously, the line of code for `int _totalc = getColumns();` suppose to be like this `int _totalc = sheet.getColumns();` but I took away the `sheet` as the error said "method getColumns() is undefined for XSSFSheet." So I am wondering how do I solve this error of allowing getColumn to be defined for XSSFSheet. — Cassie, May 30 '14 at 01:28
@DavidWallace because if I were to take away `sheet`, the only way left given by the "help" is to create method but I am unsure of what to put into the `getColumn` method if I were to create it. — Cassie, May 30 '14 at 01:29
Right. I've just done some research. To get the number of rows, you need `sheet.getLastRowNum()`. If the result of this is not zero, then to get the number of columns, you can write `sheet.getRow(0).getLastCellNum()`. You could use these expressions instead of `getRows()` and `getColumns()` in your current code, or you could wrap them up in methods `getRows()` and `getColumns()`. — Dawood ibn Kareem, May 30 '14 at 02:44
@DavidWallace Sorry to ask again, what about `getCell`? Is it the same too? — Cassie, May 30 '14 at 02:58
Umm, you need to call `sheet.getRow(r).getCell(c)` for this. Note that those indexes start at 0, so the top left cell would be `sheet.getRow(0).getCell(0)`. — Dawood ibn Kareem, May 30 '14 at 03:06
@DavidWallace Sorry I realised I have miss out one line under getCell for header. I have edited the codes above. Hmm top left cell refers to the one under "Header"? It doesn't work for me. It says "The method getContents() is undefined for the type XSSFCell." — Cassie, May 30 '14 at 03:14
Right. Once you've got the cell there are a few different methods you can use to turn it into a String. The one you want is probably `getStringCellValue()`. Have a look [here](https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCell.html) — Dawood ibn Kareem, May 30 '14 at 03:16
@DavidWallace Sorry I don't quite understand this part. Which means I would need to create a method for `getStringCellValue()`? — Cassie, May 30 '14 at 03:24
I mean, if you want to get the contents of cell C3 as a String, you might write `String contentsOfC3 = sheet.getRow(2).getCell(2).getStringCellValue();` — Dawood ibn Kareem, May 30 '14 at 03:27
@DavidWallace Ahh okay I got it. Thanks for being patient with me. Thank you so much for the help! — Cassie, May 30 '14 at 03:52
Hey @DavidWallace , sorry I got another question. Why is there a java.lang.NullPointerException error on the line `int _totalc = sheet.getRow(0).getLastCellNum();` ? I tried to type in other numbers other than 0 but the nullexeption error is still there. — Cassie, May 30 '14 at 05:55
@DavidWallace it didn't show any error. It just shows Caused by: java.lang.NullPointerException at pagecode.PageCodeBase.getConstructJXLList_xlsx(PageCodeBase.java:6491) -- which is `int _totalc = sheet.getRow(0).getLastCellNum();` on my console after I run the codes. — Cassie, May 30 '14 at 06:05
Are you able to run this with a debugger and see what comes back from `sheet.getLastRowNum()`? — Dawood ibn Kareem, May 30 '14 at 06:32
@DavidWallace Unresolved compilation problems: :The local variable _totalr may not have been initialized. The local variable _totalc may not have been initialized — Cassie, May 30 '14 at 06:55
I'm here. This is a bit tricky to explain. Do you know what those errors mean? — Dawood ibn Kareem, May 30 '14 at 08:57
@DavidWallace Hi. Hmm I think I might need to leave this section on hold first because there is some problem for my previous code. — Cassie, May 30 '14 at 09:06
@DavidWallace Hi David, is it ok for me to edit my codes above again? I think I know where's the problem already and now, after editing the codes, there's another problem.... :/ or maybe we can move the discussion to chat instead? — Cassie, May 30 '14 at 09:35
Hi Cassie, I don't think you have enough reputation points to chat. I think it's probably OK to change the code in your question when nobody has answered yet, because you won't be invalidating anyone's work. Or you could just ask another question. Whichever is better for you. — Dawood ibn Kareem, May 30 '14 at 09:53
@DavidWallace Ok I have edited the codes. I suspect there's something wrong with the line `workbook = new XSSFWorkbook(pkg);` and `sheet = workbook.getSheetAt(Sheetindex);` because it doesn't seem to capture the println. it doesnt capture println for my header and data too — Cassie, May 30 '14 at 10:04
OK, so you're getting an exception thrown, but because you're not printing it, we have no idea what it is. Can you please add `e.printStackTrace();` immediately after `} catch (Exception e) {` then run it again and paste the output into the question? — Dawood ibn Kareem, May 30 '14 at 10:08
@DavidWallace it says "Package should contain a content type part [M1.13]" but my excel is already in xlsx format. — Cassie, Jun 02 '14 at 01:49
OK, I'm guessing you've read [this](http://stackoverflow.com/q/6758364) and [this](http://stackoverflow.com/q/21992071) and [this](http://stackoverflow.com/q/10142282) already? — Dawood ibn Kareem, Jun 02 '14 at 01:54
@DavidWallace Yes. I even tried it on again but it still doesn't print the workbook and sheet println. The error "...[M1.13]" still appear. — Cassie, Jun 02 '14 at 02:12
I'm sorry, Cassie, I'm really starting to run out of ideas. That error message is what I'd expect if the XLSX file is empty, or isn't a valid workbook. I presume you've tried opening it directly in Excel. If none of the three posts that I linked to in my last comment are any help, then I don't know what is going wrong. If you let me see the whole stack trace though (as per my comment 2 days ago), I'll have a look at the POI source and see if I can work out what's causing this. — Dawood ibn Kareem, Jun 02 '14 at 02:24
@DavidWallace yup it's able to open in excel. Stack Trace as in everything that's in the console? — Cassie, Jun 02 '14 at 02:33
@DavidWallace Hey David, it's alright. I think I will have to solve it our myself. Thank for you help!! Really sorry for taking up your time to help me solve this problem despite giving poor explanation and details. — Cassie, Jun 02 '14 at 02:46
No, that's OK. Did you add `e.printStackTrace()` in where I suggested? If so, I need all the lines with file names, line numbers and so on that this generates, starting with `org.apache.poi.openxml4j.exceptions.InvalidFormatException:` — Dawood ibn Kareem, Jun 02 '14 at 02:47
@DavidWallace yup, have added `e.printStackTrace()`. I will paste the output in the qns section. — Cassie, Jun 02 '14 at 02:53
Fantastic. That's what I need. Unfortunately, I have to go out in a few minutes; but I'll have a good look at this later. — Dawood ibn Kareem, Jun 02 '14 at 03:04
@DavidWallace Hi David. Just want to let you know that the error "...[M1.13]" is no longer there but it has become "...java.lang.NullPointerException" caused by line `o[c] = sheet.getRow(r).getCell(c);` have updated my output in the qns section — Cassie, Jun 02 '14 at 08:35
OK, are you able to step through this with a debugger, so that you see what the values of `r`, `c`, `_totalr` and `_totalc` are at the time the exception is thrown, and also whether `sheet.getRow(r)` returned a real object? — Dawood ibn Kareem, Jun 02 '14 at 08:58
I don't understand, Cassie. Did you set a breakpoint on that line, then run it in debug mode? And if so, did the program produce the same output as when you ran it normally? — Dawood ibn Kareem, Jun 02 '14 at 09:07
@DavidWallace Yup I have set the breakpoint and when I click on debug, it didn't run at all. — Cassie, Jun 02 '14 at 09:09
I can't think of any explanation for that. What happens if you clean out your target directories, rebuild your project, and try again? — Dawood ibn Kareem, Jun 02 '14 at 09:11
@DavidWallace The value for r = 1, c=0, totalr = 2, totalc = 7 — Cassie, Jun 02 '14 at 09:19
And were you able to see an object returned from `sheet.getRow(r)` in that case? (Sorry, this is really hard to do, without being in the same room as your computer). — Dawood ibn Kareem, Jun 02 '14 at 09:30
@DavidWallace no it's alright. I should be the one feeling apologetic :/ Hmm, no. It didn't show/return anything for `sheet.getRow(r)` but when I tried to replace r to 0, there isn't any error on the console but instead, the website display this: "There is an error validating the loaded data. Please check the format of the data and/or the structure of the spreadsheet." — Cassie, Jun 02 '14 at 09:34
OK. Can you confirm that your spreadsheet has exactly two rows and seven columns, and that the first row is a header, and the second row is actual data? Or did I misunderstand that much? — Dawood ibn Kareem, Jun 02 '14 at 09:37
Well, the only reason for a null pointer exception on that line would be if `sheet.getRow(1)` returned null; and I don't see how that's possible if the spreadsheet has two rows. Perhaps you can try separating out the line into `XSSFRow thisRow = sheet.getRow(r);` followed by `o[c] = thisRow.getCell(c);` and just use the debugger to confirm that `thisRow` really is null. I honestly can't get my head around why it would be, but there's nothing else that can possibly be happening. — Dawood ibn Kareem, Jun 02 '14 at 09:45
@DavidWallace alright, I will try again. But I have to leave now so I will get back to you tomorrow. Thank you for your help today, and am really sorry for taking your time! — Cassie, Jun 02 '14 at 09:54
@DavidWallace Hi David. Yeap I've checked and debugged the above that you told me to. `thisRow` is already null. — Cassie, Jun 03 '14 at 01:27
Hi Cassie. I've been thinking about this. It seems to me that you're dealing with some kind of bug in POI. I mean, the Javadoc for `getRow` clearly states that it "returns the logical row ( 0-based). If you ask for a row that is not defined you get a null." So the fact that you've got a spreadsheet with two rows, and `getRow(1)` is returning null violates the Javadoc. I suggest contacting Apache via their user mailing list at http://poi.apache.org/mailinglists.html. It would be good if you could break all of this down into a much smaller test case first ... maybe with just ... — Dawood ibn Kareem, Jun 04 '14 at 05:16
`OPCPackage pkg = OPCPackage.open("C:\\Users\\....Load_AcctCntr_Template.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(pkg); XSSFSheet sheet = workbook.getSheetAt(0); System.out.println(sheet.getRow(1));` which should be enough to demonstrate the problem. If you don't feel confident doing that, then I don't mind having a look at your XLSX file and trying to find the problem by debugging POI, but ultimately, I'd probably end up contacting Apache. — Dawood ibn Kareem, Jun 04 '14 at 05:18
@DavidWallace Hi David, thanks for that but apparently, the problem/error seems to cease already. No nullpointererror etc, _m:{DATA is being printed and shown on the console. Only problem left is data not being able to be captured on the website. I think it's because of this problem here [link] (http://stackoverflow.com/questions/24006348/change-from-jxl-to-xssf) as I didn't call out the values properly. — Cassie, Jun 04 '14 at 05:37

XLSX JAVA Read file using Hashmap

0 Answers0