Click here to Skip to main content
15,867,308 members
Articles / Desktop Programming / Windows Forms
Article

ResxWriter: Generating .resx files from an Excel spreadsheet

Rate me:
Please Sign up or sign in to vote.
4.94/5 (22 votes)
17 Nov 20062 min read 129.6K   3.5K   65   28
Generate .resx files from an Excel spreadsheet; fully customizable.

ResxWriter Screen Shot

Introduction

This is my first article so bear with me :) I want to share this with you because I think ResxWriter has some very nice functionality and some cool features. A break down:

  • Generates .resx files using ADO.NET and an Excel spreadsheet.
  • Fully customizable.
  • Settings are per machine user.
  • Clean uninstallation, with launcher.

ResxWriter is a .NET tool that can read Excel spreadsheets with data used for translation, and convert this data into separate .resx files.

Recently, I was working on an ASP.NET application that needed to have full translation for all static text. We used .NET resource files (.resx) and things were moving along just fine. Updating the text, however, was another story. Using Visual Studio to manually update the .resx files was cumbersome to say the least. Formatting was a pain if you used the IDE, although formatting the XML was an option…

Instead I decided to write an app that would take data from an Excel spreadsheet and generate the .resx files. This allowed our translation staff to use a program they are familiar with, as well as share the files for others to work on.

Reading the Excel File

Suppose you had the following data in a spreadsheet:

ControlNameEnglishFrench
BtnHelloHello World!Bonjour Monde!
LblHelloHello World Again!Bonjour Monde encore!

ResxWriter can extract the data from each column and create .resx files. Any number of columns can be used, and the application can be fully customized.

C#
public bool GetDataFromExcelFile(string filePath)
{
    try
    {
        string strConnectionString = 
            @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
            + filePath + @";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";
        OleDbConnection  dbConn = new OleDbConnection(strConnectionString);
        dbConn.Open();
        string fetch = BuildCommand();
        OleDbCommand  cmdSelect = new OleDbCommand(fetch, dbConn);
        OleDbDataAdapter dbAdapter = new OleDbDataAdapter();
        dbAdapter.SelectCommand  = cmdSelect;
        dbAdapter.Fill(this.m_table);
        dbConn.Close();
        dbConn = null;
        return true;
    }
    catch(System.Data.OleDb.OleDbException ex)
    {
        FrmMessage m = new FrmMessage();
        m.SetMessageText("ResxWriter - Error Message", 
            "Cannot Load Excel File!\n\n\nException:\n\n" 
            + ex.ToString());
        m.ShowDialog();
        return false;
    }
}

Writing the Resource Files

I created a class ClsLanguage to encapsulate the name-value functionality.

C#
[Serializable]
public class ClsLanguage 
{
    private string langColumnName; 
    private string langCulture;

    public ClsLanguage() {} 
    
    public ClsLanguage(string columnName, string culture) 
    { 
        this.langColumnName = columnName; 
        this.langCulture = culture; 
    } 

    [XmlElement(ElementName = "LanguageColumnName")]
    public string LanguageColumnName 
    {
        get {return this.langColumnName;} 
        set {this.langColumnName = value;} 
    } 

    [XmlElement(ElementName = "LanguageCulture")] 
    public string LanguageCulture 
    { 
        get {return this.langCulture;} 
        set {this.langCulture = value;} 
    }
}

A strongly-typed collection of ClsLanguage objects is written out as Resource Files.

C#
public void WriteResources(string filePath)
{
    if (filePath.Length > 0)
    {
        // get columns //
        foreach (ClsLanguage l in ClsMain.settings.LanguageCollection)
        {
            ResXResourceWriter rw = 
              new ResXResourceWriter(filePath + "." + 
              l.LanguageCulture + ".resx");

            for (int i=0; i<this.m_table.Rows.Count; i++)
            {
                ClsMain.frmMain.WriteProgressBar.Visible = true;
                ClsMain.frmMain.WriteProgressBar.Maximum = m_table.Rows.Count;

                try
                {
                  string name = 
                   m_table.Rows[i][ClsMain.settings.ControlColumnName].ToString();
                  string valu = m_table.Rows[i][l.LanguageColumnName].ToString();
                  rw.AddResource(name, valu);

                  ClsMain.frmMain.WriteProgressBar.Increment(i);
                }
                catch (System.ArgumentException)
                {
                }
            }

            rw.Generate();
            rw.Close();
        }

        ClsMain.frmMain.WriteProgressBar.Visible = false;

        FrmMessage m = new FrmMessage();
        m.SetMessageText("ResxWriter - Output Results",
            "Files were written to: " + filePath);
        m.ShowDialog();
    }
}

Points of Interest

Application settings (size, location and column names) were serialized through a class, ClsSettings.

C#
[Serializable]        
public class ClsSettings
{        
    private readonly string m_settingsPath = 
          Application.UserAppDataPath + @"\ResxWriter.xml";
    private ClsLanguageCollection m_langCollection 
          = new ClsLanguageCollection();
    private Size    m_size = new Size(250, 175);
    private Point    m_location = new Point(200, 200);
    private string    m_controlColumnName;
    private string    m_sheetName;

    public ClsSettings()
    {
    }

    public string SettingsPath
    {
        get {return this.m_settingsPath;}
    }

    public ClsLanguageCollection LanguageCollection
    {
        get {return this.m_langCollection;}
    }

    public Size FormSize
    {
        get {return this.m_size;}
        set {this.m_size = value;}
    }

    public Point FormLocation
    {
        get {return this.m_location;}
        set {this.m_location = value;}
    }

    public string ControlColumnName
    {
        get {return this.m_controlColumnName;}
        set {this.m_controlColumnName = value;}
    }

    public string SheetName
    {
        get {return this.m_sheetName;}
        set {this.m_sheetName = value;}
    }
}

This allowed me to save (and load) application settings with a few lines of code:

C#
XmlSerializer xs = new XmlSerializer(typeof(ClsSettings));
using (StreamWriter sw = new StreamWriter(xml, false))
{
    ClsMain.settings.FormSize = ClsMain.frmMain.Size;
    ClsMain.settings.FormLocation = ClsMain.frmMain.Location;
    xs.Serialize(sw, ClsMain.settings);
}

All settings in this app are specific to the current user, saved at /Documents and Settings/{User name}/Application Data/... This conforms closely to Microsoft's recommendations, and lets each user have their own experience and preferences.

I hate it when an application uninstalls but doesn't remove all files it was using. So, I made an app that cleans up for a true uninstallation. It essentially deletes all directories and files in its current location. Also, the only method (through Visual Studio) to create an uninstallation icon was to create a Uninstallation Launcher. In the source code, the launcher finds the product key used by the MSI file. This triggers the repair or remove screen, and allows uninstallation.

(These are both included as separate projects in the Solution file.)

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
United States United States
- philosophy
- french
- computer science

Comments and Discussions

 
QuestionError - How should the Excel sheet be formatted? Pin
Member 915689825-Jun-12 3:35
Member 915689825-Jun-12 3:35 
QuestionWould this work with .xlsx files? Pin
Member 915689822-Jun-12 5:29
Member 915689822-Jun-12 5:29 
AnswerRe: Would this work with .xlsx files? Pin
Patrick Bounaix22-Jun-12 5:58
Patrick Bounaix22-Jun-12 5:58 
GeneralGreat but has a problem! Pin
Xm3KHs4-Dec-08 4:27
Xm3KHs4-Dec-08 4:27 
GeneralRe: Great but has a problem! Pin
Xm3KHs4-Dec-08 5:00
Xm3KHs4-Dec-08 5:00 
GeneralRe: Great but has a problem! Pin
Xm3KHs4-Dec-08 5:25
Xm3KHs4-Dec-08 5:25 
GeneralDoes not build in VS 2008 Pin
uswebpro21-Aug-08 3:30
uswebpro21-Aug-08 3:30 
GeneralGreat work !!! Pin
Ashutosh Phoujdar3-Jun-08 0:52
Ashutosh Phoujdar3-Jun-08 0:52 
GeneralGreat tool Pin
marioprado15-Aug-07 4:13
marioprado15-Aug-07 4:13 
congratulations

Mario Prado - Brazil
GeneralHandy tool, suggestion Pin
marc_anic28-Jan-07 23:43
marc_anic28-Jan-07 23:43 
GeneralAn excellent idea Pin
Dave Midgley21-Nov-06 22:37
Dave Midgley21-Nov-06 22:37 
GeneralUseful tool Pin
ivankrsul16-Nov-06 13:09
ivankrsul16-Nov-06 13:09 
GeneralRe: Useful tool Pin
Patrick Bounaix18-Nov-06 10:57
Patrick Bounaix18-Nov-06 10:57 
GeneralJust what I needed :-) Pin
User 9148331-Sep-06 22:26
User 9148331-Sep-06 22:26 
GeneralRe: Just what I needed :-) Pin
Patrick Bounaix18-Nov-06 10:55
Patrick Bounaix18-Nov-06 10:55 
GeneralGenial!! Pin
Nieve Goor18-Jul-06 4:23
Nieve Goor18-Jul-06 4:23 
GeneralRe: Genial!! Pin
Patrick Bounaix18-Jul-06 10:02
Patrick Bounaix18-Jul-06 10:02 
GeneralRe: Genial!! Pin
Nieve Goor19-Jul-06 0:00
Nieve Goor19-Jul-06 0:00 
JokeYou saved my time! Pin
Paolo Gios7-Jun-06 4:37
Paolo Gios7-Jun-06 4:37 
GeneralRe: You saved my time! Pin
Patrick Bounaix19-Jun-06 16:35
Patrick Bounaix19-Jun-06 16:35 
GeneralExcel Cell Content Truncation Pin
ideadesigners6-Jul-05 13:24
ideadesigners6-Jul-05 13:24 
GeneralRe: Excel Cell Content Truncation Pin
ideadesigners6-Jul-05 22:01
ideadesigners6-Jul-05 22:01 
GeneralRe: Excel Cell Content Truncation Pin
Patrick Bounaix19-Jul-05 15:14
Patrick Bounaix19-Jul-05 15:14 
GeneralRe: Excel Cell Content Truncation Pin
napilut31-Jul-08 6:49
napilut31-Jul-08 6:49 
Generalvery handy - so far Pin
cliff hewett3-May-05 20:19
professionalcliff hewett3-May-05 20:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.