Фиксация колонок при сохранении Excel файла из 1С

22.06.23

Разработка - Механизмы платформы 1С

Решение давно наболевшего вопроса о потере фиксации колонок при сохранении табличного документа в 1С штатными средствами.

Скачать файл

ВНИМАНИЕ: Файлы из Базы знаний - это исходный код разработки. Это примеры решения задач, шаблоны, заготовки, "строительные материалы" для учетной системы. Файлы ориентированы на специалистов 1С, которые могут разобраться в коде и оптимизировать программу для запуска в базе данных. Гарантии работоспособности нет. Возврата нет. Технической поддержки нет.

Наименование SM По подписке [?] Купить один файл
Фиксация колонок при сохранении Excel файла из 1С.:
.cfe 8,51Kb
3
3
1 SM
Скачать Купить за 1 850 руб.

Открывая отчет в 1С, мы можем насладиться многими удобствами просмотра, среди которых и фиксирование шапки отчета, которое дает понимание, какие данные мы видим в ячейках. Но как только мы добавляем отчет в рассылку, большинство удобств пропадает. И если отсутствие расшифровки и возможности сразу открыть документ понятно, то пропажа фиксации шапки отчета не выглядит логичным в тех вариантах, где это должно работать. И тут каждый начинает искать решение по форумам и после изобретает свой велосипед. Сегодня рассмотрим два велосипеда, работающих с самыми распространенным форматом Excel.

 

 

 

Вариант первый. Самый распространенный

Вариант требует установленного Excel на компьютере, на котором будет выполняться. Из-за запуска всего приложения Excel работает медленнее и при не корректном кодировании может приводить к невозможности выполнения операций и пожиранию им памяти. Плюс этого варианта в том, что он работает с форматами XLS и XLSX. Кроме того, на сервере требует дополнительной настройки безопасности. Инструкция по настройке. 

Функция, реализующая данный алгоритм.
 

// Добавляет в файл XLSX фиксацию колонок с помощью "Excel.Application"
//
// Параметры:
//  ИмяФайла  - Строка - Имя обрабатываемого файла
//  ФиксацияСверху  - Число - Количество зафиксированных строк
//  ФиксацияСлева  - Число - Количество зафиксированных колонок
//
Процедура ФиксацияШапкиЛистаXlsxExcel(ИмяФайла, Знач ФиксацияСверху, Знач ФиксацияСлева) Экспорт
	
	Попытка
		
		ФиксЭ_Эксель = Новый COMОбъект("Excel.Application");
		ФиксЭ_Эксель.MergeInstances		 = Ложь;
		ФиксЭ_Эксель.DisplayAlerts		 = Ложь;
		ФиксЭ_Эксель.DefaultSaveFormat	 = 50;
		
		ФиксЭ_Книга = ФиксЭ_Эксель.Workbooks.Open(ИмяФайла);
		
		ФиксЭ_Окно				 = ФиксЭ_Книга.Windows(1);
		ФиксЭ_Окно.SplitRow		 = ФиксацияСверху;
		ФиксЭ_Окно.SplitColumn	 = ФиксацияСлева;
		ФиксЭ_Окно.FreezePanes	 = Истина;
		
		ФиксЭ_Книга.Save();
		ФиксЭ_Книга.Close();
		
		ФиксЭ_Окно	 = Неопределено;
		ФиксЭ_Книга	 = Неопределено;
		ФиксЭ_Эксель.Quit();
		ФиксЭ_Эксель = Неопределено;
		
	Исключение
		
		ФиксЭ_Окно	 = Неопределено;
		ФиксЭ_Книга	 = Неопределено;
		Если ФиксЭ_Эксель <> Неопределено Тогда 
			ФиксЭ_Эксель.Quit();
		КонецЕсли;
		ФиксЭ_Эксель = Неопределено;
		
		ФиксЭ_СтруктураСобытий = Новый Структура("ИмяСобытия, ПредставлениеУровня, Комментарий, ДатаСобытия");
		
		ФиксЭ_СтруктураСобытий.ИмяСобытия 			 = "Не возможно установить фиксацию колонок";
		ФиксЭ_СтруктураСобытий.ПредставлениеУровня	 = "Предупреждение";
		ФиксЭ_СтруктураСобытий.Комментарий 			 = "Не возможно установить фиксацию колонок, возможно не установлен Excel на сервере";
		ФиксЭ_СтруктураСобытий.ДатаСобытия 			 = ТекущаяДата();
		
		ФиксЭ_СобытияДляЖурналаРегистрации = Новый СписокЗначений();
		ФиксЭ_СобытияДляЖурналаРегистрации.Добавить(ФиксЭ_СтруктураСобытий);
		
		ЖурналРегистрации.ЗаписатьСобытияВЖурналРегистрации(ФиксЭ_СобытияДляЖурналаРегистрации);
		
	КонецПопытки;

КонецПроцедуры // ФиксацияШапкиЛистаXlsxExcel()

В параметрах передаются полный путь к сохраненному файлу, и сколько строк/столбцов зафиксировать.

Список констант для свойства "DefaultSaveFormat".

 

Вариант второй. Редкий, но меткий

Этот вариант работает только с форматом XLSX. Не требует дополнительных настрое и программ. Использует особенность формата XLSX, в том, что он является zip-архивом XML-файлов. Возможность работы с zip-архивами в самой платформе 1C, а для удобства работы с XML-файлами используем компонент системы Msxml2.DOMDocument. Это не потребует запуска тяжелого приложения Excel, и вообще его наличия на компьютере. Исполняется быстрее, и при ошибках влечет меньшие последствия.

Будем напрямую редактировать нужный нам XML-файл, после разархивирования файла XLSX он находится по относительному пути "\xl\worksheets\sheet1.xml". Нас интересует нода "sheetView". В нее нам нужно добавить ноду "pane" с атрибутами для фиксации шапки. Атрибуты "xSplit" и "ySplit" содержат количество фиксируемых колонок и строк. Атрибут "topLeftCell" содержит адрес первой не зафиксированной ячейки, адрес должен быть в формате "А3", "С6", при указании ячейки в формате "RxCx" Excel при открытии файла ругается на ошибки формата.

Вот и сама функция этого алгоритма.

// Добавляет в файл XLSX фиксацию колонок с помощью "Msxml2.DOMDocument"
//
// Параметры:
//  ИмяФайла  - Строка - Имя обрабатываемого файла
//  ФиксацияСверху  - Число - Количество зафиксированных строк
//  ФиксацияСлева  - Число - Количество зафиксированных колонок
//
Процедура ФиксацияШапкиЛистаXlsxXml(ИмяФайла, Знач ФиксацияСверху, Знач ФиксацияСлева) Экспорт

	Каталог = ПолучитьИмяВременногоФайла("");
	
	Попытка
		
		// Разархивируем XSLX
		ФайлZIP = Новый ЧтениеZipФайла(ИмяФайла);
		ФайлZIP.ИзвлечьВсе(Каталог);
		
		// Путь до файла с параметрами фиксации
		ПутьФайла = Каталог + "\xl\worksheets\sheet1.xml";
		
		// Читаем XML документ
		ХМЛДокумент = Новый COMОбъект("Msxml2.DOMDocument");
		ХМЛДокумент.validateOnParse = true;  
		ХМЛДокумент.load(ПутьФайла);
		
		// Схема по умолчанию
		XMLNS = ХМЛДокумент.selectSingleNode("//worksheet").getAttribute("xmlns");
		
		// Получаем родительскую ноду ноду документа для параметров фиксации
		ХМЛНоде = ХМЛДокумент.selectSingleNode("//sheetView");
		
		// Создаем ноду с параметрами фиксации
		НоваяХМЛНода = ХМЛДокумент.createNode(1, "pane", XMLNS);
		
		// Фиксация по горизонтали
		ФиксацияСлева = ?(ФиксацияСлева > 25, 25, ФиксацияСлева); 
		Если ФиксацияСлева > 0 Тогда
			НовыйХМЛАттрибут = ХМЛДокумент.createNode(2, "xSplit", XMLNS);
			НовыйХМЛАттрибут.value = Строка(ФиксацияСлева);
			НоваяХМЛНода.setAttributeNode(НовыйХМЛАттрибут);
		КонецЕсли;
		
		// Фиксация по вертикали
		Если ФиксацияСверху > 0 Тогда
			НовыйХМЛАттрибут = ХМЛДокумент.createNode(2, "ySplit", XMLNS);
			НовыйХМЛАттрибут.value = Строка(ФиксацияСверху);
			НоваяХМЛНода.setAttributeNode(НовыйХМЛАттрибут);
		КонецЕсли;
		
		// Фиксация ячейка
		БуквыДляКонвертации = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; 
		БукваКолонки = Сред(БуквыДляКонвертации, ФиксацияСлева + 1, 1);
		АдресЯчейки = Сред(БуквыДляКонвертации, ФиксацияСлева + 1, 1) + Строка(ФиксацияСверху + 1);
		НовыйХМЛАттрибут = ХМЛДокумент.createNode(2, "topLeftCell", XMLNS);
		НовыйХМЛАттрибут.value = АдресЯчейки;
		НоваяХМЛНода.setAttributeNode(НовыйХМЛАттрибут);
		
		// Фиксация направление
		НовыйХМЛАттрибут = ХМЛДокумент.createNode(2, "activePane", XMLNS);
		НовыйХМЛАттрибут.value = "bottomLeft";
		НоваяХМЛНода.setAttributeNode(НовыйХМЛАттрибут);
		
		// Фиксация включена
		НовыйХМЛАттрибут = ХМЛДокумент.createNode(2, "state", XMLNS);
		НовыйХМЛАттрибут.value = "frozen";
		НоваяХМЛНода.setAttributeNode(НовыйХМЛАттрибут);
		
		// Добавляем в родительскую ноду подготовленную ноду с параметрами фиксации
		ХМЛНоде.appendChild(НоваяХМЛНода);
		
		ХМЛДокумент.save(ПутьФайла);
		
		// Архивируем XLSX
		ФайлZIP = Новый ЗаписьZipФайла(ИмяФайла, , , МетодСжатияZIP.Сжатие);
		ФайлZIP.Добавить(Каталог + "\[Content_Types].xml", РежимСохраненияПутейZIP.СохранятьОтносительныеПути, РежимОбработкиПодкаталоговZIP.ОбрабатыватьРекурсивно);
		ФайлZIP.Добавить(Каталог + "\xl\", РежимСохраненияПутейZIP.СохранятьОтносительныеПути, РежимОбработкиПодкаталоговZIP.ОбрабатыватьРекурсивно);
		ФайлZIP.Добавить(Каталог + "\_rels\", РежимСохраненияПутейZIP.СохранятьОтносительныеПути, РежимОбработкиПодкаталоговZIP.ОбрабатыватьРекурсивно);
		ФайлZIP.Записать();
		
		УдалитьФайлы(Каталог);
		
	Исключение
		УдалитьФайлы(Каталог);
	КонецПопытки;
	
КонецПроцедуры // ФиксацияШапкиЛистаXlsxXml()

Параметры такие же, как и в первом варианте.

 

А что же у нас с рассылкой отчетов

Для того, чтобы это работало при рассылке отчетов, нам нужно добавить код в функцию "ПередСохранениемТабличногоДокументаВФормат" модуля "РассылкаОтчетовПереопределяемый". Или добавить ее в расширение.

&После("ПередСохранениемТабличногоДокументаВФормат")
Процедура ФиксЭ_ПередСохранениемТабличногоДокументаВФормат(СтандартнаяОбработка, ТабличныйДокумент, Формат, ПолноеИмяФайла)
	
	Если ОбщегоНазначения.ЭтоWindowsСервер() И Формат = Перечисления.ФорматыСохраненияОтчетов.XLSX И (ТабличныйДокумент.ФиксацияСверху > 0 Или ТабличныйДокумент.ФиксацияСлева > 0) Тогда
		
		ТабличныйДокумент.Записать(ПолноеИмяФайла, ТипФайлаТабличногоДокумента.XLSX);
		ФиксЭ_ОбработкаФайловЭксель.ФиксацияШапкиЛистаXlsxXml(ПолноеИмяФайла, ТабличныйДокумент.ФиксацияСверху, ТабличныйДокумент.ФиксацияСлева);
		СтандартнаяОбработка = Ложь;
		
	КонецЕсли;
	
КонецПроцедуры

 

Вот теперь стало выглядеть лучше

 

 

Расширение

В расширении реализованы оба эти варианта решения в модуле "ФиксЭ_ОбработкаФайловЭксель". И переопределение процедуры сохранения отчета в формате XLSX. Расширение реализовано для 1С:ERP Управление предприятием 2. Проверено на версии  2.5.10.52, 2.5.12.53.

В остальных типовых конфигурациях, содержащих подсистему "Рассылка отчетов", реализуется аналогично.

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

фиксация колонок Excel

См. также

SALE! 20%

Загрузка и выгрузка в Excel Оптовая торговля Печатные формы Бухгалтер Пользователь Платформа 1С v8.3 Управляемые формы Платформа 1C v8.2 Конфигурации 1cv8 1С:Комплексная автоматизация 1.х 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление производственным предприятием 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Универсальная обработка для загрузки документов из Excel в 1С одним нажатием. Не требует указания параметров (номера колонок, номер первой строки таблицы и т.д.) и предварительной настройки. Просто выбираете файл Excel, документ 1С и нажимаете кнопку "Загрузить". Обработка сама находит таблицу в файле Excel, необходимые для загрузки данные в ней (номенклатура, количество, НДС, цена, сумма) и загружает ее в 1С. Вместе с номенклатурой может найти контрагента, номер и дату документа, штрих-коды, серии ГТД, страну и т.д. Распознает документы ЛЮБОЙ ФОРМЫ (УПД, ТОРГ-12, заказ, отчет комиссионера и т.д.). Не требует MS Office. Для поиска таблиц используются методы эвристического поиска. Загружает только то, что нужно, т.е. пропускает повторы шапки таблицы, заголовки, промежуточные итоги, подписи и т.д. Содержит модуль работы с электронной почтой и api-загрузчик отчетов о продажах маркетплейсов.

5000 4000 руб.

09.11.2016    222964    963    891    

970

Загрузка и выгрузка в Excel Маркетплейсы Программист Бухгалтер Пользователь Платформа 1С v8.3 Бухгалтерский учет 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Россия Бухгалтерский учет Управленческий учет Платные (руб)

Реальный помощник, с помощью которого Вы сделаете необходимые документы для Wildberries, OZON, ЯндексМаркет, Мегамаркет, Aliexpress, "Детский мир", МагнитЭкспресс (бывш.Казань-Экспресс), "Леруа Мерлен", ЭНФАНТА (Акушерство), ЛаМода, Летуаль, "Твой дом" в документы "Отчет комиссионера (агента) о продажах" и другие, может работать в "Бухгалтерия 3", "Бухгалтерия 3 КОРП", УТ 11, УНФ, КА 2, ERP. Возможность подключить любые маркетплейсы. Анализ продаж ОЗОН. 30 дней БЕСПЛАТНОГО пользования!

1800 руб.

12.08.2021    33800    302    65    

144

Маркетплейсы Загрузка и выгрузка в Excel Программист Пользователь Платформа 1С v8.3 1С:Управление торговлей 10 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Управление нашей фирмой 3.0 1С:Розница 3.0 Управленческий учет Платные (руб)

Загрузка данных отчета о реализации товаров из сервиса Яндекс.Маркета «Беру» в 1С из Отчетов Excel для конфигурации: Бухгалтерия предприятия, редакция 3.0; Управление торговлей, редакция 11; Управление торговлей, редакция 10.3; Управление нашей фирмой, редакция 3.0 и Розница, редакция 3.0 в документ «Отчет комиссионера о продажах».

4800 руб.

09.12.2020    24023    231    1    

96

Загрузка и выгрузка в Excel Маркетплейсы Программист Бухгалтер Пользователь Платформа 1С v8.3 1С:Управление торговлей 10 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Управление нашей фирмой 3.0 1С:Розница 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Обработки загрузки данных о продажах Ozon предназначены для следующих конфигураций: Бухгалтерия предприятия, редакция 3.0; Управление нашей фирмой, редакция 3.0; Розница, редакция 3.0; Управление торговлей, редакция 11; Управление торговлей, редакция 10.3

4800 руб.

26.11.2020    38907    673    4    

192

Загрузка и выгрузка в Excel Маркетплейсы Программист Бухгалтер Пользователь Платформа 1С v8.3 1С:Управление торговлей 10 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Управление нашей фирмой 3.0 1С:Розница 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Загрузка данных отчета о реализации товаров из сервиса СберМегаМаркет для конфигурации: Бухгалтерия предприятия, редакция 3.0; Управление торговлей, редакция 11; Управление торговлей, редакция 10.3; Управление нашей фирмой, редакция 3.0 и Розница, редакция 3.0 в документ "Отчет комиссионера (агента) о продажах".

4800 руб.

07.06.2022    16367    100    0    

78

SALE! 30%

Загрузка и выгрузка в Excel Документооборот и делопроизводство (СЭД) Учет документов Распознавание документов и образов Бухгалтер Пользователь Управляемые формы 1С:Комплексная автоматизация 1.х 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление производственным предприятием 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Управление холдингом 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Универсальная программа для распознавания ЛЮБЫХ СКАНОВ ИЛИ ФОТО ТОВАРНЫХ ДОКУМЕНТОВ в 1С. Не требует указания параметров и предварительной настройки. Просто выбираете файл (PDF, JPG, DOC, XLS, HTML и т.д.) выбираете документ 1С и нажимаете кнопку "Распознать и загрузить".

6000 5520 руб.

04.06.2019    103727    304    173    

318

Загрузка и выгрузка в Excel Розничная торговля Логистика, склад и ТМЦ Ценообразование, анализ цен Прайсы Системный администратор Программист Платформа 1С v8.3 1С:Комплексная автоматизация 1.х 1С:Розница 2 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Управленческий учет Платные (руб)

Загрузка номенклатуры из файлов Excel (xls, xlsx, ods, csv, mxl) в УТ11, КА 2, ERP 2, Розница 2. Задействованы все возможности конфигурации - заполнение реквизитов номенклатуры, дополнительных реквизитов и сведений, характеристики, доп.реквизиты и сведения характеристик. Дополнительные обработки для расширения возможностей.

10560 руб.

29.10.2014    214657    637    526    

452
Оставьте свое сообщение