Excel合并单元格在GridView中无法正常显示问题

我有一个Excel文件，包含Emp.Id、Employee Name、Designation三列，这三列各自存在两个合并单元格，其余列均未合并。将该Excel导入并在GridView中展示时出现两个异常：

• 原Excel中的合并单元格在GridView中显示为两个独立单元格，值仅出现在第一个单元格，第二个单元格为空
• 原本全为空的Sunday列反而被自动合并了

protected DataTable YourExcelFileProcessingMethod(Stream excelFileStream)
{
    ExcelPackage.LicenseContext = LicenseContext.Commercial;

    using (ExcelPackage package = new ExcelPackage(excelFileStream))
    {
        ExcelWorksheet worksheet = package.Workbook.Worksheets[0];

        DataTable dt = new DataTable();

        for (int col = 1; col <= worksheet.Dimension.End.Column; col++)
        {
            dt.Columns.Add("Column" + col);
        }

        for (int row = 1; row <= worksheet.Dimension.End.Row; row++)
        {
            DataRow newRow = dt.NewRow();

            for (int col = 1; col <= worksheet.Dimension.End.Column; col++)
            {
                ExcelRange cell = worksheet.Cells[row, col];

                if (cell.Merge)
                {
                    
                    newRow[col - 1] = cell.Merge && cell.Start.Row == row ? cell.Text : cell.Text;
                }
                else
                {
                    
                    newRow[col - 1] = cell.Text;
                }
            }

            dt.Rows.Add(newRow);
        }

        return dt;
    }
}

protected void btnImportData_Click(object sender, EventArgs e)
{
    if (FileUploadAttendance.HasFile)
    {
        HttpPostedFile file = FileUploadAttendance.PostedFile;

        if (file.FileName.EndsWith(".xls") || file.FileName.EndsWith(".xlsx") || file.FileName.EndsWith(".xlsm"))
        {
            DataTable dt = YourExcelFileProcessingMethod(file.InputStream);

            GridAttendance.DataSource = dt;
            GridAttendance.DataBind();
        }
        else
        {
            ScriptManager.RegisterStartupScript(this, this.GetType(), "FailureAlert", "alert('Only Excel files are accepted');", true);
        }
    }
    else
    {
        ScriptManager.RegisterStartupScript(this, this.GetType(), "FailureAlert", "alert('Please Select a file to import');", true);
    }
}


protected void GridAttendance_RowDataBound(object sender, GridViewRowEventArgs e)
{
    for (int rowIndex = GridAttendance.Rows.Count - 2; rowIndex >= 0; rowIndex--)
    {
        GridViewRow gvRow = GridAttendance.Rows[rowIndex];
        GridViewRow gvPreviousRow = GridAttendance.Rows[rowIndex + 1];
        for (int cellCount = 0; cellCount < gvRow.Cells.Count;
                                                      cellCount++)
        {
            if (gvRow.Cells[cellCount].Text ==
                                   gvPreviousRow.Cells[cellCount].Text)
            {
                if (gvPreviousRow.Cells[cellCount].RowSpan < 2)
                {
                    gvRow.Cells[cellCount].RowSpan = 2;
                }
                else
                {
                    gvRow.Cells[cellCount].RowSpan =
                        gvPreviousRow.Cells[cellCount].RowSpan + 1;
                }
                gvPreviousRow.Cells[cellCount].Visible = false;
            }
        }
    }
}   

问题根源在于两个环节：Excel读取时未将合并单元格的值填充到所有合并区域，以及GridView合并逻辑错误匹配空值。

1. 修复Excel读取逻辑 #

原代码仅读取合并区域起始单元格的值，其他合并单元格位置为空。修改后直接获取合并区域的起始单元格值，填充到当前单元格对应的DataTable位置：

protected DataTable YourExcelFileProcessingMethod(Stream excelFileStream)
{
    ExcelPackage.LicenseContext = LicenseContext.Commercial;

    using (ExcelPackage package = new ExcelPackage(excelFileStream))
    {
        ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
        DataTable dt = new DataTable();

        // 创建DataTable列
        for (int col = 1; col <= worksheet.Dimension.End.Column; col++)
        {
            dt.Columns.Add("Column" + col);
        }

        // 遍历所有行，填充数据
        for (int row = 1; row <= worksheet.Dimension.End.Row; row++)
        {
            DataRow newRow = dt.NewRow();
            for (int col = 1; col <= worksheet.Dimension.End.Column; col++)
            {
                ExcelRange cell = worksheet.Cells[row, col];
                // 如果是合并单元格，取合并区域起始单元格的文本
                if (cell.Merge)
                {
                    newRow[col - 1] = worksheet.Cells[cell.MergeRange.Start.Row, cell.MergeRange.Start.Column].Text;
                }
                else
                {
                    newRow[col - 1] = cell.Text;
                }
            }
            dt.Rows.Add(newRow);
        }
        return dt;
    }
}

2. 修复GridView合并逻辑 #

原逻辑会把所有文本相同的单元格合并，包括空值，导致空列被错误合并。修改后仅合并非空且文本一致的单元格，同时跳过表头行：

protected void GridAttendance_RowDataBound(object sender, GridViewRowEventArgs e)
{
    // 只处理数据行，忽略表头和页脚
    if (e.Row.RowType != DataControlRowType.DataRow) return;

    for (int rowIndex = GridAttendance.Rows.Count - 2; rowIndex >= 0; rowIndex--)
    {
        GridViewRow gvRow = GridAttendance.Rows[rowIndex];
        GridViewRow gvPreviousRow = GridAttendance.Rows[rowIndex + 1];
        
        for (int cellCount = 0; cellCount < gvRow.Cells.Count; cellCount++)
        {
            string currentText = gvRow.Cells[cellCount].Text.Trim();
            string prevText = gvPreviousRow.Cells[cellCount].Text.Trim();
            
            // 仅合并非空且文本相同的单元格
            if (!string.IsNullOrEmpty(currentText) && currentText == prevText)
            {
                if (gvPreviousRow.Cells[cellCount].RowSpan < 2)
                {
                    gvRow.Cells[cellCount].RowSpan = 2;
                }
                else
                {
                    gvRow.Cells[cellCount].RowSpan = gvPreviousRow.Cells[cellCount].RowSpan + 1;
                }
                gvPreviousRow.Cells[cellCount].Visible = false;
            }
        }
    }
}

效果说明 #

• 修复Excel读取后，DataTable中每个合并单元格的位置都会填充对应的值，不会出现空单元格
• 修改GridView合并逻辑后，只会合并有实际内容且文本一致的单元格，彻底避免空列被错误合并

内容的提问来源于stack exchange，提问作者user22693280