I have a .NET DataTable of data which I am sending to Excel using Cells.ImportDataTable(). This table contains a column of System.Boolean values. When the locale is set to en-US the export works as expected with TRUE/FALSE strings displaying in excel.
When the locale is something other than en-us, for example ja-JP, TRUE/FALSE strings still get exported. It is as if the Booleans are getting exported as a text string rather than a 0/1 flag which Excel would then convert to the Japanese symbols for true/false. This scenario holds true for all languages.
Is there a recommended way to export System.Boolean values to international clients such that they do not appear as English text?
We would prefer not to export columns of data one at a time as we have found that doing so causes a severe and unacceptable negative impact on performance.
Thank you,
Kevin
Hi Kevin,
We will check it and get back to you soon.
Thank you.
Hi Kevin,
If the cell value is boolean value, MS Excel will display it as TRUE/FALSE, it's MS Excel behavior. So, please change the boolean column type of the data table as numeric type before using Cells.ImportDataTable() method for your requirements.
Thank you.
I do not find that to be an acceptable answer. It appears to me to be a workaround that would not be very appealing to the clients using our product. You cannot expect non technical people to recognize 1's and 0's.
I want TRUE and FALSE to display in the user's language. Excel is smart enough to know what words/symbols represent true/false in each language (if that language pack is installed). If I have a Japanese colleague running Japanese Excel in Japanese Windows, there is no reason he should be seeing the English words TRUE/FALSE being exported to Excel.
My guess is that Aspose.Cells is using Boolean.ToString() (which ALWAYS uses the English words true and false regardless of culture settings) when it should be sending something similar to a bit field to Excel so that Excel can work its magic and display the culture aware strings for true and false.
Some data I have to backup this theory is that if you enter true and false into two cells in Excel, and save the workbook as an Excel 20003 XML spreadsheet you get the following output:
1
0
This shows that Excel recognizes what a Boolean is and is not storing the English true/false that I typed in.
Hi Kevin,
Thank you for providing us the details.
We will further look into your issue and get back to you soon.
Thank You & Best Regards,
Hi,
Well, we do store boolean record not string record to the file if the cell type is Boolean. Could you post your created file here? We will check whether the cell type is string or boolean.
Thank you.
Attached is a copy of a spreadsheet that Aspose.Cells produced containing true/false values. I converted this sheet to a XML file on my local computer and it does show the fields as booleans. Unless the XML conversion changed something, that means Aspose is exporting as boolean.
If you find the same thing, then can you give any recommendations as to why our international clients are seeing English booleans? I have tried two things:
1. Send a spreadsheet created using ASP.NET and the en-US culture, send to a Japanese colleague running Japanese Excel 2007/Windows. He opens the file and sees English booleans.
2. Have my Japanese colleage hit a Japanese version of our site, with Japanese culture settings in Internet Explorer, and the same Japanese Excel 2007/Windows. In this case, the booleans still show as English (screenshot attached).
Thank you for your help, Kevin.
Hi,
Thanks for providing us further details and template file.
We will look into your issue and get back to you soon.
Thank you.
Hi,
After checking your file, we do store boolean record in this file. Could you ask your Japanese colleague create a template file in Japanese Excel and post it here? We will check it soon.
Thanks for your help.
I have requested the a copy of the file form my colleague and will post it here tomorrow if he does not upload it himself.
One other thing I had him try was to remove the 'English' language from his copy of Excel entirely. Below are the screenshots from him removing 'English' and the result he is still seeing.
Thank you, Kevin
Based on the latest reply from my colleague in Japan, this is a non-issue. Here is what he had to say:
If I enter a word “真” as TRUE in Excel, Excel does not recognize it as Boolean type. When we use Boolean type in Japan, we have to write “TRUE”/“FALSE” in English as well as your country.
If Japanese users see words “TRUE”/“FALSE” on their spreadsheet, they do not mind it because they understand those words are Boolean type.
Perhaps Aspose can keep this in its records as a response for others with the same question.
Thank you, Kevin