gifts2017

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

Опубликовал sergik_nsk sergik (sergik_nsk) в раздел Программирование - Практика программирования

Буду краток, представленная информация здесь это пример работы с 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, на картинке внизу показано как это сделать

 

 

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

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

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Евгений Заручейский (zarucheisky) 05.08.15 10:37
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");
		КонецЕсли;
	Исключение
		Возврат ЛОЖЬ;
	КонецПопытки;
	Возврат Истина;
КонецФункции	
...Показать Скрыть
3. sergik_nsk sergik (sergik_nsk) 17.08.15 10:22
(1) zarucheisky, в чем ужас, есть какие то замечания по содержанию, или это просто выход эмоций в конце трудового дня в пятницу 2 января
4. sergik_nsk sergik (sergik_nsk) 17.08.15 10:31
(2) Dimel, да этот вариант известен, нормальный программный способ, только есть одно но, для его реализации необходимо пользователю дать возможность редактирования реестра, а это уже вопрос безопасности, если политика позволяет почему бы и нет.