Create Excel Files From SQL (Via Business Operation)
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("<!--[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
} }