如何获取excel表格中被占用单元格的范围 [英] How to get the range of occupied cells in excel sheet

问题描述

我使用 C# 来自动化一个 excel 文件.我能够获得工作簿及其包含的工作表.例如，如果我在 sheet1 中有两个列和 5 行.我想获得被占用单元格的范围为 A1:B5.我尝试了以下代码，但没有给出正确的结果.列 # 和行 # 大得多，单元格也是空的.

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?

推荐答案

我遇到了与您非常相似的问题.实际工作是这样的:

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屋！