storyteller storyteller - 1 month ago 9
Java Question

How to merge cells to the last row in poi

I am creating a workbook using apache poi where i am trying to merge a particular cell to the end of the output.I am using mergeRegion function and cell is merging but, that cell is not merging to the end of the row , it is always ending one line before ,

i am attaching the screen here merged cell

I want the cell to be merged properly, i am posting my code here

for(MarshActiveUser marshActiveUser : listOfMarshUser){

/***/
sheet.addMergedRegion(new CellRangeAddress(
j, //first row (0-based)
j, //last row (0-based)
18, //first column (0-based)
20 //last column (0-based)
));
/***/

int columnNo = 0;
row = sheet.createRow(j+1);
cell = row.createCell(columnNo);
cell.setCellValue(new HSSFRichTextString(String.valueOf(row.getRowNum())));
lockedCellStyle.setFont(hSSFFont);
sheet.autoSizeColumn(0);
cell.setCellStyle(lockedCellStyle);
columnNo = 1;
cell = row.createCell(columnNo);

if(null != marshActiveUser.getFistName()){

cell.setCellValue(new HSSFRichTextString(marshActiveUser.getFistName()));
lockedCellStyle.setFont(hSSFFont);
sheet.autoSizeColumn(1);
cell.setCellStyle(lockedCellStyle);

}else{
cell.setCellValue(new HSSFRichTextString(" "));
cell.setCellStyle(lockedCellStyle);
}


I have tried to start from rowCount +1 but that is not allowed in code , please help me .Thanks in advance.

Answer

There is a problem with rowCount increment. Pre incrementing the row count is skipping your last row for merging. Changed it to post increment rowcount++ and its working as expected.

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class SimpleExcelWriterExample {

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

        Object[][] bookData = {
                {"Head First Java", "Kathy Serria", 79},
                {"Effective Java", "Joshua Bloch", 36},
                {"Clean Code", "Robert martin", 42},
                {"Thinking in Java", "Bruce Eckel", 35},
        };

        int rowCount = 1;

        for (Object[] aBook : bookData) {

            /***/
            sheet.addMergedRegion(new CellRangeAddress(
                    rowCount, //first row (0-based)
                    rowCount, //last row (0-based)
                      3, //first column (0-based)
                      5 //last column (0-based)
                      ));
            /***/
            Row row = sheet.createRow(rowCount++);

            int columnCount = 0;

            for (Object field : aBook) {
                Cell cell = row.createCell(++columnCount);
                if (field instanceof String) {
                    cell.setCellValue((String) field);
                } else if (field instanceof Integer) {
                    cell.setCellValue((Integer) field);
                }
            }

        }


        try{
            FileOutputStream outputStream = new FileOutputStream("D://JavaBooks.xls");
            workbook.write(outputStream);
        }catch(Exception e){}

}}

Output:

enter image description here

Comments