You could use POI to write an Excel file, but writing to a file with APIs is usually really slow.
MYYYYYYYYYYYYYYYYYY approach is blazingly faaaaaaaaaaaaaaaaaaaaast……..
In our company we are in the lucky situation that we have single-sign-on on the web towards our Domino servers. This is important for the following solution to export documents to work properly.
Steps:
1. Create some kind of “profile” document which defines the filters for the data the user wants to export
2. Create an agent which processes those filters from the profile document and outputs XML for all documents
Example XML:
<?xml version=”1.0″ encoding=”UTF-8″?>
<data xmlns:xs=”http://www.w3.org/2001/XMLSchema-instance”>
<document>
<processeddate xs:nil=”true”></processeddate>
<wfcurrentstate><![CDATA[70]]></wfcurrentstate>
<wfcurrentstatename><![CDATA[Returned for modification]]></wfcurrentstatename>
<timecreated>2013-10-08</timecreated>
<regionname><![CDATA[SOUTH AMERICA]]></regionname>
</document>
</data>
Important here is only the “xs:nil” attribute for “empty” fields, dates/numbers you can submit as is, text fields I advise to put into the <![CDATA[textvalue]]> format.
3. Import the XML via the URL of the web agent (eg. http://yourserver/yourpath/youragent?openagent) file into Excel via Data Import from other sources from XML data import.
Now the first time you do this you are have to go through some wizard actions done by Excel.
Once the XML map has been created you can use that file over and over again (say as template). You simply need to refresh the data via the data tab in Excel.
My development workflow (to get this working start slow)
first only export some text fields
then try also empty fields (with the xs:nil) attribute
then continue with numbers and dates to see if it works
call the agent directly in the browser to check if the XML is properly formatted (Google Chrome will tell you so, maybe other browers as well)
then import that XML into Excel, ie. use the refresh button
I use to “log” the filters from the profile when the agent runs. My agent uses mostly FT Search to find the documents, only if more than 5000 are found it reverts to (if possible) view categories.
The “constructed” query is also logged. That way I can run the same query directly in the LN client to check for FT Query mistakes. As example I figured I need to put texts into double quotation marks to make queries, with filters consisting of more than one word, properly working.
The result is phantastic! I can export 5000 documents via VPN connection from my home office in about less than 30sec!
Questions? please don’t hesitate to write comments
otherwise enjoy!!!
