Dynamics Community Forum Thread Details

Export an Excel file that contains a certain layout and design

Hello,

I am exporting an Excel to make a price comparison of certain offers and I have encountered two problems:

1) Creating the Excel from the beginning using the Excel Buffer table, it is not possible to format the cells (except bold, italic and underlined).

2) Seeing that problem 1 seems complex, I have created a template on the server that has a default layout associated with it. Well, I use the Excel Buffer table to fill in the data but the format of the cells that are written is lost... and that's where I need help. Do you know how I can solve this problem? I show you two images of a small simplified test so you can see what happens as well as the associated code:

1881.Before.PNG 4061.After.PNG

codeunit 50013 "Files Mgt2"
{
    trigger OnRun()
    begin

    end;

    procedure ExportQuotesToExcel(var Quote: Record "Purchase Header")
    var
        TempExcelBuf: Record "Excel Buffer" temporary;
    begin
        InitExcel(TempExcelBuf);
        FillExcelBuffer(TempExcelBuf, Quote);
        TempExcelBuf.CloseBook();
        TempExcelBuf.SetFriendlyFilename('test');
        TempExcelBuf.OpenExcel();
    end;

    local procedure InitExcel(var TempExcelBuf: Record "Excel Buffer" temporary)
    var
        InStr: InStream;
        Files: File;
    begin
        Files.Open('C:\Users\Public\Comparador ofertas compra.xlsx');
        Files.CreateInStream(InStr);

        TempExcelBuf.UpdateBookStream(InStr, 'Precios', false);
    end;

    local procedure FillExcelBuffer(var TempExcelBuf: Record "Excel Buffer" temporary; var Quote: Record "Purchase Header")
    var
        TempExcelBufSheet: Record "Excel Buffer" temporary;
    begin
        TempExcelBufSheet.EnterCell(TempExcelBufSheet, 2, 1, 'test', true, false, false);
        TempExcelBuf.WriteAllToCurrentSheet(TempExcelBufSheet);
    end;


}

The version of BC we use is 16.1 (we know that there are new Excel features currently available, but we are not migrating for now).

Thank you so much.

Quick Links