Click here to Skip to main content
14,982,840 members
Please Sign up or sign in to vote.
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?



What I have tried:

Read and try a lot about it without success.
Updated 3-Jul-20 13:41pm
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

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
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, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900