Can we Export data from view to Excel thru web?

HCL Domino Domino Forum
1

Hi, I am able to export data from view to Excel through agent (Lotus Script). Am I able to perform the same task on the web? I have tried to run it on the web, but it shows “Operation is disallowed in this session”. Can anyone please give some advice?

Below is the script in the agent.



Dim x As Integer

Dim y As Integer

Dim strLen As Integer

Dim ws As NotesUIWorkspace

Dim vw As NotesUIView

Dim coldocs As NotesDocumentCollection

Dim doc As NotesDocument

Dim it As Variant

Dim strDesc As String

Dim strTrack As String



Set ws = New NotesUIWorkspace

Set vw = ws.CurrentView

Set coldocs = vw.Documents

Set doc = coldocs.getfirstdocument

Print "1"

’ Set up the Excel Application

Dim xlApp As Variant

Dim xlSheet As Variant

Print "2"

Set xlApp = CreateObject("Excel.Application")

Print "3"

orgchart$ = "C:\Staff Details.xls"

Print "4"

Set xlSheet = xlApp.Workbooks.Open(orgchart$)

Print "5"

xlSheet.ActiveSheet.Cells(1, 1) = "No."

xlSheet.ActiveSheet.Cells(1, 2) = "Emp's Name"

’ Format columns

’ This sets the col width

xlSheet.ActiveSheet.Columns(1).ColumnWidth = 4

xlSheet.ActiveSheet.Columns(2).ColumnWidth =33

    For i = 1 To 2

   xlSheet.ActiveSheet.Columns(i).VerticalAlignment = 1

Next

’ Set Page Orientation

xlSheet.ActiveSheet.PageSetup.Orientation = 2

’ Underline the headings

For i = 1 To 2

     xlSheet.ActiveSheet.Cells(1, i).Font.Underline = 2

Next

’ Set the 3rd row to be the title row

xlSheet.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"

x=3

Do While Not doc Is Nothing

    xlSheet.ActiveSheet.Cells(x, 2).value = doc.Emp_Name(0)

    x = x + 1

    Set doc=coldocs.GetNextDocument(doc)

Loop

xlApp.Visible = True
2

Subject: View2Excel thru web - practical solution

Hey man,

there are good articles describing nice and clean solutionm like this one

http://www.breakingpar.com/bkp/home.nsf/0/87256B280015193F87256E32007999E6

tony

3

Subject: Can we Export data from view to Excel thru web?

Hi,

UI class not working on web :-((

…

Dim ws As NotesUIWorkspace

Dim vw As NotesUIView

…

Change your code, something like this (will work with both : Notes and Web) :

Dim session As New NotesSession

Dim db As NotesDatabase

Dim vw as NoteSView

…

Set db = session.CurrentDatabase

Set vw = db.getview(“viewname”)

…

HTH

Thierry

4

Subject: RE: Can we Export data from view to Excel thru web?

That will only work if you want the Excel file to be created on the Server when the user opens it on the browser. LS does not work on a browser, it can only be triggered to run in the back end.However, forget about LS, make sure the View is not set to display using Java, Open the view in a browser, and get the URL. Then open XL, And do “Data”–“Get External Data” --“New Web Query” and paste in the URL.

Leave the option “Only the Tables” selected, as Notes displays Views as tables.

Click “OK”, and then the “Properties” button.

There are many options about formatting etc. that you can choose to suit your situation. I use this very successfully for doing lots of on the fly reports from many databases, without ever having to touch Lotus Script.

You can also set it up to refresh every time someone opens the Xl file, although if its a secure db, the user will have to supply their internet password to be allowed to refresh.

Users love it, cos you can set up graphs and stuff to work on it so its the closest thing yuo’ll get to doing graphs using Notes without loads of Coding.

As an aside, you could also create a form which has a table on it, and generate the contents of the form using formula or LS, then open the URL of that from XL. Works just as well.

Note: Make sure that all the data is displaed as text, as XL sometimes translates dates into its own format which is a real pain.

5

Subject: RE: Can we Export data from view to Excel thru web?

Evelyn,

Cheng uses an agent and LS can work in this case.

Thank for your precision about creation of file on server, I had forgotten to mention that.

Indeed, there is the solution which you speak. In my case users don’t like it : it’s too difficult for them, they want just to click a button…

No luck for me : -(

Thierry

6

Subject: RE: Can we Export data from view to Excel thru web?

Thierry, Although Cheng will be using an agent, the Lotus Script still does not run on the browser, it runs on the server (in the agent, yes), and is triggered from the browser as I mentioned.

From the code posted, Cheng seems to want to use Lotus Script to create AND LAUNCH an Excel file for the Web user, so LS is not suitable to do this directly. A very indirect method is required.

Cheng, I hope I do not offend, but your code needs many changes to generate a file that can display an Excel file to the user on the web,

If you want your code to run over any view you open you will probably need to use LS.

The method I showed previously, is good only for displaying a single pre-specified view in Excel, but is not flexible about the URL it loads.

The basics to do what yuo need in LS are as follows

–Ensure Excel is installed on server.

–use session.documentcontext (to get a pointer to the document currently opening on the browser)

–Get the name of the view to export from the document - different ways to do this depending on how you want it to work, pop-up maybe, or use the viewname field in a $$ViewTemplateDefault form

–re-write the agent in back end code( ie make sure none of the objects contain “UI” ) as per Thierry’s instructions

–save the Excel file to the hard drive( do not launch it, this will not work on Server!! )

–Attach the file to that document you got the pointer to

–Save the agent as Run when “Agent List Selection” and Trigger = “None”

–Call the agent in the WebQueryOpen of a document ( or using @command([ToolsRunMacro]), but then you have to figure out how to get back to the saved document )

–This would display the document with the attachment to the user.

–Then the user would still have to launch the file themselves.

Have you considered creating the file in LS and just emailing it to the user when they press a button, that’s a bit easier than trying to redisplay it to the user from the same agent that created it?

The solution I provided in my previous post about the Excel File and the “Get External Data” is for the Developer to set up, not for the user to do.

The developer would set it up and format it nicely and attach/link it to a Web page ( or form, or whatever ).

I wouldn’t ask users to do this kind of functionality themselves, as Thierry mentioed, it’s too difficult for users.

The user would then simply have to click on the attached Excel file. When the user launches the Excel file it will run the functionality I described previously. Provided they have a reasonable version of Excel, of course.

7

Subject: Evelyn, thank you for these précisisons and …

I hope that my remaques did not offend you, it was not my intention.If that were the case, my apologies.

Thierry

8

Subject: RE: Evelyn, thank you for these précisisons and …

It’s OK, we’re all just here to help, I think Stan’s and Antons solutions were better than ours anyway:)

I just got a bit carried away with getting the explanation exactly correct once I started.

Apologies back to you.

I just hope in all of this somewhere is a solution that Ching can use. :slight_smile:

Ev

9

Subject: RE: Can we Export data from view to Excel thru web?

There is also the simple option of creating the view AS an Excel spreadsheet. Since Domino 6 and higher allows us to “Treat as other”, all you need is a blank $$ViewTemplate set to “Treat as other: application/vnd.excel”. Your view, then, creates a simple HTML-style table in this sort of format:

data here more data evn more data

Make sure that there’s a return character between lines, and that each is on its own line. The view will need to be set to “Treat as HTML”, of course, and the embedded view should be set to “Display using view’s display settings” without headers and with as many lines to display as you think you could possibly ever need.