Category: "HTML / CSS"

Generate a .csv from an HTML table on the client side

Have you ever had someone say, "This page is great! Can I get the data and use it in Excel?"

Sometimes, the beautiful data displayed is the result of complex calculations and logic which make adding a .csv or .xlsx export difficult.

As I was looking at the page today, I realized the easiest way to create a .csv might be to extract the data out of the DOM. This had the added advantage of requiring the least amount of development.

The key HTML5 features that can be leveraged for this are Data URIs and the download attribute of the anchor tag.

Data URIs allow you to embed content into a tags. In this example, the data URI is: "data:text/csv;base64,(data here)". This indicates that the data is text, containing CSV and encoded with base64.

The download attribute tells the browser to download the content associated with the link rather than navigating to it.

This code is written in plain JavaScript, no jQuery or other library. It queries the HTML and extracts the values in the table. Multi-column cells are padded to ensure the .csv content aligns to the table. Anything that isn't numeric is enclosed in double-quotes with any embedded double-quotes escaped.

I used two loops - the first to get the data from the DOM and the second to create the .csv. It could probably be done in a single loop as well.

The HTML for the link tag is:

<a id="export-link" href="" download="nifty.csv">Export to CSV</a>

  
 /* Create a .csv of the table */
                
// Get all the rows
var trs = document.querySelectorAll("tr");
 // Declare variables
var i,l,j,k,tds,cs,m,n;
var rows = [];
var cells;
                
// Loop through all the rows
l = trs.length;
for (i = 0; i < l; i++) {
  // Get all the cells on the row
  tds = trs[i].querySelectorAll("th,td");
  k = tds.length;
    if (k > 0) {
      cells = [];
        // Loop through all the cells (including headers)
        for (j = 0; j < k; j++) {
          cells.push(tds[j].textContent);
          // Check if this cell has a colspan
          cs = tds[j].colSpan;
          m = cs - 1;
          // Pad the row to ensure everything lines up
          for (n = 0; n < m; n++) {
            cells.push("");
          }
        }
        // Add the cells as a row
      rows.push(cells);
    }
}
// At this point, rows is a two dimensional array with everything from the table

// Create a data-uri of the CSV content
var base64encode, csv = "", values;

// Loop through all the rows
l = rows.length;
for (i = 0; i < l; i++) {
   // Loop through all the cells in the row
   k = rows[i].length;
   values = [];
   for(j = 0; j < k; j++) {
     value = rows[i][j];
     // If the value is not a number, enclose it in quotes
     if (isNaN(parseFloat(value,10))) {
       value = '"' + value.replace(/"/,'\\\"') + '"';
     }
     values.push(value);               }
     // Join the cells into a CSV row
     csv += values.join(",") + "\n";
   }
}
// Create the data-uri
base64encode = "data:text/csv;base64," + btoa(csv);
// Update the link
document.getElementById("export-link").href = base64encode;

This post courtesy of Game Creek Video

HTML5 Tag Challenge

For years I've enjoyed http://www.oneplusyou.com/bb/html_quiz. I have never named all the tags, but I return periodically to see if I can.

For fun, I created and HTML5 version.

I still can't name all the tags.

Windows 7 Dual-Boot CentOS 6.4 on an External USB Drive

Before you do this, make recovery disk(s) for Windows 7. Unless you already have them. You may want to backup anything you have on the Windows 7 drive, but if you’re only using it to run browsers, you haven’t invested that much anyway. Make sure you have some sort of recovery disks or you will either have to buy them or pay someone to fix your disk. Label the disk. Eventually.

The first thing you’ll need for this is a CentOS 6.4 LiveCD. Go to one of the CentOS mirrors (http://www.centos.org/modules/tinycontent/index.php?id=15) and use the following URL pattern: http://mirror.example.com/centos/6/isos/i386/CentOS-6.4-i386-LiveCD.iso. If you have a 64-bit machine, use x86_64. You can also use a USB to boot, I recommend http://unetbootin.sourceforge.net/

Install CentOS on the external drive. I recommend doing a minimal install to get the boot loader set up, then you can either reinstall or add packages to get the system set up the way you want it to.

Once CentOS is on the drive, try to boot from it. You’ll probably have to press a key (on this Asus laptop it is Esc) to choose which drive to boot from. If it won’t boot, you’ll need to adjust the grub settings.

Apparently you can’t change the Windows 7 boot loader. I’m not going to claim this is an authoritative statement, however, installing grub on the Windows 7 drive caused it to fail to boot with a ‘Hard disk error’ (or something similar). This required the Windows 7 recovery disks to recover.

Therefore, you must put the bootloader on the external drive and configure the BIOS to try to boot from the external drive first, with an option to go to Windows.

The problem I had was that by booting off a USB stick the device numbers were a bit off.

Once I had grub loaded on the external drive, I use the find command to determine how the disk was referenced. Then I manually edited the device.map file and grub.conf files after booting into the LiveCD.

For an Asus laptop with Windows 7 on the internal hard disk, and an external USB disk drive, the device.map file looked like this:

It wasn’t really generated by anaconda since I edited it, but that’s okay.

# this device map was generated by anaconda
(hd0)     /dev/sdb

I edited grub.conf using hd0 to refer to the external drive and hd1 to refer to the internal drive.

# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /boot/, eg.
#          root (hd0,0)
#          kernel /vmlinuz-version ro root=/dev/mapper/vg_asuslaptopcentos-lv_ro
ot
#          initrd /initrd-[generic-]version.img
#boot=/dev/sda1
default=0
timeout=5
splashimage=(hd0,0)/grub/splash.xpm.gz
hiddenmenu
title CentOS (2.6.32-358.6.2.el6.i686)
	root (hd0,0)
	kernel /vmlinuz-2.6.32-358.6.2.el6.i686 ro root=/dev/mapper/vg_asuslapto
pcentos-lv_root rd_NO_LUKS LANG=en_US.UTF-8 rd_NO_MD rd_LVM_LV=vg_asuslaptopcent
os/lv_swap SYSFONT=latarcyrheb-sun16 crashkernel=auto rd_LVM_LV=vg_asuslaptopcen
tos/lv_root  KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM rhgb quiet
	initrd /initramfs-2.6.32-358.6.2.el6.i686.img
title CentOS (2.6.32-358.el6.i686)
	root (hd0,0)
	kernel /vmlinuz-2.6.32-358.el6.i686 ro root=/dev/mapper/vg_asuslaptopcen
tos-lv_root rd_NO_LUKS LANG=en_US.UTF-8 rd_NO_MD rd_LVM_LV=vg_asuslaptopcentos/l
v_swap SYSFONT=latarcyrheb-sun16 crashkernel=auto rd_LVM_LV=vg_asuslaptopcentos/
lv_root  KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM rhgb quiet
	initrd /initramfs-2.6.32-358.el6.i686.img
title Windows 7
	rootnoverify (hd1,1)
	chainloader +1

What I learned:

Drive references may vary based on how the machine was booted.

You can’t just plug an external drive into a different machine and work with it, you may mess up the other machine.

Don’t change the Windows 7 boot loader, although you can use EasyBCD to recover. Maybe.

Taking the time to read the grub documentation is well worth the investment.

Constructing targetted CSS

I am working on a page with a responsive design. The page is really two pages, assembled with PHP. This allows the content and functionality to be reduced for smaller mobile devices, and enhanced for tablets and desktops.

In order to provide consistent presentation across all devices, I broke the CSS into several files. All the CSS files are then compressed with the YUI compressor and concatenated into device specific files.

The device specific files are:

  • mobile.css - For devices that are not more than 360px wide
  • desktop_and_tablet.css - For devices that are more than 360px wide. Media queries indicate resolution specific elements.

for F in "$CSSDIR/"*.css; do
        # Compress
        echo -e "\t$F"
        `$YUICOMPRESSOR "$F" > "$TMPDIR/$F"`
done;

echo Concatenating CSS ...
# Construct targeted version
CSSTMPDIR="$TMPDIR/$CSSDIR"
cat "$CSSTMPDIR/reset.css" "$CSSTMPDIR/core.css" "$CSSTMPDIR/mobile.css" > "$MINDIR/mobile.css"
cat "$CSSTMPDIR/reset.css" "$CSSTMPDIR/core.css" "$CSSTMPDIR/color.css" "$CSSTMPDIR/desktop.css" "$CSSTMPDIR/tablet.css" "$CSSTMPDIR/js.css" "$CSSTMPDIR/common.css" > "$MINDIR/desktop_and_tablet.css"

The component CSS files are:

  • color.css - All color definitions
  • common.css - CSS that is common to both tablet and desktop devices
  • core.css - CSS that is common to all devices. Examples include setting the font, some common padding and margins.
  • desktop.css - Styles targeted for desktop browsers
  • js.css - Styles for pages which rely on JavaScript. These styles are applied if the body has the ‘js’ class applied, which indicates the client supports JavaScript.
  • mobile.css - The CSS for the smallest supported browsers
  • reset.css - The reset.css, thanks to: http://meyerweb.com/eric/tools/css/reset/
  • tablet.css - Styles for devices between 361px and 980px wide, inclusive

The advantages of this approach is that the style settings are not duplicated, they need only be maintained in one place. The content delivered to the mobile device includes only the styles and content which will be used, ensuring the leanest pages possible. The tablets and desktops, receive the CSS for tablets and desktops, but display the same content. This allows those pages to adjust dynamically to width changes.

All devices include a small JavaScript file which handles the page reload if the width of the device is modified outside the target bounds of the loaded page.

This is the JavaScript that detects and responds to the width change for mobile devices. It does require JavaScript, the page does not use media queries, since that would require more CSS and content to be loaded to support transitions.

function widthCheck() {
        if ((document.body.scrollWidth && document.body.scrollWidth > 360) ||
                (innerWidth && innerWidth > 360) ||
                (document.body.clientWidth && document.body.clientWidth > 360)) {
                        location.href = "index.php?notmobile";
        } 
}

window.onload = widthCheck;
window.onresize = widthCheck;

The ?notmobile overrides the mobile device detection code.

On the tablets and desktops, jQuery is used and the code to detect and react to width changes is:

        $(window).resize(function() {
                if ($(window).width() < 360) {
                        location.href='mobile.php';
                }
        });

PHP is used on initial page load to identify mobile devices therefore, mobile.php is used for devices which are classified as mobile, index.php is used for all others. A query string parameter is used to override the detection, which allows the viewer to switch between the pages to best suit their needs.

CentOS Firefox 10 HTML5 Audio

These tags allow you to specify that audio be played through Windows Media Player Plugin if it is available, and if it isn’t, use an HTML5 audio tag.


<object id="wmp_p" data="audio.wav" type="application/x-ms-wmp" width="175" height="75" >
      <param name="autostart" value="true" />
      <param name="volume" value="10" />
      <param name="uiMode" value="mini" />
	<audio autoplay="autoplay" controls="controls" style="height:75px;width:175px">
		<source src="audio.wav" type="audio/wav" />
		No audio player available, download <a href="audio.wav" title="Download audio">audio.wav</a>
	</audio>
    </object>

This was tested under CentOS 5.4, with Firefox 10.0.2, and it worked nicely. It may also work well with Chrome under Linux, as well as under Windows.

Notes

  • Different browser support different audio formats with the tag.
  • There may be subtle coding differences between the tags and attributes. Test thoroughly and carefully.
  • There may be layout issues across different browsers, again, test thoroughly and carefully.

Audio encoding that works well under CentOS/Firefox 10.0.2

sox -V input.wav -u -b -r 8000 -c 1 output.wav

sox: Writing Wave file: Microsoft PCM format, 1 channel, 8000 samp/sec
sox: 8000 byte/sec, 1 block align, 8 bits/samp