Написать функцию, которая копирует все содержимое из одного листа Excel (.xltm) в другой.
Excel 2010.
Дано: файл .xltm внутри >100 листов с формулами и макросами.
Требуется:
1. написать обработку (можно с толстой формой) или по крайней мере просто функцию, которая бы копировала ВСЕ содержимое ячеек с УКАЗАННОГО листа на НОВЫЙ лист (лист желательно создать новый программно). Копироваться должно все в т.ч. и формулы.
2. создать функцию, которая бы осуществляла поиск и замену значений внутри ВСЕХ формул выбранного листа по типу как работает СтрЗаменить(...).
Первый, кто напишет, получит или $m или рубли, кому как удобно (пишите договоримся).
В общем-то если программист поставил себе задачу, то это не с проста.
Возможно требуется не точная копия и при копировании необходимо внести какие-то изменения.
Мне всегда интересно практическое применение.
Можно сделать отчет, который формирует результат в виде файла Excel.
Так вот, у отчета может быть макет в виде внешнего файла, на основании которого формируется новый.
Но у такого отчета будет "тонкое место"
Если на компьютере не установлен Офис, то функция работать не будет
(1) RailMen, предлагаю идею - читать и копировать Excel средствами работы с XML
Т.к. файл xlsx представляет собой архив со вложенными в него файлами xml
то можно выдернуть нужный файл листа, скопировать его, изменить текстовые вхождения и т.д. и т.п
к примеру, файлы листов можно найти внутри xlsx по пути \xl\worksheets\sheet1.xml и далее по номерам
на сайте был даже некий подобный пример http://infostart.ru/public/205359/
(2) RailMen, а что мешает Вам в экселе начать запись макроса, потом выделить всю книгу, нажать "Копировать", перейти в следующий лист и нажать "Вставить", а потом посмотреть, какие команды эксель запишет?
Получился код для авто копирования содержимого ИсходногоЛиста в другие листы, чей список имен задан строкой через запятую:
Для Каждого ТекСтр Из ПравилаЗаполненияЛистов Цикл
ЛистОбразцовый = КнигаExcel.Sheets(ТекСтр.ИмяИсходного);
ЛистОбразцовый.Select();
ЛистОбразцовый.Cells.Select();
ЛистОбразцовый.Selection.Copy(); //ОШИБКА: Метод объекта не обнаружен (Selection)
МассивИменЗаполняемыхЛистов = РазложитьСтрокуВМассивПодстрок(ТекСтр.ИменаЗаполняемых);
Для Каждого ИмяЛиста Из МассивИменЗаполняемыхЛистов Цикл
ЛистЗаполняемый = КнигаExcel.Sheets(ИмяЛиста);
ЛистЗаполняемый.Select();
//1. все очищаем
ЛистЗаполняемый.Cells.Clear();
//2. копируем из памяти
ЛистЗаполняемый.Select();
ЛистЗаполняемый.Cells.Select();
ЛистЗаполняемый.ActiveSheet.Paste();
КонецЦикла;
Показать
Пишет ошибку: Метод объекта не обнаружен (Selection)
Чего делать?
если вы копируете только один лист - разве нужно заменять формулы? формулы должны быть точно такими же, если есть ссылка на другие книги, листы, они ведь тоже должны остаться прежними? так?
Процедура ВыполнитьЗаполнениеЛистов() Экспорт
Для Каждого ТекСтр Из ПравилаЗаполненияЛистов Цикл
ЛистОбразцовый = КнигаExcel.Sheets(ТекСтр.ИмяИсходного);
ЛистОбразцовый.Select();
ЛистОбразцовый.Cells.Select();
Excel.Application.Selection.Copy();
МассивИменЗаполняемыхЛистов = РазложитьСтрокуВМассивПодстрок(ТекСтр.ИменаЗаполняемых);
Для Каждого ИмяЛиста Из МассивИменЗаполняемыхЛистов Цикл
ЛистЗаполняемый = КнигаExcel.Sheets(ИмяЛиста);
ЛистЗаполняемый.Select();
//копируем из памяти (все форматирование заполняемого листа и его содержание удаляются автоматом)
ЛистЗаполняемый.Cells.Select();
ЛистЗаполняемый.Paste();
КонецЦикла;
КонецЦикла;
Excel.ActiveWorkbook.Save();
КонецПроцедуры
Важное дополнение: написанный выше код не работает в файле *.xltm (с макросами) и приходится его сначала сохранять в обычный *.xlsx, только потом обрабатывать кодом. Почему то код не отрабатывает в файле *.xltm (с макросами) - просто висит как будто что-то делает, но ничего не копирует в итоге.
Короче под *.xltm (с макросами) не работает. Почему?
Может из-за политики безопасности? Хмхмх
Нет, даже включенным автозапуском макросов формат *.xltm (с макросами) не поддается коду 1С.
А вот обычный *.xlsx работает.
ПравилаЗаменыВЛисте таблица с колонками:
ИмяЛиста
ЧтоМеняем - возвожны варианты "Value", "FormulaLocal"
СтрокаПоиска
СтрокаЗамены
ИменаЯчеекДляЗамены - Имена ячеек для замены через запятую "1,A; 2,B" (если не указано, то менять во всем листе)
СписокЛистовExcel - список имен листов книги Excel, задается после определения книги
Процедура АвтоЗаменаФормул() Экспорт
Для Каждого ТекСтр Из ПравилаЗаменыВЛисте Цикл
Если ПустаяСтрока(ТекСтр.СтрокаЗамены) или ПустаяСтрока(ТекСтр.СтрокаПоиска) Тогда Продолжить; КонецЕсли;
Если СписокЛистовExcel.НайтиПоЗначению(ТекСтр.ИмяЛиста)=Неопределено Тогда Продолжить; КонецЕсли;
Лист = КнигаExcel.Sheets(ТекСтр.ИмяЛиста);
Лист.Select();
//Просто меняем значение в ячейке
Если НЕ ПустаяСтрока(ТекСтр.ИменаЯчеекДляЗамены) Тогда
МассивИменЯчеек = РазложитьСтрокуВМассивПодстрок(ТекСтр.ИменаЯчеекДляЗамены, ";");
Для Каждого ИмяЯчейки Из МассивИменЯчеек Цикл
МассивАдреса = РазложитьСтрокуВМассивПодстрок(ИмяЯчейки);
Ячейка = Лист.Cells(МассивАдреса[0],МассивАдреса[1]);
Если ТекСтр.ЧтоМеняем = ЗаменяемыеТипыЯчеек[0].Значение Тогда
Ячейка.Value = СтрЗаменить(Ячейка.Value,ТекСтр.СтрокаПоиска,ТекСтр.СтрокаЗамены);
ИначеЕсли ТекСтр.ЧтоМеняем = ЗаменяемыеТипыЯчеек[1].Значение Тогда
Ячейка.FormulaLocal = СтрЗаменить(Ячейка.FormulaLocal,ТекСтр.СтрокаПоиска,ТекСтр.СтрокаЗамены);
КонецЕсли;
КонецЦикла;
//Поиск и замена значений через Replace
Иначе
Лист.Cells.Replace(ТекСтр.СтрокаПоиска, ТекСтр.СтрокаЗамены);
КонецЕсли;
Состояние("Строка №"+ТекСтр.НомерСтроки);
ОбработкаПрерыванияПользователя();
КонецЦикла;
//Сохранимся
Excel.ActiveWorkbook.Save();
КонецПроцедуры
(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
...
Нужно автоматически продолжить формулы в колонке.
Понятно, что нужно сначала как то распарсить формулу на составляющие. И т.д.
ЛистОбразцовый.Copy() или ЛистОбразцовый.Cells.Copy() пробовали?
Если я не ошибаюсь, в экселе есть отладчик, пробовали сделать точку останова (breakpoint) и в его аналоге "табло" посмотреть доступные свойства ЛистОбразцовый?
Слишком давно такое делал, примеров не сохранилось. Общая идея была копировать бланк отчета со скрытого листа.
Файл-приемник при создании будет иметь права доступа, аналогичные назначаемым при создании новых файлов (т.е. права доступа к файлу-источнику не копируются).
Допускается использование схем http, https и ftp для адресации файлов. При использовании этих схем в адресах необходимо указывать прямые слеши '/', а не обратные '\'.
Для работы метода на веб-клиенте необходимо предварительно подключить расширение работы с файлами.
На веб-клиенте метод работает только с локальными файлами.
Пример:
Общий принцип - записывается макрос, изучается хелп к командам, код переписывается без использования 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?
или правый клик на ярлыке листа, скопировать\вставить, в новую книгу.
Можно выделить все ярлыки с шифтом и скопировать разом
Лет 10 назад я писал эксельные макросы, которые делали запрос к корпоративной базе SQL, копировали скрытый лист с шаблоном печатной формы, на котором в области "табличной части" была всего одна строка с формулалми, затем макрос набивал строки и "размножал" формулы командой, аналогичной двойному щелчку на маркер заполнения, затем копировал ячейки с формулами и вставлял их сами в себя как значения - что экономило время на переписывании кода.
Посмотрите "Перевод кода макроса в код для 1С" http://infostart.ru/public/64421/ , и там же как запустить макрос, вшитый в документ, может это то, что Вам нужно.
Это нормальный процесс.
Оценки состояния заинтересованности очень субъективны.
Вполне возможно, что результаты данной ветки воплотятся в некий программный продукт или пополнят личную библиотеку.
Всё может быть, но за пределами данной страницы.
И если смотреть с точки зрения оценки потребности данной ветки, то интерес автора к ней был утрачен.
По этой причине и был мой разъясняющий пост.
Но если у вас возникли новые интересные темы для обсуждения, буду рад поучаствовать.