ExcelスプレッドシートからAndroidおよびiOSのローカライズファイルを自動的に作成する

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)