Thursday, December 1, 2022

Create Multiple Sheet Excel Book - Also send on email in BC SaaS

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);