Report 7113 Export Analysis Rep. to Excel

Base Application, Microsoft.Inventory.Analysis

src/Layers/W1/BaseApp/Inventory/Analysis/ExportAnalysisReptoExcel.Report.al305 lines, Copyright (c) Microsoft Corporation. MIT

// ------------------------------------------------------------------------------------------------// Copyright (c) Microsoft Corporation. All rights reserved.// Licensed under the MIT License. See License.txt in the project root for license information.// ------------------------------------------------------------------------------------------------namespace Microsoft.Inventory.Analysis;using Microsoft.Finance.Analysis;using Microsoft.Finance.Currency;using Microsoft.Finance.Dimension;using Microsoft.Finance.GeneralLedger.Setup;using System.IO;using System.Utilities;report 7113 "Export Analysis Rep. to Excel"{    Caption = 'Export Analysis Rep. to Excel';    ProcessingOnly = true;    UseRequestPage = false;    dataset    {        dataitem("Integer"; "Integer")        {            DataItemTableView = sorting(Number) where(Number = const(1));            trigger OnAfterGetRecord()            var                Window: Dialog;                ClientFileName: Text;                RecNo: Integer;                TotalRecNo: Integer;                RowNo: Integer;                ColumnNo: Integer;            begin                if DoUpdateExistingWorksheet then begin                    if ServerFileName = '' then                        ServerFileName := FileMgt.UploadFile(Text002, ExcelFileExtensionTok);                    if ServerFileName = '' then                        exit;                    ClientFileName := FileMgt.GetFileName(ServerFileName);                    SheetName := TempExcelBuffer.SelectSheetsName(ServerFileName);                    if SheetName = '' then                        exit;                end;                Window.Open(                  Text000 +                  '@1@@@@@@@@@@@@@@@@@@@@@\');                Window.Update(1, 0);                TotalRecNo := AnalysisLine.Count();                RecNo := 0;                TempExcelBuffer.DeleteAll();                Clear(TempExcelBuffer);                AnalysisLineTemplate.Get(AnalysisLine."Analysis Area", AnalysisLine."Analysis Line Template Name");                if AnalysisLineTemplate."Item Analysis View Code" <> '' then                    ItemAnalysisView.Get(AnalysisLineTemplate."Analysis Area", AnalysisLineTemplate."Item Analysis View Code");                GLSetup.Get();                RowNo := 1;                EnterCell(RowNo, 1, Text001, false, false, true, '', TempExcelBuffer."Cell Type"::Text);                if AnalysisLine.GetFilter("Date Filter") <> '' then begin                    RowNo := RowNo + 1;                    EnterFilterInCell(                      RowNo,                      AnalysisLine.GetFilter("Date Filter"),                      AnalysisLine.FieldCaption("Date Filter"),                      '',                      TempExcelBuffer."Cell Type"::Text);                end;                if AnalysisLine.GetFilter("Item Budget Filter") <> '' then begin                    RowNo := RowNo + 1;                    EnterFilterInCell(                      RowNo,                      AnalysisLine.GetFilter("Item Budget Filter"),                      AnalysisLine.FieldCaption("Item Budget Filter"),                      '',                      TempExcelBuffer."Cell Type"::Text);                end;                if AnalysisLine.GetFilter("Dimension 1 Filter") <> '' then begin                    RowNo := RowNo + 1;                    EnterFilterInCell(                      RowNo,                      AnalysisLine.GetFilter("Dimension 1 Filter"),                      GetDimFilterCaption(1),                      '',                      TempExcelBuffer."Cell Type"::Text);                end;                if AnalysisLine.GetFilter("Dimension 2 Filter") <> '' then begin                    RowNo := RowNo + 1;                    EnterFilterInCell(                      RowNo,                      AnalysisLine.GetFilter("Dimension 2 Filter"),                      GetDimFilterCaption(2),                      '',                      TempExcelBuffer."Cell Type"::Text);                end;                if AnalysisLine.GetFilter("Dimension 3 Filter") <> '' then begin                    RowNo := RowNo + 1;                    EnterFilterInCell(                      RowNo,                      AnalysisLine.GetFilter("Dimension 3 Filter"),                      GetDimFilterCaption(3),                      '',                      TempExcelBuffer."Cell Type"::Text);                end;                RowNo := RowNo + 1;                if GLSetup."LCY Code" <> '' then begin                    RowNo := RowNo + 1;                    EnterFilterInCell(                      RowNo,                      GLSetup."LCY Code",                      Currency.TableCaption(),                      '',                      TempExcelBuffer."Cell Type"::Text);                end;                RowNo := RowNo + 1;                AnalysisLine.SetFilter(Show, '<>%1', AnalysisLine.Show::No);                if AnalysisLine.Find('-') then begin                    if ColumnLayout.Find('-') then begin                        RowNo := RowNo + 1;                        ColumnNo := 1;                        repeat                            ColumnNo := ColumnNo + 1;                            EnterCell(                              RowNo,                              ColumnNo,                              ColumnLayout."Column Header",                              false,                              false,                              false,                              '',                              TempExcelBuffer."Cell Type"::Text);                        until ColumnLayout.Next() = 0;                    end;                    repeat                        RecNo := RecNo + 1;                        Window.Update(1, Round(RecNo / TotalRecNo * 10000, 1));                        RowNo := RowNo + 1;                        ColumnNo := 1;                        EnterCell(                          RowNo,                          ColumnNo,                          AnalysisLine.Description,                          AnalysisLine.Bold,                          AnalysisLine.Italic,                          AnalysisLine.Underline,                          '',                          TempExcelBuffer."Cell Type"::Text);                        if ColumnLayout.Find('-') then                            repeat                                ColumnValue := AnalysisReportManagement.CalcCell(AnalysisLine, ColumnLayout, false);                                if AnalysisReportManagement.GetDivisionError() then                                    ColumnValue := 0;                                ColumnNo := ColumnNo + 1;                                EnterCell(                                  RowNo,                                  ColumnNo,                                  MatrixMgt.FormatAmount(ColumnValue, ColumnLayout."Rounding Factor", false),                                  AnalysisLine.Bold,                                  AnalysisLine.Italic,                                  AnalysisLine.Underline,                                  '',                                  TempExcelBuffer."Cell Type"::Number)                            until ColumnLayout.Next() = 0;                    until AnalysisLine.Next() = 0;                end;                Window.Close();                if DoUpdateExistingWorksheet then begin                    TempExcelBuffer.UpdateBook(ServerFileName, SheetName);                    TempExcelBuffer.WriteSheet('', CompanyName, UserId);                    TempExcelBuffer.CloseBook();                    if not TestMode then                        TempExcelBuffer.OpenExcelWithName(ClientFileName);                end else begin                    TempExcelBuffer.CreateBook(ServerFileName, AnalysisTemplateName);                    TempExcelBuffer.WriteSheet(AnalysisLine.Description, CompanyName, UserId);                    TempExcelBuffer.CloseBook();                    if not TestMode then                        TempExcelBuffer.OpenExcel();                end;            end;        }    }    requestpage    {        layout        {        }        actions        {        }    }    labels    {    }    var        AnalysisLineTemplate: Record "Analysis Line Template";        AnalysisLine: Record "Analysis Line";        ColumnLayout: Record "Analysis Column";        TempExcelBuffer: Record "Excel Buffer" temporary;        GLSetup: Record "General Ledger Setup";        ItemAnalysisView: Record "Item Analysis View";        Currency: Record Currency;        AnalysisReportManagement: Codeunit "Analysis Report Management";        MatrixMgt: Codeunit "Matrix Management";        FileMgt: Codeunit "File Management";        ColumnValue: Decimal;        ServerFileName: Text;        SheetName: Text[250];        AnalysisTemplateName: Code[10];        DoUpdateExistingWorksheet: Boolean;        TestMode: Boolean;#pragma warning disable AA0074        Text000: Label 'Analyzing Data...\\';        Text001: Label 'Filters';        Text002: Label 'Update Workbook';#pragma warning restore AA0074        ExcelFileExtensionTok: Label '.xlsx', Locked = true;    procedure SetOptions(var AnalysisLine2: Record "Analysis Line"; ColumnLayoutName2: Code[10]; NewAnalysisTemplateName: Code[10])    begin        AnalysisTemplateName := NewAnalysisTemplateName;        AnalysisLine.CopyFilters(AnalysisLine2);        AnalysisLine := AnalysisLine2;        AnalysisLine.SetFilter("Analysis Line Template Name", NewAnalysisTemplateName);        ColumnLayout.SetRange("Analysis Area", AnalysisLine2."Analysis Area");        ColumnLayout.SetRange("Analysis Column Template", ColumnLayoutName2);        ColumnLayout.SetFilter(Show, '<>%1', ColumnLayout.Show::Never);    end;    local procedure EnterFilterInCell(RowNo: Integer; "Filter": Text[250]; FieldName: Text[100]; Format: Text[30]; CellType: Option)    begin        if Filter <> '' then begin            EnterCell(RowNo, 1, FieldName, false, false, false, '', TempExcelBuffer."Cell Type"::Text);            EnterCell(RowNo, 2, Filter, false, false, false, Format, CellType);        end;    end;    local procedure EnterCell(RowNo: Integer; ColumnNo: Integer; CellValue: Text[250]; Bold: Boolean; Italic: Boolean; UnderLine: Boolean; Format: Text[30]; CellType: Option)    begin        TempExcelBuffer.Init();        TempExcelBuffer.Validate("Row No.", RowNo);        TempExcelBuffer.Validate("Column No.", ColumnNo);        TempExcelBuffer."Cell Value as Text" := CellValue;        TempExcelBuffer.Formula := '';        TempExcelBuffer.Bold := Bold;        TempExcelBuffer.Italic := Italic;        TempExcelBuffer.Underline := UnderLine;        TempExcelBuffer.NumberFormat := Format;        TempExcelBuffer."Cell Type" := CellType;        TempExcelBuffer.Insert();    end;    local procedure GetDimFilterCaption(DimFilterNo: Integer): Text[80]    var        Dimension: Record Dimension;    begin        if AnalysisLineTemplate."Item Analysis View Code" = '' then            case DimFilterNo of                1:                    Dimension.Get(GLSetup."Global Dimension 1 Code");                2:                    Dimension.Get(GLSetup."Global Dimension 2 Code");            end        else            case DimFilterNo of                1:                    Dimension.Get(ItemAnalysisView."Dimension 1 Code");                2:                    Dimension.Get(ItemAnalysisView."Dimension 2 Code");                3:                    Dimension.Get(ItemAnalysisView."Dimension 3 Code");            end;        exit(CopyStr(Dimension.GetMLFilterCaption(GlobalLanguage), 1, 80));    end;    procedure SetUpdateExistingWorksheet(UpdateExistingWorksheet: Boolean)    begin        DoUpdateExistingWorksheet := UpdateExistingWorksheet;    end;    procedure SetFileNameSilent(NewFileName: Text)    begin        ServerFileName := NewFileName;    end;    procedure SetTestMode(NewTestMode: Boolean)    begin        TestMode := NewTestMode;    end;}