Export categorised view to Excel in Domino Web Application

Posted on 2011-05-12
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
Category B             test5
Category C             test10

............................................................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  

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

Your help is much appreciated.

Question by:varvoura
    LVL 46

    Accepted Solution

    If it's a web export agent, have a look at this. No need to write Excel code!

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

    Author Comment

    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.

    LVL 46

    Expert Comment

    by:Sjef Bosman
    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.
    LVL 2

    Expert Comment

    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"
                      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")
                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)
                REM Format cells
          '      Call FormatCells(xl, xlsheet, title, "Rows")             - separate routine to format cells
                Print ""
                xl.visible = True
                Messagebox "Error exporting view to Excel", 0+16, "Action Cancelled"
          End If
          Set xl = Nothing
          Exit Sub
          Messagebox "Error exporting view to Excel", 0+16, "Action Cancelled"
          If Not (xl Is Nothing) Then
                xl.DisplayAlerts = False
          End If
          Call eLog.WriteError("Export to Excel")
          Exit Sub
    End Sub

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
      In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 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

    20 Experts available now in Live!

    Get 1:1 Help Now