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;
	Для каждого стрТз из Тз Цикл 
		Стр=стрТз.ИмяКолонки;
		СтрДл=СтрДлина(стр);
		
		Если СтрДл>ДлинаРез Тогда
			ДлинаРез=СтрДл;
			рез=Стр;
		ИначеЕсли СтрДл=ДлинаРез и  Стр>рез Тогда
			рез=Стр;
		КонецЕсли;	
	КонецЦикла;
	возврат рез;
КонецФункции

Функция ПреобразоватьВТаблицу(Тз)
	рез=новый ТаблицаЗначений;
	ПоследняяКолонка=НайтиИмяПоследнейКолонки(Тз);
	СоздатьКолонки(рез.Колонки,ПоследняяКолонка);
	Колонки=рез.Колонки;
	// Часто исползую данную функцию
	// Код можно посмотреть здесь  //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
11
.zip 34,52Mb 11 Бесплатно

См. также

Комментарии
1. Александр Лыткин (TrinitronOTV) 23.08.16 05:41 Сейчас в теме
Данным методом можно получить не значение ячейки Эксель, а цвет шрифта в этой ячейке?
2. Сергей Смирнов (Serginio) 513 23.08.16 07:34 Сейчас в теме
3. Николай Зевеке (zekrus) 97 24.08.16 07:39 Сейчас в теме
Доброе утро!
Тема весьма актуальная.
Было бы здорово если бы вы дали больше информации и по другим задачам например в Fedora!
(t-sql запросы из 1С под OS Linux, отправка почты из 1С под OS Linux).
С уважением
4. Сергей Смирнов (Serginio) 513 24.08.16 07:51 Сейчас в теме
(3) Ок посмотрю. Но Линукса то у меня нет, но код кроссплатформен.
5. Сергей Смирнов (Serginio) 513 24.08.16 10:04 Сейчас в теме
(3) А разве нельзя отправить почту средствами 1С?
6. Олег (oleg_km) 24.08.16 10:50 Сейчас в теме
Прикольно, Сергей, а я не понял - какая это сборка, как ее установить не устанавливая Оффис
7. Сергей Смирнов (Serginio) 513 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) 513 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) 513 24.08.16 16:40 Сейчас в теме
(6) Нашел ошибку, можешь обновить исходники.
10. Сергей Смирнов (Serginio) 513 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) 38 25.08.16 18:20 Сейчас в теме
Добавлю, а то может не все в курсе, этот OpenXML прикольная тема, документы можно генерить прямо на сервере без установленного офиса. А чтобы легче было разобраться с форматом, нужно юзать программу от майков "Open XML SDK *.* Productivity Tool", она умеет генерировать код, вот статья на хабре
12. Сергей Смирнов (Serginio) 513 25.08.16 23:03 Сейчас в теме
(11) Да забыл написать про неё. Спасибо. Добавлю в статью.
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа