Globalize/Localize Excel Reports Using Spreadsheet

Question

My company has new customers in Brazil and we realized that our excel reports are not working when our Brazilian customers tried to open the reports in their Brazilian versions of excel.

For excel output we use spreadsheet gear in our vb.net web application. Our excel worksheets are fairly simple. Mostly outputted text/numbers/dates, a couple of formulas (sum, if) and formatting on the currency and dates.

I've tried several methods to get my excel reports to work:

First I left the excel workbook in the "en-US" culture and tried simply chaging the number format for Brazil to:

_-[$R$-416] * #.##0,00_-;-[$R$-416] * #.##0,00_-;_-[$R$-416] * "-"??_-;_-@_-

And this formatted the regular cells but the formulas still failed to show a value. Instead they showed a 0 value.

Next I tried changing the workbook to the "pt-BR" culture and that also forced me to translate the formula names (Sum -> Soma, If -> Se) but they still wouldn't should a value and instead showed a #Name/#Nome error. Interestingly enough the formulas would work if I edited the cell and hit enter. The formula wouldn't change but it would some how fix that cell.

I need to be able to out excel reports that can format dates/currencies and apply simple formulas (IF, Sum) for other excel cultures. Anyone have any advice?

Answer 1

Not sure if it works in Excel as I've not used it for a long time now but I had exactly the same problem in OpenOffice Calc Basic (using pt-BR too). It was giving me an error but worked after pressing enter in the cell. I found there is a different method to set an english formula and a localized one. For example when you write a formula with english keywords to a cell you use: cell.Formula = '=IF(cond....)'

If you use the localized keyword: cell.FomulaLocal = '=SE(cond....)'

Both methods work on the pt_BR version but the keyword has to pair with the method name.

Maybe Excel has a similar trick? Did you try the english keywords on the pt_BR version?