Showing posts with label HTA. Show all posts
Showing posts with label HTA. Show all posts

Tuesday, 29 April 2014

Handy HTA to format SQL results as Table for email

Have you ever tried to email the results of a SQL query in SSMS and found that the format is a mess, just tab delimited text?  I've just searched for neat way to get the results to show as a table in a new email, and it seems that plenty of people have resorted to messing about in Programmability to wrangle SQL into creating HTML formatted text.

However, I remember from my experiments with PHP that if you cut-n-paste from a table on a webpage, the table format is preserved onto the email. I decided to play with a new HTA to show the clipboard contents....


... convert to HTML and show that in a new webpage (another HTA just for simple display purposes). From there, with any CSS tarting up you like, it can then be copied into an email, looking most spiffing. Shame that the tr:nth-child(odd/even) CSS doesn't work in HTA/MSIE for alternating row colours!

 The HTA below gives results like this :


 I hope you find this useful, either functionally or for educational purposes :)

Instructions

  1. Ctrl-C your SSMS SQL query results
  2. Double-click the HTA file/shortcut
  3. Observe the clipboard data is correct, press the button
  4. Marvel at the new HTA showing your data looking more presentable
  5. Cut-n-paste to your email. Optional step: Allow yourself a smile.

With thanks to Notepad++ and the NppExport.."Copy HTML to clipboard" plugin for the syntax colouring, here is my HTA file :  Clip Table.hta


<head> <title>My SQL Table pretty-upper</title> <HTA:APPLICATION ID="oHTA" APPLICATIONNAME="HTA Test" SCROLL="no" SINGLEINSTANCE="yes" WINDOWSTATE="maximize" > </head> <SCRIPT LANGUAGE="javascript"> function NoContextMenu(){ alert("Sorry, but right-click is not allowed."); }; function replaceAll(find, replace, str) { return str.replace(new RegExp(find, 'g'), replace); }; function Window_onLoad(){ // Use the following line to disable right-click menu and prevent View Source document.oncontextmenu = new Function ( "NoContextMenu(); return false" ); // Resize and center window.resizeTo(screen.availWidth*.75, screen.availHeight*.75); window.moveTo(screen.availWidth*0.125, screen.availHeight*0.125); // Read Clipboard, convert tabs and newlines to HTML if (window.clipboardData) // Internet Explorer { txt_str1.value = window.clipboardData.getData("Text"); contents = window.clipboardData.getData("Text"); contents = replaceAll("\t","</TD><TD>",contents); // Tabs : table column boundaries contents = replaceAll("\r\n","</TD></TR>\r\n<TR><TD>",contents); // CrLf : new table row contents = "<TR><TD>" +contents +"</TD></TR></TABLE></HTML>"; // wrap up, except for TABLE/CSS }; }; function fn_run_button(){ txt_str1.value = contents; // Write a new smarter table in new HTA in %TEMP% folder var oWSS = new ActiveXObject("wscript.shell"); hta_file = oWSS.ExpandEnvironmentStrings("%TEMP%") + "\\testhta.hta"; // alert(hta_file); // line by line alternative : var a = escaped.split("\r\n"); for (var i = 0; i < oFil.length; i++) { *use* a[i] } // Write to file ( \x22 in the CSS is escape code for " ) var oFSO = new ActiveXObject("Scripting.FileSystemObject"); var oFil = oFSO.CreateTextFile(hta_file, true); oFil.WriteLine("<HTML><style type=\x22text/css\x22>"); oFil.WriteLine("#box-table { font-family: \x22Lucida Sans Unicode\x22, \x22Lucida Grande\x22, Sans-Serif;"); oFil.WriteLine(" font-size: 12px; text-align: left; border-collapse: collapse; border-top: 3px solid #9baff1; border-bottom: 3px solid #9baff1; }"); oFil.WriteLine("#box-table th { font-size: 13px; font-weight: normal; background: #b9c9fe; border-right: 2px solid #9baff1; "); oFil.WriteLine("border-left: 2px solid #9baff1; border-bottom: 2px solid #9baff1; color: #039; }"); oFil.WriteLine("#box-table td { border-right: 1px solid #aabcfe; border-left: 1px solid #aabcfe; border-bottom: 1px solid #aabcfe; color: #669; }"); oFil.WriteLine("</style> <table id=\x22box-table\x22 >"); oFil.WriteLine(contents); oFil.Close(); alert( "continue...") oWSS.run("cmd /C start " +hta_file, 0,false); oWSS = null; window.close(); }; window.onload=Window_onLoad; </SCRIPT> <BODY bgcolor="#90BBEE" > <font face="Lucida Sans"> <CENTER> <h1>SQL Tables made better for emailing</h1> This creates a new HTA from the clipboard data, which looks better and can be pasted into an email. <P><P> Clipboard <BR> <textarea name="txt_str1" cols="120" rows="40" ></textarea> <P> <input type="button" value="Create smarter table" name="run_button" onClick="fn_run_button()"> </CENTER> </BODY>

Friday, 7 June 2013

HTA : HTML-defined GUIs for your PowerShell Scripts

In my last posting http://advancedcase.blogspot.com/2013/05/hta-html-defined-guis-for-your-scripts.html I showed an HTA containing VBScript, to be called from a batch file or Python script.

This time it's PowerShell's turn, and I've converted to JavaScript - because I've been looking at Node.js and how it has made excellent use of JS's functions being first class objects, closures, etc. Before I carry on studying all that fun, I thought I'd finish up this HTA malarkey first.

Time to run a new script .....
C:\>powershell  c:\testps.ps1


The PowerShell Script : testps.ps1 :

This uses Invoke-Expression twice - first to launch the HTA and wait, and secondly to run a piece of PS code that the HTA will have written to the temp folder (using a cmd line echo!) to set the variables.

A line of JavaScript commands is passed into the HTA call, with | vertical bars taking the place of " quotes. This allows you to run whatever code you like within the HTA.



$htacall = 'cmd /c start /wait mshta.exe "C:\your-path\testps.hta" "^ num1=3.14 ; str1=|String Param| ; alert(|Wow!|)"'
Invoke-Expression -command $htacall

$htacall = 'cmd /c type %TEMP%\testhta.ps'
$outp = Invoke-Expression -command $htacall
Invoke-Expression $outp

"Result: " + $ZZ_NUM1 + " " + $ZZ_STR1


 

The HTA with JavaScript : testps.hta :

The script in here is explained in the previous blog posting - well, the VBScript version anyway :)

This time (compared to the VBScript version) the variables are initialised in case the HTA is called directly. The BASIC version didn't complain about undeclared variables, but JS will. And I've removed the batch_path stuff, as it's not used here. As always, any comments are welcomed.

<head> <title>My HTA Test</title> <HTA:APPLICATION ID="oHTA" APPLICATIONNAME="HTA Test" SCROLL="no" SINGLEINSTANCE="yes" WINDOWSTATE="maximize" > </head> <SCRIPT LANGUAGE="javascript"> function NoContextMenu(){ alert("Sorry, but right-click is not allowed."); }; function Window_onLoad(){ // Use the following line to disable right-click menu and prevent View Source document.oncontextmenu = new Function ( "NoContextMenu(); return false" ); // Resize to quarter of screen area, centered window.resizeTo(screen.availWidth/2,screen.availHeight/2); window.moveTo(screen.availWidth/4,screen.availHeight/4); // Initialise vars in case the HTA is called without the expected code num1=0; str1=''; // Passed-in code - will execute to set variables var arrParams = oHTA.commandLine.split('^'); for (i = 1; i < arrParams.length; ++i) { // next 2 lines needed for PowerShell method of calling the HTA arrParams[i]=arrParams[i].replace(/"/g,''); // delete any " arrParams[i]=arrParams[i].replace(/\|/g,'"'); // | become " // alert(arrParams[i]); eval(arrParams[i]); } // SCRIPT-specific code : set controls from executed code txt_num1.value=num1; txt_str1.value=str1; }; function Set_Env_Vars(){ // Write PowerShell code (for exec later) to testhta.ps in %TEMP% folder var oWSS = new ActiveXObject("wscript.shell"); strBat='Echo $ZZ_NUM1=' + txt_num1.value + '; $ZZ_STR1="' + txt_str1.value + '" > "%TEMP%"\\testhta.ps'; oWSS.run('cmd /C ' + strBat ,0,true); oWSS = null; window.close(); }; window.onload=Window_onLoad; </SCRIPT> <BODY bgcolor="#ccccFF" > <!-- onmousedown = "DisplayMessage" --> <h1>Hello!</h1> Just a little test.... <P> Number &nbsp; <input type="text" value="" name="txt_num1""> <p> String &nbsp; <input type="text" value="" name="txt_str1""> <p> <input type="button" value="Return these to the batch" name="run_button" onClick="Set_Env_Vars()"><p> </BODY>