Тема: 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-файле, используя текст в распознанных заголовках колонок (что в дальнейшем может быть очень полезным, см. те же самые Шаблоны с формулами, опирающимися на имена колонок).
Заголовки колонок определяются, выделяются, ставятся в заголовки печати и закрепляются в области просмотра, чтобы заголовки всегда были на экране.