Заполнение шаблона excel c
На практике часто случается, что нужно решить какую-либо задачу, иногда довольно тривиальную с точки зрения пользователя Excel (к примеру изменить фон ячейки в Excel, добавить левую границу и т.д.), а решение в сети/документации искать не хочется или не получается, тогда можно попробовать начать запись макроса, выполнить нужную последовательность действий (изменить фон, добавить границу) , после чего записать макрос и открыть код VBA для анализа кода, названий свойств и методов. Такой способ довольно часто помогает быстро решить проблему.
1. Подключение к Excel
При подключении к Excel не забываем закрывать приложение после работы дабы не наплодить кучу ненужных процессов. Они также могут появиться если во время выполнения модуля выскочит ошибка до того как система дойдет до строк, где происходит закрытие Excel.
2. Запись значения в ячейку
Количество листов книги
3. Шрифт и размер шрифта
4. Ширина колонки
Чтобы скрыть колонку, нужно установить ширину в значение 0.
Получение последней ячейки в используемом диапазоне
Например, при открытии таблицы нам нужно узнать до какой строки и/или до какого столбца присутствуют заполненные ячейки.
4. Установка фильтра
5. Горизонтальное выравнивание ячейки
Режим выравнивания
Константа в Excel
Значение в ISBL
По центру выделения
По правому краю
6. Вертикальное выравнивание ячейки
Режим выравнивания
Константа в Excel
Значение в ISBL
По нижнему краю
По верхнему краю
7. Установка свойства ячейки "переносить по словам"
8. Отключение/включение режима показа предупреждений
9. Формулы
Для формул с русскими названиями нужно использовать не Formula, а FormulaLocal. Таким образом правильными вариантами написания формул будут:
10. Таблица цветов с индексами
11. Установка границы ячейки и тип линии границы
Значения константы XlBordersIndex
Значение в Excel
Значение в ISBL
В виде тире и точек
В виде тире и двойных точек
В виде двойной линии
В виде наклонной пунктирной
Свойство LineStyle (тип линии) может принимать следующие значения
Расположение линии
Значение в Excel
Значение в ISBL
Линия по диагонали сверху – вниз
Линия по диагонали снизу – вверх
Линия, обрамляющая диапазон слева
Линия, обрамляющая диапазон сверху
Линия, обрамляющая диапазон снизу
Линия, обрамляющая диапазон справа
Все вертикальные линии внутри диапазона
Все горизонтальные линии внутри диапазона
Значения константы XlBordersIndex
| Расположение линии | Значение в Excel | Константа в ISBL |
|---|---|---|
| Линия по диагонали сверху вниз | xlDiagonalDown | 5 |
| Линия по диагонали снизу – вверх | xlDiagonalUp | 6 |
| Линия, обрамляющая диапазон слева | xlEdgeLeft | 7 |
| Линия, обрамляющая диапазон сверху | xlEdgeTop | 8 |
| Линия, обрамляющая диапазон снизу | xlEdgeBottom | 9 |
| Линия, обрамляющая диапазон справа | xlEdgeRight | 10 |
| Все вертикальные линии внутри диапазона | xlInsideVertical | 11 |
| Все горизонтальные линии внутри диапазона | xlInsideHorizontal | 12 |
Свойство LineStyle (тип линии) может принимать следующие значения:
| Тип линии | Значение в Excel | Константа в ISBL |
|---|---|---|
| Нет линии | xlLineStyleNone | 0 |
| Непрерывная | xlContinuous | 1 |
| В виде тире и точек | xlDashDot | 4 |
| В виде тире и двойных точек | xlDashDotDot | 5 |
| В виде точек | xlDot | 8 |
| В виде двойной линии | xlDouble | 9 |
| В виде наклонной пунктирной | xlSlantDashDot | 13 |
Получение порядкового номера колонки Excel по буквенному обозначению имени
Однажды столкнулся с тем, что нужно было получить буквенное обозначение колонки Excel по её порядковому номеру. Например, для колонки номер 3 буквенное обозначение равно "C". Так вот ниже приведены две замечательные функции, которые вполне справляются с решением данной проблемы.
Наоборот, получение буквенного обозначения имени колонки по номеру колонки
Есть некий двухэтажный документ, для которого нужно создать печатную форму, в верхней части которой указаны реквизиты шапки документа, а в нижней части - содержимое подчинённого уровня (например, список номенклатуры).
1) Из Конфигуратора запускаем FabiusClient "в режиме отладки".
Открываем (как для обычного ввода документов) нужный тип документа и тут же - на уровне "шапок" - нажимаем иконку Печать:
2) В окне открывшегося Менеджера печати нажимаем на "Создать форму с помощью мастера"
и заполняем свойства новой печатной формы, в том числе её Тип (шаблон Excel) и Наименование:
Кнопка Дальше (внизу) - переход к выбору хотя бы одного поля из "источника данных", которым является документ, для которого создаётся печатная форма:
Из _ОсновнойИсточник выбираем (галка) любое поле. Любое поле, так как мы собираемся использовать шаблон Excel, поэтому выбранное поле потом просто удалим из своей печатной формы. (Вот если бы мы собирались сформировать простейший отчёт типа набора нескольких колонок из текущего типа документа, нужно было бы осознанно пометить несколько нужных колонок.) Выбрать поле нужно, иначе не станет активной кнопка Дальше внизу.
Следующее окно "Формирование групп элементов отчёта" пропускаем кнопкой Дальше внизу.
В следующем окне - Шаблоны представления отчётов - помечаем единственный возможный вариант Табличное представление и кнопкой Дальше переходим на последнее окно, где нажимаем кнопку Финиш внизу.
Сформировать отчёт Да.
3) Открывается заготовка печатной формы, в которой в детальную полосу помещено наше "любое поле", выбранное выше. Переходим на закладку Макет:
Кликаем мышкой на голубую метку детальной полосы Detail, становится активной кнопка Сброс. Ею удаляем автоматически созданную детальную полосу.
(Детальную полосу мы встроим позднее в свой шаблон.)
Выделяем цветом все автоматические созданные строки печатной формы и удаляем их. Получаем пустую Excel-таблицу.
4) Загружаем свой шаблон - самая первая кнопка закладки Макет на предыдущем рисунке.
Выделяем все значащие ячейки шаблона и на закладке Разметка страницы фиксируем область печати:
5) Привязываем реквизиты первичного документа (_ОсновнойИсточник), перетаскивая их из окна Список полей на шаблон.
В том числе - при необходимости - привязываем к шаблону и реквизиты будущей детальной полосы, заполняя её реквизитами подчинённого уровня (например, списка номенклатуры):
В любом порядке
- перетаскиваем реквизиты списка номенклатуры на шаблон в строку детальной полосы
- обозначаем местоположение детальной полосы (в нашем примере она есть):
выделяем ячейки, образующие детальную полосу, и на закладке макет нажимаем на кнопку Детальная полоса.
Если ошиблись в указании детальной полосы, кликаем на голубую метку Detail текущей детальной полосы и удаляем её, как на рисунке в пункте 3).
Обратите внимание - справа внизу на этом рисунке: в окне Уровни привязки нужно указать, что Детальная полоса "привязана" к подчинённому уровню нашего документа.
6) Выходим с сохранением своей печатной формы:
Если нажать на "Сохранить в Entity" (красный прямоугольник), изменения печатной формы будут отражены в общей конфигурации (которую потом сравниваем с эталонной):
. \FabiusNet\ExeOut\Server\SYSTEM\PrintTemplate\имяПечатнойФормы.repx и в reps.xlm на одну папку выше. В этом случае позже в Конфигураторе обязательно нужно выполнить сохранение (закладка Дерево - Сохранить).
Если нажать на "Сохранить" (розовый прямоугольник), изменения печатной формы будут отражены как частные (не попадут в эталон):
. \FabiusNet\ExeOut\Server\PRIVATE\PrintTemplate\имяПечатнойФормы.repx и в reps.xlm на одну папку выше.
Если выйти из шаблона обычным крестиком завершения работы справа вверху, изменения будут сохранены как частные (в каталоге PRIVATE).
Если выполнить сохранение в списке печатных форм:
изменения будут сохранены "в Entity" (SYSTEM).
Если удалить печатную форму из этого списка (красный крестик рядом с дискетой сохранения), удаление произойдёт "из Entity" (из каталога SYSTEM).
7) Для продолжения работы по созданию печатной формы снова нажимаем иконку Печать - см. пункт 1) этого описания.
Выбираем в левой части нужную печатную форму (пока она всего одна) и нажимаем кнопку Редактировать.
А далее см. пункт 5).
Хотя инструмент Мгновенное заполнение (Flash Fill) появился в Excel ещё с 2013-й версии, но почему-то для многих пользователей этот факт остался незамеченным. И совершенно напрасно. Во многих случаях он оказывается проще, легче и быстрее, чем аналогичные решения на формулах или макросах. По моему опыту, на тренингах эта тема вызывает постоянное "вау!" аудитории - независимо от продвинутости и/или усталости слушателей.
Механизм работы этого инструмента прост: если у вас есть один или несколько столбцов с исходными данными и вы начинаете набирать рядом в соседнем столбце их же, но в каком-либо нужном вам измененном виде, то Excel рано или поздно намекнёт, что готов продолжить дальше за вас:
Чтобы выявить логику (шаблон, pattern) преоборазования и запустить эту функцию Excel обычно хватает ввода 1-3 первых результирующих значений вручную. Если предложенный вариант вам подходит, то достаточно нажать Enter - и остаток списка будет доделан моментально.
Если вы уже ввели 2-3 первых значения, а продолжение всё не появляется, то можно форсировать процесс сочетанием клавиш Ctrl + E или использовать кнопку Мгновенное заполнение (Flash Fill) на вкладке Данные (Data) :
Давайте рассмотрим несколько примеров использования этого инструмента на практике, чтобы понять его возможности.
Извлечение слов из текста и перестановки
Написать формулу, которая извлекает, например, третье слово из текста в ячейке - маленький подвиг. Разобрать фразу по пробелу в разные колонки с помощью Данные - Текст по столбцам (Data - Text to Columns) тоже дело не быстрое. С помощью мгновенного заполнения это делается легко и красиво. Причем, можно попутно менять извлекаемые слова местами, комбинируя их в любом порядке:
Деление текста по регистру
Для выделения слов мгновенному заполнению совершенно не обязательно наличие именно пробела. Отлично сгодится и любой другой разделитель, например, запятая или точка-с-запятой после импорта CSV-файла. Но что совсем круто, так это то, что разделителя может и не быть совсем - достаточно только заглавных букв:
Реализовать подобное формулами очень тяжко. Если без мгновенного заполнения, то поможет только макрос.
Склейка текста
Если можно делить, то можно и клеить! Мгновенное заполнение легко соберёт для вас длинную фразу из нескольких фрагментов, перемежая их нужными пробелами, запятыми, союзами или словами:
Извлечение отдельных символов
Обычно для вытаскивания отдельных символов и подстрок в Excel используются функции ЛЕВСИМВ (LEFT) , ПРАВСИМВ (RIGHT) , ПСТР (MID) и им подобные, но мгновенное заполнение с легкостью решает и эту задачу. Классический пример - формирование ФИО:
Извлечение только чисел, текста или дат
Если вы когда-нибудь пытались вытащить только нужный тип данных из буквенно-цифровой каши, то должны понимать всю сложность этой простой, на первый взгляд, задачи. Мгновенное заполнение и тут справляется "на ура", но нужен лёгкий пендель в виде Ctrl + E :
С извлечением текста тоже самое:
Даты - тоже не проблема (даже если они написаны в разных форматах):
Преобразование форматов чисел или дат
Мгновенное заполнение поможет изменить внешний вид имеющихся данных или привести их к "одному знаменателю". Например, обычную дату преобразовать "шиворот-навыворот" в Unix-формат:
Здесь нюанс в том, что перед вводом нужно заранее поменять формат итоговых ячеек на текстовый, чтобы Excel не пытался распознать введенные вручную в качестве образца "неправильные" даты.
Аналогичным образом можно также правильно представить телефонные номера, добавив код страны и трехзначный префикс оператора (города) в скобках:
Не забудьте сначала поменять формат ячеек в столбце В на текстовый - иначе Excel будет воспринимать значения начинающиеся со знака "+" как формулы.
Преобразование текста (чисел) в дату
Изменение регистра
Если вам достался текст с нЕпрАвИльНЫм рЕгисТроМ, то можно просто намекнуть в соседнем столбце к какому виду вы хотите его преобразовать - и мгновенное заполнение сделает за вас всю работу:
Чуть сложнее будет, если нужно изменить регистр по разному у разных частей текста. Например, сделать заглавными только второе слово, оставив первое в нормальном виде. Здесь двух введённых в качестве образца значений окажется недостаточно и придется внести правки, которые мгновенное заполнение тут же учтёт в результатах:
Ограничения и нюансы
При использовании в работе мгновенного заполнения следует помнить несколько моментов:
Требуется: Заполнить готовую книгу Excel с кучей формул и форматированием из 1С.
Решение: Просматриваем из 1С все листы, и в ячейки с примечаниями (комментариями) записываем результат формулы, указанные в этих же ячейках.
Т.е. нашли ячейку с примечанием - прочитали в ней выражение, вычислили с помощью 1С, и записали обратно в ячейку.
Перебор ячеек с примечаниями - только чтобы ускорить просмотр книги, не перебирать все ячейки в ней.
Текст примера под катом, функция ЗначениеПоказателя () и переменная ФайлШаблона - в фрагменте не описаны.
//
Excel = СоздатьОбъект( "Excel.Application" ) ;
Excel . Visible = 1;
Excel . DisplayAlerts = 0;
Д = Excel . Workbooks . Add ( ФайлШаблона ) ;
Для К = 1 по Д . Sheets . Count Цикл
Л = Д . Sheets ( К ) ; // текущий лист
ВсегоКомментариев = Л . Comments . Count ; // комментариев на листе
Для сч = 1 По ВсегоКомментариев Цикл
Состояние( "Заполнение " + Л . Name +" " +Цел( 100- сч / ВсегоКомментариев * 100 )+ "%" ) ;
// текст коментария, в книге только служебные
Стр = Л. Comments ( сч ) . Text () ;
// прочитаем ячейку
ИмяПоказателя = СокрЛП ( Л . Comments ( сч ) . Parent . Value ) ;
// и сразу запишем в нее результа
Л . Comments ( сч ) .Parent . Value = ЗначениеПоказателя ( ИмяПоказателя ) ;
КонецЦикла ; // по комментариям
КонецЦикла ; // по листам
ИмяФайла = ""+ Формат(РабочаяДата() ,"DГГГГММДД" )+ "_" + _шаблон+".xlsx";
Д . SaveAs (СокрЛП(Константа . КаталогЭкспортаДанных ) +"\"+ ИмяФайла ) ;
// удалим все комментарии в результирующем файле, чтобы не мешали
Для К = 1 по Д . Sheets . Count Цикл
Л = Д . Sheets ( К ) ; // текущий лист
КвоКомментариев = Л . Comments . Count ; // комментариев на листе
Пока КвоКомментариев > 0 Цикл
Л . Comments ( КвоКомментариев ) . Delete () ;
КвоКомментариев = КвоКомментариев - 1;
КонецЦикла ; // по комментариям
КонецЦикла ; // по листам
Читайте также: