blocks|key|1645998|text|您应该能够找到最后一个非空行和列，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1645999|with+m_XlWrkSheet
lastRow+=+.UsedRange.Rows.Count
lastCol+=+.UsedRange.Columns.Count
end+with|code-block|syntax|javascript|1646000|这是VB.NET，但它应该或多或少会起作用。这将返回第16行和第10列(基于上图)。然后，您可以使用它来查找要在一行中全部删除的范围。|1646001|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You should be able to find the last non-empty row and column with something similar to this:

<pre><code>with m_XlWrkSheet
lastRow = .UsedRange.Rows.Count
lastCol = .UsedRange.Columns.Count
end with
</code></pre>

That's VB.NET, but it should more or less work. That will return Row 16 and Column 10 (based on your picture above). Then you can use that to find the range you want to delete all in one line.

blocks|key|1860161|text|我使用的是ClosedXml，它有有用的'LastUsedRow‘和'LastUsedColumn’方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1860162|var+wb+=+new+XLWorkbook(@"<path>\test.xlsx",+XLEventTracking.Disabled);
var+sheet+=+wb.Worksheet("Sheet1");

for+(int+i+=+sheet.LastRowUsed().RowNumber()+-+1;+i+>=+1;+i--)
{
++++var+row+=+sheet.Row(i);
++++if+(row.IsEmpty())
++++{
++++++++row.Delete();
++++}
}

wb.Save();|code-block|syntax|javascript|1860163|这个简单的循环在38秒内删除了10000行中的5000行。不是很快，但比“小时”好多了。这取决于您正在处理的行/列的数量，当然，您不会说出来。然而，在进一步测试50000个空行中的25000个之后，删除循环中的空行确实需要大约30分钟。显然，删除行不是一个有效的过程。|1860164|更好的解决方案是创建一个新工作表，然后复制要保留的行。|1860165|步骤1-创建有50000行和20列的工作表，每隔一行和列为空。|1860166|var+wb+=+new+XLWorkbook(@"C:\Users\passp\Documents\test.xlsx");
var+sheet+=+wb.Worksheet("Sheet1");
sheet.Clear();

for+(int+i+=+1;+i+<+50000;+i%2B=2)
{
++++var+row+=+sheet.Row(i);

++++for+(int+j+=+1;+j+<+20;+j+%2B=+2)
++++{
++++++++row.Cell(j).Value+=+i+*+j;
++++}
}|1860167|第2步-将包含数据的行复制到新工作表。这需要10秒钟。|1860168|var+wb+=+new+XLWorkbook(@"C:\Users\passp\Documents\test.xlsx",+XLEventTracking.Disabled);
var+sheet+=+wb.Worksheet("Sheet1");

var+sheet2+=+wb.Worksheet("Sheet2");
sheet2.Clear();

sheet.RowsUsed()
++++.Where(r+=>+!r.IsEmpty())
++++.Select((r,+index)+=>+new+{+Row+=+r,+Index+=+index+%2B+1}+)
++++.ForEach(r+=>
++++{
++++++++var+newRow+=+sheet2.Row(r.Index);

++++++++r.Row.CopyTo(newRow);
++++}
);

wb.Save();|1860169|步骤3-这将对列执行相同的操作。|1860170|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

I'm using ClosedXml which has useful 'LastUsedRow' and 'LastUsedColumn' methods.

<pre><code>var wb = new XLWorkbook(@"&lt;path&gt;\test.xlsx", XLEventTracking.Disabled);
var sheet = wb.Worksheet("Sheet1");

for (int i = sheet.LastRowUsed().RowNumber() - 1; i &gt;= 1; i--)
{
 var row = sheet.Row(i);
 if (row.IsEmpty())
 {
 row.Delete();
 }
}

wb.Save();
</code></pre>

This simple loop deleted 5000 out of 10000 rows in 38 seconds. Not fast, but a lot better than 'hours'. That depends on how many rows/columns you're dealing with of course which you don't say.
However, after further tests with 25000 empty rows out of 50000 it does take about 30 minutes to delete the empty rows in a loop. Clearly deleting rows isn't an efficient process.

A better solution is to create a new sheet and then copy the rows you want to keep.

Step 1 - create sheet with 50000 rows and 20 columns, every other row and column is empty.

<pre><code>var wb = new XLWorkbook(@"C:\Users\passp\Documents\test.xlsx");
var sheet = wb.Worksheet("Sheet1");
sheet.Clear();

for (int i = 1; i &lt; 50000; i+=2)
{
 var row = sheet.Row(i);

 for (int j = 1; j &lt; 20; j += 2)
 {
 row.Cell(j).Value = i * j;
 }
}
</code></pre>

Step 2 - copy the rows with data to a new sheet. This takes 10 seconds.

<pre><code>var wb = new XLWorkbook(@"C:\Users\passp\Documents\test.xlsx", XLEventTracking.Disabled);
var sheet = wb.Worksheet("Sheet1");

var sheet2 = wb.Worksheet("Sheet2");
sheet2.Clear();

sheet.RowsUsed()
 .Where(r =&gt; !r.IsEmpty())
 .Select((r, index) =&gt; new { Row = r, Index = index + 1} )
 .ForEach(r =&gt;
 {
 var newRow = sheet2.Row(r.Index);

 r.Row.CopyTo(newRow);
 }
);

wb.Save();
</code></pre>

Step 3 - this would be to do the same operation for the columns.

blocks|key|1646126|text|几年前，我创建了一个MSDN代码示例，允许开发人员从工作表中获取最近使用的行和列。我修改了它，将所有需要的代码放在一个带有windows窗体前端的类库中，以演示操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1646127|底层代码使用Microsoft.Office.Interop.Excel。|1646128|Microsoft+one+drive+https://1drv.ms/u/s!AtGAgKKpqdWjiEGdBzWDCSCZAMaM上的位置|offset|length|1646129|在这里，我获取了Excel文件中的第一张表，获取了最后使用的行和列，并将其表示为一个有效的单元格地址。|1646130|Private+Sub+cmdAddress1_Click(sender+As+Object,+e+As+EventArgs)+Handles+cmdAddress1.Click
++++Dim+ops+As+New+GetExcelColumnLastRowInformation
++++Dim+info+=+New+UsedInformation
++++ExcelInformationData+=+info.UsedInformation(FileName,+ops.GetSheets(FileName))

++++Dim+SheetName+As+String+=+ExcelInformationData.FirstOrDefault.SheetName

++++Dim+cellAddress+=+(
++++++++From+item+In+ExcelInformationData
++++++++Where+item.SheetName+=+ExcelInformationData.FirstOrDefault.SheetName
++++++++Select+item.LastCell).FirstOrDefault

++++MessageBox.Show($"{SheetName}+-+{cellAddress}")

End+Sub|code-block|syntax|javascript|1646131|在演示项目中，我还获得了excel文件的所有工作表，并将它们呈现在ListBox中。从列表框中选择一个工作表名称，并在有效的单元格地址中获取该工作表的最后一行和最后一列。|1646132|Private+Sub+cmdAddress_Click(sender+As+Object,+e+As+EventArgs)+Handles+cmdAddress.Click
++++Dim+cellAddress+=
++++++++(
++++++++++++From+item+In+ExcelInformationData
++++++++++++Where+item.SheetName+=+ListBox1.Text
++++++++++++Select+item.LastCell).FirstOrDefault

++++If+cellAddress+IsNot+Nothing+Then
++++++++MessageBox.Show($"{ListBox1.Text}+{cellAddress}")
++++End+If

End+Sub|1646133|乍一看，从上面的链接打开解决方案时，您会注意到有很多代码。代码是最优的，并且将立即释放所有对象。|1646134|entityMap|0|LINK|mutability|MUTABLE|url|https://1drv.ms/u/s!AtGAgKKpqdWjiEGdBzWDCSCZAMaM^0|0|0|K|1C|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|14|8|@]|9|@$F|15|G|16|1|17]]|A|$]]|$1|H|3|I|5|6|7|18|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|19|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Q|3|R|5|L|7|1B|8|@]|9|@]|A|$M|N]]|$1|S|3|T|5|6|7|1C|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|11]]]]

Several years ago I created a MSDN code sample that permits a developer to get the last used row and column from a worksheet. I modified it, placed all needed code into a class library with a windows form front end to demo the operation.

Underlying code uses Microsoft.Office.Interop.Excel.

Location on Microsoft one drive
<a href="https://1drv.ms/u/s!AtGAgKKpqdWjiEGdBzWDCSCZAMaM" rel="noreferrer">https://1drv.ms/u/s!AtGAgKKpqdWjiEGdBzWDCSCZAMaM</a>

Here I get the first sheet in an Excel file, get the last used row and col and present as a valid cell address.

<pre><code>Private Sub cmdAddress1_Click(sender As Object, e As EventArgs) Handles cmdAddress1.Click
 Dim ops As New GetExcelColumnLastRowInformation
 Dim info = New UsedInformation
 ExcelInformationData = info.UsedInformation(FileName, ops.GetSheets(FileName))

 Dim SheetName As String = ExcelInformationData.FirstOrDefault.SheetName

 Dim cellAddress = (
 From item In ExcelInformationData
 Where item.SheetName = ExcelInformationData.FirstOrDefault.SheetName
 Select item.LastCell).FirstOrDefault

 MessageBox.Show($"{SheetName} - {cellAddress}")

End Sub
</code></pre>

Within the demo project I also get all sheets for an excel file, present them in a ListBox. Select a sheet name from the list box and get that sheet's last row and column in a valid cell address.

<pre><code>Private Sub cmdAddress_Click(sender As Object, e As EventArgs) Handles cmdAddress.Click
 Dim cellAddress =
 (
 From item In ExcelInformationData
 Where item.SheetName = ListBox1.Text
 Select item.LastCell).FirstOrDefault

 If cellAddress IsNot Nothing Then
 MessageBox.Show($"{ListBox1.Text} {cellAddress}")
 End If

End Sub
</code></pre>

Upon first glance when opening the solution from the link above you will note there is a lot of code. The code is optimal and will release all objects immediately.

blocks|key|1860426|text|让我们假设包含数据的最后一个角落单元格是J16+-因此在列K中没有数据，在第17行中没有数据。场景是什么?你想要实现什么？是否正在清除显示空字符串的公式？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1860427|在任何情况下，循环都不是解决问题的方法。|1860428|会比循环快得多。|1860429|//code+uses+variables+declared+appropriately+as+Excel.Range+&+Excel.Worksheet+Using+Interop+library
int+x;
int+y;
//+get+the+row+of+the+last+value+content+row-wise
oRange+=+oSheet.Cells.Find(What:+"*",+
+++++++++++++++++++++++++++After:+oSheet.get_Range("A1"),
+++++++++++++++++++++++++++LookIn:+XlFindLookIn.xlValues,
+++++++++++++++++++++++++++LookAt:+XlLookAt.xlPart,+
+++++++++++++++++++++++++++SearchDirection:+XlSearchDirection.xlPrevious,
+++++++++++++++++++++++++++SearchOrder:+XlSearchOrder.xlByRows);

if+(oRange+==+null)
{
++++return;
}
x+=+oRange.Row;

//+get+the+column+of+the+last+value+content+column-wise
oRange+=+oSheet.Cells.Find(What:+"*",
+++++++++++++++++++++++++++After:+oSheet.get_Range("A1"),
+++++++++++++++++++++++++++LookIn:+XlFindLookIn.xlValues,+LookAt:+XlLookAt.xlPart,
+++++++++++++++++++++++++++SearchDirection:+XlSearchDirection.xlPrevious,
+++++++++++++++++++++++++++SearchOrder:+XlSearchOrder.xlByColumns);
y+=+oRange.Column;

//+now+we+have+the+corner+(x,+y),+we+can+delete+or+clear+all+content+to+the+right+and+below
//+say+J16+is+the+cell,+so+x+=+16,+and+j=10

Excel.Range+clearRange;

//set+clearRange+to+("K1:XFD1048576")
clearRange+=+oSheet.Range[oSheet.Cells[1,+y+%2B+1],+oSheet.Cells[oSheet.Rows.Count,+oSheet.Columns.Count]];
clearRange.Clear();+//clears+all+content,+formulas+and+formatting
//clearRange.Delete();+if+you+REALLY+want+to+hard+delete+the+rows

//set+clearRange+to+("A17:J1048576")++++++++++++
clearRange+=+oSheet.Range[oSheet.Cells[x+%2B+1,+1],+oSheet.Cells[oSheet.Rows.Count,+y]];
clearRange.Clear();+//clears+all+content,+formulas+and+formatting
//clearRange.Delete();++if+you+REALLY+want+to+hard+delete+the+columns|code-block|syntax|javascript|1860430|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Let's say the last corner cell with data is J16 - so no data in columns K onwards, or in rows 17 downwards. Why are you actually deleting them? What is the scenario and what are you trying to achieve? Is it clearing our formatting? Is is clearing our formulas which show an empty string? 

In any case, looping is not the way. 

The code below shows a way to use the Clear() method of Range object to clear all contents and formulas and formatting from a range. Alternatively if you really want to delete them, you can use the Delete() method to delete a whole rectangular Range in one hit. Will be much faster than looping...

<pre><code>//code uses variables declared appropriately as Excel.Range &amp; Excel.Worksheet Using Interop library
int x;
int y;
// get the row of the last value content row-wise
oRange = oSheet.Cells.Find(What: "*", 
 After: oSheet.get_Range("A1"),
 LookIn: XlFindLookIn.xlValues,
 LookAt: XlLookAt.xlPart, 
 SearchDirection: XlSearchDirection.xlPrevious,
 SearchOrder: XlSearchOrder.xlByRows);

if (oRange == null)
{
 return;
}
x = oRange.Row;

// get the column of the last value content column-wise
oRange = oSheet.Cells.Find(What: "*",
 After: oSheet.get_Range("A1"),
 LookIn: XlFindLookIn.xlValues, LookAt: XlLookAt.xlPart,
 SearchDirection: XlSearchDirection.xlPrevious,
 SearchOrder: XlSearchOrder.xlByColumns);
y = oRange.Column;

// now we have the corner (x, y), we can delete or clear all content to the right and below
// say J16 is the cell, so x = 16, and j=10

Excel.Range clearRange;

//set clearRange to ("K1:XFD1048576")
clearRange = oSheet.Range[oSheet.Cells[1, y + 1], oSheet.Cells[oSheet.Rows.Count, oSheet.Columns.Count]];
clearRange.Clear(); //clears all content, formulas and formatting
//clearRange.Delete(); if you REALLY want to hard delete the rows

//set clearRange to ("A17:J1048576") 
clearRange = oSheet.Range[oSheet.Cells[x + 1, 1], oSheet.Cells[oSheet.Rows.Count, y]];
clearRange.Clear(); //clears all content, formulas and formatting
//clearRange.Delete(); if you REALLY want to hard delete the columns
</code></pre>

blocks|key|1646290|text|我想你可以试着用这个范围。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1646291|++++++++Application+excel+=+new+Application();
++++++++Workbook+workBook=++excel.Workbooks.Open("file.xlsx")
++++++++Worksheet+excelSheet+=+workBook.ActiveSheet;
++++++++Range+excelRange+=+excelSheet.UsedRange.Columns[1,+Missing.Value]+as+Range;

++++++++var+lastNonEmptyRow+=+excelRange.Cells.Count;|code-block|syntax|javascript|1646292|上面的代码适用于我。|1646293|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I think you can try using the Range.

<pre><code> Application excel = new Application();
 Workbook workBook= excel.Workbooks.Open("file.xlsx")
 Worksheet excelSheet = workBook.ActiveSheet;
 Range excelRange = excelSheet.UsedRange.Columns[1, Missing.Value] as Range;

 var lastNonEmptyRow = excelRange.Cells.Count;
</code></pre>

The above code works for me.

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

I followed this question <a href="https://stackoverflow.com/questions/40574084/fastest-method-to-remove-empty-rows-and-columns-from-excel-files-using-interop">Fastest method to remove Empty rows and Columns From Excel Files using Interop</a> 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 

<pre><code>XlWks.Range("...").EntireRow.Delete(xlShiftUp)
</code></pre>

<a href="https://i.stack.imgur.com/QTYcU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QTYcU.png" alt="enter image description here"></a>

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

Any suggestions?

<hr>

Note: The code must be compatible with SSIS Script Task environment

Get Last non empty column and row index from excel using Interop

我正在尝试使用Interop Library从excel文件中删除所有多余的空白行和列。我跟踪了这个问题，我发现它很有帮助。但是我的excel文件只包含一小部分数据，但有许多空行和空列(从最后一个非空行(或列)到工作表的末尾)。我试着对行和列进行循环，但是循环花费了几个小时。我正在尝试获取最后一个非空行和列索引，这样我...

问使用Interop从excel中获取最后一个非空列和行索引
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Interop从excel中获取最后一个非空列和行索引EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Interop从excel中获取最后一个非空列和行索引
EN