Hello Friends,
Below sample code to save multiple sheet data in final merge sheet
var
ExcelBuf: Record "Excel Buffer" temporary;
ExcelBuf2: Record "Excel Buffer" temporary;
ExcelBuf3: Record "Excel Buffer" temporary;
MergeExcelBuf: Record "Excel Buffer" temporary;
procedure CreateExcelbook()
begin
if not SendEmil_gBln then begin
ExcelBuf.Reset();
IF ExcelBuf.FindSet() Then begin
repeat
MergeExcelBuf.Init();
MergeExcelBuf := ExcelBuf;
MergeExcelBuf.Insert();
until ExcelBuf.Next() = 0;
end;
CreateExcelSheet('Sales', TRUE);
ExcelBuf2.Reset();
IF ExcelBuf2.FindFirst() THen begin
repeat
MergeExcelBuf.Init();
MergeExcelBuf := ExcelBuf2;
MergeExcelBuf.Insert();
until ExcelBuf2.NExt = 0;
end;
CreateExcelSheet('Collection', false);
ExcelBuf3.Reset();
IF ExcelBuf3.FindSet() Then begin
repeat
MergeExcelBuf.Init();
MergeExcelBuf := ExcelBuf3;
MergeExcelBuf.Insert();
until ExcelBuf3.NExt = 0;
end;
CreateExcelSheet('Receivables', false);
CreateExcelBook_Final;
end else begin
ExcelBuf.Reset();
IF ExcelBuf.FindSet() Then begin
repeat
MergeExcelBuf.Init();
MergeExcelBuf := ExcelBuf;
MergeExcelBuf.Insert();
until ExcelBuf.Next() = 0;
end;
CreateExcelSheet('Sales', TRUE);
ExcelBuf2.Reset();
IF ExcelBuf2.FindFirst() THen begin
repeat
MergeExcelBuf.Init();
MergeExcelBuf := ExcelBuf2;
MergeExcelBuf.Insert();
until ExcelBuf2.NExt = 0;
end;
CreateExcelSheet('Collection', false);
ExcelBuf3.Reset();
IF ExcelBuf3.FindSet() Then begin
repeat
MergeExcelBuf.Init();
MergeExcelBuf := ExcelBuf3;
MergeExcelBuf.Insert();
until ExcelBuf3.NExt = 0;
end;
CreateExcelSheet('Receivables', false);
end;
end;
local procedure CreateExcelSheet(SheetName: Text[250]; NewBook: Boolean)
begin
if NewBook then
MergeExcelBuf.CreateNewBook(SheetName)
else
MergeExcelBuf.SelectOrAddSheet(SheetName);
MergeExcelBuf.WriteSheet(SheetName, CompanyName, UserId);
MergeExcelBuf.DeleteAll();
MergeExcelBuf.ClearNewRow();
end;
local procedure CreateExcelBook_Final()
var
TempBlob_lCdu: Codeunit "Temp Blob";
begin
MergeExcelBuf.CloseBook();
MergeExcelBuf.SetFriendlyFilename('Stock Report');
MergeExcelBuf.OpenExcel();
end;
procedure GetExportExcelFileToBlob(var TempBlob: Codeunit "Temp Blob")
var
OutStr: OutStream;
begin
MergeExcelBuf.CloseBook();
MergeExcelBuf.SetFriendlyFilename('Stock Report');
TempBlob.CreateOutStream(OutStr);
MergeExcelBuf.SaveToStream(OutStr, true);
end;
=============
Now below code is use to send email for multiple sheet email
Clear(FortnightlyReport_lRpt);
FortnightlyReport_lRpt.SetMail_gFnc(CalcDate('<-CM>', Today), Today); //T12358-N
FortnightlyReport_lRpt.UseRequestPage(false);
FortnightlyReport_lRpt.RunModal;
FortnightlyReport_lRpt.GetExportExcelFileToBlob(TempBlob_lCdu);
if not FortnightlyReport_lRpt.GetDataInserted_gBln then
CurrReport.Break;
TempBlob_lCdu.CREATEINSTREAM(Instr);
EmailMessage.AddAttachment('Fortnightly Report.XLSX', 'xlsx', Instr);
