Excel: создание в формате xlsx без COM технологии

19.09.2018 16 Comments

Загрузка и выгрузка в Excel , Обмен данными 1С, Разработка

,

По работе приходится часто выгружать данные в Excel формате. При большом количестве строк и формул этот процесс достаточно длительный.
Решил написать для своих целей небольшую библиотечку для выгрузки данных. Используется технология open-xml. Никаких библиотек не надо, пишу напрямую в xml файлики.
На данном этапе сделано форматирование, формулы.

С чего я начал.

Прочитал книгу Воутер Ван Вугт "OpenXML Кратко и доступно"

Переименовал xlsx  в zip и распаковал архив в папку.

Получил 3 папки и  файл [Content_Types].xml.

Разработку вел снизу-вверх. Сперва насоздавал файлы необходимые для Excel.  Затем сделал структуру с данными на основании которых генерится документ.

Струкутуру с данными назвал  ДанныеКниги

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


Функция Записать_Content_Types_xml(путь)

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

хмл.ЗаписатьНачалоЭлемента("Types");
хмл.ЗаписатьАтрибут("xmlns", "http://schemas.openxmlformats.org/package/2006/content-types");

хмл.ЗаписатьНачалоЭлемента("Default");
хмл.ЗаписатьАтрибут("Extension", "rels");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-package.relationships+xml");
хмл.ЗаписатьКонецЭлемента();

хмл.ЗаписатьНачалоЭлемента("Default");
хмл.ЗаписатьАтрибут("Extension", "xml");
хмл.ЗаписатьАтрибут("ContentType", "application/xml");
хмл.ЗаписатьКонецЭлемента();

хмл.ЗаписатьНачалоЭлемента("Override");
хмл.ЗаписатьАтрибут("PartName", "/xl/workbook.xml");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml");
хмл.ЗаписатьКонецЭлемента();
к = 1;
Для каждого стр из ДанныеКниги.Листы цикл
хмл.ЗаписатьНачалоЭлемента("Override");
хмл.ЗаписатьАтрибут("PartName", "/xl/worksheets/sheet" + строка(к) + ".xml");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml");
хмл.ЗаписатьКонецЭлемента();
к=к+1;
КонецЦикла;
к = 1;
Для каждого стр из ДанныеКниги.Таблицы цикл
хмл.ЗаписатьНачалоЭлемента("Override");
хмл.ЗаписатьАтрибут("PartName", "/xl/tables/table" + строка(к) + ".xml");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml");
хмл.ЗаписатьКонецЭлемента();
к=к+1;
КонецЦикла;

хмл.ЗаписатьНачалоЭлемента("Override");
хмл.ЗаписатьАтрибут("PartName", "/xl/theme/theme1.xml");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-officedocument.theme+xml");
хмл.ЗаписатьКонецЭлемента();

хмл.ЗаписатьНачалоЭлемента("Override");
хмл.ЗаписатьАтрибут("PartName", "/xl/styles.xml");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml");
хмл.ЗаписатьКонецЭлемента();

хмл.ЗаписатьНачалоЭлемента("Override");
хмл.ЗаписатьАтрибут("PartName", "/docProps/core.xml");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-package.core-properties+xml");
хмл.ЗаписатьКонецЭлемента();

хмл.ЗаписатьНачалоЭлемента("Override");
хмл.ЗаписатьАтрибут("PartName", "/docProps/app.xml");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-officedocument.extended-properties+xml");
хмл.ЗаписатьКонецЭлемента();

хмл.ЗаписатьНачалоЭлемента("Override");
хмл.ЗаписатьАтрибут("PartName", "/xl/sharedStrings.xml");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml");
хмл.ЗаписатьКонецЭлемента();

хмл.ЗаписатьКонецЭлемента();

хмл.Закрыть();

КонецФункции

Делал выгрузку шапка 10 строк  тч 4000 строк, 67 столбцов 20 формул в строке. форматирование(шрифты, рамки, ширина столбцов, заливка) времени заняло 135 секунд.

пример формирования шапки

// заполняем лист
&НаКлиенте
Функция СформироватьШапку(подразделение, код,датасоставления, месяц)
// создадим  стили в нашей книги
Заголовок11 = ДобавитьСтиль(0, Объект.ДанныеКниги.шрифты.Индекс(Объект.ДанныеКниги.шрифты.НайтиПоЗначению("Arial_11")),  0, 0, "vertical='center'");
Заголовок11_рамка_центр = ДобавитьСтиль(0,  Объект.ДанныеКниги.шрифты.Индекс(Объект.ДанныеКниги.шрифты.НайтиПоЗначению("Arial_12_b")), 0,  Объект.ДанныеКниги.рамки.Индекс(Объект.ДанныеКниги.рамки.НайтиПоЗначению("1111")), "horizontal='center'");
Заголовок11_рамка_дата = ДобавитьСтиль(14, Объект.ДанныеКниги.шрифты.Индекс(Объект.ДанныеКниги.шрифты.НайтиПоЗначению("Arial_11")), 0, Объект.ДанныеКниги.рамки.Индекс(Объект.ДанныеКниги.рамки.НайтиПоЗначению("1111")),  "horizontal='center'");
Рамка_верх_низ = ДобавитьСтиль(0, 0,  0, Объект.ДанныеКниги.рамки.Индекс(Объект.ДанныеКниги.рамки.НайтиПоЗначению("0011")), "");
Рамка_верх_низ_право = ДобавитьСтиль(0, 0,  0, Объект.ДанныеКниги.рамки.Индекс(Объект.ДанныеКниги.рамки.НайтиПоЗначению("0111")), "");
Рамка_верх = ДобавитьСтиль(0, 0,  0, Объект.ДанныеКниги.рамки.Индекс(Объект.ДанныеКниги.рамки.НайтиПоЗначению("0010")), "");
Рамка_л = ДобавитьСтиль(0, 0,  0, Объект.ДанныеКниги.рамки.Индекс(Объект.ДанныеКниги.рамки.НайтиПоЗначению("1000")), "");
ПодСтрокой  = ДобавитьСтиль(0,Объект.ДанныеКниги.шрифты.Индекс(Объект.ДанныеКниги.шрифты.НайтиПоЗначению("Arial_6")), 0,0, "horizontal='center' vertical='top'");
Заголовок12 = ДобавитьСтиль(0,Объект.ДанныеКниги.шрифты.Индекс(Объект.ДанныеКниги.шрифты.НайтиПоЗначению("Arial_12")),0, 0, "");
хмл = "";
хмл = хмл + ДобавитьСтроку("B" ,"1", "ооо веснушка",Заголовок12);
хмл = хмл + ДобавитьСтроку( "B" ,"2", "наименование организации",ПодСтрокой);
хмл = хмл + ДобавитьСтроку( "B" ,"3", подразделение,Заголовок12);
хмл = хмл + ДобавитьСтроку( "B" ,"4", "подразделение",ПодСтрокой);
хмл = хмл + ДобавитьСтроку( "V" ,"5", "Код подразделения",Заголовок11_рамка_центр);
хмл = хмл + ДобавитьСтроку( "W" ,"5", "",Рамка_верх_низ);
хмл = хмл + ДобавитьСтроку( "X" ,"5", "",Рамка_верх_низ);
хмл = хмл + ДобавитьСтроку( "Y" ,"5", "",Рамка_верх_низ);
хмл = хмл + ДобавитьСтроку( "Z" ,"5", "",Рамка_верх_низ_право);
хмл = хмл + ДобавитьСтроку( "AA","5", "Дата составления",Заголовок11_рамка_центр);
хмл = хмл + ДобавитьСтроку( "AB" ,"5", "",Рамка_верх_низ);
хмл = хмл + ДобавитьСтроку( "AC" ,"5", "",Рамка_верх_низ);
хмл = хмл + ДобавитьСтроку( "AD" ,"5", "",Рамка_верх_низ_право);



хмл = хмл + ДобавитьДату( "AA","7", Формат(датасоставления,"ДФ=dd.MM.yyyy"), Заголовок11_рамка_дата);
хмл = хмл + ДобавитьСтроку( "AB" ,"7", "",Рамка_верх_низ);
хмл = хмл + ДобавитьСтроку( "AC" ,"7", "",Рамка_верх_низ);
хмл = хмл + ДобавитьСтроку( "AD" ,"7", "",Рамка_верх_низ_право);

ДобавитьДату( "AF","7", Формат(НачалоМесяца(месяц),"ДФ=dd.MM.yyyy"), Заголовок11_рамка_дата);
хмл = хмл + ДобавитьСтроку( "AG" ,"7", "",Рамка_верх_низ);
хмл = хмл + ДобавитьСтроку( "AH" ,"7", "",Рамка_верх_низ);
хмл = хмл + ДобавитьСтроку( "AI" ,"7", "",Рамка_верх_низ_право);

ДобавитьДату( "AJ","7", Формат(КонецМесяца(месяц),"ДФ=dd.MM.yyyy"), Заголовок11_рамка_дата);
хмл = хмл + ДобавитьСтроку( "AK" ,"7", "",Рамка_верх_низ);
хмл = хмл + ДобавитьСтроку( "AL" ,"7", "",Рамка_верх_низ);
хмл = хмл + ДобавитьСтроку( "AM" ,"7", "",Рамка_верх_низ_право);

возврат хмл;
КонецФункции

Книга хранится в структуре которая инициализируется следующим образом

Процедура ИнициализацияКниги()
Шрифты  = Новый СписокЗначений;
шрифты.Добавить("Arial_11","<font><sz val='11'/><name val='Arial'/><family val='2'/></font>");
Заливки = Новый СписокЗначений;
стр = Заливки.Добавить("fill_none","<fill><patternFill patternType='none'/></fill>");
стр = Заливки.Добавить("fill_gray125", "<fill><patternFill patternType='gray125'/></fill>");
Рамки   = Новый СписокЗначений;
стр = рамки.Добавить("0000","<border><left/><right/><top/><bottom/><diagonal/></border>");
Стили   = Новый СписокЗначений;
Листы   = Новый СписокЗначений;
лист = новый Структура;
лист.Вставить("Имя", "Лист_1");
лист.Вставить("ДанныеЛиста","");
лист.Вставить("Колонки", Новый СписокЗначений);
лист.Вставить("ОбъединенныеЯчейки", Новый массив);

Таблица = Новый Структура;
таблица.Вставить("Имя", "");
таблица.Вставить("Лист", 0);
таблица.Вставить("Колонки", новый списокЗначений);
таблица.Вставить("диапазон", "");

Листы.Добавить(лист);

ОбъединениеЯчеек   = Новый массив;
ПорядокВычислений   = Новый СписокЗначений;
СписокСтрок   = Новый Массив;

ДанныеКниги= Новый Структура;
ДанныеКниги.Вставить("Рамки"  , Рамки);
ДанныеКниги.Вставить("Шрифты" , Шрифты);
ДанныеКниги.Вставить("Заливки", Заливки);
ДанныеКниги.Вставить("Листы"  , Листы);
ДанныеКниги.Вставить("Стили"  , Стили);
ДанныеКниги.Вставить("ОбъединениеЯчеек"  , ОбъединениеЯчеек);
ДанныеКниги.Вставить("ПорядокВычислений"  , ПорядокВычислений);
ДанныеКниги.Вставить("СписокСтрок"  , СписокСтрок);
ДанныеКниги.Вставить("Таблицы"  , новый СписокЗначений);


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

Стили (рамки, шрифты, заливки) хранятся для всей книги в файле styles.xml.

Все строковые значения ячеек книги(дата тоже) хранятся в списке "СписокСтрок"  который сохраняется в файл sharedStrings.xml.

листы книги каждый в своем файле sheet1.xml. (ширина колонок, объединенные ячейки, данные листа).

Пример формирования [Content_Types].xml

Функция Записать_Content_Types_xml(путь)

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

хмл.ЗаписатьНачалоЭлемента("Types");
хмл.ЗаписатьАтрибут("xmlns", "http://schemas.openxmlformats.org/package/2006/content-types");

хмл.ЗаписатьНачалоЭлемента("Default");
хмл.ЗаписатьАтрибут("Extension", "rels");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-package.relationships+xml");
хмл.ЗаписатьКонецЭлемента();

хмл.ЗаписатьНачалоЭлемента("Default");
хмл.ЗаписатьАтрибут("Extension", "xml");
хмл.ЗаписатьАтрибут("ContentType", "application/xml");
хмл.ЗаписатьКонецЭлемента();

хмл.ЗаписатьНачалоЭлемента("Override");
хмл.ЗаписатьАтрибут("PartName", "/xl/workbook.xml");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml");
хмл.ЗаписатьКонецЭлемента();
к = 1;
Для каждого стр из ДанныеКниги.Листы цикл
хмл.ЗаписатьНачалоЭлемента("Override");
хмл.ЗаписатьАтрибут("PartName", "/xl/worksheets/sheet" + строка(к) + ".xml");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml");
хмл.ЗаписатьКонецЭлемента();
к=к+1;
КонецЦикла;
хмл.ЗаписатьНачалоЭлемента("Override");
хмл.ЗаписатьАтрибут("PartName", "/xl/theme/theme1.xml");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-officedocument.theme+xml");
хмл.ЗаписатьКонецЭлемента();

хмл.ЗаписатьНачалоЭлемента("Override");
хмл.ЗаписатьАтрибут("PartName", "/xl/styles.xml");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml");
хмл.ЗаписатьКонецЭлемента();

хмл.ЗаписатьНачалоЭлемента("Override");
хмл.ЗаписатьАтрибут("PartName", "/docProps/core.xml");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-package.core-properties+xml");
хмл.ЗаписатьКонецЭлемента();

хмл.ЗаписатьНачалоЭлемента("Override");
хмл.ЗаписатьАтрибут("PartName", "/docProps/app.xml");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-officedocument.extended-properties+xml");
хмл.ЗаписатьКонецЭлемента();

хмл.ЗаписатьНачалоЭлемента("Override");
хмл.ЗаписатьАтрибут("PartName", "/xl/sharedStrings.xml");
хмл.ЗаписатьАтрибут("ContentType", "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml");
хмл.ЗаписатьКонецЭлемента();

хмл.ЗаписатьКонецЭлемента();

хмл.Закрыть();

КонецФункции

Добавил таблицы. Для моих целей завершил обработку.

демо версия находится по адресу //infostart.ru/public/905578/

Подробная документация https://www.ecma-international.org/publications/standards/Ecma-376.htm

 

 

Related Posts

16 Comments

  1. stanislav1esnik

    Спасибо за примеры кода и наводку на книгу! )

    Тема чтения/изменения файлов Excel из составных xml-ек уже поднималась, но думаю актуальности не потеряла.

    Reply ↓
  2. user1045404

    Спасибо за комментарий. Смотрю тема заинтересовала. Подготовлю демонстрашку. Подчищу код и выложу.

    Reply ↓
  3. tsukanov

    Не используйте конкатенацию. Лучше массив + СтрСоединить().

    При конкатенации на каждый «+» скорее всего выделяется новая память под всю! строку. Это медленно и вообще бессмысленная нагрузка на процессор и память.

    Было бы здорово, если бы вы оформили это в виде обработки-библиотеки. Давно напрашивается )

    Reply ↓
  4. user1045404

    спасибо. подправлю

    Reply ↓
  5. premierex

    (0) А зачем в процедуре ИнициализацияКниги() фрагмент кода ДанныеКниги = ДанныеКниги;?

    Reply ↓
  6. kasper076

    Было бы здорово пакеты XDTO получить для каждой xml-ки.

    Reply ↓
  7. user1045404

    (5)делал для уф, потом переделывал для обычных. удалил слово «объект. «

    Reply ↓
  8. user1045404

    (6)надеюсь в понедельник обработку выложу (осталось чистку кода и демонстрашку доделать + Исправляю замечание Александра Цуканова).

    Reply ↓
  9. krollzlat

    Написано что процесс достаточно длительный …И насколько быстрее отрабатывает этот метод на одном и том же объеме данных?

    Reply ↓
  10. Gureev

    Предлагаю оформить и выложить на гитхаб, силами сообщества из этого быстро вырастет библиотека.

    Reply ↓
  11. triviumfan

    Я один не уловил сути создания таким методом заветного xlsx? Не понимаю, зачем твой разврат. Скоростью тут и не пахнет совсем.

    Reply ↓
  12. acanta

    Интересно было бы загрузить в лист екселя/опенофиса вместе с формулами и сохранить в 1с, обновить какие то данные в базе, отредактировать формулы, увидеть результаты их расчета и сохранить в ексель/опенофис с формулами. ИМХО, методика очень даже подходит, если закинуть все это в общий модуль и визуализировать. Но вот в чем это можно сохранить?

    Reply ↓
  13. Кадош

    (12) вопрос не в скорости, а в работе с экселем без COM и ADO

    Reply ↓
  14. user1045404

    (11) скоростью пахнет и еще как. когда попробуешь через СОМ формулы устанавливать и рамочки , скорость ощутима.

    Reply ↓
  15. Serj1C

    на дворе 2к18.

    платформа из коробки умеет сохранять табличный документ в формат эксель без костылей.

     ТабличныйДокумент.Записать(ИмяФайлаXLSX, ТипФайлаТабличногоДокумента.XLSX);

    даже в контексте сервера.

    даже уже с поддержкой листов

    Reply ↓
  16. azhilichev

    (15) Как вы при такой выгрузке добавите табличный документ формулы, которые будут работать в Excel?

    Reply ↓

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *