Merging Cells Vertically and Inserting Data Into T

问题:

My question is I have 4 values. I need to represent them in a single cell.

I need to merge the cells vertically (columns) and present the four values one below the other (top to bottom) having line feeds between in that merged cell.

I am able to merge the cells vertically but unable to present the four values in a single cell.

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

回答1:

The merged cell region orients them self on the first cell in that region. That means cell style as well as cell value. So the need is at first setting the cell style of first cell to wrap text. Then we need concatenating all cell values together into first cell's cell value delimited by "\n", the line feed. Then we can merging the cells.

Example:

SAMPLE.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();

 }
}

SAMPLENEW.xlsx:

My method concatenateAndMerge uses DataFormatter because the source cells content might be not simply text but date or other numbers. Since the merged cells content needs to be a concatenated string, the different content of the previous single cells should be present in that concatenated string as shown before in the single cells. That's why the DataFormatter. And my method uses FormulaEvaluator because the source cells content might contain formulas too.

回答2:

If using VBA directly in excel is a viable alternative, you can write a macro to do this task. The macro will be saved within the actual excel file.

When Excel merges cells, it only keeps the value of the first cell, so before you make those cells merge, concatenate their value in the format you want. To add a line-break to your value, concatenate Chr(10) where you want a new line.

Keep in mind that if you format numeric cells that way, the cell value will become a string and therefore can't be used anymore as a number in formulas.

The following code will do what you want* :

Dim finalValue As String
For Each c In Selection
    finalValue = finalValue & IIf((Len(finalValue) > 0) And (Len(c.Text) > 0), Chr(10), Empty) & c.Text
Next c

Selection.Clear
Selection.Merge
Selection.Value = finalValue

---

*note : I made the code with a selection in case you need to perform this task on a variable cell number, but it will work on any Range object

also note that an empty cell will not make an empty line unless you remove the And (Len(c.Text) > 0) condition in IIf. But by doing this, merging a selection with already merged cells will create as many empty lines as [the size of the merged cell] - 1