How to add header column in excel using Java Apache POI?

Question

I am writing a java program in which I have to take data from XML file and put it into excel file. While doing this I have to create row headers and add data accordingly.

I know how to create column headers. I can do it in following way:

....
HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("EDR Raw Data");

    Row header = sheet.createRow(0);
    header.createCell(0).setCellValue("Header1");
    header.createCell(1).setCellValue("Header2");
    header.createCell(2).setCellValue("Header3");
    header.createCell(3).setCellValue("Header4");
....

It creates excel file as shown below:

But I want my excel file looks like below:

I am not getting a way to do this. It's not good idea to create required number of rows individually. Is there any way by which we can create a column and add all header in that column?

The way I tried to do this is:

....
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("EDR Raw Data");

sheet.createRow(0).createCell(0).setCellValue("header1");
sheet.createRow(1).createCell(0).setCellValue("header2");
sheet.createRow(2).createCell(0).setCellValue("header3");
sheet.createRow(3).createCell(0).setCellValue("header4");
...

But in my case I have to give 100+ row labels. SO this won't be efficient way.

Answer 1

Just create one row for each header, and populate the first cell in each!

Something like:

String[] headers = new String[] { "Header1", "Header2", "Header3" };

Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("EDR Raw Data");

for (int rn=0; rn<headers.length; rn++) {
   Row r = sheet.createRow(rn);
   r.createCell(0).setCellValue(headers[rn]);
}

Then when populating your data, do sheet.getRow(rownumber) to get the existing row, and populate the remaining cells of interest in it