Outputting a Report to Excel - for dummies

Business Central/NAV Developers Forum
1

We have had several requests to output Nav reports to Excel. Is this hard to do??

Version 5.0 S.P. 1.

I know the Top_Customer List does this.

Thanks in advance,

Greg

2

Hi Greg,

Depending on the report and the complexity there are a number of options:

  1. No codeing bt messy output - In the preview window of the report select File → Save as HTML. Then open the HTML file in Excel

  2. The way most report are coded - Nav use a table 370 Excel Buffer for Excel input/output. In the C/AL of this table there is function to help you export to Excel. Look at an example report e.g. 120 Aged Accounts Receivable. The standard coding uses functions MakeExcelInfo, MakeExcelDataHeader, MakeExcelDataBody and CreateExcelbook. The main function call is AddColumn and NewRow.

  3. The most advanced is using Excel automation - this is quite advanced so I won’t go into detail here - an example is the Analysis by Dimension export to Excel codeunit 424 Export Analysis View

Hope this helps

3

Well another option is JetReports: http://www.jetreports.com/

4

Thanks Dave and Eric.

Greg

5

Okay I added the Excel Buffer table 370 to the Cal/Globals but when I go to Cal/Code on the main table how do you add MakeExcelInfo, MakeExcelDataHeader, MakeExcelDatabody, and CreateExcelBook.

Thanks in advance,

Greg3858.Excel.JPG

6

Hi Greg,

These are not functions of Excel Buffer but in the report which call function in Excel Buffer. Edit report 120 to see the examples

7

I’m making some progress. I put MakeExcelInfo, MakeExcelDataHeader, etc. under the Function tab and their showing up now.

Greg

8

http://savatage99.googlepages.com/ExcelBufferForDummies.doc

9

Hi Harry,

Nice example [Y]

10

Special Thanks to MARK BRUMMEL & His Excel Buffer Usage Demo

http://www.mibuso.com/dl.asp?FileID=596&Type=file

This one uses dataitem:Customer

11

Thanks Harry.

Great example.

Greg

12

I tried it and it works great…

Thanks again Harry.

Great example…

13

Great to hear - Now let Exporting Reign!!!

Here are some others I palayed with and so can you

http://savatage99.googlepages.com/50095-CustomerExportToExcel.fob
http://savatage99.googlepages.com/Report50095-CustomerInfoExportToExce.txt
http://savatage99.googlepages.com/50085-CustomerAgingInfoToExcel.fob
http://savatage99.googlepages.com/Report50069-ItemLedgerEntryExcelExpo.txt
http://savatage99.googlepages.com/Report50073-CLE_Export2Excel_2.0.fob

14

Harry,

On your 50085 Aging it is cutting off our leading zero numbers on the Customer no.

Ex. 00302 is showing up as 302 on the Excel sheet. Must be treating it as a number instead of text.

Any way to fix this?

Greg

15

Hi Greg,

This is because Excel thinks you are sending a number so you need to flag it as a text field.

You will need to add

TempExcelBuffer.NumberFormat := ‘@’;

for fields you what as text and

TempExcelBuffer.NumberFormat := ‘’;

for numbers

Modify the EnterCell function and pass down a parameter is best.

16

Or simply add 4 single quotes to the front of the code

EnterCell(Row, Column, ‘’’’+“Customer”.“No.”, FALSE, FALSE, FALSE);

17

Thanks Harry.

This also works. Put this before the field name.

‘’’’+

Greg

18

Harry,

I’m working on one of my own and for some reason it’s not working. After I run the report the fields are in the table 370 Excel Buffer but Excel isn’t starting and I have to remove all the records from table 370 before I run it again.

I must be missing one step. It compiles fine and runs the only thing is the records in the Excel Buffer table stay there.

Greg

19

I fixed it Harry.

I added this to the OnPostDataItem.

//Create The Worksheet
TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet(Text000,Text000,COMPANYNAME,USERID);
TempExcelBuffer.GiveUserControl;

20

glad you got it to work creating the worksheet is a critical part [:P].

Once you enter the info into one report - or get it from one of the reports I posted. You can simply copy & paste it into other report so you don’t have to re-type.