使用Interop从excel中获取最后一个非空列和行索引
我正在尝试使用Interop Library从excel文件中删除所有多余的空白行和列。
我跟踪了这个问题Fastest method to remove Empty rows and Columns From Excel Files using Interop，我发现它很有帮助。
但是我的excel文件只包含一小部分数据，但有许多空行和空列(从最后一个非空行(或列)到工作表的末尾)。
我试着对行和列进行循环，但是循环花费了几个小时。
我正在尝试获取最后一个非空行和列索引，这样我就可以在一行中删除整个空范围
XlWks.Range("...").EntireRow.Delete(xlShiftUp)
注意:我正在尝试获取包含数据的最后一行，以删除所有额外的空白(在此行或列之后)
有什么建议吗？
注意:代码必须与SSIS脚本任务环境兼容
回答 5
Stack Overflow用户
发布于 2017-05-14 23:48:11
几年前，我创建了一个MSDN代码示例，允许开发人员从工作表中获取最近使用的行和列。我修改了它，将所有需要的代码放在一个带有windows窗体前端的类库中，以演示操作。
底层代码使用Microsoft.Office.Interop.Excel。
Microsoft one drive https://1drv.ms/u/s!AtGAgKKpqdWjiEGdBzWDCSCZAMaM上的位置
在这里，我获取了Excel文件中的第一张表，获取了最后使用的行和列，并将其表示为一个有效的单元格地址。
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在演示项目中，我还获得了excel文件的所有工作表，并将它们呈现在ListBox中。从列表框中选择一个工作表名称，并在有效的单元格地址中获取该工作表的最后一行和最后一列。
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乍一看，从上面的链接打开解决方案时，您会注意到有很多代码。代码是最优的，并且将立即释放所有对象。
Stack Overflow用户
发布于 2017-05-13 22:47:22
我使用的是ClosedXml，它有有用的'LastUsedRow‘和'LastUsedColumn’方法。
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();这个简单的循环在38秒内删除了10000行中的5000行。不是很快，但比“小时”好多了。这取决于您正在处理的行/列的数量，当然，您不会说出来。然而，在进一步测试50000个空行中的25000个之后，删除循环中的空行确实需要大约30分钟。显然，删除行不是一个有效的过程。
更好的解决方案是创建一个新工作表，然后复制要保留的行。
步骤1-创建有50000行和20列的工作表，每隔一行和列为空。
var wb = new XLWorkbook(@"C:\Users\passp\Documents\test.xlsx");
var sheet = wb.Worksheet("Sheet1");
sheet.Clear();
for (int i = 1; i < 50000; i+=2)
{
var row = sheet.Row(i);
for (int j = 1; j < 20; j += 2)
{
row.Cell(j).Value = i * j;
}
}第2步-将包含数据的行复制到新工作表。这需要10秒钟。
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 + 1} )
.ForEach(r =>
{
var newRow = sheet2.Row(r.Index);
r.Row.CopyTo(newRow);
}
);
wb.Save();步骤3-这将对列执行相同的操作。
Stack Overflow用户
发布于 2017-05-19 08:46:02
让我们假设包含数据的最后一个角落单元格是J16 -因此在列K中没有数据，在第17行中没有数据。场景是什么?你想要实现什么？是否正在清除显示空字符串的公式？
在任何情况下，循环都不是解决问题的方法。
会比循环快得多。
//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 + 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 columnshttps://stackoverflow.com/questions/43910117
复制相似问题