Page 1 of 1

Export to Excel regional issue

Posted: Fri Dec 26, 2014 11:31 am
by bryanl641
If our European office has their Windows regional settings set properly for their location the Export to Excel option just dumps the csv file into Excel as plain text, not split into columns. We have to override the Windows region settings to "List separator=," and also the decimal and thousands separators back to US style 1,234.56 instead of their preference of 1.234,56 for the export to excel to work properly.

I also found that adding a line at the top of the csv file with "sep=," lets Excel open the profound ui csv files properly.

Can the grid widget be updated to allow specification of the field separator? Or maybe provide a setting so that the "sep=," line is included in the downloaded csv file? Or is there a way to accommodate this already?

--Bryan

Re: Export to Excel regional issue

Posted: Fri Dec 26, 2014 1:50 pm
by Glenn
Bryan,

Until we can look into this in more detail, I may have found a workaround.

There is a setting available in Profound UI to change the separator used for the CSV export (see here: http://www.profoundlogic.com/docs/displ ... +Separator). After doing some research it appears that the European versions of Excel expect a semi-colon as the separator. Of course, you probably wouldn't want to change it for everyone...

You can change this property via JavaScript at any time prior to the user clicking the 'export' option. I wrote some quick JavaScript that uses a bound RPG field to decide which 'locale' to use and switches the separator appropriately (this code could be modified to retrieve the locale information from the browser instead, of course the browser might not have the same setting as their Windows config). I assigned this function to the onload property of the record format. When I change the RPG field between 'Europe' and 'US' I get the specified separator.

Code: Select all

function setCSVSep() {
	
	var regionValue = getElementValue("regionField");
	
	if (regionValue == 'Europe') {
		pui["csv separator"] = ";";
	} else {
		pui["csv separator"] = ",";
	}

}
I don't have an easy way to test this with a European configured PC so YMMV.

Glenn

Re: Export to Excel regional issue

Posted: Fri Dec 26, 2014 5:57 pm
by bryanl641
Thanks. I'll give this a try next week!

Re: Export to Excel regional issue

Posted: Mon Jan 05, 2015 5:23 pm
by bryanl641
That solution almost works, but for one initial character entity we always get when we use the Export to Excel link.

Every export begins with this: 
So when I add the ability to say the semi-colon is the CSV separator, we get &#65279 as the first column heading, causing a column mis-alignment. For our non-European PCs, we just have that character entity showing up *with* the actual column heading for column A.

Is there any way to prevent  from being included in the Export to Excel? I don't see it in the HTML when I inspect with FireBug or Chrome Developer tools.

--Bryan

Re: Export to Excel regional issue

Posted: Mon Jan 05, 2015 5:34 pm
by Scott Klement
I think what you're seeing here is the Byte Order Mark. We deliberately add this so that Windows will see the file as Unicode... programs like Notepad will otherwise tread the file as ASCII instead of UTF-8, which of course would cause us to lose tons of the international characters.

But, it shouldn't be coming back escaped as an HTML entity. After all, this is a CSV file not an HTML file! It should just be hex FEFF at the start of the file... a byte order mark.

Re: Export to Excel regional issue

Posted: Tue Jan 06, 2015 4:07 pm
by bryanl641
When I click on "Export to Excel" and save the file, then open it in notepad, the first line is this:
"BillTo","Customer name","CSR","Nbr Regions","Stores in Regions"

--Bryan

Re: Export to Excel regional issue

Posted: Tue Jan 06, 2015 4:56 pm
by Scott Klement
Hmm... I can't seem to get it to do that... I tried the current versions of Firefox, Chrome and Internet Explorer, all seem to just put out the hex value (not the HTML entity.)

Does this happen to you in a particular browser? Or all of them? Does it only happen if you set the csv separator?

Trying to figure out how I can reproduce it...

Re: Export to Excel regional issue

Posted: Fri Jan 09, 2015 8:19 pm
by bryanl641
For me I get the entity code on every browser, every client platform. That makes me think it's a server setting. I should try it from the profoundui http instance and then (assuming it doesn't have the same issue) look for settings that differ from our other instances. I'll let you know how it goes.

Thanks!
Bryan

Re: Export to Excel regional issue

Posted: Tue Jan 13, 2015 4:39 pm
by bryanl641
I found that an Apache config statement was missing from our instance config: DefaultNetCCSID 1208. After adding this, I no longer get the html entity when I export from Excel. Provided this doesn't cause any issues with any of our other applications I'll make the same change in our production instance.

Then it seems I can just add a checkbox to allow the user to toggle the separator, unless I can find a way to read the windows configuration.

[ ] Use semi-colon separator

Code: Select all

Onclick:
if (this.checked) {
      pui["csv separator"] = ";";
   } else {
      pui["csv separator"] = ",";
} 
--Bryan

Re: Export to Excel regional issue

Posted: Tue Jan 13, 2015 4:52 pm
by Glenn
I found a couple of links that might help. They show usage of navigator.language. Note that I believe these all return the setting from the browser, not the OS.

http://www.w3schools.com/js/js_window_navigator.asp
https://alicoding.com/detect-browser-la ... avascript/

Glenn