Codeunit 608 Data Archive Export To Excel

Data Archive, System.DataAdministration

src/Apps/W1/DataArchive/App/src/DataArchiveExportToExcel.Codeunit.al182 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.// ------------------------------------------------------------------------------------------------/// <summary>/// Exports the contents one or more "Data Archive Table" records into an Excel book with one sheet per table./// </summary>namespace System.DataAdministration;using System.IO;using System.Utilities;codeunit 608 "Data Archive Export To Excel"{    Access = Internal;    TableNo = "Data Archive Table";    trigger OnRun()    begin        ExportToExcel(Rec);    end;    var        ArchiveEmptyErr: Label 'There are no archived records.';        GeneratingFileMsg: Label 'Creating file @1@@';        NotAllTablesExportedMsg: Label 'You do not have the Read permission for some of the selected tables. Only the tables you have the Read permission for have been exported.';    local procedure ExportToExcel(var DataArchiveTable: Record "Data Archive Table")    var        TempExcelBuffer: Record "Excel Buffer" temporary;        Window: Dialog;        CurrentRecNo: Integer;        NoOfTablesWithNoReadAccess: Integer;        T0: Time;    begin        if DataArchiveTable.GetFilter("Data Archive Entry No.") = '' then            DataArchiveTable.SetRange("Data Archive Entry No.", DataArchiveTable."Entry No.");        DataArchiveTable.SetFilter("No. of Records", '>0');        DataArchiveTable.SetAutoCalcFields("Table Name");        if not DataArchiveTable.FindSet() then            Error(ArchiveEmptyErr);        Window.Open(GeneratingFileMsg);        T0 := Time;        TempExcelBuffer.CreateNewBook(DataArchiveTable."Table Name" + '-' + format(DataArchiveTable."Entry No."));        Window.Update(1, 0);        repeat            if DataArchiveTable.HasReadPermission() then begin                TempExcelBuffer.DeleteAll();                FillExcelBuffer(TempExcelBuffer, DataArchiveTable, Window, T0, CurrentRecNo);                TempExcelBuffer.SelectOrAddSheet(DataArchiveTable."Table Name" + '-' + format(DataArchiveTable."Entry No."));                TempExcelBuffer.WriteAllToCurrentSheet(TempExcelBuffer);            end else                 NoOfTablesWithNoReadAccess += 1;        until DataArchiveTable.Next() = 0;        TempExcelBuffer.CloseBook();        Window.Close();        if NoOfTablesWithNoReadAccess > 0 then            Message(NotAllTablesExportedMsg);        TempExcelBuffer.OpenExcel();    end;    local procedure FillExcelBuffer(var TempExcelBuffer: Record "Excel Buffer" temporary; var DataArchiveTable: Record "Data Archive Table"; var Window: Dialog; T0: Time; var CurrentRecNo: integer)    var        DataArchiveMediaField: Record "Data Archive Media Field";        TempBlob: Codeunit "Temp Blob";        SchemaJson: JsonArray;        TableJson: JsonArray;        RecordJson: JsonArray;        FieldJson: JsonObject;        JsonToken: JsonToken;        InStr: InStream;        OutStr: OutStream;        RecordNo: Integer;        FieldIndex: Integer;        FieldNo: Integer;        FieldValueAsText: Text;        FieldTypeAsText: Text;        FieldValueAsDateTime: DateTime;        FieldValueAsDate: Date;        FieldValueAsInteger: Integer;        FieldValueAsDecimal: Decimal;        FieldValueAsTime: Time;    begin        // Schema - Headers in the sheet        TempBlob.CreateOutStream(OutStr, TextEncoding::UTF8);        DataArchiveTable."Table Fields (json)".ExportStream(OutStr);        TempBlob.CreateInStream(InStr, TextEncoding::UTF8);        SchemaJson.ReadFrom(InStr);        for FieldIndex := 0 to SchemaJson.Count() - 1 do begin            SchemaJson.Get(FieldIndex, JsonToken);            FieldJson := JsonToken.AsObject();            FieldJson.Get('FieldName', JsonToken);            FieldValueAsText := JsonToken.AsValue().AsText();            TempExcelBuffer.Init();            TempExcelBuffer.Validate("Column No.", FieldIndex + 1);            TempExcelBuffer.Validate("Row No.", 1);            TempExcelBuffer.Validate("Cell Type", TempExcelBuffer."Cell Type"::Text);            TempExcelBuffer."Cell Value as Text" := copystr(FieldValueAsText, 1, MaxStrLen(TempExcelBuffer."Cell Value as Text"));            TempExcelBuffer.Insert();        end;        // Data - Rows in the sheet        Clear(TempBlob);        TempBlob.CreateOutStream(OutStr, TextEncoding::UTF8);        DataArchiveTable."Table Data (json)".ExportStream(OutStr);        TempBlob.CreateInStream(InStr, TextEncoding::UTF8);        TableJson.ReadFrom(InStr);        for RecordNo := 0 to TableJson.Count() - 1 do begin            TableJson.Get(RecordNo, JsonToken);            RecordJson := JsonToken.AsArray();            for FieldIndex := 0 to SchemaJson.Count() - 1 do begin                SchemaJson.Get(FieldIndex, JsonToken);                FieldJson := JsonToken.AsObject();                FieldJson.Get('FieldNumber', JsonToken);                FieldNo := JsonToken.AsValue().AsInteger();                FieldJson.Get('DataType', JsonToken);                FieldTypeAsText := JsonToken.AsValue().AsText();                RecordJson.Get(FieldIndex, JsonToken);                FieldJson := JsonToken.AsObject();                FieldJson.Get(Format(FieldNo), JsonToken);                FieldValueAsText := JsonToken.AsValue().AsText();                TempExcelBuffer.Init();                TempExcelBuffer.Validate("Column No.", FieldIndex + 1);                TempExcelBuffer.Validate("Row No.", RecordNo + 2);                TempExcelBuffer.Validate("Cell Type", TempExcelBuffer."Cell Type"::Text);                case FieldTypeAsText of                    'Integer':                        begin                            if Evaluate(FieldValueAsInteger, FieldValueAsText, 9) then                                FieldValueAsText := format(FieldValueAsInteger);                            TempExcelBuffer.Validate("Cell Type", TempExcelBuffer."Cell Type"::Number);                        end;                    'Decimal':                        begin                            if Evaluate(FieldValueAsDecimal, FieldValueAsText, 9) then                                FieldValueAsText := format(FieldValueAsDecimal);                            TempExcelBuffer.Validate("Cell Type", TempExcelBuffer."Cell Type"::Number);                        end;                    'Date':                        begin                            if Evaluate(FieldValueAsDate, FieldValueAsText, 9) then                                FieldValueAsText := format(FieldValueAsDate);                            TempExcelBuffer.Validate("Cell Type", TempExcelBuffer."Cell Type"::Date);                        end;                    'DateTime':                        begin                            if Evaluate(FieldValueAsDateTime, FieldValueAsText, 9) then                                FieldValueAsText := format(FieldValueAsDateTime);                            TempExcelBuffer.Validate("Cell Type", TempExcelBuffer."Cell Type"::Date);                        end;                    'Time':                        begin                            if Evaluate(FieldValueAsTime, FieldValueAsText, 9) then                                FieldValueAsText := format(FieldValueAsTime);                            TempExcelBuffer.Validate("Cell Type", TempExcelBuffer."Cell Type"::Time);                        end;                    'Blob', 'Media':                        if Evaluate(FieldValueAsInteger, FieldValueAsText, 9) then                            if DataArchiveMediaField.Get(FieldValueAsInteger) then                                FieldValueAsText := format(DataArchiveMediaField."Field Content");                    else                        TempExcelBuffer.Validate("Cell Type", TempExcelBuffer."Cell Type"::Text);                end;                TempExcelBuffer."Cell Value as Text" := copystr(FieldValueAsText, 1, MaxStrLen(TempExcelBuffer."Cell Value as Text"));                TempExcelBuffer.Insert();            end;            CurrentRecNo += 1;            if Time > T0 + 1000 then begin                Window.Update(1, CurrentRecNo * 100 div DataArchiveTable."No. of Records");                T0 := Time;            end;        end;    end;}