gifts2017

1С, Linux, Excel, Word, OpenXML, ADO, Net Core

Опубликовал Сергей Смирнов (Serginio) в раздел Программирование - Внешние компоненты

В данной статье показаны примеры кроссплатформенной работы с файлами Excel и Word (xlsx,docx) с помощью библиотеки OpenXML и Net Core

Это продолжение статей Кроссплатформенное использование классов .Net в 1С через Native ВК. Или замена COM на Linux
Кроссплатформенное использование классов .Net в 1С через Native ВК. Или замена COM на Linux II
Асинхронное программирование в 1С через использование классов .Net из Native ВК

С того времени я добавил использование расширений Linq.
В этой статье я коснусь практического использования моей компоненты. А именно кроссплатформенной работы с файлами Excel и Word c помощью OpenXML и NetStandart.

Собственно ради чего эта разработка и задумывалась.
Исходники были взяты отсюда github.com/ph1ll/Open-XML-SDK/tree/netstandard
К сожалению, без Nuget подключить библиотеку к проекту нельзя. Но через CoreClr его можно подключить.

Справочную информацию по работе с OpenXML можно посмотреть здесь
msdn.microsoft.com/ru-ru/library/office/gg278325.aspx

Итак, начнем с чтения страниц Excel. Задача - преобразовать данные в ТаблицуЗначений.

Процедура ПрочитатьExcel(ИмяФайла)
	
	// Загрузим сборку 
	СборкаOpenXml=ъ(Врап.Сборка("DocumentFormat.OpenXml.dll"));
	
	// Получим типы
	SpreadsheetDocument=ъ(СборкаOpenXml.GetType("DocumentFormat.OpenXml.Packaging.SpreadsheetDocument"));
	SharedStringTablePart=ъ(СборкаOpenXml.GetType("DocumentFormat.OpenXml.Packaging.SharedStringTablePart"));
	
	CellValues=ъ(СборкаOpenXml.GetType("DocumentFormat.OpenXml.Spreadsheet.CellValues"));
	
	SharedString=ъ(CellValues.SharedString);
	Cell=ъ(СборкаOpenXml.GetType("DocumentFormat.OpenXml.Spreadsheet.Cell"));
	Sheet=ъ(СборкаOpenXml.GetType("DocumentFormat.OpenXml.Spreadsheet.Sheet"));
	Regex=ъТип("System.Text.RegularExpressions.Regex","System.Text.RegularExpressions",истина);
	
	// Вот чего не хватает в 1С так это  Regex
	ШаблонКолонки=ъ(Врап.Новый(Regex.ПолучитьСсылку(),"[A-Za-z]+"));
	ШаблонСтроки=ъ(Врап.Новый(Regex.ПолучитьСсылку(),"\d+"));
	
	// Откроем файл Excel
	doc = ъ(SpreadsheetDocument.Open(ИмяФайла, false));
	workbookPart = ъ(doc.WorkbookPart);
	
	// Строки хранятся отдельно
	// В ячейках хранится индекс
	pt=ъ(ъ(workbookPart.in(SharedStringTablePart.ПолучитьСсылку())).GetPartsOfType());
	sstpart = ъ(pt.First());
	sst = ъ(sstpart.SharedStringTable);
	ОбщиеСтроки=ъ(sst.ChildElements);
	
	workbook = ъ(workbookPart.Workbook);
	
	// Получим список страниц
	sheets = ъ(ъ(workbook.in(Sheet.ПолучитьСсылку())).Descendants());
	sheets=ъ(Врап.ПолучитьЭнумератор(sheets.ПолучитьСсылку()));
	
	
	Пока sheets.MoveNext() Цикл
		sheet= ъ(sheets.Current);
		id=ъ(sheet.Id).Value;
		ИмяСтраницы=ъ(sheet.Name).Value;
		Сообщить(ИмяСтраницы);
		worksheetPart = ъ(workbookPart.GetPartById(id));
		
		Worksheet=ъ(worksheetPart.Worksheet);
		// Так как данные хранятся только те которые имеют значения 
		// Получим тз с колонками ИмяЯчейки,ИмяКолонки,НомСтроки,ЗначениеЯчейки
		//Где  ИмяЯчейки= A1, ИмяКолонки=A,НомСтроки=1;
		Тз=ЗаписатьСтраницуВТЗ(Worksheet,ОбщиеСтроки);
		// Можно можно эту тз проиндексировать и получать доступ к ячейкам
		// через Найти  используя ИмяЯчейки или
		// НайтиСтроки используя ИмяКолонки и  НомСтроки
		
		// Получим таблицу с колонками A,B,D..AA,AB..
		Тз=ПреобразоватьВТаблицу(Тз);
		Тз.ВыбратьСтроку(ИмяСтраницы);
		
	КонецЦикла
	
КонецПроцедуры

Рассмотрим более подробно методы для получения данных о ячейках таблицы

Функция ИмяКолонки(ИмяЯчейки)
	//Получим Имя колнки ячейки
	match = ъ(ШаблонКолонки.Match(ИмяЯчейки));
	return match.Value;
	
КонецФункции

Функция НомерСтроки(ИмяЯчейки)
	// Получим Номер строки ячейки
	match = ъ(ШаблонСтроки.Match(ИмяЯчейки));
	return число(match.Value);
КонецФункции

Процедура ЗаписатьДанныеЯчейки(знач Тз,знач Ячейка,знач ОбщиеСтроки)
	Перем ТипДанных;
	
	// Получим адрес ячейки и Тип данных
	адрес=ъ(Ячейка.CellReference).InnerText;
	ТипДанных=Ячейка.DataType;
	ФлЕстьДанные=ложь;				
	Если ТипДанных<> null Тогда
		ТипДанных=ъ(ъ(ТипДанных).Value);
		// Проверим тип данных на Строку
		Если ТипДанных.Equals(SharedString.ПолучитьСсылку()) Тогда
			//В CellValue хранится индек строки в таблице общих строк
			CellValue=ъ(ячейка.CellValue);
			Text=CellValue.Text;
			ssid = Число(Text);
			ChildElement=ъ(ОбщиеСтроки.get_Item(ssid));
			ЗначениеЯчейки = ChildElement.InnerText;
			ФлЕстьДанные=истина;
		КонецЕсли;
	КонецЕсли;
	
	// Если это не строка то получим, то получим значение из  CellValue
	Если не  ФлЕстьДанные Тогда
		ЗначениеЯчейки= ячейка.CellValue;
		Если (ЗначениеЯчейки<> null) Тогда
			ЗначениеЯчейки=ъ(ЗначениеЯчейки);
			ЗначениеЯчейки=ЗначениеЯчейки.Text;
			ФлЕстьДанные=истина;
		КонецЕсли;
		
	КонецЕсли;
	
	Если   ФлЕстьДанные Тогда
		Стр=Тз.Добавить();
		Стр.ИмяЯчейки=адрес;
		Стр.ИмяКолонки=ИмяКолонки(адрес);
		Стр.НомСтроки =НомерСтроки(адрес);
		Стр.ЗначениеЯчейки=ЗначениеЯчейки;
	КонецЕсли;
	
	
КонецПроцедуры
Функция СоздатьТз()
	// Так как данные хранятся не ввиде таблицы
	// То создадим ТЗ с данными по ячейкам
	ТипЧисла = Новый ОписаниеТипов("Число",Новый КвалификаторыЧисла(10,0,ДопустимыйЗнак.Неотрицательный)); 
	Тз=новый ТаблицаЗначений;
	Колонки=Тз.Колонки;
	Колонки.Добавить("ИмяЯчейки",ОписаниеСтроки());
	Колонки.Добавить("ИмяКолонки",ОписаниеСтроки());
	Колонки.Добавить("НомСтроки",ТипЧисла);
	Колонки.Добавить("ЗначениеЯчейки",ОписаниеСтроки());
	
	возврат тз;
КонецФункции

Функция ЗаписатьСтраницуВТЗ(знач sheet,Знач ОбщиеСтроки)
	
	// Прочитаем все ячейки страницы
	cells = ъ(ъ(sheet.in(Cell.ПолучитьСсылку())).Descendants());
	cells=ъ(Врап.ПолучитьЭнумератор(cells.ПолучитьСсылку()));
	
	Тз= СоздатьТз();
	// Запишем данные ячейки в ТЗ
	Пока cells.MoveNext() Цикл
		Ячейка=ъ(cells.Current);
		ЗаписатьДанныеЯчейки(Тз,Ячейка,ОбщиеСтроки)		
	КонецЦикла;
	
	возврат Тз;
КонецФункции

Теперь нам нужно преобразовать ТЗ с данными о ячейках в Таблицу Здначений аналогичной Странице Excel

Функция ЗаписатьКолонки(Колонки,НачСтр,Разряд,КоличествоРазрядов,Сравнивать,ПоследняяКолонка)
// Процедура создает колонки которые меньше или равны имени последней колоки
// A,B,..,AA..ABC
	Для сч=КодСимвола("A") по  КодСимвола("Z") Цикл
		НовСтр=НачСтр+Символ(сч);
		
		Если  Разряд<КоличествоРазрядов Тогда
			рез= ЗаписатьКолонки(Колонки,НовСтр,Разряд+1,КоличествоРазрядов,Сравнивать,ПоследняяКолонка);
			Если Сравнивать и Рез Тогда
				возврат истина
			КонецЕсли;	
		Иначе
			Колонки.Добавить(НовСтр,ОписаниеСтроки());
			
			Если Сравнивать и НовСтр=ПоследняяКолонка Тогда
				возврат  истина
			КонецЕсли
		КонецЕсли;
	КонецЦикла;
	
	
	возврат ложь;
КонецФункции

Процедура СоздатьКолонки(Колонки,ПоследняяКолонка)
	
	// Создадим колонки учитывая разряды
	// Например если имя последней колоки ABC то колонки идут по разрядно
	//A..Z
	//AA..ZZ
	//AAA..ABC
	КоличествоРазрядов=СтрДлина(ПоследняяКолонка);
	
	Для сч=1 По  КоличествоРазрядов Цикл
		Сравнивать=сч=КоличествоРазрядов;
		рез= ЗаписатьКолонки(Колонки,"",1,сч,Сравнивать,ПоследняяКолонка);
		Если Сравнивать и рез Тогда
			возврат;
		КонецЕсли;	
	КонецЦикла;	
КонецПроцедуры

Функция НайтиИмяПоследнейКолонки(Тз)
	рез="";
	ДлинаРез=0;
	Для каждого стрТз из Тз Цикл 
		Стр=стрТз.ИмяКолонки;
		СтрДл=СтрДлина(стр);
		
		Если СтрДл>ДлинаРез Тогда
			ДлинаРез=СтрДл;
			рез=Стр;
		ИначеЕсли СтрДл=ДлинаРез и  Стр>рез Тогда
			рез=Стр;
		КонецЕсли;	
	КонецЦикла;
	возврат рез;
КонецФункции

Функция ПреобразоватьВТаблицу(Тз)
	рез=новый ТаблицаЗначений;
	ПоследняяКолонка=НайтиИмяПоследнейКолонки(Тз);
	СоздатьКолонки(рез.Колонки,ПоследняяКолонка);
	Колонки=рез.Колонки;
	// Часто исползую данную функцию
	// Код можно посмотреть здесь  http://infostart.ru/public/371762/
	// Сгруппируем данные ТЗ по номеру строки
	Тз=глСгруппироватьТзПоПолю(тз,"НомСтроки");
	сч=1;
	
	Для каждого стрТз из Тз Цикл 
		НомСтроки=стрТз.НомСтроки;
           // Добавим колонки номера которых меньше НомСтроки
		Пока сч<НомСтроки Цикл
			сч=сч+1;
			рез.Добавить();
		КонецЦикла;
                сч=сч+1;
		стр=рез.Добавить();
		ТзГр=стрТз.ТзПоГруппе;
		
		
		Для каждого стрТзГр из ТзГр Цикл
			// Можно конечно получить индекс зная смещение символа 64 относительно 1 и 26 разрядную систему
			// но найдем колонку по имени и её индекс
			Колонка=Колонки.Найти(стрТзГр.ИмяКолонки);
			стр.Установить(Колонки.Индекс(Колонка),стрТзГр.ЗначениеЯчейки); 
		КонецЦикла;	
	КонецЦикла;
	возврат рез;
КонецФункции

Теперь перейдем к чтению данных файла Word.

Функция GetPlainText(знач Элемент)
	ЗаписьXML = Новый ЗаписьXML;
	ЗаписьXML.УстановитьСтроку();

	// Получим секции и рекурсивно пройдемся по их значениям
	Секции=ъ(Элемент.Elements());         
	
	Секции=ъ(Врап.ПолучитьЭнумератор(Секции.ПолучитьСсылку()));
	
	Пока Секции.MoveNext() Цикл
		Секция= ъ(Секции.Current);
		ИмяСекции=Секция.LocalName;
		Если ИмяСекции= "t" Тогда
			Стр=Секция.InnerText;
			ЗаписьXML.ЗаписатьБезОбработки(Стр);
			
		ИначеЕсли    ИмяСекции= "cr" или ИмяСекции= "br"  Тогда
			ЗаписьXML.ЗаписатьБезОбработки(NewLine); 
		ИначеЕсли    ИмяСекции= "tab"  Тогда
			ЗаписьXML.ЗаписатьБезОбработки(Таб); 
			// Paragraph
		ИначеЕсли    ИмяСекции= "p"  Тогда
			ЗаписьXML.ЗаписатьБезОбработки(GetPlainText(Секция));
			ЗаписьXML.ЗаписатьБезОбработки(NewLine+NewLine);
		Иначе
			ЗаписьXML.ЗаписатьБезОбработки(GetPlainText(Секция));
		КонецЕсли;            
	КонецЦикла;
	
	возврат  ЗаписьXML.Закрыть();
КонецФункции

Функция ПрочитатьWord(ИмяФайла)
    СборкаOpenXml=ъ(Врап.Сборка("DocumentFormat.OpenXml.dll"));
	WordprocessingDocument=ъ(СборкаOpenXml.GetType("DocumentFormat.OpenXml.Packaging.WordprocessingDocument"));
	Пакет = ъ(WordprocessingDocument.Open(ИмяФайла, ложь));
	Элемент = ъ(ъ(ъ(Пакет.MainDocumentPart).Document).Body);
	if (Элемент = null) Тогда
		возврат ""
	КонецЕсли;
	
	возврат GetPlainText(Элемент);
КонецФункции // 

 Очень удобно использовать Productivity Tool", она умеет генерировать код Генерируем OfficeOpenXML-документы за 5 минут      

Кроме того есть множество провайдеров к различным базам данных, как MS SQL так и другим, в том числе NoSQL

Приведу пример доступа к MS SQL

СборкаSqlClient=ъ(Врап.Сборка("System.Data.SqlClient.dll"));
    SqlConnection=ъ(СборкаSqlClient.GetType("System.Data.SqlClient.SqlConnection"));
    SqlCommand=ъ(СборкаSqlClient.GetType("System.Data.SqlClient.SqlCommand")); 
    
    connection =ъ(Врап.Новый(SqlConnection.ПолучитьСсылку(),ConnectionString));
    connection.Open();
    
    ТекстЗапроса = "Select Номенклатура.DESCR Наименование  From sc84 Номенклатура where DESCR Like '%'+@Строка+'%'
    |order by Номенклатура.DESCR";
    
    
    command = ъ(Врап.Новый(SqlCommand.ПолучитьСсылку(),ТекстЗапроса,connection.ПолучитьСсылку()));
    
    Parameters=ъ(command.Parameters);
    Parameters.AddWithValue("@Строка", "ДСП");
    dr = ъ(command.ExecuteReader());
    
    Пока dr.Read() Цикл
        Сообщить(dr.get_Item("Наименование"));
    КонецЦикла;

При этом можно сделать обертку DynamicObject над SqlDataReader и использовать так

   Пока dr.Read() Цикл
        Сообщить(dr.Наименование);
    КонецЦикла;

В своих статьях я хочу донести прежде всего до 1С, что есть кроссплатформенная замена COM с помощью NetStadart.
Но, к моему большому сожалению, пока данный подход никого не интересует. Привлекает внимание только Руслиш.
Если у кого будут идеи, чем можно привлечь внимание к замене COM, пишите. Буду только рад.

Примеры и исходники - в приложенном файле. Также их можно скачать   Здесь

Скачать файлы

Наименование Файл Версия Размер Кол. Скачив.
TestVK
.zip 34,52Mb
05.09.16
5
.zip 34,52Mb 5 Бесплатно

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Александр Лыткин (TrinitronOTV) 23.08.16 05:41
Данным методом можно получить не значение ячейки Эксель, а цвет шрифта в этой ячейке?
3. Николай Зевеке (zekrus) 24.08.16 07:39
Доброе утро!
Тема весьма актуальная.
Было бы здорово если бы вы дали больше информации и по другим задачам например в Fedora!
(t-sql запросы из 1С под OS Linux, отправка почты из 1С под OS Linux).
С уважением
4. Сергей Смирнов (Serginio) 24.08.16 07:51
(3) Ок посмотрю. Но Линукса то у меня нет, но код кроссплатформен.
5. Сергей Смирнов (Serginio) 24.08.16 10:04
(3) А разве нельзя отправить почту средствами 1С?
6. Олег (oleg_km) 24.08.16 10:50
Прикольно, Сергей, а я не понял - какая это сборка, как ее установить не устанавливая Оффис
7. Сергей Смирнов (Serginio) 24.08.16 10:56
(6) oleg_km, Ссылка на исходники здесь https://yadi.sk/d/jRSHEA19svgrn
Ссылка на OpenXML NetStandart здесь

https://github.com/ph1ll/Open-XML-SDK/tree/netstandard

На взрослую Nuget OpenXML
8. Сергей Смирнов (Serginio) 24.08.16 16:39
(3) Сейчас проверил

       smtp = "smtp.yandex.ru";
	login = "XXXX@yandex.ru";
	password = "YYYYYY";
	Кому = "YYYYYYY@XXXXXXXX.ru";
	
	
	СборкаMailKit=ъ(Врап.Сборка("MailKit.dll"));
	СборкаMimeKit=ъ(Врап.Сборка("MimeKit.dll"));
	
	MimeMessage=ъ(СборкаMimeKit.GetType("MimeKit.MimeMessage"));
	MailboxAddress=ъ(СборкаMimeKit.GetType("MimeKit.MailboxAddress"));
	TextPart=ъ(СборкаMimeKit.GetType("MimeKit.TextPart"));
	
	SmtpClient=ъ(СборкаMailKit.GetType("MailKit.Net.Smtp.SmtpClient"));
	
	
	
	message = ъНовый(MimeMessage.ПолучитьСсылку());
	From= ъ(Врап.Новый(MailboxAddress.ПолучитьСсылку(),"Сергей Смирнов", login));
	ъ(message.From).Add( From.ПолучитьСсылку());
	
	ToMail=ъ(Врап.Новый(MailboxAddress.ПолучитьСсылку(),"Сергей Смирнов", Кому));
	ъ(message.To).Add(ToMail.ПолучитьСсылку());
	message.Subject = "Как дела?";
	
	ТелоСообщения=  ъ(Врап.Новый(TextPart.ПолучитьСсылку(),"plain"));
	ТелоСообщения.Text = "Здесь любое сообщение
	| что фантазия подскажет
	| Это тест отправки почты";
	
	message.Body=ТелоСообщения.ПолучитьСсылку();
	client =ъНовый(SmtpClient.ПолучитьСсылку());
	client.Connect(smtp, 465, true);
	
	// Note: since we don't have an OAuth2 token, disable
	// the XOAUTH2 authentication mechanism.
	ъ(client.AuthenticationMechanisms).Remove("XOAUTH2");
	
	// Note: only needed if the SMTP server requires authentication
	client.Authenticate(login, password);
	
	client.Send(message.ПолучитьСсылку());
	client.Disconnect(true);
	Врап.ЗакрытьРесурс(client.ПолучитьСсылку()); 


...Показать Скрыть
9. Сергей Смирнов (Serginio) 24.08.16 16:40
(6) Нашел ошибку, можешь обновить исходники.
10. Сергей Смирнов (Serginio) 25.08.16 11:42
(3) Вот SQL запрос к MS SQL



	СборкаSqlClient=ъ(Врап.Сборка("System.Data.SqlClient.dll"));
	SqlConnection=ъ(СборкаSqlClient.GetType("System.Data.SqlClient.SqlConnection"));
	SqlCommand=ъ(СборкаSqlClient.GetType("System.Data.SqlClient.SqlCommand")); 
	
	connection =ъ(Врап.Новый(SqlConnection.ПолучитьСсылку(),ConnectionString));
	connection.Open();
	
	ТекстЗапроса = "Sel ect Номенклатура.DESCR Наименование  Fr om sc84 Номенклатура where DESCR Like '%'+@Строка+'%'
	|order by Номенклатура.DESCR";
	
	
	command = ъ(Врап.Новый(SqlCommand.ПолучитьСсылку(),ТекстЗапроса,connection.ПолучитьСсылку()));
	
	Parameters=ъ(command.Parameters);
	Parameters.AddWithValue("@Строка", "ДСП");
	dr = ъ(command.ExecuteReader());
	
	Пока dr.Read() Цикл
		Сообщить(dr.get_Item("Наименование"));
	КонецЦикла;

...Показать Скрыть
11. Игорь <...> (I_G_O_R) 25.08.16 18:20
Добавлю, а то может не все в курсе, этот OpenXML прикольная тема, документы можно генерить прямо на сервере без установленного офиса. А чтобы легче было разобраться с форматом, нужно юзать программу от майков "Open XML SDK *.* Productivity Tool", она умеет генерировать код, вот статья на хабре
12. Сергей Смирнов (Serginio) 25.08.16 23:03
(11) Да забыл написать про неё. Спасибо. Добавлю в статью.
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа