How can we Export Excel With Multiple Sheets?

How can we Export Excel With Multiple Sheets?

Hello everyone,

Yesterday, my friend asked me a question how to export data to excel with Multiple Sheets From Business Central SaaS. So I created a very easy sample code, and thought it would make sense to write a blog post to explain the code.

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.

1. Create a new page extension and add a new action called “Export Excel”
   
   

2. Refer an export procedure in OnAction trigger
   
   
   
   
   
   
   
3. Create a new Table extension and Create a new Procedure called “ExportExcel”
   
   
   
   

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.

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

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.
  
  
  
  

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 :
  
  
  

• Hope you find this article useful.
  
  Thank you all.
  
  

For more details, please visit our website www.madhda.com or contact us on Contact@madhda.com