检测Excel范围中的空单元格 [英] Detecting Empty cell in Excel Range

问题描述

您好，



我必须从Excel文件中获取一系列单元格中的信息，然后转移到列表中以便稍后处理。



案例是我不知道填充单元格列表有多长，当我在填充范围内进一步读取单元格时出现错误。



其余的代码运行良好，直到我到达范围外的单元格。



我的代码如下：

Hello,

I have to get the information from a range of cells from an Excel file and transfer to a list to process it later on.

The case is that I do not know how long is the list of filled cells and I get an error when I read a cell further on the populated range.

The rest of the code works well until I reach the cell out of the range.

My code is as follows:

// Get Data
string itemPartNumber;
int itemQuantity;
int iLoop = 2; // Skip the column title
bool exitLoop = false;

ClearList(ref bomList);  //Clear my destination list

while (!exitLoop)
{
    var itemPn = xlsSheet.Cells[iLoop,1].Value2;
    var itemQu = xlsSheet.Cells[iLoop,2].Value2;

    exitLoop = (itemPn == null);
    if (!exitLoop)
    {
        itemPartNumber = (string) itemPn;
        itemQuantity = Int32.Parse(itemQu.ToString());
        ComponentPNQ itemList = new ComponentPNQ(itemPartNumber, itemQuantity);
        bomList.Add(itemList);
        iLoop++;
    }
}

我的问题是我在我给出值之前找不到正确的比较退出循环，这会产生错误（它在itemQuantity行上标记，这是一个整数。字符串itemPartNumber没有错误，但是循环永远不会结束）



我尝试了几种方法，但我无法解决它。



任何人都可以给我一个主意吗？



非常感谢您提前。



David

PS：我想附上我的excel文件作为示例，但我不顺利。

My problem is that I can not found the right comparison to exit the Loop, before I assing the values, that makes the error (it is marked on the "itemQuantity" row, that is an integer. No error with the string itemPartNumber, but then the loop never ends)

I have tryed several ways, but I am not able to solve it.

Can anyone give me an idea?

Thank you very much in advance.

David
PS: I would like to attach my excel file as example, but I do not get the way.

推荐答案

您对动态类型Value2使用隐式类型var。无论是int，string还是DateTime，Value2都将返回该类型。将动态类型解析为隐式类型，您将不知道您实际获得了什么。



如果您确定项目数量是数字，那么您可以明确定义类型

Hi,
you are using the implicit type var against the dynamic type Value2. the Value2 will return the type whether it is an int, string, or DateTime. Parsing an dynamic type to implicit type, you will not know what you are actually getting.

if you are sure that the item quantity is number, then you can explicitly define the type

int itemQu = xlsSheet.Cells[iLoop,2].Value2;

因此，如果返回的值类型错误，那么您在该点编码失败，而不是稍后失败。

另外你还有另一个转换为字符串的问题，如果返回的值是null，并且将null转换为string将引发异常。如果我知道这种类型，我就不会无缘无故地转换它。



在ItemPn和ItemQu上设一个断点，确保你收到的类型，使这些变量显式，如果需要，将Value2强制转换为正确的类型，你的while循环和数量的解析都可以工作。





编辑：

如果单元格值是文本，则Value2将返回类型字符串，如果单元格值为数字，则Value2将返回double类型。如果单元格为空，则返回类型为COM对象null。



因此您需要修改代码如下：

So if the returned value type is wrong, then you code fail in that point, rather than failing later.
Also you have another problem of converting to string, if the value returned is null, and converting a null to string will throw exception. if I know the type, I wouldn''t convert it for no reason.

Stick a break point on the ItemPn and ItemQu, make sure the type you are receiving, make those variable explicit, cast the Value2 to its correct type if you need to, the both your while loop and the parsing of the quantity will work.



If the cell value is text then the Value2 will return type string, if the cell value is number then the Value2 will return type double. if the cell is empty the return type is COM object null.

so you need amend your code as follows:

string itemPartNumber;
int itemQuantity;
int iLoop = 2; // Skip the column title

ClearList(ref bomList);  //Clear my destination list

while (!string.IsNullOrEmpty(itemPartNumber = xlsSheet.Cells[iLoop,1].Value2))
{
    itemQuantity = (int) (xlsSheet.Cells[iLoop,2].Value2 ?? 0);
    ComponentPNQ itemList = new ComponentPNQ(itemPartNumber, itemQuantity);
    bomList.Add(itemList);
    iLoop++;
}

问候

Jegan

Regards
Jegan

有看一下类似的讨论：

如何检查C＃中的Excel范围是否为空[ | ^ ]

检查excel中的单元格是否为空白 [ ^ ]

Have a look on similar discussion:
How to check if an Excel Range is empty in C#[^]
Check if a cell is blank in excel[^]

< br>

请阅读： var（C＃reference） [ ^ ]并且： Implicity Typed Local Variables（C＃编程指南） [ ^ ]。



尝试更改变量声明的类型：

Please, read this: var (C# reference)[^] and this: Implicity Typed Local Variables (C# programming guide)[^].

Try to change the type of variables declaration:

string itemPn = xlsSheet.Cells[iLoop,1].Text;
string itemQu = xlsSheet.Cells[iLoop,2].Text;

然后检查字符串 IsNullOrEmpty [ ^ ]。



;）

then check if string IsNullOrEmpty[^].

;)

这篇关于检测Excel范围中的空单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！