DevBlog: Daniel

I just wasted two hours of my life trying to find out why a table I'd build up using DOM methods wasn't displaying in IE. I was building a table like this: var cell = document.createElement('td'); var row = document.createElement('tr'); var table = document.createElement('table'); cell.appendChild( document.createTextNode('test') ); row.appendChild(cell); table.appendChild(row); document.getElementById('placeForTable').appendChild(table);..but my table wasn't showing in IE (Firefox was fine). I could alert(node.innerHTML); and see my table source, and unbelieveably, if I did a node.innerHTML = node.innerHTML; my table would show up (but any events were lost). Well it turns out that if you don't explicitly add a tbody element, IE won't show your table. I found that out here. Grr!

This is a sweet little trick that took ages to work out.

Usually when you're working with stuff in Excel you capture raw data in one column, and then write formulae to clean that data and serve up the cleaned version in another column for use.

Sometimes though, you just can't do that. Here's a how-to for letting your users type whatever random rubbish they want into a column, and then cleaning the data right there in the cell they typed into.

For this example I'll demonstrate making a column uppercase, but the trick is fairly generalisable.

1. Make sure you've saved the workbook somewhere (weirdly, in an unsaved workbook, this trick silently fails)
2. Hit Alt-F11 to get the Visual Basic editor up
3. in the Project pane on the left, double-click on the name of the sheet you want to apply the effect to, eg Sheet1
4. Paste the following code into the blank window that opens up:

Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo Whoops If Target.Count = 1 Then If Target.Column = 1 Then 'this number picks the column: 1 is column A, 2 is column B etc If Target.Row > 1 Then 'ignore row 1 which is probably a header Target.Value = UCase(Target.Value) End If End If End If Whoops: Application.EnableEvents = True End Sub
As it stands, this code will capitalise anything typed into column A, except for cell A1. You can easily change what cells and columns are covered, and what the thing actually does to cells that are included. To fiddle with which columns are covered, change line 6. Eg if you want columns C, D, E and F (ie columns 3 to 6, change it to:
If Target.Column > 2 and Target.Column < 7
If you want to affect just row 12, change line 7 to something like:
If Target.Row = 7 Then
If you want to affect the whole column, you can delete lines 7 and 9 completely.. etc etc.

Someone asked me how to get rid of the "Insert Options" button. Here's what he was getting; I've highlighted the button in this image:
You get this when you insert a row. Excel is asking what formatting you want for your new row (you can choose to copy formatting from the row above or the row below). Even when you make a choice, however, the button stays on screen, forever obscuring nearby data (you can click in another cell and the buttons stays there -- to get rid of it you have to actually edit a cell).

So, here's how to get rid of it:

1. In the Tools menu, choose Options.
2. Select the Edit tab.
3. Un-check the box next to "Show Insert Options buttons".

That's it -- you're free of the Insert Options buttoon forever (until you re-install Excel).

I'm writing some XSLT that is supposed to generate a text file. I thought I needed to pad the fields with spaces to get them to the right width, but actually I don't, it is delimited text not fixed width.

Thought I better post this somewhere I can find it again if I need to :)

<!-- this is the function for making a field the right width --> <xsl:template name="leftjustify"> <xsl:param name="content"> <xsl:param name="width"> <xsl:choose> <xsl:when test="string-length($content) &gt; $width"> <xsl:value-of select="substring($content,1,$width)"> </xsl:when> <xsl:otherwise> <xsl:value-of select="$content"> <xsl:call-template name="spaces"> <xsl:with-param name="length"><xsl:value-of select="$width - string-length($content)"></xsl:with-param> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:template> <xsl:template name="spaces"> <xsl:param name="length"> <!-- the value of this next variable is 255 spaces.. --> <xsl:variable name="longstringofspaces"><xsl:text> </xsl:text></xsl:variable> <xsl:value-of select="substring($longstringofspaces,1,$length)"> </xsl:template>

There's two named templates (read, functions), "spaces" that returns a specified number of spaces (up to 255), and one that uses spaces to do truncation or left-justification. Call it like this:

<xsl:call-template name="leftjustify"> <xsl:with-param name="content">this is too short</xsl:with-param> <xsl:with-param name="width">40</xsl:with-param> </xsl:call-template>

and the result will be the text "this is too short" with 23 spaces after it (making it 40 chars long).

This is very cool:

var typeInSet = nodeType in set(2, 3, 4, 7, 8);