C# get formatted excel cell value as displayed

Question

0.00/5 (No votes)

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 3-Jul-20 3:48am

Ken Guru

Updated 3-Jul-20 13:41pm

Garth J Lancaster

v2

Add a Solution

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

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)

Garth J Lancaster · Answer 1 · 2020-07-03T13:39:00

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