При разработке механизмов обмена данными часто возникает задача выгрузить информацию в формат xlsx так, чтобы пользователь не мог изменить определенные данные (например, настройки загрузки, справочную информацию или формулы). Стандартный подход программиста 1С кажется очевидным: установить свойства Защита у ячеек и ТолькоПросмотр у табличного документа. Однако при использовании метода Записать(ИмяФайла, ТипФайлаТабличногоДокумента.XLSX) многие сталкиваются с тем, что в результирующем файле Excel защита попросту отсутствует. Рассмотрим, почему это происходит и как решить проблему различными способами.
Проанализируем ситуацию: платформа 1С при конвертации объекта ТабличныйДокумент в файл xlsx переносит данные, типы (числа, даты) и базовое визуальное форматирование (цвета, границы, шрифты). Но механизмы защиты в 1С и Microsoft Excel реализованы по-разному. В Excel для активации блокировки ячеек должны быть выполнены два условия одновременно:
Стандартный метод 1С Записать() часто игнорирует передачу команды на включение защиты самого листа, даже если у ячеек стоит признак защиты. Поэтому после открытия файла в Excel мы видим, что редактирование разрешено. Выясним, как это обойти.
Это самый надежный и популярный метод, если выгрузка происходит на компьютере с установленным Microsoft Office. Суть метода заключается в том, что мы сначала сохраняем файл средствами 1С, а затем «невидимо» для пользователя открываем его через Excel и программно включаем защиту. Рассмотрим пример кода:
// 1. Формируем табличный документ стандартным способом
ТабДок = Новый ТабличныйДокумент;
// ... заполнение ТабДок данными ...
// Устанавливаем защиту на уровне 1С (на всякий случай)
Область = ТабДок.Область("R1C1:R10C10");
Область.Защита = Истина;
ТабДок.ТолькоПросмотр = Истина;
// 2. Записываем файл
ИмяФайла = "C:\Reports\MyReport.xlsx";
ТабДок.Записать(ИмяФайла, ТипФайлаТабличногоДокумента.XLSX);
// 3. Дорабатываем файл через COM
Попытка
Эксель = Новый COMОбъект("Excel.Application");
Эксель.Visible = Ложь;
Эксель.DisplayAlerts = Ложь;
Книга = Эксель.Workbooks.Open(ИмяФайла);
// Выбираем нужный лист (например, второй)
Лист = Книга.Worksheets(2);
// Включаем защиту листа. Параметры: Пароль, ЗащитаСодержимого, ЗащитаОбъектов
Лист.Protect("ВашПароль", Истина, Истина);
Книга.Save();
Книга.Close();
Эксель.Quit();
Исключение
Сообщить("Ошибка при установке защиты Excel: " + ОписаниеОшибки());
Если Эксель <> Неопределено Тогда
Эксель.Quit();
КонецЕсли;
КонецПопытки;
Разберем по шагам: метод Protect активирует режим защиты, без которого свойство ячеек Locked (которое 1С обычно передает корректно) просто не учитывается программой Excel.
Если нам нужно выгружать данные в сложную структуру, где часть листов должна быть жестко заблокирована, мы можем пойти «от обратного». Проанализируем этот вариант: вместо создания нового файла каждый раз, мы используем готовый xlsx файл-шаблон.
Этот способ гарантирует, что все специфические настройки Excel (валидация данных, макросы, защита) останутся нетронутыми, так как 1С не пересоздает структуру файла, а лишь редактирует значения в ячейках.
Этот метод подходит для продвинутых разработчиков и случаев, когда на сервере не установлен Excel (например, сервер на Linux). Как известно, файл xlsx — это архив с набором XML-документов. Мы можем программно изменить содержимое этого архива.
Выясним причину отсутствия защиты: в файле xl/worksheets/sheet2.xml (отвечающем за второй лист) отсутствует тег <sheetProtection>. Пошаговый алгоритм выглядит так:
Записать().ЧтениеZipФайла для распаковки xlsx.<sheetProtection sheet="1" objects="1" scenarios="1" /> перед закрывающим тегом листа.xlsx.Это трудоемкий, но максимально «чистый» способ, не зависящий от внешнего ПО.
При заполнении табличного документа важно правильно указывать типы данных, чтобы Excel понимал их корректно. Рассмотрим пример заполнения из обсуждения на форуме, дополненный проверкой типов:
НомерСтроки = 2;
Для каждого СтрокаТаблицы Из Таблица Цикл
Для НомерКолонки = 1 По КоличествоКолонок Цикл
ТекущаяОбласть = ТабДок.Область("R" + Формат(НомерСтроки, "ЧГ=0") + "C" + Формат(НомерКолонки, "ЧГ=0"));
Значение = СтрокаТаблицы[НомерКолонки - 1];
ТекущаяОбласть.Текст = Значение;
// Установка формата в зависимости от типа данных в 1С
Если ТипЗнч(Значение) = Тип("Число") Тогда
ТекущаяОбласть.Формат = "ЧЦ=15; ЧДЦ=2; ЧРД=.; ЧРГ=' '; ЧГ=0";
ИначеЕсли ТипЗнч(Значение) = Тип("Дата") Тогда
ТекущаяОбласть.Формат = "ДФ=dd.MM.yyyy";
КонецЕсли;
// Попытка установить защиту на ячейку (сработает при последующем вызове Protect через COM)
ТекущаяОбласть.Защита = Истина;
КонецЦикла;
НомерСтроки = НомерСтроки + 1;
КонецЦикла;
Обратите внимание на использование функции Формат(..., "ЧГ=0"). Это критично при формировании адреса ячеек (например, "R10C2"), так как стандартное строковое представление числа в 1С может содержать неразрывные пробелы (например, "1 0"), что приведет к ошибке обращения к области табличного документа.
Для того чтобы успешно заблокировать данные при выгрузке в Excel, недостаточно использовать только внутренние инструменты 1С Защита и ТолькоПросмотр. Основное решение — программная активация защиты листа через COM-объект Excel.Application после записи файла. Если же использование COM невозможно, следует рассмотреть вариант с использованием OpenXML или шаблонов с заранее настроенной защитой.