image


Здравствуйте, меня зовут Александр и я разработчик приложений для Android. Однажды я попал на проект, в котором было 11 языков интерфейса и более 600 строк. На стороне заказчика программистов не было, поэтому они хранили всё это дело в таблице Excel. Когда они что-то меняли в ней, то потом присылали эту таблицу нам со словами «Мы там жёлтым выделили ячейки с изменениями, актуализируйте Android и iOS приложения соответственно». После этого два разработчика теряли по паре часов, внося изменения вручную. А потом ещё выяснялось, что кто-то что-то где-то забыл, ошибся или не доделал, появлялись расхождения между платформами, заказчик нервничал, разработчики бесились. Меня такая ситуация в корне не устраивала, я стал искать пути автоматизации выгрузки строк из Excel. Результатом стал замечательный код на VBScript, которым мы до сих пор с удовольствием пользуемся. Сейчас я вам этот скрипт и представлю. Под катом некоторое количество картинок и код скрипта.


Итак, сначала взглянем на саму таблицу и оценим масштаб проблемы:


image


Вот она, красавица! Как мы видим, здесь есть несколько служебных колонок, глобальные названия строк и их переводы. Причём некоторые строки представлены только на английском и немецком языках, поскольку в версии приложения 2.0 заказчик решил оставить пока только два языка и остальные добавить потом. А может, ему денег на переводчиков жалко. Но это его дело, а вот нам придётся это учитывать. То есть скрипт должен пропускать пустые ячейки и не создавать пустые строки для такого языка. Кроме того, нужно учитывать знаки форматирования, такие как «%s» в ячейке F5. С ними придётся поработать, поскольку то, что Андроиду хорошо, в iOS должно быть заменено на «%@». Про остальные нюансы расскажу по пути.


Чтобы не томить и не тянуть кота за хвост, весь скрипт выложу прямо сейчас:


VBScript во всей красе
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)

Вот теперь время пройтись по нюансам.


Наше приложение требует нескольких разрешений пользователя. На iOS строки для запроса этих разрешений хранятся не как обычно в Localizable.strings, а в InfoPlist.strings, поэтому в самом начале нашего скрипта мы определяем названия тех строк, которые будут вынесены в InfoPlist:


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

Следующий достойный внимания фрагмент, это названия папок, куда будут сохранятся все файлы. На iOS у нас все папки названы двухбуквенным обозначением языка, типа "en.lproj", "de.lproj". Все, кроме русского, тут "ru-RU". А в самой таблице колонки указаны в нотации Android. Поэтому парсим:


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

И последняя задача, замена и экранирование символов. Для iOS мы будем менять, как я уже говорил, %s на %@ и экранировать кавычки и апострофы:


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

Для Android мы также экранируем апострофы и подменим так называемый Line Feed character (Chr(10)) на обычный New Line character \n. И тому есть причина. В одной из ячеек у нас есть довольно немаленький текст, составленный заказчиком в MS Word и помещёный в ячейку Excel с помощью техничной копипасты. И пока мы методом проб и ошибок не подобрали правильную замену, в iOS текст отображался нужными абзацами, а в Android сливался в один абзац.


Заключение


Как вы уже, наверное, догадались, скрипт запускается в Windows command line. Для простоты помещаем в одну папку скрипт и файл .xlsx, переходим туда в command line и пишем команду:


cscript ConvertExcelToTXTandXML.vbs <filename>.xlsx


Далее нажимаем Enter и наслаждаемся красивой визуализацией работы скрипта в виде появляющихся в командном окне точек на наждый шаг программы. Плодом титанического труда нашего скрипта становятся две папки, "ios" и "res", содержимое которых осталось скопировать в iOS и Android проект соответственно.


Вот и всё. Надеюсь, этот скрипт окажется полезным кому-нибудь и сэкономит кучу времени.

Комментарии (13)


  1. Alexufo
    30.03.2019 22:33
    +4

    Дружище, есть же экспорт в CSV. Да и распарсить либой excell не проблема. Решение у вас только под винду. Разве что надо чтобы Колю трогать не надо, он один знает как vbs работает тогда вопросов нет)

    Все бы может и ничего но в топку cmd c cscript. Добавь скрипт в файл перевода и выведи надстройку в виде новой кнопки на панели. Тогда будет тру уэй. Хотя если надо не заглядывая никогда в файл… ну тогда пункт выше.


    1. JustDont
      31.03.2019 14:09
      +1

      Да и распарсить либой excell не проблема.

      Лул. Нет. «Распарсить любой эксель», именно в такой постановке — задача куда страшнее ядерной войны.
      А вот экспорт в csv — действительно, есть везде и начинать надо было явно с него.


      1. Alexufo
        31.03.2019 18:06

        Ну если есть либа по работе с excell? чем сложнее то?)


  1. Hidadmin
    31.03.2019 13:21
    +4

    А как же Google Sheets

    image


    1. Bookvarenko
      31.03.2019 17:52

      Хитро!


    1. jawaharlalnehru Автор
      01.04.2019 01:10

      Ну это вопрос не к нам, а к заказчику. Он нам не платит за то, чтобы мы учили его составлять переводы.


  1. Dolios
    31.03.2019 14:24
    +1

    А не проще было заказчику рассказать про «po» файлы и соответствующий инструментарий?


  1. lubezniy
    31.03.2019 16:25

    А чего автора заминусовали-то? Перевод приходит от заказчика в виде Excel-файлов и этим скриптом буквально в несколько кликов преобразуется в необходимое — задача решена доступным и удобным для конкретной стороны способом, и решение опубликовано. Что, заказчик неправильный, раз в Excel шлёт? Или ему кровь с носу этот инструментарий нужен, хоть он и так справляется?


    1. Bookvarenko
      31.03.2019 17:43

      Испытывают личную неприязнь, хе-хе. Кушать не могут.


  1. baevra
    01.04.2019 01:05

    Мы написали cli программу, которая из yaml генерирует файлы локализации с поддержкой всех фич, а также генерирует структуру L10n для автокомплита, каждый раз в preBuild скрипте.
    Локализация боль, особенно когда есть необходимость в частом редактировании


  1. immaculate
    01.04.2019 03:30

    Выше одной строкой уже упомянули, а я немного разверну. Что мешает использовать .po файлы и огромный развитой инструментарий gettext?


    Когда я вижу переводы в Excel, у меня аж скулы начинает сводить. Потому что каждый проект, где использовались такие переводы, был болью. Сильной непрекращающейся болью. И сколько не автоматизируй этот ужас, легче не станет.


    Совсем недавно в одном таком проекте заменил переводы через Excel на gettext, развернул weblate для переводчиков. Во-первых, все стали намного меньше тратить времени на возню с переводами. Во-вторых, перестали теряться переводы, что очень не нравилось переводчикам (никому не нравится заново переводить одно и то же несколько раз из-за несовершенства инструмента).


    Поэтому решительно не понимаю, зачем люди в 2019 году строят велосипеды с квадратными колесами… И ладно бы уперлись в ограничения gettext (а таковые есть), так нет, просто с самого начала стреляют себе в ногу.


    1. lubezniy
      01.04.2019 07:40

      Простите, не удержался:
      image
      По мне так автор вполне себе ясно расписал специфику работы не только своей, но и взаимодействия с заказчиком. Ему что, заказчика бросать из-за того, что он ищет и использует переводчиков со знанием Excel, а не weblate/gettext? Может, в будущем, если они столкнутся с проблемами, то посмотрят подходящие для себя решения, их опробуют и распространят на все проекты. Но не стоит совершенно со стороны абсолютно всё развивающееся «не по канонам» (со своей, совершенно посторонней точки зрения) считать сразу говном.


    1. Bookvarenko
      01.04.2019 12:55
      +1

      Одного комментария маловато. Есть ли возможность запилить статью «Как правильно переводить в 2019 году без ада и суеты»?