Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Exporting web search results to Excel

Posted on 2003-11-26
12
Medium Priority
?
1,009 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
12 Comments
 
LVL 31

Expert Comment

by:qwaletee
ID: 9824642
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
ID: 9824676
(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
ID: 9824783
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:s3cmp5
ID: 9824906
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
ID: 9825041
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
 
LVL 31

Expert Comment

by:qwaletee
ID: 9825104
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
ID: 9825227
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
ID: 9825393
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
ID: 9825400
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 1200 total points
ID: 9825531
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

688 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