Работа с Excel

23.11.2018 39 Comments

Практика программирования, Разработка

, , ,

Собрал различные полезности для работы с Excel из 1С. Иногда приходится форматировать документ Excel программно из 1С. Так вот, чтобы не искать постоянно на просторах интернета как сделать левое выравнивание в ячейке Excel из 1С и т.п. решил опубликовать это…

СПОСОБ ПОИСКА НАИМЕНОВАНИЙ СВОЙСТВ И МЕТОДОВ VBA EXCEL С ПОМОЩЬЮ ЗАПИСИ МАКРОСОВ

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

1. Подключение к Excel

Попытка
Excel = Новый COMОбъект("Excel.Application");
Исключение
Сообщить(ОписаниеОшибки() + "Возможно программа Exсel не установлена на данном компьютере!");
Возврат Ложь;
КонецПопытки;

//Создание книги
Книга = Excel.WorkBooks.Add();

//Открытие существующей книги
Книга = Excel.WorkBooks.Open(ПутьКФайлу);

//Выбор рабочего листа по номеру
Лист = WorkBook.WorkSheets(НомерЛиста);

//Выбор рабочего листа по имени
Лист = WorkBook.WorkSheets(ИмяЛиста);

//Сохранение книги
Попытка
Книга.SaveAs(ПутьКФайлу);
Книга.Close();
Excel.Quit();
Исключение
Книга.Close();
Excel.Quit();
КонецПопытки;

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

2. Запись значения в ячейку

ТекущийЛист.Cells(i, j).Value = "2";

Количество листов книги

КоличествоЛистов = Книга.Sheets.Count;

3. Шрифт и размер шрифта 

//Выделение области
Excel.Columns("A:H").Select();

//Размер шрифта
Лист.Cells.Font.Size = 12;

//Тип шрифта
Лист.Cells.Font.Name = "Calibri";

//1 — жирный шрифт, 0 — обычный.
Лист.Cells.Font.Bold = 1;

//1 — наклонный шрифт, 0 — обычный.
Лист.Cells.Font.Italic = 1;

//2 — подчеркнутый, 1 — нет.
Лист.Cells.Font.Underline = 1;

4. Ширина колонки

Чтобы скрыть колонку, нужно установить ширину в значение 0.

Лист.Columns(НомерКолонки).ColumnWidth = Ширина;

Получение последней ячейки в используемом диапазоне

Например, при открытии таблицы нам нужно узнать до какой строки и/или до какого столбца присутствуют заполненные ячейки. 

ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column;
ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;

4. Установка фильтра

Лист.Cells.AutoFilter();

 

5. Горизонтальное выравнивание ячейки

ТекущийЛист.Cells(i, j).HorizontalAlignment = -4130;

Режим выравнивания

Константа в Excel

Значение в ISBL

По центру

xlHAlignCenter

-4108

По центру выделения

xlHAlignCenterAcrossSelection

7

Распределенное

xlHAlignDistributed

-4117

С заполнением

xlHAlignFill

5

По значению

xlHAlignGeneral

1

По ширине

xlHAlignJustify

-4130

По левому краю

xlHAlignLeft

-4131

По правому краю

xlHAlignRight

-4152

 

 

6. Вертикальное выравнивание ячейки

ТекущийЛист.Cells(i, j).VerticalAlignment = -4130;

Режим выравнивания

Константа в Excel

Значение в ISBL

По нижнему краю

xlVAlignBottom

-4107

По центру

xlVAlignCenter

-4108

Распределенное

xlVAlignDistributed

-4117

По высоте

xlVAlignJustify

-4130

По верхнему краю

xlVAlignTop

-4160

7. Установка свойства ячейки "переносить по словам"

ТекущийЛист.Cells(i, j).WrapText = True;

 

8. Отключение/включение режима показа предупреждений

ExcelApp.DisplayAlerts = False; // отключение
ExcelApp.DisplayAlerts = True;  // включение

 

9. Формулы

ТекущийЛист.Cells(i, j).Formula = "SUM(A1:A10)";

Для формул с русскими названиями нужно использовать не Formula, а FormulaLocal. Таким образом правильными вариантами написания формул будут:

ТекущийЛист.Cells(i, j).FormulaLocal = "Сумм(A1:A10)";

 

10. Таблица цветов с индексами

//Установка цвета текста ячейки
ТекущийЛист.Cells(i, j).Font.ColorIndex = 8;

//Установка цвета фона ячейки
ТекущийЛист.Cells(i, j).Interior.Color = 49;

//Установка цвета рамки
ТекущийЛист.Cells(i, j).Borders.Color = 8;

 

11. Установка границы ячейки и тип линии границы

Значения константы XlBordersIndex

Тип линии

Значение в Excel

Значение в ISBL

Нет линии

xlLineStyleNone

0

Непрерывная

xlContinuous

1

В виде тире и точек

xlDashDot

4

В виде тире и двойных точек

xlDashDotDot

5

В виде точек

xlDot

8

В виде двойной линии

xlDouble

9

В виде наклонной пунктирной

xlSlantDashDot

13

 

Свойство LineStyle (тип линии) может принимать следующие значения

Расположение линии

Значение в Excel

Значение в ISBL

Линия по диагонали сверху – вниз

xlDiagonalDown

5

Линия по диагонали снизу – вверх

xlDiagonalUp

6

Линия, обрамляющая диапазон слева

xlEdgeLeft

7

Линия, обрамляющая диапазон сверху

xlEdgeTop

8

Линия, обрамляющая диапазон снизу

xlEdgeBottom

9

Линия, обрамляющая диапазон справа

xlEdgeRight

10

Все вертикальные линии внутри диапазона

xlInsideVertical

11

Все горизонтальные линии внутри диапазона

xlInsideHorizontal

12

 

Получение порядкового номера колонки Excel по буквенному обозначению имени

Однажды столкнулся с тем, что нужно было получить буквенное обозначение колонки Excel по её порядковому номеру. Например, для колонки номер 3 буквенное обозначение равно "C". Так вот ниже приведены две замечательные функции, которые вполне справляются с решением данной проблемы. 

// Функция - Имя колонки Excel в номер
//
// Параметры:
//  тИмяКолонки - строка - буквенное обозначение колонки Excel (Пример: A, AB, AAC и т.д.)
//
// Возвращаемое значение:
//   - число, порядковый номер колонки
//
Функция ExcelColumnNameToNumber(тИмяКолонки)
тЛатАлфавит = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
тДлинаНомера = СтрДлина(тИмяКолонки);

тНомерКолонки = 0;

Для тСчет = 1 По тДлинаНомера Цикл
тПоз = Найти(тЛатАлфавит, Сред(тИмяКолонки, (тДлинаНомера + 1 - тСчет), 1));
тНомерКолонки = тНомерКолонки + тПоз * Pow(26, тСчет - 1);
КонецЦикла;

Возврат тНомерКолонки;
КонецФункции

Наоборот, получение буквенного обозначения имени колонки по номеру колонки

// Функция - Номер колонки Excel в буквенное обозначение
//
// Параметры:
//  тНомерКолонки - число - порядковый номер колонки
//
// Возвращаемое значение:
//   - строка, буквенное обозначение колонки
//
Функция NumberToExcelColumnName(Знач тНомерКолонки)
тЛатАлфавит = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
тИмяКолонки = "";

Пока тНомерКолонки > 0 Цикл
тОстаток = (тНомерКолонки - 1)%26;

тБуква = Сред(тЛатАлфавит, тОстаток + 1, 1);
тИмяКолонки = тБуква + тИмяКолонки;

тНомерКолонки = Цел((тНомерКолонки - тОстаток)/26);
КонецЦикла;

Возврат тИмяКолонки;
КонецФункции
 

 ИСТОЧНИКИ

 

Related Posts

39 Comments

  1. script

    Допишите еще один универсальный способ. Через макросы. Включаем запись макроса. Меняем что нужно в таблице. Завершаем макрос. Смотрим код в VB редакторе.

    Reply ↓
  2. Denis Nsk

    Спасибо за Ваши труды!

    Лично для меня, полезная шпаргалка.

    Reply ↓
  3. TrinitronOTV

    В пункте 11 почему-то нет примера кода на языке 1С

    Reply ↓
  4. ADirks

    ещё в шпору

    последняя строка

    xlLastCell = 11;

    LastRow = Sh.Cells(1,1).SpecialCells(xlLastCell).Row;

    чтобы не умничал, пытаясь превращать строки в числа и даты (особенно бесит)

    Sh.Cells(Row, i).NumberFormat = «@»;

    Sh.Cells(Row, i).Value = Value;

    иногда и это не помогает, тогда так

    Sh.Cells(Row, i).Value = «‘» + Value;

    Reply ↓
  5. arakelyan

    (1)Хорошо, добавлю

    Reply ↓
  6. arakelyan

    (3)Он, был….но таинственным образом куда-то пропал. Добавлю сегодня.

    Reply ↓
  7. user662672_explorer2000

    (1) Тоже всегда считал этот метод 100% работающим, но когда возникла необходимость программно заменить картинку на листе, то запись макроса вернула пустую процедуру (( пришлось много и долго читать интернет

    Reply ↓
  8. arakelyan

    (7) Программно заменить картинку можно напрямую из 1С, однако появляется проблема с её позиционированием. Влить картинку ровно в ячейку Excel только средствами 1С у меня не вышло, пришлось добавлять макрос в VBA. Если тема интересна, могу добавить в статью.

    Reply ↓
  9. user662672_explorer2000

    (8) Задача у меня такого плана: на листе много картинок высокого качества, из-за чего файл вести очень много. Нужно картинки заменить на картинки с качеством пониже. Как понизить качество я представляю, как картинку забрать с листа тоже, но как ее заменить пока не очень понимаю, еще ищу, благо терпит. Буду благодарен если подскажете

    Reply ↓
  10. kirillkr

    Не хватает информации, в каком формате сохранить книгу, которую как раз искал несколько дней назад.

    Если Число(Excel.Version) < 12 Тогда
// Используется Excel 97-2003
пРасширение = «xls»;
пФорматФайла = -4143;
Иначе
Если впИспользуемМакросы Тогда
пРасширение = «xlsm»;
пФорматФайла = 52;
Иначе
пРасширение = «xlsx»;
пФорматФайла = 51;
КонецЕсли;
КонецЕсли;

    Показать

    Reply ↓
  11. o.nikolaev

    Нет:

    — добавления картинок, позиционирования на нужной картинке и установке ее свойств;

    — запись, чтение очень больших объемов данных в (из) файла Excel;

    — работы с диаграммами.

    Reply ↓
  12. gorakh 
    ДокументExcel = ПолучитьCOMОбъект(ИмяФайла);
//Листы :
СписокЛистов = Новый СписокЗначений;
Для каждого Лист Из ДокументExcel.Sheets Цикл
СписокЛистов.Добавить(Лист.Index,Лист.Name);
КонецЦикла;

ЛистExcel= ДокументExcel.Sheets(НомерЛистаКниги);
ПоследняяСтрока = ЛистExcel.UsedRange.Rows.Count;
ЛеваяКолонка = ЛистExcel.UsedRange.Column;
ПоследняяКолонка =  ЛистExcel.UsedRange.Columns.Count;
НомерКолонкиСТекстом=ЛистExcel.UsedRange.Find(«text»).Column;
//Или
НомерКолонкиСТекстом=ЛистExcel.Cells.Find(«text»).Column;
//Подчищаем за собой память
ДокументExcel.Application.Quit();

    Показать

    Reply ↓
  13. sergalei

    Дамп листа в массив.

    Значительно ускоряет обработку больших таблиц.

    Функция ПолучитьМассивИзЛистаЭксель(Путь, НомерЛиста)

Попытка
Эксель = Новый ComОбъект(«Excel.Application»);
Эксель.DisplayAlerts = 0;
Книга = Эксель.Workbooks.Open(Путь);
Исключение
Возврат Неопределено;
КонецПопытки;

Результат = Эксель.Sheets(НомерЛиста).UsedRange.Value.Выгрузить();
Эксель.Quit();

Возврат Результат;

КонецФункции

    Показать

    Reply ↓
  14. maxdmt

    (13), подробнее

    Функция ПрочитатьЛистExcel(ТЗ = Неопределено, ЛистЭксель = Неопределено, НомерПервойСтроки = 1, НомерПервойКолонки = 1, ВсегоСтрок = 0, ВсегоКолонок = 0) Экспорт

Если ЛистЭксель = Неопределено Тогда
ЛистЭксель = ПолучитьCOMОбъект(,»Excel.Application»);
КонецЕсли;
Если ВсегоСтрок = 0 Тогда
ВсегоСтрок = ЛистЭксель.Cells.SpecialCells(11).Row;
КонецЕсли;
Если ВсегоКолонок = 0 Тогда
ВсегоКолонок = ЛистЭксель.Cells.SpecialCells(11).Column;
КонецЕсли;
Если ТЗ = Неопределено Тогда
ТЗ =  Новый ТаблицаЗначений;
Для Счетчик = 1 По ВсегоКолонок Цикл
ТЗ.Колонки.Добавить(«Колонка»+Счетчик, Новый ОписаниеТипов(«Строка»));
КонецЦикла;
КонецЕсли;
Состояние(«Заполнение пустой таблицы…»);
Для Счетчик = НомерПервойСтроки По ВсегоСтрок Цикл
НоваяСтрока = ТЗ.Добавить();
КонецЦикла;

Состояние(«Выгрузка в массив…»);
Область = ЛистЭксель.Range(ЛистЭксель.Cells(НомерПервойСтроки,НомерПервойКолонки), ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок));
Данные = Область.Value.Выгрузить();

Для Счетчик = 0 По ВсегоКолонок-1 Цикл
Состояние(«Загрузка колонки: » + (Счетчик + 1) + » из » + ВсегоКолонок);
ТЗ.ЗагрузитьКолонку(Данные[Счетчик], Счетчик);
КонецЦикла;
//ЛистЭксель = Неопределено;
Возврат ТЗ;
КонецФункции

    Показать

    Reply ↓
  15. Alias

    Из опыта собственной необходимости:

    — к пункту 9: формулы можно задавать также через FormulaR1C1 и FormulaR1C1Local, это бывает много удобнее тем что не нужно постоянно менять буквы в формуле — она одна и та же для всех ячеек, например расчёт НДС по сумме из предыдущей колонки «=ROUND(RC[-1]*1.18, 2)» для всех строк колонки.

    — к пункту 10: заливка фона это Ячейка.Interior.ColorIndex

    — Ячейка.Locked = False; это отключить защиту ячейки, после чего Лист.Protect(«p@ssw0rd») — защитить лист от изменений.

    Всецело присоединяюсь также к комментарию (1).

    P.S. И да, спасибо, отличная шпаргалка 🙂

    Reply ↓
  16. ArchLord42

    Группировка строк, аналогичная табличному документу

    Лист.Range(«A1:A3»).Rows.Group()

    Собственно чтобы сгрупиировать колонки нужно обратиться не к Rows, а к Cols

    так же можно настроить способ вывода значка с «+», либо сверху, либо снизу

    Лист.Outline.SummaryRow = 0;

    0 — вверху

    1 — внизу (по умолчанию)

    и конечно же имеется аналог метода «ПоказатьУровеньГруппировокСтрок/ПоказатьУровеньГруппировокК­олонок»

    Лист.Outline.ShowLevels(УровеньГруппировки)
    Reply ↓
  17. vladismi

    Хорошая шпаргалка.

    Плюс!

    Reply ↓
  18. Sitial
    Reply ↓
  19. splxgf

    (9)

    (8) Задача у меня такого плана: на листе много картинок высокого качества, из-за чего файл вести очень много. Нужно картинки заменить на картинки с качеством пониже. Как понизить качество я представляю, как картинку забрать с листа тоже, но как ее заменить пока не очень понимаю, еще ищу, благо терпит. Буду благодарен если подскажете

    В экселе есть встроенная функция понижения качества всех картинок или выделенной.

    При очень большом желании это можно сделать средствами VBA.

    https://infostart.ru/public/19977/

    Reply ↓
  20. user662672_explorer2000

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

    Reply ↓
  21. frkbvfnjh

    (1) Зачем раскрываешь секреты Масонов?! 🙂

    Reply ↓
  22. premierex

    (0)

    чтобы не искать постоянно на просторах интернета

    достаточно в редакторе VBA открыть Object Browser (F2). Он содержит описание объектов MS Office, значения констант VBA, а также встроенную справочную систему. Видимо не зря шутка появилась, что программер нажимает F1, когда других возможностей что-то узнать о программе уже не осталось )))

    Reply ↓
  23. Mi11er

    Вставлю свою пять копеек, так как сам намучался с excel

    Объединение яйчеек

    Лист.Range(«E1:F1»).MergeCells = Истина;

    Может уже было, но все же

    Цвет фона

    Лист.Cells(2,3).Interior.ColorIndex = 42;

    ГиперСсылки

    Лист.Cells(СтрокаЛиста, 2).Hyperlinks.Add(Лист.Cells(СтрокаЛиста, 2), Выборка.МДО_URLТовара,,»Ссылка на товар с сайта www.___.ru»,Выборка.Артикул );

    Закрепление областей

    Эксель.Application.ActiveWindow.SplitRow = 4;
Эксель.Application.ActiveWindow.FreezePanes = 1;

    Ширина колонки

    Лист.Columns(7).ColumnWidth = «10»;
    Reply ↓
  24. waol

    как говорится — на полку в качестве справочного пособия

    Reply ↓
  25. &rew

    (13) Да и в принципе зачем держать Ексель открытым занимая без дела ресурсы.

    Reply ↓
  26. ADirks

    (22) так то да, но быстрее в шпаргалку посмотреть. Задачи то обычно весьма однотипные.

    Reply ↓
  27. niki295

    +1 Спасибо! Пригодится

    Reply ↓
  28. splxgf

    (20) Если делали вручную, задавали качество для экрана и применяли ко всем картинкам, то уменьшение размера должно было произойти.

    Если нет, то маловероятно что достигнете лучших результатов дополнительными скриптами.

    Кстати с заменой картинки проблем нет, делал такой код на экселе, но он сильно зависит от версии.

    Находите картинку, забираете ее, запоминаете характеристики (положение, размер), и удаляеете…

    Затем вставляете уже сжатую через ActiveSheet.Shapes.AddPicture

    Reply ↓
  29. rpgshnik

    Статья хорошая, в закладки.

    Предлагаю автору, обновить статью добавив выдержки из комментарий, очень полезно и информативно будет.

    Reply ↓
  30. arakelyan

    (29)Добавлю обязательно, занят немного на работе. Я ещё не все свои мысли изложить успел)

    Reply ↓
  31. soba

    1С вроде как сечас рекомендует нативные методы платформы. Много юзеров сейчас без exel на компе

    Reply ↓
  32. fly_men

    Флаг чтобы убрать режим защищенного просмотра в сохраненном файле:

    Excel.FileValidation = 1;
    Reply ↓
  33. hasp_x

    Как можно отключить макросы на время загрузки в Excel?

    Reply ↓
  34. Perfolenta

    а я уже больше 10 лет пользуюсь своей внешней компонентой, которая позволяет «как в 1С» создать шаблон-макет документа Эксель, а потом выводить секции вместе с нужными формулами и значениями опять же «как в 1С»…

    https://infostart.ru/public/564876/

    как по мне, так гораздо удобней, чем писать на VBA

    Reply ↓
  35. kembrik

    «Универсальное» получение последней строки выглядит так:

    Version = ОбъектExcel.Version;

Версия = Лев(Version, Найти(Version,».») — 1);

Если Версия = «8» тогда
ФайлСтрок = ОбъектExcel.Cells.CurrentRegion.Rows.Count;
ФайлКолонок = Макс(ОбъектExcel.Cells.CurrentRegion.Columns.Count, 13);
Иначе
ФайлСтрок = ОбъектExcel.Cells(1,1).SpecialCells(11).Row;
ФайлКолонок = ОбъектExcel.Cells(1,1).SpecialCells(11).Column;
Конецесли;

    Показать

    Reply ↓
  36. zarucheisky

    (22) мало того, оно позволяет посмотреть объктную модель класса COM, доступного для данного пользователя.

    Reply ↓
  37. starik-2005

    А стоит написать статью о том, как выгрузить табличные документы в Excel на разные листы средствами платформы (ну мало ли нет на сервере 1С офиса от мелкомягких)? Или такая статья уже есть?

    Reply ↓
  38. ImHunter

    (38) Возможно, стандартная запись платформы ТабДока в Excel так и делает. При этом в ТабДоке должны быть именованные области строк. Чтение, по-крайней мере, подобным образом происходит — в ТабДоке присутствует несколько областей по именам листов.

    Reply ↓
  39. starik-2005

    (39)

    Возможно, стандартная запись платформы ТабДока в Excel так и делает.

    Стандартная запись текДока так НЕ делает, но в 1С запись не ограничивается только объектом ТабличныйДокумент…

    Reply ↓

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *