Ну что ж, приступим.
Казалось бы задача проста:
Есть два или более однотипных файлов в эксель (в моем случае это инструкции на каждый пост производства которые надо последовательно слепить в одну большую инструкцию на всю сборочную линию) надо их последовательно слить в один длинный файл. Первое что приходит в голову — сделать новый файл (новую книгу эксель) и последовательно скопировать туда открытые исходные файлы. Но по ходу хождения по «граблям» оказалось несколько тонкостей о которых я бы и хотел рассказать, во-первых, что бы я этого больше никогда не забыл, а во-вторых мне бы хотелось помочь всем тем кто столкнется с подобной задачей.
Решение:
1. Создаем экземпляр эксель, книгу, лист (это понятно)
2. Если в нашем файле эксель есть картинки, как например у меня, вписанные в ячейки, то если не скопировать формат колонок в наш новый лист эксель, их покорежит. Поэтому копируем формат колонок из листа приемника в лист источник.
Процедура ФорматироватьШиринуКолонок(ЛистИсточник, ЛистПриемник)
КоординатыИсточник = КоординатыОбласти(ЛистИсточник);
ДиапазонИсточник = ДиапазонОбласти(ЛистИсточник,КоординатыИсточник);
КопироватьШиринуСтолбцовДиапазона(ЛистПриемник, КоординатыИсточник, ДиапазонИсточник);
КонецПроцедуры
Функция КоординатыОбласти(Лист)
НачальнаяКолонка = 1;
НачальнаяСтрока = 1;
КонечнаяКолонка = Лист.Cells.CurrentRegion.Columns.Count;
КонечнаяСтрока = Лист.UsedRange.Rows.Count;
СтруктураКоординат = Новый Структура("НачальнаяКолонка,НачальнаяСтрока,КонечнаяКолонка,КонечнаяСтрока",
НачальнаяКолонка,НачальнаяСтрока,КонечнаяКолонка,КонечнаяСтрока);
Возврат СтруктураКоординат;
КонецФункции
Функция ДиапазонОбласти(Лист, Координаты)
Диапазон = Лист.Range(Лист.Cells(Координаты.НачальнаяСтрока, Координаты.НачальнаяКолонка),
Лист.Cells(Координаты.КонечнаяСтрока, Координаты.КонечнаяКолонка));
Возврат Диапазон;
КонецФункции // ()
Процедура КопироватьШиринуСтолбцовДиапазона(ЛистПриемник, Координаты, Диапазон)
Диапазон.Copy(); //записали в буфер
ЛистПриемник.Range(ЛистПриемник.Cells(Координаты.НачальнаяСтрока, Координаты.НачальнаяКолонка),
ЛистПриемник.Cells(Координаты.НачальнаяСтрока+Координаты.КонечнаяСтрока-1,
// цифра 8 означает сохранить ширину столбцов как в источнике.
Координаты.НачальнаяКолонка+Координаты.КонечнаяКолонка-1)).PasteSpecial(8);
КонецПроцедуры
3. Осталось дело за малым — скопировать лист.
Для этого мы обязательно должны:
— Активировать лист источник
Процедура АктивироватьЛист(Лист)
Лист.Activate();
КонецПроцедуры
— Выделить диапазон копирования
Процедура ВыделитьДиапазон(Лист, НачальнаяСтрока, КонечнаяСтрока)
ДиапазонСтрок = Лист.Rows("" + Строка(НачальнаяСтрока) + ":" + Строка(КонечнаяСтрока));
ДиапазонСтрок.Select();
КонецПроцедуры
— Копировать выделение в буфер
Процедура КопироватьВыделениеВБуфер(Эксель)
Эксель.Selection.Copy();
КонецПроцедуры
— Активировать лист приемник
— Выделить диапазон вставки
— И собственно вставить
Процедура ВставитьВыделенное(Эксель)
Эксель.Selection.Insert(-4121);
КонецПроцедуры
— не забудем выделение снять
Процедура СнятьВыделение(Эксель)
Эксель.Selection.Cells(1).Select();
КонецПроцедуры
Вся процедура выглядит так
Процедура СкопироватьЛистЭксельВКонец(Эксель, ЛистИсточник, ЛистПриемник, КоличествоСтрокОтступа, ЛистФормата = Ложь)
//Получим координаты областей
КоординатыИсточника = КоординатыОбласти(ЛистИсточник);
КоординатыПриемника = КоординатыОбласти(ЛистПриемник);
//Для выделения диапазона строк сначала обязательно надо активировать лист в котором производится выделение
АктивироватьЛист(ЛистИсточник);
ВыделитьДиапазон(ЛистИсточник, КоординатыИсточника.НачальнаяСтрока, КоординатыИсточника.КонечнаяСтрока);
КопироватьВыделениеВБуфер(Эксель);
АктивироватьЛист(ЛистПриемник);
//При создании новой книги в ней присутствуют 3 пустых строки
//Что бы была только одна для первого копируемого листа установим начальную строку
Если ЛистФормата Тогда
НомерСтрокиНачалаВставки = 1;
Иначе
НомерСтрокиНачалаВставки = КоординатыПриемника.КонечнаяСтрока + КоличествоСтрокОтступа + 1;
КонецЕсли;
НомерСтрокиКонцаВставки = НомерСтрокиНачалаВставки + КоординатыИсточника.КонечнаяСтрока;
ВыделитьДиапазон(ЛистПриемник, НомерСтрокиНачалаВставки, НомерСтрокиКонцаВставки);
ВставитьВыделенное(Эксель);
СнятьВыделение(Эксель);
КонецПроцедуры
Вот собственно и все. Как говорится: «Ловкость рук и никакого мошейничества».
Прикладываю обработку слияния файлов, которую при необходимости вы легко сможете применять в любой своей конфигурации. Все что ей нужно — дать на вход массив путей ваших однотипных файлов эксель, а на выходе получить их квинтэссенцию. Либо просто заполнить табличную часть путями к файлам
Related Posts
Получение логина и пароля техподдержки 1С из базы
Класс для вывода отчета в Excel
Счет-фактура для УПП
Библиотека классов для создания внешней компоненты 1С на C#- Акт об оказании услуг (со скидками) — внешняя печатная форма для Управление торговлей 11.1.10.86
Прайс-лист с артикулом в отдельной колонке
Это конечно прикольно, на 1С склеивать 2 excel файла,
но в чем профит?
А зачем что-то делать в Эксель, если есть 1С?
В чисто исследовательских целях, очень даже интересно, хотя .. на кой это нужно!? 🙂
(1) alsoftik, (2) dka80, (3) DoctorRoza, в моем случае есть линия постовой сборки, технологи пишут инструкцию на каждый пост. На каждый пост своя инструкция и соответственно свой файл эксель. Что бы составить инструкцию на всю линию по каждому изделию я применил этот механизм. Зачем в прошлый раз вставала эта задача я уже не могу припомнить, но факт в том, что это надо было сделать так как оно реализовано.
Мы не ищем легких путей!
Sheets(«Лист2»).Select
Sheets(«Лист2»).Copy Before:=Sheets(1)
Точнее так
Workbooks.Add
Windows(«Книга1»).Activate
Sheets(«Лист1»).Select
Sheets(«Лист1»).Move After:=Workbooks(«Книга2»).Sheets(3)
(5) AnryMc, (6) AnryMc,
Пардон, невнимательно посмотрел, что всё надо «влепить» на один лист книги….
Добрый день.
Обратитесь на форум «Планета Эксель» и Вам там обязательно помогут. 🙂
(8) Hellgga, не думаю, что там кто-то напишет за меня на языке 1С 🙂
Ясное дело, что на языке 1С не напишут (хотя, как знать ;)) — форум-то экселевский.
А вот макрос для документа, сохранённого в экселе, очень даже напишут 🙂
(10) Hellgga, макрос можно и самому сделать))) потом посмотреть что он там понаписал) этим методом тоже пользуюсь периодически, но макрос не всегда делает оптимально конечно при этом.
Посмотреть, что он там понаписал! Это макрорекордером, что ли?
Макрорекордером это как след не напишешь…
Зачем копировать формат колонок? Почему нельзя было взять за основу один из файлов, сохранить его копию и добавлять сразу в него? Если уж формат у всех одинаковый…
(12) Hellgga, не владею термином «макрорекордер». Просто включаешь запись макроса, делаешь, то что надо руками. Останавливаешь запись макроса и смотришь как это все выглядит на Visual Basic
(13) Klyacksa, можно сделать и так, но тут могут возникнуть тонкости. Как например, если вдруг пользователь хочет слепить файлы, посмотреть на результат и сохранить результат в отдельный файл. В вашем варианте существует возможность, что пользователь просто нажмет записать и таким образом перезапишет исходный файл. Хотя если копию… можно и копию…
(14) так это и есть «запись макроса макрорекордером»; что-нибудь простенькое им можно наваять, а в Вашем случае надобно писать полноценный макрос.:)
(16), Дааа… Это просто офигительный сложный макрос:
AnryMc написал в (6):
Workbooks.Add
Windows(«Книга1»).Activate
Sheets(«Лист1»).Select
Sheets(«Лист1»).Move After:=Workbooks(«Книга2»).Sheets(3)
Как и остальные, не совсем понял, что делал автор. После прочтения статьи появились вопросы:
1. Автор изучает возможности доступные для экселя из vba методом тыка? Так вроде же документация существует.
2. Не понял, почему технологи не могут колотить сразу в 1Стекстовый документкуда угодно. Что бы сразу с нуля формировать нужный эксель из 1С, легкими движениями и без граблей. То есть в целом я бы начал со сбора и обработки данных. Если это возможно конечно.
3. Если просто есть 2 экселя и нужно сделать один, причем здесь 1С?
4. Почему работа с эксель у автора похожа на хождение по граблям? Ведь есть же документация?
В офисе есть отличный механизм — запись макроса. Включаешь, выполняешь необходимые действия — смотришь получившийся код. Пользы от статьи — 0.
(18) webester, мне поставлена задача, я ее решаю исходя из тех условий которые есть. По второму пункту, инструкции в файлах должны быть доступны в не зависимости от работоспособности 1С. По третьему пункту, если файлов эксель 2, то действительно проблем никаких и 1С не надо, но если постов 20 шт. на каждый пост по инструкции, при этом могут менятся как инструкции так и количество постов, то задача составить полную инструкцию на линию перестает быть такой тривиальной. По пункту №4: Если у вас есть документация по эксель полнее чем MDSN, буду рад, если вы со мной поделитесь.
(20) Вот чессно слово, если бы автор сразу написал, что постов 15-25, а инструкции меняются в среднем еженедельно, то вопросов «на фиг это надо» было бы меньше. кроме одного…
Зачем было делать это на VBA через 1С, а не в родной среде VBA? Неужто «так легче»?
(21) gaglo, ответ очень прост, я не владею VBA, но владею 1С. Поэтому я не понимаю, что вы имеете в виду говоря, что это можно было сделать в среде VBA, если вы мне дадите более развернутый ответ, как это можно сделать я буду признателен и обязательно изучу эту возможность для саморазвития.
Извините, ради Бога,за то, что вмешиваюсь в Ващ диалог…
Если Вы владеете 1С, то, не грех бы, владеть и VBA, поскольку 1С и Эксель всё-таки, хоть немного, но связаны.
А на VBA можно решать оочень многие вопросы (выгруженные из 1С)
Ещё раз, извините.
С уважением,
Ольга
(23) Hellgga, конечно не грех. Но согласитесь, в 1с множество вопросов, которые следует изучать и как это обычно бывает мы изучаем то, что нам нужно для решения текущих задач, придумывать себе мифические задачи и изучать их решение достаточно трудоемкая задача при большой текущей нагрузке.
Свободного времени всегда не хватает. Шутка: Раз уж мы владеем 1С не грех и научится плавать на собственной яхте ))
🙂
Эт точно. Для тех, кто владеет 🙂
(22) и (24) — Похвастун! Это 1С владеет вами! (Если свободного времени не хватает «всегда» — задумайтесь!)
Если серьезно, то «можно было сделать в среде VBA» значит именно то, что написано.
Открыть Excel, в нем редактор VBA, написать процедуры, вытащить на лист рабочей книги кнопку и посадить на нее вызов этой процедуры…
Только вместо
было бы
Sub CopyExcelSheetToEnd(SheetFrom, SheetTo, NumIndentRows, SheetFormat)…
а вместо
что-то вроде
ActiveSheet.Selection.Insert(xlShiftDown)
(примерно, конечно)
Чего все так накинулись на то, что автор склеивала файлы через 1С, а не непосредственно из VBA? Нормальная задача, тем более что дальше напрашивается расширение задачи — если нужно «клеить» файлы постоянно, в 1С делаем регламентную задачу с расписанием — и забываем даже про «нажатие» кнопочки в обработке. Ну конечно, при условии клиент-серверного варианта базы и возможности хоть как-то составить расписание (например, раз в день, или раз в неделю).
(15) я имела ввиду копию конечно, да… сами исходные файлы только на чтение, чтобы ни в коем случае их не повредить — хороший тон.
На самом деле интересен феномен, что такая простая разработка так оказалась востребована (25 плюсов спустя 4 дня). Можно сколько угодно ругать автора, но количество плюсов делает все эти нападки бессмысленными. Думаю польза от этой публикации в том что она дала легкий старт тем кто хотел написать более сложные алгоритмы на том же 1С механизме.
Возможно авторы правы, что надо знать VBA, но тут сразу возникает вопрос:
Если менее 5% твоей работы касается Экселевских документов — следует:
а) Профессионально изучать VBA и использовать его
б) Разобраться в азах VBA, сделать работу на VBA и потом долго исправлять косяки и обидные ошибки
в) Реализовать все на той платформе, где ты себя чувствуешь рыбой в воде, то есть 1С.
Вопрос не однозначный и можно бесконечно спорить по его решению. Но каждое решение имеет право на жизнь.
(26) gaglo, да, это здорово, но не в контексте решаемой задачи.
Если еще чуть подробнее, то показ инструкции на посте происходит по прибытии продукции на пост каждые 7 минут, происходит сканирование серийного номера, в зависимости от модели продукции инструкция выдается работнику, это же событие фиксируется в системе, для последующего анализа времени нахождения продукции на отдельных постах, линиях, участках. Возможно автоматическое создание документов. Поэтому логичнее все действия выполнять при помощи одной системы, в данном случае на базе 1С. Никто не нажимает никаких кнопок в эксель, только сканирование.
(28) Aleksey81, Спасибо, все верно, зачем тратить время на изучение не нужных вещей.
(27) Klyacksa, в моей задаче получается что результирующий файл не нужен на диске, он нужен в момент сканирования устройства и формируется на основании модели. Вот почему лишняя запись файла на диск была просто не нужна.
(29) вот тут не поняла. Что имеется ввиду под «лишней записи файла на диск»?
Ваше решение: берем пустой файл, копируем туда колонки из первого файла, и дальше дописываем из остальных файлов.
Мое предложение: берем первый файл, копируем его целиком (при этом не обязательно его куда-то записывать!), и дальше дописываем из остальных файлов.
Итого разница — «бегаем» по первому файлу, или копируем его системными средствами, вот и вся разница. Никакой доп.записи.
(30) Klyacksa,
а это как? если мы его копируем, разве это не значит, что мы его копию записываем на диск?
(31) да, действительно, создавать новый файл все-равно придется.
Но копировать-то можно сразу весь лист первого файла целиком:
Если скопировать первый файлиз списка файлов, то первый лист можно не копировать, а сразу лепить к нему все оставшиеся. Потом надо за собой убрать и удалить эту копию. Тут я с вами согласен, такой вариант тоже возможен, а можно просто скопировать формат ))
(27) Накинулись, накинулись… Вот я объясню, почему я «накинулся». Если бы в публикации сразу было написано, что инструкцию надо собирать и показывать каждые 7 минут, да по-разному, в зависимости от текущего изделия — я бы и не вспоминал про «чистый VBA», потому что он тут действительно не к месту. А пока формулировки типа «мне поставили задачу, я ее решаю» — задачу-то не раскрывали полностью — пока все и удивлялись, почему это прямо в 1С надо делать…
(28) Дело в том, что 24 этих скачивания у меня лично вызывают подозрения, что сделаны на всякий случай людьми, которые читают только свежие публикации, а поиском по Инфостарту почему-то не пользуются. Потому как тут за прошлые годы ну очень много насчет взаимодействия с Excel накопилось…
А ещё: «знать VBA» — если точнее, сам язык VBA достаточно прост, чтоб сказать, что там и знать-то нечего. А вот познать объектную модель документа Excel (вот эти вот Range, Cells, Activate) приходится независимо от того, на каком языке решаем мы нашу нелегкую задачу, уж если связались с файлами Excel вообще. Я лично понял, что именно познание этих методов и объектов сравнивал автор с «хождением по граблям».
А VBA знать не надо, так и договоримся.
к вящей славе автора и к моей потере времени поисковик на тематический запрос выводит на эту статью. простое копирование кода не взлетело почему-то. мож потому что на сервере. не было смысла разбираться, тк можно чуток проще — так, как советуют «знатоки vba».
содержимое TempSheet одной книги добавляется в SheetDest другой. сохранение формата достигается в моем случае макетами СКД и условным оформлением.
Показать
активизировать листы необязательно, можно просто присвоить sheetdest = book.sheets(1);