2

Need help! I am trying to code a simple function that could return the data from an excel source. I am trying to create a list of Maps of data from excel file but the list that I am getting has the same values. All value are the same.

Here is my code:

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.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

public class ExcelReaderFinal {
    public static void main(String[] args) throws IOException {
        String path = "C:\\Users\\username\\Downloads\\TestFile.xlsx";
        FileInputStream fis = new FileInputStream(path);
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);
        int lastRow = sheet.getLastRowNum()-5;
        int lastColumn = 3;

        Map<String, Object> dataMap = new HashMap<String, Object>();
        ArrayList<Map<String, Object>> dataList = new ArrayList<>();

        for(int j=0; j<=lastRow; j++){


            for(int i=0; i<=lastColumn; i++) {

                Row row = sheet.getRow(4);
                Cell keyCell = row.getCell(i);
                Row val = sheet.getRow(5+j);
                Cell valueCell = val.getCell(i);

                String value = valueCell.getStringCellValue().trim();
                String key = keyCell.getStringCellValue().trim();
                dataMap.put(key, value);

            }
            dataList.add(dataMap);
        }
        System.out.println(dataList);
    }

}

my actual output is like this:

[{Negative Val=0, Account Number=121312C, Positive Val=20,000,000.00, Banko=RCBC}, {Negative Val=0, Account Number=121312C, Positive Val=20,000,000.00, Banko=RCBC}, {Negative Val=0, Account Number=121312C, Positive Val=20,000,000.00, Banko=RCBC}]

but my expected output is like this: enter image description here

And here is my excel file source: enter image description here

In Summary:

  1. I want to get all the data in excel file in list format
  2. that list should be sorted based on the arrangement of columns from excel.

Thank you so much!!

TesterMan
  • 31
  • 1
  • 2
    You only have one `dataMap`, so each time around your outer loop its entries are getting overwritten. When you add it to the list it is not being copied, you are adding a reference. You need to move `Map dataMap = new HashMap();` inside your outer loop. – tgdavies Dec 22 '20 at 04:53
  • In future, stepping through your code with a debugger will help you solve problems like this. – tgdavies Dec 22 '20 at 04:54
  • Hi @tgdavies - it worked. thank you so much :) – TesterMan Dec 22 '20 at 05:15
  • Hi @tgdavies - is there a way i can do this also in CSV files? as of now my code only works for excel files. I am actually new to programming. Thanks! – TesterMan Dec 23 '20 at 09:34
  • Does this help? https://stackoverflow.com/questions/721859/importing-csv-data-with-apache-poi – tgdavies Dec 23 '20 at 11:01

0 Answers0