Getting formatted cell value

Aspose.Cells Product Family
1

Is there a way to get the formatted value of a cell? For example, if the cell contains 0.5 and the custom format is Currency, I want to get “$0.50”. Basically I want to be able to retrieve what is visible on the worksheet when it is being viewed in Excel.

Is this possible? I tried Cell.StringValue but that doesn’t work.

2

Hi Mike,

Thank you for considering Aspose.

Well, I checked your issue with the following simple code and cell.StringValue returns your desired result i.e.$0.50. Please provide us your sample code and template file (if any) to show the issue and we will check it soon.

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

Cells cells = worksheet.Cells;

cells["A1"].PutValue(0.5);

cells["A1"].Style.Custom = "$0.00";

MessageBox.Show(cells["A1"].StringValue);

workbook.Save("c:\\out.xls");

Thank You & Best Regards,

3

I will be able to get back to you with a sample xls later today, but what I can tell you now is the problem happens with excel-created workbook. So just use excel 2003 to create this workbook and set the custom number formatting (instead of doing it using Aspose.Cells).

When I do this, the StringValue does not handle this correctly.

4

Hi,

Thank you for considering Aspose.

I tested by using the attached template file and the below sample code and it works fine.

Workbook workbook = new Workbook();
workbook.Open("C:\\res.xls");
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
MessageBox.Show(cells["A1"].StringValue);

We will wait for your template file for further testing of the issue.

Thank You & Best Regards,

5

I have attached the workbook I am using. When I do this:

_workbook.Worksheets[0].Cells[0,0].StringValue

I get "0.2". I also need this to work on datetime custom formats and it is always returning the numeric datetime value.

6

Hi Mike,

Thank you for considering Aspose.

Well, I tested your template file with the attached latest version and it works fine too. Please try the attached latest version and let us know if it works fine for you.

Thank You & Best Regards,

7

Strange, it is not working here even with 4.8.1.9. Here is my code:

Workbook w = new Workbook();
w.LoadData(@"C:\Excel2003.xls");
var a = w.Worksheets[0].Cells[0, 0].StringValue;

The "a" string contains "0.2", not $0.20 like it should.

8

Hi,

Please use Workbook.Open() method instead as using Workbook.LoadData() method would discard all types of formatting and other details regarding workbook.

Change your code to:

Workbook w = new Workbook();
w.Open(@“C:\Excel2003.xls”);
var a = w.Worksheets[0].Cells[0, 0].StringValue;



Thank you.

9

That's it. I should be using Open instead of LoadData. Damn...

That was entirely my fault but to prevent this it would be nice to see this effect (removing formatting) as a remark in the XML comment of the LoadData method.

Thanks again,

Mike

10

Hi,

We will fix the issue of Workbook.LoadData method soon.

Thank you.

11

Hi,

Please try the attached version, we have fixed the issue.

Thank you.

12

The issues you have found earlier (filed as 12747) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
13

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan

14
15