Fig. 5. stdinterface.xsl






CISE Technical Report TR03-012, July 2003


Student Name |Exaiml Exax2

Kevin Ayers 61 50

John Buisson 99 66


Fig. 6. HTML produced by stdinterface.xsl stylesheet



xmlns:xsl="http://www.w3.org/1999/XSI







student)"/>





<br /> <xsl:value-of select="@name"/> Exam Scores<br />



Group Title: Department of Computer and Information Science and Engineering Technical Reports
Title: Using XML/XSLT as an alternative to Microsoft Excel
CITATION PDF VIEWER THUMBNAILS PAGE IMAGE
Full Citation
STANDARD VIEW MARC VIEW
Permanent Link: http://ufdc.ufl.edu/UF00095596/00001
 Material Information
Title: Using XML/XSLT as an alternative to Microsoft Excel
Series Title: Department of Computer and Information Science and Engineering Technical Report ; 03-012
Physical Description: Book
Language: English
Creator: Padala, Pradeep
Publisher: Department of Computer and Information Science and Engineering, University of Florida
Place of Publication: Gainesville, Fla.
Publication Date: July, 2003
Copyright Date: 2003
 Record Information
Bibliographic ID: UF00095596
Volume ID: VID00001
Source Institution: University of Florida
Holding Location: University of Florida
Rights Management: All rights reserved by the source institution and holding location.

Downloads

This item has the following downloads:

2003343 ( PDF )


Full Text


CISE Technical Report TR03-012, July 2003


Using XML/XSLT as an Alternative to Microsoft

Excel

Pradeep Padala
Computer & Information Science & Engineering
University of Florida
Gainesville, Florida 32611-6120
Email: ppadala@cise.ufl.edu


Abstract-In CISE1, instructors usually maintain student
records in Microsoft Excel. This causes serious interoperability
and flexibility issues. Alternatives like storing the data in flat
files or comma separated value (CSV) files require glue code
to be written. XML/XSLT provide an exciting alternative to
representing the data in a platform independent way. In this
paper, we design XML documents for representing student
records and XSL transformations to apply on the documents. We
conclude by showing that XML/XSLT offer a distinct advantage
over the alternatives.


I. INTRODUCTION
XML[1] provides a convenient way of representing het-
erogeneous data. It provides interoperability and an easy of
manipulating the data with tools like XSLT[2]. Microsoft
Excel is a spreadsheet and analysis program that is used to
store data like payrolls, student grades etc. Excel provides
various ways to interact with the data using macros. But, Excel
can only be run on windows and has serious interoperability
problems with other office suites like OpenOffice.
At CISE, instructors maintain student records containing
various information like project grades, exam grades and extra
credit. Usually, they are stored in Excel causing interoperabil-
ity problems with UNIX applications.
As a teaching assistant for CEN3031 (Introduction to Soft-
ware Engineering), I have developed various methods to cope
up with the platform dependence of Excel. In this paper, first,
I explain the basic problem associated with using Excel to
store data and basics of data representation. Then, the proposed
solution for representing the data in XML is explained. Next,
variations of the document and benefits of each representation
are discussed. Finally, the XSL transformations applied to the
XML document and resulting HTML output is shown.

II. THE PROBLEM DATA REPRESENTATION
In CEN3031, we have student records containing grades.
The grades are entered by a TA and are shown to the students
on on-line services. The on-line services serve the content
using CGI (Common Gateway Interface).
Student records often contain simple fields like name, ssn,
examl grade etc. Using Excel to store and manipulating the

This article is the result of a discussion with Dr. Cubert regarding using
XML/XSLT for representing student records in CEN3031
'Computer & Information Science & Engineering




blah blah blah
blah blah blah



Fig. 1. Sample listing


records is straight-forward and easy. But, Excel is dependent
on windows platform. The data can also be represented in
simple text or csv (comma separated value format) files. Due
to simple nature of these files, they require glue code for
manipulation. Though the glue code can be developed quickly
using scripting languages like Perl, writing the code is still a
significant overhead for course staff. On the other extreme, we
have databases that can store information in a very structured
way. Though this is the best choice for large data, for simple
data like student grades it is an overkill.
XML with XSLT is the right choice for representing the
data. XSLT can easily be applied to produce HTML output
that can be served using CGI.

III. XML RECORDS
We can create a simple record structure with XML docu-
ments. Theoretically we don't need to have a DTD. Here I
took liberty and created a simple XML document whose top
level element is and contains nodes.
Think of them as records. Figure 1 shows a sample of how a
student record can be represented in XML. Figure 2 shows the
listing of a complete XML document containing two student
records.
This document contains two student records, whose con-
tents can be included in any order. Meaning, I can have
any where in the record.
The fields need not be in order. This solves the problem
of some one sorting the excel file and messing the master
document's structure.

IV. CONVERSION TO HTML
This section describes the way XSL transformations are
used to produce HTML output. We can do variety of things
using XSLT. The main concept is to separate the data and






CISE Technical Report TR03-012, July 2003


?xml version="l.0"?>




Kevin
Ayers
al234xyz
12345678
7EG
CEN

352
1234567

abc@bbc.com
kma

61


50






John
<1astname>Buisson
cl234abc
12345678
7EG

352
1234467

CEN
jb
bbc@abc.com

99


66






Fig. 2. XML student records (student.xml)


presentation. We write an XSL style sheet which can be
applied using an XSLT processor like xsltproc. This converts
the XML document to something useful like html or comma
separated file or even pdf.

A. Student Roster Style sheet
First, I wrote a simple XSL style sheet which just lists the
information in plain tabular form. The XSL style sheet is in
figure 3 and the resulting HTML is shown in figure 4
If you look at the original document closely, you observe
that the phone number is divided into area code and number,
which makes it easy to use a stylesheet which outputs a phone
number like

[areacode]-[number]

If one day we decide to get rid of the '-', we can simply
delete a character from the stylesheet.

B. Student Interface Style sheet
I wrote another style sheet, which provides a student inter-
face. The XSL style sheet and output HTML are shown in
figure 5 and 6 respectively.
This style sheet is much simpler and I could have used
sorting as well.

C. Style sheet for producing averages
Another style sheet, just prints the averages of all exams.
Interestingly, I figured out that having jexami, nodes with at-
tribute names like is cumbersome
to manipulate. So I changed student.xml to 7
This listing has similar content except that iexamli iexam2,
nodes replacing the plain exam, nodes. This is a bit of hard-
coding but makes things simpler. Coming back to averages,
the style sheet is pretty simple. The style sheet and resulting
HTML are shown in figure 8 and 9 respectively.

V. SUMMARY AND CONCLUSIONS
We have described a simple method to represent student
records in XML. XSL transformations can be used to convert
the original XML into various useful forms. The following is
a summary of the techniques.
XML/XSLT solves the problem of multiple people work-
ing on the student grades. Different TAs just work with
their XML documents and turn it over to main person
entering grades. All he has to do is to cat them and apply
the style sheet. The style sheets will take care of sorting
etc...
The document need not have the nodes in order. The TA
need not worry about having node before
. He/She can put them in any order he/she
wishes. For consistency, a simple DTD can be created.
It's text based. So people like me can use their favourite
text editor to enter the grades. Or We can create a web-
based front end.
We can also create a comma separated fields file and
import it into Excel







CISE Technical Report TR03-012, July 2003





:?xml version='1.0'?>
:xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version='l.0'>
:xsl:output method="html"/>

:xsl:template match="/course">
:html>
:head>
:title>


scores
:/title>
:meta http-equiv="Content-Type" content="text/htr
:/head>
:body>







il; charset=iso-8859-1"/>


'assword
I>Phone




First Name Second Name SSN Class Major E-Mail Username TA-
Examl Exam2
Section

'body>
'html>
'xsl:template>

sl:template match="student">



:xsl:template m
:td>


ates st
ates st
ates st
ates st
ates st
ates st


="firstname"/>
"password"/>
="class"/>
="phone"/>
="username"/>
="sect"/>


atch="ta sect">


:xsl:value-of select="@name"/>


:/td>
:/xsl:template
:xsl:template
:td>


match="phone">


:xsl:value-of select="areacode"/>-

-/td>
:/xsl:template
:xsl:template
:td>


of select="number"/>


match="exam">



'td>
'xsl:template>


It of

>f select="@max"/>


:!--Default T(
:xsl:template
:td>


:/td>
:/xsl:template>
:!--Ignore the t
:xsl:template me
:/xsl:template>
:/xsl:stylesheet


pmplate -->
match="*">


of select="."/>



text nodes (the
itch="text()">


white spaces)


Fig. 3. roster.xsl


:tr>
:/tr>


.:app.
Sapp.
.:app.
.:app.
.:app.
.:app.








CISE Technical Report TR03-012, July 2003


firat Ne Seeod Nuame Paisword SS Clau MNjIr hcne EMadil Usemume TA Scrin ExRMl Eiam2
S .I.14 1 I F 1 I 1' l ll U 1 *1 F-1 I_ i r



Fig. 4. HTML produced by roster.xsl stylesheet



xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version='l.0'>





<br /> <xsl:value-of select="@name"/> Student Interface<br />







Student Name Examl Exam2
























Exam Name Average
Exam l out of
Exam 2 out of











Fig. 8. averages.xsl






CISE Technical Report TR03-012, July 2003


Exam Name Average

Exami 80 outof00
:?xml version="1.0"?> Exam2 58 outof70




Kevin
Ayers
al234xyz
12345678
7EG
CEN

352
1234567

abc@bbc.com
kma

61


50






John
<1astname>Buisson
cl234abc
12345678
7EG

352
1234467

CEN
jb
bbc@abc.com

99


66






Fig. 7. Modified student record XML document


Fig. 9. HTML produced by averages.xsl stylesheet


REFERENCES
[1] T. Bray, J. Paoli, and C. M. Sperberg-McQueen (Eds), ""Extensible
Markup Language (XML) 1.0 (2nd Edition)"," W3C Recommendation,
2000.
[2] "XSL Transformations (XSLT)," 1999,
http://www.w3.org/TR/xslt.




University of Florida Home Page
© 2004 - 2010 University of Florida George A. Smathers Libraries.
All rights reserved.

Acceptable Use, Copyright, and Disclaimer Statement
Last updated October 10, 2010 - - mvs