Tuesday, 16. August 2005
Importing MS Excel into Lotus Notes via XML04:43:01 PMWritten By : Stephan H. WisselCategory : XML
Location : Singapore
A very typical task in any Notes environment
is to import MS-Excel into Lotus Notes. While the client sports import
filters (with an eventual round trip through the venerable 1-2-3 format,
the server doesn't allow an import. OLE is rarely an option since (rightfully)
any administrator rather runs naked into the CEO's office than to allow
desktop applications (read MS Office) to be installed on the server.
Since Office 2000 there is a new possibility: One could save an MS-Excel file as XML Spreadsheet and use XML processing to extract the data. However the rather harmless looking table:
turns into quite a LOT of XML. Before even thinking of processing it we need to clean-up the code.
When you look at the Excel XML Workbook (the full vesion would be a bit lenghty, so below there's only the part relevant for us), you find a lot of stuff that is not relevant for your import, so you need to filter it out. The biggest obstacle however is the way how rows, columns and cells are handled:
<Row>
<Cell>
<Data ss:Type="String">T3</Data>
</Cell>
<Cell>
<Data ss:Type="String">Mary</Data>
</Cell>
<Cell ss:Index="4">
<Data ss:Type="String">Bejing</Data>
</Cell>
</Row>
Some clever XSLT is needed (this usually is Pit's domain, but I had itchy fingers <g>).
The cells in the XML Worksheet don't contain position information (neither row or column information). So you only can us XSLT position() to get the row/column. Unless a cell is empty. Then the XML simply uses ss:Index to tell, that there is a number of empty cells before it. When I tried to figure out how to cover that in XSLT I hit the wall. Luckily the XSL mailing list at lists.mulberrytech.com came to the rescue. Kaila Kaarle pointed me to the solution how to use a recursive XSLT call to manage that. So I finally managed to transform above XML Spreadsheet into a more stripped down format (It's a bit redundant, but that's great for checking):
<?xml version="1.0" encoding="UTF-8"?>
<importdata type="Sheet1">
<record position="1">
<field col="1" name="ID">T1</field>
<field col="2" name="Name">Peter</field>
<field col="3" name="Color">red</field>
<field col="4" name="Location">London</field>
</record>
...
</importdata>
Download the stylesheet, have a look and let me know what you think!
Comments :
No documents found
Site purpose and disclaimer
You consider to extend or replace your Domino infrastructure. You found a lot of information about messaging migration. You didn't find much about the applications, other than tool vendors advertisements. You realized that Domino migration is an emotional mine field. Bookmark this site, we will provide information and discuss the move from Domino to J2EE and other environments (both retaining and replacing Domino). We focus on applications, not on messaging.
The articles on this site mention products and phrases, that might be subject to copyright or trademarks. So we acknowledge, that the copyrights belong to the owner of the respective copyright or trademark.The links on this page are provided for convenience and are constitute no endorsement of the content of the target site.
So once your ready to discuss if and/or how to move away from Domino contact us.
The articles on this site mention products and phrases, that might be subject to copyright or trademarks. So we acknowledge, that the copyrights belong to the owner of the respective copyright or trademark.The links on this page are provided for convenience and are constitute no endorsement of the content of the target site.
So once your ready to discuss if and/or how to move away from Domino contact us.
