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()

The

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:

Essentially, what this says is take the value that's in cell

, combine it with the string value text, and then combine those two with the value in cell

. So, if the number 3 was in cell

and the word testing in cell

, 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

functions: One for the header row and one for the body, or the rest of the rows.

The Header Row

The

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

(column

, row

). All we are going to do is take the data in this header row and combine it with the HTML markup

and

.

The specific

function used to do this is shown below.

The Table Body

The next

function will be for the rest of the rows and will combine the data of each row with the markup

and

.

This function will first be input in cell

(see Figure 1 for a visual reference). Once complete, it will be copied down column

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

function will be referencing the cells in its same row.

The specific

function for the body rows is given below. (The

function is used to correctly format the numbers in the table, more on this function can be found in the spreadsheet's Help menu.)

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

(or at least all of the cells with data in column

) 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!