Cleaning up Excel Table HTML for Drupal and WordPress with Dreamweaver

Often content managers need to post tables of data for a specific purpose. There are table builders out there that can do it but few more powerful than Microsoft’s Excel. After a table is built is often arduous task to paste such a table into WordPress or Drupal without breaking the formatting or preventing some other layout issue from occurring.

In this tutorial we will:

  • Export the Excel table to HTML
  • Use Dreamweaver’s Find and Replace functions to single out and remove all that extra code that causes problems.
  • Paste the cleaned up code into a CMS editor (Drupal)
    • Format table headers using Drupal

Open your table file and select the cells that make up the table to set the print area.

Select File > Save as Web Page

Save as Web Page

Save as Web Page

Name the file and select save. Check the format says Web Page (.htm).

Save as Web Page Format

Save as Web Page Format

At this point we have our table in HTML format. You can even open the document with a web browser and see that it is indeed a fully functional webpage with our table data displaying properly.

Table as Webpage

Table as Webpage

Our task now is to make sure that we can take the HTML that makes up this table, clean it up and paste it into our CMS. For this case we are using Drupal but this process can be repeated for WordPRess and even Google Sites.

Open up the .htm file in Dreamweaver

Open the .htm file in Dreamweaver

Open the .htm file in Dreamweaver

Once the file is open switch to Code View. We can see that this indeed has all of the code necessary for a standalone web page. We need to remove as much as we can so we can copy and paste this into our CMS.

Excel table HTML code

Excel table HTML code

Select Edit > Find and Replace to bring up the Dreamweaver Find and Replace dialog box.
Check that Find in: Current Document and Search: Specific Tag are selected in the options.

Dreamweaver's Find and Replace Specific Tag

Dreamweaver Find and Replace Specific Tag

At this point we want to strip everything but the HTML tags themselves. All styles, width, height, and any other appearance related attributes need to be removed.

In this example, we are looking at a specific tag: td with an attribute style equal to any value. We want Dreamweaver to remove this attribute and we want it to do this for all of the td tags in our file. Once we set these options we can select Replace All. Dremweaver will go through the current document and apply these rules and in effect remove all of the style tags from all td tags in the document.

Example if our tag looks like this:

<td height=14 class=xl201 style='height:14.0pt;border-top:none'>1</td>

It will look like this after the Find and Replace:

<td height=14 class=xl201>1</td>

Use this technique to isolate and remove all tags until you are left with only the most basic table HTML possible:

<td>1<td>

Do not worry about layout and look and feel at this point. Just make sure your table data is correct.

A little bit of logic can be used using the Find and Replace dialog box if necessary to isolate tags and reduce your time snipping out all of that extra code that Excel thought that we needed but didn’t.

You can also remove the head tag and all the code located inside these tags as well at this time.

Your cleaned up file should be fairly sparse of much code save for the table itself:

Clean table HTML

Clean table HTML

At this point you can switch back to Design view within Dreamweaver and see that the table is devoid of all styles and it should fill up the viewport due to all width and height settings being removed.

Table in Design view

Table in Design view

We are ready to copy and paste our cleaned up table code into our CMS now.

In Code view within Dreamweaver select only your table code from the opening <table> tag until the closing </table> tag. Do not copy the <html> tags.

Navigate to your Drupal webpage and go into the editor.

Within the Drupal editor select the “HTML” button. An editor box should pop up with your page’s HTML code viewable.

Drupal's HTML editor button

Drupal’s HTML editor button

Paste your cleaned up code into the editor and make any adjustments you may need. You should be all set with the table code at this time. Select Update to insert the code and close out the HTML editor window.

Pasted cleaned up table HTML

Pasted cleaned up table HTML

Suggested Action: Format Headers

At this point you can save your Drupal page and be done, but it is advised that some formatting to the table occurs for accessibility reasons. In this example we will format the headers of the table within the Drupal editor.

Place your cursor in the first header cell and select the Table Cell Properties button in the editor

Table Cell Properties button within TinyMCE editor

Table Cell Properties button within TinyMCE editor

For the Cell Type select Header, Scope is the Row (or column if you have a vertically formatted table), Select Update all Cells in Row (or column if you have a vertically formatted table). Select Update.

Table Cell Properties Settings for table headers

Table Cell Properties Settings for table headers

Within the editor we can see that the proper code has been inserted with a visual change.

Upon saving the webpage we can see that style of the header has been applied to to the table as well now.

Table header styles applied

Table header styles applied

Tags:

Back to Top