Excel dotnet in Excel Buffer table

Roelof · 2019-03-14

I need to find a solution to update an existing Excel Spreadsheet. There is some code in table 370 (Excel Buffer) that allows a user to write to excel as a new spreadsheet but NOT updating an existing sheet with tabs.
Which dotnet options do I have in order to achieve that?
In older NAV version with Automation it was a lot easier.
Any ideas?

AlexDen · 2019-03-14

Hello,

There is a function UpdateBook() in table 370 which you can use to update existing workbook.
Below is an example how to add text to D3 cell:

ClientFileName := FileManagement.OpenFileDialog('','',FileManagement.GetToFilterText('','*.xlsx'));
IF ClientFileName = '' THEN 
  EXIT;

ServerFileName := FileManagement.UploadFileSilent(ClientFileName);

SheetName := xlBuf.SelectSheetsName(ServerFileName);
xlBuf.UpdateBook(ServerFileName,SheetName);
xlBuf.SetCurrent(3,3);
xlBuf.AddColumn('This cell is populated from Nav',FALSE,'',FALSE,FALSE,FALSE,'',xlBuf."Cell Type"::Text);
xlBuf.WriteSheet('',COMPANYNAME,USERID);
xlBuf.CloseBook;
xlBuf.DownloadAndOpenExcel;

Roelof · 2019-03-14

So, would 'XlBuf.SetCurrent(3,3) refer to Cel D3?
And what options in your example is there to read the Cel?
I need to compare the cel.value before I will write to it.

AlexDen · 2019-03-15

To read cell content you have to declare one more Excel Buffer variable and execute ReadSheet() function to get content of the sheet. Then just add to xlBuf cells which you need to update:

xlBufRead.OpenBook(ServerFileName,SheetName);
xlBufRead.ReadSheet;

xlBuf.UpdateBook(ServerFileName,SheetName);

IF xlBufRead.FINDSET THEN
  REPEAT
    IF xlBufRead."Cell Value as Text" = 'Your condition' THEN BEGIN
      xlBuf := xlBufRead;
      xlBuf."Cell Value as Text" := 'New cell value';
      xlBuf.INSERT;
    END;
  UNTIL xlBufRead.NEXT = 0;

xlBuf.WriteSheet('',COMPANYNAME,USERID);
xlBuf.CloseBook;
xlBuf.DownloadAndOpenExcel;

Howdy, Stranger!

Quick Links

Categories

Excel dotnet in Excel Buffer table

Answers