Выгрузка в Excel с использованием формата OpenXML, разбор файла sheet1.xml

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

обработка excel openxml библиотека

13
Судя по количеству просмотров темы https://infostart.ru/public/900022/ решил описать структуру файла Листа excel. На полноту не претендую, опишу то, что знаю.

Итак,  сам файл с описанием содержимого листа хранится в папке ...xl\worksheets с названием sheet1.xml. сколько листов, столько файлов.

Ссылки на лист записываются в несколько файлов:

1. [Content_Types].xml

  

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

2. ...\xl\workbook.xml

	к=1;
	Для каждого стр из книга.Листы цикл
		хмл.ЗаписатьНачалоЭлемента("sheet");
		хмл.ЗаписатьАтрибут("name", стр.Имя);
		хмл.ЗаписатьАтрибут("sheetId", строка(к));
		хмл.ЗаписатьАтрибут("r:id", "rId"+ строка(к));
		хмл.ЗаписатьКонецЭлемента(); // sheet
		к=к+1;
	КонецЦикла;

3. \xl\_rels\workbook.xml.rels

	к=1;
	Для каждого стр из книга.Листы цикл
		хмл.ЗаписатьНачалоЭлемента("Relationship");
		хмл.ЗаписатьАтрибут("Id", "rId"+ строка(к));
		хмл.ЗаписатьАтрибут("Type", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet");
		хмл.ЗаписатьАтрибут("Target", "worksheets/sheet"++ строка(к) + ".xml");
		хмл.ЗаписатьКонецЭлемента(); //Relationship
		к=к+1;
	КонецЦикла;	

3. xl\worksheets\_rels - папка с файлами описание связей листов


Процедура Записать_sheet1_xml_rels(Книга, путь)
	листы = новый массив;
	Для каждого табл из  Книга.Таблицы Цикл
		Если листы.Найти(табл.Значение.лист) = неопределено Тогда
			листы.Добавить(табл.Значение.лист);
		КонецЕсли;
	КонецЦикла;
	
	Для каждого лист из Листы Цикл 
		хмл = Новый ЗаписьXML;
		хмл.ОткрытьФайл(Путь+ "\sheet"+ строка(лист+1) + ".xml.rels");
		хмл.ЗаписатьОбъявлениеXML();
		
		хмл.ЗаписатьНачалоЭлемента("Relationships");
		хмл.ЗаписатьАтрибут("xmlns","http://schemas.openxmlformats.org/package/2006/relationships");
		
		Для каждого табл из Книга.Таблицы цикл
			если  лист = табл.Значение.Лист Тогда
				хмл.ЗаписатьНачалоЭлемента("Relationship");
				хмл.ЗаписатьАтрибут("Id", "rId"+ строка(лист+1));
				хмл.ЗаписатьАтрибут("Type", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/table");
				хмл.ЗаписатьАтрибут("Target", "../tables/table"++ строка(лист+1) + ".xml");
				хмл.ЗаписатьКонецЭлемента(); //Relationship
			КонецЕсли;
		КонецЦикла;	
		хмл.ЗаписатьКонецЭлемента(); //Relationships
		хмл.Закрыть();
	КонецЦикла;	
КонецПроцедуры

в этих файлах привязываются таблицы к листу.

 

 

Далее немного о структуре файла листа.

<dimension ref="A1:BO38"/> - это первая и последняя ячейка листа.

-<sheetViews>

_______________________________________

-<sheetViews>


-<sheetView workbookViewId="0" tabSelected="1">

<pane state="frozenSplit" activePane="bottomRight" topLeftCell="I27" ySplit="11" xSplit="2"/> Заморозка листа: колонка строка

У меня написана функция

Процедура ДобавитьЗаморозку(книга,лист, колонка, строчка)  Экспорт
	книга.листы[лист].Заморозка = "<pane xSplit="""+ Строка(колонка)+""" ySplit="""+ Строка(строчка)+""" topLeftCell="""+
	       Excel_получить_имя(колонка+1)+  Строка(строчка+1)+""" activePane=""bottomRight"" state=""frozenSplit""/>";
КонецПроцедуры

 

</sheetView>

</sheetViews>

____________________

Секция cols содержит сведения о видимости колонок, и их ширина.  аттрибуты max  и min на какие столбцы это распространяется

<col customWidth="1" width="7" max="1" min="1"/>

<col customWidth="1" width="0" max="8" min="8" hidden="1"/>

_____________________________

секция <sheetData>

Основная секция листа. В ней хранится вся информация о данных листа.

<row r="1" spans="1:67" ht="16" customHeight="1" x14ac:dyDescent="0.35">  номер строки с данными spans как я понял  67 это последняя заполненная колонка на данной строке ht -высота строки  x14ac:dyDescent="0.35" - выравнивание в ячейке.

    <row r='1' spans='1:2' x14ac:dyDescent='0.2'>
        <c r='B1' s='9' t='s'><v>0</v></c>   тут хранятся данные:"r" - ячейка, "s" - стиль(рамка,шрифт,фон, форматирование, выравнивание.....),  t='s' - значит это строка или дата  v - индекс где она хранится в файле  sharedStrings.xml
    </row>

__________

секция mergeCells

описывает объединнные ячейки <mergeCell ref="AJ7:AM7"/>

в библиотеке по работе с OpenXML сделал такую структуру листа

Функция СоздатьКнигу(КоличествоЛистов = 1) Экспорт
	Шрифты  = Новый СписокЗначений;
	шрифты.Добавить("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>");
	
	Таблица = Новый Структура;
	таблица.Вставить("Имя"     , "");
	таблица.Вставить("Лист"    , 0);
	таблица.Вставить("Колонки" , новый массив);
	таблица.Вставить("диапазон", "");

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

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

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

	Если книга.листы[лист].ДанныеЛиста.Количество()< номСтрока или книга.листы[лист].ДанныеЛиста[номСтрока-1] = Неопределено Тогда
		книга.листы[лист].ДанныеЛиста.вставить(номСтрока-1,Новый Массив);
	Конецесли;
	
	 //проверяем есть ли колонка
    Если книга.листы[лист].ДанныеЛиста[номСтрока-1].Количество()< номКолонка или книга.листы[лист].ДанныеЛиста[номСтрока-1][номКолонка-1] = Неопределено Тогда
		книга.листы[лист].ДанныеЛиста[номСтрока-1].вставить(номКолонка-1,Новый Массив);
	Конецесли;
	
	ячейка = Новый Структура;
	ячейка.Вставить("Значение",  значение);
	ячейка.Вставить("Представление",  "<c r='"  +  Excel_получить_имя(номКолонка) + номСтрока + "' s='"+строка(НомерСтиля)+"' t='s'><v>" + Строка(НомерСтроки_shared_Strings)+"</v></c>");

	книга.листы[лист].ДанныеЛиста[номСтрока-1][номКолонка-1]= ячейка;
КонецПроцедуры


дальше надо создать файл для листа

Процедура Записать_sheet_xml(Книга,путь,НомерЛиста,  ДанныеЛиста = неопределено)
	ФайлЛиста = путь + "\xl\worksheets\sheet" + НомерЛиста + ".xml";
	хмл = Новый ЗаписьXML;
	хмл.ОткрытьФайл(ФайлЛиста);
	хмл.ЗаписатьОбъявлениеXML();
	
	хмл.ЗаписатьНачалоЭлемента("worksheet");
	хмл.ЗаписатьАтрибут("xmlns", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
	хмл.ЗаписатьАтрибут("xmlns:r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
	хмл.ЗаписатьАтрибут("xmlns:mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
	хмл.ЗаписатьАтрибут("mc:Ignorable","x14ac");
	хмл.ЗаписатьАтрибут("xmlns:x14ac","http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
	хмл.ЗаписатьНачалоЭлемента("dimension");
	//Область = ПолучитьОбласть(Лист.Количество() - 1, Лист.Колонки.Количество() - 1);
	хмл.ЗаписатьАтрибут("ref", "A1:BO15");
	хмл.ЗаписатьКонецЭлемента();//dimension
	
	хмл.ЗаписатьНачалоЭлемента("sheetViews");
	хмл.ЗаписатьНачалоЭлемента("sheetView");
	хмл.ЗаписатьАтрибут("tabSelected", "1");
	хмл.ЗаписатьАтрибут("workbookViewId", "0");
	Если  Книга.Листы[НомерЛиста-1].Заморозка <> "" Тогда
		хмл.ЗаписатьБезОбработки(Книга.Листы[НомерЛиста-1].Заморозка);
	КонецЕсли;
	хмл.ЗаписатьКонецЭлемента();//sheetView
	хмл.ЗаписатьКонецЭлемента();// sheetViews
	
	хмл.ЗаписатьНачалоЭлемента("sheetFormatPr");
	хмл.ЗаписатьАтрибут("defaultRowHeight", "15");
	хмл.ЗаписатьАтрибут("x14ac:dyDescent","0.25");
	хмл.ЗаписатьКонецЭлемента();
	Если   Книга.Листы[НомерЛиста-1].Колонки.Количество()> 0 тогда
		Записать_sheet_cols(хмл, Книга.Листы[НомерЛиста-1].Колонки);
	Конецесли;
	
	хмл.ЗаписатьНачалоЭлемента("sheetData");
	мас = Новый Массив;
	Если    Книга.Листы[НомерЛиста-1].ДанныеЛиста.Количество()> 0 тогда
		Для к = 1 По Книга.Листы[НомерЛиста-1].ДанныеЛиста.количество()  цикл
			Если Книга.Листы[НомерЛиста-1].ДанныеЛиста[к-1] <> Неопределено Тогда
				стр = "<row r='"+ СтрЗаменить(к,Символы.НПП,"") + "' spans='1:"+ Книга.Листы[НомерЛиста-1].ДанныеЛиста[к-1].количество()+"' x14ac:dyDescent='0.2'>";
			
				мас1 = новый Массив;
				Для л = 1 по Книга.Листы[НомерЛиста-1].ДанныеЛиста[к-1].количество() Цикл
					Если Книга.Листы[НомерЛиста-1].ДанныеЛиста[к-1][л-1] <> Неопределено Тогда
						мас1.Добавить(Книга.Листы[НомерЛиста-1].ДанныеЛиста[к-1][л-1].Представление);
					КонецЕсли;
				КонецЦикла;	
				мас.Добавить(стр+ СтрСоединить(мас1)+  "</row>");
				
			КонецЕсли;
		КонецЦикла;
		                                                                   
		хмл.ЗаписатьБезОбработки(СтрСоединить(мас));
	КонецЕсли;
	хмл.ЗаписатьКонецЭлемента();
	
	Если   Книга.Листы[0].ОбъединенныеЯчейки <> неопределено тогда
		Записать_merge_Cells(хмл,Книга.Листы[0].ОбъединенныеЯчейки );
	Конецесли;
	
	хмл.ЗаписатьНачалоЭлемента("pageMargins");
	хмл.ЗаписатьАтрибут("left", "0.7");
	хмл.ЗаписатьАтрибут("right", "0.7");
	хмл.ЗаписатьАтрибут("top", "0.75");
	хмл.ЗаписатьАтрибут("bottom", "0.75");
	хмл.ЗаписатьАтрибут("header", "0.3");
	хмл.ЗаписатьАтрибут("footer", "0.3");
	
	хмл.ЗаписатьКонецЭлемента();
	колтабл = 0;
	стрТаблицы = "";
	Если Книга.Таблицы.Количество()   > 0  Тогда
		
		Для каждого табл из  Книга.Таблицы Цикл
			Если табл.Значение.Лист+1 = НомерЛиста Тогда
				колтабл = колтабл+1;
				стрТаблицы = "<tablePart r:id=""rId" +  строка(Книга.Таблицы.индекс(табл)+1)+ """/>";
			КонецЕсли;
		КонецЦикла;
		
		хмл.ЗаписатьБезОбработки("<tableParts count=""" + строка(колтабл)  + """>" + стрТаблицы+"</tableParts>");
	Конецесли;
	
	хмл.ЗаписатьКонецЭлемента();
	хмл.Закрыть();
КонецПроцедуры

Библиотека получилась на 940 строк.

Ускорение по сравнению с com  в разы.

Предыдущая публикация //1c.forus.ru/public/900022/

13

См. также

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо
1. A_Max 16 24.09.18 15:00 Сейчас в теме
Поменяйте приведение числа к строке!!!
А то получите "1 000"
2. user1045404 135 24.09.18 15:10 Сейчас в теме
(1) я выгружаю по 3000 строк ошибок нет. Если про количество листов речь, то смысла не вижу.
Оставьте свое сообщение