XML number formatting in international locales

%3CLINGO-SUB%20id%3D%22lingo-sub-1732428%22%20slang%3D%22en-US%22%3EXML%20number%20formatting%20in%20international%20locales%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1732428%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20program%20I'm%20running%20to%20generate%20an%20XML%20file.%20When%20creating%20this%20XML%20file%2C%20there%20are%20cells%20which%20contain%20numeric%20data.%20Sometimes%20this%20data%20will%20be%20formatted%20for%20an%20English%20locale%2C%20meaning%20traditional%20formatting%20is%20used%20(decimal%20separators%20are%20periods%20and%20thousands%20separators%20are%20commas).%20However%2C%20there%20are%20also%20times%20when%20these%20numeric%20values%20may%20be%20formatted%20for%20other%20locales%2C%20such%20as%20dutch%20locales.%20This%20means%20decimal%20separators%20are%20commas%20and%20thousand%20separators%20are%20periods.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3ERight%20now%2C%20I'm%20saving%20the%20numeric%20values%20in%20XML%20with%20these%20separators%20per%20locale.%20For%20example%2C%20my%20file%20would%20show%2045.66%20in%20an%20English%20locale%20and%2045%2C66%20in%20a%20Dutch%20locale.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELikewise%2C%20when%20I%20open%20this%20in%20Excel%2C%20I%20would%20expect%20my%20locale%20formatting%20to%20be%20respected%2C%20such%20that%20if%20opening%20in%20an%20English%20locale%20I%20would%20see%2045.66%20and%20when%20opening%20in%20a%20Dutch%20locale%2C%20I%20would%20see%2045%2C66.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EHowever%2C%20it%20always%20appears%20to%20break%20when%20trying%20to%20view%20in%20terms%20of%20the%20Dutch%20locale%20--%20this%2045%2C66%20instead%20shows%20as%204566%20in%20Excel%2C%20even%20though%20the%20XML%20shows%20as%2045%2C66.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EAre%20there%20settings%20within%20Excel%20that%20are%20needed%20to%20ensure%20this%20opens%20appropriately%3F%20Or%20does%20the%20XML%20version%20always%20need%20to%20be%20locale%20neutral%3F%20Or%20is%20there%20something%20else%20I'm%20missing.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20attached%20files%20showing%20the%20XML%20view%20along%20with%20the%20Excel%20workbook%20view%20for%20some%20examples.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1732428%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1737107%22%20slang%3D%22en-US%22%3ERe%3A%20XML%20number%20formatting%20in%20international%20locales%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1737107%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F815602%22%20target%3D%22_blank%22%3E%40kbolande%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20do%20you%20open%20XML%20in%20Excel%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a program I'm running to generate an XML file. When creating this XML file, there are cells which contain numeric data. Sometimes this data will be formatted for an English locale, meaning traditional formatting is used (decimal separators are periods and thousands separators are commas). However, there are also times when these numeric values may be formatted for other locales, such as dutch locales. This means decimal separators are commas and thousand separators are periods. 

Right now, I'm saving the numeric values in XML with these separators per locale. For example, my file would show 45.66 in an English locale and 45,66 in a Dutch locale. 

 

Likewise, when I open this in Excel, I would expect my locale formatting to be respected, such that if opening in an English locale I would see 45.66 and when opening in a Dutch locale, I would see 45,66. 

However, it always appears to break when trying to view in terms of the Dutch locale -- this 45,66 instead shows as 4566 in Excel, even though the XML shows as 45,66. 

Are there settings within Excel that are needed to ensure this opens appropriately? Or does the XML version always need to be locale neutral? Or is there something else I'm missing. 

I attached files showing the XML view along with the Excel workbook view for some examples. 

2 Replies

@kbolande 

How do you open XML in Excel?

@Sergei Baklan I just right click on the file and chose to "open with" and then choose Excel.