Populating a table from an Excel file

[Gordon2004]

Hey all,

Sorry about posting this in this section, wasn't sure which category it fell under. Hopefully the friendly webmaster will move it to where it belongs ;)

I'm very new to web design, and have just designed a site using CSS. It contains a lot of fairly large text filled tables, the information for which comes from one large Excel file. At the moment the tables are hard coded in HTML but I was wondering if there is any way I can create an automated procedure to populate these tables from the Excel file.

Any help would be greatly appreciated, but please keep it simple as I am new to this.

Thanks very much.

[TheDave]

It depends:

1. Are you hosting the file on a server? If so, use server side instructions (ASP is good for begineers) to incorporate the data from the table.

2. Incorporate the HTML tags into the excel file somewhere, and manually update the page with an export or copy/paste

[Gordon2004]

Thanks for your reply Dave.

The Excel file is just local file (not on the server) so I wont need to use ASP. It will be used to update the HTML tables about once a month.

You mention using HTML tags in the Excel file. Could you elaborate?

Sorry for being so slow!

[TheDave]

Well, what I would probably do is start a new worksheet in the excel file (or down the bottom where it says sheet1, sheet2, sheet3, just go to sheet2) and in the first cell start your table declaration, ie [cell A1] <table>, then in the next cell down, type [cell B1] <tr><td>, and in the cell next to that, reference your data in your main sheet. You can do by typing = and then selecting the sheet where the data is (down the bottom), and selecting the cell (it should look something like [cell B2] =sheet1!A1). Now finish your table cell definition in the next cell, ie [cell B3] </td>, repeat until finished, and then finish with </tr>. Copy down and then in the last cell add your final </table>. Once you're finished, you can export your sheet as text. I only ever do this on a small scale so actually just copy and paste into notepad, then find/replace all tabs with nothing.

edit - I added some notes to show what cell I'm talking about, so you dont type anything between square brackets, like [cell A1] :) Just in case ;)

[ytswy]

A slightly different approach, that I use for converting stock files into pages.

If you have some programming experience you can use Exel's Macros, which you write in Visual Basic.

Basically I just use the special Excel procedures and functions to navigate the spreadsheet and suck the data off into my own data structure.

You then just build a string with your data structure and html tags, and bingo you have you're code.

The advantage of this method is that (although its a pain to write initially) next month all you have to do is load your Macros and run it on the new sheet and its done.

[Gordon2004]

Thanks for your help guys.

ytswy: I think your suggestion is the way to go.
I can write basic VB code, never used Excel's macros before but hopefully I'll be able to figure them out. I haven't had a chance to play with it yet. Will let you know how it goes.

Thanks for your help.

This site rocks!

[panic]

My advice : cut/paste the Excel document into a text document. Use a programming language (even Perl will do) to split the lines by tab, and then output the way you'd like it done. It's fairly easy.

-p

[normaldude]

I tried this (below) with Excel 2000 and Word 2000, and it worked.

1) Highlight the cells you want to capture in Excel. Ctrl-C (copy) the cells.

2) Open a blank MS Word doc, and "Edit > Paste Special", and choose "HTML Format".

3) Then go "File > Save as Webpage", and save the file as an .htm file.

Now you have the Excel data as an html file.

[Gordon2004]

Hey guys,

Haven't a chance to look at this properly yet. I did try out your suggestion NormalDude, but it creates massive code bloat (I'm talking like 3 pages of code of a 10 row sample table!). Thanks for the suggestion anyway.

Panic: how would I split the lines by tab? I don't have any experience in Perl...

ytswy: Your way is still sounding the best to me.

Still open to suggestions.

Thanks guys!

[panic]

Here's the PHP solution :

<?php

$file = file("data.txt");

print "<table>
<tr><td>Field 1</td><td>Field 2</td><td>Field 3</td></tr>";

foreach($file as $line){
$line = trim($line);
$split = split("\t",$line);
print "<tr><td>$split[0]</td><td>$split[1]</td><td>$split[2]</td></tr>";
}

print "</table>";

?>

[panic]

I forgot to tell you... data.txt is your text file with your tab-delimited data.

[Gordon2004]

Thanks for your PHP solution Panic.

I've never used PHP, but I would like to learn it (at least the basics) as this would obviously make creating and updating these tables a lot quicker.

Any suggestions as to where to start? I really need the basics... ;)

[Gordon2004]

Jeez... PHP is looking complicated.

I've downloaded and installed minixampp, which is basically a package of Apache, mySQL, PERL and PHP. But now I don't know what to do with ;(

Help... please...

[danieljean]

Gordon2004- I learned PHP with a now out of date, old-fashioned dead-tree book, "Beginning PHP4" published by "WROX".

If it's your first time dealing with programming, consider whether it is really worth learning more than the basics. If you prefer design (as suggested by your comment about CSS), I would recommend finding a programmer to do work-exchanges.