仅获取包含数据（数字）的单元格范围

发布于 2025-01-06 04:59:21 字数 1879 浏览 3 评论 0原文

有一张 MS Excel 工作表，其中填充了一个矩阵数据。

如何仅获取数字区域？

现在我正在使用 workSheet.UsedRange 提取数据范围。但是如果我删除一根矩阵线，即使该线已被删除，它也会被考虑在usedRange中！那只是因为我以前使用过这些细胞。

那么，我怎样才能只获得包含实际事物（数字）的范围？

代码：

public Array ReadData(string fullPath, Size size)
{
    Excel.Application application;
    Workbook workBook;
    Worksheet workSheet;
    Range range;

    application = new Excel.ApplicationClass();
    workBook = application.Workbooks.Open(fullPath, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    workSheet = (Worksheet)workBook.Worksheets.get_Item(1);

    //HERE IS THE PROBLEM
    range = workSheet.UsedRange; 

    if (!size.IsEmpty)
    {
        Size availableSize = new Size(range.Columns.Count, range.Rows.Count);
        if ((availableSize.Width < size.Width) || (availableSize.Height < size.Height))
        {
            string msg = string.Format("Available data range ({0}) at the sheet is smaller than needed size ({1})", availableSize, size);
            throw new ArgumentException(msg);
        }
        else if ((availableSize.Width > size.Width) || (availableSize.Height > size.Height))
        {
            range = workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[size.Width, size.Height]);
            string msg = string.Format("Available data range ({0}) at the sheet is bigger than needed size ({1})", availableSize, size);
            Trace.TraceInformation(msg);
        }
    }

    Array data;

    data = (Array)range.Cells.Value2;

    return data;
}

我认为可能的解决方案是在 VB 中发布的。在 C# 中将是：

        range = workSheet.UsedRange;
        range = range.CurrentRegion.SpecialCells(XlCellType.xlCellTypeConstants, XlSpecialCellsValue.xlNumbers);

但它会引发“未找到单元格”的异常

原文

There is a MS Excel sheet filled with one matrix data.

How can I get only the numbers region?

Right now I'm extracting the data range with the workSheet.UsedRange. But if I delete one matrix line, it will be considered in the usedRange, even though the line has been deleted! That's just because I have previously used those cells.

So, how can I get only the range with actual things (numbers) on it?

The code:

public Array ReadData(string fullPath, Size size)
{
    Excel.Application application;
    Workbook workBook;
    Worksheet workSheet;
    Range range;

    application = new Excel.ApplicationClass();
    workBook = application.Workbooks.Open(fullPath, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    workSheet = (Worksheet)workBook.Worksheets.get_Item(1);

    //HERE IS THE PROBLEM
    range = workSheet.UsedRange; 

    if (!size.IsEmpty)
    {
        Size availableSize = new Size(range.Columns.Count, range.Rows.Count);
        if ((availableSize.Width < size.Width) || (availableSize.Height < size.Height))
        {
            string msg = string.Format("Available data range ({0}) at the sheet is smaller than needed size ({1})", availableSize, size);
            throw new ArgumentException(msg);
        }
        else if ((availableSize.Width > size.Width) || (availableSize.Height > size.Height))
        {
            range = workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[size.Width, size.Height]);
            string msg = string.Format("Available data range ({0}) at the sheet is bigger than needed size ({1})", availableSize, size);
            Trace.TraceInformation(msg);
        }
    }

    Array data;

    data = (Array)range.Cells.Value2;

    return data;
}

The possible solution was post in VB I think. In C# will be:

        range = workSheet.UsedRange;
        range = range.CurrentRegion.SpecialCells(XlCellType.xlCellTypeConstants, XlSpecialCellsValue.xlNumbers);

but it raises an excpetion "no cells found"

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

辞取 2025-01-13 04:59:21

我不太了解 C#，但在 Excel 中，您可以使用
Range("yourRange").SpecialCells(xlCellTypeConstants, xlNumbers) 或
范围("yourRange").CurrentRegion.SpecialCells(xlCellTypeConstants, xlNumbers)

http://msdn.microsoft.com/en-us/library/aa213567(v=office.11).aspx
或
VSTO 中的特殊单元
或
如何获取Excel工作表中占用单元格的范围