C# EPPLUS + NPOI 读取和写入合并单元格(C# epplus + npoi read and write merged cells)

1. 读取合并单元格的值

如果是合并单元格读取的就是合并单元格的值,如果该单元格是普通单元格则直接取的就是单元格的值

private string GetMegerValue(ExcelWorksheet sheet, int row, int column)
        {
            var range = sheet.MergedCells[row, column];
            if (range == null)
            {
                if (sheet.Cells[row, column]?.Value != null)
                {
                    return sheet.Cells[row, column].Value.ToString().Trim();
                }
                else
                {
                    return "";
                }
            }
            Object value = sheet.Cells[(new ExcelAddress(range)).Start.Row, (new ExcelAddress(range)).Start.Column].Value;

            return value != null ? value.ToString().Trim() : "";
        }

2. 写入合并单元格的值

/// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet">合并单元格所在的sheet</param>
        /// <param name="rowStart">开始行的索引</param>
        /// <param name="rowEnd">结束行的索引</param>
        /// <param name="colStart">开始列的索引</param>
        /// <param name="colEnd">结束列的索引</param>
        private void SetCellRangeAddress(ISheet sheet, int rowStart, int rowEnd, int colStart, int colEnd)
        {
            for (var r = rowStart; r <= rowEnd; r++)
            {
                for (var c = colStart; c < colEnd; c++)
                {
                    if (sheet.GetRow(r) == null)
                    {
                        sheet.CreateRow(r); //如果行不存在,则创建行
                    }
                    if (sheet.GetRow(r).GetCell(c) == null)
                    {
                        sheet.GetRow(r).CreateCell(c);  //如果列不存在,则创建列
                    }
                }
            }
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd);
            sheet.AddMergedRegion(cellRangeAddress);
        }
————————

1. Read the value of the merged cell

If the cell is merged, the value of the merged cell is read. If the cell is an ordinary cell, the value of the cell is taken directly

private string GetMegerValue(ExcelWorksheet sheet, int row, int column)
        {
            var range = sheet.MergedCells[row, column];
            if (range == null)
            {
                if (sheet.Cells[row, column]?.Value != null)
                {
                    return sheet.Cells[row, column].Value.ToString().Trim();
                }
                else
                {
                    return "";
                }
            }
            Object value = sheet.Cells[(new ExcelAddress(range)).Start.Row, (new ExcelAddress(range)).Start.Column].Value;

            return value != null ? value.ToString().Trim() : "";
        }

2. Write the value of the merged cell

/// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet">合并单元格所在的sheet</param>
        /// <param name="rowStart">开始行的索引</param>
        /// <param name="rowEnd">结束行的索引</param>
        /// <param name="colStart">开始列的索引</param>
        /// <param name="colEnd">结束列的索引</param>
        private void SetCellRangeAddress(ISheet sheet, int rowStart, int rowEnd, int colStart, int colEnd)
        {
            for (var r = rowStart; r <= rowEnd; r++)
            {
                for (var c = colStart; c < colEnd; c++)
                {
                    if (sheet.GetRow(r) == null)
                    {
                        sheet.CreateRow(r); //如果行不存在,则创建行
                    }
                    if (sheet.GetRow(r).GetCell(c) == null)
                    {
                        sheet.GetRow(r).CreateCell(c);  //如果列不存在,则创建列
                    }
                }
            }
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd);
            sheet.AddMergedRegion(cellRangeAddress);
        }