C# 如何获取excel表格中被占用单元格的范围

How to get the range of occupied cells in excel sheet

提问by Sarah

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.

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

     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?

我可以用另一种方法来获得该范围吗？

回答by Joe Erickson

See the Range.SpecialCells method. For example, to get cells with constant values or formulas use:

请参阅 Range.SpecialCells 方法。例如，要获取具有常量值或公式的单元格，请使用：

_xlWorksheet.UsedRange.SpecialCells(
        Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants |
        Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeFormulas)

回答by Patrick Honorez

dim lastRow as long   'in VBA it's a long 
lastrow = wks.range("A65000").end(xlup).row

回答by Zurb

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

"range" will now be the occupied cell range

“范围”现在将是被占用的单元格范围

回答by sidon

Bit old question now, but if somebody is looking for solution this works for me.

现在有点老问题，但如果有人正在寻找解决方案，这对我有用。

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);

回答by Farhan

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;

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 中删除数据时，它一直认为这些单元格中有数据，尽管它们是空白的。当我清除格式时，它会删除空白单元格并因此返回实际计数。

回答by Gerhard Powell

The only way I could get it to work in ALL scenarios (except Protected sheets) (based on Farham's Answer):

我可以让它在所有场景中工作的唯一方法（受保护的工作表除外）（基于 Farham 的回答）：

It supports:

它支持：

• Scanning Hidden Row / Columns

• Ignores formatted cells with no data / formula

• 扫描隐藏的行/列

• 忽略没有数据/公式的格式化单元格

Code:

代码：

// Unhide All Cells and clear formats
sheet.Columns.ClearFormats();
sheet.Rows.ClearFormats();

// Detect Last used Row - Ignore cells that contains formulas that result in blank values
int lastRowIgnoreFormulas = sheet.Cells.Find(
                "*",
                System.Reflection.Missing.Value,
                InteropExcel.XlFindLookIn.xlValues,
                InteropExcel.XlLookAt.xlWhole,
                InteropExcel.XlSearchOrder.xlByRows,
                InteropExcel.XlSearchDirection.xlPrevious,
                false,
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value).Row;
// Detect Last Used Column  - Ignore cells that contains formulas that result in blank values
int lastColIgnoreFormulas = sheet.Cells.Find(
                "*",
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value,
                InteropExcel.XlSearchOrder.xlByColumns,
                InteropExcel.XlSearchDirection.xlPrevious,
                false,
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value).Column;

// Detect Last used Row / Column - Including cells that contains formulas that result in blank values
int lastColIncludeFormulas = sheet.UsedRange.Columns.Count;
int lastColIncludeFormulas = sheet.UsedRange.Rows.Count;

回答by Simon

These two lines on their own wasnt working for me:

这两行本身对我不起作用：

xlWorkSheet.Columns.ClearFormats();
xlWorkSheet.Rows.ClearFormats();

You can test by hitting ctrl+end in the sheet and seeing which cell is selected.

您可以通过在工作表中按 ctrl+end 并查看选择了哪个单元格来进行测试。

I found that adding this line after the first two solved the problem in all instances I've encountered:

我发现在前两行之后添加这一行解决了我遇到的所有情况下的问题：

Excel.Range xlActiveRange = WorkSheet.UsedRange;

回答by Utsav Jha

You should try the currentRegion property, if you know from where you are to find the range. This will give you the boundaries of your used range.

如果您知道从哪里找到范围，您应该尝试使用 currentRegion 属性。这将为您提供使用范围的边界。

回答by MikeJ

This is tailored to finding formulas but you should be able to expand it to general content by altering how you test the starting cells. You'll have to handle single cell ranges outside of this.

这是专为查找公式而设计的，但您应该能够通过改变测试起始单元格的方式将其扩展到一般内容。您必须处理除此之外的单个单元格范围。

    public static Range GetUsedPartOfRange(this Range range)
    {
        Excel.Range beginCell = range.Cells[1, 1];
        Excel.Range endCell = range.Cells[range.Rows.Count, range.Columns.Count];

        if (!beginCell.HasFormula)
        {
            var beginCellRow = range.Find(
                "*",
                beginCell,
                XlFindLookIn.xlFormulas,
                XlLookAt.xlPart,
                XlSearchOrder.xlByRows,
                XlSearchDirection.xlNext,
                false);

            var beginCellCol = range.Find(
                "*",
                beginCell,
                XlFindLookIn.xlFormulas,
                XlLookAt.xlPart,
                XlSearchOrder.xlByColumns,
                XlSearchDirection.xlNext,
                false);

            if (null == beginCellRow || null == beginCellCol)
                return null;

            beginCell = range.Worksheet.Cells[beginCellRow.Row, beginCellCol.Column];
        }

        if (!endCell.HasFormula)
        {
            var endCellRow = range.Find(
            "*",
            endCell,
            XlFindLookIn.xlFormulas,
            XlLookAt.xlPart,
            XlSearchOrder.xlByRows,         
            XlSearchDirection.xlPrevious,
            false);

            var endCellCol = range.Find(
                "*",
                endCell,
                XlFindLookIn.xlFormulas,
                XlLookAt.xlPart,
                XlSearchOrder.xlByColumns,
                XlSearchDirection.xlPrevious,
                false);

            if (null == endCellRow || null == endCellCol)
                return null;

            endCell = range.Worksheet.Cells[endCellRow.Row, endCellCol.Column];
        }

        if (null == endCell || null == beginCell)
            return null;

        Excel.Range finalRng = range.Worksheet.Range[beginCell, endCell];

        return finalRng;
    }
}

回答by codeislife

You should not delete the data in box by pressing "delete", i think thats the problem , because excel will still detected the box as "" <- still have value, u should delete by right click the box and click delete.

您不应该通过按“删除”来删除框中的数据，我认为这就是问题所在，因为 excel 仍会将该框检测为“” <- 仍然有价值，您应该通过右键单击该框并单击删除来删除。