gifts2017

Я его слепила из того, что было. Два файла Excel в один.

Опубликовал Денис (Den_D) в раздел Обработки - Универсальные обработки

Когда встает задача сделать что-нибудь с файлами Excel, лично я чувствую себя как человек с завязанными глазами, идущий по полю с граблями. Матерясь, наступаю на грабли, но все равно иду вперед. И вот в очередной раз встала задача, на которую в прошлые разы ответа на просторах интернета найдено не было. Склеить два листа EXCEL в один.

Ну что ж, приступим.

Казалось бы задача проста:

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

Решение:

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;
	КонецЕсли; 
	
	НомерСтрокиКонцаВставки	 = НомерСтрокиНачалаВставки + КоординатыИсточника.КонечнаяСтрока;
	ВыделитьДиапазон(ЛистПриемник, НомерСтрокиНачалаВставки, НомерСтрокиКонцаВставки);
	
	ВставитьВыделенное(Эксель);
	
	СнятьВыделение(Эксель);
	
КонецПроцедуры

Вот собственно и все. Как говорится: "Ловкость рук и никакого мошейничества".

Прикладываю обработку слияния файлов, которую при необходимости вы легко сможете применять в любой своей конфигурации. Все что ей нужно - дать на вход массив путей ваших однотипных файлов эксель, а на выходе получить их квинтэссенцию. Либо просто заполнить табличную часть путями к файлам

Скачать файлы

Наименование Файл Версия Размер
СлияниеФайловEXCEL.epf 41
.epf 11,16Kb
30.07.13
41
.epf 11,16Kb Скачать

См. также

PowerTools от 1 000
Подписаться Добавить вознаграждение

Комментарии

1. Алексей (alsoftik) 31.07.13 06:26
Это конечно прикольно, на 1С склеивать 2 excel файла,
но в чем профит?
DrAku1a; WhiteOwl; +2 Ответить 3
2. dka80 ~ (dka80) 31.07.13 06:43
А зачем что-то делать в Эксель, если есть 1С?
3. Алексей Роза (DoctorRoza) 31.07.13 08:29
В чисто исследовательских целях, очень даже интересно, хотя .. на кой это нужно!? :)
4. Денис (Den_D) 31.07.13 09:12
(1) alsoftik, (2) dka80, (3) DoctorRoza, в моем случае есть линия постовой сборки, технологи пишут инструкцию на каждый пост. На каждый пост своя инструкция и соответственно свой файл эксель. Что бы составить инструкцию на всю линию по каждому изделию я применил этот механизм. Зачем в прошлый раз вставала эта задача я уже не могу припомнить, но факт в том, что это надо было сделать так как оно реализовано.
5. anry mc (AnryMc) 31.07.13 09:58
Мы не ищем легких путей!


Sheets("Лист2").Select
Sheets("Лист2").Copy Before:=Sheets(1)
6. anry mc (AnryMc) 31.07.13 10:01
Точнее так


Workbooks.Add
Windows("Книга1").Activate
Sheets("Лист1").Select
Sheets("Лист1").Move After:=Workbooks("Книга2").Sheets(3)
7. anry mc (AnryMc) 31.07.13 10:56
(5) AnryMc, (6) AnryMc,

Пардон, невнимательно посмотрел, что всё надо "влепить" на один лист книги....
8. Hellgga 31.07.13 11:49
Добрый день.
Обратитесь на форум "Планета Эксель" и Вам там обязательно помогут. :)
9. Денис (Den_D) 31.07.13 12:17
(8) Hellgga, не думаю, что там кто-то напишет за меня на языке 1С :)
10. Hellgga 31.07.13 14:56
Ясное дело, что на языке 1С не напишут (хотя, как знать ;)) - форум-то экселевский.
А вот макрос для документа, сохранённого в экселе, очень даже напишут :)
11. Денис (Den_D) 31.07.13 15:32
(10) Hellgga, макрос можно и самому сделать))) потом посмотреть что он там понаписал) этим методом тоже пользуюсь периодически, но макрос не всегда делает оптимально конечно при этом.
12. Hellgga 31.07.13 16:44
Посмотреть, что он там понаписал! Это макрорекордером, что ли?
Макрорекордером это как след не напишешь...
13. Ирина Симоненко (Klyacksa) 31.07.13 17:19
Зачем копировать формат колонок? Почему нельзя было взять за основу один из файлов, сохранить его копию и добавлять сразу в него? Если уж формат у всех одинаковый...
14. Денис (Den_D) 31.07.13 17:20
(12) Hellgga, не владею термином "макрорекордер". Просто включаешь запись макроса, делаешь, то что надо руками. Останавливаешь запись макроса и смотришь как это все выглядит на Visual Basic
15. Денис (Den_D) 31.07.13 17:23
(13) Klyacksa, можно сделать и так, но тут могут возникнуть тонкости. Как например, если вдруг пользователь хочет слепить файлы, посмотреть на результат и сохранить результат в отдельный файл. В вашем варианте существует возможность, что пользователь просто нажмет записать и таким образом перезапишет исходный файл. Хотя если копию... можно и копию...
16. Hellgga 31.07.13 19:47
(14) Den_D, так это и есть "запись макроса макрорекордером"; что-нибудь простенькое им можно наваять, а в Вашем случае надобно писать полноценный макрос.:)
17. Андрей Акулов (DrAku1a) 01.08.13 02:37
(16), Дааа... Это просто офигительный сложный макрос:

AnryMc написал в (6):

Workbooks.Add
Windows("Книга1").Activate
Sheets("Лист1").Select
Sheets("Лист1").Move After:=Workbooks("Книга2").Sheets(3)
18. Роман Ложкин (webester) 01.08.13 02:55
Как и остальные, не совсем понял, что делал автор. После прочтения статьи появились вопросы:
1. Автор изучает возможности доступные для экселя из vba методом тыка? Так вроде же документация существует.
2. Не понял, почему технологи не могут колотить сразу в 1С\текстовый документ\куда угодно. Что бы сразу с нуля формировать нужный эксель из 1С, легкими движениями и без граблей. То есть в целом я бы начал со сбора и обработки данных. Если это возможно конечно.
3. Если просто есть 2 экселя и нужно сделать один, причем здесь 1С?
4. Почему работа с эксель у автора похожа на хождение по граблям? Ведь есть же документация?
19. Sergei Disev (viramen) 01.08.13 08:06
В офисе есть отличный механизм - запись макроса. Включаешь, выполняешь необходимые действия - смотришь получившийся код. Пользы от статьи - 0.
20. Денис (Den_D) 01.08.13 09:09
(18) webester, мне поставлена задача, я ее решаю исходя из тех условий которые есть. По второму пункту, инструкции в файлах должны быть доступны в не зависимости от работоспособности 1С. По третьему пункту, если файлов эксель 2, то действительно проблем никаких и 1С не надо, но если постов 20 шт. на каждый пост по инструкции, при этом могут менятся как инструкции так и количество постов, то задача составить полную инструкцию на линию перестает быть такой тривиальной. По пункту №4: Если у вас есть документация по эксель полнее чем MDSN, буду рад, если вы со мной поделитесь.
21. Борис Скворцов (gaglo) 01.08.13 10:33
(20) Вот чессно слово, если бы автор сразу написал, что постов 15-25, а инструкции меняются в среднем еженедельно, то вопросов "на фиг это надо" было бы меньше. кроме одного...
Зачем было делать это на VBA через 1С, а не в родной среде VBA? Неужто "так легче"?
22. Денис (Den_D) 01.08.13 11:52
(21) gaglo, ответ очень прост, я не владею VBA, но владею 1С. Поэтому я не понимаю, что вы имеете в виду говоря, что это можно было сделать в среде VBA, если вы мне дадите более развернутый ответ, как это можно сделать я буду признателен и обязательно изучу эту возможность для саморазвития.
23. Hellgga 01.08.13 12:59
Извините, ради Бога,за то, что вмешиваюсь в Ващ диалог...
Если Вы владеете 1С, то, не грех бы, владеть и VBA, поскольку 1С и Эксель всё-таки, хоть немного, но связаны.
А на VBA можно решать оочень многие вопросы (выгруженные из 1С)

Ещё раз, извините.
С уважением,
Ольга
24. Денис (Den_D) 01.08.13 14:05
(23) Hellgga, конечно не грех. Но согласитесь, в 1с множество вопросов, которые следует изучать и как это обычно бывает мы изучаем то, что нам нужно для решения текущих задач, придумывать себе мифические задачи и изучать их решение достаточно трудоемкая задача при большой текущей нагрузке.
Свободного времени всегда не хватает. Шутка: Раз уж мы владеем 1С не грех и научится плавать на собственной яхте ))
25. Hellgga 01.08.13 16:21
:)
Эт точно. Для тех, кто владеет :)
26. Борис Скворцов (gaglo) 02.08.13 10:08
(22) и (24) - Похвастун! Это 1С владеет вами! (Если свободного времени не хватает "всегда" - задумайтесь!)
Если серьезно, то "можно было сделать в среде VBA" значит именно то, что написано.
Открыть Excel, в нем редактор VBA, написать процедуры, вытащить на лист рабочей книги кнопку и посадить на нее вызов этой процедуры...
Только вместо
Процедура СкопироватьЛистЭксельВКонец(Эксель, ЛистИсточник, ЛистПриемник, КоличествоСтрокОтступа, ЛистФормата = Ложь)

было бы
Sub CopyExcelSheetToEnd(SheetFrom, SheetTo, NumIndentRows, SheetFormat)...
а вместо
Эксель.Selection.Insert(-4121);

что-то вроде
ActiveSheet.Selection.Insert(xlShiftDown)
(примерно, конечно)
27. Ирина Симоненко (Klyacksa) 02.08.13 11:19
Чего все так накинулись на то, что автор склеивала файлы через 1С, а не непосредственно из VBA? Нормальная задача, тем более что дальше напрашивается расширение задачи - если нужно "клеить" файлы постоянно, в 1С делаем регламентную задачу с расписанием - и забываем даже про "нажатие" кнопочки в обработке. Ну конечно, при условии клиент-серверного варианта базы и возможности хоть как-то составить расписание (например, раз в день, или раз в неделю).

(15) Den_D, я имела ввиду копию конечно, да... сами исходные файлы только на чтение, чтобы ни в коем случае их не повредить - хороший тон.
28. Алексей Голосеев (Aleksey81) 02.08.13 11:54
На самом деле интересен феномен, что такая простая разработка так оказалась востребована (25 плюсов спустя 4 дня). Можно сколько угодно ругать автора, но количество плюсов делает все эти нападки бессмысленными. Думаю польза от этой публикации в том что она дала легкий старт тем кто хотел написать более сложные алгоритмы на том же 1С механизме.
Возможно авторы правы, что надо знать VBA, но тут сразу возникает вопрос:
Если менее 5% твоей работы касается Экселевских документов - следует:
а) Профессионально изучать VBA и использовать его
б) Разобраться в азах VBA, сделать работу на VBA и потом долго исправлять косяки и обидные ошибки
в) Реализовать все на той платформе, где ты себя чувствуешь рыбой в воде, то есть 1С.
Вопрос не однозначный и можно бесконечно спорить по его решению. Но каждое решение имеет право на жизнь.
29. Денис (Den_D) 02.08.13 14:45
(26) gaglo, да, это здорово, но не в контексте решаемой задачи.
Если еще чуть подробнее, то показ инструкции на посте происходит по прибытии продукции на пост каждые 7 минут, происходит сканирование серийного номера, в зависимости от модели продукции инструкция выдается работнику, это же событие фиксируется в системе, для последующего анализа времени нахождения продукции на отдельных постах, линиях, участках. Возможно автоматическое создание документов. Поэтому логичнее все действия выполнять при помощи одной системы, в данном случае на базе 1С. Никто не нажимает никаких кнопок в эксель, только сканирование.
(28) Aleksey81, Спасибо, все верно, зачем тратить время на изучение не нужных вещей.
(27) Klyacksa, в моей задаче получается что результирующий файл не нужен на диске, он нужен в момент сканирования устройства и формируется на основании модели. Вот почему лишняя запись файла на диск была просто не нужна.
30. Ирина Симоненко (Klyacksa) 02.08.13 15:22
(29) Den_D, вот тут не поняла. Что имеется ввиду под "лишней записи файла на диск"?
Ваше решение: берем пустой файл, копируем туда колонки из первого файла, и дальше дописываем из остальных файлов.
Мое предложение: берем первый файл, копируем его целиком (при этом не обязательно его куда-то записывать!), и дальше дописываем из остальных файлов.

Итого разница - "бегаем" по первому файлу, или копируем его системными средствами, вот и вся разница. Никакой доп.записи.
31. Денис (Den_D) 02.08.13 15:41
(30) Klyacksa,
берем первый файл, копируем его целиком (при этом не обязательно его куда-то записывать!)

а это как? если мы его копируем, разве это не значит, что мы его копию записываем на диск?
32. Ирина Симоненко (Klyacksa) 02.08.13 16:05
(31) Den_D, да, действительно, создавать новый файл все-равно придется.
Но копировать-то можно сразу весь лист первого файла целиком:

	Excel = Новый COMОбъект("Excel.Application");
	Книга = Excel.WorkBooks.Add();
	КнигаТемп = Excel.WorkBooks.Open("D:\temp\aaa.xls");
	
	ЛистШаблона = КнигаТемп.WorkSheets(1); 
	ЛистШаблона.Copy(Книга.Sheets(1));
...Показать Скрыть
33. Денис (Den_D) 02.08.13 17:06
Если скопировать первый файлиз списка файлов, то первый лист можно не копировать, а сразу лепить к нему все оставшиеся. Потом надо за собой убрать и удалить эту копию. Тут я с вами согласен, такой вариант тоже возможен, а можно просто скопировать формат ))
34. Борис Скворцов (gaglo) 04.08.13 14:13
(27) Накинулись, накинулись... Вот я объясню, почему я "накинулся". Если бы в публикации сразу было написано, что инструкцию надо собирать и показывать каждые 7 минут, да по-разному, в зависимости от текущего изделия - я бы и не вспоминал про "чистый VBA", потому что он тут действительно не к месту. А пока формулировки типа "мне поставили задачу, я ее решаю" - задачу-то не раскрывали полностью - пока все и удивлялись, почему это прямо в 1С надо делать...
35. Борис Скворцов (gaglo) 04.08.13 14:41
(28) Дело в том, что 24 этих скачивания у меня лично вызывают подозрения, что сделаны на всякий случай людьми, которые читают только свежие публикации, а поиском по Инфостарту почему-то не пользуются. Потому как тут за прошлые годы ну очень много насчет взаимодействия с Excel накопилось...
А ещё: "знать VBA" - если точнее, сам язык VBA достаточно прост, чтоб сказать, что там и знать-то нечего. А вот познать объектную модель документа Excel (вот эти вот Range, Cells, Activate) приходится независимо от того, на каком языке решаем мы нашу нелегкую задачу, уж если связались с файлами Excel вообще. Я лично понял, что именно познание этих методов и объектов сравнивал автор с "хождением по граблям".
А VBA знать не надо, так и договоримся.
Den_D; Aleksey81; +2 Ответить
36. Денис (xrrg) 15.12.15 12:31
к вящей славе автора и к моей потере времени поисковик на тематический запрос выводит на эту статью. простое копирование кода не взлетело почему-то. мож потому что на сервере. не было смысла разбираться, тк можно чуток проще - так, как советуют "знатоки vba".

содержимое TempSheet одной книги добавляется в SheetDest другой. сохранение формата достигается в моем случае макетами СКД и условным оформлением.

xlCellTypeLastCell = 11;

MaxRow = TempSheet.Cells.SpecialCells(xlCellTypeLastCell).Row;
Diapason = TempSheet.Rows("1:" + Формат(MaxRow, "ЧГ=0"));

SheetDest = Book.ActiveSheet;
MaxRowDest = SheetDest.Cells.SpecialCells(xlCellTypeLastCell).Row;
DiapasonDest = SheetDest.Rows(Формат(MaxRowDest + 1, "ЧГ=0") + ":" + Формат(MaxRowDest + 1 + MaxRow, "ЧГ=0"));

Diapason.Copy(DiapasonDest);
...Показать Скрыть


активизировать листы необязательно, можно просто присвоить sheetdest = book.sheets(1);
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа