Worksheet.Cells[x,y].Value=NUL...

Question

5.00/5 (1 vote)

See more:

, +

Hi community of Code Project,

im having a seroius trouble with reading data of an xls with the office.interop.excel.

My big problem is, that i read a cell in an xls, where content is defentily. When u open this xls with excel directly u can see that the value of the cell is "101". But when i try to read it with

C#

string value = worksheet.Cells[1,1].Value as string;

i get null returned. The cell beside the one with "101" as content has as content "102" and on "103, 104, 105". I can read all of them. Just the 101 cell is returning NULL.

And im totally weird. SR guys but i cant upload this xls, its not for the public available.

So i have to ask if anyone has any idea what can be with this cell???

tia Synergi

Posted 12-Nov-12 23:10pm

SynergiAios

Add a Solution

Comments

Richard MacCutchan 13-Nov-12 5:15am

Have you checked the spreadsheet to be sure of what is in that cell?

SynergiAios 13-Nov-12 5:48am

Sr i cant understand ur question really. If u mean if i have checked in a real Excel 2007 if the content of the cell is "101", then i can say yes. Its a combined Cell of 2 cells. the content of these two cells is the text "101".

Richard MacCutchan 13-Nov-12 5:56am

Its a combined Cell of 2 cells.

~~Then it is not Cells[1,1].~~

OK, I have just tested this and it works OK, there must be something else in this cell that is preventing you from getting the value.

SynergiAios 13-Nov-12 6:27am

Thx for this advice but im know this x) it was just as an example badly descriped of me. To have an complete example lets say we have an xls with the following

Cell[1,1] & Cell[2,1] are combined with the content "101"
Cell[3,1] & Cell[4,1] are combined with the content "102"
Cell[5,1] & Cell[6,1] are combined with the content "103"
...

So the Cells in Code with the content would be: (allways the first cell of the combined one)

Cell[1,1] = "101"
Cell[3,1] = "102"
Cell[5,1] = "103"
....

So my Problem is that in Cell[1,1] = NULL, Cell[3,1] = "102", Cell[5,1] = "103", ...

Why is Cell[1,1] = NULL.

Thats my exact problem

Richard MacCutchan 13-Nov-12 6:33am

I have just created a spreadsheet with values and combined cells as you describe, and I get the correct values in each case. There must still be something in your data or in your program which is incorrect.

SynergiAios 13-Nov-12 8:58am

Ok i have no managed to make an copy of the xls and change it so i can upload it to the public. Here ist the xls to download

http://www27.zippyshare.com/v/5152649/file.html

Try to read the Content of the Cell with the Content "101"

tia Synergi

HGH-Developers 13-Nov-12 10:21am

Hi,
I think your problem is the type of the content, the cell does not contains a string of "101" but a numerical value 101.

worksheet.Cells[1,1].Value is not null but with "as string" it becomes null because it is not a string.
you can use worksheet.Cells[1,1].Value.ToString() (if .Value is not null of course)

Pascal

Richard MacCutchan 13-Nov-12 11:21am

I have created cells with numerical values and they can be extracted without problems.

Richard MacCutchan 13-Nov-12 11:22am

Sorry but that site expects me to install some software on my PC in order to do the download. I am not going to do that. Try loading it somewhere else.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

HGH-Developers · Accepted Answer · 2012-11-13T04:27:00

Solution 1

You should use

C#

as string

only if you are sure of the type of Value.

To get a string from any kind of value in the cell, you can use the ToString() method.
For an Excel Cell, you may have a .Text property or something similar.

Pascal

Posted 13-Nov-12 4:27am

HGH-Developers

Richard MacCutchan · Accepted Answer · 2012-11-13T22:26:00

I have managed to download your spreadsheet file (no idea what happened yesterday), and it contains the sequence 101, 102, ... in cells [1, 5], [1, 7] etc. I am able to access each of these values successfully with the following short code sequence:

C#

Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
for (int i = 5; i < 20; i += 2)
{
    string cellValue = xlWorksheet.Cells[1, i].Value.ToString();
    Console.WriteLine("Cell[1, {0}]: {1}", i, foo);
}

which gives these results:

Cell[1, 5]: 101
Cell[1, 7]: 102
Cell[1, 9]: 103
Cell[1, 11]: 104
Cell[1, 13]: 105
Cell[1, 15]: 106
Cell[1, 17]: 107
Cell[1, 19]: 108

What I did notice is that cell[1, 5] contains the value 101, whereas the remaining cells contain strings. Thus you cannot get the first cell as string, but need to use the ToString() method to convert it.