CODEWITHSUNDEEP
javaexcelapache-poi
bhargav desai
bhargav desai

Reputation: 119

Read excel using apache poi - Hashmap,ArrayList

enter image description here

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.

Upvotes: 0

Views: 17310

Answers (2)

parthi
parthi

Reputation: 422

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

Upvotes: 1

Chetan Jadhav CD
Chetan Jadhav CD

Reputation: 1146

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.

Upvotes: 1

Related Questions