Create Excel Files From SQL (Via Business Operation)

Announcement

Neerav Verma · Jun 1, 2019

This Utility creates an xlsx file with the file name provided.
There are mutliple ways to write data to it, I am retrieving it via Sql queries in this example.

Include %occFile Class Utils.XLSX
{
ClassMethod DateTime() As %String [ CodeMode = expression ]
{
$TR($ZDateTime($H,3),"- :")
}

ClassMethod GenerateXLSX(
IsActive As %Boolean,
Output FileName As %String) As %Status
{
Set stream = ##class(%Stream.FileCharacter).%New()
Set tmpFile = ##class(%File).TempFilename("xls") If (IsActive) {

s lutFileName ="Test"_..DateTime()
Set tmpFile = ##class(%File).GetDirectory(tmpFile)_lutFileName_".xls"
If (##class(%File).Exists(tmpFile)){
do ##class(%File).Delete(tmpFile)
}
}
Set FileName = tmpFile
Set sc = stream.LinkToFile(tmpFile) //Fetch Table Colors
#dim headerBgColor = ##class(Ens.Util.FunctionSet).Lookup("Weekly Report Colors", "Header BG Color")
#dim headerFontSize = ##class(Ens.Util.FunctionSet).Lookup("Weekly Report Colors", "Header Font Size")
#dim headerDateFontSize = ##class(Ens.Util.FunctionSet).Lookup("Weekly Report Colors", "Header Date Font Size")
#dim headingsBgColor = ##class(Ens.Util.FunctionSet).Lookup("Weekly Report Colors", "Headings BG Color")
#dim headingsFontSize = ##class(Ens.Util.FunctionSet).Lookup("Weekly Report Colors", "Headings Font Size")

#dim reportFontSize = ##class(Ens.Util.FunctionSet).Lookup("Weekly Report Colors", "Report Font Size")

do stream.WriteLine("<html xmlns:o=""urn:schemas-microsoft-com:office:office""")
do stream.WriteLine("xmlns:x=""urn:schemas-microsoft-com:office:excel""")
do stream.WriteLine("xmlns=""http://www.w3.org/TR/REC-html40"">")
do stream.WriteLine("<head>")
do stream.WriteLine("<meta http-equiv=Content-Type content=""text/html; charset=windows-1252"">")
do stream.WriteLine("<meta name=ProgId content=Excel.Sheet>")
do stream.WriteLine("<meta name=Generator content=""Microsoft Excel 11"">") //do stream.WriteLine("Content-Type: application/msexcel")
//do stream.WriteLine("Content-Disposition: attachment; filename=REPORT.xls")
do stream.WriteLine("")
 do stream.WriteLine("</head>")
do stream.WriteLine("<body>") do stream.WriteLine("<table border=""1"" width=""100%"" style=""align:left; table-layout: auto"">") //Row Title
do stream.WriteLine("<thead>")
do stream.WriteLine("<tr bgcolor='"_headerBgColor_"'>")
do stream.WriteLine("<th colspan=11 align=""left"">"_weeklyReportTitle_"</th>")
do stream.WriteLine("<th colspan=7 align=""right"">"_$ZDT($H,5)_"</th>")
do stream.WriteLine("</tr>") //Row 3
do stream.WriteLine("<tr bgcolor='"_headingsBgColor_"'>")
do stream.WriteLine("<th colspan=""3"">Project</th>")
do stream.WriteLine("<th>Milestones</th>")
do stream.WriteLine("<th>Tasks</th>")
do stream.WriteLine("<th>Estimated</th>")
do stream.WriteLine("<th>Logged</th>")
do stream.WriteLine("<th>Progress</th>")
do stream.WriteLine("<th>Created On</th>")
do stream.WriteLine("<th>Last Logged</th>")
do stream.WriteLine("<th>Value</th>")
do stream.WriteLine("<th>Average</th>")
do stream.WriteLine("<th>Cost</th>")
do stream.WriteLine("<th>Profit</th>")
do stream.WriteLine("<th>25%</th>")
do stream.WriteLine("<th>50%</th>")
do stream.WriteLine("<th>75%</th>")
do stream.WriteLine("<th>100%</th>")
do stream.WriteLine("</tr>")
do stream.WriteLine("</thead>")

Set sqlStatement = " QUERY"

Set query = ##class(%ResultSet).%New()
Set sc = query.Prepare(sqlStatement)
Set:sc sc = query.Execute()
quit:'sc sc while query.Next() {

do stream.WriteLine("<tr bgcolor='"_accountNameBgColor_"'>")
do stream.WriteLine("<td colspan=""18"">"_query.Get("AccountName")_"</td>") do stream.WriteLine("</tr>") //Row 2
do stream.WriteLine("<tr bgcolor='"_projectHeadingBgColor_"'>")
do stream.WriteLine("<td colspan=""2"" align=""left"">"_query.Get("ProjectId")_"</td>")
do stream.WriteLine("<td colspan=""6"">"_query.Get("ProjectName")_"</td>")
do stream.WriteLine("<td>"_$ZDT(query.Get("DateCreated"),3)_"</td>")
do stream.WriteLine("<td>£ "_$FNumber(query.Get("ProjectValue"),",",0)_"</td>")
do stream.WriteLine("<td>£ "_$FNumber(##class(Restart.Data.TeamworkWeeklyReportUsers).GetAverageUserRate(query.Get("ProjectId")),",",2)_"</td>")
do stream.WriteLine("<td>£ "_$FNumber(ProjectCost,",+",2)_"</td>")
If (ProjectProfit = 0) {
do stream.WriteLine("<td>£ 0</td>")
} Else {
do stream.WriteLine("<td>£ "_$FNumber(ProjectProfit,",+",2)_"</td>")
}
do stream.WriteLine("<td></td>")
do stream.WriteLine("<td></td>")
do stream.WriteLine("<td></td>")
do stream.WriteLine("<td></td>")
do stream.WriteLine("<td></td>")
do stream.WriteLine("</tr>") //STEP 2

do stream.WriteLine("</tr>") Do stream.WriteLine("</table>") do stream.WriteLine("</body>")
do stream.WriteLine("</html>")
Set sc = stream.%Save() Quit sc
} }

Robert Cemper · Jun 1, 2019

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
 }

5 0

Neerav Verma · Jun 4, 2019

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

0 0

Robert Cemper · Jun 4, 2019

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)
......

}

1 0

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

Neerav Verma · Jun 17, 2019

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