Announcement
Neerav Adam Verma · Jun 1, 2019

Create Excel Files From SQL (Via Business Operation)

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

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("<!--[if gte mso 9]><xml>")
do stream.WriteLine("<x:excelworkbook>")
do stream.WriteLine("<x:excelworksheets>")
do stream.WriteLine("<x:excelworksheet>")
do stream.WriteLine("<x:name>Restart Team Work Hours Report</x:name>")
do stream.WriteLine("<x:worksheetoptions>")
do stream.WriteLine("<x:selected></x:selected>")
do stream.WriteLine("<x:freezepanes>3</x:freezepanes>")
do stream.WriteLine("<x:frozennosplit></x:frozennosplit>")
do stream.WriteLine("<x:splithorizontal>3</x:splithorizontal>")
do stream.WriteLine("<x:toprowbottompane>3</x:toprowbottompane>")
do stream.WriteLine("<x:splitvertical></x:splitvertical>")
do stream.WriteLine("<x:leftcolumnrightpane></x:leftcolumnrightpane>")
do stream.WriteLine("<x:activepane></x:activepane>")
do stream.WriteLine("<x:panes>")
do stream.WriteLine("<x:pane><x:number>3</x:number></x:pane>")
do stream.WriteLine("<x:pane><x:number>1</x:number></x:pane>")
do stream.WriteLine("<x:pane><x:number>2</x:number></x:pane>")
do stream.WriteLine("<x:pane><x:number>0</x:number></x:pane>")
do stream.WriteLine("</x:panes>")
 
     
     do stream.WriteLine("<x:protectcontents>False</x:protectcontents>")
     do stream.WriteLine("<x:protectobjects>False</x:protectobjects>")
     do stream.WriteLine("<x:protectscenarios>False</x:protectscenarios>")
     do stream.WriteLine("</x:worksheetoptions>")
     do stream.WriteLine("</x:excelworksheet>")
     do stream.WriteLine("</x:excelworksheets>")
     do stream.WriteLine("<x:protectstructure>False</x:protectstructure>")
     do stream.WriteLine("<x:protectwindows>False</x:protectwindows>")
     do stream.WriteLine("</x:excelworkbook>")
     do stream.WriteLine("</xml><![endif]-->")
      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""><b><font color='"_fontColor_"' size='"_headerFontSize_"'>"_weeklyReportTitle_"</font></b></th>")
do stream.WriteLine("<th colspan=7 align=""right""><b><font color='"_fontColor_"' size='"_headerDateFontSize_"'>"_$ZDT($H,5)_"</font></b></th>")
do stream.WriteLine("</tr>") //Row 3
do stream.WriteLine("<tr bgcolor='"_headingsBgColor_"'>")
do stream.WriteLine("<th colspan=""3""><b><font color='"_fontColor_"' size='"_headingsFontSize_"'>Project</font></b></th>")
do stream.WriteLine("<th><b><font color='"_fontColor_"' size='"_headingsFontSize_"'>Milestones</font></b></th>")
do stream.WriteLine("<th><b><font color='"_fontColor_"' size='"_headingsFontSize_"'>Tasks</font></b></th>")
do stream.WriteLine("<th><b><font color='"_fontColor_"' size='"_headingsFontSize_"'>Estimated</font></b></th>")
do stream.WriteLine("<th><b><font color='"_fontColor_"' size='"_headingsFontSize_"'>Logged</font></b></th>")
do stream.WriteLine("<th><b><font color='"_fontColor_"' size='"_headingsFontSize_"'>Progress</font></b></th>")
do stream.WriteLine("<th><b><font color='"_fontColor_"' size='"_headingsFontSize_"'>Created On</font></b></th>")
do stream.WriteLine("<th><b><font color='"_fontColor_"' size='"_headingsFontSize_"'>Last Logged</font></b></th>")
do stream.WriteLine("<th><b><font color='"_fontColor_"' size='"_headingsFontSize_"'>Value</font></b></th>")
do stream.WriteLine("<th><b><font color='"_fontColor_"' size='"_headingsFontSize_"'>Average</font></b></th>")
do stream.WriteLine("<th><b><font color='"_fontColor_"' size='"_headingsFontSize_"'>Cost</font></b></th>")
do stream.WriteLine("<th><b><font color='"_fontColor_"' size='"_headingsFontSize_"'>Profit</font></b></th>")
do stream.WriteLine("<th><b><font color='"_fontColor_"' size='"_headingsFontSize_"'>25%</font></b></th>")
do stream.WriteLine("<th><b><font color='"_fontColor_"' size='"_headingsFontSize_"'>50%</font></b></th>")
do stream.WriteLine("<th><b><font color='"_fontColor_"' size='"_headingsFontSize_"'>75%</font></b></th>")
do stream.WriteLine("<th><b><font color='"_fontColor_"' size='"_headingsFontSize_"'>100%</font></b></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""><b><font color='"_accountNameFontColor_"' size='"_accountNameFontSize_"'>"_query.Get("AccountName")_"</font></b></td>") do stream.WriteLine("</tr>") //Row 2
do stream.WriteLine("<tr bgcolor='"_projectHeadingBgColor_"'>")
do stream.WriteLine("<td colspan=""2"" align=""left""><b><font color='"_fontColor_"' size='"_projectHeadingFontSize_"'>"_query.Get("ProjectId")_"</font></b></td>")
do stream.WriteLine("<td colspan=""6""><b><font color='"_fontColor_"' size='"_projectHeadingFontSize_"'>"_query.Get("ProjectName")_"</font></b></td>")
do stream.WriteLine("<td><b><font color='"_fontColor_"' size='"_projectHeadingFontSize_"'>"_$ZDT(query.Get("DateCreated"),3)_"</font></b></td>")
do stream.WriteLine("<td><b><font color='"_fontColor_"' size='"_projectHeadingFontSize_"'>£ "_$FNumber(query.Get("ProjectValue"),",",0)_"</font></b></td>")
do stream.WriteLine("<td><b><font color='"_fontColor_"' size='"_projectHeadingFontSize_"'>£ "_$FNumber(##class(Restart.Data.TeamworkWeeklyReportUsers).GetAverageUserRate(query.Get("ProjectId")),",",2)_"</font></b></td>")
do stream.WriteLine("<td><b><font color='"_fontColor_"' size='"_projectHeadingFontSize_"'>£ "_$FNumber(ProjectCost,",+",2)_"</font></b></td>")
If (ProjectProfit = 0) {
do stream.WriteLine("<td><b><font color='"_fontColor_"' size='"_projectHeadingFontSize_"'>£ 0</font></b></td>")
Else {
do stream.WriteLine("<td><b><font color='"_fontColor_"' size='"_projectHeadingFontSize_"'>£ "_$FNumber(ProjectProfit,",+",2)_"</font></b></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
} }

0
0 403
Discussion (5)0
Log in or sign up to continue

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