SUBJECT…
Today I’m going to create a windows form app to write items to application resource (.resx) file using data in a excel file.
REASON…
The reason I decided to develop this kind of app is, I’m working on a Xamarin project and it supports for English, French and Dutch. So I had to add translations for both two languages for English.
In Visual Studio we can Add Recourse key and value attributes manually using resx file. I’ll show you how we can do it manually.
Image 1 : Add attributes manually to Resource file
What I had to do is copy Name and keys from above excel file and paste in to Name field and Key field one by one.
But it is not easy when there are thousands of Names and translations (keys). Because it’ll waste time.
MY SOLUTION…
So I decided to develop a small app to write resource attributes using a excel file to resx files.
I have two resource files for French and Dutch and you can see them under project resource folder in below image.
Image 2 : Application design and class structure
Then I created a small windows form with two buttons for French and Dutch and a List view.
Now I’ll describe the coding part for above functionality.
First I have added resource file paths as attributes to App.config file.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<appSettings>
<add key="resPathFr" value="F:\My softwares\Excel to RESX\TranslationMobile\Translation\Properties\AppResources.fr.resx"/>
<add key="resPathNl" value="F:\My softwares\Excel to RESX\TranslationMobile\Translation\Properties\AppResources.nl.resx"/>
</appSettings>
</configuration>
Then Add the Excel file “Book1.xlsx” to Startup path “//bin”.
Here is the format of the excel file I’m going to use..
Image 3 : The Excel file which contains Translations
According to this excel file, Keys are in “ID” column which is column “A”. And English definitions are in column “B”, French/FR definitions are in column “D” and Dutch/NL definitions are in column “F”.
And These keys and definitions are stored in “Sheet1”.
Then I will add useful namespaces that we have to use in Form1.cs class additionally to default namespaces .
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using System.Resources;
using System.Configuration;
We have to Add Office.Interop.Excel namespace from references like below image
Image 4 : import Excel resource from references
Then I’ll add another class called “ResourcesFr” with two property to store Key, Value collection. But you can use a Dictionary<string,string> instead of using a Generic collection to store Keys and Values. Because we are using only two properties to store data.
public class ResourcesFr
{
public string Key { get; set; }
public string Value { get; set; }
}
Then we can start coding in Form1.
I’ll add five properties in Form one for Excel Application,Workbook, Sheet, Key/value list and another one for missing values.
namespace Translation
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
List<ResourcesFr> keyList = new List<ResourcesFr>();
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
}
}
Then Double Click the “Dutch button” in the Form1 and it’ll create a Button_Click event.
private void btnDutch_Click(object sender, EventArgs e)
{
}
In this event we can add code for read the excel file and populate key,value information to the keyList property that we declared in the top of the Form1 class.
///<summary>
/// Add Dutch Translations
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnDutch_Click(object sender, EventArgs e)
{
xlApp = new Excel.Application();
// path of the Excel file
string path = (Application.StartupPath + "\\Book1.xlsx");
// Get Excel file to xlWorkBook property
xlWorkBook = xlApp.Workbooks.Open(path);
// Get "Sheet1" as xlWorkSheet : you can see it in excel sheet image
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// In the excel sheet we have 322 rows, So I added go through the loop untill 322
for (int i = 1; i < 322; i++)
{
ResourcesFr fr = new ResourcesFr();
string s = "";
string s2 = "";
s = string.Format("A"); // The Key(ID) is in Column A
s2 = string.Format("F"); // The Value(Text (NL)) is in Column F
if (xlWorkSheet.Cells[i, s].Value != null)
{
// Get Key
fr.Key = xlWorkSheet.Cells[i, s].Value.ToString();
//Get Value
fr.Value = (xlWorkSheet.Cells[i, s2].Value != null) ? xlWorkSheet.Cells[i, s2].Value.ToString() : "";
// Add Key and Value to List<ResourcesFr> keyList
keyList.Add(fr);
}
}
// CLose the Excel workbook
xlWorkBook.Close(true, misValue, misValue);
// Exit form Excel
xlApp.Quit();
// Clean the memory
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
AddMemoLine("Dutch");
int c = 0;
foreach (var f in keyList)
{
// Insert or Update values
AddOrUpdateResource(f.Key, f.Value, "NL");
c++;
// write in List view
AddMemoLine(string.Format("Key : {0}, Value : {1}", f.Key, f.Value));
}
MessageBox.Show("Dutch resx properties has updated");
}
ReleaseObject() Method does clean the Garbage collection and get the unused memory.
private void ReleaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
AddOrUpdate() Method does Insert new record to resource file if it is not existing or update the existing record if it is existing.
///
<summary>
/// update resource file
/// </summary>
/// <param name="key"></param>
/// <param name="value"></param>
/// <param name="lang"></param>
public void AddOrUpdateResource(string key, string value, string lang)
{
try
{
// Get Resource File Path
string resPath = string.Empty;
if (lang == "FR")
{
resPath = ConfigurationManager.AppSettings["resPathFr"];
}
else if (lang == "NL")
{
resPath = ConfigurationManager.AppSettings["resPathNl"];
}
else { }
if (!string.IsNullOrWhiteSpace(resPath))
{
var resx = new List<DictionaryEntry>();
using (var reader = new ResXResourceReader(resPath))
{
resx = reader.Cast<DictionaryEntry>().ToList();
// Check if the key is existing
var existingResource = resx.Where(r => r.Key.ToString() == key).FirstOrDefault();
// Insert new Record if the key is not existing
if (existingResource.Key == null && existingResource.Value == null) // NEW!
{
resx.Add(new DictionaryEntry() { Key = key, Value = value });
}
else // MODIFIED RESOURCE!
{
var modifiedResx = new DictionaryEntry() { Key = existingResource.Key, Value = value };
resx.Remove(existingResource); // REMOVING RESOURCE!
resx.Add(modifiedResx); // AND THEN ADDING RESOURCE!
}
}
// Write resources to resx file
using (var writer = new ResXResourceWriter(resPath))
{
resx.ForEach(r =>
{
// Again Adding all resource to generate with final items
writer.AddResource(r.Key.ToString(), r.Value.ToString());
});
writer.Generate();
}
}
}
catch (Exception ex)
{
AddMemoLine(string.Format("Key : {0}, Value : {1}, Lang{2} Throws an error {3} ", key,value, lang,ex.Message));
}
}
And MemoLine() Method does insert an item to list view.. Then user can see what is happening now..
///
<summary>
/// Insert a record to List view..
/// </summary>
/// <param name="data"></param>
private void AddMemoLine(string data)
{
txtResultList.AppendText(data);
txtResultList.AppendText(Environment.NewLine);
}
Then run the program and Click the “Dutch” button and you will see update/Inserted records in few seconds.
Image 5 : Adding Attributes to resource file
Then close the program and Open the resource file Resource.nl.resx and you will see all records in the excel sheet has transferred to the resx file.
Added attributes to Resources.nl.resx
You can do the Same thing to “French button”. And You can download the full project using below link.
Download the project file
Thanks !
Buddhima Kudagama
kgbuddhima@gmail.com
Like this:
Like Loading...
