How do I read an excel file and put it into an array?

Question

So I am working on a school project where I have to read an excel file into an array, and then use that data and manipulate and give answers to the user. I can not even figure out how to set a file to equal an array, and then from there get specific bits of data from it. Any help would be appreciated. Here is the actual question prompt:

Design a Java application that will read a file containing data related to the US. Crime statistics from 1994-2013. The description of the file is shown below. The application should provide statistical results on the data. Use arrays and Java classes to store the data. (Hint: You can and should create a USCrimeClass to store the fields. You can also have an Array of US Crime Objects.)

https://poi.apache.org/ http://stackoverflow.com/questions/15336457/how-to-process-old-excel-xls-files-using-poi — user3159253, Oct 15 '16 at 02:31
Possible duplicate of [How to read and write excel file in java](http://stackoverflow.com/questions/1516144/how-to-read-and-write-excel-file-in-java) — user3159253, Oct 15 '16 at 02:32
Your assignment doesn't say that you need to read in an excel file. You can read in a variety of types. It simply says: "read a file containing data". — Edward Jezisek, Oct 15 '16 at 03:19
I accidentally left out the file that was given to us, and it is an excel file which we are told not to modify in any way. — Chris B., Oct 15 '16 at 03:45

score 0 · Accepted Answer · answered Oct 15 '16 at 03:47

I have copied this from Official site and add some TODO lines as a guide line for you. You will find a solution by following this outline. I am not giving exact answer because it is your assignment.

One Excel Row == USCrimeClass object

You have to create a class that mapped to Row class. That class should be named USCrimeClass.

USCrimeClass

USCrimeClass should have fields that are mapped to cells in a row.

example :

from 1994-2013

Official Reference

Getting the cell contents To get the contents of a cell, you first need to know what kind of cell it is (asking a string cell for its numeric contents will get you a NumberFormatException for example). So, you will want to switch on the cell's type, and then call the appropriate getter for that cell.

In the code below, we loop over every cell in one sheet, print out the cell's reference (eg A3), and then the cell's contents.

// import org.apache.poi.ss.usermodel.*;

DataFormatter formatter = new DataFormatter();
Sheet sheet1 = wb.getSheetAt(0);

//TODO int numberOfRows=getNumberOf Rows from sheet1;
//TODO USCrimeClass[] arrayOfUSCrimeClass =new USCrimeClass [numberOfRows](); 

for (Row row : sheet1) {

     //TODO create new object of USCrimeClass
     //TODO USCrimeClass objectUSCrimeClass=new USCrimeClass();
    for (Cell cell : row) {
        CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
        System.out.print(cellRef.formatAsString());
        System.out.print(" - ");

        // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
        String text = formatter.formatCellValue(cell);
        System.out.println(text);

        // Alternatively, get the value and format it yourself
        switch (cell.getCellTypeEnum()) {
            case CellType.STRING:
                System.out.println(cell.getRichStringCellValue().getString());
                //TODO set values to feild from excel cell objectUSCrimeClass.setFrom(cell.getRichStringCellValue().getString());
                break;
            case CellType.NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    System.out.println(cell.getDateCellValue());
                } else {
                    System.out.println(cell.getNumericCellValue());
                }
                break;
            case CellType.BOOLEAN:
                System.out.println(cell.getBooleanCellValue());
                break;
            case CellType.FORMULA:
                System.out.println(cell.getCellFormula());
                break;
            case CellType.BLANK:
                System.out.println();
                break;
            default:
                System.out.println();
        }
    }

//TODO add row object to array or list  arrayOfUSCrimeClass[i]=objectUSCrimeClass;
}

Text Extraction

For most text extraction requirements, the standard ExcelExtractor class should provide all you need.

InputStream inp = new FileInputStream("workbook.xls");
HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
ExcelExtractor extractor = new ExcelExtractor(wb);

extractor.setFormulasNotResults(true);
extractor.setIncludeSheetNames(false);
String text = extractor.getText();

https://poi.apache.org/spreadsheet/examples.html

https://poi.apache.org/spreadsheet/quick-guide.html#Iterator

How do I read an excel file and put it into an array?

1 Answers1