I was reading the excellent developerworks article on
and it got me to thinking about some work I did recently which used LotusScript to create an MS Excel document using xml. I find this approach better than using the com objects for a few reasons:
- It's much, much faster
- It doesn't have the problems you run into when your com object doesn't create, or fails for whatever reason
- It is platform independent (mostly)
The basic steps are
Create a file
Figure out how many rows and columns you have
Output the xml heading
Create some styles (much like you would with CSS)
Write out the column headings
Write out the data
Close the file
Open the file in Excel
It sounds easy when you say it like that but really, it's not that hard. Here's some example code for you
Create a file
Open "c:\out.xml" For Output As filenum
Write out the XML heading
Print #filenum, {
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<ss:Workbook
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
}note there the magic " line. That's the one that tells it that it's Excel. Doesn't matter what the extension is (eg, you can call your file .xls or .txt or whatever), when windows sees that line it's like magic. If you are debugging your xml, you need to take that out otherwise it keeps opening in Excel which will drive you bananas.
Write out the Excel Properties
Print #filenum, { <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>} & strUserName & {</Author>
<Company>Department of Treasury and Finance</Company>
</DocumentProperties>
}
Write out the styles
Print #filenum, { <ss:Styles>
<ss:Style ss:ID="Default" ss:Name="Normal">
<ss:Alignment ss:Vertical="Top"/>
<ss:Borders/>
<ss:Font/>
<ss:Interior/>
<ss:NumberFormat/>
<ss:Protection/>
</ss:Style>
<ss:Style ss:ID="Heading">
<Font ss:Bold="1" />
<Interior ss:Color="#D8D8D8" ss:Pattern="Solid"/>
</ss:Style>
<ss:Style ss:ID="Rating">
<ss:NumberFormat ss:Format="0.0" />
</ss:Style>
<ss:Style ss:ID="Dollars">
<ss:NumberFormat ss:Format="Currency" />
</ss:Style>
<ss:Style ss:ID="ShortDate">
<ss:NumberFormat ss:Format="[ENG][$-409]d\-mmm\-yyyy;@" >
</ss:Style>
<ss:Style ss:ID="YesNo">
<ss:NumberFormat ss:Format="Yes/No" />
</ss:Style>
</ss:Styles>
}
Write out the sheet name
Sub writeXMLWorksheet
Print #filenum, {
<ss:Worksheet ss:Name="Referrals">
}
End Sub
Figure out how many rows and columns you have
Set view = db.GetView("ByReferralNumber")
strHeadings=("Referral Number,Dept Referral Number,Referral Type,Dept File,Department, Date Received")
varHeadings = Split(strHeadings,",")
intNumColumns = Ubound(varHeadings) + 1
lngNumRows = 1 + view.TopLevelEntryCount
Create the columns
Sub writeXMLCreateColumns(intNumColumns As Integer, lngNumRows As Long, vHeadings As Variant)
Dim i As Integer
Print #filenum,{
<ss:Table
ss:ExpandedColumnCount="} & intNumColumns & {"
ss:ExpandedRowCount="} & lngNumRows & {"
x:FullColumns="1"
x:FullRows="1">
}
For i = 1 To intNumColumns
Print #filenum, {<ss:Column ss:Index="} & i & {" ss:Width="} & 8 * Len(vHeadings(i-1)) & {" />}
Next
End Sub
Create the Header Row
Sub writeXMLHeaderRow(HeadingValues As Variant)
Print #filenum, " <ss:Row>"
Forall a In HeadingValues
Print #filenum, {<ss:Cell ss:StyleID="Heading">}
Print #filenum, |<ss:Data ss:Type="String">| & a & |</ss:Data>|
Print #filenum, "</ss:Cell>"
End Forall
Print #filenum, {
</ss:Row>
}
End Sub
Loop through and write the data
Sub writeXMLData(noteCurrent As NotesDocument, fnum As Integer)
Dim item As NotesItem
Print #fnum, {<ss:Row>}
Print #fnum, {<ss:Cell>}
Print #fnum, { <Data ss:Type="String"><![CDATA[} & noteCurrent.ReferralNumber(0) & {]]></Data>}
Print #fnum, {</ss:Cell>}
Print #fnum, {<ss:Cell>} Print #fnum, {<Data ss:Type="String"><![CDATA[} & noteCurrent.DeptRefNumber(0) & {]]></Data>}
Print #fnum, {</ss:Cell>}
Print #fnum, {<ss:Cell>} Print #fnum, {<Data ss:Type="String"><![CDATA[} & noteCurrent.ReferralType(0) & {]]></Data>}
Print #fnum, {</ss:Cell>}
Print #fnum, {<ss:Cell>}
Print #fnum, {<Data ss:Type="String"><![CDATA[} & noteCurrent.DeptFileNumber(0) & {]]></Data>}
Print #fnum, {</ss:Cell>}
Print #fnum, {<ss:Cell>}
Print #fnum, {<Data ss:Type="String"><![CDATA[} & noteCurrent.DepartmentName(0) & {]]></Data>}
Print #fnum, {</ss:Cell>}
Set item = noteCurrent.GetFirstItem("DateReceived")
Call writeXMLPrintDate(item, fnum)
End Sub
(for interest, here is the writeXMLPrintDate function)
Sub writeXMLPrintDate(item As NotesItem, fnum As Integer)
Dim ndt As New NotesDateTime("")
If Not (item Is Nothing) Then
Set ndt = item.DateTimeValue
Print #fnum, {<ss:Cell ss:StyleID="ShortDate">}
If Not (ndt Is Nothing) Then
Print #fnum, {<Data ss:Type="DateTime"><![CDATA[} & Format(ndt.LSLocalTime, "yyyy-mm-dd") & {]]></Data>}
Else
Print #fnum, {<Data ss:Type="String"> </Data>}
End If
Print #fnum, {</ss:Cell>}
Else
Print #fnum, {<ss:Cell>}
Print #fnum, {<Data ss:Type="String"><![CDATA[ ]]></Data>}
Print #fnum, {</ss:Cell>}
End If
End Sub
Then close your file off and open it up in excel. A cheats way to do this, as long as you know it works in your environment is to just do a urlopen on it.
ws.URLOpen("file://c:\out.xml")
And there you go. Simple and really flexible (you can write out formulas and all sorts of things). It seems to work in Office 2003 and 2007. I haven't really tried other versions. I've done the same thing with Word, but I imagine it would work with all of the products that read XML.
The other trick is, if you can't figure out what the XML is for something, just do it in Excel and then save the file as XML and reverse engineer it.
Last but not least, a handy reference is the Microsoft Office XML Spreadsheet Reference - which is great if you get lost.
Anyway, it's worth a try the next time you need to output something to Excel (or word etc). Leave me a comment and let me know how you go...
