EPiServer: Generated Language XML Files

I’m working on a new project at the moment and am aware that we’re probably going to have a fair few translations to do.  Our general approach at Interakting with regards to labels (i.e. static bits of text that are not content managed) is as follows:

1. Create an Excel spreadsheet once we know what all of the labels in our final site are going to be with a tab for each control and page type.  This is a manual process carried out by some poor person reading the XML files in the project.
2. Supply that spreadsheet to the customer/translation agency. 
3. The afore mentioned ‘poor person’ the enters information from the spreadsheet in to the XML file sin the project and essentially crosses their fingers a bit.

As you can no doubt tell – it’s pretty tedious.

For my new project, I wanted to automate this somewhat – and that is exactly what I did.  For this project, we created the spreadsheet at the beginning of the project and gave someone ownership of it.  Every time a new label is created it is entered in to this spreadsheet.  The spreadsheet has a very simple macro in it that take the contents of the worksheets and generates a language XML file tat can be used in EPiServer.

The advantage of this approach is that the spreadsheet be comes the ‘master’ (a project managers dream!) and can always be used to generate a copy of the language XML.  There are a couple of dependencies though:

1. This spreadsheet is going to go to the client, so you either have to lock it down or stress to them that they can’t go changing the format of the sheet, otherwise you will have a lot of work to do reformatting it.
2. The spreadsheet must be kept up to date, otherwise it is worthless or a very intensive job of updating it will need to occur.

I’m not VBA whiz, so please do excuse this sloppy cut of code – I have yet to tidy it up and make it more robust.  But this is the Macro code to do it (and this is the spreadsheet):

Dim xmlDoc As MSXML.DOMDocument

Const showAlerts = True

Sub CreateEPiServerLanguageXML()

    Dim sheet As Worksheet

    Set xmlDoc = New MSXML.DOMDocument

    Set xmlDoc.DocumentElement = xmlDoc.createElement("languages")

    For Each sheet In ThisWorkbook.Worksheets

        sheet.Activate

        Call ProcessWorksheet

    Next

    xmlDoc.Save Replace(ActiveWorkbook.FullName, "xlsm", "xml")

End Sub

'Processes the contents of the supplied worksheet in to an XML Element

Private Sub ProcessWorksheet()

    ' Identify populated column and row range

    Dim columnCount As Integer

    Dim rowCount As Integer

    columnCount = CountItems("A1:IV1")

    rowCount = CountItems("A1:A65569")

    Alert rowCount & " rows with " & columnCount & " columns"

    'Harvest the languages

    ProcessSheetLanguages columnCount, rowCount

End Sub

' Find the number of populated cells in the given range

Private Function CountItems(rangeValue As String) As Integer

    CountItems = WorksheetFunction.CountA(Range(rangeValue))

End Function

Private Sub ProcessSheetLanguages(numColumns As Integer, numRows As Integer)

    Dim index As Integer

    For index = 3 To numColumns

        CreateLanaguageElement ActiveSheet.Cells(1, index).value, numRows, index

    Next index

End Sub

' Creates the XML for a given language on the current sheet

Private Sub CreateLanaguageElement(language As String, rowCount As Integer, columnIndex As Integer)

    Dim languageElement As MSXML.IXMLDOMElement

    Dim groupElement As MSXML.IXMLDOMElement

    Set languageElement = xmlDoc.createElement("language")

    xmlDoc.DocumentElement.appendChild languageElement

    Call languageElement.setAttribute("id", language)

    Call languageElement.setAttribute("name", "")

    Dim sheetName As String

    sheetName = ActiveSheet.name

    Set groupElement = xmlDoc.createElement(sheetName)

    languageElement.appendChild groupElement

    'Create the individual nodes

    Dim itemIndex As Integer

    For itemIndex = 2 To rowCount

        Dim itemElement As MSXML.IXMLDOMNode

        Set itemElement = xmlDoc.createElement(ActiveSheet.Cells(itemIndex, 1).value)

        itemElement.Text = ActiveSheet.Cells(itemIndex, columnIndex).value

        groupElement.appendChild itemElement

    Next itemIndex

End Sub

' Display debug messages

Private Sub Alert(message As String)

    If showAlerts Then Call MsgBox(message, vbOKOnly)

End Sub

A couple of notes:

• It is worth noting that I am supplying an empty string to the name attribute of the language element.  Originally, I did not supply this.  Omitting it means that EPiServer will not only not load any of that language element, but it will also prevent EPiServer from processing the rest of the file.
• It’s good practice to put you project-specific labels in a separate file, this way, you’re not effected by any EPiServer upgrades that may replace the standard XML files