0

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.

Improve this question
1
  • where is this exception thrown from? Commented May 22, 2014 at 6:32

3 Answers 3

Reset to default
2

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.
1

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 ,

Improve this answer

7 Comments

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.
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);
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 for more
@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.
@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.
|
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

Improve this answer

9 Comments

Package should contain a content type part [M1.13] will be the error after I remove POIFSFileSystem fs = new POIFSFileSystem(input);
try using wb = WorkbookFactory.create(InputStream input)
wb and InputStream input will be in red underline
OK then try this XSSFWorkbook wb = WorkbookFactory.create (new File("C:\\Users\\admin\\Desktop\\Load_AcctCntr_Template.xlsx"));
create will still be underline and the error is: The method create(POIFSFileSystem) in the type WorkbookFactory is not applicable for the arguments (File)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.