Hello everyone.
Yesterday, my friend asked me a question how to export data to excel with Business Central SaaS. Their company also has similar customization, but there is a problem that the exported data is all text. So I created a very easy sample code, and thought it would make sense to write a blog post to explain the code.
Before it, please remember that Microsoft has added “Open in Excel” on the list page. You can use it to export data without any customization.
For more details: (Japanese)
Dynamics 365 Business CentralとExcelの連携について (BC<->Excel、ノーカスタマイズ)
The example: Exports the Customer Ledger Entries to a new Excel file.
1. Create a new page extension and add a new action called “Export to Excel”
2. Refer an export procedure in OnAction trigger
3. Create a new export procedure
local procedure ExportCustLedgerEntries(var CustLedgerEntryRec: Record “Cust. Ledger Entry”)
TempExcelBuffer: Record “Excel Buffer” temporary; Main method
CustLedgerEntriesLbl: Label ‘Customer Ledger Entries’; Sheet Name
ExcelFileName: Label ‘CustomerLedgerEntries_%1_%2’; Excel File Name
4. Clear Excel Buffer
5. Add header of contents
TempExcelBuffer.NewRow(); Start adding new row in Excel.
TempExcelBuffer.AddColumn(CustLedgerEntryRec.FieldCaption(“Entry No.”), false, ”, false, false, false, ”, TempExcelBuffer.”Cell Type”::Text); Start adding new Column (Caption of “Entry No.”)
………
Note:
When you add columns, you can choose some default font and cell type.
AddColumn Function:
procedure AddColumn(Value: Variant, IsFormula: Boolean, CommentText: Text, IsBold: Boolean, IsItalics: Boolean, IsUnderline: Boolean, NumFormat: Text[30], CellType: Option)
CellType: Date, Number, Text, Time.
So you should set the celltype according to the type of the field in Business Central.
For example:
Date -> Cell Type::Date
Integer, BigInteger, Decimal -> Cell Type::Number
Time -> Cell Type::Time
Others -> Cell Type::Text
6. Add Customer Ledger Entry lines to excel
As mentioned above, please pay attention to the value of CellType.
7. Create a excel and save.
This is similar to a fixed format, specify the sheet name, excel file name, and save.
END.
Let’s publish and test it.
The file name:
Sheet name:
Test Video:
PS:
If you think the code for inserting columns is too long, you can use RecordRef and FieldRef.
For Example:
RecordRef.Getable(CustLedgerEntryRec)
FieldRef.Number
FieldRef.Name
FieldRef.Caption
FieldRef.Value
The complete code is as follows:
pageextension 50101 CustLedgEntriesExt extends "Customer Ledger Entries"
{
actions
{
addafter("F&unctions")
{
action(ExportToExcel)
{
Caption = 'Export to Excel';
ApplicationArea = All;
Promoted = true;
PromotedCategory = Process;
PromotedIsBig = true;
Image = Export;
trigger OnAction()
var
begin
ExportCustLedgerEntries(Rec);
end;
}
}
}
local procedure ExportCustLedgerEntries(var CustLedgerEntryRec: Record "Cust. Ledger Entry")
var
TempExcelBuffer: Record "Excel Buffer" temporary;
CustLedgerEntriesLbl: Label 'Customer Ledger Entries';
ExcelFileName: Label 'CustomerLedgerEntries_%1_%2';
begin
TempExcelBuffer.Reset();
TempExcelBuffer.DeleteAll();
TempExcelBuffer.NewRow();
TempExcelBuffer.AddColumn(CustLedgerEntryRec.FieldCaption("Entry No."), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec.FieldCaption("Posting Date"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec.FieldCaption("Document Type"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec.FieldCaption("Document No."), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec.FieldCaption("Customer No."), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec.FieldCaption("Customer Name"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec.FieldCaption(Description), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec.FieldCaption("Currency Code"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec.FieldCaption("Original Amount"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec.FieldCaption(Amount), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec.FieldCaption("Amount (LCY)"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec.FieldCaption("Remaining Amount"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec.FieldCaption("Remaining Amt. (LCY)"), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec.FieldCaption(Open), false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
if CustLedgerEntryRec.FindSet() then
repeat
TempExcelBuffer.NewRow();
TempExcelBuffer.AddColumn(CustLedgerEntryRec."Entry No.", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec."Posting Date", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Date);
TempExcelBuffer.AddColumn(CustLedgerEntryRec."Document Type", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec."Document No.", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec."Customer No.", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec."Customer Name", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec.Description, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec."Currency Code", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(CustLedgerEntryRec."Original Amount", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number);
TempExcelBuffer.AddColumn(CustLedgerEntryRec.Amount, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number);
TempExcelBuffer.AddColumn(CustLedgerEntryRec."Amount (LCY)", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number);
TempExcelBuffer.AddColumn(CustLedgerEntryRec."Remaining Amount", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number);
TempExcelBuffer.AddColumn(CustLedgerEntryRec."Remaining Amt. (LCY)", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number);
TempExcelBuffer.AddColumn(CustLedgerEntryRec.Open, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
until CustLedgerEntryRec.Next() = 0;
TempExcelBuffer.CreateNewBook(CustLedgerEntriesLbl);
TempExcelBuffer.WriteSheet(CustLedgerEntriesLbl, CompanyName, UserId);
TempExcelBuffer.CloseBook();
TempExcelBuffer.SetFriendlyFilename(StrSubstNo(ExcelFileName, CurrentDateTime, UserId));
TempExcelBuffer.OpenExcel();
end;
}END
Hope this will help.
Thanks.
ZHU
コメント