designer.SetDataSource not taking into account the regional settings

Aspose.Cells Product Family
1
I have a cell formatted as a Number with 4 decimal places.
I fill this cell using the designer, like this:
designer.SetDataSource("ExchangeRate", (decimal)exchangeRate);
Now, when I set the regional settings of my pc to English-US, it works fine. The decimal separator is "."
However, when I set the regional settings to Dutch, the decimal separator is "," and all my calculations fail because the value set into the cell has a "." as a separator.
I'm sure more people have come across this issue. Is there any way to work around this?
Thanks,
Jeffry
2

Hi Jeffry,

Could you please post a sample project to show your problem? I use the following code with attached designer file and all work fine whether the decimal point is "." or ",".

WorkbookDesigner wb = new WorkbookDesigner();
wb.Open("d:\\test\\book1.xls");
double exchangeRate = 1.23;
wb.SetDataSource("ExchangeRate", (decimal)exchangeRate);
wb.Process();
wb.Save("d:\\test\\abc.xls");

3
Hi Laurence,
I found out what the problem was. However, I'm not sure about the solution yet.
I've linked a VBA TextBox to the cell with the Exchange Rate, so whenever I change a value in the cell or in the textbox, it updates the other one. I did this because I want to call a VBA method when the textbox loses focus to refresh a pivot table.
I'm not sure how to trigger a VBA method when I step out of an Excel cell. If that is possible, it already solves my problem, because then I can get rid of the VBA textbox.
Back to the textbox. It is linked to the exchange rate cell, but when the designer fills the exchange rate cell, the linked textbox is not updated, so it still shows &=$ExchangeRate.
To workaround this I made a method Workbook_Open to fill the textbox based on the exchange rate field:
Sheets("Quote").txtExchangeRate.Value = Sheets("Quote").Range("C15").Value
This is where it goes wrong. Even though the value from C15 is e.g. 1,27 (comma), the textbox is updated with 1.27 (dot). And because the cell and the textbox are linked, the cell is also updated to 1.27 (dot)!
So, I see a few possible solutions:
1. Get rid of the textbox, but then I need to trigger a VBA method when I leave an Excel cell (don't know how to do that yet)
2. Get the designer to fill in the textbox with the same value as the cell. Now, even though they are linked, the textbox is not updated.
3. Change the Workbook_Open method so that the correct value is copied from the cell to the textbox.
Any ideas? :) Personally I prefer option 1.
Thanks,
Jeffry
4
Ok, I found it. I catched the WorkSheet_Change event, checked if it was triggered by cell C15 and if so, call the method to update the pivot table. I knew it had to be possible.
Thanks for listening. Sometimes you just need someone to tell your story to and then the solution comes naturally :).
5

I am not familiar with VBA so I don’t have any idea for this issue. Could you post your designer file here? Then I can check if I can find a solution for your problem.

6

Great you have a solution.