Hi Readers.
Yesterday, I posted a blog about How to use Excel Buffer to Export data in Business Central. Today in this post we will discuss about how to import excel data to BC SaaS.
Before it, please remember that you can use Configuration Packages to export or import excel files without any customization.
In order to explain import function from excel simply and clearly, I created a new table and a new Worksheet page. You can also import data from the Excel file into existing Business Central table.
1. Create a new table with fields
SO Import Buffer table:
2. Create a new Worksheet Page
SO Import Worksheet Page:
3. Add new action button to import data from excel.
New page after publishing:
Import Excel format:
4. Add two steps in Action.
5. Read Excel Sheet.
You need to use streams in Business Central SaaS.
For Example:
IStream: InStream;
You can also get the file name and sheet name of Excel. (FileName and SheetName)
PS:
After you choose Import action on the page:
UploadExcelMsg: Label ‘Please Choose the Excel file.’;
After you choose Cancel:
NoFileFoundMsg: Label ‘No Excel file found!’;
6. Import Excel Data: Get last Line Number of new table and Row Number of excel
7. Import Excel Data: Create a new “get value at cell” function
8. Import Excel Data to BC table.
PS:
If you need to validate the value, you can add validate under Evaluate easily.
9. Create a excel file which you want to import.
OK, let’s publish and test it.
Test Video:
END
This is just a simple demonstration. You can also put the function in the report and add some parameters to control the actions when importing.
Hope this will help.
Thanks.
ZHU
The complete code is as follows:
Table:
table 50101 "SO Import Buffer"
{
Caption = 'SO Import Buffer';
fields
{
field(1; "Batch Name"; Code[10])
{
Caption = 'Batch Name';
DataClassification = CustomerContent;
}
field(2; "Line No."; Integer)
{
Caption = 'Line No.';
Editable = false;
DataClassification = CustomerContent;
}
field(3; "File Name"; Text[50])
{
Caption = 'File Name';
Editable = false;
DataClassification = CustomerContent;
}
field(4; "Sheet Name"; Text[30])
{
Caption = 'Sheet Name';
DataClassification = CustomerContent;
}
field(5; "Imported Date"; Date)
{
Caption = 'Imported Date';
Editable = false;
DataClassification = CustomerContent;
}
field(6; "Imported Time"; Time)
{
Caption = 'Imported Time';
Editable = false;
DataClassification = CustomerContent;
}
field(7; "Sell-to Customer No."; Code[20])
{
Caption = 'Sell-to Customer No.';
TableRelation = Customer;
DataClassification = CustomerContent;
}
field(8; "Document No."; Code[20])
{
Caption = 'Document No.';
DataClassification = CustomerContent;
}
field(9; "Posting Date"; Date)
{
Caption = 'Posting Date';
DataClassification = CustomerContent;
}
field(10; "Currency Code"; Code[10])
{
Caption = 'Currency Code';
TableRelation = Currency;
DataClassification = CustomerContent;
}
field(11; "Document Date"; Date)
{
Caption = 'Document Date';
DataClassification = CustomerContent;
}
field(12; "External Document No."; Code[35])
{
Caption = 'External Document No.';
DataClassification = CustomerContent;
}
field(13; Type; Enum "Sales Line Type")
{
Caption = 'Type';
DataClassification = CustomerContent;
}
field(14; "No."; Code[20])
{
Caption = 'No.';
DataClassification = CustomerContent;
}
field(15; Quantity; Decimal)
{
Caption = 'Quantity';
DecimalPlaces = 0 : 5;
DataClassification = CustomerContent;
}
field(16; "Unit Price"; Decimal)
{
Caption = 'Unit Price';
AutoFormatType = 2;
AutoFormatExpression = "Currency Code";
DataClassification = CustomerContent;
}
}
keys
{
key(Key1; "Batch Name", "Line No.")
{
}
}
}Page:
page 50102 "SO Import Worksheet"
{
AutoSplitKey = true;
Caption = 'SO Import Worksheet';
DelayedInsert = true;
InsertAllowed = false;
ModifyAllowed = false;
PageType = Worksheet;
SaveValues = true;
SourceTable = "SO Import Buffer";
SourceTableView = sorting("Batch Name", "Line No.");
UsageCategory = Tasks;
ApplicationArea = All;
layout
{
area(content)
{
field(BatchName; BatchName)
{
Caption = 'Batch Name';
ApplicationArea = All;
}
repeater(Group)
{
Editable = false;
field("Batch Name"; Rec."Batch Name")
{
Visible = false;
ApplicationArea = All;
}
field("Line No."; Rec."Line No.")
{
Visible = false;
ApplicationArea = All;
}
field("Document No."; Rec."Document No.")
{
ApplicationArea = All;
}
field("Sell-to Customer No."; Rec."Sell-to Customer No.")
{
ApplicationArea = All;
}
field("Posting Date"; Rec."Posting Date")
{
ApplicationArea = All;
}
field("Currency Code"; Rec."Currency Code")
{
ApplicationArea = All;
}
field("Document Date"; Rec."Document Date")
{
ApplicationArea = All;
}
field("External Document No."; Rec."External Document No.")
{
ApplicationArea = All;
}
field(Type; Rec.Type)
{
ApplicationArea = All;
}
field("No."; Rec."No.")
{
ApplicationArea = All;
}
field(Quantity; Rec.Quantity)
{
ApplicationArea = All;
}
field("Unit Price"; Rec."Unit Price")
{
ApplicationArea = All;
}
field("File Name"; Rec."File Name")
{
ApplicationArea = All;
}
field("Sheet Name"; Rec."Sheet Name")
{
ApplicationArea = All;
}
field("Imported Date"; Rec."Imported Date")
{
ApplicationArea = All;
}
field("Imported Time"; Rec."Imported Time")
{
ApplicationArea = All;
}
}
}
}
actions
{
area(processing)
{
action("&Import")
{
Caption = '&Import';
Image = ImportExcel;
Promoted = true;
PromotedCategory = Process;
ApplicationArea = All;
ToolTip = 'Import data from excel.';
trigger OnAction()
var
begin
if BatchName = '' then
Error(BatchISBlankMsg);
ReadExcelSheet();
ImportExcelData();
end;
}
}
}
var
BatchName: Code[10];
FileName: Text[100];
SheetName: Text[100];
TempExcelBuffer: Record "Excel Buffer" temporary;
UploadExcelMsg: Label 'Please Choose the Excel file.';
NoFileFoundMsg: Label 'No Excel file found!';
BatchISBlankMsg: Label 'Batch name is blank';
ExcelImportSucess: Label 'Excel is successfully imported.';
local procedure ReadExcelSheet()
var
FileMgt: Codeunit "File Management";
IStream: InStream;
FromFile: Text[100];
begin
UploadIntoStream(UploadExcelMsg, '', '', FromFile, IStream);
if FromFile <> '' then begin
FileName := FileMgt.GetFileName(FromFile);
SheetName := TempExcelBuffer.SelectSheetsNameStream(IStream);
end else
Error(NoFileFoundMsg);
TempExcelBuffer.Reset();
TempExcelBuffer.DeleteAll();
TempExcelBuffer.OpenBookStream(IStream, SheetName);
TempExcelBuffer.ReadSheet();
end;
local procedure ImportExcelData()
var
SOImportBuffer: Record "SO Import Buffer";
RowNo: Integer;
ColNo: Integer;
LineNo: Integer;
MaxRowNo: Integer;
begin
RowNo := 0;
ColNo := 0;
MaxRowNo := 0;
LineNo := 0;
SOImportBuffer.Reset();
if SOImportBuffer.FindLast() then
LineNo := SOImportBuffer."Line No.";
TempExcelBuffer.Reset();
if TempExcelBuffer.FindLast() then begin
MaxRowNo := TempExcelBuffer."Row No.";
end;
for RowNo := 2 to MaxRowNo do begin
LineNo := LineNo + 10000;
SOImportBuffer.Init();
Evaluate(SOImportBuffer."Batch Name", BatchName);
SOImportBuffer."Line No." := LineNo;
Evaluate(SOImportBuffer."Document No.", GetValueAtCell(RowNo, 1));
Evaluate(SOImportBuffer."Sell-to Customer No.", GetValueAtCell(RowNo, 2));
Evaluate(SOImportBuffer."Posting Date", GetValueAtCell(RowNo, 3));
Evaluate(SOImportBuffer."Currency Code", GetValueAtCell(RowNo, 4));
Evaluate(SOImportBuffer."Document Date", GetValueAtCell(RowNo, 5));
Evaluate(SOImportBuffer."External Document No.", GetValueAtCell(RowNo, 6));
Evaluate(SOImportBuffer.Type, GetValueAtCell(RowNo, 7));
Evaluate(SOImportBuffer."No.", GetValueAtCell(RowNo, 8));
Evaluate(SOImportBuffer.Quantity, GetValueAtCell(RowNo, 9));
Evaluate(SOImportBuffer."Unit Price", GetValueAtCell(RowNo, 10));
SOImportBuffer."Sheet Name" := SheetName;
SOImportBuffer."File Name" := FileName;
SOImportBuffer."Imported Date" := Today;
SOImportBuffer."Imported Time" := Time;
SOImportBuffer.Insert();
end;
Message(ExcelImportSucess);
end;
local procedure GetValueAtCell(RowNo: Integer; ColNo: Integer): Text
begin
TempExcelBuffer.Reset();
If TempExcelBuffer.Get(RowNo, ColNo) then
exit(TempExcelBuffer."Cell Value as Text")
else
exit('');
end;
}
コメント