Same data is getting inserted in every row in excel even though the result set has different data using java

Question

I have a requirement to export the data to excel.

I have written a method passing the result of query i.e., collection object and String array - attrs which is of type LinkedHashMap<Integer, Object[]>. The method is shown below. The result of each entry is stored in Object array and inserted into a key. The returned result is now in resultSet and passed through another method processReport(resultSet).

LinkedHashMap<Integer, Object[]> resultSet = dqlHelper.insertAttrValues(collection,
        attrs);

processReport(resultSet);

---

public LinkedHashMap<Integer, Object[]> insertAttrValues(IDfCollection collection,
                                                         String[] properties) throws DfException {

    if (collection == null || properties == null) {
        throw new MissingParameterException("collection and properties");
    }

    LinkedHashMap<Integer, Object[]> map = new LinkedHashMap<>();
    Object[] values = new Object[properties.length];

    int i = 0;
    while (collection.next()) {
        for (int x = 0; x < properties.length; x++) {
            values[x] = collection.getString(properties[x]);
        }
        map.put(i++, values);
    }
    return map;
}

---

Method processReport(resultSet); This method takes the resultSet and write the data to excel. But in each row, I'm getting the same data as shown in the comment in method. But the result from resultSet has all data. Not sure where, I'm doing wrong. Any help is highly appreciated.

private void processReport(LinkedHashMap<Integer, Object[]> resultSet)
            throws IOException {
        // Create a Workbook - for xlsx
        Workbook workbook = new XSSFWorkbook();

         /* CreationHelper helps us create instances of various things like DataFormat,
           Hyperlink, RichTextString etc, in a format (HSSF, XSSF) independent way
        */
        workbook.getCreationHelper();

        // Create a Sheet
        Sheet sheet = workbook.createSheet("Report");

        // Create a Font for styling header cells
        Font headerFont = workbook.createFont();
        headerFont.setBold(false);
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setColor(IndexedColors.GREEN.getIndex());

        // Create a CellStyle with the font
        CellStyle headerCellStyle  = workbook.createCellStyle();
        headerCellStyle.setFont(headerFont);

        // Create a Row
        Row headerRow = sheet.createRow(0);
        // Create cells
        for (int i = 0; i < attrs.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(redigo_attrs[i]);
            cell.setCellStyle(headerCellStyle);
        }

        // Facing problem in the following code.....
        // same entry in every row in excel sheet
        // 0902b69881bd01a5     NLP_031124  VPS17_BR-NPI-EN-GILENYA_-06.06.19_MS_(clean)    Brazil  Gilenya
        // 0902b69881bd01a5     NLP_031124  VPS17_BR-NPI-EN-GILENYA_-06.06.19_MS_(clean)    Brazil  Gilenya

        Set<Integer> key_set = resultSet.keySet();
        sheet = workbook.getSheetAt(0);
        for (Integer key: key_set) {
            int last_row = sheet.getLastRowNum();
            row = sheet.createRow(++last_row);
            int cellNum = 0;
            Object[] valuesObject = resultSet.get(key);
            for (Object value: valuesObject) {
                row.createCell(cellNum++).setCellValue(value.toString());
            }
        }

        // Resize all columns to fit the content size
        for (int i = 0; i < attrs.length; i++) {
            sheet.autoSizeColumn(i);
        }
        // Write the output to the file
        FileOutputStream fileOutputStream = new FileOutputStream(XLSX_FILE_PATH);
        workbook.write(fileOutputStream);
        // close the file
        fileOutputStream.close();
        // close the workbook
        workbook.close();
    }

Answer 1

You're putting the references to the same array (values) over an over. As a result all LinkedHashMap values are references to the same array that contains the last row from the result set.

You need to create a different array object for each row:

//remove this line
//Object[] values = new Object[properties.length];

int i = 0;
while (collection.next()) {
    //put it here
    Object[] values = new Object[properties.length];
    for (int x = 0; x < properties.length; x++) {
        values[x] = collection.getString(properties[x]);
    }
    map.put(i++, values);
}

P.S. Please, read How to create a Minimal, Reproducible Example and How to debug small programs.