Java - Merge cells vertically and insert data into cells

Merge cells vertically and insert data into cells… here is a solution to the problem.

Merge cells vertically and insert data into cells

My problem is that I have 4 values. I need to represent them in one cell.

I need to merge cells (columns) vertically and display the four values one by one (from top to bottom) in the merged cell, wrapping lines in them.

I was able to merge cells vertically, but not four values in a single cell.

CellRangeAddress cellRangeAddress = new CellRangeAddress(2,5,5,5);
sheet.addMergedRegion(cellRangeAddress);

Solution

Merged ranges of cells position themselves on the first cell in the range. This means cell style and cell value. So you need to set the cell style of the first cell first to wrap the line text. Then we need to concatenate all cell values together to become the cell values of the first cell, separated by the newline character “\n”. Then we can merge the cells.

Example:

Example .xlsx:

Code:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.util.LocaleUtil;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Locale;

class ExcelConcatenateAndMerge {

private static void concatenateAndMerge(
  Sheet sheet, CellRangeAddress cellRangeAddress, DataFormatter formatter, FormulaEvaluator evaluator, CellStyle cellStyle) {

Row row = null;
  Cell cell = null;
  Cell firstCell = null;
  String cellValue = "";
  boolean first = true;
  for (CellAddress cellAddress : cellRangeAddress) {
   row = sheet.getRow(cellAddress.getRow());
   if (first) {
    if (row == null) row = sheet.createRow(cellAddress.getRow());
    firstCell = row.getCell(cellAddress.getColumn());
    if (firstCell == null) firstCell = row.createCell(cellAddress.getColumn());
    firstCell.setCellStyle(cellStyle);
    cellValue = formatter.formatCellValue(firstCell, evaluator);
    first = false;
   } else {
    if (row != null) {
     cell = row.getCell(cellAddress.getColumn());
     if (cell != null) {
      cellValue += "\n" + formatter.formatCellValue(cell, evaluator);
     } else cellValue += "\n" + "";
    } else cellValue += "\n" + "";
   }
  }

firstCell.setCellValue(cellValue);

sheet.addMergedRegion(cellRangeAddress);

}

public static void main(String[] args) throws Exception {

Workbook wb  = WorkbookFactory.create(new FileInputStream("SAMPLE.xlsx"));

Locale locale = new Locale("en", "US");
  LocaleUtil.setUserLocale(locale);
  DataFormatter formatter = new DataFormatter();
  FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
  CellStyle cellStyle = wb.createCellStyle();
  cellStyle.setWrapText(true);

Sheet sheet = wb.getSheetAt(0);
  CellRangeAddress cellRangeAddress = new CellRangeAddress(2,5,5,5);

concatenateAndMerge(sheet, cellRangeAddress, formatter, evaluator, cellStyle);

FileOutputStream out = new FileOutputStream("SAMPLENEW.xlsx");
  wb.write(out);
  out.close();
  wb.close();

}
}

Example new .xlsx:

My method concatenateAndMerge uses DataFormatter because the source cell contents may not just be text, but dates or other numbers. Since the merged cell contents need to be a concatenated string, the different contents of the previous single cell should appear in the concatenated string, as shown earlier in a single cell. That’s why DataFormatter comes in. My method uses FormulaEvaluator because the source cell contents may also contain formulas.

Java – Merge cells vertically and insert data into cells

Merge cells vertically and insert data into cells

Solution

Related Problems and Solutions