Делаем формат ячейки дата в Excel без Excel

26.03.21

Интеграция - Загрузка и выгрузка в Excel

Задача отправлять печатную форму в формате Excel, но у этой печатной формы должен быть правильный формат ячеек с датой

При сохранении печатной формы из 1с в Excel ячейки с датами сохраняются как общий формат. И это никак не изменить настройками макета печатной формы. 

Сначала решил сделать задачу с помощью Excel установленного на сервере 1с, но через некоторое время Excel на сервере начал зависать, потому решил отказаться от такой реализации.

 

 

Процедура СделатьФорматЯчеекДатаВЕкселе2(ПутьКФайлу) Экспорт
	
	Попытка
		Excel = Новый COMОбъект("Excel.Application");
		Excel.Visible = Ложь;
		Excel.DisplayAlerts = Ложь;
		КнигаExcel = Excel.WorkBooks.Open(ПутьКФайлу);
		
		НомерЛиста = 1;
		КнигаExcel.Sheets(НомерЛиста).Range(КнигаExcel.Sheets(НомерЛиста).Cells(2,2),КнигаExcel.Sheets(НомерЛиста).Cells(2,3)).NumberFormat = "ДД.ММ.ГГГГ"; 
		КнигаExcel.Sheets(НомерЛиста).Cells(2, 6).NumberFormat = "ДД.ММ.ГГГГ";
		КнигаExcel.Sheets(НомерЛиста).Cells(2, 10).NumberFormat = "ДД.ММ.ГГГГ";
		КнигаExcel.Save();
		
		КнигаExcel.Close();
		Excel.DisplayAlerts = Истина;
		Excel.Quit();
		Excel = Неопределено;
	Исключение
		Ошибка = ОписаниеОшибки();
		g = 1;
	КонецПопытки;	

КонецПроцедуры

 

Решил пойти другим путем. 

Файл xlsx - это набор xml-файлов в zip-архиве.

1. Для начала нужно распаковать файл в папку на диске.

 

 

ПутьВыгрузки = КаталогВременныхФайлов()+"excel\"+Строка(Новый УникальныйИдентификатор);
Зип = Новый ЧтениеZipФайла;
Зип.Открыть(ПутьКФайлу);
Зип.ИзвлечьВсе(ПутьВыгрузки, РежимВосстановленияПутейФайловZIP.Восстанавливать);

 

2. Открываем xml и читаем с помощью ПостроительDOM.

 

 

ОбъектXML   = Новый ЧтениеXML;
ОбъектXML.ОткрытьФайл(ПолныйПуть);
ПостроительDOM = Новый ПостроительDOM;
ДокументDOM    = ПостроительDOM.Прочитать(ОбъектXML);
ОбъектXML.Закрыть();

3. Добавляем новый элемент с описанием формата дата в styles.xml. Также у элемента "cellXfs"  в атрибуте "count" нужно увеличить счетчик на 1.

 

Элементы1 = ДокументDOM.ПолучитьЭлементыПоИмени("cellXfs");
НужныйЭлемент = Элементы1[0];
ИндексXfs = НужныйЭлемент.Атрибуты.ПолучитьИменованныйЭлемент("count").ЗначениеУзла;
НужныйЭлемент.Атрибуты.ПолучитьИменованныйЭлемент("count").ЗначениеУзла=XMLСтрока(Число(ИндексXfs)+1);

Элемент = ДокументDOM.СоздатьЭлемент("xf");
Элемент.УстановитьАтрибут("numFmtId", "14");
Для Каждого Атр Из НужныйЭлемент.ПоследнийДочерний.Атрибуты Цикл
	Если Атр.Имя = "borderId" Тогда
		Элемент.УстановитьАтрибут("borderId", Атр.Значение);
	ИначеЕсли Атр.Имя = "fillId" Тогда
		Элемент.УстановитьАтрибут("fillId", Атр.Значение);
	ИначеЕсли Атр.Имя = "fontId" Тогда	
		Элемент.УстановитьАтрибут("fontId", Атр.Значение);
	ИначеЕсли Атр.Имя = "applyNumberFormat" Тогда	
		Элемент.УстановитьАтрибут("applyNumberFormat", Атр.Значение);	
	ИначеЕсли Атр.Имя = "xfId" Тогда	
		Элемент.УстановитьАтрибут("xfId", Атр.Значение);	
	ИначеЕсли Атр.Имя = "applyBorder" Тогда
		Элемент.УстановитьАтрибут("applyBorder", Атр.Значение);
	КонецЕсли;	
КонецЦикла;	
НужныйЭлемент.ДобавитьДочерний(Элемент);

Формат определяется по атрибуту numFmtId элемента xf

1 0
2 0.00
3 #,##0
4 #,##0.00
5 $#,##0_);($#,##0)
6 $#,##0_);[Red]($#,##0)
7 $#,##0.00_);($#,##0.00)
8 $#,##0.00_);[Red]($#,##0.00)
9 0%
10 0.00%
11 0.00E+00
12 # ?/?
13 # ??/??
14 m/d/yyyy
15 d-mmm-yy
16 d-mmm
17 mmm-yy
18 h:mm AM/PM
19 h:mm:ss AM/PM
20 h:mm
21 h:mm:ss
22 m/d/yyyy h:mm
37 #,##0_);(#,##0)
38 #,##0_);[Red](#,##0)
39 #,##0.00_);(#,##0.00)
40 #,##0.00_);[Red](#,##0.00)
45 mm:ss
46 [h]:mm:ss
47 mm:ss.0
48 ##0.0E+0
49 @

4. Значение всех текстовых ячеек хранится в файле sharedStrings.xml. Прочитаем все эти значения в переменную элементы_si потом будем получать из этой переменной значения по индексу, который хранится в значении ячейки в файле sheet1.xml.

 

ОбъектXML2   = Новый ЧтениеXML;
ОбъектXML2.ОткрытьФайл(СтрЗаменить(ПолныйПуть,"worksheets\sheet1","sharedStrings"));
ПостроительDOM2 = Новый ПостроительDOM;
ДокументDOM2    = ПостроительDOM2.Прочитать(ОбъектXML2);
ОбъектXML2.Закрыть();
Элементы_si = ДокументDOM2.ПолучитьЭлементыПоИмени("si");

 

5. Добавляю ячейки в которых нужно поменять формат в "Соответствие". Ключом в соответствии является номер строки, а значением массив со столбцами.

Находим нужные ячейки на "листе1" в файле sheet1.xml и меняем атрибут "s" на индекс значения формата из файла styles.xml

 

МассивЯчеек = Новый Массив();
МассивЯчеек.Добавить("B2");
МассивЯчеек.Добавить("C2");
МассивЯчеек.Добавить("F2");
МассивЯчеек.Добавить("J2");
МассивЯчеек2 = Новый Массив();
МассивЯчеек2.Добавить("B3");
МассивЯчеек2.Добавить("C3");

Параметры1 = Новый Соответствие;
Параметры1.Вставить("2", МассивЯчеек);
Параметры1.Вставить("3", МассивЯчеек2);


Элементы1 = ДокументDOM.ПолучитьЭлементыПоИмени("row");	
Для Каждого Эл1 Из Элементы1 Цикл
	МассивЯчеек = Параметры1.Получить(Эл1.Атрибуты.ПолучитьИменованныйЭлемент("r").ЗначениеУзла);
	Если МассивЯчеек <> Неопределено Тогда
		Для Каждого Эл2 Из Эл1.ДочерниеУзлы Цикл
			Если МассивЯчеек.Найти(Эл2.Атрибуты.ПолучитьИменованныйЭлемент("r").ЗначениеУзла) <> Неопределено Тогда
				Эл2.ПервыйДочерний.ТекстовоеСодержимое = XMLСтрока(ПоучитьЧислоЕксель(Дата(Элементы_si[Число(Эл2.ПервыйДочерний.ТекстовоеСодержимое)].ТекстовоеСодержимое+ " 00:00:00")));
				Эл2.Атрибуты.УдалитьИменованныйЭлемент("t");	
				
				Эл2.Атрибуты.ПолучитьИменованныйЭлемент("s").ЗначениеУзла=ИндексXfs;
			КонецЕсли;
		КонецЦикла;	
	КонецЕсли;		
КонецЦикла;	

 

6. Переносим значение из файла sharedStrings.xml в значение ячейки непосредственно в файл sheet1.xml. 

Но в файле sharedStrings.xml  хранится текстовое значение ДД.ММ.ГГГГ, его нужно преобразовать к числу в котором хранится дата в Excel.

Удаляем атрибут "t", который обозначает что нужно искать значение в файле sharedStrings.xml по индексу, который хранился в значении ячейки.

 

До изменений

после

Эл2.ПервыйДочерний.ТекстовоеСодержимое = XMLСтрока(ПоучитьЧислоЕксель(Дата(Элементы_si[Число(Эл2.ПервыйДочерний.ТекстовоеСодержимое)].ТекстовоеСодержимое+ " 00:00:00")));
Эл2.Атрибуты.УдалитьИменованныйЭлемент("t");

Функция ПоучитьЧислоЕксель(Дата2)
	Возврат ((Дата2-'19000101000000')/86400) + 2;
КонецФункции

 

7. Записываем изменения в файлах и запаковываем все обратно в zip.

 

 

ЗаписьXML = Новый ЗаписьXML;
ЗаписьXML.ОткрытьФайл(ПолныйПуть);
ЗаписьDOM = Новый ЗаписьDOM;
ЗаписьDOM.Записать(ДокументDOM, ЗаписьXML);
ЗаписьXML.Закрыть();

ЗаписьZIP = Новый ЗаписьZipФайла();
ЗаписьZIP.Открыть(ПутьКФайлу);
ЗаписьZIP.Добавить(ПутьВыгрузки+"\*", РежимСохраненияПутейZIP.СохранятьОтносительныеПути, РежимОбработкиПодкаталоговZIP.ОбрабатыватьРекурсивно);		
ЗаписьZIP.Записать();

 

Процедуры одинаково работают, как на клиенте так и на сервере

 
Полный текст процедур

Тестировал: 

1С:Комплексная автоматизация 2 (2.4.9.98)

Платформа 1С:Предприятие 8.3 (8.3.16.1148)

excel xml

См. также

SALE! 10%

Загрузка номенклатуры из Excel в УТ11, КА 2, ERP 2, Розница 2. Дополнительные реквизиты и сведения, характеристики, картинки, цены, остатки

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

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

10560 9504 руб.

29.10.2014    210180    620    524    

439

Загрузка номенклатуры c картинками (несколько потоков одновременно) и сопутствующими данными в базу и любые документы из yml, xls, xlsx, xlsm, ods, ots, csv для УТ 10.3, УТ 11 (все), БП 3, КА 2, ERP 2, УНФ 1.6/3.0, Розница 2

Загрузка и выгрузка в Excel Логистика, склад и ТМЦ Ценообразование, анализ цен Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Платные (руб)

Эволюция не стоит на месте - новая удобная версия функциональной обработки для Вашего бизнеса! Что же Вы получаете? Удобный и интуитивно понятный интерфейс с 3-мя этапами работы. 2 режима - автоматический и ручной. Чтение XLSX, XLSM, CSV, XML/YML форматов без офиса, на любом сервере! Визуальное связывание колонок файла и реквизитов простым перетаскиванием колонок. Создание или обновление номенклатуры с иерархией, характеристик, доп. реквизитов, упаковок, загрузка практически неограниченного количества картинок на одну номенклатуру (с возможностью загрузки в несколько потоков одновременно), с хранением в томах или в базе. Загрузка номенклатуры поставщиков или поиск по их данным номенклатуры. Загрузка доп. реквизитов в характеристики. Загрузка штрихкодов с генерацией новых. Создание элементов справочников и ПВХ "на лету" для выбранных реквизитов. (Обновление от 11.12.2023, версия 9.5 - 9.9)

13200 руб.

20.11.2015    150726    367    375    

501

Маркетплейсный загрузчик для 12-ти маркетплейсов в "БП 3", "УТ 11", "КА 2", ERP, УНФ

Загрузка и выгрузка в 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    31894    226    63    

117

SALE! 20%

Загрузка документов и номенклатуры из Excel в 1С "одним нажатием": УПД, ТОРГ-12, отчеты маркетплейсов, заказы, счета, прайсы

Загрузка и выгрузка в 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    214930    925    886    

939

Загрузка данных отчета о реализации товаров из Excel файла СберМегаМаркет

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

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

4800 руб.

07.06.2022    15080    79    0    

60

SALE! 30%

Распознавание и загрузка сканов в 1С "одним нажатием": УПД, ТОРГ-12, накладные, счета, номенклатура, заказы и т.д.

Загрузка и выгрузка в 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    101380    296    173    

312
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. &rew 49 26.03.21 11:34 Сейчас в теме
Нравятся такие НИОКРы. Вряд ли в своей деятельности буду использовать, но сама идея работы с Excel файлом через XML-ские плюхи 1Са интересна.
DERL; Fox-trot; cleaner_it; +3 Ответить
15. Yashazz 4709 30.03.21 13:39 Сейчас в теме
(1) Ей сто лет в обед, этой идее. А автор, к сожалению, не упомянул сие, из-за чего у некоторых может сложиться ощущение, что это его, автора, личное открытие.
2. slknnk 65 26.03.21 11:37 Сейчас в теме
Отличная идея, берём на заметку.
4. Serginio 938 26.03.21 12:13 Сейчас в теме
На заметку Вместо Excel.Application можно использовать OpenXml

https://infostart.ru/1c/articles/544232/
5. Petr54-ru 90 26.03.21 12:41 Сейчас в теме
У меня года четыре была задача, я ее не смог допинать из за политики.

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

Обращаться на сервере к MS Excel - оно не успевало за ночь формировать прайсы, надо было это делать без экселя - фомировать текстовй файл в виде xml, а потом его зиповать. Майкрософты кстати не рекомендуют использовать Excel в не интерактивном режиме.

Мануал тут

Кто запилит толковый гуайд по работе с экселем без экселя с красивым оформлением ячеек и прочими экселевскми свистелками тот прославится в веках.
6. LavinVladik 214 26.03.21 15:01 Сейчас в теме
7. xlmel 26.03.21 15:02 Сейчас в теме
NumberFormat = "ДД.ММ.ГГГГ"
А разве можно русскими символами задавать формат числа в Excel?
8. AnryMc 849 26.03.21 17:35 Сейчас в теме
(7)
А разве можно русскими символами задавать формат числа в Excel?


Английскими можно всегда.
Русскими - если есть локализация, т.е. если открыть в чисто английском - не сработает.

P.S. в разделе «Язык и региональные стандарты» параметр «Языковые стандарты и форматы»
9. xlmel 26.03.21 18:49 Сейчас в теме
(8)
я, честно говоря, не думал, что Майкрософт будет заморачиваться и в СОМ-объекте делать числовые форматы в зависимости от локализации. Во всяком случае, макрос, записанный в в русской версии офиса, добавляет такой формат NumberFormat = "[$-ru-Ru,1]dd.mm.yyyy;@".
10. jimli 6 28.03.21 20:09 Сейчас в теме
Классная идея! А так же гиперссылки можно прописывать?
Изначально невозможно сохранение файлов с гиперссылкой в ячейке...
11. John_d 5277 29.03.21 10:27 Сейчас в теме
(10) да. Пример в ячейке E2 находится текст в виде гиперссылки (но ссылка не кликается)
1. в файле styles.xml добавить элемент xf с атрибутом numFmtId="1" и xfId="1"
2. в файле sheet1.xml добавить элемент hyperlinks который содержит список элементами hyperlink с атрибутом ref="E2"
3. в файле sheet1.xml в ячейке прописать, чтобы атрибут s="" ссылался на новый элемент из п.1
Прикрепленные файлы:
12. John_d 5277 29.03.21 12:01 Сейчас в теме
(11) Перемудрил. 1 и 3 пункт не нужны
13. Serginio 938 29.03.21 14:45 Сейчас в теме
14. Yashazz 4709 30.03.21 12:08 Сейчас в теме
Идея баян, конечно, а главное, ненадёжна. Вот автор пишет:
Записываем изменения в файлах и запаковываем все обратно в zip

и... и потом нихрена в экселе не открывается. Точнее, в 20% случаев открывается, а вот в остальных - какое настроение будет у экселя, так и пойдёт. Поэтому я подобную механику, лет 6 назад сделанную, даже публиковать не стал, слишком вероятны грабли.
16. Yashazz 4709 30.03.21 13:43 Сейчас в теме
На самом деле, у меня есть подозрения, что упаковка стандартным zip-упаковщиком 1С делает нечто немножко не то, что ожидает Эксель. То ли уровень сжатия, то ли метод шифрования, то и в самом упаковщике что-то, то ли разрядность... Я в своё время убил неделю, пытаясь угадать, на 2010-м офисе, но увы. Так что идея-то хороша, а вот воплощение взлетает редко.
17. John_d 5277 30.03.21 16:27 Сейчас в теме
(16) все нормально работает. Протестировано и внедрено в продакшен базу.
Нужно делать zip с методом сжатия = без сжатия. Ексель ожидает zip без сжатия.
18. bulldog 31.03.21 08:02 Сейчас в теме
(17) сжатие явно есть, откройте архиватором и посмотрите на размеры файлов
19. Yashazz 4709 31.03.21 08:59 Сейчас в теме
(17) Значит, вам крупно повезло и, может, будет везти до какого-нибудь обновления офиса. Вы ведь даже не указали в статье самое основное - какой версии эксель, какой состав дистрибутива, какая разрядность. Но делать на этой зыбкой почве продакшен и тем более публиковать на ИС я б поостерёгся. У меня полно интересных решений, которые я не выкладываю исключительно потому, что они не везде и не всегда срабатывают, а вы, видимо, считаете это нормой.
21. John_d 5277 08.04.21 17:26 Сейчас в теме
(19) Есть задача и ее надо решить. Ваше решение?
Excel = Новый COMОбъект("Excel.Application");
23. Yashazz 4709 26.04.21 13:19 Сейчас в теме
(21) Решение - это прежде всего надёжность у клиента. Опубликованный тут вариант надёжным решением назвать нельзя. Точка.
20. user1045404 166 01.04.21 12:14 Сейчас в теме
Использую у себя в работе. Проблем нет. Openxml работает хорошо.
22. user722229 23.04.21 14:32 Сейчас в теме
мне кажется с одним стилем вы не совсем правы-вы берете в таблице стилей последний, копируете его и изменяете его формат на нужный. Но ведь копируются и другие элементы стиля-заливка, рамки. А ведь могут быть несколько разных колонок с типом дата, но с разными стилями рамок, заливок и т.д. Этот момент переделал, а все остальное очень полезно оказалось, спасибо!
24. valex1c 26.03.24 20:52 Сейчас в теме
Оставьте свое сообщение