Not able to write excel row column (apache poi) by multiple for loop

Question

I have a xlsx file which has multiple sheet i am using apache poi for writing excel, in sheet2 i have 2 columns each column i want to populate by running a for loop , but i see that only last for loop get written previous one get blank in final written output file, i want to write both column by these for loop please help .

for(int  i=0;i<fileNamesArray.length;i++)
    {
        XSSFRow row = worksheet1.createRow(i+1);
        cell = row.createCell(0);

        cell.setCellValue(fileNamesArray[i].toString());

    }//this dont get written 



    for(int i=0;i<fileDatesArray.length;i++)
    {
        XSSFRow row = worksheet1.createRow(i+1);
        cell = row.createCell(1);
        cell.setCellValue(fileDatesArray[i].toString());

    }//only this get written

this is complete code

    public class DashBoard {

    public void writeDashBoard() throws IOException, SQLException
    {
        CODToolUtil codToolUtil = new CODToolUtil();
        // Read property file to initialize constants
        String templateDashBoardFile = codToolUtil.getPropValues("templateDashBoardFile");
        String outputDir = codToolUtil.getPropValues("outputDir");
        String dirSeprator = codToolUtil.getPropValues("dirSeprator");
        String fdate = CODToolUtil.getDate();
        CODDAO coddao=new CODDAO();
        LinkedHashSet<String> hs= new LinkedHashSet<String>();
        LinkedHashSet<String> hs1= new LinkedHashSet<String>();
        FileInputStream fsIP= new FileInputStream(new File(templateDashBoardFile)); //Template file
        XSSFWorkbook wb = new XSSFWorkbook(fsIP);

        XSSFSheet worksheet = wb.getSheetAt(0);



        Cell cell = null; 

        cell = worksheet.getRow(1).getCell(0);
        cell.setCellValue(CODToolUtil.getDate());//Date
        cell = worksheet.getRow(1).getCell(1);
        int allfiles=coddao.getAllfiles();
        cell.setCellValue(allfiles);//All Files

        cell = worksheet.getRow(1).getCell(2);
        int callfilesY=coddao.getAllProcessedfilesCallY();
        cell.setCellValue(callfilesY);//All Y Files

        cell = worksheet.getRow(1).getCell(3);
        int callfilesN=coddao.getAllProcessedfilesCallN();
        cell.setCellValue(callfilesN);//All N Files

        cell = worksheet.getRow(1).getCell(4);
        int allLTE=coddao.getAllProcessedfilesLTE();
        cell.setCellValue(allLTE);//All LTE Files

        cell = worksheet.getRow(1).getCell(5);
        int allWCDMA=coddao.getAllProcessedfilesWCDMA();
        cell.setCellValue(allWCDMA);//All WCDMA Files
        //Sheet 0 OverView Complete
        //Sheet 1 Successfull CT
        XSSFSheet worksheet1 = wb.getSheetAt(1);

        hs=coddao.getAllProcessedfilesNameY();
        hs1=coddao.getAllProcessedfilesDateY();
        Object[] fileNamesArray =  hs.toArray();
        Object[] fileDatesArray =  hs1.toArray();





        for(int  i=0;i<fileNamesArray.length;i++)
        {
            XSSFRow row = worksheet1.createRow(i+1);
            cell = row.createCell(0);

            cell.setCellValue(fileNamesArray[i].toString());

        }//this dont get written 



        for(int i=0;i<fileDatesArray.length;i++)
        {
            XSSFRow row = worksheet1.createRow(i+1);
            cell = row.createCell(1);
            cell.setCellValue(fileDatesArray[i].toString());

        }//only this get written



        fsIP.close();
        File saveDirectory = new File(outputDir);// Create OutPutDirectory
        saveDirectory.mkdir();
        String savefilePath = saveDirectory.getAbsolutePath();
        FileOutputStream output_file = newFileOutputStream(newFile(savefilePath+dirSeprator+fdate+"-"+templateDashBoardFile)); // save in output
        wb.write(output_file); // write changes save it.
        output_file.close(); // close the stream




    }

public static void main(String[] args) throws IOException, SQLException {
    new DashBoard().writeDashBoard();
}
}

Ofer Lando · Accepted Answer · 2015-01-29 11:34:21Z

You are creating the same row twice - probably overriding the "first" row created in the first loop, with the "second" row created in the second loop.

If fileNamesArray and fileDatesArray are the same size, you can combine the loops as:

for(int  i=0;i<fileNamesArray.length;i++)
{
    XSSFRow row = worksheet1.createRow(i+1);
    cell1 = row.createCell(0);
    cell1.setCellValue(fileNamesArray[i].toString());

    cell2 = row.createCell(1);
    cell2.setCellValue(fileDatesArray[i].toString());
}

check which array is bigger and loop through it first, then loop through the second array, but instead of using worksheet1.createRow(i+1) - use worksheet1.getRow(i+1), reusing the row element you created in the first loop.

Note: in theory, even if the arrays are of different sizes you can still use one loop, just make sure you apply relevant checks to avoid ArrayIndexOutOfBoundsException.

Kieran Bristow · Accepted Answer · 2015-01-29 11:31:12Z

0

Try

for(int  i=0;i<fileNamesArray.length;i++)
{
    XSSFRow row = worksheet1.createRow(i+1);
    cell = row.createCell(0);
    cell.setCellValue(fileNamesArray[i].toString());
    cell = row.createCell(1);
    cell.setCellValue(fileDatesArray[i].toString());
}

Instead of of using those 2 loops. I would imagine you are overwriting the your row when you call worksheet1.createRow in the second loop.

answered Jan 29, 2015 at 11:31

Kieran Bristow

3382 silver badges11 bronze badges

Comments

Neel Sanchala · Accepted Answer · 2017-10-20 14:27:59Z

gradeList is an ArrayList of strings with the value "80", "81" ... "85"

         for(int y = 0; y < gradeList.size(); y++){
            HSSFRow row1 = worksheet.createRow((short) 1);//1
            HSSFCell cell1 =row1.createCell((short) y+1);//2
            cell1.setCellValue("" + gradeList.get(y));//3
            HSSFCellStyle cellStylei = workbook.createCellStyle();//4
            cellStylei.setFillForegroundColor(HSSFColor.GREEN.index);
            cell1.setCellStyle(cellStylei);//6
         }

Output of Code: _, _, _, _, _, 85. intended Output: 80, 81, 82, 83, 84, 85.

After changing the code to

        HSSFRow row1 = worksheet.createRow((short) 1);//1
        HSSFCell cell1;
        for(int y = 0; y < gradeList.size(); y++){

            cell1 = row1.createCell((short) y+1);//2
            cell1.setCellValue("" + gradeList.get(y));//3

        }
        HSSFCellStyle cellStylei = workbook.createCellStyle();//4
        cellStylei.setFillForegroundColor(HSSFColor.GREEN.index);//5

the code prints 80, 81, 82, 83, 84, and 85 as intended but using the previous six line code it only prints 85. Can someone please explain to me why is first one wrong or not working, and if possible also can you please also explain what lines 4,5, and 6 do.

Collectives™ on Stack Overflow

Not able to write excel row column (apache poi) by multiple for loop

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related