Working with FTP and uploading data to xlsx (Caché Object Script)
I bring to your attention an article on the following topics:
Web services? No, not heard.
The third day of our company had a chance to organize stream synchronization of data with the production complex “He”. The IT department of the customer insisted on using an FTP server, other methods of exchange were violently rejected.
We will need:
Nothing is as good as the grief of a neighbor.
I remember well how in the morning, when I came to the office, a colleague smiled at me and, not without a share of gloating, reported on the release of a new version of Mozilla Firefox. And all because the task of exporting data to excel in our software products was resolved using a browser plugin that used a library written in visual-C. After changing the security policy of FireFox applications, I had to at every change of version, at best, download a new SDK and rebuild a DLL (and the plugin as a whole), at worst - rewrite the ssh code for the DLL and the JS parser page). I don’t want to recall the need to update the plugin with users.
')
However, the third day and on our street, a truck with cookies turned over in the form of a simple way to export to Excel, which I want to share with you:
The cell, for example, can be set to the font size -
Surely not everyone knows that in COS there are abbreviated versions of operators, like these:
Set = s
Do = d
Write = w
Kill = k
Quit = q
...
Abbreviations in most cases are reflected in the documentation like this:
COS is a language without strict typing, but sometimes it is useful or even necessary to carry out typing beforehand, for this the #dim construction was created
Example:
A rare but very real case - when building a query, you must output the name of the class (Analog to $ CLASSNAME in SQL), for this purpose you can use a hidden field that is common to all classes - x__classname.
Example:
There are 2 classes A and B, which are inherited from a common ancestor of Letters Extends% Persistent. We include in the selection the x__classname field.
Version Cache - Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2012.2.1 (Build 705U) Wed Oct 24 2012 14:32:01 EDT.
- Work with FTP server using% Net.FtpSession
- An easy way to upload data to xls format
- Some useful tips
Work with FTP server.
Web services? No, not heard.
The third day of our company had a chance to organize stream synchronization of data with the production complex “He”. The IT department of the customer insisted on using an FTP server, other methods of exchange were violently rejected.
We will need:
s ftp = ##class(%Net.FtpSession).%New() – FTP ftp.Timeout = – ftp.Connect(, , , ) – FTP ftp.SetDirectory() – FTP ( ) ftp.List( , ) – , , ftp.Binary() – ftp.Retrieve(, .GlobalStream) – ftp.Delete() – ftp.Append(, ) - ftp.Logout() – FTP An example of importing files from an FTP server (files are selected by mask).
ClassMethod FTPGetFiles(ftp, fileName) As %Status { s ftp = ##class(%Net.FtpSession).%New() s ftp.Timeout = 2000 s host = "11.111.11.111" s port = 2021 s user = "myth/user" s pass = "userspass" if ftp.Connect(host, user, pass, port) { d ftp.SetDirectory("/TestDir") // s fileName = "????????t??????vK*.xml" // d ..ParserDir(ftp, fileName) s fileName = "????????a??????yK*.xml" d ..ParserDir(ftp, fileName) } q ftp.Logout() } /// , ClassMethod ParserDir(ftp, fileName) As %Status { s file = "" sx = 1 s file(x) = "" // d ftp.List(fileName, .stream) q:'$IsObject(stream) while 'stream.AtEnd { // s file = stream.ReadLine(1, .sc, .eol) if ( file = $C(10) ) { // sx = x + 1 s file(x) = "" } else { // s file(x) = file(x) _ file } if $$$ISERR(sc) { w "ERROR" q } } // d ftp.Binary() // s key = $ORDER(file(""),1) while ( key '= "" ) { // s fName = $E(file(key), 40, *) if ( $L(fName) > 0 ) { #dim GlobalStream As %GlobalBinaryStream; // fName. - FTP CP1251 d ftp.Retrieve($zcvt($zcvt(fName,"I","CP1251"),"O","CP1251"),.GlobalStream) #dim status As %String; // if ( $F(fName,"s") > 0 ) { s status = ..Parser(GlobalStream, "Product", "Shipment", parser) } elseif ( $F(fName,"d") > 0 ) { s status = ..Parser(GlobalStream, "Product", "Disposal" ,parser) } if ( status ) { d ftp.Delete(fName) } } s key = $order(file(key),1) } q ftp.Logout() } Example of exporting files to an FTP server
/// FTP ClassMethod RunExport() As %Status { s ftp = ##class(%Net.FtpSession).%New() s ftp.Timeout = 2000 s host = "11.111.11.111" s port = 2021 s user = "myth/user" s pass = "userspass" if ftp.Connect(host, user, pass, port) { d ftp.Binary() // s st = ##class(%SQL.Statement).%New() d st.%PrepareClassQuery("%File","FileSet") s rs = st.%Execute("/usr/cachesys201221/csp/sm/export_xml","*.xml","Size,Name") while rs.%Next() { // , d ftp.Delete(rs.%GetData(6)) // s stream = ##class(%FileBinaryStream).%New() // 1 - s stream.Filename = rs.%GetData(1) // - "done" if ( ftp.Append(rs.%GetData(6),stream) = $$$OK ) { d ##class(%File).Rename(rs.%GetData(1), ##class(%File).GetDirectory(rs.%GetData(1)) _ "done/" _ rs.%GetData(6)) } k stream } } d ftp.Logout() k ftp q $$$OK } Uploading data to xlsx format.
Nothing is as good as the grief of a neighbor.
I remember well how in the morning, when I came to the office, a colleague smiled at me and, not without a share of gloating, reported on the release of a new version of Mozilla Firefox. And all because the task of exporting data to excel in our software products was resolved using a browser plugin that used a library written in visual-C. After changing the security policy of FireFox applications, I had to at every change of version, at best, download a new SDK and rebuild a DLL (and the plugin as a whole), at worst - rewrite the ssh code for the DLL and the JS parser page). I don’t want to recall the need to update the plugin with users.
')
However, the third day and on our street, a truck with cookies turned over in the form of a simple way to export to Excel, which I want to share with you:
- We take (form) html table with data
- Save the table in a text file, change the extension to xls
- Open the file, do not forget to click “yes” on the warning plate and ... voila! Excel perfectly recognized our table. If necessary, cells can transmit css styles and specify the data format.
Example
#server(TestProject.MakeExcelFile($("#table_to_excel").html(), "PriceList"))# ClassMethod MakeExcelFile(Data As %String, FileName As %String) As %Status { s FileName = "/tkf/reports/" _ FileName _ "_Excel.xls" s stream = ##class(%Library.FileCharacterStream).%New() s FP = $$GetFilename^%apiCSP(FileName) if ( $L(Data) = 22 ) { d stream.CopyFrom(Data) } else { d stream.Write(Data) } d stream.SetAttribute("Content-Length",stream.Size) d stream.SetAttribute("ContentType","application/excel") d stream.SetAttribute("Charset","UTF8") d stream.SetAttribute("ContentDisposition","attachment; filename=" _ $p(FileName,"/",4)) d stream.LinkToFile(FP) d stream.SaveStream() s oid = stream.%Oid() &js<window.location="#url(%25CSP.StreamServer.cls?STREAMOID=#(..Encrypt(oid))#)#";> q $$$OK } The cell, for example, can be set to the font size -
<style>.toText{ mso-number-format:\"\@\"; } </style> <td class=”toText”> </td> Useful tips
Abbreviations
Surely not everyone knows that in COS there are abbreviated versions of operators, like these:
Set = s
Do = d
Write = w
Kill = k
Quit = q
...
Abbreviations in most cases are reflected in the documentation like this:
Typing
COS is a language without strict typing, but sometimes it is useful or even necessary to carry out typing beforehand, for this the #dim construction was created
Example:
$ CLASSNAME to SQL
A rare but very real case - when building a query, you must output the name of the class (Analog to $ CLASSNAME in SQL), for this purpose you can use a hidden field that is common to all classes - x__classname.
Example:
There are 2 classes A and B, which are inherited from a common ancestor of Letters Extends% Persistent. We include in the selection the x__classname field.
Version Cache - Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2012.2.1 (Build 705U) Wed Oct 24 2012 14:32:01 EDT.
Source: https://habr.com/ru/post/177609/
All Articles