垂直合并单元格并将数据插入单元格 [英] Merging Cells Vertically and Inserting Data Into The Cell

问题描述

我的问题是我有4个值.我需要在一个单元格中代表他们.

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

推荐答案

合并的单元格区域将它们自身定位在该区域的第一个单元格上.这意味着单元格样式以及单元格值.因此，首先需要设置第一个单元格的单元格样式以包装文本.然后，我们需要将所有单元格值连接在一起，以换行符"\ n"分隔的第一个单元格的单元格值.然后我们可以合并单元格.

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.

示例:

SAMPLE.xlsx:

SAMPLE.xlsx:

代码:

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:

SAMPLENEW.xlsx:

我的方法concatenateAndMerge使用DataFormatter，因为源单元格的内容可能不只是文本，而是日期或其他数字.由于合并后的单元格内容需要是一个串联字符串，因此以前的单个单元格的不同内容应该在该串联字符串中出现，如之前在单个单元格中所示.这就是DataFormatter的原因.我的方法使用FormulaEvaluator，因为源单元格的内容也可能包含公式.

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.

这篇关于垂直合并单元格并将数据插入单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！