Epplus 4.1中的透明和清除细胞

Question

我没有运气在excel中删除行，所以现在我尝试清除他们的内容并仍然得到错误："无法删除/覆盖合并的单元格。范围部分与另一个合并范围合并。A57788：J57788"。

列1-10确实合并了，但是如何解开它们？

这是我的代码：

cntr = 0;
while (ws.Cells[RowNum + cntr, 1].Value == null || !ws.Cells[RowNum + cntr, 1].Value.ToString().StartsWith("Report generation date"))
{
    ws.Cells[RowNum + cntr, 1, RowNum + cntr, 18].Value = "";
    ws.Cells[RowNum + cntr, 1, RowNum + cntr, 10].Merge = false;
    for (int i = 1; i < 17; i++)
    {
        ws.Cells[RowNum + cntr, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.None);
        ws.Cells[RowNum + cntr, i].Clear();
    }
    cntr++;
}
//ws.DeleteRow(RowNum, cntr);

Answer 1

事实是，您不能在一个范围内解开单个单元格，您必须拆开整个范围。

为此，您可以在此处获取单元格属于使用我的解决方案的合并范围：

 public string GetMergedRange(ExcelWorksheet worksheet, string cellAddress)
    {
        ExcelWorksheet.MergeCellsCollection mergedCells = worksheet.MergedCells;
        foreach (var merged in mergedCells)
        {
            ExcelRange range = worksheet.Cells[merged];
            ExcelCellAddress cell = new ExcelCellAddress(cellAddress);
            if (range.Start.Row<=cell.Row && range.Start.Column <= cell.Column)
            {
                if (range.End.Row >= cell.Row && range.End.Column >= cell.Column)
                {
                    return merged.ToString();
                }
            }
        }
        return "";
    }

第二步是使用：

来解开整个范围

public void DeleteCell(ExcelWorksheet worksheet, string cellAddress)
    {
        if (worksheet.Cells[cellAddress].Merge == true)
        {
            string range = GetMergedRange(worksheet, cellAddress); //get range of merged cells
            worksheet.Cells[range].Merge = false; //unmerge range
            worksheet.Cells[cellAddress].Clear(); //clear value
        }
    }

这种方式将使您失去其他单元格的合并及其价值，要克服它，您可以在清除和取消编写之前节省价值，然后您可以将其写回来，例如：

public void DeleteCell(ExcelWorksheet worksheet, string cellAddress)
    {
        if (worksheet.Cells[cellAddress].Merge == true)
        {
            var value = worksheet.Cells[cellAddress].Value;
            string range = GetMergedRange(worksheet, cellAddress); //get range of merged cells
            worksheet.Cells[range].Merge = false; //unmerge range
            worksheet.Cells[cellAddress].Clear(); //clear value
            //merge the cells you want again.
            //fill the value in cells again
        }
    }