So I’m working on an enterprise level Mobile Application using Xamarin Forms, which is also a multilingual supported app, and there was this one time my boss asked me to prepare an excel document with all the language strings we are using in the app, along with their available translations.
Now I knew I would be able to easily do this in my Xamarin Forms project, but how about writing the extracted resource strings into an excel file ? and hell no I didn’t want to complicate and mess our project code. So yeah, I had to execute this outside the the project code we had implemented.
Simply put I had to read the content of all the language resource files and write their content accordingly, aligning with their available translations, into an Excel document.
To be more exact, we had three language files for, English, French, and Dutch whereas whenever we add a new Language String in English we had to convert it into the other two languages and add to those two files respectively. But sometimes we miss a one or two translations during development, so to check whether all the strings are translated we needed check the existence of all the strings and their translations in all three languages using an MS Excel document.
So here is how I did it… 🙂
I created a simple WPF based C# project and copied all three language files into it (to the project’s root folder). You could even create a Command Prompt based C# solution also, its totally up to you as long as it’s a desktop-supported dot net solution.
Also keep in mind, I’ll be writing this article based on the three language file I had to deal with, but if you have any more number of language resource file, don’t be afraid to follow the same procedure and make the necessary code modifications accordingly. 🙂
Make sure to copy only the “.resx” extension files, as that’s all we need to retrieve the available language strings. And ignore the “.Designer.cs” that comes attached with it in dot net projects.
Once that’s done, next make sure you have set their Build Action to “Embedded Resource“.
Then we have to add necessary reference for accessing Microsoft Excel functionalities, and for that go to Add Reference -> COM -> Type Libraries -> type in “Microsoft Excel” !
There you should get a search result with “Microsoft Excel XX.XX Object Library” the XX.XX could change according to the Microsoft Excel version you have installed in your PC. Select that and click “OK” to import the reference.
Alright, now that we are done setting up let’s dive into coding…
Reading content from Language Resource Files
Below code will be reading the content from the embedded Languages resource files…
You are passing in the type of language you need to read from and the assembly reference of the current program instance, which is to access the embedded resource files. While reading from the resource file, it will create a Key Value Dictionary array list and add the keys and values of the resource file into this array.
We could use this above method for reading any king of a language resource file and retrieve the values to a Key-Value pair Array list.
Writing the Results to MS Excel file
This below code will write whatever the results you have retrieved from the Resource files into an excel file accordingly aligning with the string keys and their available translation values.
There as you can see, we are passing in the VALUES_EN_LIST Dictionary list, which has the full collection of Language Strings which will be use to cross check the availability of translations for all the given string keys. This is very cruicial, because this parameter will be used to cross check the availability of the translations in the other resource Dictionary lists. You can see I have two more parameters VALUES_FR_LIST and VALUES_NL_LIST these are the other resource Dictionary lists which I need to cross check with the VALUES_EN_LIST in order to write to Excel file aligning with the string keys.
You can see above that I have created an Excel.Workbook and Excel.Worksheet to be incorporated with our writing to an Excel file. Inside the loop we are adding the String Keys and String Values, column by column and row by row. There you can see we are also calling a method CheckAndAddExistingTranslationForKeyValue() which will be given next, where as this method cross-check for the existence of translations for a given String Key and adds the values to the Excel worksheet that we are passing the reference to.
Also there you can see I have added columns as 1,2,3… so on, which is to move from one column to another, accordingly to the amount of translation resources we have.
Then we are increasing the value of rowNo to move on to the next Row in the next iteration of the loop.
And at the end it calls the SaveAs() method and write the file to the disk according to the given Path (make sure you give the accurate path along with a valid file name), and finally release the MS Excel objects.
This is the method that I have used inside the above code’s loop, which cross-checks for the available translation values for a given key and write the found value to the worksheet accordingly to the given column.
Finally let’s put it together…
Alright now let’s put those above methods to use.
As you can see, I have defined a three Dictionary arrays for the three language Resource files I have and then inside the ExecuteReadingAndWriting() method I’m calling the ReadEmbededLanguageFile() method to read the content from each resource file according to their culture suffix (“en” – English, “nl” – Dutch and so on).
You can define any number of Dictionary arrays for the number of Language Resource files you have, but make sure to use a sensible naming conversion for them. (VALUES_EN_LIST – for Dictionary array which holds English strings)
Then finally I’m calling the WriteToExcelFile() method to write the read values to an Excel file.
Alright let me give and overall-put-together view of the whole code… 😀
Now that’s how our whole code should look like when put together along with all the above methods I have given you. So go ahead and just call the ExecuteReadingAndWriting() method to fire it up. 😉
If you had programmed it exactly I have shown here, it should work out without any issue. 😉 Once the execution is complete, go ahead and open the Excel Document, it should be show as below, first the String resource Key, String value in English, String value in French (if available) and so on based on the number of language resources you added. Well I haven’t added any special formattings to the excel document sheet in the above code, but you can do it pretty easily by playing around with the code. 😉
Well there it goes folks, hope this post was helpful for you and prolly saved you a lot of time… 🙂 Please share this among other developers and you may save another developer’s time as well… 😉
Cheers ! Stay Awesome ^_^ !