By Vijayeta Tilak of Sonata Software Ltd
This article explains how to create a SpreadsheetML document holding data from an external datasource, by creating an XML file that holds the external data and addting it to a predefined Open XML document structure.
The asp net application (downloadable) reads the data from table in database and displays it in a single worksheet, using the .NET 3.0 System.IO.Packaging API.
The GUI ,
Fig 1
The UI displays a list of tables in the database. The user can decide on the table whose data is to be displayed.
On selection of the table
1) The application gives him the provision to view the data in the table.
2) Option on importing the data into spread sheet.
When the application starts up, based on the database chosen (connection string, in ‘web.config’), all the tables of the corresponding database are listed out in a listbox.
The user can select a single table at a time and view its data, in the gridview.
On click of the button ‘Create Spreadsheet’, the contents of the selected table are made a part of spreadsheet. The user can go ahead and select another table to import its contents. Here each table is imported to a single sheet in a single spreadsheet document.
Onclick of button ‘Add to Spreadsheet’, all the selected table contents are now consolidated into a single spreadsheet, and are available for download, through a link button provided.
The Spreadsheet with the table contents, would be
Fig 2.
On exploring the package structure, you would find that, the data is stored in the xml file ‘sharedStrings.xml’.This data is then mapped to the ‘sheetX.xml’(here, the sheets are given the names of the tables form which the data is imported).The numeric data is directly added to ‘sheetX.ml’.Since the xml file ‘styles.xml’ more or less deals with the formatting data, it’s read from a application folder(addData).
Note:
This application would throw an error before opening if the data from the table involves numeric value of length more that 8(i.e. more than the default width of the cell) as numbering format for a bigger value is not handled in this application.