Read excel using apache poi - Hashmap,ArrayList

Question

Hi,

I am trying to read column values using column headers in excel (using java). As attached in the image, suppose i want to read for header "city", all subsequent cells under the column and display them . How will i do that ?

Looking at examples over the internet, i found most of the examples iterating using an iterator over the row.

Suppose i have a hashmap of key as headers of excel and subsequent values stored in ArrayList. Something like

HashMap<String, ArrayList<String>> hashmap = 
                new HashMap<String, ArrayList<String>>();

How should i proceed ? Can anyone help me with the code and logic?

Please do not mark this as duplicate, i have tried searching over stackoverflow and internet, but most of them iterate using the row.

Not clear where the problem is here. Just iterate over the rows and getCell(4), which is column E from each row. — Axel Richter
– Axel Richter, Commented Sep 30, 2016 at 15:24
I want to make it generic. Not a specific row or column. For a particular column header, i want to extract all cell values. If you can help me with some piece of code for the same, that would be great. — bhargav desai
– bhargav desai, Commented Sep 30, 2016 at 15:29

Chetan Jadhav CD · Accepted Answer · 2016-09-30 15:13:15Z

1

There is no way to directly read all the cells in a column without iterating over the rows as that is the way data is stored in an excel sheet (in rows, not in columns). A workaround could be to transpose the rows and columns like it is shown here, and then iterating over the rows to get your data in the Hashmap as you indicated in your question.

answered Sep 30, 2016 at 15:13

Chetan Jadhav CD

1,1468 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bhargav desai Over a year ago

Thanks for the info. Rather than transposing rows and columns , is there a way, we can retrieve all cells based on the column header name ?

Chetan Jadhav CD Over a year ago

I do not know of any other way, and don't think there is any, at least for tables that are CSV or excel formats, because of the fundamental limitation imposed by the way in which the data is stored (row by row) in them. There is a direct pointer (sort of) from one cell to the next in a row, but no such linkage exists for cells in a column across the different rows. Imagine it in this way - threads of equal length are laid on a table horizontally, one below the other (rows). You can traverse the length of the thread, but cannot jump from one point on one thread to its counterpart on the next!

parthi · Accepted Answer · 2016-09-30 15:29:06Z

1

First, you will read 1st row after you can get all column values,below i have given how to read first row

public void readFirstRow(String filename)
{

    List<String> fieldsArrayList = new ArrayList<String>();

     try{

            FileInputStream myInput = new FileInputStream(new File(filename));

            Workbook workBook = null;

            workBook    = new HSSFWorkbook(myInput);

            Sheet sheet = workBook.getSheetAt(0);

            Row firstRow = sheet.getRow(0);

            int length  = firstRow.getLastCellNum();

            Cell cell = null;

            for( int i = 0 ; i < length ; i++ )

                {
                     cell = firstRow.getCell(i);

                     fieldsArrayList.add(cell.toString());

                }

     }
     catch(Exception e)
     {
         e.printStackTrace();
     }
}

Read column Values code:

//You just pass the cityPostion and filename

public void readColumnValues(int cityPosition,String fileName)
{
    try
    {
        FileInputStream myInput = new FileInputStream(fileName);

        Workbook workBook = null;

        workBook = new HSSFWorkbook(myInput);
        Sheet sheet = workBook.getSheetAt(0);

        for ( int i = 0 ; i <= sheet.getLastRowNum() ; i++ )
        {
            Row row = sheet.getRow(i);

            if (i > 0) //skip first row
            {

                Cell cityCell   = row.getCell(cityPosition);


                String cityNames      = cityCell.toString();
            }

        }

    }
    catch(Exception e)
    {
        e.printStackTrace();
    }
}

Might be it will help to you...

answered Sep 30, 2016 at 15:29

parthi

4241 gold badge5 silver badges16 bronze badges

2 Comments

IceArdor Over a year ago

Check for null rows and null cells unless you want NullPointerExceptions.

bhargav desai Over a year ago

@parthi: public void readFirstRow(String filename) {try{ FileInputStream myInput = new FileInputStream(new File(filename)); Workbook workBook = null; workBook = new HSSFWorkbook(myInput); Sheet sheet = workBook.getSheetAt(0); Row firstRow = sheet.getRow(0); int length = firstRow.getLastCellNum(); Cell cell = null; for( int i = 0 ; i < length ; i++ ) { cell = firstRow.getCell(i); if (cell.getStringCellValue().equalsIgnoreCase(UserInput)) {hmap.put(cell, fieldsArrayList);}}} }

Collectives™ on Stack Overflow

Read excel using apache poi - Hashmap,ArrayList

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related