HTA & VBS: TXT-XLS конвертация

Всем привет!

    Предисловие.

Долго не решался выложить данную программу, т.к. всё время что-то менялось, постоянно программа переписывалась.
И сомневался, нужна ли она широким массам - хотя сам использую её постоянно (+50 пользователей).

    Недостатки.

Код не очень гладкий (очень даже не гладкий) - не нашёл я способа, как ловко и быстро заменить, допустим, "if then else" на "If Then Else".
Код слабо комментирован - дополню, если кого заинтересует.
В качестве оправдания, можно сказать, что 4 года код "в движении", переписывается и видоизменяется.
Но вроде бы работает, вопреки всем законам физики

    Благодарности.

Спасибо данному форуму, который поистине кладезь идей - и его участникам.
Я многие из идей и наработок использовал, и если кого-то из авторов не упомянул лично - обещаю исправить.

Далее идёт текст, который, возможно, удобнее прочитать в прилагаемом архиве, файл ZTxtXls_TechInfo.docx.

    Назначение.

Данная программа конвертирует текстовые файлы определённого формата (табличный вид с разделителями, наподобие CSV) в файлы Excel.

В той КИС, в которой я работаю (построенной на Оракле – OEBS), большинство отчётов получаются в виде PDF-файлов, с дополнительной возможностью выгрузки данных в текстовый файл (TXT-файл). Для этого в режиме просмотра PDF-файла нужно нажать на ссылку вида: Доступ к файлу здесь, после чего данные отчёта будет сохранены в виде TXT-файла.

После сохранения данных отчёта в TXT-файл, для дальнейшего анализа и обработки данных, перед пользователем встаёт задача конвертации этого файла в формат MS Excel (XLS-файл). Вручную пользователю предстоит выполнить следующие действия (затратив время и силы, при этом сам он должен обладать определённой квалификацией):

• выбрать тип файлов: Текст, найти и открыть сохранённый TXT-файл;
• определить формат кодировки символов: чаще всего это 866-DOS или 1251-Windows;
• подобрать разделитель столбцов в TXT-файле – это могут быть (из тех, что я наблюдал в нашей КИС):
«;» (точка с запятой)
«,» (запятая)
«:» (двоеточие)
«знак табуляции»;
• задать формат столбцов, содержащих числа:
если оставить «Общий», то Excel будет самостоятельно определять тип данных в каждой ячейке столбца, из-за чего возникают проблемы – например, числа вида «5.2» (в отчётах КИС разделитель дробной части точка) могут быть необратимо преобразованы в даты «5.фев»;
если задать «Текст», то в дальнейшем потребуются дополнительные действия для того, чтобы получить числовой столбец (например, заменить все вхождения «.» на «,»);
• после открытия файла – задать шрифт, ширину столбцов, сохранить файл в формате книги Excel и т.п.
Данная программа предназначена для автоматизации вышеперечисленных действий.

    История.

Программа реализована в 2016 г. как простая утилита для загрузки TXT-файлов в Excel.

Изначально в папке программы содержался сам файл программы ZTxtXls.vbs, а также папки:
• TXT\ – в эту папку нужно было текстовые (.txt, .csv) файлы для обработки.
• XLS\ – в этой папке сохранялись XLS-файлы.
• Архив\ – сюда переносились обработанные TXT-файлы.

Если при запуске утилиты был указан параметр (имя обрабатываемого файла) в командной строке, то обрабатывался именно этот файл, а содержимое папки TXT\ игнорировалось.

После окончания конвертации:
• если конвертировался 1 файл, то открывался получившийся XLS-файл.
• в случае нескольких файлов, появлялось сообщение «Файлов обработано: ...» и открывалась папка XLS\ (где хранились результаты конвертации).

С 2017 г. разработан и развивается графический интерфейс программы (Txt-в-Xls.hta). Основное его предназначение – следить за выбранной папкой TXT-файлов (по умолчанию это папка Загрузки, C:\Users\Имя пользователя\Downloads), и при сохранении в неё TXT-файла запускать его конвертацию (вызывая ZTxtXls.vbs с заданными параметрами). Кроме этого, в интерфейсе (HTA) можно наглядно задавать параметры для запуска утилиты (VBS).

    Установка.

• Перед установкой необходимо закрыть окно программы «TXT-XLS конвертация» (если в данный момент запущена старая версия) и все окна программы MS Excel.

• Сохраните прилагаемый архивный файл в любом месте (например, на Рабочий стол);

• Извлеките содержимое – нажмите на архивный файл правой кнопкой мыши, выберите пункт меню «Извлечь в текущую папку».

Архив будет распакован в папку «Рабочий стол\TXT-XLS конвертация».

• Системные требования: Windows XP и выше, Excel 2007 и выше.

    Описание работы.

Инструкция для пользователя находится в папке программы, в файле ZTxtXls.docx.

В папке «TXT-XLS конвертация\» находится папка программы ZTxtXls\ и ярлык «Txt-в-Xls» (запускающий ZTXTXLS\txt-в-xls.hta):

В папке программы содержатся уже рассмотренные выше папки:
• TXT\ – папка для обрабатываемых TXT-файлов, если не найдена папка Загрузки\.
• XLS\ – в этой папке сохраняются XLS-файлы.
• Архив\ – сюда переносятся обработанные TXT-файлы, а также сохраняются перезаписываемые XLS-файлы.
• Шаблоны\ – здесь хранятся файлы Шаблонов, в которых листы-справочники заполняются из TXT-файлов, это уже более «продвинутое» использование программы.

Далее рассмотрим работу программных файлов HTA и VBS.

    Txt-в-Xls.hta.

Графический интерфейс программы:

1. Автоматическая обработка TXT-файлов, появляющихся в папке Загрузки\, основана на механизме асинхронного отслеживания изменения содержимого данной папки:

Sink_Init()

  oService.ExecNotificationQueryAsync objSinkCreat, _
    "SELECT * FROM __InstanceCreationEvent WITHIN 2 WHERE " &_
    "Targetinstance ISA 'CIM_DirectoryContainsFile' " &_
    " AND TargetInstance.GroupComponent= 'Win32_Directory.Name=""" & Replace(sFolder, "\", "\\\\") & """'"

  oService.ExecNotificationQueryAsync objSinkModif, _
    "SELECT * FROM __InstanceModificationEvent WITHIN 2 WHERE " &_
    "Targetinstance ISA 'CIM_DataFile' " &_
    " AND (   TargetInstance.Extension LIKE '" & Mid(s_TxtExt, 2) & "%' " &_
    "      OR TargetInstance.Extension LIKE '" & Mid(s_CsvExt, 2) & "%' " &_
    "      OR TargetInstance.Extension LIKE '" & Mid(s_XlsExt, 2) & "%')" &_
    " AND TargetInstance.Drive = '" & sDrive & "'" &_
    " AND TargetInstance.Path = '" & Replace(sFolder, "\", "\\") & "'" '\\Temp\\

В случае появления в папке Загрузки\ файлов с нужным расширением (.csv*, .txt*, .xls*), вызывается процедура постановки файла в очередь TXT-файлов Sink_Queue().

Кроме этого, отслеживаются события завершения дочерних процессов (VBS вызывается из HTA):

  oService.ExecNotificationQueryAsync objSinkExec, _ 
    "SELECT * FROM __InstanceDeletionEvent WITHIN 1 WHERE TargetInstance ISA 'Win32_Process' AND TargetInstance.ParentProcessId = " & gnScriptProcessID

После завершения такого процесса, если в очереди есть ожидающие обработки TXT-файлы, происходит запуск конвертации очередного файла: Exec_Queue() (вызывается также из процедуры Window_Timeout(), с определённой периодичностью) – т.е., новый вызов VBS из HTA.

2. Реализованы ведение журнала и информирование пользователя:
HtaLog() – вывод сообщения в журнал;
HtaStatus() – вывод сообщения в информационную область окна;
HtaLogStatus() – вывод сообщения в журнал и в информационную область окна.

3. Для наглядности в окне отражаются пути с использованием пользовательских имён папок: 

FolderToLocalizedNameFull() – выводит более понятное пользователю "Загрузки\111\222" вместо полного имени "C:\Users\Пользователь\Downloads\111\222";

  Set oFolder = goShellApp.NameSpace(sFolder)
  FolderToLocalizedName = oFolder.Title

FolderFromLocalizedNameFull(): – при переходе в поле ввода имени папки, выводит полное имя "C:\Users\Пользователь\Downloads\111\222" вместо Загрузки\111\222" (чтобы можно было вручную редактировать путь, либо копировать его в буфер обмена).

Из ветки реестра "HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders", дополнительно используя set oFolder = goShellApp.NameSpace(i), sFolder = oFolder.Self.Path.

4. Функции, используемые и в HTA, и в VBS расположены в одном месте: 

В HTA:

<script language="VBScript" src="ZTxtXls.vbs"> </script>

В VBS (проверка существования глобальной переменной glDebug – если да, то не переопределять константы и глобальные переменные):

If IsEmpty(Eval("glDebug")) Then 

    ZTxtXls.vbs.

Непосредственно программа конвертации TXT в XLS:

1. CsvProcess(): Анализ обрабатываемого TXT-файла (количество колонок, тип их данных, разделители и т.п.):

Определяется разделитель колонок (из определённых заранее вариантов: vbTab & ";:,|!");

Определяется кодовая страница (2 варианта: 866-DOS-Rus или 1251-Windows-Rus), функцию Guess_CP() я взял у yuriy2000 http://forum.script-coding.com/viewtopic.php?id=9940, за что ему – Огромное Спасибо!);

Function Guess_CP(byVal sStr)
   Dim sVowel    : sVowel    = "[аеоиуяы]" 'гласные (часто встречающиеся)
   Dim sNotVowel : sNotVowel = "[нтсрвлкмдпзбьчгй]{1,2}" 'согласные (часто встречающиеся)

   With New RegExp
      .Global     = True              
      .Pattern    = "[\s]+"            
       sStr       = .Replace(sStr, " ") 'Убрать переносы строк и двойные пробелы
      .Global     = False 
      .IgnoreCase = True 
      .Pattern    =  sNotVowel & sVowel & sNotVowel & sVowel  
      if .Test(sStr) then 
          Guess_CP = 1251
      elseif .Test(OemToAnsi(sStr)) then 
          Guess_CP = 866
      else
          Guess_CP = 0 'Не понятно
      end if
   End With

End Function

Определяются типы колонок:
- Время HH:MM:SS или HH:MM (если в тексте секунды не упомянуты);
- Дата, включая филологию типа «12-ОКТ-2007» (разделители подбираются из ".-/");
- Длинное целое число (типа ИНН, 11 знаков и больше) ¬– строго тип «Число», иначе тип «Общий» покажет что-то вроде «7.445E+11»;

2. XlsProcess(): Непосредственно конвертация, которая использует предыдущий анализ TXT-файла:

Программа присваивает имена колонкам в XLS-файле, используя текст в распознанных заголовках колонок (что в дальнейшем может быть очень полезным, см. те же самые Шаблоны с формулами, опирающимися на имена колонок).

Заголовки колонок определяются, выделяются, ставятся в заголовки печати и закрепляются в области просмотра, чтобы заголовки всегда были на экране.