Using Apache POI to print data into Excel cell as it loops

Question

I have Java program and I wish to output the data into an Excel spreadsheet. I discovered that I am able to do that using Apache POI. I tried using the example found here and everything seems to be working. However, I am having trouble printing new row in the Excel with each iteration (loop) in my sample program.

This is my program:

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

public class Excel {

    public static void main(String[] args) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sample sheet");

        Map<String, Object[]> data = new HashMap<String, Object[]>();
        data.put("1", new Object[] {"Emp No.", "Name", "Salary"});  

        for (int i=0;i<5;i++){

            data.put("2", new Object[] {"5", "Jane", "300"});

        }

        Set<String> keyset = data.keySet();
        int rownum = 0;
        for (String key : keyset) {
            Row row = sheet.createRow(rownum++);
            Object [] objArr = data.get(key);
            int cellnum = 0;
            for (Object obj : objArr) {
                Cell cell = row.createCell(cellnum++);
                if(obj instanceof Date) 
                    cell.setCellValue((Date)obj);
                else if(obj instanceof Boolean)
                    cell.setCellValue((Boolean)obj);
                else if(obj instanceof String)
                    cell.setCellValue((String)obj);
                else if(obj instanceof Double)
                    cell.setCellValue((Double)obj);
            }
        }

    try {
        FileOutputStream out = 
                new FileOutputStream(new File("new.xls"));
        workbook.write(out);
        out.close();
        System.out.println("Excel written successfully..");

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    }

}

Supposely, I wanted the output something like this:

EmpNo Name Salary
5     Jane 300
5     Jane 300
5     Jane 300
5     Jane 300
5     Jane 300

but it always only print the first row in the Excel sheet. Anyone familiar with using Apache POI could help me on this? Thanks!

Damith · Accepted Answer · 2016-03-03 08:42:45Z

1

here data.put("2", new Object[] {"5", "Jane", "300"}); "2" -> means the number of row. so always you replace the row number2 so instead try

for (int i=2;i<7;i++){
        data.put(i + "", new Object[] {"5", "Jane", "300"});
   }

which means i will be dynamic value. you will have 5 rows :D

answered Mar 3, 2016 at 8:42

Damith

7474 silver badges13 bronze badges

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

Comments

Coralie B · Accepted Answer · 2016-03-03 09:05:13Z

1

You built your data set like that:

   data.put("1", new Object[] {"Emp No.", "Name", "Salary"});
   for (int i=0;i<5;i++){
        data.put("2", new Object[] {"5", "Jane", "300"});
    }

I think you should put:

   data.put(0, new Object[] {"Emp No.", "Name", "Salary"});
   for (int i=1;i<6;i++){
        data.put(i, new Object[] {"5", "Jane", "300"});
    }

If there are no specific reasons why you uses strings as keys of course.

edited Mar 3, 2016 at 9:05

answered Mar 3, 2016 at 8:43

Coralie B

6034 silver badges11 bronze badges

Collectives™ on Stack Overflow

Using Apache POI to print data into Excel cell as it loops

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related