Foreign currency

Aspose.Cells Product Family
1

I’m trying to use the html loadoptions for creating a workbook.


My html has currency values in the format #.###,## when the browser language setting is set to Spanish (Latin America).

However, after I create the workbook and save it to a file, the values aren’t recognized as currencies nor can I use the values in a SUM formula.

I guess I’m needing to know how to set the style for these values so they’re recognized as currencies but with a different symbol and format other than “$” and #,###.##. Also, I would like my =SUM formulas to work and show the correct symbol.

Thanks.


2

Hi Steve,

Thanks for your posting and using Aspose.Cells.

Please download and try the latest version: Aspose.Cells
for .NET v8.0.0.3 and see if it fixes your issue.

If your issue still persists, then please provide us your source html and sample code. We will look into it and update you asap.

3

Hi, please see the attached file.


I would like for the 1st row to behave like the 2nd row and show the total for the numbers that are displayed in Spanish format. My html data could also have other rows with different currencies and I would want those formulas to work as well.

Also, I noticed even after calling calculateformula on the workbook, the formulas aren’t executing until I click in the cell and hit enter.
4

Hi,

Thanks for your posting and using Aspose.Cells.

If you open your html file directly in MS-Excel, it will also not calculate the formula. Because your currency values are detected as General not as Numbers. So, I think, this issue cannot be fixed.

However, I have logged this issue in our database for further investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-42562.

I have attached the output file and screenshot for a reference.

C#
private string GetHtmlText()
{
return @"


<table style="“width: 100%”">

1.500,50 $
1.500,50 $
1.500,50 $
1.500,50 $
1.500,50 $
=SUM($A$1:$E$1)
Spanish Data and format


$ 1
$ 1
$ 1
$ 1
$ 1
=SUM($A$2:$E$2)
US Data



";
}

private void button1_Click(object sender, System.EventArgs e)
{
string html = GetHtmlText();

byte[] data = Encoding.UTF8.GetBytes(html);
var stream = new MemoryStream(data);

var loadOptions = new LoadOptions(LoadFormat.Html);

var wb = new Workbook(stream, loadOptions);
wb.Worksheets[0].AutoFitRows();
wb.Worksheets[0].AutoFitColumns();
// wb.CalculateFormula();
wb.Save(@“myasposetest.xlsx”, FileFormatType.Xlsx);
}

5

Hi Steve,

Thanks for using Aspose.Cells.

For numeric value, you can set the correct HtmlLoadOptions.Region to recognize the correct separators such as for your case ‘.’ is group separator and ‘,’ is decimal separator.

For currency symbol, because it is at the tail of the value, only ‘€’ can be recognized. If value in your given html file is like:



1.500,50 €



then with HtmlLoadOptions.Region = CountryCode.Germany user can get the correct double vlaue 1500.5 for this cell.

C#
var loadOptions = new HTMLLoadOptions();
loadOptions.Region = CountryCode.Germany;

var wb = new Workbook(stream, loadOptions);
6

Hi,

Thanks for using Aspose.Cells.

Please download and try this fix: Aspose.Cells
for .NET v8.0.1.1 and let us know your feedback.

7

Can you paste in the entire code you’re using for this to work? I’m getting the following error when I try setting the region.


loadOptions.Region = CountryCode.Germany;

Object reference not set to an instance of an object.

If I comment out that line, I don’t get the error.
8

Hi Steve,

Thanks for your posting and using Aspose.Cells.

Please use the following code. I have attached the output xlsx file generated by it for your reference.

C#


string html = @"





















1.500,50 € 1.500,50 € 1.500,50 € 1.500,50 € 1.500,50 € =SUM($A$1:$E$1) Spanish Data and format
$ 1 $ 1 $ 1 $ 1 $ 1 =SUM($A$2:$E$2) US Data


";

byte[] data = Encoding.UTF8.GetBytes(html);
var stream = new MemoryStream(data);

var loadOptions = new HTMLLoadOptions();
loadOptions.Region = CountryCode.Germany;

var wb = new Workbook(stream, loadOptions);
wb.Worksheets[0].AutoFitRows();
wb.Worksheets[0].AutoFitColumns();
//wb.CalculateFormula();
wb.Save(@"myasposetest.xlsx", SaveFormat.Xlsx);

9

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.