使用Interop从Excel获取上一个非空列和行索引 [英] Get Last non empty column and row index from excel using Interop

问题描述

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

我遵循这个问题

注意：我试图获取包含数据的最后一行删除所有额外的空格（在此行之后，或列）

任何建议？

解决方案

正如你所说，你从以下问题开始：

• 使用Interop从Excel文件中删除空行和列的最简单方法

您正在尝试获取包含数据的最后一行删除所有额外的空格（在此行后，或列）

所以假设你正在使用接受答案（由 @ JohnG ），所以你可以添加一行代码来获取最后使用的行和列

空行存储在一个整数列表 rowsToDelete

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

 列表< int> NonEmptyRows = Enumerable.Range（1，rowsToDelete.Max（））。ToList（）。Except（rowsToDelete）.ToList（）; 
  

如果 NonEmptyRows.Max（） rowsToDelete.Max（）最后一个非空行是 NonEmptyRows.Max（）否则它是 worksheet.Rows。计数，最后一次使用后不会有空行。

同样的事情可以做到最后一个非空列

代码在 DeleteCols 和 DeleteRows 函数：

  private static void DeleteRows（List< int> rowsToDelete，Microsoft.Office.Interop.Excel.Worksheet工作表）
 {
 //行排列高到低 - 所以索引不会移动
 
列表< int> NonEmptyRows = Enumerable.Range（1，rowsToDelete.Max（））。ToList（）。Except（rowsToDelete）.ToList（）; 
 
 if（NonEmptyRows.Max（）< rowsToDelete.Max（））
 {
 
 //最后一个非空行后有空行
 
 Microsoft.Office.Interop.Excel.Range cell1 = worksheet.Cells [NonEmptyRows.Max（）+ 1,1]; 
 Microsoft.Office.Interop.Excel.Range cell2 = worksheet.Cells [rowsToDelete.Max（），1]; 
 
 //删除最后一行后的所有空行
工作表.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工作表）
 {
 / / cols从高到低排序 - 所以索引不会移动
 
 //获取非空Cols 
列表< int> NonEmptyCols = Enumerable.Range（1，colsToDelete.Max（））。ToList（）。Except（colsToDelete）.ToList（）; 
 
 if（NonEmptyCols.Max（）< colsToDelete.Max（））
 {
 
 //最后一个非空行后有空行
 
 Microsoft.Office.Interop.Excel.Range cell1 = worksheet.Cells [1，NonEmptyCols.Max（）+ 1]; 
 Microsoft.Office.Interop.Excel.Range cell2 = worksheet.Cells [1，NonEmptyCols.Max（）]; 
 
 //删除最后一行后的所有空行
工作表.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（）; 
} 
} 
  

I am trying to remove all extra blank rows and columns from an excel file using Interop Library.

I followed this question Fastest method to remove Empty rows and Columns From Excel Files using Interop and i find it helpful.

But i have excel files that contains a small set of data but a lot of empty rows and columns (from the last non empty row (or column) to the end of the worksheet)

I tried looping over Rows and Columns but the loop is taking hours.

I am trying to get the last non-empty row and column index so i can delete the whole empty range in one line

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

Note: i am trying to get the last row containing data to remove all extra blanks (after this row , or column)

Any suggestions?

解决方案

As you said you started from the following question:

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

And you are trying to "get the last row containing data to remove all extra blanks (after this row , or column)"

So assuming that you are working with the accept answer (provided by @JohnG), so you can add some line of code to get the last used row and column

Empty Rows are stored in a list of integer rowsToDelete

You can use the following code to get the last non empty rows with an index smaller than the last empty row

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

And if NonEmptyRows.Max() < rowsToDelete.Max() the last non-empty row is NonEmptyRows.Max() Else it is worksheet.Rows.Count and there is no empty rows after the last used one.

The same thing can be done to get the last non empty column

The code is Edited in DeleteCols and DeleteRows functions:

    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();
        }
    }

这篇关于使用Interop从Excel获取上一个非空列和行索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！