Программная работа с Excel из 1Сv8 - кнопки, макросы, области, отображение листов и полос прокрутки при открытии

Публикация № 340826

Программирование - Практика программирования

Excel.Application закрепление областей Создание Кнопки макросы Workbooks Worksheets VBProject

36
Буду краток, представленная информация здесь это пример работы с Excel программно из 1С, от начала - создание COMОбъект("Excel.Application"), до конца его закрытия. Тут представлен пример, как создать кнопку, присвоить ей макрос, затем добавить лист, прописать в ячейки необходимую информацию, затем установка нужного листа в момент при открытии Excel, корректное отображение листов и полосы прокрутки, затем сохранение файла в необходимый формат. Данная статья не уникальна, само собой, но в данном контексте она консолидирует информацию от различных источников и призвана только помочь в познании методов программной работы с Excel как полноценный законченный пример. Предполагается, что читатель уже имеет опыт в создании СКД отчетов.

Задача:

Создать прайс-лист и выгрузить его в excel, предполагается, что в файле excel пользователь-клиент может создать предварительный заказ, работу по созданию заказа необходимо также автоматизировать, сделаем это с помощью макросов.

Решение:

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

 

//Процедура Формирования Табличного документа средствами СКД
//....
//Получили табличный документ с результатом работы СКД это ДокументРезультат
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВТабличныйДокумент;
ПроцессорВывода.УстановитьДокумент(ДокументРезультат);
ПроцессорВывода.ОтображатьПроцентВывода = Истина;
ПроцессорВывода.Вывести(ПроцессорКомпоновкиДанных, Истина);
//В реквизатах на форме переменные Путь и ИмяФайла могут быть определены явно или по выбору пользователя
ПолныйПутьФайла = Путь + ИмяФайла + ".xlsx";
ИмяФайла = ИмяФайла + ".xlsx";
//Записываем табличный документ в файл екселя 
ДокументРезультат.Записать(ПолныйПутьФайла,ТипФайлаТабличногоДокумента.XLSX);
//Код ранее это было вступление, теперь мы подошли к месту, где и начинается наш пример
//В нашем примере рассматривается табличный документ с полями
код наименование товара кол-во цена комментарий
1 2 3 4 5

 

ВставитьКнопкиУправленияНаЛистExcel(ИмяФайла);
Процедура ВставитьКнопкиУправленияНаЛистExcel(ФайлЕксель)
    
    ИмяФайлаЕксель = Путь + ФайлЕксель;
    //Немного по форматам
    // formats in Excel 2007-2010
    xlExcel12 = 50; // XLSB - данные в бинарном виде, хорошо для больших таблиц, макс скорость,мин объем ).
    xlOpenXMLWorkbook = 51; // XLSX - не поддерживает макросы, вообще не наш случай
    xlOpenXMLWorkbookMacroEnabled = 52; // XLSM - поддерживает макросы, данные в виде openxml
    xlExcel8 = 56; // XLS (export to Excel 97-2003) небольшие файлы
   
    //Получаем доступ к екселю
    Ексель = Новый COMОбъект("Excel.Application");
   
    //Делаем книгу
    Книга = Ексель.Workbooks.Open(ИмяФайлаЕксель);
   
    //Теперь рабочую область 
    Лист1 = Книга.Worksheets(1);
    Лист1.Name = "Прайс лист";
    
    //Ставим закрепление областей
    Ексель.ActiveWindow.FreezePanes = 0;
    Ексель.ActiveWindow.SplitRow = 10;
    Ексель.ActiveWindow.SplitColumn = 5;
    Ексель.ActiveWindow.FreezePanes = 1;
    
    //Создание Кнопки
    Кнопка1=Книга.Worksheets(1).Buttons.Add(420.5, 12.5, 89.5, 17.5);
    //первые 2 цифарки (420.5, 12.5, , ) это координаты точки
    //вторые 2 цифарки размеры кнопки длина ширина (, , 89.5, 17.5)
    Кнопка1.Caption="Создать Заказ";
    Кнопка1.OnAction = "CommandButton1_Click";
    
    Кнопка2=Книга.Sheets(1).Buttons.Add(420.5, 42.5, 89.5, 17.5);
    Кнопка2.Caption="Очистить";
    Кнопка2.OnAction = "CommandButton2_Click";
    
    //Пишем макросы в кнопки
    //Название "CommandButton1_Click" должно совпадать в процедуре ниже строка и в OnAction на кнопке
    МакросСЗ = "Private Sub CommandButton1_Click()
    |Dim bookname As String, sheetname As String
    |Dim sh As Worksheet, sh1 As Worksheet
    |  
    |Application.ScreenUpdating = False
    |
    |sheetname = ActiveSheet.Name
    |bookname = ActiveWorkbook.Name
    |Set sh = Workbooks(bookname).Worksheets(sheetname)
    |Workbooks.Add
    |ActiveSheet.Name = ""Заказ"" // создали лист с заказом
    |Set sh1 = ActiveSheet
    |
    |sh1.Cells(1, 1) = ""Код""
    |sh1.Cells(1, 2) = ""Наименование товара""
    |sh1.Cells(1, 3) = ""Кол-во""
    |sh1.Cells(1, 4) = ""Цена""
    |  
    |sh1.Columns(""a:a"").ColumnWidth = 12
    |sh1.Columns(""b:b"").ColumnWidth = 70
    |sh1.Columns(""c:c"").ColumnWidth = 8
    |sh1.Columns(""d:d"").ColumnWidth = 8
    |   
    |sh1.Range(""A1"", ""D1"").Font.Bold = True
    |  
    |j = 2 // адрес строки в листе заказа
    |i = 14 // адрес строки входа в листе прайса
    |With sh
    |  While .Cells(i, 2) <> """" // если наименование не равно пустой строке то 
    |   If (.Cells(i, 3) <> """") And (.Cells(i, 3) <> "" "") Then // если пользователь в колонке кол-во указал необходимое нам колво в заказе, то берем эту строку в заказ
    |    
    |      sh1.Cells(j, 1) = .Cells(i, 1)  ' Код товара
    |      sh1.Cells(j, 2) = .Cells(i, 2)  ' Наименование товара
    |      sh1.Cells(j, 3) = .Cells(i, 3) ' Кол-во товара
    |      sh1.Cells(j, 4) = .Cells(i, 4) ' Цена товара
    |      j = j + 1
    |    End If
    |    i = i + 1
    |  Wend
    |
    |  End With
    |
    | MsgBox (""Заказ готов!"")
    |
    |  Application.ScreenUpdating = True
    |End Sub";
    
    МакросОч = "Private Sub CommandButton2_Click()// если пользователь хочет почистить свое введенное количество в прайсе
    |Dim i As Integer
    |Application.ScreenUpdating = False
    |
    |sheetname = ActiveSheet.Name
    |bookname = ActiveWorkbook.Name
    |Set ThisSheet = Workbooks(bookname).Worksheets(sheetname)
    |With ThisSheet
    |    i = 14
    |    While .Cells(i, 2).Value <> """"
    |        If (.Cells(i, 3) <> """") And (.Cells(i, 3) <> "" "") Then
    |            .Cells(i, 3).Value = """"
    |        End If
    |    i = i + 1
    |    Wend
    |End With
    |Application.ScreenUpdating = True
    |End Sub";
    
    
    VBComponents = Книга.VBProject.VBComponents;
    Кол = VBComponents.Count();
    Модуль = VBComponents.Add(1);
    Модуль.CodeModule.InsertLines(1,МакросСЗ);
    Модуль.CodeModule.InsertLines(48,МакросОч);
    
    //Выключаем ругательства
    Ексель.DisplayAlerts = 0;
    
    //Если в прайсе есть цветовая индикация строк то мы можем сделать лист с аннотацией цветовых обозначений
   //Делаем второй лист для информации о расскраске строк на прайсе
    НомерПоследнегоЛиста = Книга.Worksheets.Count;
    Лист2 = Книга.WorkSheets.Add(,Книга.WorkSheets(НомерПоследнегоЛиста));
    Лист2.Name = "Информация";
    Ячейка=Лист2.Cells(1,1);
    Ячейка.Value = "Цветовая информация в прайс-листе";
    Ячейка.Font.Bold = 1;
    Ячейка=Лист2.Cells(2,1);
    Ячейка.Value = "Красный – спецпредложение!!!";
    Ячейка.Font.ColorIndex = 3;
    Ячейка=Лист2.Cells(3,1);
    Ячейка.Value = "Синий – новинка!!!";
    Ячейка.Font.ColorIndex= 5;
    Ячейка=Лист2.Cells(4,1);
    Ячейка.Value = "Оранжевый – эксклюзив!!!";
    Ячейка.Font.ColorIndex = 44;
    
    //Устанавливаем открытие листа с прайсом при открытии файла клиентом
    Лист1.Activate();
    //Устанавливаем видимость закладок листов, по умолчанию они выключены
    Ексель.ActiveWindow.DisplayWorkbookTabs = 1; 
    //Устанавливаем положение полосы прокрутки, по умолчанию прокрутка закрывает видимость закладок листов
    Ексель.ActiveWindow.TabRatio = 0.6;
    //Сохраняем в формат с поддержкой макросов           
    Книга.SaveAS(СтрЗаменить(ИмяФайлаЕксель,".xlsx", ".xlsm"),xlOpenXMLWorkbookMacroEnabled);
    //Сохраняем в формат с поддержкой макросов только в бинарном виде - маленький размер файла.
    Книга.SaveAS(СтрЗаменить(ИмяФайлаЕксель,".xlsx", ".xlsb"),xlExcel12);
    //Закрываем книгу
    Книга.Close();
    //Выходим из ком
    Ексель.Quit();
    //Чистим xlsx, больше он не нужен
    УдалитьФайлы(ИмяФайлаЕксель);
    ФайлЕксель = СтрЗаменить(ФайлЕксель,".xlsx", ".xlsb");
КонецПроцедуры

//Наш пример закончен

Для желающих посмотреть вживую можно скачать отчет, в нем также реализован живой пример установки картинки в СКД, почему-то разработчики явно вывод картинок в СКД не реализовали, вот и приходится использовать хитрые методы обхода, как, например, специальная система зеркал в сабже про кошку и программистов. Также его можно использовать как пособие по созданию СКД отчета с раскраской строк по условиям.

Маленький нюанс для программного создания макросов, нужно в екселе, в параметрах, доверить доступ к объектной модели VBA, на картинке внизу показано как это сделать

 

 

36

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

Наименование Файл Версия Размер
Пример прайса скд
.erf 812,36Kb
29.07.15
19
.erf 01 812,36Kb 19 Скачать
результат выгрузки в ексель с кнопками
.xlsb 90,12Kb
29.07.15
11
.xlsb 01 90,12Kb 11 Скачать

См. также

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо
1. zarucheisky 05.08.15 10:37 Сейчас в теме
3. sergik_nsk 134 17.08.15 10:22 Сейчас в теме
(1) zarucheisky, в чем ужас, есть какие то замечания по содержанию, или это просто выход эмоций в конце трудового дня в пятницу 2 января
2. Dimel 06.08.15 04:07 Сейчас в теме
Для записи макроса в Excel необходимо чтобы был включен доверительный доступ к модели VBA. Вот как я это делал (года 4 назад):

////////////////////////////////////////////////////////////////////////////////
// ПРОЦЕДУРЫ ЗАПИСИ МАКРОСОВ В EXCEL

Процедура ЗаписатьМакросВМодуль(Excel,WorkBooks,ТекстМакроса)
	Версия = Excel.Version;
	ВключилиДоверительныйДоступ = ВключитьДоверительныйДоступКОбъектамVBA(1,Версия);
	Если ВключилиДоверительныйДоступ Тогда
		ТекущийИндекс= Excel.VBE.ActiveVBProject.VBComponents.Count;
		Excel.VBE.ActiveVBProject.VBComponents.Add( 1 );//добавим модуль
		WorkBooks.VBProject.VBComponents.Item(ТекущийИндекс+1).CodeModule.AddFromString(ТекстМакроса); //добавим в модуль наш скрипт
	Иначе
		//Сообщить("Для правильной работы необходимо включить доверительный доступ к VBA");
		ТекстСообщения = "Для правильной работы необходимо включить доверительный доступ к VBA";
		ОбработкаКомментариев.ДобавитьСообщение(ТекстСообщения, Перечисления.ВидыСообщений.Ошибка,,);
	КонецЕсли;
КонецПроцедуры

Процедура ЗаписатьМакросВЛист(Excel,ИмяЛиста,ТекстМакроса)
	Версия = Excel.Version;
	ВключилиДоверительныйДоступ = ВключитьДоверительныйДоступКОбъектамVBA(1,Версия);
	Если ВключилиДоверительныйДоступ Тогда
		Excel.VBE.ActiveVBProject.VBComponents(ИмяЛиста).CodeModule.AddFromString(ТекстМакроса);   //Добавим на лист наш скрипт
	Иначе
		//Сообщить("Для правильной работы необходимо включить доверительный доступ к VBA");
		ТекстСообщения = "Для правильной работы необходимо включить доверительный доступ к VBA";
		ОбработкаКомментариев.ДобавитьСообщение(ТекстСообщения, Перечисления.ВидыСообщений.Ошибка,,);
	КонецЕсли;
КонецПроцедуры

Процедура ЗаписатьМакросВКнигу(Excel,ИмяКниги,ТекстМакроса)
	Версия = Excel.Version;
	ВключилиДоверительныйДоступ = ВключитьДоверительныйДоступКОбъектамVBA(1,Версия);
	Если ВключилиДоверительныйДоступ Тогда
		Excel.VBE.ActiveVBProject.VBComponents(ИмяКниги).CodeModule.AddFromString(ТекстМакроса);   //в книгу наш наш скрипт
	Иначе
		//Сообщить("Для правильной работы необходимо включить доверительный доступ к VBA");
		ТекстСообщения = "Для правильной работы необходимо включить доверительный доступ к VBA";
		ОбработкаКомментариев.ДобавитьСообщение(ТекстСообщения, Перечисления.ВидыСообщений.Ошибка,,);
	КонецЕсли;
КонецПроцедуры

//Функция производит включение (отключение) доверительного доступа к объектной модели VBA
// параметры включить = 1 - включить,0 - отключить 
Функция ВключитьДоверительныйДоступКОбъектамVBA(Включить=1,Версия)
	//Получим текущую версию екселя
	Попытка
		Ключ = "HKEY_CURRENT_USER\Software\Microsoft\Office\" +Версия+"\Excel\Security\AccessVBOM";
		
		WshShell = Новый COMОбъект("WScript.Shell");
		//а надо ли менять?
		ТекущееЗначение = WshShell.RegRead(Ключ);
		Если ТекущееЗначение <> Включить Тогда
			WshShell.RegWrite(Ключ,Включить,"REG_DWORD");
		КонецЕсли;
	Исключение
		Возврат ЛОЖЬ;
	КонецПопытки;
	Возврат Истина;
КонецФункции	
Показать
4. sergik_nsk 134 17.08.15 10:31 Сейчас в теме
(2) Dimel, да этот вариант известен, нормальный программный способ, только есть одно но, для его реализации необходимо пользователю дать возможность редактирования реестра, а это уже вопрос безопасности, если политика позволяет почему бы и нет.
Восьмой; +1 Ответить
5. UserPMV 15.06.19 09:53 Сейчас в теме
У меня не получается передать параметры (https://docs.microsoft.com/ru-ru/office/vba/api/excel.window.scrollworkbooktabs)

Пробовал
Ексель.ActiveWindow.ScrollWorkbookTabs(Null,"xlFirst");
Ексель.ActiveWindow.ScrollWorkbookTabs(,"xlFirst");
Ексель.ActiveWindow.ScrollWorkbookTabs("xlFirst");
Ексель.ActiveWindow.ScrollWorkbookTabs("Position:=xlFirst");

Если воспользоваться записью макроса в Экселе, то получим ActiveWindow.ScrollWorkbookTabs Position:=xlFirst

Как вызвать прокрутку листов в начало?
Оставьте свое сообщение