Excel or ODBC?
I currently have a small site I created for my bass fishing club. One of the pages is set up to show how many points each angler has for each lake fished. I am currently using the <Table> feature in HTML and have to input the numbers into the code after each tournament. I was thinking there must be a way to either place an excel worksheet on the page and I can then update the workbook from the web server. Or I could create a database and somehow using ODBC so that it shows the updated database fields on the website.
What are your thoughts on this?
There are ways of using Excel to update a web site, but I'm no expert on that. The usual way of linking a database behind a web page is to use PHP and SQL. But that is a steep learning curve and is really overkill unless you have a large number of anglers. So it should be possible to achieve a compromise. I'd think that it would be possible to set up a spreadsheet in such a way that an export could be cut and pasted to the web page. That should be a lot simpler, and more reliable, than manual input. That is how I would approach it.
You can do what ever works for you on the back end as long as you are serving good HTML on the front end.
I do a lot of moving data around and it comes at me in all different formats but I get a lot of Excel. And if you're on a Windows box then it's pretty straight forward to read the Excel. There are, however, two methods. You can use the Jet or you can use ActiveX.
The Jet is easier and cleaner but is does read Excel incorrectly from time to time. Use Access instead and you won't have those problems. Just use the ODBC functions and the proper connection string, http://www.connectionstrings.com/ .
The ActiveX method is something like:
$excel = new COM ('Excel.Application');
$excel->visible = true; #keeps you aware of the orphans during development
$book = $excel->workbooks->open(realpath($filepath));
$sheet = $book->worksheets(1);
for ($i=1; $i <= $sheet->usedRange->columns->count; $i++) $field_names[$i] = $sheet->cells(1, $i)->value;
Oops, I forgot something important. If you use the Jet method then then you can make SQL calls but the table name is a little tricky. If your table is on Sheet1 then the table name is Sheet1$. But that assumes that there is nothing on that sheet but that table and that the filed names are on the first row. If your table isn't the whole sheet then you can post-pend the range, use something like Sheet1$B2:Z25.
I think ODBC will be better helpful.