To export data to an Excel file with multiple sheets in Microsoft Dynamics 365 Business Central, you can use the built-in capabilities along with some custom development.
In this article, we will try to list down methods required to export data to excel using Excel Buffer.
For Demo, let’s assume we would like to Export data from Sales Header Table with associated Sales Line entries.
Step 1
Create a new page extension and add a new action called “Export Excel”
Step 2
Refer an export procedure in OnAction trigger
Step 3
Create a new Table extension and Create a new Procedure called “ExportExcel”
Step 4
For Exporting Data using Excel Buffer, create a global variable ExcelBuffer which should always be set to Temporary as shown below.
Note : The Function that is used to write data into excel buffer is AddColumn which requires following parameters –
1. Value – Pass the value that you want to print in Excel.
2. IsFormula – If the Value passed is a Excel Formula then set it to True.
3. CommentText – If you would like to add excel comment then pass a text.
4. IsBold – If you would like to set the column Text to be Bold.
5. IsItalics – If you would like to set the column Text to be Italics.
6. IsUnderline – If you would like to set the column Text to be Underline.
7. NumFormat – If you would like to set the column with a Number formatting.
8. CellType – This parameter set the Cell Type in Excel, Options are – Text, Date, Time, Number.
Step 5
Create a new Procedure called “ExportHeader”
• In this procedure we would like to add Captions in Excel. I am using fixed text, but you can also use Field Captions for fields in Customer Table if required.
• The Headers will be Bold and That’s why Parameter 4 is set to True.
• After caption we would like to add data in Excel. To add a New Row after every record, you will require to call function NewRow. Then using AddColumn you can add data into the Excel Buffer Table.
• After adding data in excel write a function CreateNewBook with SheetName.
• After Creating ExcelBook we have to write function WriteSheet with ExcelFileName.
Step 6
Create a new Procedure called “ExportLine”
• In this procedure we will export second sheet with LineData in continue excelfile.
• For create new sheet in same Excelfile first we should set Row & Column to beginning of sheet for that we will use “SetCurrent” function from ExcelBuffer Table.
• After we will add new sheet with using “SelectOrAddSheet” function with SheetName from ExcelBuffer table. And write sheet.
Step 7
Create a new procedure called CreateExcel.
• This is similar to a fixed format, specify the sheet name, excel file name, and save.
• As shown below all functions would be call from “ExportExcel” main function.
• Let’s publish and test it.
• The file name:
• Header Sheet Name With Data :
• SalesLine Sheet Name With Data :
Thank you for investing your time to read our blog! We’re passionate about sharing valuable insights to help you navigate the world of Business Central.
If you’re curious about how we can assist you in reaching your goals, don’t hesitate to get in touch. Our dedicated team is ready to provide support every step of the way.
Let’s turn your vision into reality together!
