使用Excel Interop删除空行

Question

我有一些用户提供的Excel文件需要转换为PDF。使用Excel Interop，我可以使用.ExportAsFixedFormat()轻松完成此操作。但是当工作簿具有数百万行时，问题就出现了。这将变成一个具有50k+页面的文件。如果工作簿中所有这些行都有内容，那么这将是可以接受的。然而，每次出现这些文件时，可能只有50行具有内容，其余为空白。我该如何去除空行，以便将其导出到大小合适的PDF中？

1. 我尝试从最后一行开始，逐个使用CountA检查该行是否具有内容，如果有，则删除它。不仅这需要很长时间，而且在大约100k行后会出现以下错误：

   由于代码已经被优化或原生框架位于调用堆栈的顶部，无法计算表达式。

2. 我尝试使用SpecialCells（XlCellType.xlCellTypeLastCell，XlSpecialCellsValue.xlTextValues），但是如果任何单元格具有格式（如背景颜色），则会包括该行。

3. 我尝试使用Worksheet.UsedRange然后删除其后的所有内容，但是UsedRange与第二个问题相同。

---

这是我尝试过的代码：

for (int i = 0; i < worksheets.Count; i++)
{
    sheet = worksheets[i + 1];
    rows = sheet.Rows;
    currentRowIndex = rows.Count;
    bool contentFound = false;

    while (!contentFound && currentRowIndex > 0)
    {
        currentRow = rows[currentRowIndex];

        if (Application.WorksheetFunction.CountA(currentRow) == 0)
        {
            currentRow.Delete();
        }
        else
        {
            contentFound = true;
        }

        Marshal.FinalReleaseComObject(currentRow);
        currentRowIndex--;
    }

    Marshal.FinalReleaseComObject(rows);
    Marshal.FinalReleaseComObject(sheet);
}

---

for (int i = 0; i < worksheets.Count; i++)
{
    sheet = worksheets[i + 1];
    rows = sheet.Rows;

    lastCell = rows.SpecialCells(XlCellType.xlCellTypeLastCell, XlSpecialCellsValue.xlTextValues);
    int startRow = lastCell.Row;

    Range range = sheet.get_Range(lastCell.get_Address(RowAbsolute: startRow));
    range.Delete();

    Marshal.FinalReleaseComObject(range);
    Marshal.FinalReleaseComObject(lastCell);
    Marshal.FinalReleaseComObject(rows);
    Marshal.FinalReleaseComObject(sheet);
}

我的代码有问题吗？这是一个互操作性问题还是Excel的限制？有没有更好的方法来实现我想要做的事情？

- Chris

---

我真的很想研究这个主题。你有演示文件可以进行测试吗？ - Pilgerstorfer Franz

@PilgerstorferFranz 抱歉，我不知道。这个项目已经不存在了。 - Chris

你找到任何解决方案了吗？ - Pilgerstorfer Franz

我没有这样做。最终有一个用户教育片段告诉用户在转换之前筛选工作簿。 :( - Chris

Answer 1

我建议您使用CountA（就像您在第一点中尝试的那样）来获取包含某些值的行数。然后将这些行复制到新工作表中并从那里导出。将少量行复制到新工作表并对其进行操作比尝试从源工作表中删除大量行更容易。

要创建新工作表并复制行，请使用以下代码：

        excel.Worksheet tempSheet = workbook.Worksheets.Add();
        tempSheet.Name = sheetName;
        workbook.Save();

//创建一个新的方法来复制新行

//作为行索引，您可以传递使用CountA找到的总行数

public void CopyRows(excel.Workbook workbook, string sourceSheetName, string DestSheetName, int rowIndex)
        {
            excel.Worksheet sourceSheet = (excel.Worksheet)workbook.Sheets[sourceSheetName];
            excel.Range source = (excel.Range)sourceSheet.Range["A" + rowIndex.ToString(), Type.Missing].EntireRow;

            excel.Worksheet destSheet = (excel.Worksheet)workbook.Sheets[DestSheetName];
            excel.Range dest = (excel.Range)destSheet.Range["A" + rowIndex.ToString(), Type.Missing].EntireRow;
            source.Copy(dest);

            excel.Range newRow = (excel.Range)destSheet.Rows[rowIndex+1];
            newRow.Insert();
            workbook.Save();
        }

- Akhila

Answer 2

你能尝试使用下面的代码吗：

for (int rowIndex = workSheet.Dimension.Start.Row; rowIndex <= workSheet.Dimension.End.Row; rowIndex++)
                    {
                        //Assume the first row is the header. Then use the column match ups by name to determine the index.
                        //This will allow you to have the order of the header.Keys change without any affect.
                        var row = workSheet.Cells[string.Format("{0}:{0}", rowIndex)];
                        // check if the row and column cells are empty
                        bool allEmpty = row.All(c => string.IsNullOrWhiteSpace(c.Text));
                        if (allEmpty)
                            continue; // skip this row
                        else{
                               //here read header
                               if()
                                 {
                                  //some code
                                 }
                               else
                                  {
                                   //some code to read body
                                  }
                            }
                    }

希望这能帮到你，如果需要关于代码的描述，请告诉我。

更新：

• 以下代码用于检查工作表中有多少行。 for循环将遍历工作表的行直到结束。

for (int rowIndex = workSheet.Dimension.Start.Row; rowIndex <= workSheet.Dimension.End.Row; rowIndex++)

• 在此，我们使用linq来检查行和列单元格是否为空：

bool allEmpty = row.All(c => string.IsNullOrWhiteSpace(c.Text));
if (allEmpty)
continue; // 如果为true，则跳过此行
else // 读取标题（假设它在工作表中呈现）
// 否则按行读取数据 然后执行必要的步骤。

希望现在已经清晰了。

- Chandan

Answer 3

今天我必须解决这个问题，可能是你可能遇到的一部分情况。

如果您的电子表格符合以下条件：

1. 所有包含数据的列都在第一行具有标题文本。
2. 所有包含数据的行都按顺序排列，直到第一个空行。

那么，以下代码可能会有所帮助：

    private static string[,] LoadCellData(Excel.Application excel, dynamic sheet)
    {
        int countCols = CountColsToFirstBlank(excel, sheet);
        int countRows = CountRowsToFirstBlank(excel, sheet);
        cellData = new string[countCols, countRows];
        string datum;

        for (int i = 0; i < countCols; i++)
        {
            for (int j = 0; j < countRows; j++)
            {
                try
                {
                    if (null != sheet.Cells[i + 1, j + 1].Value)
                    {
                        datum = excel.Cells[i + 1, j + 1].Value.ToString();
                        cellData[i, j] = datum;
                    }
                }
                catch (Exception ex)
                {
                    lastException = ex;
                    //Console.WriteLine(String.Format("LoadCellData [{1}, {2}] reported an error: [{0}]", ex.Message, i, j));
                }
            }
        }

        return cellData;
    }

    private static int CountRowsToFirstBlank(Excel.Application excel, dynamic sheet)
    {
        int count = 0;

        for (int j = 0; j < sheet.UsedRange.Rows.Count; j++)
        {
            if (IsBlankRow(excel, sheet, j + 1))
                break;

            count++;
        }
        return count;
    }
    private static int CountColsToFirstBlank(Excel.Application excel, dynamic sheet)
    {
        int count = 0;

        for (int i = 0; i < sheet.UsedRange.Columns.Count; i++)
        {
            if (IsBlankCol(excel, sheet, i + 1))
                break;

            count++;
        }
        return count;
    }

    private static bool IsBlankCol(Excel.Application excel, dynamic sheet, int col)
    {
        for (int i = 0; i < sheet.UsedRange.Rows.Count; i++)
        {
            if (null != sheet.Cells[i + 1, col].Value)
            {
                return false;
            }
        }

        return true;
    }
    private static bool IsBlankRow(Excel.Application excel, dynamic sheet, int row)
    {
        for (int i = 0; i < sheet.UsedRange.Columns.Count; i++)
        {
            if (null != sheet.Cells[i + 1, row].Value)
            {
                return false;
            }
        }

        return true;
    }

- Ted Campbell

---

我不相信这是解决问题的可行方案，因为（如问题中所述）具有格式的空单元格不应被删除。除非我弄错了，您的代码片段将错误地删除这些行，因为值可能为null，而格式可能是为保留而设置的。 - gravity

Answer 4

你尝试过使用Sheet1.Range("A1").CurrentRegion.ExportAsFixedFormat()吗？其中Sheet1是一个有效的工作表名称，"A1"是您可以测试以确保其位于要导出的范围内的单元格。

问题仍然存在，为什么Excel认为那些“空”单元格中有数据？格式化？需要清除的预先存在的打印区域？我知道我以前遇到过这样的情况，这些是我现在能想到的唯一可能性。

- Nick Spreitzer

---

糟糕，这个还是不起作用。我遇到了和第二点和第三点一样的问题。如果我能告诉用户不要制作荒谬的电子表格就好了。:D - Chris

Answer 5

请尝试以下步骤：

1. 将 Worksheet.UsedRange 复制到另一张工作表（sheet2）。
2. 使用“粘贴特殊”选项以保留格式。
3. 尝试在 sheet2 中解析未使用的行。

如果这不能解决问题，请尝试使用清除格式信息的方式重复第2步，然后解析 sheet2。如果格式信息足够简单，则随时可以复制格式信息。

- Vinay B R

---

我尝试了你建议的第一部分。和第二点、第三点一样出现了同样的问题。不过我没有尝试过复制无格式内容，然后重新应用格式。那该怎么做呢？“如果它们足够简单” - 这是否意味着复制格式并不总是可行的选项？由于这些都是用户提供的表格，我不能保证它们会有什么样的格式。 - Chris

Answer 6

如果您可以通过OleDBAdapter将Excel文件首先加载到DataSet中，那么在导入时删除空行相对容易...请尝试我通过Stack Overflow发布的OleDBAdapter Excel QA。

然后将DataSet导出到新的Excel文件并将该文件转换为PDF。当然，这可能是一个很大的“IF”，具体取决于Excel布局（或缺乏布局）。

- Brian Wells

---

我没有使用DataSet。我需要修改实际的Excel文件，而且看起来ADO.NET不支持“删除”操作。参考链接：http://support.microsoft.com/kb/316934 - Chris

啊，我不应该假设你正在使用oledbadapter和DataSet。我会修改我的答案。 - Brian Wells

Answer 7

我曾经遇到过同样的问题，最终通过使用CurrentRegion解决了它：

                    var lastcell = sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell);
                    var filledcells = sheet.Cells.Range[sheet.Cells.Item[1, 1],
                            sheet.Cells[lastcell.Row - 1, lastcell.Column]]
                        .CurrentRegion;
                    filledcells.ExportAsFixedFormat(

等等。CurrentRegion 被认为会扩展到单元格为空的边界，显然这也意味着如果它包含许多空单元格，它也会缩小。

- Maarten Havinga

Answer 8

请尝试以下代码：

for (int i = 0; i < worksheets.Count; i++)
{
    sheet = worksheets[i + 1];
    sheet.Columns("A:A").SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete
    sheet.Rows("1:1").SpecialCells(XlCellType.xlCellTypeBlanks).EntireColumn.Delete
    Marshal.FinalReleaseComObject(sheet);
}

- smartobelix