垂直合并单元格并将数据插入单元格 [英] 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.

示例:

样本.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屋！