Using Spreadsheets to Easily Create HTML Tables and Forms

A while ago I published a post about cities and their populations that contained a large table with six columns and about seventy rows. The table was originally downloaded and modified in Microsoft Excel^®, and to convert that spreadsheet table to valid XHTML “by hand” would be excruciating.

Luckily, there’s an easy way to convert a table in a spreadsheet to valid XHTML markup using the spreadsheet function concatenate().

Concatenate()

The concatenate() function allows you to easily combine the tabular Excel data with XHTML markup by stringing together text and the data contained in the spreadsheet cells.

The function syntax is simple: =concatenate(A1,”text”,A2)

Essentially, what this says is take the value that’s in cell A1, combine it with the string value text, and then combine those two with the value in cell A2. So, if the number 3 was in cell A1 and the word testing in cell A2, the function would output: 3texttesting

Enough talk! Show me a screen shot.

Figure 1 is a screen shot of the original MS Excel^® table to give an idea of the workspace.

Figure 1: The original table in MS Excel

As you can see, we have seven columns and a large number of rows. To create our table we will need to have two custom concatenate() functions: One for the header row and one for the body, or the rest of the rows.

The Header Row

The concatenate() function for the header row will only be in one cell. If you reference back to Figure 1 you will see that the header data is in row 2, and it ranges from column A to column G (with column A being blank).

We are going insert our function in cell I2 (column I, row 2). All we are going to do is take the data in this header row and combine it with the HTML markup <tr> and <th>.

The specific concatenate() function used to do this is shown below.

=CONCATENATE("<tr><th>","Rank","</th><th>",B2,"</th><th>",C2,"</th><th>",D2,"</th><th>",E2,"</th><th>",F2,"</th><th>",G2,"</th></tr>")

The Table Body

The next concatenate() function will be for the rest of the rows and will combine the data of each row with the markup <tr> and <td>.

This function will first be input in cell I3 (see Figure 1 for a visual reference). Once complete, it will be copied down column I all the way to the last row in the table. The cell values in the function are relative; so as the function is copied down the column, each cell with the concatenate() function will be referencing the cells in its same row.

The specific concatenate() function for the body rows is given below. (The text() function is used to correctly format the numbers in the table, more on this function can be found in the spreadsheet’s Help menu.)

=CONCATENATE("<tr><td>",TEXT(A3,"0."),"</td><td>",B3,"</td><td>",C3,"</td><td>",TEXT(D3,"#,##0"),"</td><td>",TEXT(E3,"#,##0"),"</td><td>",TEXT(F3,"0.00%"),"</td><td>",TEXT(G3,"#,##0"),"</td></tr>")

Once that cell has been copied down, the spreadsheet will be similar to the screen shot shown in Figure 2.

Figure 2: The table in MS Excel with the concatenate() function inserted.

Next Steps

Lastly, you need to get this from the spreadsheet to the web page. First, select all of column I (or at least all of the cells with data in column I) and copy them. Next, paste the data, either in the same column or a blank one, using Edit > Paste Special. When the dialogue box appears change the Paste selection from All to Values and click OK (visual reference).

This gives us a column with absolute data instead of relative cell references. Now you have a HTML table (sans opening and closing <table> tags) that is ready to be copied and pasted anywhere.

Forms and More

This same method can be applied to other situations as well: A drop-down select box with Country or State names, a large field of check box options, a lengthy ordered list, etc. More than likely, any type of repetitive markup can be sped up using this method.

Also, don’t miss the article on using spreadsheets to brainstorm grid layouts for more spreadsheet handiness.

Have any questions or need further clarification? Let me know in the comments below!