Click here to Skip to main content
14,603,017 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi there,

i've got a big problem.

A sheet contains in one column the following values and formats:

5678 with format '00000' resulting in 05678 displayed
06789 with format 'Standard' resulting in 06789 displayed

Now, when i process the sheet for the first cell with

string cellContent = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExcelWorksheet.Cells[r,c]).Value);

I'm getting 5678

string cellContent = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExcelWorksheet.Cells[r,c]).Value2);

I'm getting 5678

string cellContent = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)ExcelWorksheet.Cells[r,c]).Text);

I'm getting 5678

In each case i'm getting the base values, not the displayed values.

My question is, how do i get the formatted (displayed) value from the cell?

Thanks

Ken

What I have tried:

Read and try a lot about it without success.
Posted
Updated 3-Jul-20 13:41pm
v2
Comments
Richard Deeming 6-Jul-20 10:09am
   
The Range.Text property[^] should return "the formatted text for the specified object", which sound like what you want.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Quote:
My question is, how do i get the formatted (displayed) value from the cell?

Well, it would appear you have some formatting being applied at a global/sheet level, or a column level, or individual cells - you seem to indicate this because of the
Quote:
with format '00000'
but Im unsure how the 'Standard' format is set up

Approach 1 would therefore be to be able to read and decode the format information from the spreadsheet and apply the same format to your C# string cellContent, whether that's a 'local' or a 'Standard' format setting/ This discussion *may* ymmv help you in this respect c# - How to know the formatting of Excel Cell - Stack Overflow[^]

Approach 2 would be "I cant get/interpret the formatting data from Excel", so I'll handle it locally in C#

Both approaches end the same way, in that (in the case you have shown) it's a

cellContent.PadLeft(5, '0')

It is often the case in software that a value - number, or date, is stored in way/value, but what we see is a 'formatting' directive being applied - dates are a classic, they are so rarely stored as a character set of (eg) "2020/07/04", as opposed to being stored in a numerical form which makes them easier to compare and calculate against
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100