Using Excel to make the HTML for the "body" of a table

This article is about how Excel's text functions can be used to make the HTML statements to put inside the body section of a table. It's written with Blogger users in mind, but applies to anyone who needs to create a large HTML table. Two Excel example files are provided, one using CSS and one not, which have some formatting included.

HTML table code

Previously I've described tables in your blog: why you might want to use them, and some options for making a table,

These include using a HTML-code-generator to get the "skeleton" of the table. With this approach, the hardest part is putting the table content (ie the words, numbers and pictures) into the correct place in the HTML, so that it appears in right cell in the table.

This is easy enough for a small table. But if you want to make a table with more data (eg I recently posted a book-index with 100 rows by 5 columns), it can be a little tedious. Even if you prepare your posts in private (which lets you save frequently without worrying about auto-save), it can still be very hard to make sure that you put the right contents in the right cell. And because Blogger's post editor doesn't handle tables well, it can sometimes do unexpected things to them,.

But there are some alternatives.

Tools for creating the "body" section of HTML table statements

I'm sure that lots of people have written custom applications that take a text file and turn it into HTML. (Though when i googled, the first SERPs included a program for Win 3.1 / 95, which most people gave up on a long time ago!).

Many programs like Excel, Word, etc now have an option to save as HTML - so you could just use look at the file they make with a text editior (eg Notepad), and take the relevant bits. That said, they usually include a lot of extra code, eg for fonts, layouts etc, so you may get more than you bargain for.

Commercial web development packages, eg Dreamweaver, may have table editing tools - however many people cannot justify the cost of buying and learning them for smaller jobs.

Another option is to use Excel to manage your table of data, and use it's text-string manipulation functions to turn it into valid HTML code. This isn't as hard as it sounds, especially if someone else does the initial formulas for you.

Excel Templates for making HTML

First version -
cell level formatting - no CSS

Second version
with CSS styles

The first template was made before I understood CSS: it uses text-functions to put formatting code into every single table cell - though you can just leave things blank if you don't want them formatted. It may be best to use if you don't understand CSS.

The second template uses CSS styles to format the table. This means you can change the formatting of all the tables in your website just by changing the CSS rules, ie you don't have to edit each individual table. The Excel file lists the CSS styles you need to define, and the names (eg keyTableOdd for odd numbered rows) show what each style is used for.

picture of Excel spreadsheet, data ranges have different colours

Version 2: the formula area is green,
the input area is yellow, and the output area is orange

To use these templates

1 Download the template

2 Check the formatting settings in the template. Set up CSS styles in your website if you are using the 2nd version.

3 Create table header and footer statements yourself, or by using an HTML table generator.

4 Adjust the Excel template, so that

the number of rows and columns in the data area is what you need,
the formulas in the output area are right for your chosen rows and columns

The section below explains the types of formulas used: you can model new forumlas (for new columns) on the current ones, and new rows can be made by copying the formulas from an existing row.

5 Put the data for your table into the data area in the spreadsheet

6 Copy the text from the output area, paste it into a text editior (eg Notepad)

7 Use the text editor's Find/Replace function to change all cases of three apostrophes in a row (ie ''') into double quotes.

8 Copy the changed text from your text editor into the "body" section of your table statement.

This sounds like a lot of work but I've found that even if a table is only 10 rows long, using a HTML-generators-spreadsheet is a lot more efficient than trying to type the values into it row-by-row.

Understanding the Excel templates

I'm assuming that you understand a little about how Excel stores data in cells, and refers to those cells by their Row/Column combination, eg "E5" - if not, check out an Excel tutorial before reading on

Also, you need to understand the ideas of copying formulas between rows, and the use of relative co-ordinates in them to make sure that each new row or column picks up the right values. Again, an Excel tutorial would be a good idea if you're not familiar with copying formulas.

As well as these general ideas, there are here are two Excel concepts that are the basis of these templates:

1 Text concatenation functions

Excel has a wide range of functions that are used to glue strings together, or to pull them apart. Important ones used in these templates are:

= "whatever" puts a text-string with the characters whatever into the cell
= a1 & b1 combines the text strings in cells a1 and b1 (b1 goes immediately after a1)
= "This is A1: " & a1 & ". This is B1: " & B1 &"."

The third function combined the two first ones. It makes a text string like "This is A1: AahOne. This is B1: BeeOne."

Notice that it is a bit fiddly: there are spaces inside the text-strings, and a full-stop at the end, so that the output looks sensible. To make it easier to read, I've put the conent that's inside double quotes (ie " ") on a pink background.

Once you understand this type of formula, though, it's not a big jump to formulas like these which combine HTML commands and values from the cells in the spreadsheet.

="<tr class='''"&A21&"'''>"
=" <td class='''keytableID'''>"&B21&"</td>"
=" <td> " & C21 & "</td> "
=" </tr>"
=E21&F21&G21&H21

This uses a step-by-step formula creation, which makes the parts of the final statement, then glues them all together in the last formula..

2 Putting an Excel special character in the output:

HTML needs to have double-quote characters around things that should be shown as text - for example

<a href="A-FILE-URL">Anchor-text</a>

However in Excel, the double-quote is used to show the beginning and end of text strings.

At first , this may put people off using Excel, But there is a very simple way around this:

Use another (set of) characters that don't appear in the table instead, and
Before you use the the generated code, use a text-editor (eg Notepad) to change them to the required special characters.