storyteller storyteller - 1 year ago 159
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())));
columnNo = 1;
cell = row.createCell(columnNo);

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

cell.setCellValue(new HSSFRichTextString(marshActiveUser.getFistName()));

cell.setCellValue(new HSSFRichTextString(" "));

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

Answer Source

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 org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;

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


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



enter image description here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download