About loading scores
List of available scores (Use either your FAI wiki login credentials or username: anonymous pw: blank)
About
- This is an experiment in loading scores to a website directly from an excel spreadsheet.
- It is implemented on the client side as an excel add-in (.xla)
- Tested to work in excel 2003 & 2007
- It is implemented on the server side as a simple display of the xml loaded from the client with the addition of an XLST transformation and a bit of CSS, but of course can be read as data, loaded into a DB, whatever.
Installing
- Download the excel add-in and save it somewhere on your hard disk
- Open excel and add it as an add-in
- Excel 2003 Tools --> Addins
- Excel 2007 Office button --> Excel options (at the bottom) --> Add-ins (left side) --> Manage Excel Add-ins (at the bottom) --> Go
- Browse to where you saved the .xla file and select it. Make sure the checkbox is checked against FAI Data Loader and click OK. You can say Yes or No to the option to save it to your addins library.(If you say yes it is always loaded when you start excel which might not be what you want.)
- Once installed, there should be a new FAI menu item
- Excel 2003 In the main menu at the top
- Excel 2007 In the add-ins ribbon
- Some catches
- If asked, enable macros. They are safe and do not intefere with your system at all. (I can't afford a certificate.)
- It tries to do this automatically, but you may need to manually attach a reference to the MSXML v6 library (if you need to know how, ask me)
Use
- Load a spreadsheet with some scores in it
- Click FAI --> Send scores to FAI. The first time you do this the settings page will open (Also accesible by clicking FAI --> Settings)
- In the settings page:
- Event Name Enter something like WLC 2012 or WPC 2012 or whatever concisely describes your event (keep it short because it is used in the eventual .xml filename)
- FAI Data URL Should be pre-configured to https://www.flymicro.com/faidata/in.cfm
- User name and password Use your wiki credentials - anonymous/blank will not work!
Click save. These will be permanently saved (except un/pw which are wiped when you close excel)
- In the select data page
- Data title Select a cell on your sheet which contains a description of these scores eg 'Task 1 Individual scores', 'General Team scores'. As above, keep this short as it is used in the eventual .xml filename.
- Data status Select a cell containing the scores status, eg Provisional, Final, whatever.
- Data, including column names Select the entire block of scores including the column names.
It is important to understand all three values on this page must be cell range addresses so should look something like Individual!$A$3:$H$85
- Click OK. If all went well you should receive a message back from the server. You can access the loaded scores from the listing
- To delete scores you loaded If you are logged in with your wiki credentials you can delete them on the listing page.
- Enjoy
Future
- This is just a demo of easy loading to online, in this demo the loaded files are only checked for correct xml syntax and the link to the xsl transformation is added in.
If you inspect the source of the WLC Individual scores you will see there is a column hidden by the transformation called FAI_id. This is a ficticious reference, but if every SL had a unique id then it is easy to add links (eg to pilot biography pages Etc).
With a sensibly arranged api a system like this can be used to very easily download, modify and upload any tabular data in a database in a secure way which includes close control on who can do what. (The download section coming soon)
Credits
-
James Rivera for his excellent xml tools Excel add-in (also works in excel 2007) parts of which have been used in this add-in.
- Matthew Murdoch for a fine solution to the difficult problem of url encoding utf-8 http POST or PUT data out of excel