Re: Excel Cells are not readable if hidden - C# Discussion Boards

9-Aug-09 22:22

Hello all you C# and Excel programming people,

some days ago I discovered something that I have to tell...
I wrote a programm writing/reading to and from an MSSQL-db to and from Excel-sheets.
In one of my Excel-sheets I have to read the column "A" in order to get the right row to insert some values.
inside a ...
for(int row=0;...){ // I do like...
Excel.Range range = (Excel.Range)mySheet.Cells[row, "A"];
string val = range.Text.ToString();
...
}

The problem is: when one of the rows is hidden I do not get the row and for that reason I don't get a value.
When the whole column "A" is just not as wide as it has to be you can only see "####" and this is exactly what I get, what my string val looks like.
So the data in the Excel sheet is some number, but what I get is only what I see.

IF someone knows how to get rid of such a problem, I'd like to know.
Else I just wanted to mention.

Greets
Harry

no plan,
no signature

Luc Pattyn9-Aug-09 22:49

Luc Pattyn

9-Aug-09 22:49

Harry666 wrote:
string val = range.Text.ToString();

have you tried without the ToString() method?
When it is called Text, I suspect it already is a string...

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

Harry66610-Aug-09 4:48

10-Aug-09 4:48

Well, calling it Text doesn't make its type to string. It's just an object.
So this would simply not compile:
string val = range.Text;

Instead of using .ToString()
perhaps I could cast like this...
string val = (string)range.Text;

But there is also .Value (clearly spoken I've got no .Value but .Value2)
...let's try

no plan,
no signature

Moreno Airoldi10-Aug-09 0:53

Moreno Airoldi

10-Aug-09 0:53

You must use .Value, not .Text, in order to obtain the content of the cell. Smile | :)

2+2=5 for very large amounts of 2
(always loved that one hehe!)

Harry66610-Aug-09 5:34

10-Aug-09 5:34

Thanks,

I already thought and was about to try this, but I almost forgot it. Wink | ;)

Some points to mention:
My Excel.Range doesn't have a .Value but there is .Value2
... they probably do almost the same.

If there is no value in the cell (the range), Value2 equals null. So I have to test it...

if (range.Value2 == null)
  val = "";
else
  val = range.Value2.ToString();

Using range.Text.ToString()
I do not have to test against null, I just get an empty string.

Greets
Harry

no plan,
no signature

Moreno Airoldi10-Aug-09 7:05

Moreno Airoldi

10-Aug-09 7:05

Yep all correct. If I remember right, the fact it's Value2 instead of Value merely depends on how the interop assembly is built. But it's been a long time since I used that, so you may want to check, just to be sure. Smile | :)

Yep I know that Text doesn't require a null check, but it represents the exact text shown in the cell.
This means it will be, for example, "#####" for cells whose width is not enough to show the (numerical) content, etc.!
So, I doubt it will be of any use to you.

2+2=5 for very large amounts of 2
(always loved that one hehe!)

Harry66610-Aug-09 22:53

10-Aug-09 22:53

So be it.

Value2 gives me what I need. That's bought - I take it. (German expression translated literally $Shucks | :-\$ )
I simply check it and set it to "" if it equals null. (I don't mind these 2 lines of code).

I know nothing about the history of Value and Value2.
Perhaps it is because I use Microsoft.Interop.Excel Version12.0.0.0 or Microsoft.Office.Tools.Excel.v9.0

Well, I simply can forget Text and use Value2. So be it.
Thanks,
Harry

no plan,
no signature

Moreno Airoldi10-Aug-09 23:06

Moreno Airoldi

10-Aug-09 23:06

Wunderbar then! Poke tongue | ;-P

Enjoy!

2+2=5 for very large amounts of 2
(always loved that one hehe!)

Control Listner

satsumatable9-Aug-09 20:39

satsumatable

9-Aug-09 20:39

Hi,

I'm looking to build a component which has to listen current active form for any changes over any controls.

Let's say I've a form with 10 controls and those controls have to subscribe with my component to listen the activities.

Is there any pattern or components like this ?

Thanks

Re: Control Listner

Pete O'Hanlon10-Aug-09 1:42

Pete O'Hanlon

10-Aug-09 1:42

It sounds like you're thinking of the Observer pattern.

"WPF has many lovers. It's a veritable porn star!" - Josh Smith

As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.

My blog | My articles | MoXAML PowerToys | Onyx

Logic I use.......??

Hum Dum9-Aug-09 20:35

9-Aug-09 20:35

I created an offer-report application, C# and MS-Access.
Its multi user application. Not so big, so leave DB part and don't comment Smile | :)

What i want to achieve, is one record viewed by multiple Users.

A record created by a manager(higher marketing person), now he assigns some persons to look after it, and update it. presently he can assign 1 user, and when user log-in with his ID-pass, he can see offer assign to him(Not all).

Now, they want to assign multiple user for single offer.
if one is on leave, another can handle his task.
How to do this?

regards Smile | :)

PIEBALDconsult9-Aug-09 20:42

9-Aug-09 20:42

With a many-to-many relationship; the simplest way is to have a table that contains the IDs of the assignments and agents (or whatever):

Assignment
ID , Description ...
 
Agent
ID , Name ...
 
Offer
AssignmentID , AgentID ... (perhaps datetime of when it was assigned and by whom, etc.)

So then if Assignment 1 is offered to Agents 1 and 2, there will be two rows in Offers -- 1,1 and 1,2 .

Hum Dum9-Aug-09 21:01

9-Aug-09 21:01

I am not getting your idea !!

Doesn't this going to create redundancy in offer table, if for a single offer i create same rows with only change in AgentID?

At present this way i had design DB tables!!

Table - Offer(ID, PartyName, offerDate, etc....., AssignPerson,...)
Table - Users(Name , pass)

table offfer is main table holding all discription about OFFER.
Now at offer creating i simply insert Assign person name in Offer table,
and at login time checks login name and fetch only records

...from offer where AssignPerson = loginName

regards

PIEBALDconsult9-Aug-09 21:19

9-Aug-09 21:19

Hum Dum wrote:
create redundancy in offer table

No, no two records should be the same there.

Hum Dum wrote:
only change in AgentID?

Maybe, but perhaps also in any other fields you may have there, e.g. when assigned and by whom.

Hum Dum wrote:
Table - Users(Name , pass)

Your users should have IDs, then you use the ID, not the name. And are you storing plain text passwords?

Hum Dum9-Aug-09 21:59

9-Aug-09 21:59

PIEBALDconsult wrote:
No, no two records should be the same there.

But all fields are same also data, except AgentID ?

PIEBALDconsult wrote:
And are you storing plain text passwords?
Quote Selected Text

yes. is this incorrect?

Pete O'Hanlon9-Aug-09 22:24

Pete O'Hanlon

9-Aug-09 22:24

Hum Dum wrote:
And are you storing plain text passwords?
Quote Selected Text

yes. is this incorrect?

Yes. Anybody who can gain access to the database can see a users password - remember that this could be somebody who's managed to hack into your system.

"WPF has many lovers. It's a veritable porn star!" - Josh Smith

As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.

My blog | My articles | MoXAML PowerToys | Onyx

PIEBALDconsult10-Aug-09 5:59

10-Aug-09 5:59

Hum Dum wrote:
But all fields are same also data, except AgentID ?

No, and so what if it is? I suspect you're too new to database work to balk at such concepts.

User ( ID , Name , ... [ password , etc. ] )
1 , Alice , ...
2 , Bob , ...
3 , Carol , ...
4 , Ted , ...

Let's say Alice creates issue 1 and assigns it to Bob, you get:

(I'll use {} to indicate a datetime.)

Issue ( ID , CreatedByID , CreatedWhen , Description , ... )
1 , 1 , {} , "Normalize the database" , ...
2 , 1 , {} , "Document the code" , ...
3 , 1 , {} , "Peer review Ted's code" , ...

Then she assigns them to Bob:

Assignment ( IssueID , AssignedToID , AssignedByID , AssignedWhen , ... )
1 , 2 , 1 , {} , ...
2 , 2 , 1 , {} , ...
3 , 2 , 1 , {} , ...

Later, Carol may assign issues 1 and 2 to Ted:

Assignment ( IssueID , AssignedToID , AssignedByID , AssignedWhen , ... )
1 , 2 , 1 , {} , ...
2 , 2 , 1 , {} , ...
3 , 2 , 1 , {} , ...
1 , 4 , 3 , {} , ...
2 , 4 , 3 , {} , ...

So, you can see that no two rows are the same and can be quite different, especially as time goes on.

You'll need to learn about table JOINs.

Hum Dum wrote:
yes. is this incorrect?

Yes, at least store a hash of the password instead of the actual password:
0) When someone sets a password, store the hash. (I use SHA-1[^])
1) When someone logs in; take the provided password, hash it, and compare the new hash to the stored hash.

Hum Dum11-Aug-09 20:35

11-Aug-09 20:35

thanks for your needful reply.

cheers Smile | :)

PIEBALDconsult12-Aug-09 4:09

12-Aug-09 4:09

Glad to be of service.

Comboboxes in DataGrid

AmbitiousBeginner9-Aug-09 20:31

AmbitiousBeginner

9-Aug-09 20:31

hi;

i wonder if there's a designer support for inserting ComboBoxes into a DataGrid in windows applications, rather than writing codes for it since the samples are too complicated. can anyone help?

--- Thanks

PIEBALDconsult9-Aug-09 20:46

9-Aug-09 20:46

0) Why are you using a DataGrid(View) ?
1) Are you trying to allow editing in one?

See this[^] recent thread.

AmbitiousBeginner9-Aug-09 20:52

AmbitiousBeginner

9-Aug-09 20:52

yes, i need to present an sql table in a grid and allow the user to change its values if needed. one column of the grid should have restricted values, so i thought it might be a good idea to insert a combo for it and i found several samples which were too complicated and time-consuming. if there's other ways of restricting values for a DataGridColumn, i'll be glad to learn.

--- Thanks

PIEBALDconsult9-Aug-09 21:23

9-Aug-09 21:23

AmbitiousBeginner wrote:
i need to present an sql table in a grid

Who says? That's a very poor way of presenting information to a user.