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
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.
The XML Spreadsheet
Excel can save data in spreadsheets format in XML. When a workbook is saved Excel saves it as XMLSS automatically which means Extensible markup language spread sheet. Some objects remain an exception as they cannot be integrated into the XML spreadsheet like charts, ole objects, drawing objects. Even multisheet XML spread sheets can be created in XMLSS.
HTML or Hypertext markup language is the most ideal file which can display the excel table in a user friendly way. Excel Html can get a lot more complex than it actually looks. But some of the useful features of implementing XML in excel are
- Information can be completely mapped through excel spreadsheets and XML components.
- It can open a non excel and non spread sheet component using XML
- Flattening the XML file
- Opening a formatted XML sheet using XSL for viewing in HTML
- Can also open hand written XML sheets
- It can also publish data from a XML file to XML spreadsheet
- Copy and paste data from Excel to spreadsheet component
- Using the query table from XML in Excel
The XML web services enabled excel spread sheet is not considered to be ordinary. These sheets can use the Universal description discovery and integration or UDDI to find information on companies and they also use a service called web catalog to find business profiles and product information.
Another feature that can be discussed here is transforming the XML files using the XSL while transporting them to excel spread sheets. We already know that Microsoft Excel can save and load extensible markup language files by using a spread sheet called extensive mark up language spread sheets or XMLSS. The XMLSS format is common to the XML and as well as Microsoft Excel and for that matter the Microsoft office environment.
The main function of XML remains that it is a markup language that is used to describing structured data so that various applications can read it and access it. This kind of data interchange provides many opportunities for organizations. This also presents a challenge for the excel sheets. Manipulating the elements within a XML document any number of dimensions can be created in a document. This feature can create some confusions for the excel sheet.
To solve this kind of ambiguity the excel sheet when it imports data or opens a XML data file it looks for an element which points to the XSLT file and uses that to format the XML sheet and flatten it out. The XSLT uses both the HTML and the template elements to format and restructure the available data into an output file. The template rules start functioning when the pattern rules in the XML input data file matches the data pattern in the template of the output file. These templates also carry the information about the parent and child elements present in the document and they use XPath for this.
Xpath itself is a language that is used to identify some parts of the XML document and it has been designed to be used with the XSLT. However the expressions in Xpath just identify the elements and navigate them along the data tree so that the XSLT can process the instructions.
Prolog is the first section of the XSL sheet which is used to declare the first template instruction and the initial section of the style sheet. It understands that the XSL and XSLT are a part of XML and they use XML instructions so the first or the initial statement that is made in the template is an XML statement so that it can be identified. However conditional instructions by the programmer can further allow you to filter and process the data.