?
Solved

Export categorised view to Excel in Domino Web Application

Posted on 2011-05-12
4
Medium Priority
?
794 Views
Last Modified: 2013-12-18
Hi all,

Does anyone have a routine which exports a categorised view to excel in the following format:

ColumnA                Column B

Category A             test
                                test1
                                test2
                                test3
                                test4
Category B             test5
                                test6
                                test7
                                test8
                                test9
Category C             test10
                                tes12
                                test13

............................................................automatic page break occurs here.............................
Next page should also create an entry for Category C before it continues with category C documents so the user doesn't get confused.

ColumnA                Column B
Category C  
                                test14
                                test15
                                test15


This is a web application so it needs to be a web export agent.

Your help is much appreciated.


0
Comment
Question by:varvoura
  • 2
4 Comments
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 2000 total points
ID: 35746699
If it's a web export agent, have a look at this. No need to write Excel code!

http://www.botstation.com/code/view2excelweb.php

What might be a drawback: I think it only works for the Internet Exploder...
0
 

Author Comment

by:varvoura
ID: 35753576
Hi sjef,

Thanks for your help and prompt response.

I have already downloaded this agent last week and I also have few other web agents which I run on a regular basis.  I was looking for a solution which first, takes into account the categorised view and also transfers the category row to the next page if a category extends over a page.


0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 35753975
Notes knows nothing about the size of a page in Excel, so don't expect any miracles there. I think there's an option in Excel to repeat headers on every page. Maybe there's also an option to repeat a row 'header"? Better ask this in the Excel Zone, or have this question transferred there if you want to go that way.
0
 
LVL 2

Expert Comment

by:gingerdeb
ID: 35767033
See code below - it exports everything in the view.  Category values will be repeated in every line, but I'm sure you can amend the script to check for this value and not write it if the same.  There's also a call to another routine that formats the cells, which I've commented out.

The parameters are the view being exported and the report type, which is just used in the title.

Sub ExportToExcel (view As NotesView, reptype As String)
            
      REM Declare variables
      Dim s As New NotesSession
      Dim db As NotesDatabase
      Dim doc As NotesDocument
      Dim xl As Variant
      Dim xlsheet As Variant
      Dim col As Integer
      Dim row As Integer
      Dim eLog As New ErrorLog(False)
      Dim ctr As Long
      Dim title As String
      
      On Error Goto errorhandler
      
      REM Set/initialise variables
      Set db = s.CurrentDatabase
      If Not (view Is Nothing) Then
            If reptype = "ViewExport" Then
                  title = db.Title + " - " + view.Name + " view" + Chr$(13) + "as at &D"
            Else
                  title = db.Title + " - Ad Hoc Report" + Chr$(13) + "as at &D"
            End If
            Print "Creating Excel Document "
            REM Create the Excel worksheet
            Set xl = CreateObject("Excel.Application")
            xl.Workbooks.Add
            Set xlsheet = xl.Workbooks(1).Worksheets(1)
            REM Create the column headings
            col = 1
            Forall vcol In view.Columns
                  xlsheet.Cells(1, col) = vcol.Title
                  col = col + 1
            End Forall
            REM Copy all the data to Excel
            Set doc = view.GetFirstDocument
            row = 2
            While Not (doc Is Nothing)
                  col = 1
                  Forall cval In doc.ColumnValues
                        xlsheet.Cells(row, col) = cval
                        col = col + 1
                  End Forall
                  row = row + 1
                  Set doc = view.GetNextDocument(doc)
            Wend
            
            REM Format cells
      '      Call FormatCells(xl, xlsheet, title, "Rows")             - separate routine to format cells
            Print ""
            xl.Range("A1").Select
            xl.visible = True
      Else
            Messagebox "Error exporting view to Excel", 0+16, "Action Cancelled"
      End If
      
      Set xl = Nothing
      Exit Sub
      
errorhandler:
      
      Messagebox "Error exporting view to Excel", 0+16, "Action Cancelled"
      If Not (xl Is Nothing) Then
            xl.DisplayAlerts = False
            xl.Quit
      End If
      Call eLog.WriteError("Export to Excel")
      Exit Sub
      
      
End Sub
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

864 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