Write Excel file with Java

Question

I created Excel file with Apache POI.

In my database, I have a list of 400 people. I want to write their name and surname to that Excel file.

Here is my code sample:

try {
    FileOutputStream fileOut = new FileOutputStream("C:/testExcelForJava/test.xls");
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet worksheet = workbook.createSheet("POI Worksheet");

    // index from 0,0... cell A1 is cell(0,0)
    HSSFRow row1 = worksheet.createRow((short) 0);

    HSSFCell cellA1 = row1.createCell((short) 0);
    cellA1.setCellValue("Ad");
    HSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    cellA1.setCellStyle(cellStyle);

    HSSFCell cellB1 = row1.createCell((short) 1);
    cellB1.setCellValue("Soyad");
    cellStyle = workbook.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    cellB1.setCellStyle(cellStyle);

    List<Driver> driverList = Dao.driverDao.queryForAll();

    for(Driver driver :driverList){

        String name = driver.getName();
        String surname = driver.getSurname();
        String birthDay = driver.getBirthDay();

        cellA1.setCellValue(name);
        cellB1.setCellValue(surname);
    }

    workbook.write(fileOut);
    fileOut.flush();
    fileOut.close();
} catch(FileNotFoundException e){
    e.printStackTrace();
    log.error(e.getMessage());

} catch(IOException e){
    e.printStackTrace();
    log.error(e.getMessage());
}

When I look at to my Excel file, I only see the last person of list.

How can I write all my people's information?

Thanks

Loop through your Excel cells as well

shree.pat18
– shree.pat18

2014-03-27 10:24:33 +00:00
Commented Mar 27, 2014 at 10:24 — shree.pat18
– shree.pat18, Commented Mar 27, 2014 at 10:24

Thomas · Accepted Answer · 2014-03-27 10:24:30Z

5

Without reading the code entirely it seems as if you're overwriting the value of cells A1 and B1 in the loop:

for(Driver driver :driverList){

    String name = driver.getName();
    String surname = driver.getSurname();
    String birthDay = driver.getBirthDay();

    cellA1.setCellValue(name); //the same cell as before!!!
    cellB1.setCellValue(surname); //the same cell as before!!!
}

I assume you want to change the cells in the loop as well, e.g. put all the row and cell creation code into the loop as well and advance the row index in every iteration.

answered Mar 27, 2014 at 10:24

Thomas

88.9k13 gold badges126 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Manuel · Accepted Answer · 2014-03-27 10:35:49Z

2

You need to enter a new row after you have written the name and surname. Your problem is:

HSSFRow row1 = worksheet.createRow((short) 0); // always the same row
....
cellA1.setCellValue(name); // always the same cell
cellB1.setCellValue(surname); // always the same cell

You do always write to the same cell in the same row! Proposed solution analog to your code. Notice (thanks to the comment) that a CellStyle only gets created once and used multiple times.

HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

Row row;
Cell nameCell;
Cell surNameCell;
int rowNum = 0;
for (Driver driver : driverList){
  row = worksheet.createRow(rowNum++); // here I'm incrementing the excel row

  nameCell = row.createCell((short) 0);
  nameCell .setCellValue("Ad");
  nameCell .setCellStyle(cellStyle);

  urNameCell= row1.createCell((short) 1);
  surNameCell.setCellValue("Soyad");
  surNameCell.setCellStyle(cellStyle);

  String name = driver.getName();
  String surname = driver.getSurname();
  String birthDay = driver.getBirthDay();
  nameCell.setCellValue(name);
  surNameCell.setCellValue(surname);
}

edited Mar 27, 2014 at 10:35

answered Mar 27, 2014 at 10:28

Manuel

4,4536 gold badges38 silver badges52 bronze badges

2 Comments

user432 Over a year ago

You are overwriting the CellStyle in the loop. Create 2 separate CellStyles before the loop and reuse them to get this to work.

Manuel Over a year ago

True. Ill fix that. This is also mentioned in the apache poi documents.

Christian · Accepted Answer · 2014-03-27 10:24:42Z

0

I think this piece of code ...

cellA1.setCellValue(name);
cellB1.setCellValue(surname);

... means that you are trying to write all content to cells A1 and B1, thus always overwriting the previous content in your loop.

answered Mar 27, 2014 at 10:24

Christian

1,5861 gold badge22 silver badges37 bronze badges

Collectives™ on Stack Overflow

Write Excel file with Java

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related