Excel Buffer How to Check Worksheet Exists

samantha73

Hi All
I'm importing an Excel file with multiple worksheets into BC SaaS, and I want to check if a specific worksheet exists and if not provide a user friendly error message. Say worksheet "Order" does not exist.

ftornero

Hello @samantha73,

From the "Excel Buffer" table you can use the procedure GetSheetsNameListFromStream, and if it return true check the temp table TempNameValueBufferOut for the worsheet name that you are looking for.

    procedure GetSheetsNameListFromStream(FileStream: InStream; var TempNameValueBufferOut: Record "Name/Value Buffer" temporary) SheetsFound: Boolean
    var
        SheetNames: DotNet ArrayList;
        SheetName: Text[250];
        i: Integer;
    begin
        XlWrkBkReader := XlWrkBkReader.Open(FileStream);
        TempNameValueBufferOut.Reset();
        TempNameValueBufferOut.DeleteAll();

        SheetNames := SheetNames.ArrayList(XlWrkBkReader.SheetNames);
        if IsNull(SheetNames) then
            exit(false);

        SheetsFound := SheetNames.Count > 0;

        if not SheetsFound then
            exit(false);

        for i := 0 to SheetNames.Count - 1 do begin
            SheetName := SheetNames.Item(i);
            if SheetName <> '' then begin
                TempNameValueBufferOut.Init();
                TempNameValueBufferOut.ID := i;
                TempNameValueBufferOut.Name := Format(i + 1);
                TempNameValueBufferOut.Value := SheetName;
                TempNameValueBufferOut.Insert();
            end;
        end;

        CloseBook;
    end;

Regards

ftornero

Hello @samantha73,

From the "Excel Buffer" table you can use the procedure GetSheetsNameListFromStream, and if it return true check the temp table TempNameValueBufferOut for the worsheet name that you are looking for.

    procedure GetSheetsNameListFromStream(FileStream: InStream; var TempNameValueBufferOut: Record "Name/Value Buffer" temporary) SheetsFound: Boolean
    var
        SheetNames: DotNet ArrayList;
        SheetName: Text[250];
        i: Integer;
    begin
        XlWrkBkReader := XlWrkBkReader.Open(FileStream);
        TempNameValueBufferOut.Reset();
        TempNameValueBufferOut.DeleteAll();

        SheetNames := SheetNames.ArrayList(XlWrkBkReader.SheetNames);
        if IsNull(SheetNames) then
            exit(false);

        SheetsFound := SheetNames.Count > 0;

        if not SheetsFound then
            exit(false);

        for i := 0 to SheetNames.Count - 1 do begin
            SheetName := SheetNames.Item(i);
            if SheetName <> '' then begin
                TempNameValueBufferOut.Init();
                TempNameValueBufferOut.ID := i;
                TempNameValueBufferOut.Name := Format(i + 1);
                TempNameValueBufferOut.Value := SheetName;
                TempNameValueBufferOut.Insert();
            end;
        end;

        CloseBook;
    end;

Regards