Введение
В истории ведения бизнеса с помощью платформы 1С часто бывают случаи когда необходимо выгрузить информацию в какой-либо файл, например excel.
Так вот, таким образом и в нашей истории нужно было выгружать и собирать информацию в понятном виде, с максимальным удовлетворением желаний пользователей, и главное с целью минимизировать трудозатраты и повысить эффективность труда.
В нашем распоряжении имеется два отчета. Отчет на СКД, с помощью которого можно типовым механизмом вывести вывести отчет в excel и точно такой же отчет на макете. Оба этих отчета берут данные из одного места и выводят информацию в одинаковом для пользователей виде.
В механизм СКД и вывод отчета лучше не лезть, он формируется типовым способом, пусть так и будет. А вот отчет на макете можно изменить.
План действий при изменении будет такой:
- Соберем табличную часть для отчета
- Сделаем отдельную кнопку вывода (либо сразу пользователю, либо будем выводить его в excel)
- Сформируем имя файла и сохраним его в excel
- Добавим в сохраненный файл формулы
В ходе описания данной статьи рассмотрим пункты 3 и 4.
Пример кода для разбора:
ПолноеИмяВременногоФайла = ПолучитьИмяВременногоФайла("xls");
//Конец Формирования имени файла
ТабДок.Записать(ПолноеИмяВременногоФайла, ТипФайлаТабличногоДокумента.XLS);
Попытка
Excel = Новый COMОбъект("Excel.Application");
Исключение
Сообщить(ОписаниеОшибки() + "Возможно программа Exсel не установлена на данном компьютере!");
КонецПопытки;
Эксель = Excel.WorkBooks;
Книга = Эксель.Open(ПолноеИмяВременногоФайла);
Лист = Книга.WorkSheets(1);
Для СтрокаНомер = 1 По ТабДок.ВысотаТаблицы Цикл
//Записываем значения строки
Для КолонкаНомер = 1 По ТабДок.ШиринаТаблицы Цикл
Область = ТабДок.Область("R"+СтрЗаменить(Строка(СтрокаНомер), " ", "")+"C" +СтрЗаменить(Строка(КолонкаНомер), " ", ""));
ОбластьДляОпределения = ТабДок.Область("R"+СтрЗаменить(Строка(СтрокаНомер), " ", "")+"C" +Строка(1));
Если Область.Заполнение = ТипЗаполненияОбластиТабличногоДокумента.Текст Тогда
Если Строка(ОбластьДляОпределения.Текст) = "Итого, Ч:М" Тогда
Если КолонкаНомер > 2 И КолонкаНомер <= Цел((КонецПериода - НачалоПериода)/60/60/24) + 3 Тогда
Минуты = ВремяПерерыва % 60;
Часы = (ВремяПерерыва - Минуты)/60;
Лист.Cells(СтрокаНомер, КолонкаНомер).FormulaLocal = "=ЕСЛИ(ИЛИ(R[-1]C=R[-2]C;R[-1]C=""0"";R[-1]C="""";R[-2]C=""0"";R[-2]C="""");0;ЕСЛИ(R[-1]C*24>R[-2]C*24;R[-1]C-R[-2]C - """+Строка(Часы)+":"+Строка(Минуты)+""";1-R[-2]C+R[-1]C - """+Строка(Часы)+":"+Строка(Минуты)+"""))";
Лист.Cells(СтрокаНомер, КолонкаНомер).NumberFormat = "ч:мм";
Лист.Cells(СтрокаНомер, КолонкаНомер).Locked = Истина;
Лист.Cells(СтрокаНомер, КолонкаНомер).FormulaHidden = Истина;
Лист.Cells(СтрокаНомер - 1, КолонкаНомер).Locked = Ложь;
Лист.Cells(СтрокаНомер - 2, КолонкаНомер).Locked = Ложь;
КонецЕсли;
Если 1 = КолонкаНомер - (Цел((КонецПериода - НачалоПериода)/60/60/24) + 3) Тогда
Лист.Cells(СтрокаНомер, КолонкаНомер).FormulaLocal = "=СУММ(RC[-"+Строка(Цел((КонецПериода - НачалоПериода)/60/60/24+1))+"]:RC[-1])";
Лист.Cells(СтрокаНомер, КолонкаНомер).NumberFormat = "[ч]:мм";
Лист.Cells(СтрокаНомер, КолонкаНомер).Locked = Истина;
Лист.Cells(СтрокаНомер, КолонкаНомер).FormulaHidden = Истина;
КонецЕсли;
Если 2 = КолонкаНомер - (Цел((КонецПериода - НачалоПериода)/60/60/24) + 3) Тогда
Лист.Cells(СтрокаНомер, КолонкаНомер).FormulaLocal = "=СЧЁТЕСЛИ(RC[-"+Строка(Цел((КонецПериода - НачалоПериода)/60/60/24)+2)+"]:RC[-2];""<> 0"")";
Лист.Cells(СтрокаНомер, КолонкаНомер).NumberFormat = "Основной";
Лист.Cells(СтрокаНомер, КолонкаНомер).Locked = Истина;
Лист.Cells(СтрокаНомер, КолонкаНомер).FormulaHidden = Истина;
Лист.Cells(СтрокаНомер - 3, КолонкаНомер).FormulaLocal = "=ЦЕЛОЕ(R[3]C[-1]*24)";
Лист.Cells(СтрокаНомер - 3, КолонкаНомер).NumberFormat = "# ##0,00";
КонецЕсли;
КонецЕсли;
ИначеЕсли Область.Заполнение = ТипЗаполненияОбластиТабличногоДокумента.Параметр тогда
Лист.Cells(СтрокаНомер, КолонкаНомер).Value = Строка(Область.Параметр);
Иначе
Лист.Cells(СтрокаНомер, КолонкаНомер).Value = Область.Шаблон;
КонецЕсли;
КонецЦикла;
КонецЦикла;
Книга.WorkSheets(1).Protect("12345", 1, 1, 1);
Эксель.Application.DisplayAlerts = Ложь;
Книга.SaveAs(ПолноеИмяВременногоФайла);
Книга.Close();
Разбор кода:
Первой строкой задаем имя файла: "ПолноеИмяВременногоФайла = ПолучитьИмяВременногоФайла("xls");".
Второй строкой записываем полученный табличный документ в файл с расширением excel: "ТабДок.Записать(ПолноеИмяВременногоФайла, ТипФайлаТабличногоДокумента.XLS);"
А теперь следующими пятью строками, так как ms office у пользователя может быть не установлен, начинаем обработку исключений и пытаемся получить com-объект(Это объекты которые устанавливается в самой системе, их также можно вызывать из командой строки, либо системными файлами). При этом попытка не начинается выше потому что для записи файла установленный объект не потребуется, но обратиться к нему уже не получится:
Попытка
Excel = Новый COMОбъект("Excel.Application");
Исключение
Сообщить(ОписаниеОшибки() + "Возможно программа Exсel не установлена на данном компьютере!");
КонецПопытки;
После успешного получения com-объекта, им можно управлять, изменять, сохранять; выполняя при этом команды для этого объекта с помощью 1С.
Строками:
Эксель = Excel.WorkBooks;
Книга = Эксель.Open(ПолноеИмяВременногоФайла);
Лист = Книга.WorkSheets(1);
Создается новый документ в excel. При открытии документа мы получаем книгу. В этой книге нужно будет получить таблицу с которой и будем работать.
Теперь представляем область в виде матрицы, ее нужно будет обойти.
"ТабДок.ВысотаТаблицы" получаем заполненную область таблицы в excel.
Запускаем цикл начиная с первой строки:
Для СтрокаНомер = 1 По ТабДок.ВысотаТаблицы Цикл
В строке имеются колонки. Создаем обход по этим колонкам.
Для КолонкаНомер = 1 По ТабДок.ШиринаТаблицы Цикл
Дальше нужно определить места в которых будут находиться формулы.
Это делается с помощью следующего кода:
Область = ТабДок.Область("R"+СтрЗаменить(Строка(СтрокаНомер), " ", "")+"C" +СтрЗаменить(Строка(КолонкаНомер), " ", ""));
ОбластьДляОпределения = ТабДок.Область("R"+СтрЗаменить(Строка(СтрокаНомер), " ", "")+"C" +Строка(1));
Если Область.Заполнение = ТипЗаполненияОбластиТабличногоДокумента.Текст Тогда
Если Строка(ОбластьДляОпределения.Текст) = "Итого, Ч:М" Тогда
Если КолонкаНомер > 2 И КолонкаНомер <= Цел((КонецПериода - НачалоПериода)/60/60/24) + 3 Тогда
//Код для формулы
КонецЕсли;
Если 1 = КолонкаНомер - (Цел((КонецПериода - НачалоПериода)/60/60/24) + 3) Тогда
//Код для формулы
КонецЕсли;
Если 2 = КолонкаНомер - (Цел((КонецПериода - НачалоПериода)/60/60/24) + 3) Тогда
//Код для формулы
КонецЕсли;
КонецЕсли;
Теперь по определенным местам нужно расставить формулы. Начнем с самой простой.
Лист.Cells(СтрокаНомер, КолонкаНомер).FormulaLocal = "=СУММ(RC[-"+Строка(Цел((КонецПериода - НачалоПериода)/60/60/24+1))+"]:RC[-1])";
Лист.Cells(СтрокаНомер, КолонкаНомер).NumberFormat = "[ч]:мм";
Лист.Cells(СтрокаНомер, КолонкаНомер).Locked = Истина;
Лист.Cells(СтрокаНомер, КолонкаНомер).FormulaHidden = Истина;
Нам нужно получить сумму часов в удобно для чтения виде.
Первой строкой задаем формулу, прописываем такой какой она должна быть в excel. В данном случае мы считаем сумму по строке.
Второй строкой задаем формат в котором будем отображать значение. Здесь - это Часы : минуты.
Третьей строкой блокируем ячейку, чтобы пользователь не смог "испортить" формулу.
Четвертой строкой мы скрываем формулу. Так мы освободим пользователя от линей информации.
Следующими строками, мы будем считать только если выполняется условие и аналогично коду выше блокировать ячейку.
Лист.Cells(СтрокаНомер, КолонкаНомер).FormulaLocal = "=СЧЁТЕСЛИ(RC[-"+Строка(Цел((КонецПериода - НачалоПериода)/60/60/24)+2)+"]:RC[-2];""<> 0"")";
Лист.Cells(СтрокаНомер, КолонкаНомер).NumberFormat = "Основной";
Лист.Cells(СтрокаНомер, КолонкаНомер).Locked = Истина;
Лист.Cells(СтрокаНомер, КолонкаНомер).FormulaHidden = Истина;
Лист.Cells(СтрокаНомер - 3, КолонкаНомер).FormulaLocal = "=ЦЕЛОЕ(R[3]C[-1]*24)";
Лист.Cells(СтрокаНомер - 3, КолонкаНомер).NumberFormat = "# ##0,00";
А теперь, самая сложная из формул.
Минуты = ВремяПерерыва % 60;
Часы = (ВремяПерерыва - Минуты)/60;
//Часы = (ВремяПерерыва - Секунды - Минуты * 60)/3600;
Лист.Cells(СтрокаНомер, КолонкаНомер).FormulaLocal = "=ЕСЛИ(ИЛИ(R[-1]C=R[-2]C;R[-1]C=""0"";R[-1]C="""";R[-2]C=""0"";R[-2]C="""");0;ЕСЛИ(R[-1]C*24>R[-2]C*24;R[-1]C-R[-2]C - """+Строка(Часы)+":"+Строка(Минуты)+""";1-R[-2]C+R[-1]C - """+Строка(Часы)+":"+Строка(Минуты)+"""))";
Лист.Cells(СтрокаНомер, КолонкаНомер).NumberFormat = "ч:мм";
Лист.Cells(СтрокаНомер, КолонкаНомер).Locked = Истина;
Лист.Cells(СтрокаНомер, КолонкаНомер).FormulaHidden = Истина;
Лист.Cells(СтрокаНомер - 1, КолонкаНомер).Locked = Ложь;
Лист.Cells(СтрокаНомер - 2, КолонкаНомер).Locked = Ложь;
Подсчет будет вестись только при выполнении условий. RС в формуле говорит о том что обращение будет по координатам, в квадратных скобках указывается смещение от нулевой координаты. Но сложность вычислений состоит в том в что в формуле нужно найти разность двух дат. Также как и в 1С при вычитании двух дат мы получим число. Excel автоматически полученное число считает типом Число и если перевести это число например, снова в дату, мы получим дату которая отсчитывалась от начала времен или нулевой даты, поэтому составляем формулу с учетом этого.
А теперь следующими строками, блокируем от отчет от редактирования формул поставив на него предварительно пароль сохраняем его с нашим именем и закрываем (если не закрыть, пользователь при открытии получить ошибку о том что файл уже открыт).
Книга.WorkSheets(1).Protect("12345", 1, 1, 1);
Эксель.Application.DisplayAlerts = Ложь;
Книга.SaveAs(ПолноеИмяВременногоФайла);
Книга.Close();
В заключении
В 1С с помощью com-объекта можно обращаться считай к любому приложению, бд и тд, выполняя его команды с помощью платформы.
Вступайте в нашу телеграмм-группу Инфостарт