Convert Excel headers to numbers

Quite often I have to convert datasets from one source to another target. Sometimes I can only export them as CSV to investigate the field order/column structure

Then, once I work out the columns I can import them. But, the headings are often not in the source, so I only have Excel/OpenOffice column headers which go from A to Z and then AA to AZ, and BA to BZ, etc etc

I needed a quick way to convert these to numbers, so I could state “the 141st column is /this/ field”

So here it is:

$(document).ready(function() {
    $("#testes").on("keyup", function(event) {
        var str = $(this).val();
        if (str.length == 1) {
        } else {
            var fstr = str.slice(0,1);
            var estr = str.slice(1,2);
            $("#results").val(get_string_value(estr) + (get_string_value(fstr) * 26));

function get_string_value(str) {
    return str.charCodeAt() - 96;

Or if you like, a fiddle: http://jsfiddle.net/8eAV6/

Convert HTML table to Excel XLS

I recently needed to update a page that dumped a HTML table into Excel – and found it dumped everything, including hyperlinks, buttons, images, etc. This didn’t sit well with Excel for Mac, and probably not a good idea.

I thought I would re-write it, but in a way that was re-usable (as this code was situated on every datagrid in the application)

So, using the PEAR extension Spreadsheet_Excel_Writer and a simple HTML DOM class, I came up with this. Its by no means finished, but as it stands, it works pretty well for what I need. Feel free to rob it and use the code.

The call is from a form which uses jQuery to copy the table as HTML into an input and then uses that in the output script ($_REQUEST[‘gridcontent’])

When its all complete, I may put it on an open source repo, such as sourceforge.

FYI, there is a section in here that takes cell text with a
in it as a split cell. So if your TD has a BR in it, it will make all the other rows merged so that the data looks nice. This is by design.
Continue reading