Для решения проблемы можно например использовать EXCEL через ком объект, но это замедляет работу. Да и сам Microsoft не рекомендует использовать ком объект EXCEL в серверных приложениях.
Можно напрямую писать весь документ, лист, в формат Office Open XML, работая как с xml файлом. Но тогда нужно в нем разбираться, понимать, знать. А самое главное поддерживать, вносить изменения, новые разработчики должны тоже уметь и т.д.
В данной статье предлагается простое универсальное решение:
1.Число
Тут все просто.
Если нужно, чтобы при сохранении у ячейки табличного документа, в файле excel было число, делаем так:
Область = ТабличныйДокумент.Область("R1C1:R1C1");
Область.СодержитЗначение = Истина;
Область.ТипЗначения = Новый ОписаниеТипов("Число");
Область.Значение = 2;//Число которое нужно сохранить
2. Дата, Дата время.
Тут сложнее, предлагается способ с небольшой пост обработкой стилей xlsx файла, универсальным методом.
Ниже подробнее, по шагам рассмотрим весь способ.
2.1 Формат дата в EXCEL.
На самом деле в EXCEL, на уровне хранения данных, нет понятия даты или времени.
Все хранится как число, обозначающее количество дней с 1 января 1900 г.
Пример:
0 - Пустая дата
1 - 01.01.1900
2 - 02.01.1900
И т.д.
А понимает EXCEL что это дата или время и т.д., с помощью форматных строк.
У ячеек есть понятие формата.
Из пункта 1, мы умеем сохранять формат число , через Табличный документ.
Таким образом реализовав простой метод, который будет конвертировать дату 1С, в число по стандартам EXCEL.
Мы можем в момент формирования Табличного документа, сразу писать xlsx файл как нужно.
И делать это все привычными средствами 1С, пример:
ДатаДляЗаписи=Строка(ТекущаяДата());
Область = ТабличныйДокумент.Область("R1C1:R1C1");
Область.СодержитЗначение = Истина;
Область.ТипЗначения = Новый ОписаниеТипов("Число");
Область.Значение = ПреобразоватьДатуСтрокойВФорматEXCEL(ДатаДляЗаписи);//Запишем в файл excel дату в виде числа
Остается только указать нужный нам формат даты.
Для этого мы будем использовать Имя шрифта.
Так как оно будет записано в файл стилей, как обычная строка.
При этом мы запомним, какие шрифты, на какие форматы мы заменили, что бы потом обратно сделать подмену.
Дополним наш код выше примером:
Формат="dd\.mm\.yyyy\ hh:mm:ss";//Именно в таком формате у нас будет отображаться дата, пользователю. в файле excel,
СоответствиеФорматаИмениШрифта=Новый Соответствие;
СоответствиеФорматаИмениШрифта.Вставить(Формат,Область.Шрифт.Имя);
Область.Шрифт = Новый Шрифт(Область.Шрифт,Формат);
2.2 Универсальное редактирование стилей в файле XLSX.
Все стили у формата Office Open XML хранятся в xl\styles.xml.
После пункта 2.1, наша ячейка будет ссылаться на стиль, который будет записан с именем шрифта, равным нашей форматной строке.
Нам остается лишь:
-Тем стилям которые ссылаются на такой шрифт, указать форматную строку.
-Вернуть корректное имя шрифта.
Это можно обернуть в универсальный метод:
УстановитьФорматДатыВремяУЯчеекЧерезСтили(ПутьДоФайлаXLSX,СоответствиеФорматаИмениШрифта);
Внутри которого реализована модификация xl\styles.xml
Под спойлером пример кода, с реализацией всех методов, можно использовать по лицензии MIT.
Для запуска примера можно:
1.Создать внешнюю обработку.
2.Добавить форму, в модуль формы скопировать весь код под спойлером.
3.Добавить команду и на ее вызов повесить СформироватьПример().
4.По пути "C:\Пример.xlsx" будет создан пример xlsx файла из табличного документа, с форматами.
Для удобства можно объявить все как "НаКлиенте".
Цель кода показать пример, сам код каждый может изменить под себя:
// Процедура - Извлечь архив ZIPФормата
// Извлекает переданный zip архив в нужную папку
// Параметры:
// ПутьДоФайлаАрхива - Строка - путь до архива который нужно извлечь
// ПутьКудаИзвлечь - Строка - куда нужно извлечь архив
//
Процедура ИзвлечьАрхивZIPФормата(ПутьДоФайлаАрхива,ПутьКудаИзвлечь) Экспорт
ДвоичныеДанныеОригинальногоФайла=Новый ДвоичныеДанные(ПутьДоФайлаАрхива);
Поток = Новый ПотокВПамяти(ПолучитьБуферДвоичныхДанныхИзДвоичныхДанных(ДвоичныеДанныеОригинальногоФайла));
Архив = Новый ЧтениеZipФайла(Поток);
Архив.ИзвлечьВсе(ПутьКудаИзвлечь, РежимВосстановленияПутейФайловZIP.Восстанавливать);
Архив.Закрыть();
Поток.Закрыть();
КонецПроцедуры
// Процедура - Собрать zip архив
// Упаковывает нужную папку в архив
// Параметры:
// ПутьДоПапапки - Строка - Путь до папки которую нужно упаковать
// ПутьДоФайла - Строка - Путь куда нужно собрать архив
//
Процедура СобратьZipАрхив(ПутьДоПапапки,ПутьДоФайла) Экспорт
АрхивXLSXРезультат = Новый ЗаписьZipФайла(ПутьДоФайла);
АрхивXLSXРезультат.Добавить(ПутьДоПапапки+"\*.*", РежимСохраненияПутейZIP.СохранятьОтносительныеПути, РежимОбработкиПодкаталоговZIP.ОбрабатыватьРекурсивно);
АрхивXLSXРезультат.Записать();
КонецПроцедуры
// Функция - Получить DOMДокумент из XMLФайла
// Возвращает указанный xml файл в виде дом модели
// Параметры:
// ПутьДоXMLФайла - Строка - путь до xml файла который нужно получить как дом документ
//
// Возвращаемое значение:
// ДокументDOM - полученный ДокументDOM
//
Функция ПолучитьDOMДокументИзXMLФайла(ПутьДоXMLФайла) Экспорт
ЧтениеXML=Новый ЧтениеXML;
ЧтениеXML.ОткрытьФайл(ПутьДоXMLФайла);
ПостроительDOM = Новый ПостроительDOM;
ДокументDOM = ПостроительDOM.Прочитать(ЧтениеXML);
ЧтениеXML.Закрыть();
Возврат ДокументDOM;
КонецФункции
// Процедура - Сохранить DOMДокумент КАКXMLФайл
// Сохраняет переданный дом документа, в указанный файл
// Параметры:
// ДокументDOM - ДокументDom - Документ который нужно сохранить
// ПутьДоXMLФайла - Строка - путь до файла xml в который нужно сохранить
//
Процедура СохранитьDOMДокументКАКXMLФайл(ДокументDOM,ПутьДоXMLФайла) Экспорт
ФайлЗаписьXML = Новый ЗаписьXML();
ФайлЗаписьXML.ОткрытьФайл(ПутьДоXMLФайла);
//ЗаписьDOM - способ конвертировать созданный ранее XML документ для записи
ФайлDOM = Новый ЗаписьDOM();
//производим запись
ФайлDOM.Записать(ДокументDOM, ФайлЗаписьXML);
//закрываем файл
ФайлЗаписьXML.Закрыть();
КонецПроцедуры
// Функция - Получить соответствие индексов шрифтов для установки формата
// Перебирает раздел шрифтов файла стилей.
// Если в каком то из имен шрифта есть макрос ФЧ тогда будет справа от него взят формат
// А сам макрос с форматной строкой будет удален, что бы шрифт нормально работал
// Пример форматной строки ArialФЧdd\.mm\.yyyy\ hh:mm:ss
// Параметры:
// ДокументDOM - ДокументDOM - Полученный ДокументDOM файла стилей см метод ПолучитьDOMДокументИзXMLФайла()
//
// Возвращаемое значение:
// Соответствие - В котором ключ это индекс шрифта, на который будут ссылаться описание стилей ячеек, а значение это форматная строка которую нужно установит для ячеек
//
Функция ПолучитьСоответствиеИндексовШрифтовДляУстановкиФормата(ДокументDOM,СоответствиеФорматаИмениШрифта) Экспорт
ИндексыШрифтовИФормат=Новый Соответствие;//В этот контейнер их соберем
СписокЭлементовFont=ДокументDOM.ПолучитьЭлементыПоИмени("font");
Индкс=-1;
Для Каждого Font ИЗ СписокЭлементовFont Цикл
Индкс=Индкс+1;
СписокЭлементовName=Font.ПолучитьЭлементыПоИмени("name");
Если СписокЭлементовName.Количество()=0 Тогда
Продолжить;
КонецЕсли;
АтрибутVal=СписокЭлементовName[0].Атрибуты.ПолучитьИменованныйЭлемент("val");
Если АтрибутVal=Неопределено Тогда
Продолжить;
КонецЕсли;
//В имени шрифта передается формат который нужно установить
Формат=АтрибутVal.ТекстовоеСодержимое;
//Если он есть в нашем описание, то будем его ставить
ИмяШрифта=СоответствиеФорматаИмениШрифта.Получить(Формат);
Если ИмяШрифта=Неопределено Тогда
Продолжить;
КонецЕсли;
//Вернем верное название
АтрибутVal.ТекстовоеСодержимое=ИмяШрифта;
//Запомним формат
ИндексыШрифтовИФормат.Вставить(Индкс,Формат);
КонецЦикла;
Возврат ИндексыШрифтовИФормат;
КонецФункции
// Функция - Получить соответствие шрифтов и стилей
// Перебирает в цикле полученный индексы шрифтов, кототорые указаны у тех стилей ячеек,
// у которых нужно изменить ссылку на описание формата числа, ну ту где указан формат даты
// Параметры:
// ДокументDOM - ДокументДом - дом документа, в который загружен xml описания стилей excel
// ИндексыШрифтовИФормат - Соответствие - см ПолучитьСоответствиеИндексовШрифтовДляУстановкиФормата()
//
// Возвращаемое значение:
// Соответствие - В котором ключ это индкс шрифта, у которого нужно заменить ссылку на форамт числа, ну нужный, который мы добавили
//
Функция ПолучитьСоответствиеШрифтовИСтилей(ДокументDOM,ИндексыШрифтовИФормат) Экспорт
ИндексыШрифтовИСтилей=Новый Соответствие;
СписокЭлементовnumFmts=ДокументDOM.ПолучитьЭлементыПоИмени("numFmts");
Если СписокЭлементовnumFmts.Количество()=0 Тогда
Возврат ИндексыШрифтовИСтилей;//Что то пошло не так
КонецЕсли;
numFmts=СписокЭлементовnumFmts[0];
//Для поддержки уникальности получим максимальный ид описания формата ячейки
СписокТекущихЭлементовnumFmt=ДокументDOM.ПолучитьЭлементыПоИмени("numFmts");
МаксnumFmtId=1;
Для Каждого numFmt ИЗ СписокТекущихЭлементовnumFmt Цикл
numFmtId=numFmt.Атрибуты.ПолучитьИменованныйЭлемент("numFmtId");
Если numFmtId=Неопределено Тогда
Продолжить;
КонецЕсли;
МаксnumFmtId=Макс(Число(numFmtId),МаксnumFmtId);
КонецЦикла;
//Обходим наши и добавляем их к текущим со своим индексом уникальным
//Сохранив эти уникальные индексы в еще один контейнер, который будет результатом этого метода
Для Каждого ИндксШрифтаИФормат ИЗ ИндексыШрифтовИФормат Цикл
МаксnumFmtId=МаксnumFmtId+1;//Увеличим для уникальности
//Если не нашли то создадим и добавим в дом модель
ЭлементDOMnumFmt=ДокументDOM.СоздатьЭлемент("numFmt");
ЭлементDOMnumFmt.УстановитьАтрибут("numFmtId",СтрЗаменить(МаксnumFmtId,Символы.НПП,""));//Пишем индкс и неразрывный пробел уберем
ЭлементDOMnumFmt.УстановитьАтрибут("formatCode",ИндксШрифтаИФормат.Значение);//Пишем формат даты, числа
numFmts.ДобавитьДочерний(ЭлементDOMnumFmt);
//Запомним индкс этого стиля числа
//Что бы потом проще находить пишем индкс шрифта, по нему будем искать индкс стиля
ИндексыШрифтовИСтилей.Вставить(ИндксШрифтаИФормат.Ключ,МаксnumFmtId);
КонецЦикла;
//Обновим верным количеством, незабываем про неразрывный пробел
numFmts.УстановитьАтрибут("count",СтрЗаменить(numFmts.ДочерниеУзлы.Количество(),Символы.НПП,""));
Возврат ИндексыШрифтовИСтилей;
КонецФункции
// Процедура - Записать у стилей ячеек ссылки на нужные форматы чисел
// Перебирает описание стилей ячеек и подменяет им ссылки на нужные форматы чисел
// Параметры:
// ДокументDOM - ДокументDom - дом документ в который загружен файл стилей ячеек
// ИндексыШрифтовИСтилей - Соответствие - см ПолучитьСоответствиеШрифтовИСтилей()
//
Процедура ЗаписатьУСтилейЯчеекСсылкиНаНужныеФорматыЧисел(ДокументDOM,ИндексыШрифтовИСтилей) Экспорт
СписокЭлементовnumcellXfs=ДокументDOM.ПолучитьЭлементыПоИмени("cellXfs");
Если СписокЭлементовnumcellXfs.Количество()=0 Тогда
Возврат;//Что то пошло не так
КонецЕсли;
//Все дочерние стили, получаем и указываем у них ссылки не те форматы числа которые мы выше добавили
СписокЭлементовnumcellXfsXF=СписокЭлементовnumcellXfs[0].ПолучитьЭлементыПоИмени("xf");
Для Каждого XF ИЗ СписокЭлементовnumcellXfsXF Цикл
fontId=XF.ПолучитьАтрибут("fontId");
Если fontId=Неопределено Тогда
Продолжить;
КонецЕсли;
НужныйnumFmtId=ИндексыШрифтовИСтилей.Получить(Число(fontId));
Если НужныйnumFmtId=Неопределено Тогда
Продолжить;
КонецЕсли;
XF.УстановитьАтрибут("numFmtId",СтрЗаменить(НужныйnumFmtId,Символы.НПП,""));
КонецЦикла;
КонецПроцедуры
// Процедура - Установить формат даты время у ячеек через стили
// Распаковывает xlsx файл как архив, получает описание стилей ячеек
// Затем среди имен используемых шрифтов находит индексы тех, имена которых есть в ключах СоответствиеФорматаИмениШрифта
// Далее добавляет их как новые описания форматов ячеек
// Затем находит те стили ячеек, которые ссылаются на такой шрифт и меняет им формат на новый, тот который в СоответствиеФорматаИмениШрифта
// Собирает обратно все в архив и перезаписывает переданный xlsx файл
// Параметры:
// ПутьДоФайлаXLSX - Строка - пуолный путь до xlsx файла который нужно подменить
// СоответствиеФорматаИмениШрифта - Соответствие - соответствие форматов и имен шрифтов которые подменили
//
Процедура УстановитьФорматДатыВремяУЯчеекЧерезСтили(ПутьДоФайлаXLSX,СоответствиеФорматаИмениШрифта) Экспорт
//Подготовим папку куда извелкать будем xlsx файл как архив
ПутьДоПапкиСXML=ПолучитьИмяВременногоФайла()+"\";
//Архив извлечем
ИзвлечьАрхивZIPФормата(ПутьДоФайлаXLSX,ПутьДоПапкиСXML);
//XML Файл со стилями в Дом Модель преобразуем, для удобной работы
ПутьДоXMLДокументаСтилей=ПутьДоПапкиСXML + "xl\styles.xml";
ДокументDOM=ПолучитьDOMДокументИзXMLФайла(ПутьДоXMLДокументаСтилей);
//Определяем индексы шрифтов и формат, которые передали через свойство Шрифт у областей ТабличногоДокумента
//Которые содержат признак что нужно устанавливать, потом мы будем искать стили ячеек которым присвоим эти шрифты
ИндексыШрифтовИФормат=ПолучитьСоответствиеИндексовШрифтовДляУстановкиФормата(ДокументDOM,СоответствиеФорматаИмениШрифта);
//Если мы нечего не нашли то тут останавливаемся, почистив за собой папку
Если ИндексыШрифтовИФормат.Количество()=0 Тогда
УдалитьФайлы(ПутьДоПапкиСXML);
Возврат;
КонецЕсли;
//Теперь добавим их в описание стилей числа, со своими уникальными индексами и получим их Соответствие
ИндексыШрифтовИСтилей=ПолучитьСоответствиеШрифтовИСтилей(ДокументDOM,ИндексыШрифтовИФормат);
Если ИндексыШрифтовИСтилей.Количество()=0 Тогда
УдалитьФайлы(ПутьДоПапкиСXML);
Возврат;
КонецЕсли;
//Теперь последнее, обходим стили ячеек
//И те которые ссылаются на шрифты, какие мы нашли, обновим их на стили какие мы добавили с переданным форматом
ЗаписатьУСтилейЯчеекСсылкиНаНужныеФорматыЧисел(ДокументDOM,ИндексыШрифтовИСтилей);
//Сохраним измененный xml
СохранитьDOMДокументКАКXMLФайл(ДокументDOM,ПутьДоXMLДокументаСтилей);
//Упакуем обратно в XLSX результат и перезапишем изначальный файл
УдалитьФайлы(ПутьДоФайлаXLSX);
СобратьZipАрхив(ПутьДоПапкиСXML,ПутьДоФайлаXLSX);
КонецПроцедуры
// Функция - Преобразовать дату строкой в формат EXCEL
// В excel даты храняться числом, по своему внутреннему формату
// Данный метод получает на вход дату, строкой, в 1с формате 23.03.2017 10:45:25, а на выходе строка в формате excel
// Параметры:
// ДатаСтрокой - Строка - дата см Строка(ТекущаяДата())
// ИгнорироватьВремя - Булево - если истина, тогда часть времени игноруется, пишется только дата
//
// Возвращаемое значение:
// Строка - строка в которой лежит число, преобразованная дата по стандратам excel
//
Функция ПреобразоватьДатуСтрокойВФорматEXCEL(знач ДатаСтрокой,ИгнорироватьВремя=Ложь) Экспорт
Результат="";
Попытка
врДата=Дата(ДатаСтрокой);
//Все даты хранятся в виде целых чисел, обозначающих количество дней с 1 января 1900 г. (записывается как 1) до 31 декабря 9999 г.
//(сохраняется как 2958465).
НачалоВремен=Дата('1900.01.01 00:00:00');
Если НачалоВремен > врДата Тогда
врДата = НачалоВремен;
КонецЕсли;
РазницаВДнях = (НачалоДня(врДата) - НачалоДня(НачалоВремен)) / (60 * 60 * 24);
Если РазницаВДнях = 0 Тогда
Возврат 0;//это пустая дата
КонецЕсли;
//+2 это пустая дата
РазницаВДнях = РазницаВДнях +2;
//Дни записываем
Результат=СтрЗаменить(РазницаВДнях,Символы.НПП,"");
Если ИгнорироватьВремя Тогда
//Просто как дату вернем, это полезно например для формата только дата
Возврат Результат;
КонецЕсли;
//Время хранится в виде десятичных дробей от 0,0 до 0,99999, которые представляют собой долю дня, где 0,0 - 00:00:00, а 0,99999 - 23:59:59.
//Например:
// 0.25 - 06:00
// 0.5 - 12:00
// 0.541655093 это 12:59:59
//В секунды время преобразуем, если наша дата не начало дня тогда будем время преобразовывать
врВремя=СтрРазделить(ДатаСтрокой," ", Ложь);
Если врВремя.Количество()>1 И НЕ врВремя[1]="00:00:00" Тогда
врВремя=СтрРазделить(врВремя[1],":", Ложь);
//Часы в сек
ВремяВСекундах=Число(врВремя[0])*60*60;
//Часы в сек + минуты в сек + сами сек
ВремяВСекундах=ВремяВСекундах+(Число(врВремя[1])*60)+Число(врВремя[2]);
//Доля в дне получаем
ДоляВДне=Окр(ВремяВСекундах/86400,9,РежимОкругления.Окр15как20);
//Запишем долю в дне
Результат=СтрЗаменить(Строка(ДоляВДне),"0,",Результат+".");
КонецЕсли;
Исключение
ТекстОшибки=ОписаниеОшибки();
Возврат Неопределено;
КонецПопытки;
Возврат Результат;
КонецФункции
Процедура СформироватьПример()
ТабличныйДокумент=Новый ТабличныйДокумент;
//Контейнер где храним соответствие шрифта - формата
СоответствиеФорматаИмениШрифта=Новый Соответствие;
//Первую ячейку делаем обычным число
Область = ТабличныйДокумент.Область("R1C1:R1C1");
Область.СодержитЗначение = Истина;
Область.ТипЗначения = Новый ОписаниеТипов("Число");
Область.Значение = 2;
//Вторую сделаем датой
Формат="dd\.mm\.yyyy";
Область = ТабличныйДокумент.Область("R1C2:R1C2");
Область.СодержитЗначение = Истина;
Область.ТипЗначения = Новый ОписаниеТипов("Число");
Область.Значение = ПреобразоватьДатуСтрокойВФорматEXCEL(Строка(ТекущаяДата()),Истина);
СоответствиеФорматаИмениШрифта.Вставить(Формат,Область.Шрифт.Имя);
Область.Шрифт = Новый Шрифт(Область.Шрифт,Формат);
//Эту датой время
Формат="dd\.mm\.yyyy\ hh:mm:ss";
Область = ТабличныйДокумент.Область("R1C3:R1C3");
Область.СодержитЗначение = Истина;
Область.ТипЗначения = Новый ОписаниеТипов("Число");
Область.Значение = ПреобразоватьДатуСтрокойВФорматEXCEL(Строка(ТекущаяДата()));
СоответствиеФорматаИмениШрифта.Вставить(Формат,Область.Шрифт.Имя);
Область.Шрифт = Новый Шрифт(Область.Шрифт,Формат);
ПутьДоФайлаXLSX="C:\Пример.xlsx";
ТабличныйДокумент.Записать(ПутьДоФайлаXLSX,ТипФайлаТабличногоДокумента.XLSX);
УстановитьФорматДатыВремяУЯчеекЧерезСтили(ПутьДоФайлаXLSX,СоответствиеФорматаИмениШрифта);
КонецПроцедуры
