XML or what?

[NeilKelty]

I am working with my school's basketball team on a project to devlop and design a website that will offer statistics, schedules, rosters, and all that nice stuff that goes along with sports. I must be able to quickly store and manipulate that stats so static HTML pages are not an option.

I think a MySQL database with editable browser interface (A simple content management system) would be the best option here, but my school doesn't want to allow access to the MySQL Server becase they are worried about server security, and I respect that because the last thing our school needs is for the grades to be posted all over the web, or worse LOST.

So I came up with an idea that I could update the stats and all that other information in Microsoft Excel and then export it to an XML file. I would then parse the XML file and then create the pages with the data. However, I am unsure of how to get Excel to put the data into a nice neat format except by using a macro which takes a very long time, and might become a problem later because i won't be able to move from machine to machine without copying to macro. Can anyone point me in the right direction here?

Or is there some kind of technology that I am missing here? Is there another way to do this, or should I just not use Excel? What do you guys advise?

[mark87]

You can use Excel if it's easiest for you - this should help -

http://office.microsoft.com/en-gb/assistance/HA011064961033.aspx

[NeilKelty]

I read and printed out that article before, and it doesn't seem to help very much because it talks about Excel giving you an option to open as read-only or mapping, but I don't get that option. Any suggestions?


You can use Excel if it's easiest for you - this should help

I could edit the raw XML file, but that would take a long time, so I need something quick and easy, and a teacher that doesn't know that much about XML will probably need to edit it sometimes so I need something that is easy, and Excel looks like it is the most widely used, and the school has it also.

[gsnedders]

For the XML, you could just parse it using PHP. Here is a script which takes the XML and parses it and outputs it as a table...

<?php

/* excel2table() is by Geoffrey Sneddon, www.geoffers.uni.cc/
*
* Ye Ole "Feel Free To Use it However" License [PHP, BSD, GPL].
*
* This is a stable release, 1.0. I don't foresee any changes.
*
* Feed it Excel XML, and it'll give you a table... It's a very kind script.
*
* usage: $exceltable = excel2table ($data, $charset);
*/

function excel2table ($data, $charset = 'iso-8859-1') {

$xml_data = xmlize($data);
$table = '';
foreach ($xml_data['Workbook']['#']['Worksheet'] as $worksheet) {
$table .= '<h1>' . htmlentities($worksheet['@']['ss:Name'], ENT_QUOTES, $charset) . "</h1>\n";
$table .= "<table summary=\"Excel Table\">\n\t<tbody>\n";
foreach ($worksheet['#']['Table']['0']['#']['Row'] as $row) {
$table .= "\t\t<tr>\n";
foreach ($row['#']['Cell'] as $cell) {
$table .= "\t\t\t<td>" . htmlentities($cell['#']['Data']['0']['#'], ENT_QUOTES, $charset) . "</td>\n";
}
$table .= "\t\t</tr>\n";
}
$table .= "\t</tbody>\n</table>";
}

return $table;

}

/* xmlize() is by Hans Anderson, www.hansanderson.com/contact/
*
* Ye Ole "Feel Free To Use it However" License [PHP, BSD, GPL].
* some code in xml_depth is based on code written by other PHPers
* as well as one Perl script. Poor programming practice and organization
* on my part is to blame for the credit these people aren't receiving.
* None of the code was copyrighted, though.
*
* This is a stable release, 1.0. I don't foresee any changes, but you
* might check http://www.hansanderson.com/php/xml/ to see
*
* usage: $xml = xmlize($xml_data);
*
* See the function traverse_xmlize() for information about the
* structure of the array, it's much easier to explain by showing you.
* Be aware that the array is very complex. I use xmlize all the time,
* but still need to use traverse_xmlize or print_r() quite often to
* show me the structure!
*
*/

function xmlize($data, $WHITE=1) {

$data = trim($data);
$vals = $index = $array = array();
$parser = xml_parser_create();
xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, 0);
xml_parser_set_option($parser, XML_OPTION_SKIP_WHITE, $WHITE);
if ( !xml_parse_into_struct($parser, $data, $vals, $index) )
{
die(sprintf("XML error: %s at line %d",
xml_error_string(xml_get_error_code($parser)),
xml_get_current_line_number($parser)));

}
xml_parser_free($parser);

$i = 0;

$tagname = $vals[$i]['tag'];
if ( isset ($vals[$i]['attributes'] ) )
{
$array[$tagname]['@'] = $vals[$i]['attributes'];
} else {
$array[$tagname]['@'] = array();
}

$array[$tagname]["#"] = xml_depth($vals, $i);

return $array;
}

/*
*
* You don't need to do anything with this function, it's called by
* xmlize. It's a recursive function, calling itself as it goes deeper
* into the xml levels. If you make any improvements, please let me know.
*
*
*/

function xml_depth($vals, &$i) {
$children = array();

if ( isset($vals[$i]['value']) )
{
array_push($children, $vals[$i]['value']);
}

while (++$i < count($vals)) {

switch ($vals[$i]['type']) {

case 'open':

if ( isset ( $vals[$i]['tag'] ) )
{
$tagname = $vals[$i]['tag'];
} else {
$tagname = '';
}

if ( isset ( $children[$tagname] ) )
{
$size = sizeof($children[$tagname]);
} else {
$size = 0;
}

if ( isset ( $vals[$i]['attributes'] ) ) {
$children[$tagname][$size]['@'] = $vals[$i]["attributes"];
}

$children[$tagname][$size]['#'] = xml_depth($vals, $i);

break;


case 'cdata':
array_push($children, $vals[$i]['value']);
break;

case 'complete':
$tagname = $vals[$i]['tag'];

if( isset ($children[$tagname]) )
{
$size = sizeof($children[$tagname]);
} else {
$size = 0;
}

if( isset ( $vals[$i]['value'] ) )
{
$children[$tagname][$size]["#"] = $vals[$i]['value'];
} else {
$children[$tagname][$size]["#"] = '';
}

if ( isset ($vals[$i]['attributes']) ) {
$children[$tagname][$size]['@']
= $vals[$i]['attributes'];
}

break;

case 'close':
return $children;
break;
}

}

return $children;

}

?>