Export to xls problem

Posted by: dillen-dd on 4 August 2017, 2:47 pm EST

  • Posted 4 August 2017, 2:47 pm EST


    when exporting a report to xls we encounter the problem that tiny lines are created.

    All the fields are positioned by a pgm and have all the same .top value

    3 issues

    1) The tiny extra rows for all ARCHES records

    2) Why only ARCHES, not others

    3) Why is line 20 in xls created like this? Row height far too big (it't coming from group footer).

    Export settings

        Set myExcelExport = New ActiveReportsExcelExport.ARExportExcel

        myExcelExport.Version = 8

        myExcelExport.FileName = fSaveAsDlg.saveAsDlg.FileName

        myExcelExport.AutoRowHeight = False 'True

        'myExcelExport.BorderSpace = 0

        myExcelExport.TrimEmptySpace = False   ' important! '

        myExcelExport.MultiSheet = True

        myExcelExport.ShowMarginSpace = True

        myExcelExport.DoubleBoundaries = True

        myExcelExport.MinColumnWidth = 500   ' do not make it larger (colour problems otherwise) '

        myExcelExport.MinRowHeight = 50 '25

    The attached file includes screen shots and .rpx

    Any ideas to overcome these problems?


  • Replied 4 August 2017, 2:47 pm EST


    could you pls provide a good sample using the spreadbuilder class for xls export

    Ideally it should cover the handling of report header/footer, page header/footer, group header/footer and detail sections.

    This would help a lot for a quick start


  • Replied 4 August 2017, 2:47 pm EST

    Here is a sample we have for spreadbuilder:


  • Replied 4 August 2017, 2:47 pm EST

    ... and here the zip file.

  • Replied 4 August 2017, 2:47 pm EST

    I spoke with Dillen on the phone regarding this issue, but I am replying to this post for future reference to other users.

    The Excel Export is not WYSIWYG. The line control, shapes, overlapping controls, and control borders are not supported. The export also works best when everything on the report is clearly defined in vertical columns (you should be able to envision where you would insert a vertical line to define a column. This line should not intersect any controls).

    A good way to test how the export looks with various property settings, is to save the report out to the RDF format using the .Pages.Save method. You can then open the RDF file using the RDF viewer sample and export it to excel with various property settings that are accessible for the excel export. This way you can tweek how the output looks.

    If you need more control over the excel document then the export can provide. You should take a look at using the spreadbuilder class. This class gives you a lot more control over the generated spreadsheet.

  • Replied 4 August 2017, 2:47 pm EST


    thanks for the example, but this is quite basic for just putting a table content to xls.

    Do I have to manage page & group header/footer etc. all by myself using Spreadbuilder?

    Any more advanced sample would help me a lot.

    Do you have any customers using this? So I could contact them, if allowed.



  • Replied 4 August 2017, 2:47 pm EST

    Do you have any additional questions on the usage of
    spreadbuilder?  You can either place your page header at the top
    of each sheet, or place it right within your spreadsheet as it would
    appear in your report output by including the spreadbuilder code in
    your beforeprint events of your sections.

    Several customers do use spreadbuilder.  We do not keep a list,
    but if you search the AR2 forums for "spreadbuilder", you may be able
    to contact some users that have implemented a spreadbuilder solution.

  • Replied 4 August 2017, 2:47 pm EST

    <font face="Arial">I did some futher investigation on our excel export problem.

    The extra mini-rows created are forced by a bottom vertical alignement.

    After changing the vertical alignment to TOP, no extra rows are genereated.

    Could you pls check in your export code, if this really has an influence?

    We have now the following remaining problem:

    if fields are aligned as follows

    </font><font face="Arial">+-----------------+</font>

    <font face="Arial">|         xxxxx      |


    |     yy  |  zzz     |</font><font face="Arial">


    then 2 columns are generated, but the xxxx text is assigned to either column 1 or 2.

    In the HTML export this is not a problem, merged cells are detected.

    Could you pls check if this could be resolved.

    I have the latest build installed.



Need extra support?

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

Learn More

Forum Channels