Excel dotnet in Excel Buffer table

RoelofRoelof Posts: 348Member
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?
Roelof de Jong
http://www.esopro.com

Answers

  • AlexDenAlexDen Posts: 34Member
    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;
    
  • RoelofRoelof Posts: 348Member
    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.
    Roelof de Jong
    http://www.esopro.com
  • AlexDenAlexDen Posts: 34Member
    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;
    
Sign In or Register to comment.