Spreadbuilder Question

Posted by: itiasreo on 4 August 2017, 2:55 pm EST

  • Posted 4 August 2017, 2:55 pm EST

    Hi,


    I have a decent amount of reports that run different query strings. I have added an Export to Excel button onto the viewer toolbar. When I export to excel the entire report is exported; header, footer, etc. I am currently using the XLSExport feature. Is there a way to export just the SQL select field names and the data associated with the fields? If possible could you please provide code.


    I understand how to use the spreadbuilder and statically add the field names and data, but I would like to make this dynamic so that when a different report loads different information is populated into excel according to the SQL query strings.


    Thank You,


    John

  • Replied 4 August 2017, 2:55 pm EST

    Hello ,


    You can create the excel file by spread builder in the report's code.


    Here is a sample of code:


    'Code of main program


    Dim ar As New MyReport
    ar.SpreadBuilderEnabled = True
    ar.Run



    'Report's code


    Public SpreadBuilderEnabled As Boolean
    Dim SB As ActiveReportsExcelExport.SpreadBuilder
    Dim j


    Private Sub ActiveReport_DataInitialize()
        j = 2
    End Sub


    Private Sub ActiveReport_FetchData(EOF As Boolean)
    If SpreadBuilderEnabled Then
    On Error Resume Next
        If EOF = False Then
            For i = 0 To Fields.Count - 1
                SB.Sheets("Sheet1").Cell(j, i).Value = CStr(Fields(i).Value)
                If Err.Number = 5017 Then
                    SB.Sheets("Sheet1").Cell(j, i).Type = SBBlank
                    SB.Sheets("Sheet1").Cell(j, i).Value = ""
                End If
            Next
            j = j + 1
        Else
            SB.Save ("c:\temp.xls")
            Set SB = Nothing
            MsgBox "c:\temp.xls"
        End If
    End If
    End Sub


    Private Sub ActiveReport_ReportStart()
    If SpreadBuilderEnabled Then
        Set SB = New SpreadBuilder
        SB.Clear
        SB.Sheets.Add ("Sheet1")
        For i = 0 To Fields.Count - 1
            SB.Sheets("Sheet1").Cell(0, i).Value = Fields(i).Name
            SB.Sheets("Sheet1").Cell(0, i).BorderBottomStyle = 1
        Next i
    End If
    End Sub


    Sergey Romanov.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels