Someone on Stack Overflow made a jsfiddle to export existing HTML tables to .csv using jQuery.
It does not, however, translate directly to PowerSchool custom pages, because PowerSchool has its own implementation of jQuery that’s a little funky.
I’ve done a little playing around with it, and this tweaked code seems to work.
For the table, make sure it’s enclosed with a div with an id (for this example, we’re calling it dvData):
<table class=”tableToGrid”>
</div>
Include an actual clickable link to do the download:
Put the table-to-CSV code into your jQuery document ready section (or create the section if it doesn’t exist yet:
$j(document).ready(function(){
// Adapted from http://jsfiddle.net/terryyounghk/KPEGU/ but tweaked for PowerSchool’s jQuery implementation
function exportTableToCSV($table, filename) {
// Original code
//var $rows = $table.find(‘tr:has(td)’),
// My tweak so we can get the header row
var $rows = $table.find(‘tr’),
// Temporary delimiter characters unlikely to be typed by keyboard
// This is to avoid accidentally splitting the actual contents
tmpColDelim = String.fromCharCode(11), // vertical tab character
tmpRowDelim = String.fromCharCode(0), // null character
// actual delimiter characters for CSV format
colDelim = ‘”,”‘,
rowDelim = ‘”\r\n”‘,
// Grab text from table into CSV formatted string
csv = ‘”‘ + $rows.map(function (i, row) {
var $row = $j(row),
$cols = $row.find(‘td’);
// This one line from https://gist.github.com/adilapapaya/9787842
if(!$cols.length) $cols = $row.find(‘th’);
return $cols.map(function (j, col) {
var $col = $j(col),
text = $col.text();
return text.replace(/”/g, ‘””‘); // escape double quotes
}).get().join(tmpColDelim);
}).get().join(tmpRowDelim)
.split(tmpRowDelim).join(rowDelim)
.split(tmpColDelim).join(colDelim) + ‘”‘,
// Data URI
csvData = ‘data:application/csv;charset=utf-8,’ + encodeURIComponent(csv);
$j(this)
.attr({
‘download’: filename,
‘href’: csvData,
‘target’: ‘_blank’
});
}
// This must be a hyperlink
$j(“.export”).on(‘click’, function (event) {
// CSV
exportTableToCSV.apply(this, [$j(‘#dvData’), ‘export.csv’]);
// IF CSV, don’t do event.preventDefault() or return false
// We actually need this to be a typical hyperlink
});
// End document ready
});
</script>
You should then have a link at the bottom of the report that allows users to download the existing table to .csv.
I have a full example on GitHub that just pulls all enrolled students, but you can tweak the SQL and headers, obviously.
th tags?
I made a couple of tweaks after extensive trial and error and managed a messy hack that gets the headers in but also may provide some encoding issues (not sure where those are coming from—the HTML generated by tlist_sql should be the same as the regular HTML), as well as a random extra empty row after the header row
As you can see, there’s a random “space” generated in front of each header title (the space isn’t there on the HTML page), and then in Excel that translates to Â.
Granted, this happened to only one of my custom pages, so your mileage may vary. Even for that page, it’s more useful to end users than no header row at all, and it’s far easier for them to delete an empty row in Excel than to highlight and copy the header row separately.
Unfortunately, the code as is does not export any <th> tags. I haven’t had time to fully experiment with how to tweak it to do so.
I found this fork that includes table headers, and it works great in a regular HTML file, but, for some reason, it totally breaks on a PowerSchool custom page, even if you change the $ jQuery references to $j, which is weird, because it seems to be almost the exact same method, except it extracts out some of the other pieces to be functions, which get called twice.
DataTables?
Also, using DataTables seems to work fine in regular HTML, but I haven’t found a way to properly implement it in PowerSchool custom pages. Here are the problems I’ve found:
- No matter what you do with the width of the datatables element in CSS, if it’s within the box-round div on the custom page, it will be pushed down (lots of extra white space at the top) if the sidebar is expanded. I can’t figure out how to stop that from happening.
- You can use jQuery.noConflict(true) to separate the DataTables jQuery from the PowerSchool jQuery, but then no other jQuery commands will run. It’s very strange.
- Even then, you have to put the CDN from DataTables above the ~[wc:commonscripts], or even the noConflict() won’t work at all.
sqlReports4
Someone on PowerSource forums mentioned that sqlReports4 would probably be the best solution for making reports that can then be exported as .csv. I tried it out, and it looks promising, assuming you can get the SQL statement just right. My SQL skills are beginner-to-intermediate and not advanced, so I sometimes use custom pages with normal SQL (some joins, and some if/else) and then futz with the results using JavaScript to push them to the appropriate places. I may do a separate blog post on sqlreports4…