Make Attractive Exports of Categorized Notes Views

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:

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

The resulting spreadsheet looks like
this:

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.

Comments

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...

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?

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?

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.