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

kanika

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!!

AlexDen

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;