Geekly Articles each Day
📜 ⬆️ ⬇️

Automatic creation of Android and iOS localization files from Excel spreadsheet

image


Hello, my name is Alexander and I am an Android application developer. Once I got on a project in which there were 11 interface languages ​​and over 600 lines. There were no programmers on the customer side, so they stored the whole thing in an Excel spreadsheet. When they changed something in it, then they sent this table to us with the words "We highlighted cells with changes in yellow, update Android and iOS applications accordingly." After that, two developers lost a couple of hours, making changes manually. And then it turned out that someone somewhere forgot something, was mistaken or did not finish, discrepancies appeared between the platforms, the customer was nervous, the developers were furious. I was not satisfied with such a situation, I began to look for ways to automate the unloading of rows from Excel. The result was a wonderful VBScript code, which we still enjoy using. Now I will submit this script to you. Under the cut a number of images and script code.


So, first take a look at the table itself and estimate the scale of the problem:


image


Here she is beautiful! As we can see, there are several service columns, global row names and their translations. Moreover, some lines are presented only in English and German, since in the version 2.0 of the application the customer decided to leave only two languages ​​for the time being and add the rest later. Or maybe he was sorry for the money for translators. But this is his business, but we will have to take this into account. That is, the script must skip empty cells and not create empty lines for such a language. In addition, you need to consider formatting characters, such as "% s" in cell F5. They will have to work with them, because the fact that Android is good in iOS should be replaced by “% @”. About the rest of the nuances tell along the way.


In order not to torment and pull the cat by the tail, I’ll post the whole script right now:


VBScript in all its glory
option explicit ' Start it with: cscript ConvertExcelToTXTandXML.vbs If UCASE(right(wscript.fullname,11)) <> "CSCRIPT.EXE" Then Msgbox "Please enter: cscript ConvertExcelToTXTandXML.vbs <filename>.xlsx" WScript.Quit 255 End If ' The column of the key is 4 Const KeyColumn = 4 ' Names of destination files to create Const outFileiOS="\Localizable.strings" Const outFileiOSLocale="\InfoPlist.strings" Const outFileAndroid="\stringsToFormat.xml" Const NsCameraUsageDescription = "NsCameraUsageDescription" Const NSLocationAlwaysAndWhenInUseUsageDescription = "NSLocationAlwaysAndWhenInUseUsageDescription" Const NSLocationAlwaysUsageDescription = "NSLocationAlwaysUsageDescription" Const NSLocationWhenInUseUsageDescription = "NSLocationWhenInUseUsageDescription" Const NSPhotoLibraryAddUsageDescription = "NSPhotoLibraryAddUsageDescription" Const NSPhotoLibraryUsageDescription = "NSPhotoLibraryUsageDescription" Dim oExcel Dim oTranslations Dim objOutputFileiOS Dim objOutputFileiOSLocale Dim objFSO Dim objFSOandroid Dim objFSOios Dim myArgs Dim myParameter Dim sName Dim CompletePath Dim WorkingDir Dim WorkingDirAndroid Dim WorkingDirIos Dim LanguageColumnIndex Dim UsedRows Dim nCounter Dim xmlDoc Dim objIntro Dim objRoot Dim objHdr Dim objHdrAtt Dim theText Dim AndroidString Dim iOSString ' **************************************** ' MAKE PRETTY XML ' **************************************** Const strOutputFile = "\strings.xml" ' **************************************** Dim objInputFile, objOutputFile, strXML Dim objXML : Set objXML = WScript.CreateObject("Msxml2.DOMDocument") Dim objXSL : Set objXSL = WScript.CreateObject("Msxml2.DOMDocument") ' Create interface to Excel Set oExcel = CreateObject("Excel.application") ' Create the file interface Set objFSO=CreateObject("Scripting.FileSystemObject") ' Get the commandline parameter Set myArgs = WScript.Arguments.Unnamed If myArgs.count > 0 Then If (not objFSO.FileExists(myArgs.item(0))) Then Wscript.Echo "Error: '" & myArgs.item(0) & "' not found" WScript.Quit 255 End If Set myParameter = objFSO.GetFile(myArgs.item(0)) sName = myParameter.Name CompletePath = myParameter.Path WorkingDir = myParameter.Path WorkingDir = Left(WorkingDir, Len(WorkingDir)-Len(sName)) WorkingDirAndroid = "res\" WorkingDirIos = "ios\" If Not objFSO.FolderExists(WorkingDir & WorkingDirAndroid) Then ' Create folder if not exists' objFSO.CreateFolder(WorkingDir & WorkingDirAndroid) End If If Not objFSO.FolderExists(WorkingDir & WorkingDirIos) Then ' Create folder if not exists' objFSO.CreateFolder(WorkingDir & WorkingDirIos) End If Else Wscript.Echo "Error: A filename is needed" WScript.Quit 255 End If ' Source file Set oTranslations = oExcel.Workbooks.Open(CompletePath) ' Get the maximum number of rows in the sheet UsedRows = oTranslations.Sheets(1).UsedRange.Rows.Count ' In this column start the start the languages' LanguageColumnIndex = 6 ' stop the processing when the cell is empty --> end of languages while oTranslations.Sheets(1).Cells(3, LanguageColumnIndex) <> "" WScript.stdout.Write "Create files for: " WScript.stdout.Write oTranslations.Sheets(1).Cells(3, LanguageColumnIndex) WScript.stdout.Write " " objFSOandroid = oTranslations.Sheets(1).Cells(3, LanguageColumnIndex) If objFSOandroid = "values" Then objFSOios = "en" & ".lproj" ElseIf objFSOandroid = "values-ru" Then objFSOios = "ru-RU" & ".lproj" Else objFSOios = Right(objFSOandroid,2) & ".lproj" End If WScript.stdout.Write "; iOs folder: " WScript.stdout.Write objFSOios If Not objFSO.FolderExists(WorkingDir & WorkingDirAndroid & objFSOandroid) Then ' Create folder if not exists' objFSO.CreateFolder(WorkingDir & WorkingDirAndroid & objFSOandroid) End If If Not objFSO.FolderExists(WorkingDir & WorkingDirIos & objFSOios) Then ' Create folder if not exists' objFSO.CreateFolder(WorkingDir & WorkingDirIos & objFSOios) End If ' Create the destination files Set objOutputFileiOS = CreateObject("ADODB.Stream") objOutputFileiOS.CharSet = "utf-8" objOutputFileiOS.Open Set objOutputFileiOSLocale = CreateObject("ADODB.Stream") objOutputFileiOSLocale.CharSet = "utf-8" objOutputFileiOSLocale.Open Set xmlDoc = CreateObject("Msxml2.DOMDocument") ' NOTE: chr(34) is " ' NOTE: vbCrLf is <CR><LF> ' Create the XML header Set objIntro = xmlDoc.createProcessingInstruction("xml","version='1.0' encoding='UTF-8' standalone='yes'") xmlDoc.insertBefore objIntro,xmlDoc.childNodes(0) Set objRoot = xmlDoc.createElement("resources") xmlDoc.appendChild objRoot ' keys start in row 3!!! For nCounter = 3 To UsedRows WScript.stdout.Write "." If oTranslations.Sheets(1).Cells(nCounter, KeyColumn).Value <> "" Then ' Write to iOS file If Not oTranslations.Sheets(1).Cells(nCounter, LanguageColumnIndex).Value = "" Then objOutputFileiOS.WriteText chr(34) objOutputFileiOS.WriteText oTranslations.Sheets(1).Cells(nCounter, KeyColumn).Value objOutputFileiOS.WriteText chr(34) objOutputFileiOS.WriteText " = " objOutputFileiOS.WriteText chr(34) iOSString = Replace(oTranslations.Sheets(1).Cells(nCounter, LanguageColumnIndex).Value, "%s", "%@") iOSString = Replace(iOSString, "'", "\'") iOSString = Replace(iOSString, chr(34), "\" & chr(34)) objOutputFileiOS.WriteText iOSString objOutputFileiOS.WriteText chr(34) objOutputFileiOS.WriteText ";" & vbCrLf If ( (oTranslations.Sheets(1).Cells(nCounter, KeyColumn).Value = NsCameraUsageDescription) _ or (oTranslations.Sheets(1).Cells(nCounter, KeyColumn).Value = NSLocationAlwaysAndWhenInUseUsageDescription) _ or (oTranslations.Sheets(1).Cells(nCounter, KeyColumn).Value = NSLocationAlwaysUsageDescription) _ or (oTranslations.Sheets(1).Cells(nCounter, KeyColumn).Value = NSLocationWhenInUseUsageDescription) _ or (oTranslations.Sheets(1).Cells(nCounter, KeyColumn).Value = NSPhotoLibraryAddUsageDescription) _ or (oTranslations.Sheets(1).Cells(nCounter, KeyColumn).Value = NSPhotoLibraryUsageDescription) _ ) Then objOutputFileiOSLocale.WriteText chr(34) objOutputFileiOSLocale.WriteText oTranslations.Sheets(1).Cells(nCounter, KeyColumn).Value objOutputFileiOSLocale.WriteText chr(34) objOutputFileiOSLocale.WriteText " = " objOutputFileiOSLocale.WriteText chr(34) objOutputFileiOSLocale.WriteText iOSString objOutputFileiOSLocale.WriteText chr(34) objOutputFileiOSLocale.WriteText ";" & vbCrLf End If End If ' Write to Android file Set objHdr = xmlDoc.createElement("string") Set objHdrAtt = xmlDoc.createAttribute("name") objHdrAtt.text = oTranslations.Sheets(1).Cells(nCounter, KeyColumn).Value AndroidString =Replace (oTranslations.Sheets(1).Cells(nCounter, LanguageColumnIndex).Value, "'", "\'") AndroidString =Replace (oTranslations.Sheets(1).Cells(nCounter, LanguageColumnIndex).Value, Chr(10), "\n") If Not AndroidString = "" Then Set theText=xmlDoc.createTextNode(AndroidString) objHdr.setAttributeNode objHdrAtt objHdr.appendChild theText objRoot.appendChild objHdr End If End If Next ' Save the files xmlDoc.Save WorkingDir & WorkingDirAndroid & objFSOandroid & outFileAndroid ' **************************************** ' Put whitespace between tags. (Required for XSL transformation.) ' **************************************** Set objInputFile = objFSO.OpenTextFile(WorkingDir & WorkingDirAndroid & objFSOandroid & outFileAndroid,1,False,-2) Set objOutputFile = objFSO.CreateTextFile(WorkingDir & WorkingDirAndroid & objFSOandroid & strOutputFile,True,False) strXML = objInputFile.ReadAll strXML = Replace(strXML,"><",">" & vbCrLf & "<") objOutputFile.Write strXML objInputFile.Close objFSO.DeleteFile(WorkingDir & WorkingDirAndroid & objFSOandroid & outFileAndroid) objOutputFile.Close ' **************************************** ' Create an XSL stylesheet for transformation. ' **************************************** Dim strStylesheet : strStylesheet = _ "<xsl:stylesheet version=""1.0"" xmlns:xsl=""http://www.w3.org/1999/XSL/Transform"">" & _ "<xsl:output method=""xml"" indent=""yes""/>" & _ "<xsl:template match=""/"">" & _ "<xsl:copy-of select="".""/>" & _ "</xsl:template>" & _ "</xsl:stylesheet>" ' **************************************** ' Transform the XML. ' **************************************** objXSL.loadXML strStylesheet objXML.load WorkingDir & WorkingDirAndroid & objFSOandroid & strOutputFile objXML.transformNode objXSL objXML.save WorkingDir & WorkingDirAndroid & objFSOandroid & strOutputFile ' **************************************** ' End transformation. ' **************************************** objOutputFileiOSLocale.SaveToFile WorkingDir & WorkingDirIos & objFSOios & outFileiOSLocale, 2 objOutputFileiOS.SaveToFile WorkingDir & WorkingDirIos & objFSOios & outFileiOS, 2 LanguageColumnIndex = LanguageColumnIndex + 1 WScript.stdout.Write vbCrLf wend oTranslations.Close oExcel.Quit WScript.Echo "With success done" WScript.Quit(0)

Now is the time to go through the nuances.


Our application requires multiple user permissions. On iOS, strings for requesting these permissions are not stored as usual in Localizable.strings, but in InfoPlist.strings, so at the very beginning of our script we define the names of those strings that will be rendered in InfoPlist:


 Const NsCameraUsageDescription = "NsCameraUsageDescription" Const NSLocationAlwaysAndWhenInUseUsageDescription = "NSLocationAlwaysAndWhenInUseUsageDescription" Const NSLocationAlwaysUsageDescription = "NSLocationAlwaysUsageDescription" Const NSLocationWhenInUseUsageDescription = "NSLocationWhenInUseUsageDescription" Const NSPhotoLibraryAddUsageDescription = "NSPhotoLibraryAddUsageDescription" Const NSPhotoLibraryUsageDescription = "NSPhotoLibraryUsageDescription"

The next interesting piece is the folder names where all files will be saved. On iOS, we have all the folders called two-letter language designation, such as "en.lproj", "de.lproj". Everything, except Russian, here "ru-RU". And in the table itself, the columns are indicated in the Android notation. Therefore parsim:


 If objFSOandroid = "values" Then objFSOios = "en" & ".lproj" ElseIf objFSOandroid = "values-ru" Then objFSOios = "ru-RU" & ".lproj" Else objFSOios = Right(objFSOandroid,2) & ".lproj" End If

And the last task, replacing and escaping characters. For iOS, we will change, as I said, % s to % @ and escape quotes and apostrophes:


 iOSString = Replace(oTranslations.Sheets(1).Cells(nCounter, LanguageColumnIndex).Value, "%s", "%@") iOSString = Replace(iOSString, "'", "\'") iOSString = Replace(iOSString, chr(34), "\" & chr(34))

For Android, we also screen the apostrophes and replace the so-called Line Feed character ( Chr (10) ) with the usual New Line character \ n . And there is a reason for this. In one of the cells, we have a rather rather large text, compiled by the customer in MS Word and placed in an Excel cell using technical copy-paste. And while we didn’t pick up the correct replacement by trial and error, the text in iOS was displayed with the necessary paragraphs, and in Android it merged into one paragraph.


Conclusion


As you have probably guessed, the script runs in the Windows command line. For simplicity, put the script and the .xlsx file in one folder, go to the command line and write the command:


cscript ConvertExcelToTXTandXML.vbs <filename>.xlsx


Next, press Enter and enjoy the beautiful visualization of the script in the form of points appearing in the command window for each program step. The fruit of the titanic work of our script are two folders, "ios" and "res", the contents of which remained to be copied to iOS and Android, respectively, the project.


That's all. I hope this script will be useful to someone and save a lot of time.


')

Source: https://habr.com/ru/post/446100/

More articles:


All Articles