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

Обмен - Загрузка и выгрузка в Excel

обработка Excel отчет

109
По работе приходится часто выгружать данные в 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");
	хмл.ЗаписатьКонецЭлемента();
	
	хмл.ЗаписатьКонецЭлемента();
	
	хмл.Закрыть();
	
КонецФункции

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

демо версия находится по адресу //vygruzka-zagruzka-1c.ru/public/905578/

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

 

 

109

См. также

Комментарии
Избранное Подписка Сортировка: Древо
1. stanislav1esnik 13 11.09.18 09:44 Сейчас в теме
Спасибо за примеры кода и наводку на книгу! )
Тема чтения/изменения файлов Excel из составных xml-ек уже поднималась, но думаю актуальности не потеряла.
2. user1045404 135 11.09.18 10:13 Сейчас в теме
Спасибо за комментарий. Смотрю тема заинтересовала. Подготовлю демонстрашку. Подчищу код и выложу.
3. tsukanov 65 11.09.18 11:07 Сейчас в теме
Не используйте конкатенацию. Лучше массив + СтрСоединить().
При конкатенации на каждый "+" скорее всего выделяется новая память под всю! строку. Это медленно и вообще бессмысленная нагрузка на процессор и память.

Было бы здорово, если бы вы оформили это в виде обработки-библиотеки. Давно напрашивается )
mivari; pbabincev; echo77; kraynev-navi; +4 Ответить
4. user1045404 135 11.09.18 11:38 Сейчас в теме
5. premier 165 12.09.18 08:48 Сейчас в теме
(0) А зачем в процедуре ИнициализацияКниги() фрагмент кода ДанныеКниги = ДанныеКниги;?
7. user1045404 135 12.09.18 11:24 Сейчас в теме
(5)делал для уф, потом переделывал для обычных. удалил слово "объект. "
6. kasper076 19 12.09.18 09:29 Сейчас в теме
Было бы здорово пакеты XDTO получить для каждой xml-ки.
8. user1045404 135 12.09.18 11:38 Сейчас в теме
(6)надеюсь в понедельник обработку выложу (осталось чистку кода и демонстрашку доделать + Исправляю замечание Александра Цуканова).
9. krollzlat 17.10.18 11:20 Сейчас в теме
Написано что процесс достаточно длительный ...И насколько быстрее отрабатывает этот метод на одном и том же объеме данных?
10. Gureev 17.10.18 15:55 Сейчас в теме
Предлагаю оформить и выложить на гитхаб, силами сообщества из этого быстро вырастет библиотека.
11. yelloo 4 17.10.18 20:13 Сейчас в теме
Я один не уловил сути создания таким методом заветного xlsx? Не понимаю, зачем твой разврат. Скоростью тут и не пахнет совсем.
14. user1045404 135 18.10.18 12:55 Сейчас в теме
(11) скоростью пахнет и еще как. когда попробуешь через СОМ формулы устанавливать и рамочки , скорость ощутима.
12. acanta 45 17.10.18 20:38 Сейчас в теме
Интересно было бы загрузить в лист екселя/опенофиса вместе с формулами и сохранить в 1с, обновить какие то данные в базе, отредактировать формулы, увидеть результаты их расчета и сохранить в ексель/опенофис с формулами. ИМХО, методика очень даже подходит, если закинуть все это в общий модуль и визуализировать. Но вот в чем это можно сохранить?
13. Кадош 17.10.18 23:53 Сейчас в теме
(12) вопрос не в скорости, а в работе с экселем без COM и ADO
15. Serj1C 472 22.10.18 11:59 Сейчас в теме
на дворе 2к18.
платформа из коробки умеет сохранять табличный документ в формат эксель без костылей.
	ТабличныйДокумент.Записать(ИмяФайлаXLSX, ТипФайлаТабличногоДокумента.XLSX);

даже в контексте сервера.
даже уже с поддержкой листов
16. azhilichev 23.10.18 17:52 Сейчас в теме
(15) Как вы при такой выгрузке добавите табличный документ формулы, которые будут работать в Excel?
Оставьте свое сообщение