[решено] как внести коррективы в .xls файл используя .BAT

[Ka3hak]

День добрый Уважаемые знатоки, всеми нами любимого, супер-форума OSZone!
Уважаемый "Боженька кодеров" megaloman, ну и конечно же Iska, дорогие...
Излагаю суть:
1) Необходимо внести коррективы в .xls-файл по средствам .BAT, .CMD либо .VBS.
Лучше наверное будет .VBS, потому как на BAT-ники многие антивирусы попросту ругаются. А там, конечно же, на ваше усмотрение, Уважаемые.
Почему коррективы? Потому, как столбцы в .xls изначально размечены по ширине и присутствует некая шапка-заголовок.
Информация заносится "в строку", в каждую ячейку, (слева на право :D), как в текстовом, так и в числовом виде (зависит от типа столбца).
Для примера: (№3|Вася Пупкин|Мужчина|КрасаУчиГ|28|ЖиЕсть);
или: (12|текст|текст|текст|56|текст), в общем, что-то вроде того.
2) Возможно ли выполнить проверку на "пустоту строки"? Т.е. строки №1,№2,№3 уже заполнены Васей, Федей и Толиком, а нужна запись в №4 - как следствие.
Или, другими словами, "запись в близжайшую свободную строку",
что в конкретной задаче необходимо для последовательности:
#1
#2
#3
...

3) Есть ли возможность привязать вдовесок какую-нибудь формулу, например "произведение чисел" или ячеек?

P.S. Возможно я всё усложняю и "пункт 2)" можно реализовать как-то иначе. Например несколько BAT-ников(если на .BAT всё же пал выбор)
вносят коррективы в один ОСНОВНОЙ (пополня список Вась, Петь и т.д.), а тот уже одним махом внесёт все данные в .xls
Т.е. BAT1(№1), BAT2(№2), BAT(...) to>>> .BAT(основной)
(№1|текст|число|текст|)
(№2|текст|число|текст|)
to>>> .xls

Мне кажется, что так даже будет лучше. Но вот незадача! Снова проверка ближайших свободных "пустых строк" :(

[Iska]

1) Необходимо внести коррективы в .xls-файл по средствам .BAT, .CMD либо .VBS. »
Про первое — забудьте.

потому как на BAT-ники многие антивирусы попросту ругаются. »
Не видел ни единого разу.

Информация заносится "в строку", в каждую ячейку, (слева на право :D), как в текстовом, так и в числовом виде (зависит от типа столбца).
Для примера: (№3|Вася Пупкин|Мужчина|КрасаУчиГ|28|ЖиЕсть);
или: (12|текст|текст|текст|56|текст), в общем, что-то вроде того. »
Где файл Рабочей книги, упакованный в архив и приложенный к сообщению («что-то вроде того» может вызвать только аналогичный ответ)? Как и чем «заносится информация» на Рабочий лист?

2) Возможно ли выполнить проверку на "пустоту строки"? Т.е. строки №1,№2,№3 уже заполнены Васей, Федей и Толиком, а нужна запись в №4 - как следствие.
Или, другими словами, "запись в близжайшую свободную строку", »
Легко. Как только будет от Вас выложен образец Рабочей книги.

3) Есть ли возможность привязать вдовесок какую-нибудь формулу, например "произведение чисел" или ячеек? »
Можно.

[DJ Mogarych]

Ka3hak, если файл будет перекодирован в xlsx, то Powershell в модулем ImportExcel без проблем работает с такими файлами.

Работать с xls - это крайне неудобно и медленно.

[Iska]

Работать с xls - это крайне неудобно и медленно. »
Не могу с Вами согласиться, коллега.

[Ka3hak]

Не видел ни единого разу. »
Да, но это факт.
Про первое — забудьте. »
Вот, после такого ответа, разве имеется смысл дальнейших манипуляций в виде "файла Рабочей книги, упакованного в архив и приложенного к сообщению"?
Или Вы в силах помочь мне каким-то другим способом?
Легко. Как только будет от Вас выложен образец Рабочей книги. »
Хорошо. Позже прикреплю архив.

[DJ Mogarych]

Iska, ну а как с ним работать? Только через COM-объект Экселя?

[Iska]

Да, но это факт. »
Продемонстрируйте.

Вот, после такого ответа, разве имеется смысл дальнейших манипуляций в виде "файла Рабочей книги, упакованного в архив и приложенного к сообщению"? »
Имеет. А вот без архива с файлом я не вижу особого смысла заниматься и вторым:
либо .VBS. »
вариантом.

Iska, ну а как с ним работать? Только через COM-объект Экселя? »
Угу. В ряде случаев можно и ADO пользовать (если сие допустимо по сути ТЗ).

[DJ Mogarych]

Ну я это и называю "неудобно". Лучше сначала

"C:\Program Files (x86)\Microsoft Office\Office12\excelcnv.exe" -oice "C:\scripts\test1.xls" "C:\scripts\test1.xlsx"

а потом ImportExcel-ем делай что хочешь.

[Ka3hak]

Iska, прикрепил файл к теме.
Скрипт производит запись в строки с 12-ой по 24-ую (выделенные кроваво-красным цветом), а точнее - любую ближайшую, свободную, в данном случае 12-ую, нО_о...!
Вот не задача! :( Ведь "столбец B" и "столбец А" занимают аж 3 строки! И если скрипт произвёл запись в строку 12, то следующая ближайшая (для повторной отработки скрипта) аж 15-ая!
Не окажется ли это непреодолимой преградой в реализации поставленной задачи? Далее...
"Строку 19" наверное нужно игнорировать, даже не знаю. Т.к. это некое оглавление определённого типа работ, именуемого например "Укладка", но это уже "вопрос десятый", опустим.
В "столбце F"(который зелёненький), с 7-ой строки и до бесконечности происходит произведение "столбцов H на I". По-большому счёту, это основная формула для этого столбца.
Продемонстрируйте. »
P.S. Все (или почти) BAT-ники из этой темы: http://forum.oszone.net/thread-327297.html ловко сожрал Dr.Web CureIt, а Касперовский визжал как свинья. Хорошо хоть в архивах исходники не тронул.
Не помог даже некий "Bat To Exe Converter", чтобы скрыть содержание файла. А хотяяя! Может в нём-то и была проблема, уже не помню... Но интереса ради - обязательно проверю и залью скриншоты Log-а ;)

[Iska]

Скрипт производит запись »
Какой скрипт? Какую запись?

выделенные кроваво-красным цветом »
который зелёненький »
Вам стоит проверить зрение.

Все (или почти) BAT-ники из этой темы: http://forum.oszone.net/thread-327297.html … »
Конкретнее, пожалуйста. «Все (или почти)» — это ни о чём.

Не помог даже некий "Bat To Exe Converter" »
Вот «некий "Bat To Exe Converter"», как правило, ломаный и заражённый какой-нибудь хренью, может давать такой эффект.


Ka3hak, попробуйте теперь внятно сформулировать техзадание.

[Ka3hak]

Какой скрипт? Какую запись? »
Visual Basic Script. Visual Basic Script-овую запись. Запись в .xls файл, если помните...
Под словом "производит" - я подразумеваю "должен". Это своего рода конкретизация поставленной задачи.
В строку 12. Если ячейка столбца данной строки не пустая, то в 13. Если и та содержит информацию, то в 14.
ЗАПИСЬ аналогичную специально представленными выше примерами(в .xls файле).

[DJ Mogarych]

В нынешнем виде таблица непригодна для автоматизации, во всяком случае, эта автоматизация будет крайне трудоёмкая.

1) Для нормальной автоматизации нужно избавиться от объединённых ячеек, продублировав данные.


№ п/п : 1
Наименование работы : Устройство армированных стяжек толщ 20мм
Материал : раствор или бетон
Ед. изм : м3
Норма на ед. изм. : 6,5
Нормативный документ : Устройство монолитных ж/б и бетонных конструкций
"это" : 1,3
умножить на "это" : 5

№ п/п : 1
Наименование работы : Устройство армированных стяжек толщ 20мм
Материал : сетка сварная
Ед. изм : м2
Норма на ед. изм. : 7,6464
Нормативный документ : Устройство монолитных ж/б и бетонных конструкций
"это" : 1,08
умножить на "это" : 7,08

№ п/п : 2
Наименование работы : Устройство стяжек армированных полипропиленовой фиброй
Материал : цемент
Ед. изм : кг
Норма на ед. изм. : 15120
Нормативный документ : Устройство монолитных ж/б и бетонных конструкций
"это" : 280
умножить на "это" : 54

№ п/п : 2
Наименование работы : Устройство стяжек армированных полипропиленовой фиброй
Материал : песок
Ед. изм : м3
Норма на ед. изм. : 2,3
Нормативный документ : Устройство монолитных ж/б и бетонных конструкций
"это" : 1,15
умножить на "это" : 2

№ п/п : 2
Наименование работы : Устройство стяжек армированных полипропиленовой фиброй
Материал : фибра
Ед. изм : кг
Норма на ед. изм. : 0,63
Нормативный документ : Устройство монолитных ж/б и бетонных конструкций
"это" : 0,7
умножить на "это" : 0,9

Потом с этими данными уже можно работать, добавлять строки и т. д.

2) Ячейки с числами сделать реально числовыми. Сейчас, если импортировать из экселя данные, то умножить числа не получается, для этого надо объяснить компьютеру, что это действительно числа

[double]$1 = $_.'"это"' -replace ',','.'
[double]$2 = $_.'умножить на "это"' -replace ',','.'

[Ka3hak]

DJ Mogarych, спасибо Вам большое за то, что откликнулись!
№ п/п : 1
Наименование работы : Устройство армированных стяжек толщ 20мм
Материал : раствор или бетон
Ед. изм : м3
Норма на ед. изм. : 6,5
Нормативный документ : Устройство монолитных ж/б и бетонных конструкций
"это" : 1,3
умножить на "это" : 5

№ п/п : 1
Наименование работы : Устройство армированных стяжек толщ 20мм
Материал : сетка сварная
Ед. изм : м2
Норма на ед. изм. : 7,6464
Нормативный документ : Устройство монолитных ж/б и бетонных конструкций
"это" : 1,08
умножить на "это" : 7,08

№ п/п : 2
Наименование работы : Устройство стяжек армированных полипропиленовой фиброй
Материал : цемент
Ед. изм : кг
Норма на ед. изм. : 15120
Нормативный документ : Устройство монолитных ж/б и бетонных конструкций
"это" : 280
умножить на "это" : 54

№ п/п : 2
Наименование работы : Устройство стяжек армированных полипропиленовой фиброй
Материал : песок
Ед. изм : м3
Норма на ед. изм. : 2,3
Нормативный документ : Устройство монолитных ж/б и бетонных конструкций
"это" : 1,15
умножить на "это" : 2

№ п/п : 2
Наименование работы : Устройство стяжек армированных полипропиленовой фиброй
Материал : фибра
Ед. изм : кг
Норма на ед. изм. : 0,63
Нормативный документ : Устройство монолитных ж/б и бетонных конструкций
"это" : 0,7
умножить на "это" : 0,9»
На счёт этого я всё понял. Спасибо Вам кстати за наставления, которые безусловно внесли прояснения.
Но вот что касается остального - возникают вопросы.
[double]$1 = $_.'"это"' -replace ',','.'
[double]$2 = $_.'умножить на "это"' -replace ',','.' »
Это код .VBS? Осмелюсь предположить что здесь речь об произведении двух столбцов, и замена символов - запятой на точку, так?

[Ka3hak]

DJ Mogarych, дело в том, что на текущий момент, в .VBS я нулевой. Но спешу заверить, что быстро учусь :)
Могу ли я предложить поставить задачу ещё проще и попросить кусочек полного кода, чтобы было от чего отталкиваться?
Мне важны только 3 ключевых момента.
1) Внедрение формулы в одну из ячеек строки, а лучше - на весь столбец. Потому как на основе одного (рабочего) скрипта, поочерёдно будут вноситься разные данные.
Но и хрен бы с этим "пунктом первым". Он просто блекнет на фоне пункта №2, ведь формулу произведения чисел можно изначально забить и ручками.
2) Очерёдности записи в ближайшую, верхнюю, свободную строку. С учётом дублирования, как вы подсказали - буду также колдовать.
3) Внесённые данные должны различаться по типу: текстовый - числовой, хотя и это, как я понимаю, тоже можно исполнить вручную.

И вдовесок ещё вопросик, пожалуйста... Я никак не смогу управлять размером ячейки по средствам .VBS?

[DJ Mogarych]

Нет, это Powershell. Также, необходимо установить модуль Import-Excel: https://www.powershellgallery.com/packages/ImportExcel

Полный код:

# Конвертация xls в xlsx
& 'C:\Program Files (x86)\Microsoft Office\Office16\excelcnv.exe' -oice D:\temp\normy_spisania_materialov.xls D:\temp\normy_spisania_materialov.xlsx

$xlsx = import-excel D:\temp\normy_spisania_materialov.xlsx -StartRow 4 -DataOnly

# Если ячейки в исходном файле не числовые
$xlsx.where{$_.'Ед. изм'} |% {
[double]$1 = $_.'"это"' -replace ',','.'
[double]$2 = $_.'умножить на "это"' -replace ',','.'
$1 * $2
}

# Если числовые
$xlsx.where{$_.'Ед. изм'} |% {
$_.'"это"' * $_.'умножить на "это"'
}


Видео по модулю: https://www.youtube.com/watch?v=U3Ne_yX4tYo&list=PL5uoqS92stXioZw-u-ze_NtvSo0k0K0kq

1) Формулы там вроде бы есть, см. справку по модулю.
2) Можно внести ещё один столбец просто с порядковыми номерами и отталкиваться от них, или сделать № п/п последовательными, если я правильно понял ваш вопрос.
3) Если данные будут вноситься скриптом, то при экспорте готового файла с данными будет всё в порядке. Вопрос в исходном файле.

Управлять размером ячейки при экспорте можно. Я обычно пользуюсь параметром -AutoSize.

[Iska]

Избавляться надо от Excel, потому как в примере — типичная картина для базы данных с тремя таблицами: Нормативные документы, Наименование работы, Материалы и связями между ними один-ко-многим. Посему — MS Access, 1С или любую другую не плоскую СУБД.

[megaloman]

В нынешнем виде таблица непригодна для автоматизации »Ka3hak, А что подразумевается под автоматизацией? Откуда надо вставлять данные? В каком они виде? Откуда известно, на какие нормы надо ссылаться? Возьмите для примера уже имеющиеся 2 пункта и покажите, как они выглядят в файле (?) для вставки в Excel.Внедрение формулы в одну из ячеек строки, а лучше - на весь столбец. »Это не проблема даже в Вашей таблице, но смысла в этом нет - руками в Excel это сделать проще и надёжнее.
И, опять же, не проще ли это сделать макросом в таблице, а не внешним скриптом?

[Ka3hak]

megaloman, доброго времени суток!
не проще ли это сделать макросом в таблице, а не внешним скриптом? »
Я рассматривал такой вариант. Но = нет. Задача стоит однозначная. Это "должно быть похоже" на полноценное ПО,
с удобным, понятным интерфейсом и как следствие - минимумом манипуляций для пользователя.
А что подразумевается под автоматизацией?
В аналогии с моей прошлой темой (http://forum.oszone.net/thread-327297.html) за что Вам отдельное, громаднейшее СПАСИБО,
подразумевается некая связка визуального GUI-интерфейса и исполняемых файлов в виде скриптов.
- В GUI оболочке есть выпадающий список с наименованием производимых работ на выбор.
- Выбираем необходимый пункт (тип производимых работ) и он уже ссылается на заведомо определённые скриптом значения.
- Далее - кнопка "apply" и уже тут отработывает скрипт, который заносит необходимую информацию в .xls файл.
Если быть несколько точнее, то условно: when press "apply"\ if change "наименование работ №1"\ then run "script1"\ else if... и т.д. и т.п.
Откуда надо вставлять данные? В каком они виде? Откуда известно, на какие нормы надо ссылаться? »
Данные содержит сам скрипт. Для каждого из пунктов производимых работ "он свой".

[DJ Mogarych]

Это "должно быть похоже" на полноценное ПО, с удобным, понятным интерфейсом и как следствие - минимумом манипуляций для пользователя. »И максимумом геморроя для того, кто возьмётся это реализовывать.

Зачем вообще всё это? Почему не использовать просто Excel, а не дублировать его интерфейс непонятными костылями?

[megaloman]

Ka3hak, Не понимаю задачи.Почему не использовать просто Excel, а не дублировать его интерфейс непонятными костылями »Данные содержит сам скрипт. Для каждого из пунктов производимых работ "он свой". »Как максимум, можно предложить вносить исходные данные в Excel - таблицы, например - одно данное - одна книга созданная по бланку, со своим именем в определённой папке, не пытаться ваять непонятную конструкцию для каждого данного в скрипте. И затем объединять таблицы в одну например макросом или скриптом.
То есть, работать надо в направлении организации процесса на базе полноценного ПО (Excel), либо Избавляться надо от Excel, потому как в примере — типичная картина для базы данных с тремя таблицами: Нормативные документы, Наименование работы, Материалы и связями между ними один-ко-многим. »