Using hashmap for POI Java XLSX

Question

I have been trying to edit my code to allow a XLSX file to be uploaded and be able to be read on the website. But after countless tries, the data I typed into the XLSX File is unable to be captured on the website. (Eg: After downloading the XLSX Template from the website, I am able to type in anything that I want in the XLSX file and able to upload it again to the website so I do not need to keep on adding new data by clicking "new" every single time. I can just type in everything in that XLSX File all at once and upload it right away)

I was told to use hashmap but I am unsure of the way it works. The codes I have currently only enables the website to capture the header title and I am not suppose to use jxl.
While removing those codes that has jxl, I encounter some errors (being underline in red).

 public HashMap getConstructJXLList_xlsx(UploadedFile File, int Sheetindex) {
    String _LOC = "[PageCodeBase: getConstructJXLList]";
    HashMap _m = new HashMap();

    InputStream _is = null;
    try {
        _is = File.getInputstream();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    XSSFWorkbook workbook;
     XSSFSheet s;

    try {
        workbook = new XSSFWorkbook(_is);
        s = workbook.getSheetAt(Sheetindex);
    } catch (Exception e) {
        System.out.println(_LOC + "1.0 " + " Test:");

        int _totalc = getColumns(); //getColumns is being underline in red
        int _totalr = getRows();    //getRows is being underline in red

        // Header r=0
        String[] _st = new String[_totalc];
        //XSSFSheet sheet = null;
        for (int _c = 0; _c < _totalc; _c++) {
           _st[_c] = getCell(_c, 0); //getCell is being underline in red

        }

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

    // Data r=1 thereafter
    List _l = new ArrayList();
    for (int _r = 1; _r < _totalr; _r++) {
        Object[] _o = new Object[_totalc];
        String _s_r = null;

        for (int _c = 0; _c < _totalc; _c++) {
            _o[_c] = getCell(_c, _r);
            String _cn = _o[_c].getClass().getName();
            String _s_c = null;

            if (!isEmptyNull(_s_c)) {
                _s_r = "record_available";
            }
        }

        if ((_o != null) && (_o.length != 0)) {
            _l.add(_o);

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

Do you mind helping me to solve this? Why there isn't any data being capture in the website?
The error shown is "The method getColumns/getCell/getRows is undefined for the type PageCodeBase." And the help/quick fix given is to create a new method. But after creating the new method, I am unsure of what to add in the methods. Have tried various example (http://snippetjournal.wordpress.com/2014/02/05/read-xlsx-using-poi/) but I stil can't seem to get it work out.

where is this exception thrown from? – Scary Wombat May 22 '14 at 06:32 — Scary Wombat, May 22 '14 at 06:32

score 2 · Answer 1 · answered May 22 '14 at 06:47

I would recommend you to manage de excel file using this classes from the apache POI api

org.apache.poi.ss.usermodel.Cell;
org.apache.poi.ss.usermodel.Row;
org.apache.poi.ss.usermodel.Sheet;
org.apache.poi.ss.usermodel.Workbook;
org.apache.poi.ss.usermodel.WorkbookFactory;

instead of those XSSFWorkbook, XSSFSheet...

And also when accessing the file input stream try doing it this way:

FileInputStream input = new FileInputStream(new File("C:\\Users\\admin\\Desktop\\Load_AcctCntr_Template.xlsx"));
Workbook workBook = WorkbookFactory.create(stream);
workBook.getSheetAt(0);

Hi. I'm not sure why but there's red underline at the usermodel.Cell, sheet and workbook. Also at (stream) and getSheetAt. But I did add in all the .jar files I am suppose to. — Cassie, May 22 '14 at 06:51

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

use this.

FileInputStream input = new FileInputStream(new File("C:/Users/admin/Desktop/Load_AcctCntr_Template.xlsx"));
Workbook wb = WorkbookFactory.create(input);

as mentioned in user3661357 answer. use Workbook instead of XSSFWorkbook. Sheet instead of XSSFSheet. etc..

Also read this Getting Exception(org.apache.poi.openxml4j.exception - no content type [M1.13]) when reading xlsx file using Apache POI?

*HINT > use ALT+SHIFT+I in netbeans to load the necessary packages.

A working example

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class POITest {

public static void test() {

    try {
        FileInputStream input = new FileInputStream(new File("C:/Users/kingslayer/Desktop/test/a.xlsx"));
        Workbook wb = WorkbookFactory.create(input);
        Sheet s = wb.getSheetAt(0);

        Iterator<Row> rows = s.rowIterator();

        while (rows.hasNext()) {
            Row row = rows.next();
            Iterator cells = row.cellIterator();
            while (cells.hasNext()) {
                Cell cell = (Cell) cells.next();

                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    System.out.print(cell.getStringCellValue() + "t");
                } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    System.out.print(cell.getNumericCellValue() + "t");
                } else if (cell.CELL_TYPE_BLANK == cell.getCellType()) {
                    System.out.print("BLANK ");
                } else {
                    System.out.print("Unknown cell type");
                }
            }
            input.close();
        }
    } catch (IOException | InvalidFormatException ex) {
        Logger.getLogger(POITest.class.getName()).log(Level.SEVERE, null, ex);
    }

}

public static void main(String[] args) {
    test();
}

}

All the libraries you must have on the project path.
commons-codec-1.5.jar , commons-logging-1.1.jar , dom4j-1.6.1.jar , junit-3.8.1.jar , log4j-1.2.13.jar , poi-3.9-20121203.jar , poi-excelant-3.9-20121203.jar , poi-ooxml-3.9-20121203.jar , poi-ooxml-schemas-3.9-20121203.jar , poi-scratchpad-3.9-20121203.jar , stax-api-1.0.1.jar , xmlbeans-2.3.0.jar ,

Hi. But I can't add the org.apache.poi.ss.usermodel.Cell; Sheet and Workbook. It's being underline in red. I'm not sure why but I have already add in all the .jar files I am suppose to. — Cassie, May 22 '14 at 07:37
Is it the same to use this? `org.apache.poi.ss.usermodel.Workbook wb = WorkbookFactory.create(input); org.apache.poi.ss.usermodel.Sheet s = wb.getSheetAt(0);` — Cassie, May 22 '14 at 08:00
If you have a `File`, open direct from that, don't go via an `InputStream` it's much less efficient! See the [Apache POI Docs on File vs Stream](http://poi.apache.org/spreadsheet/quick-guide.html#FileInputStream) for more — Gagravarr, May 22 '14 at 10:41
@Gagravarr Hi. I changed my codes again because I was told that I need to use hashmap. Now that I have changed my codes, it doesn't show the data. — Cassie, May 28 '14 at 09:22
@LalithJayasinghe Hi, thanks for the help and reply. But I am not allowed to include jxl and was told that I need to include hashmap too. I have updated the codes above. But somehow, it still doesn't work. — Cassie, May 28 '14 at 09:25
@LalithJayasinghe The current code is being update on the question section already — Cassie, May 29 '14 at 08:40

score 0 · Answer 3 · answered May 22 '14 at 06:31

0

1) get rid of POIFSFileSystem fs = new POIFSFileSystem(input); as you are not using it

2) input.close(); is called after first iteration of row

answered May 22 '14 at 06:31

Scary Wombat

44,617
6
35
64

Package should contain a content type part [M1.13] will be the error after I remove POIFSFileSystem fs = new POIFSFileSystem(input); – Cassie May 22 '14 at 06:38
try using `wb = WorkbookFactory.create(InputStream input)` – Scary Wombat May 22 '14 at 06:44
wb and InputStream input will be in red underline – Cassie May 22 '14 at 06:45
OK then try this `XSSFWorkbook wb = WorkbookFactory.create (new File("C:\\Users\\admin\\Desktop\\Load_AcctCntr_Template.xlsx"));` – Scary Wombat May 22 '14 at 06:47
create will still be underline and the error is: The method create(POIFSFileSystem) in the type WorkbookFactory is not applicable for the arguments (File) – Cassie May 22 '14 at 06:54
what code should I add in order for this to work? Because I am quite confused. If I use this: org.apache.poi.ss.usermodel.Workbook wb = WorkbookFactory.create(input); OR THIS: XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(input); it doesn't have any red underline – Cassie May 22 '14 at 07:03
Yes, that should be also OK see the API http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/WorkbookFactory.html#create(java.io.InputStream) – Scary Wombat May 22 '14 at 07:07
Yup, that's what I did before. No error, able to upload file to website but the website still doesn't capture the data I entered in the excel. – Cassie May 22 '14 at 07:17
well that is a different problem isn't. In the above code there is nothing web specific. – Scary Wombat May 22 '14 at 07:20

Using hashmap for POI Java XLSX

3 Answers3