Exporting web search results to Excel

Hi all

I recently created a search tool for one of our customer's web-based CRM system which allows you to enter one or more criteria (eg Company Name = "IBM" and Contact Name = "Smith") using a combination of dialog lists and editable fields and return the results in a view-sorted order.

I used the usual method ie in the $$Return field:
@If(ToggleType = "Contact";
"[[/"+DBName+"/VVCNT00SURNAMESSD/?SearchView&Query="+QueryContact+"&SearchOrder="+@Text(Sort)+"&SearchMax="+@Text(5000)+"&SearchWV="+@If(ExactMatch="";"TRUE";"FALSE")+"&SearchThesaurus="+@If(UseThesaurus="";"FALSE";"TRUE")+"&tt=Contact]]" etc in combination with a $$SearchTemplateDefault form.

This was all working fine until..... the customer requested an "Export to Excel" button which would effectively dump all these results into Excel "as is" and IN THE SAME SORT ORDER as the results view.  The first problem with this was getting a handle on all the returned documents which is tricky as they all just appear within a load of <TD> tags in the Source.  OK you think, so just create a lotusscript agent which does the same search as the above formula language and loop through the results, dumping each one into Excel.  Next problem - if you do a view.FTSearch in script, the collection returned does NOT respect the sort order of the view, but instead returns everything in Relevance order.

The only way I could come up with getting around this was, in the onLoad event, to loop through document.links using javascript and write out all the noteids to a hidden text field (all delimited by "~").  In my agent, instead of having to repeat the search, I did an @Explode(fieldname; "~" )etc using Evaluate, then looped through the resulting array, located each document using a hidden view and GetDocumentByKey, and wrote each doc to Excel.  This was working fine until someone did a search which returned 4,000 docs - basically the field being used to receive all the note ids in onLoad was getting blown out by the usual field limits (this is all R5 by the way).

My solution:  modify the javascript to split out all the results in more manageable chunks and write them to separate fields (to be reunited later in the agent).  My javascript (pretty shoddy probably!):

alert('Your search returned ' + document.links.length + ' results');

for(i=0; i<document.links.length; i++) {
var lnk = document.links[i];
var strlnk = String(lnk);
var pos = strlnk.indexOf(".nsf");
var newlnk =strlnk.substr(pos+38);
lnk = newlnk.substr(0, 32);
var testi = Math.round(i/100);

var sval = document.all['ltext_' + testi].value;

if(sval == "") {
document.all['ltext_' + testi].value = lnk} else {
document.all['ltext_' + testi].value = document.all['ltext_' + testi].value + "~" + lnk }
}


In other words, loop through each noteid, divide the "loop count" by 100 and round it to the nearest integer, then write it to the relevant field (eg "ltext_10") if on, say, document 1000.

What happens now is that there seems to be some sort of mad "browser crash" for several minutes after clicking OK to the above js alert.  If I put a "return" above the alert, the search results all appear after a short delay (well there are 4,000 after all which is understandable), and the browser behaves normally.  If the "return" isn't there, after clicking OK to the alert, the browser says "Not Responding" for about 10 minutes, before returning to normal.  The export still works OK afterwards though.

I suppose my question is:
 - Can anyone see a reason for the crashing, and/or have I totally missed something i.e am I massively overcomplicating how to get these documents into Excel in the right order and is there a better way of doing it anyway, given that I have to do the original search in formula language (there are a lot of complicated formula search strings)?

Thanks in advance





s3cmp5Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

qwaleteeCommented:
Don't bother!!!!!

Excel can read HTML, and will put HTML tables cells into spreadsheet cells. The only quuestion is one of formatting, and of document type.  We can't fix document type, but we can create a batch file that contains a command to launch Excel with the URL as a parameter.  And we can fix formatting... put an extra parameter, something like &ReportEssentials.  Do hide whens to skip any text that you don't want Excel to have based on the extra URL query string item.
qwaleteeCommented:
(Just ruminating here... if you work hard enough at it, you can probably figure out how to use the OBJECT tag to load Excel as an ActiveX control on a web page, and pass it a parameter telling it where to get the search results page.)
CRAKCommented:
The search you do in your browser, can also be carried out in back-end lotusscript, like an agent:

Set notesDocumentCollection = notesDatabase.FTSearch( query$, maxDocs% [,sortoptions [, otheroptions]] )

(Ref. designer help.)
I have NEVER tried it, and can't spare the time right now - sorry about that -, but using the sortoptions, you should get the same resultlist twice. What else would its use be? from there you could browse the documentcollection and construct an export list.
You can execute the lotusscript as agent from within the browser. I bet I don't have to tell you....

I can't explain the crash.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

s3cmp5Author Commented:
Thanks CRAK, but the problem doing the original search as an agent is that I need to display the results in a specific Notes view-dependent order BEFORE hitting the Export to Excel button (ie the customer's original requirement).  Each search type requires a different view, eg the "SearchResultsCompany" view for doing searches on company documents.  I obviously looked into doing the FTSearch and then sorting the document collection - this would have been OK if the search results views only had one sorted column - easy to resort a document collection based on one criteria - but multiple sorted columns.....????!! Headache.....

qwaletee, your first response intrigues me!  I knew Excel could understand html, in fact that is exactly what my agent does, looping throught the documents and using Print statements ie:
'Sets the download to use Excel
      Print |Content-Type:application/vnd.ms-excel|
'Triggers the save/open prompt instead of embedding the spreadsheet in the browser
      Print |Content-Disposition:Attachment; filename="Report.xls"|

Then.....(just a snippet so you get the idea):
            For x = 0 To Ubound(ev)
                  Set ve = v.GetEntryByKey(Ucase(ev(x)), True)
                  If ve Is Nothing Then
                        Print ev(x)
                        End
                  End If
                  col=1
                  Forall cValue In ve.ColumnValues
                        If Isarray(cValue) Then
                        'Added following if statement to handle multivalue entries in column
                              Dim newcVal As String
                              Forall ar In cValue
                                    If newcVal = "" Then
                                          newcVal = ar
                                    Else
                                          newcVal = newcVal & ", " & ar
                                    End If
                              End Forall
                              cValue = newcVal
                        End If
                        
                        If col=1 Then
                              lineitem=|<tr>|
                        End If
                        
                        If col <> 1 Then
                              
                              If cValue="" Then 'blank value still formats the cell
                                    lineitem=lineitem+|<td>&nbsp;</td>|
                              Elseif Isdate(cValue) Then 'date format
                                    lineitem=lineitem+|<TD ALIGN="right" STYLE="vnd.ms-excel.numberformat:dd-mmm-yyyy">|+cValue+|</td>|
                              Elseif Isnumeric(cValue) Then
                                    If (v.columns(col-1).numberformat=3) Then 'currency format
                                          lineitem=lineitem+|<td ALIGN="right" STYLE="vnd.ms-excel.numberformat:$#,##0.00">|+cValue+|</td>|
                                    Else 'other number format
                                          lineitem=lineitem+|<td ALIGN="right">|+cValue+|</td>|
                                    End If
                              Else 'Plain text format
                                    lineitem=lineitem+|<td>|+cValue+|</td>|
                              End If
                        End If
                        
                        col=col+1
                  End Forall
                  Print lineitem+|</tr>|
            'Set ve = vec.GetNextEntry(ve)
            Next x
      Next
      Print |</table>|

This works a treat.  So how are you suggesting I grab all the HTML generated in the search results view and "dump" it into Excel?

Thanks


qwaleteeCommented:
s3cmp5,
> This works a treat.  So how are you suggesting I grab all the HTML generated
> in the search results view and "dump" it into Excel?
I'm proposing that you don't!

Let Excel open THE CURRENT HTML PAGE directly.  That is, instead of using a browser to display the  current result page, use Excel to dispay the page.

The possible ideas I have for this are:

1) Figure out how to embed the Excel ActiveX object in the a web page, and pass it the search results URL to load within the object

2) If using R6, set the form's MIME type to application/x-msexcel (I **think** that's the MIME type, wll have to check
qwaleteeCommented:
Oops, clicked SUBMIT my accident...


2) If using R6, set the form's MIME type to application/vnd.ms-excel which will cause IE to automatically load it into Excel

3) Use a servlet to do an internal retrieve of teh page, then turn it back around to the browser with the Excel MIME type

4) If you know the standard location for Excel's instal directory at your site, send a batch file containing something like "c:\program files\microsoft office\office\excel.exe" "http://yoruserver/yourdb.nsf/VVCNT00SURNAMESSD/?SearchView&Query=xyzyz&....

5) Use a temporary folder in your processing.  Folder should have the same design as te view.  You just clear the folder, FTSearch, dump collection to flder, then just pick off the column values and print them
s3cmp5Author Commented:
qwaletee

Thanks, I see what you're saying, but WHAT URL do I use to feed into Excel??  If I look at the properties of the search results window, the URL is:

http://memlight1/memlightsw.nsf/$$Search?OpenForm&Seq=1&BaseTarget=MainFrame&AutoFramed

The actual URL is generated in $$Return field ie:
"[[/"+DBName+"/VVCNT00SURNAMESSD/?SearchView&Query="+QueryContact+"&SearchOrder="+@Text(Sort)+"&SearchMax="+@Text(5000)+"&SearchWV="+@If(ExactMatch="";"TRUE";"FALSE")+"&SearchThesaurus="+@If(UseThesaurus="";"FALSE";"TRUE")+"&tt=Contact]]"

OK actually I think I see what you mean now - use a computed field to evaluate the above to a valid URL, then pass it in?

OK, but to be honest, I've never done anything with ActiveX.  Have I not already done the equivalent with my:
Print |Content-Type:application/vnd.ms-excel|

I'm stuck.

It's R5 by the way.







qwaleteeCommented:
Domino hides the URL if youuse the double bracket syntax, "[[/"+DBName+"/VVCNT00SURNAMESSD/?SearchView&Query="+QueryContact+"&SearchOrder="+@Text(Sort)+"&SearchMax="+@Text(5000)+"&SearchWV="+@If(ExactMatch="";"TRUE";"FALSE")+"&SearchThesaurus="+@If(UseThesaurus="";"FALSE";"TRUE")+"&tt=Contact]]"


When it sees TWO sets of enclosing brackets, it generates the HTML for that URL, but only displays the normal URL (as if you had no $$Return or WQS agent).

If you use single brackets...

"[/"+DBName+"/VVCNT00SURNAMESSD/?SearchView&Query="+QueryContact+"&SearchOrder="+@Text(Sort)+"&SearchMax="+@Text(5000)+"&SearchWV="+@If(ExactMatch="";"TRUE";"FALSE")+"&SearchThesaurus="+@If(UseThesaurus="";"FALSE";"TRUE")+"&tt=Contact]"

You will see that teh page is identical, but the URL looks like teh formula result.

So, the URL for Excel to load would be...

1) If loaded within the page (e.g., ActiveX control parameter):
"/"+DBName+"/VVCNT00SURNAMESSD/?SearchView&Query="+QueryContact+"&SearchOrder="+@Text(Sort)+"&SearchMax="+@Text(5000)+"&SearchWV="+@If(ExactMatch="";"TRUE";"FALSE")+"&SearchThesaurus="+@If(UseThesaurus="";"FALSE";"TRUE")+"&tt=Contact"


2) If the independent Excel applicatino is to load the page itself by URL:
"http://" + serverAddress + "/"+DBName+"/VVCNT00SURNAMESSD/?SearchView&Query="+QueryContact+"&SearchOrder="+@Text(Sort)+"&SearchMax="+@Text(5000)+"&SearchWV="+@If(ExactMatch="";"TRUE";"FALSE")+"&SearchThesaurus="+@If(UseThesaurus="";"FALSE";"TRUE")+"&tt=Contact"
qwaleteeCommented:
You could also conceivably use VBScript to start Excel and instruct it to load the correct URL.  If you don't have a signed VBScript, though, the browser will issue a security warning.
qwaleteeCommented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.