« My Domino DST Fix Utility for Custom Applications | Main| Two-Row View Action Bars in Lotus Notes? »

Make Attractive Exports of Categorized Notes Views

QuickImage Category   


While there are plenty of Excel export routines for Notes posted in places like the Notes.net Sandbox, OpenNTF, and several blogs, I wanted a routine that was relatively simple code-wise but had the ability to produce clean-looking exports even for views with multiple levels of categorization, hidden columns, and even total columns.  

As a starting point I used a great piece of code written by Ian Irving and posted on his False Positives blog back in December.  Ian actually posted code to export a view to either Excel or a comma-delimited (CSV) file, but I'm only focusing here on the Excel code.  What I've ended up with is a set of two entirely self-contained agents, one for exporting an entire view, and a second for just selected documents.  I say "self-contained" because there are no script library dependencies so these agents can be dropped into any database without concerns of forgetting anything. To ensure that category column titles appear in the final spreadsheet, there are some simple changes that need to made to the view, but more on that in a moment.

The resulting agents do the following:
  • Export either all documents in the view or selected documents
  • Ignore hidden columns such as those used to force a certain sorting arrangement in more complex views
  • Ignore total columns such as shown above
  • Flatten category columns and include a column title.  For the title to show in the spreadsheet, you must include a category column title in the view design, but you can hide it in Notes by including several leading spaces, as shown here:

    A picture named M2
  • Ignore columns for which the string "NoExport" is included somewhere in the column title, such as for this icon column:

    A picture named M3
  • The resulting spreadsheet looks like this:

    A picture named M4

This code is probably not perfect for all views, and will certainly not work for complex views with lots of quantitative information where those columns use totals.  One cool enhancement that I think would be feasible is to place something like "$Profile-profilename-profilefield" in a column title that would point the export script to a profile document where you store custom column titles.  To hide it in a wider Notes column (i.e. not a narrow categorized one), you could set the font color to match the view header color (typically gray).  This could be problematic should you want a usable column title to show in the Notes view, but narrow columns should work fine.

The code is available in Downloads section of this blog here.

Comments

1 - Kevin,

First, sorry about the delay. I've been working from home, our vpn connection was dead for 3 days, and I'm just now back at work catching up.

Second, the pasted html/rich text(?) stuff looks just like text in Notes and, when passed to Excel, still looks like text.

To try and make the export force text to be text, I include the following bit of code. Even this sometimes fails and it always fails on those fields where email 'text' has been pasted into a Notes field.

Oh BTW, the email client is Outlook... Emoticon

I have not tried to do anything like write the 'bad' text out to a text file and the debugger is not the easiest thing to use when looking at looooonng strings.

Any thoughts are appreciated.

Doug

' Determine if the Notes data is text and force Excel to make the data text
' This is required to keep Excel from making text 0100 into number 100 or text 10e10 into exponent 10 e to the 10th
' Columns that are formulas are not processed by this module
' If there is no formula, then the field name is used to derive the type then process the data
If c.Formula = "" Then
Set item = CurrDoc.GetFirstItem(FldNm$ )
itemType = item.Type

If itemType = 1280 Then '1280 = Text. For all columns containing text data, force Excel to text format and force value into @Text
Forall f In FldVal
xlsheet.Cells(Row, Col).NumberFormat = "@" 'force cells to text format if item type = String. Being used to force SN to load into Excel properly.
TextFldVal$ = {@Text(} + FldNm$ + {)} ' Field name is the field used in the column.
TFldVal = Evaluate(TextFldVal$ , CurrDoc)
xlsheet.Cells(Row,Col).Value = TFldVal(0)
' just stuff value into cell
End Forall
Else
'just stuff value into cell
Forall f In FldVal
xlsheet.Cells(Row, Col).Value = Trim(xlsheet.Cells(Row, Col).Value & " " & f)
End Forall
End If
Else
' If there is a forumula, just process the evaluated value(s) into the Excel sheet.
Forall f In FldVal
xlsheet.Cells(Row, Col).Value = Trim(xlsheet.Cells(Row, Col).Value & " " & f)
End Forall
End If

2 - Hi Doug, thanks for the comment. I haven't tested this particular routine to see how many characters it can export for a particular field, but I seem to recall running into limits of around 1000 in the past. I don't think the html in your example should have any bearing on the limit, but it does add to the character count so that could be your issue.

Are you having a problem with the html itself somehow trying to render in Excel instead of just looking like text?

3 - Emoticon . This was very help to me. Thank you very much. It save me hours of time and is code I will use in the future.

4 - Neat tip - guess it's time to update the old faq of faqs and get some of these new export to excel routines listed (like anybody actually uses the faqs...)

Periodically, I run into cases where these generic exports just fail or acts badly. Sometimes I understand the issue (numbers entered as text somehow and needed as numbers or vice versa) and I handle that by setting the column format.
A more complex case involves users pasting html based text (ie an Outlook html email message) into a text field. During the export, no matter what I try, the export truncates the field value.
Have you seen this and found a way around the problem?

Thanks for any tips or ideas.

Doug

Your Host

KevinPettitt.jpg
Kevin Pettitt View Kevin Pettitt's profile on LinkedIn

Tools I Use

Idea Jam

Subscribe to This Blog

 Full Posts  Comments

MyYahoo
netvibes Add to Netvibes

Contact

Hosted by

OpenNTF

Disclaimer

This site is in no way affiliated, endorsed, sanctioned, supported, nor blessed by Lotus Software nor IBM Corporation, nor any of my past or future clients (although they are welcome to do so). The opinions, theories, facts, etc. presented here are my own and in no way represent any official pronouncement by me on behalf of any other entity.

© 2005-2017 Kevin Pettitt - all rights reserved as listed below.

Creative Commons License
Unless otherwise labeled by its originating author, the content found on this site is made available under the terms of an Attribution / NonCommercial / ShareAlike