1с excel скопировать лист в другую книгу
Пользователи часто просят сохранить им стопиццот отчетов в один файл Excel, чтобы отчеты были в одном файле. Ну или клиент хочет счет, накладную и счет-фактуру получить в одном документе. 1С умеет это делать без всяких там COM-объектов. Давайте разберемся, куда нажимать и что крутить.
Сохранение нескольких табличных документов в одну книгу Excel
ВВЕДЕНИЕ
В 1С миллион лет назад появилась возможность сохранять табличный документ в Excel. А с версии 8.3.какой-то там еще и читать Excel в этот самый табличный документ. При этом прочитать 1С может документ с произвольным количеством закладок (книг), а вот если записать табличный документ обратно, то все содержимое будет помещено в лист "TDSheet" (ну или как-то так).
Меня давно мучила мысль о том, почему 1С не может записать несколько листов в одну книгу. Оказалось, что может. Давайте разберемся, каким образом она это делает.
НОВЫЕ СТАРЫЕ ОБЪЕКТЫ
Уже давно в 1С существует такой объект, как "ПакетОтображаемыхДокументов". Если зайти в синтаксис-помощник, то толку от этого будет мало - скудность информации зашкаливает.
Что же делает этот объект? Он позволяет записать несколько файлов в один. Как оказалось, среди типов записываемых документов есть и Excel.
Познакомился я с этим замечательным объектом при осуществлении поддержки механизма автоматической печати документов, который прекрасно жил на моей прошлой работе до часа Х. Час Х настал тогда, когда сохраняемые 1С PDF перестали сохраняться в том виде, в котором они должны были бы быть напечатаны. До какого-то времени PDF из табличного документа сохранялись таким образом, что количество экземпляров для печати становилось количеством повторов в PDF-файле в соответствии с настройками печати конкретного табличного документа. И вот пришел час Х - обновили платформу и автопечать выплюнула все документы в одном экземпляре. Этакая подстава от 1С (кстати, последняя подстава от желтых была в последней версии, и связана она с тем, что теперь нельзя редактировать строку табличной части, если выделено более одной строки, а кое-кто пользовался редактированием выделенных строк для того, чтобы установить всем выделенным строкам отредактированное значение - это было очень удобно, спасибо что лишили нас этого), которую совсем не ждешь. Стали разбираться и нашли в документации, что теперь для сохранения PDF в том виде, в котором должен быть напечатан документ, следует использовать новый метод объекта "ПакетОтображаемыхДокументов", который пишет как раз PDF-файл (и никакой другой).
Вообще, на просторах желтого сайта есть пример использования данного объекта. Смотрите здесь. Давайте разберемся, как это поможет нам сохранить несколько табличных документов в одну книгу.
ОПИСАНИЕ ОБЪЕКТА
Сам по себе пакет отображаемых документов по большому состоит из коллекции "Состав", имеющей тип "ЭлементПакетаОтображаемыхДокументов", в который, как следует из информации по ссылке выше, можно поместить в том числе и пакет отображаемых документов. Т.е. возможности коллекции достаточно большие - можно поместить в нее несколько пакетов, для которых количество печатаемых экземпляров будет отличаться, в итоге можно напечатать 10 экземпляров для первого документа, и 3 для второго. Также у этого объекта есть парочка методов сохранения - для PDF и для всего на свете. Ну и их экземпляры для асинхронной работы тоже наличествуют.
Для создания объекта существует простой конструктор:
Создав объект "ОднаКнига", мы можем в его состав поместить несколько табличных документов каким-то таким образом:
А сохранить в одну книгу все это хозяйство можно так:
ЗАКЛЮЧЕНИЕ
Данный метод работает в Linux - у меня на скриншоте как раз конфигуратор, открытый на Ubuntu 18.4, в котором я и отлаживал тестовый примерчик. В итоге получилась замечательная обработка, которая создает столько закладок, сколько вам нужно, открывает в закладке файл Excel и сохраняет файлы со всех закладок в одну книгу. Реализовать данную обработку вы можете в качестве домашнего задания.
Excel файл как Внешний источник данных 16
Подключимся и загрузим из файла Excel данные в таблицу значений 1С. Сделать теперь это очень просто. 1. В конфигурации добавляет новый объект метаданных типа " Внешние источники данных" и назовем его просто " Excel" . https://helpf.pro/uploads/img Активация открытой книги Excel для корректировки 0
Нужно было получить для изменения уже открытую книгу Excel, а если она закрыта, то открыть и изменять. Ниже код, который данную задачу решает. Попытка Эксель = Неопределено; Книга = Неопределено; Лист = Неопределено; Состояние(" Выполняется Быстрая загрузка данных большого размера файла Excel в многомерный Массив 0
// OldthiefXXX Перем СерверExcel; Процедура КнопкаВыполнитьНажатие(Кнопка) ФайлХЛС=СерверExcel.Application.Workbooks.Open(ДокументХЛС); КонечнаяСтрокаДанных=ФайлХЛС.ActiveSheet.UsedRange.Rows.Count; КоличествоКолонокДанных=ФайлХЛС.ActiveSheet Быстрая функция чтения данных с листа Excel 9
При чтении файла Excel я использую вариантный массив. Он позволяет быстро получить ВСЮ таблицу листа в память, а также получать данные массива целыми колонками. Тем самым время на чтение области файла Excel в таблицу значений сокращается в десятки ра Вставить формулу, функцию в ячейку Excel 0
Ячейка = Лист.Cells(1,8); Ячейка.Formula=" =SUM(R11C:R C)" ; Процедура Сформировать() Перем Кат,Шаб; Ехсел=СоздатьОбъект(" Excel.Application" ); Книга=Ехсел.Workbooks; Если ФС.ВыбратьФайл(0,Кат,Шаб," Выбери файл Еxcel" ,," *.xls" ,)=1 Тогда Посмотреть все результаты поиска похожих
Еще в этой же категории
Как в excel сохраненный из 1С вставить новый лист? 15
Я думаю, все сталкивались с однолистовым excel файлом из 1С, у которого на первый взгляд, нет возможности добавить листы. Все не так страшно - их просто скрыли или, что чаще всего - даже не отображали (обусловлено созданием файла из стороннего прил Как сформировать документ в Word из 1С? (Active Document) 14
ВНИМАНИЕ! При использовании кодов описанных ниже, уберите символ _ Давайте рассмотрим на примерах как это делается! =) Мне известно 2 способа: 1. Путем замены шаблонного текста в макете Wordа. 2. Путем вставки текста с помощью закладок в Wo Экспорт и Импорт данных 1С - Microsoft Excel 12
Экспорт Процедура ОсновныеДействияФормыЭкспорт(Кнопка) - выгружает из указанного справочника данные в режиме экспорта в файл в формате Microsoft Excel с указанным именем, используя преобразование данных к типу число и типу строка; Процедура Основ Преобразование XLS в MXL 10
Конвертация таблиц из Excel в MXL - скачать Converter Excel v MXL: Для 8.х - Для 7.7 - // Выбор файла Microsoft Excel Procedure FileNameStartChoice(Element, StdProcessing) StdProcessing = False; DialogFile = New FileDialog(FileDialogMode. Работа с MS Word через OLE 10
В качестве примера использования можно указать вот такую ситуацию: в зависимости от типа клиента (договора, документа и т.п) формируются разные тексты договоров с разным количеством таблиц. В реальности, конечно должены использоваться и макет и опис Посмотреть все в категории Работа с Microsoft Office и OpenOffice
Добрый день! Подскажите, как программно в 1С, работая с объектами Excel.Application и соответственно с производными объектами книга, листы в книге, скопировать лист из одной книги в другую?
Скопировать один лист из одной книги excel в другую книгу excel
Мне нужно скопировать 1 лист из одной книги excel в другую книгу excel. Именно лист, а не диапазон.
Как скопировать лист Excel из одной книги в другую
Подскажите, пожалуйста, как скопировать целиком лист из одной книги в другую со всеми разделителями.
Как скопировать активный лист с одной книги в определёную другую под заданым именем?
ПОМОГИТЕ ПОЖАЛУСТА: скопировать активный лист с одной книги в определёную другую под заданым.
Скопировать диапазон ячеек одной книги Excel в другую книгу Excel
Из темы все ясно. Не могу разобраться никак. Можно ли открыть одну книгу, скопировать диапазон.
сбой в Excel 2007 при попытке скопировать листы из одной книги в другую
Доброго всем времени суток. Вопрос в следующем: почему может не работать стандартная функция.
Загрузить лист из одной книги в другую
Как загрузить лист из одной книги (Например 111.xls) в другую книгу вложенного файла на место Листа.
Как скопировать данные из одной книги в другую?
Нужно скопировать данные из книги списки студентов, в книгу итоги успеваемости. А именно из книги.
Как макросом скопировать ф-ию из одной книги в другую?
Есть книга - шаблон, из нее макросом копирую выбранный лист в новую книгу. Кроме того из книги.
Программно скопировать данные из одной базы в другую
Всем доброго дня! Проблема в следующем. Есть две базы 1С8.2 на сервере, каждой соответствует своя.
Написать функцию, которая копирует все содержимое из одного листа Excel (.xltm) в другой.
Excel 2010.
Дано: файл .xltm внутри >100 листов с формулами и макросами.
Требуется:
1. написать обработку (можно с толстой формой) или по крайней мере просто функцию, которая бы копировала ВСЕ содержимое ячеек с УКАЗАННОГО листа на НОВЫЙ лист (лист желательно создать новый программно). Копироваться должно все в т.ч. и формулы.
2. создать функцию, которая бы осуществляла поиск и замену значений внутри ВСЕХ формул выбранного листа по типу как работает СтрЗаменить(. ).
Первый, кто напишет, получит или $m или рубли, кому как удобно (пишите договоримся).
(1) Функции получились рабочие.
Только вот остаётся один вопрос: Какой практический смысл вы вкладываете в них?
Ещё надо учитывать, что Если на компьютере не установлен Офис, то функция работать не будет.
Пока читал думал имеено об этом, работать с Excel из 1С то еще колдунство, если можно обойтись лучше так и сделать.
В общем-то если программист поставил себе задачу, то это не с проста.
Возможно требуется не точная копия и при копировании необходимо внести какие-то изменения.
Мне всегда интересно практическое применение.
Можно сделать отчет, который формирует результат в виде файла Excel.
Так вот, у отчета может быть макет в виде внешнего файла, на основании которого формируется новый.
Но у такого отчета будет "тонкое место"
Проще не копировать содержимое а скопировать сам лист.
Если на компьютере не установлен Офис, то работать не будет
(2) RailMen, а что мешает Вам в экселе начать запись макроса, потом выделить всю книгу, нажать "Копировать", перейти в следующий лист и нажать "Вставить", а потом посмотреть, какие команды эксель запишет?
Вот собственно, что записал макрос, когда я выделил все в листе "20" и скопировал содержимое в листы "21", "22", "23":
Сейчас сам тогда напишу в 1С, потестирую и отпишусь.
Получился код для авто копирования содержимого ИсходногоЛиста в другие листы, чей список имен задан строкой через запятую:
Пишет ошибку: Метод объекта не обнаружен (Selection)
Чего делать?
если вы копируете только один лист - разве нужно заменять формулы? формулы должны быть точно такими же, если есть ссылка на другие книги, листы, они ведь тоже должны остаться прежними? так?
(8) user_2010, там очень много ссылок и на другие листы и на все, что только может приснится в самом страшном сне 1Снега.
Я решил п.п. 1 смоими силами. Excel из под 1С шайтанство какое-то )))) Сейчас выложу обработину
Важное дополнение: написанный выше код не работает в файле *.xltm (с макросами) и приходится его сначала сохранять в обычный *.xlsx, только потом обрабатывать кодом. Почему то код не отрабатывает в файле *.xltm (с макросами) - просто висит как будто что-то делает, но ничего не копирует в итоге.
Короче под *.xltm (с макросами) не работает. Почему?
Может из-за политики безопасности? Хмхмх
Нет, даже включенным автозапуском макросов формат *.xltm (с макросами) не поддается коду 1С.
А вот обычный *.xlsx работает.
Решение 2 задачи:
ПравилаЗаменыВЛисте таблица с колонками:
ИмяЛиста
ЧтоМеняем - возвожны варианты "Value", "FormulaLocal"
СтрокаПоиска
СтрокаЗамены
ИменаЯчеекДляЗамены - Имена ячеек для замены через запятую "1,A; 2,B" (если не указано, то менять во всем листе)
СписокЛистовExcel - список имен листов книги Excel, задается после определения книги
(12) RailMen, Не пойму зачем менять формулы? при копировании листа формулы остаются теми же. Возможно изменение только ссылок на листы или книги - если вы копируете несколько листов или книг. Я не права?
(12) RailMen, по моему код будет выполняться относительно долго. 1 хинт. если менять значения, то гораздо быстрее сформировать массив или ТЗ значений (загрузить через ADO, обработать) , выгрузить через Табличный документ в excel , затем прочитать через ADO в RecordSet и загрузить в исходную колонку или диапазон через CopyFromRecordset . второй хинт. по формулам. вряд ли формулы все разные, есть некая единая формула по колонке или диапазону. ну тогда надо задать ее Cells[граница1, граница2]).Formula = строковоепредставлениеформулы. (по формулам можно также отключить ScreenUpdating , потом включить ,но я думаю это всем известно)
(28) cool.vlad4, копировал лист 100 раз как написал выше: выходит не более минуты это не критично.
На каждом листе МНОГО таблиц, каждая имеет свою логику вычисления ячеек. Приходится упорно вычленять области, где можно отследить зависимости и их менять программно в созданных листах. Адская работа.
Теперь стоит другая задача: по заданной области (к примеру, столбец А1:А99) определить зависимость находящихся внутри формул и автоматически сгенерировать новые формулы на новом диапазоне А100:А199. Пример формул, которые надо продолжить вниз (пробелы вставил для облегчения понимания) :
=I2 * '1'!B289 * '1'!$G$1
=I3 * '1'!B290 * '1'!$G$1
=I4 * '1'!B291 * '1'!$G$1
=I5 * '2'!B289 * '2'!$G$1
=I6 * '2'!B290 * '2'!$G$1
=I7 * '2'!B291 * '2'!$G$1
=I8 * '3'!B289 * '3'!$G$1
=I9 * '3'!B290 * '3'!$G$1
=I10 * '3'!B291 *'3'!$G$1
.
Нужно автоматически продолжить формулы в колонке.
Понятно, что нужно сначала как то распарсить формулу на составляющие. И т.д.
1С-ник эгоист :) Сам поставил задачу, назначил вознаграждение, решил, выдал себе вознаграждение. И это пока все спали :)))
ЛистОбразцовый.Copy() или ЛистОбразцовый.Cells.Copy() пробовали?
Если я не ошибаюсь, в экселе есть отладчик, пробовали сделать точку останова (breakpoint) и в его аналоге "табло" посмотреть доступные свойства ЛистОбразцовый?
Слишком давно такое делал, примеров не сохранилось. Общая идея была копировать бланк отчета со скрытого листа.
Если содержимое одно и тоже то можно так:КопироватьФайл (FileCopy)
Синтаксис:
Тип: Строка.
Полное имя файла-источника.
(обязательный)
Тип: Строка.
Полное имя файла-приемника.
Описание:
Копирует файл-источник в файл-приемник. Допускается перезапись файла-приемника.
Тонкий клиент, веб-клиент, сервер, толстый клиент, внешнее соединение, мобильное приложение(клиент), мобильное приложение(сервер).
Примечание:
КопироватьФайл("C:\Temp\Order.htm", "C:\My Documents\Order.htm");
До кучи, чтобы не пропадал полезный опыт:
Общий принцип - записывается макрос, изучается хелп к командам, код переписывается без использования Selection. Бланки и шаблоны удобно прятать на скрытые листы, через Range удобно ссылаться на именованные ячейки - Range("Курс"), параметры удобно выносить либо на отдельный лист, либо фиксировать область шапки и размещать в ней. Параметры печати "по ширине листа" в экселе - 1 лист в ширину, 999 в высоту.
(33) Очень верное замечание.
А ещё При составлении таблиц Excel нужно руководствоваться следующими принципами:
1. Нельзя пропускать пустые столбцы, то есть, если столбец «Наименование» имеет адрес «B», то столбец «Количество» должен иметь адрес «C». Если название столбца длиннее чем ширина столбца, то его можно растянуть или воспользоваться вкладкой выравнивание в диалоговом окне «Формат ячейки» и установить флажок «Переносить по словам».
2. Желательно, не оставлять пустых строк в таблице, без необходимости.
3. Заголовок столбца нужно писать в одной ячейке.
При выполнении данного задания использовать значок «Объединить и поместить в центре» для центрирования названий таблиц. Значения «Итого» в таблицах должны рассчитываться с помощью значка суммы , а значения в столбце «Сумма» во второй таблицы должны рассчитываться с помощью формул.
Для расположения текста внутри ячейки в несколько строк можно воспользоваться вкладкой «Выравнивание» в диалоговом окне «Формат» ячейки и отметить там пункт «Переносить по словам» или нажать в ячейке комбинацию клавиш «Alt + Enter».
4. Не объединяйте ячейки в над, под и внутри табличных данных!
Формулы с участием объединенных ячеек не гарантируют правильности результата.
Ячейки, объединенные в шапке будут мешать сортировке таблицы.
5. В функциях ВПР и ГПР в качестве диапазона поиска удобнее использовать столбцы/строки целиком.
(37) Очень верно!
Но главное - надо помнить, что если зажать на клавиатуре клавишу «Ctrl» и,не отпуская её, нажать клавишу «S», а далее,выбрать папку «Папка:», например «D:\», указать «Имя файла:», нажать «Сохранить», то файл будет защищён от исчезания при отключении питания у компьютера.
И ещё.
Разница между «Сохранить» или «Сохранить как» следующая:
«Сохранить» - перезаписывает уже сохранённый 1 или 2 способом файл.
«Сохранить как» - предлагает сохранить данные в новый файл.
зачем вы тгавите? Это чем то отличается от ctrl+A \ ctr+V?
или правый клик на ярлыке листа, скопировать\вставить, в новую книгу.
Можно выделить все ярлыки с шифтом и скопировать разом
Стандартная функция поиска замены в Excel тоже работает.
(41) adapter, суть не в простом копировании листа. Нужно скопировать лист с формулами и "на лету" все формулы заменить по смыслу и по аналогии.
Лет 10 назад я писал эксельные макросы, которые делали запрос к корпоративной базе SQL, копировали скрытый лист с шаблоном печатной формы, на котором в области "табличной части" была всего одна строка с формулалми, затем макрос набивал строки и "размножал" формулы командой, аналогичной двойному щелчку на маркер заполнения, затем копировал ячейки с формулами и вставлял их сами в себя как значения - что экономило время на переписывании кода.
Хотя, RailMen уже потерял интерес к вопросу и всё решил самостоятельно в (7)
Это нормальный процесс.
Оценки состояния заинтересованности очень субъективны.
Вполне возможно, что результаты данной ветки воплотятся в некий программный продукт или пополнят личную библиотеку.
Всё может быть, но за пределами данной страницы.
И если смотреть с точки зрения оценки потребности данной ветки, то интерес автора к ней был утрачен.
По этой причине и был мой разъясняющий пост.
Но если у вас возникли новые интересные темы для обсуждения, буду рад поучаствовать.
После манипуляции с файлом необходимо его закрыть. В противном случае он останется захваченным в системе, и мы не сможем его использовать в других приложениях.
Клиент-Серверный вариант в управляемых формах.
1. Считываем данные из файла в массив из структур и затем этот массив передаем на сервер для дальнейших манипуляций с данными.
2-ой способ - это передать файл Excel на сервер через хранилище данных и далее работать с ним уже на сервере.
Очень часто приходится работать с очень большими файлами Excel, и его обработка путем перебора строк занимает огромное количество времени.
В таких случаех удобно в одно действие загрузить всю таблицу в массив и в потом работать уже непосредственно с массивом.
На выходе получаем двумерный массив, который содержит все данные указанного листа Excel
Полезные функции при работе с Excel:
Устанавливает видимость Excel при работе |
Добавление новой книги в файл Excel |
Сохранение книги Excel |
Добавление нового листа к книге |
Переименование листа |
Изменение маштаба листа |
Изменение ориентации листа |
Отступы листа |
Обращение к ячейки чтение/запись |
Обращение к области ячеек |
Очень часто при чтении или записи значений в Excel ставятся лишние пробелы в числе, например, вместо 1502 он читает как 1 502 и в дальнейшем это значение не приводится к числу. Эту проблему можно решить заменой. |
Объединение ячеек |
Работа с именованными ячейками в Excel |
Удаление ячейки |
Удаление области ячеек |
Обращение к строке |
Изменение ширины колонки |
Обращение к колонке |
Удаление Строки |
Фон ячейки / области / Строки / |
Функция переводит цвет из формата RGB в формат Excel |
Управление шрифтом в ячейки/строке/области |
Р азрешает переносить по словам в ячейке |
Управление рамкой ячейки |
Устанавливаем формат ячейки |
Формула в ячейки |
Формула в ячейки |
Формула в ячейки |
Формула в ячейки |
Функция для получения ширины колонки Excel Спасибо пользователю goodwill |
Разрешить перенос слов в ячейке Спасибо пользователю roofless |
Группировки данных на листе Спасибо пользователю dr-wit, ignor |
При работе с Excel мы оперируем столбцами как числом (Например, 1 столбец), а у Excel адресация столбцов производится с помощью символов. И когда нам нужно отредактировать формулу, то нам нужно номер столбца преобразовать в символ. В таких случаях вам пригодится эта функция.
2. Работа с Excel через ТабличныйДокумент 1С
С помощью данного метода можно и загружать из Excel и выгружать в Excel. Но на мой взгляд этот метод идепально подходит когда вам необходимо посто сохранить информацию в Excel без дальнейшей манипуляции.
Итак, приступим: загрузка из Excel:
1. Загружаем файл Excel в табличный документ
2. Производим манипуляции уже с ТабличнымДокументом
Давайте теперь разберем сохранение в Excel с помощью данного метода:
Тут все очень просто сначала мы формируем обычный Табличный документ и затем записываем его в Excel
2. Работа с Excel ADODB
Выражаю особую благодарность коллеги Fragster за хороший комментарий
Данный метод позволяет работать с Excel через ODBC и имеет ряд преимуществ:
- Не требует установки самой Excel, необходима лишь установить ODBC. Но как правило он уже установлен. Это особенность позволяет работать на стороне сервера без дополнительных установок Excel.
- Позволяет работать с таблицой Excel как с БД и строить к ней запросы на T-SQL. Таким образом мы можем делать отборы еще на этапе чтения данных и другие преимущества что дает Т-SQL. Что на мой взгляд огромный плюс.
Подключение к ADO
Отключение от ADO
Выполнение запроса
Пример запроса:
Запись в Excel тоже производится в виде запроса:
Хочу отметить что наименование полей производится по первой строке в таблице
ADODB предоставляет ряд объектов, с которыми мы работаем
ADODB.Recordset
В файле продемонстрированны оба варианта работы с запросами.
На этом пока все. По возможности буду дополнять статью :)
В архиве находится обработка, которая демонстрирует все описанные и другие возможности при работе с Excel.
Читайте также: