Create Excel Files From SQL (Via Business Operation)

Hello

I need to create an excel file from some data 

There are some examples of  the same dated from 2016 on the posts but they don't seem to work. Also I think in last 3 years there must be some much better ways which must have come up

 

Thanks in advance for help

 

  • 0
  • 0
  • 119
  • 4
  • 1

Answers

IF you have seen the "Light weight Excel"  and its  Open Exchange  Repository
then all you have to do is

- open your file 

- use your file

execute OnPage method (from example or your personal  variation of it)

- close your file

BINGO!

to make it easier the referred method here:

ClassMethod OnPage() As %Status
 {
 set sqlStatement="SELECT TOP 23 ID,Name,%ODBCOUT(DOB) DOB,SSN FROM Sample.Person"
    ,query = ##class(%ResultSet).%New()
    ,sc = query.Prepare(sqlStatement)
  set:sc sc=query.Execute()
  quit:'sc sc
  set cols=query.GetColumnCount()
  write "<table border=1>",!,"<tr>"
  for col=1:1:cols {
   write "<th align=left><b>"_query.GetColumnHeader(col)_"</b></th>"
   }
  while query.Next() {
   write "</tr>",!,"<tr>"
    for col=1:1:cols {
      write "<td>"_query.GetData(col)_"</td>"
     }
   }
  write "</tr>",!,"</table>"
  quit $$$OK
 }
 

So you saying we make a static csp file that will display excel. This may be an easy solution but really hard to configure and pass params too and then attach it as an excel in email

misunderstanding:

I suggested to just use that EXAMPLE method to write a HTML structured table.
Variation is in the SQL statement that you pass to it 

ClassMethod any(sqlStatement as %string) As %Status  {  
     set query = ##class(%ResultSet).%New()   ,
     sc = query.Prepare(sqlStatement)
......

}

Yes, that I was already doing in my business operation that sends the email

Read through some data.  Iterate it and display a simple html in tables / rows. It works

Now requirement is to generate an excel file and then attach it to the email itself instead of displaying html table in the email

This does makes an excel and also attaches. But it doens't open in email