Hi, I tried again in my test solution and it seems to be fine.
You can refer the code from my post. Hope to give you some hints.
https://yzhums.com/21483/
[View:/cfs-file/__key/communityserver-discussions-components-files/758/UploadZipfileWithExcelFiles.mp4:1066:800
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 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 Excel';
Image = ImportExcel;
Promoted = true;
PromotedCategory = Process;
ApplicationArea = All;
ToolTip = 'Import data from excel.';
trigger OnAction()
begin
if BatchName = '' then
Error(BatchISBlankMsg);
ImportFromZip := false;
ReadExcelSheet();
ImportExcelData();
end;
}
action(ImportZipFile)
{
Caption = 'Import Zip File';
ApplicationArea = All;
Promoted = true;
PromotedCategory = Process;
PromotedIsBig = true;
Image = Import;
ToolTip = 'Import Attachments from Zip';
trigger OnAction()
var
FileMgt: Codeunit "File Management";
DataCompression: Codeunit "Data Compression";
TempBlob: Codeunit "Temp Blob";
EntryList: List of [Text];
EntryListKey: Text;
ZipFileName: Text;
FileName: Text;
FileExtension: Text;
InStream: InStream;
EntryOutStream: OutStream;
EntryInStream: InStream;
Length: Integer;
SelectZIPFileMsg: Label 'Select ZIP File';
FileCount: Integer;
ImportedMsg: Label '%1 excel Imported successfully.';
FromFile: Text[100];
begin
if BatchName = '' then
Error(BatchISBlankMsg);
if not UploadIntoStream(SelectZIPFileMsg, '', 'Zip Files|*.zip', ZipFileName, InStream) then
Error('');
ImportFromZip := true;
//Extract zip file and store files to list type
DataCompression.OpenZipArchive(InStream, false);
DataCompression.GetEntryList(EntryList);
FileCount := 0;
//Loop files from the list type
foreach EntryListKey in EntryList do begin
FileName := CopyStr(FileMgt.GetFileNameWithoutExtension(EntryListKey), 1, MaxStrLen(FileName));
FileExtension := CopyStr(FileMgt.GetExtension(EntryListKey), 1, MaxStrLen(FileExtension));
TempBlob.CreateOutStream(EntryOutStream);
DataCompression.ExtractEntry(EntryListKey, EntryOutStream, Length);
TempBlob.CreateInStream(EntryInStream);
SheetName := TempExcelBuffer.SelectSheetsNameStream(EntryInStream);
TempExcelBuffer.Reset();
TempExcelBuffer.DeleteAll();
TempExcelBuffer.OpenBookStream(EntryInStream, SheetName);
TempExcelBuffer.ReadSheet();
ImportExcelData();
FileCount = 1;
end;
//Close the zip file
DataCompression.CloseZipArchive();
if FileCount > 0 then
Message(ImportedMsg, FileCount);
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.';
ImportFromZip: Boolean;
trigger OnOpenPage()
begin
BatchName := 'Test';
end;
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;
if not ImportFromZip then
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;
}
Hope this helps.
Thanks.
ZHU
