1C, Linux, Excel, Word, OpenXML, ADO and Net Core

Good day to the people of Habratchane! Today I will continue to torment you with the great and mighty Ruslish. This is a continuation of the articles:

» Development → Cross-platform use of .Net classes from unmanaged code. Or analogue IDispatch on Linux
» Development → Cross-platform use of .Net classes in 1C through Native VK. Or replacing COM with Linux
» Cross-platform use of .Net classes in 1C through Native VK. Or replacing COM with Linux II
» Asynchronous programming in 1C via .Net Native VK

Since then, I have added the use of Linq extensions. In this article I will touch on the practical use of my component. Namely, cross-platform work with Excel and Word files using OpenXML and NetStandart.
')
Actually for the sake of what this development was conceived. Sources were taken from here . Unfortunately, without Nuget you cannot connect the library to the project. But through CoreClr it can be connected. Background information on working with OpenXML can be found here .
How do I ... (Open XML SDK)

So let's start by reading the pages of Excel. The task is to convert the data to the Table of Values.

 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.. =(); .();   

Let us consider in more detail the methods for obtaining data on table cells.

  () //    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); (,,) ;  ;  

Now we need to convert the TOR with cell data into a Table of Values ​​similar to the Excel Page

  (,,,,,) //           // 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   //         =.(.); .(.(),.); ; ;  ;  

We now turn to reading the data in the Word file.

  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 is very convenient to use, it can generate code. We generate OfficeOpenXML documents in 5 minutes.

In addition, there are many providers to various databases, both MS SQL and others, including NoSQL

I will give an example of access to 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("")); ; 

In this case, you can make a DynamicObject wrapper over the SqlDataReader and use

  dr.Read()  (dr.); ; 

In my articles, I want to convey first of all to 1C that there is a cross-platform replacement of COM using NetStadart. But to my great regret, nobody is interested in this approach yet. Attracts attention only Ruslish. If anyone has any ideas what you can draw attention to replacing COM write. I would be glad.

Examples and sources can be downloaded here .