CODEWITHSUNDEEP
javaexcelswingapacheapache-poi
Sumit Singh Rana
Sumit Singh Rana

Reputation: 95

Can't write data in Excel Sheet using for loop (Apache_poi)

I am trying to write data into Excel sheet using a for loop. But it only writes on first cell and don't iterate further. I tried a lot but couldn't fix this bug. What is wrong with my cod e. Please help. Here is what i have done so far

 HSSFRow row1 = sheet.createRow((short) 1);
            String[] cellname = new String[]{"A", "B", "C", "D", "E", "F", "G", "H", "I"};
            String[] fields = new String[]{"Student's Name", "Father's Name", "Mother's Name", "Address", "Phone No", "Date Of Birth", "Roll NO", "Class", "subjectMajor"};
            for (int i = 0; i <= 9; i++) {
                String Cellname = cellname[i] + 2;
                System.out.print(Cellname);
                HSSFCell CellName = row1.createCell((short) i);
                CellName.setCellValue(fields[i]);
                wb.write(output);
                output.close();
            }

Upvotes: 2

Views: 3702

Answers (1)

DenisFLASH
DenisFLASH

Reputation: 742

  • Actually, you don't even need to specify cell coordinates as "A2", "B2", etc. You're already defining which cell to fill when providing arguments to the createRow() and createCell() methods. For example: 

    // creating an HSSFCell at "D9" and setting its value
// "D" column has index 3; row number 9 has index 8 (starting index is 0)
HSSFRow row = sheet.createRow( 8 );
HSSFCell cell = row.createCell( 3 );
cell.setCellValue( 1341.873 );

  • Also, to avoid ArrayIndexOutOfBoundsException, use < instead of <= in your loop. Otherwise, fields[9] will try to access the 10th element, while your array has only 9. Don't forget, arrays are also "0 based" (the first element has index 0, not 1). And, you'll better use fields.length() instead of specifying 9 directly. Doing this, you won't have to change your loop's limits when some day you will add/remove some column names in fields[] array.

  • As said in comments, wb.write(output) and output.close() are to be put after the loop, normally at the end of program.

So here is the complete code which will perform your task. Hope it helps!

String[] fields = new String[] { "Student's Name", "Father's Name", "Mother's Name", "Address", "Phone No", "Date Of Birth", "Roll NO", "Class", "subjectMajor" };

// Row with index 1 is the second row in Excel sheet
HSSFRow row1 = sheet1.createRow( 1 );

// Filling the row with the given data, one element per cell, 
// starting from the "A" column (cellIndex = 0).
for ( int cellIndex = 0; cellIndex < fields.length; cellIndex++ ) {
    HSSFCell cell = row1.createCell( cellIndex );
    cell.setCellValue( fields[cellIndex] );
}

// Writing a workbook to the disk
try {
    FileOutputStream fileOut = new FileOutputStream( "students.xls" );
    wb.write( fileOut );
    fileOut.close();
} catch ( IOException e ) {
    System.out.println( "IOException:" );
    System.out.println( e.getMessage() );
}

Upvotes: 2

Related Questions