Using XML with Microsoft Excel

One of the most useful features for a programmer would be Microsoft Excel’s capability to support XML schemas which are user defined. Though it is not a very prominent feature which can be obviously noted it still exists and is very functional tool. In simple English it means that you can add, and modify XML data in an Excel workbook. Excel has an inbuilt feature to process the data for you.

You can perform various functions using the Microsoft excel workbook.  You can view the data using an XML list. If you need to see the entire data in one single list then you can use this option to view the data. You can sort the data alphabetically; values wise in ascending or descending order and even perform small functions of shuffling the data.

Another option is to view the data in a read only format. You can use this option when you have to view the data for reading only and you don’t have the need to perform any action or modify the data in any way. The read only files are very helpful in creating business reports really quickly. The data is shown in a grid format.

You can create a map from the structure and use this to import data from the file to the map. When you import data in to the map it basically populates the cells in the map. XML data is very flexible and can be merged into any format. You can either import the data into the map by yourself or even set up a system where it takes place automatically. In the specified time it will automatically perform the updating action. Excel automatically surrounds the mapped cells with a blue colored border.

Repeating and Non-Repeating Elements

There two kinds of elements in an excel map, repeating and non repeating elements. Non repeating elements are single and appear only once in the XML document, whereas repeating elements are that which occur frequently in an XML document. The Microsoft excel identifies the repeating and non repeating elements and automatically formats the repeating list of elements into a list when you drag and drop the XML elements. Therefore this can be used as the demarcation or difference between the repeating and non repeating elements type. Apart from these inbuilt features excel also provides some additional knick knacks. If you happen to place a repeating element adjacent to another similar element in the same row then excel places another single XML list in between. Excel also formats them as individual lists.

Import and Export Files

When a map is added in a excel sheet data can be imported or exported and this way you can create links to the excel document even from an external source. This data can also be saved in Excel format.  There is a two way relationship between XML and excel. One is converting XML data to Excel and the other is converting Excel data into XML.

• Converting XML data into excel was discussed above. We will understand the conversion of excel table into XML.
• Converting an excel table into a XML macro

You can transfer data from excel into a HTML web page using the XML data files. VBA tables can be used to export data files from excel or even Microsoft access into the html file. A visual basic application can convert any complex macro into XML file easily. When you run the application the procedure prompts you to name the output generated. This program formats the numeric data values by using a function called formchk.

However to enhance the data for viewing purpose some more additional tools can be used. XML can be used with XSL styles for better viewing and a user friendly format. For example a simple plain excel table can be displayed using the XML file on a web page. The unformatted XML file is modified and replaced with a formatted XSL sheet and the data can be viewed in well designed table instead. The XSL adds all additional features like page borders, headings, footers, notes, titles and table columns. It has a local cascading style sheet which can perform an all these actions and format the data to give it an attractive look.

The XML file is also capable of receiving instructions from the SL file for sorting data and manipulating data or modifying it. You can also create more than one option for viewing data by creating more XML documents. You can also use java script to do this function. These java scripts are typically used to highlight the functions and displayed as buttons which will send requests to perform these
actions.

Understanding the XML Style Sheet

The function of the XML tags is to describe the data in an XML file but XML cannot decide how the data is going to be displayed to the user. So the rules for formatting the data is often mentioned in the XML style sheets which have all the instructions to display the data in a certain format. XML style sheets are well formatted XML files which use the extensible style language to format the data and present it in an attractive way to the user. In a single XML file you can have several XSL sheets and present the same data in several ways.