c# - 使用 Interop 从 excel 获取最后一个非空列和行索引

Question

我正在尝试使用 Interop Library 从 Excel 文件中删除所有多余的空白行和列。

我关注了这个问题 Fastest method to remove Empty rows and Columns From Excel Files using Interop我发现它很有帮助。

但我的 Excel 文件包含一小部分数据，但有很多空行和空列(从最后一个非空行(或列)到工作表末尾)

我尝试循环遍历行和列，但循环需要几个小时。

我正在尝试获取最后一个非空行和列索引，以便我可以在一行中删除整个空范围

XlWks.Range("...").EntireRow.Delete(xlShiftUp)

注意:我正在尝试获取包含数据的最后一行以删除所有额外的空白(在此行或列之后)

有什么建议吗？

---

注意:代码必须兼容SSIS脚本任务环境

Answer 1

更新1

如果您的目标是使用 C# 导入 Excel 数据，假设您已经确定了工作表中使用率最高的索引(在您发布的图片中，它是 Col = 10，Row = 16)，您可以将最大使用索引转换为字母，因此它将是 J16并使用和 OLEDBCommand 仅选择使用的范围

SELECT * FROM [Sheet1$A1:J16]

否则，我认为找到更快的方法并不容易。

您可以引用这些文章将索引转换为字母并使用 OLEDB 连接到 excel:

• How to convert a column number (eg. 127) into an excel column (eg. AA)
• Allowing VB.NET app to convert Excel Files to Datatable

---

初始答案

正如您所说，您是从以下问题开始的:

• Fastest method to remove Empty rows and Columns From Excel Files using Interop

并且您正在尝试“获取包含数据的最后一行以删除所有额外的空白(在该行或列之后)”

假设您正在使用接受答案(由 @JohnG 提供)，那么您可以添加一些代码行来获取最后使用的行和列

空行存储在整数列表 rowsToDelete 中

您可以使用以下代码获取索引小于最后一个空行的最后一个非空行

List<int> NonEmptyRows = Enumerable.Range(1, rowsToDelete.Max()).ToList().Except(rowsToDelete).ToList();

如果NonEmptyRows.Max() < rowsToDelete.Max()最后一个非空行是 NonEmptyRows.Max()否则它是 worksheet.Rows.Count并且在最后使用的行之后没有空行。

可以做同样的事情来获取最后一个非空列

代码在 DeleteCols 中编辑和 DeleteRows功能:

    private static void DeleteRows(List<int> rowsToDelete, Microsoft.Office.Interop.Excel.Worksheet worksheet)
    {
        // the rows are sorted high to low - so index's wont shift

        List<int> NonEmptyRows = Enumerable.Range(1, rowsToDelete.Max()).ToList().Except(rowsToDelete).ToList();

        if (NonEmptyRows.Max() < rowsToDelete.Max())
        {

            // there are empty rows after the last non empty row

            Microsoft.Office.Interop.Excel.Range cell1 = worksheet.Cells[NonEmptyRows.Max() + 1,1];
            Microsoft.Office.Interop.Excel.Range cell2 = worksheet.Cells[rowsToDelete.Max(), 1];

            //Delete all empty rows after the last used row
            worksheet.Range[cell1, cell2].EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);


        }    //else last non empty row = worksheet.Rows.Count



        foreach (int rowIndex in rowsToDelete.Where(x => x < NonEmptyRows.Max()))
        {
            worksheet.Rows[rowIndex].Delete();
        }
    }

    private static void DeleteCols(List<int> colsToDelete, Microsoft.Office.Interop.Excel.Worksheet worksheet)
    {
        // the cols are sorted high to low - so index's wont shift

        //Get non Empty Cols
        List<int> NonEmptyCols = Enumerable.Range(1, colsToDelete.Max()).ToList().Except(colsToDelete).ToList();

        if (NonEmptyCols.Max() < colsToDelete.Max())
        {

            // there are empty rows after the last non empty row

            Microsoft.Office.Interop.Excel.Range cell1 = worksheet.Cells[1,NonEmptyCols.Max() + 1];
            Microsoft.Office.Interop.Excel.Range cell2 = worksheet.Cells[1,NonEmptyCols.Max()];

            //Delete all empty rows after the last used row
            worksheet.Range[cell1, cell2].EntireColumn.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftToLeft);


        }            //else last non empty column = worksheet.Columns.Count

        foreach (int colIndex in colsToDelete.Where(x => x < NonEmptyCols.Max()))
        {
            worksheet.Columns[colIndex].Delete();
        }
    }