如何获得在Excel工作表中占用的单元格区域 [英] How to get the range of occupied cells in excel sheet

问题描述

我用C＃自动化Excel文件。我能得到的工作簿，它包含的表。
例如，如果我有在Sheet1 2议和5行。我想Ø得到被占领的细胞为A1的范围：B5。我尝试以下code，但它并没有给出正确的结果。
列＃和行＃分别大得多和细胞空为好。

I use C# to automate an excel file. I was able to get the workbook and the sheets it contains. If for example I have in sheet1 two cols and 5 rows. I wanted o get the range for the occupied cells as A1:B5. I tried the following code but it did not give the correct result. the columns # and row # were much bigger and the cells were empty as well.

     Excel.Range xlRange = excelWorksheet.UsedRange;
     int col = xlRange.Columns.Count;
     int row = xlRange.Rows.Count;

有另一种方式，我可以用得到的范围是多少？

Is there another way I can use to get that range?

推荐答案

。我有一个非常类似的问题，因为你有。什么实际工作是这样的：

Though you have already selected an answer but that didn't work for me. I had a very similar issue as you had. What actually worked is this:

iTotalColumns = xlWorkSheet.UsedRange.Columns.Count;
iTotalRows = xlWorkSheet.UsedRange.Rows.Count;

//These two lines do the magic.
xlWorkSheet.Columns.ClearFormats();
xlWorkSheet.Rows.ClearFormats();

iTotalColumns = xlWorkSheet.UsedRange.Columns.Count;
iTotalRows = xlWorkSheet.UsedRange.Rows.Count;

恕我直言，会发生什么情况是，当你从Excel中删除数据时，它不断在想，有这些单元格的数据，虽然他们都是空白。当我清除格式，它消除了空白单元格，因此返回实际计数。

IMHO what happens is that when you delete data from Excel, it keeps on thinking that there is data in those cells, though they are blank. When I cleared the formats, it removes the blank cells and hence returns actual counts.

这篇关于如何获得在Excel工作表中占用的单元格区域的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！