CODEWITHSUNDEEP
javaexcelapache-poidao
serkan
serkan

Reputation: 1237

Write Excel file with Java

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

Upvotes: 1

Views: 523

Answers (3)

Manuel
Manuel

Reputation: 4228

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);
}

Upvotes: 2

Christian
Christian

Reputation: 1586

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.

Upvotes: 0

Thomas
Thomas

Reputation: 88707

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.

Upvotes: 5

Related Questions