Задача:
Создать прайс-лист и выгрузить его в 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, на картинке внизу показано как это сделать
