Solved

Exporting web search results to Excel

Posted on 2003-11-26
12
995 Views
Last Modified: 2013-12-18
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





0
Comment
Question by:s3cmp5
  • 7
  • 2
12 Comments
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
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.
0
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
(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.)
0
 
LVL 13

Expert Comment

by:CRAK
Comment Utility
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.
0
 

Author Comment

by:s3cmp5
Comment Utility
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


0
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
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
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
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
0
 

Author Comment

by:s3cmp5
Comment Utility
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.







0
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
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"
0
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
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.
0
 
LVL 31

Accepted Solution

by:
qwaletee earned 300 total points
Comment Utility
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now