send a formula to exceL and return the result (2)

kanikakanika Member Posts: 247
I need to send a formula to excel from a CU and return the result.

I can not do what I need, this is my code:

ExcelBuffer.NewRow;
ExcelBuffer.AddColumn(vFormula,TRUE,'',FALSE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Number);

***option 1:
ExcelBuffer.CreateBookAndOpenExcel('\\naviserver\RED\Libro1.xlsx','Hoja1','header',COMPANYNAME,USERID);
problem: passes the formula but does not close,therefore the code that follows is not executed

***option 2:
ExcelBuffer.CreateBook('\\naviserver\RED\Libro1.xlsx','Hoja1');
problem:does not pass the formula as CreateBookAndOpenExcel but if it can be closed,the code that follows does run but since it does not save the formula it reads ''

ExcelBuffer.CloseBook;
ExcelBuffer.OpenBook('\\RED\MyBook.xlsx','Sheet1');
ExcelBuffer.ReadSheet();
IF ExcelBuffer.FINDFIRST THEN
REPEAT
txtValor := txtValor + ExcelBuffer."Cell Value as Text" + '\';
UNTIL ExcelBuffer.NEXT = 0;
MESSAGE(txtValor);

Variable (temporary=Yes) ExcelBuffer record 370

functions:
txtValor (text,250)

I also tried functions in table 370, but the instance fails me
SabeBook, Internal, Global, Parameters: txtFileName(text,250)
XlWrkBk.SaveAs(txtFileName,'xlAddIn','','','','',1,'','','','','');
QuitApp, Internal, Global
XlApp.Quit;

I need guidance because I do not know what I'm doing wrong.

THANKS BY ADVANCE!!

Answers

  • AlexDenAlexDen Member Posts: 85
    Hi.

    Excel Buffer generates xml file and doesn't calculate formulas.
    To calculate formulas you have to use Excel Interop.

    Here is an example how to achieve this.

    1. Function to generate file with formula and get formulas value
    OBJECT Codeunit 50000 Test
    {
      OBJECT-PROPERTIES
      {
        Date=31.07.18;
        Time=12:00:00;
        Modified=Yes;
        Version List=;
      }
      PROPERTIES
      {
        OnRun=BEGIN
                GetExcelFormulaValue;
              END;
    
      }
      CODE
      {
    
        LOCAL PROCEDURE GetExcelFormulaValue@1000000001();
        VAR
          xlBuf@1000000011 : TEMPORARY Record 370;
          XMLDOMManagement@1000000010 : Codeunit 6224;
          FileManagement@1000000009 : Codeunit 419;
          XlWrkShtReader@1000000008 : DotNet "'Microsoft.Dynamics.Nav.OpenXml, Version=10.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorksheetReader";
          XmlWrkShtDoc@1000000006 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";
          XmlCellNode@1000000005 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlElement";
          XmlNode@1000000012 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlElement";
          XmlNamespaceManager@1000000004 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNamespaceManager";
          Variant@1000000003 : Variant;
          xPath@1000000002 : Text;
          FileNameClient@1000000001 : Text;
          FileNameServer@1000000000 : Text;
          FormulaText@1000000013 : Text;
          ValueText@1000000014 : Text;
        BEGIN
          // Generate Excel file
          xlBuf.AddColumn('=2*2',TRUE,'',TRUE,FALSE,FALSE,'',xlBuf."Cell Type"::Number);
          xlBuf.CreateBook('','Sheet1');
          xlBuf.WriteSheet('','','');
          xlBuf.CloseBook;
          // Download file to client, open it in Excel to calculate formulas
          FileNameClient := xlBuf.OpenAndSaveExcel;  // New function in Excel Buffer
          xlBuf.QuitExcel;
    
          // Upload file with calculated formulas back to server
          FileNameServer := FileManagement.UploadFileSilent(FileNameClient);
          FileManagement.DeleteClientFile(FileNameClient);
    
          //Open book
          xlBuf.OpenBook(FileNameServer, 'Sheet1');
          xlBuf.UTgetGlobalValue('XlWrkShtReader',Variant);  // 'XlWrkShtReader' is new parameter for this function
          XlWrkShtReader := Variant;
    
          //read formula and value from XmlDocument
          XmlWrkShtDoc := XmlWrkShtDoc.XmlDocument;
          XmlWrkShtDoc.LoadXml(XlWrkShtReader.Worksheet.OuterXml);
    
          XMLDOMManagement.AddNamespaces(XmlNamespaceManager,XmlWrkShtDoc);
          xPath := STRSUBSTNO('/x:worksheet/x:sheetData/x:row[@r=''%1'']/x:c[@r=''%2%1'']', 1, 'A');
          XmlCellNode := XmlWrkShtDoc.SelectSingleNode(xPath,XmlNamespaceManager);
    
          IF ISNULL(XmlCellNode) THEN
            EXIT;
    
          XmlNode := XmlCellNode.SelectSingleNode('x:f',XmlNamespaceManager);
          IF NOT ISNULL(XmlNode) THEN
            FormulaText := XmlNode.InnerText;
    
          XmlNode := XmlCellNode.SelectSingleNode('x:v',XmlNamespaceManager);
          IF NOT ISNULL(XmlNode) THEN
            ValueText := XmlNode.InnerText;
    
          MESSAGE('Formula: %1\Value: %2', FormulaText, ValueText);
    
          xlBuf.QuitExcel;
        END;
    
        BEGIN
        END.
      }
    
    2. Add some code to Excel buffer:
    Add these lines to UTgetGlobalValue function:
    'XlWrkShtReader':
              value := XlWrkShtReader;
    
    Add this new function:
        PROCEDURE OpenAndSaveExcel@1000000000() FileNameClient : Text;
        VAR
          FileNameClientTemp@1000000000 : Text;
        BEGIN
          IF OpenUsingDocumentService('') THEN
            EXIT;
    
          IF NOT PreOpenExcel THEN
            EXIT;
    
          FileNameClientTemp := FileManagement.DownloadTempFile(FileNameServer);
          FileNameClient := FileManagement.ClientTempFileName(ExcelFileExtensionTok);
    
          XlWrkBk := XlHelper.CallOpen(XlApp,FileNameClientTemp);
    
          PostOpenExcel;
    
          XlHelper.CallSaveAs(XlWrkBk, FileNameClient);
          QuitExcel;
    
          FileManagement.DeleteClientFile(FileNameClientTemp);
        END;
    
Sign In or Register to comment.