blocks|key|546049|text|几种方式，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|546050|using+Excel+=+Microsoft.Office.Interop.Excel;

Excel.ApplicationClass+excel+=+new+Excel.ApplicationClass();
Excel.Application+app+=+excel.Application;
Excel.Range+all+=+app.get_Range("A1:H10",+Type.Missing);|code-block|syntax|javascript|546051|或|546052|Excel.Range+last+=+sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell,+Type.Missing);
Excel.Range+range+=+sheet.get_Range("A1",+last);

int+lastUsedRow+=+last.Row;
int+lastUsedColumn+=+last.Column;|546053|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Couple ways, 

<pre><code>using Excel = Microsoft.Office.Interop.Excel;

Excel.ApplicationClass excel = new Excel.ApplicationClass();
Excel.Application app = excel.Application;
Excel.Range all = app.get_Range("A1:H10", Type.Missing);
</code></pre>

OR

<pre><code>Excel.Range last = sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = sheet.get_Range("A1", last);

int lastUsedRow = last.Row;
int lastUsedColumn = last.Column;
</code></pre>

blocks|key|762796|text|Pryank's+answer是最适合我的。我在最后添加了一点(.Row)，所以我返回的不只是一个range，而是一个integer。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|762797|int+lastRow+=+wkSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell,+Type.Missing).Row;|code-block|syntax|javascript|762798|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/7674652/65994^0|W|4|1D|5|1N|7|0|F|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|F|3|G|5|H|7|12|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|13|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

<a href="https://stackoverflow.com/a/7674652/65994">Pryank's answer</a> is what worked closest for me. I added a little bit towards the end (<code>.Row</code>) so I am not just returning a <code>range</code>, but an <code>integer</code>.

<pre><code>int lastRow = wkSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row;
</code></pre>

blocks|key|762735|text|对于涉及Excel对象模型的问题，首先在VBA中试用它通常更容易，然后再转换为C#是相当简单的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|762736|在这种情况下，在VBA中执行此操作的一种方法是：|762737|Worksheet.UsedRange.Row+%2B+Worksheet.UsedRange.Rows.Count+-+1|code-block|syntax|javascript|762738|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

For questions involving the Excel object model, it's often easier to try it out in VBA first, then translating to C# is fairly trivial.

In this case one way to do it in VBA is:

<pre><code>Worksheet.UsedRange.Row + Worksheet.UsedRange.Rows.Count - 1
</code></pre>

blocks|key|762842|text|ActiveSheet.UsedRange.Value返回[row,+column]的二维对象数组。检查两个维度的长度将提供LastRow索引和LastColumn索引。下面的示例使用的是C#。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|762843|Excel.Worksheet+activeSheet;
Excel.Range+activeRange;

public+virtual+object[,]+RangeArray+
{
++++get+{+return+ActiveRange.Value;+}
}

public+virtual+int+ColumnCount+
{
++++get+{+return+RangeArray.GetLength(1);+}
}

public+virtual+int+RowCount
{
++++get+{+return+RangeArray.GetLength(0);+}
}

public+virtual+int+LastRow+
{
++++get+{+return+RowCount;+}
}|code-block|syntax|javascript|762844|entityMap^0|0|R|T|D|1Q|7|20|A|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|L|$]]

The <code>ActiveSheet.UsedRange.Value</code> returns a 2 dimensional object array of <code>[row, column]</code>. Checking the length of both dimensions will provide the <code>LastRow</code> index and the <code>LastColumn</code> index. The example below is using C#.

<pre><code>Excel.Worksheet activeSheet;
Excel.Range activeRange;

public virtual object[,] RangeArray 
{
 get { return ActiveRange.Value; }
}

public virtual int ColumnCount 
{
 get { return RangeArray.GetLength(1); }
}

public virtual int RowCount
{
 get { return RangeArray.GetLength(0); }
}

public virtual int LastRow 
{
 get { return RowCount; }
}
</code></pre>

blocks|key|762895|text|当可能有空单元格时，这个问题就更糟了。但是，即使只填充了一个值，也必须读取一行。当有许多未填充的单元格时，这可能需要一段时间，但如果输入接近正确，则速度会相当快。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|762896|我的解决方案忽略了完全空的行，并返回最长的列的行数：|762897|private+static+int+GetLastRow(Worksheet+worksheet)
++++{
++++++++int+lastUsedRow+=+1;
++++++++Range+range+=+worksheet.UsedRange;
++++++++for+(int+i+=+1;+i+<+range.Columns.Count;+i%2B%2B)
++++++++{
++++++++++++int+lastRow+=+range.Rows.Count;
++++++++++++for+(int+j+=+range.Rows.Count;+j+>+0;+j--)
++++++++++++{
++++++++++++++++if+(lastUsedRow+<+lastRow)
++++++++++++++++{
++++++++++++++++++++lastRow+=+j;
++++++++++++++++++++if+(!String.IsNullOrWhiteSpace(Convert.ToString((worksheet.Cells[j,+i]+as+Range).Value)))
++++++++++++++++++++{
++++++++++++++++++++++++if+(lastUsedRow+<+lastRow)
++++++++++++++++++++++++++++lastUsedRow+=+lastRow;
++++++++++++++++++++++++if+(lastUsedRow+==+range.Rows.Count)
++++++++++++++++++++++++++++return+lastUsedRow+-+1;
++++++++++++++++++++++++break;
++++++++++++++++++++}
++++++++++++++++}
++++++++++++++++else
++++++++++++++++++++break;
++++++++++++}
++++++++}
++++++++return+lastUsedRow;
++++}|code-block|syntax|javascript|762898|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

This issue is even worse when there are possibly empty cells. But you have to read a row even if only one value is filled. It can take a while when there are a lot of unfilled cells but if the input is close to correct it is rather fast.

My solution ignores completely empty rows and returns the longest column's row count:

<pre><code>private static int GetLastRow(Worksheet worksheet)
 {
 int lastUsedRow = 1;
 Range range = worksheet.UsedRange;
 for (int i = 1; i &lt; range.Columns.Count; i++)
 {
 int lastRow = range.Rows.Count;
 for (int j = range.Rows.Count; j &gt; 0; j--)
 {
 if (lastUsedRow &lt; lastRow)
 {
 lastRow = j;
 if (!String.IsNullOrWhiteSpace(Convert.ToString((worksheet.Cells[j, i] as Range).Value)))
 {
 if (lastUsedRow &lt; lastRow)
 lastUsedRow = lastRow;
 if (lastUsedRow == range.Rows.Count)
 return lastUsedRow - 1;
 break;
 }
 }
 else
 break;
 }
 }
 return lastUsedRow;
 }
</code></pre>

blocks|key|546310|text|如前所述，上述技术(xlCellTypeLastCell等)不要总是提供预期的结果。尽管在列中向下迭代检查值并不困难，但有时您可能会发现有一些空单元格或包含要在后续行中考虑的数据的行。当直接使用Excel时，找到最后一行的好方法是按CTRL+%2B向下箭头键几次(对于XLSX工作表，您将到达第1048576行)，然后按CTRL+%2B向上箭头键，这将选择最后填充的单元格。如果你在录制宏时在Excel中这样做，你会得到复制它的代码，然后就是使用Microsoft.Office.Interop.Excel库为C#调整它的例子了。例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|546311|++++private+int+GetLastRow()
++++{
++++++++Excel.Application+ExcelApp;
++++++++ExcelApp+=+new+Excel.Application();

++++++++ExcelApp.Selection.End(Excel.XlDirection.xlDown).Select();
++++++++ExcelApp.Selection.End(Excel.XlDirection.xlDown).Select();
++++++++ExcelApp.Selection.End(Excel.XlDirection.xlDown).Select();

++++++++ExcelApp.Selection.End(Excel.XlDirection.xlUp).Select();

++++++++return+ExcelApp.ActiveCell.Row;
++++}|code-block|syntax|javascript|546312|这可能不是最优雅的解决方案(我想您可以在使用XlUp之前直接导航到电子表格中的最后一行)，但它似乎更可靠。|546313|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|$]]

As previously discussed, the techniques above (xlCellTypeLastCell etc.) do not always provide expected results. Although it's not difficult to iterate down through a column checking for values, sometimes you may find that there are empty cells or rows with data that you want to consider in subsequent rows. When using Excel directly, a good way of finding the last row is to press CTRL + Down Arrow a couple of times (you'll end up at row 1048576 for an XLSX worksheet) and then press CTRL + Up Arrow which will select the last populated cell. If you do this within Excel while recording a Macro you'll get the code to replicate this, and then it's just a case of tweaking it for C# using the Microsoft.Office.Interop.Excel libraries. For example:

<pre><code> private int GetLastRow()
 {
 Excel.Application ExcelApp;
 ExcelApp = new Excel.Application();

 ExcelApp.Selection.End(Excel.XlDirection.xlDown).Select();
 ExcelApp.Selection.End(Excel.XlDirection.xlDown).Select();
 ExcelApp.Selection.End(Excel.XlDirection.xlDown).Select();

 ExcelApp.Selection.End(Excel.XlDirection.xlUp).Select();

 return ExcelApp.ActiveCell.Row;
 }
</code></pre>

It may not be the most elegant solution (I guess instead you could navigate to the final row within the spreadsheet first directly before using XlUp) but it seems to be more reliable.

blocks|key|763000|text|正如CtrlDot和Leo+Guardian所说，它不是很敏锐的方法，有一些文件的格式影响"SpecialCells“。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|763001|所以我使用了一个组合，外加一段时间。|763002|Range+last+=+sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell,+Type.Missing);
Range+range+=+sheet.get_Range("A1",+last);
int+lastrow+=+last.Row;
//+Complement+to+confirm+that+the+last+row+is+the+last
string+textCell=+"Existe";
while+(textCell+!=+null)
{
+lastrow%2B%2B;
+textCell+=+sheet.Cells[lastrow+%2B+1,+1].Value;
}|code-block|syntax|javascript|763003|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

As CtrlDot and Leo Guardian says, it is not very acuarate the method, there some files where formats affect the &quot;SpecialCells&quot;.
So I used a combination of that plus a While.
<pre><code>Range last = sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);
Range range = sheet.get_Range(&quot;A1&quot;, last);
int lastrow = last.Row;
// Complement to confirm that the last row is the last
string textCell= &quot;Existe&quot;;
while (textCell != null)
{
 lastrow++;
 textCell = sheet.Cells[lastrow + 1, 1].Value;
}
 
 
</code></pre>

I am trying to get the last row of an excel sheet programatically using the Microsoft.interop.Excel Library and C#. I want to do that, because I am charged with looping through all the records of an excel spreadsheet and performing some kind of operation on them. Specifically, I need the actual number of the last row, as I will throw this number into a function. Anybody have any idea how to do that?

Programmatically getting the last filled excel row using C#

我正在尝试使用Microsoft.interop.Excel库和C#以编程方式获取excel工作表的最后一行。我之所以这样做，是因为我负责遍历excel电子表格中的所有记录，并对它们执行某种操作。具体地说，我需要最后一行的实际数字，因为我将把这个数字放入一个函数中。有谁知道怎么做吗？

问使用C#以编程方式获取最后填充的excel行
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用C#以编程方式获取最后填充的excel行EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用C#以编程方式获取最后填充的excel行
EN