1
0
-1

Buenas tardes, alguien tendrá un ejemplo completo de como crear y incluir datos en una hoja de excel?. 

Los ejemplos que están en el portal son muy viejos y poco claros. Nosotros deseamos incluir una cantidad de datos en forma de líneas completas si es posible y no de celda en celda.

Muchas gracias

    CommentAdd your comment...

    4 answers

    1.  
      1
      0
      -1

      Gracias Gianni, pero no me funciona el OpenText y no se que estoy haciendo mal, Te copio el codigo que estoy usando a ver si me puedes ayudar..

      ; Create instance of Excel
          newinstance "APPLICATION", $vExcelHandle$
          if ($status < 0) return ($status)
      ;
          $vExcelHandle$->SET_VISIBLE(1)
          if ($status < 0) return ($status)
      ;
      ; Execute Workbooks.get -- creates handle vWorkBooks for more manipulations
          $vExcelHandle$->GET_WORKBOOKS($vWorkBooks$)
          if ($status < 0) return ($status)
      ;
          FileName = "D:\Temp\%%$cprog$%%%.csv"
      ;
      ; Open spread sheet named in FileName  Note: parameter "-" indicates optional parm not included
          $vWorkBooks$->OpenText(FileName, -, -, -, -, -, -, 1, -, -, -, -, -, -, -, -, -, -)
          if ($status < 0) return ($status)
      ;
      ; Create handle to active workbook
          $vExcelHandle$->GET_ACTIVEWORKBOOK($vActiveWorkBook$)
          if ($status < 0) return ($status)
      ;
      ; Needs to create handle to active sheet ( p_workbook->GET_ACTIVESHEET(p_activesheet))
          $vActiveWorkBook$->GET_ACTIVESHEET($vActivesheet$)
          if ($status < 0) return ($status)
      ;

      Al ejecutar el "OpenText" arroja un error $status = -1 y $procerror=-150


      Muchas gracias y saludos

      1. Gianni Sandigliano

        Hi Pedro Manuel,

        my working path is different from yours:
        you are trying to fully drive Excel via Uniface for the whole flow of actions
           while
        the module I maintain is driving Excel via Uniface to run a macro saved in a standard Excel program; this Excel BIG macro is implementing the flow of actions I've proposed to you.

        Anyhow I try to help you but only based on indirect experience.

        $procerror = -150 : I can only suppose here that Excel does NOT like your call because you are simplifying it too much. what I mean is, in this instruction:
            $vWorkBooks$->OpenText(FileName, -, -, -, -, -, -, 1, -, -, -, -, -, -, -, -, -, -)
        you are asking Excel to load a CSV file sending him just the "filename" in the first parameter and a value of 1 for the "semicolon" eighth parameter. You have a BIG hope all other parameters could be avoided having a working default. I am supposing this is NOT correct for Excel. The OpenText method in MSExcel (up to 2016 version) when loaded as signature in Uniface is reported having these parameters:

        FileName                IN    String
        Origin                  IN    String
        StartRow                IN    String
        DataType                IN    String
        TextQualifier           IN    Numeric
        ConsecutiveDelimiter    IN    String
        Tab                     IN    String
        Semicolon               IN    String
        Comma                   IN    String
        Space                   IN    String
        Other                   IN    String
        OtherChar               IN    String
        FieldInfo               IN    String
        TextVisualLayout        IN    String
        DecimalSeparator        IN    String
        ThousandsSeparator      IN    String
        TrailingMinusNumbers    IN    String
        Local                   IN    String

        If you try to register an Excel macro to load a CSV file the resulting macro command is the one I had already published:

            Workbooks.OpenText Filename:=v_csv, Origin:=xlWindows, _
                StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
                ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False, _
                Space:=False, Other:=False, FieldInfo:=v_fieldinfo

        As you can see:
        - Filename is defined with the full path name of the file to be loaded (this is OK in your call)
        - Origin is defined equal to xlWindows Excel constant
        - StartRow is explicitely defined as 1.
        - DataType is defined equal to xlDelimited Excel constant
        - TextQualifier is defined to xlNone Excel constant
        - ConsecutiveDelimiter is defined to False Excel constant
        - Tab is defined to False Excel constant
        - Semicolon is defined to True Excel constant
        - Comma is defined to False Excel constant
        - Space is defined to False Excel constant
        - Other is defined to False Excel constant
        - FieldInfo is a vector object (in my example: v_fieldinfo) which contain DataType for each single column
        Latest parameters in the OpenText() call could probably defined effectively as Optional with a simple dash "-".

        $procerror = -150 I suppose means Excel is expecting some of these parameters to be explicitely defined. Four hints:
        1) If you define semicolon as True you must also probably define Tab, Comma, Space, Other as False
        2) Semicolon in the signature is defined as String while you are passing a Numeric Constant
        3) FieldInfo is IMHO a mandatory parameter to correctly load a CSV file
        4) All Excel constants REAL values could be easily found in the Excel programmer online documentation

        I suppose you can only find YOUR solution with a Trial&Error session.

        Hope it helps.

        Regards
        Gianni

      CommentAdd your comment...
    2.  
      1
      0
      -1

      Hola, disculpa pero tengo una duda basado en tu recomendación:


      2) Build a Uniface functionality which:
          a) Starts an hidden Excel instance (if not already available)

      >> No debería haber problema

          b) Asks to the Excel instance to load your CSV

      >> Cual es la instrucción o funciona para cargar el CVS

          c) ... (many things could be implemented here!) ...
          d) Asks to the Excel instance to save the file as Excel format

      >> No debería haber problema

          e) End the hidden Excel instance

      >> No debería haber problema

      3) Delete the CSV file used to send informations to Excel


      Si es posible, me pudiera aclarar el punto (b)


      Muchas gracias

      1. Gianni Sandigliano

        Hi Pedro Manuel,

        Yes, I agree with your analysis: loading cell by cell or also row by row it works, but could be an acceptable technique up to few thousands rows (IMHO up to max 4 digits = 9999); with larger amount of data it's too slow. In the same timeframe using a CSV file the Uniface application could send to Excel 10 to 15 times more rows.
        I've made few stress test using CSV with 32bit Uniface 9.7.05 and 32bit Excel365 2016 installed onPremises into a virtualized (VMware) Windows 10 64bit environment with 4GB memory. My target was 500.000 rows but the test application was able to generate a CSV and convert it to a real Excel spreadsheet with up to 850.000 rows. To send full 1 million rows from Uniface into Excel a larger memory and/or a full 64bit environment is probably mandatory but it was out of my scope to dig further.

        Going to your question:
        if you are already opening an Excel spreadsheet from within Uniface, probably you should already have a signature called "WORKBOOKS" exposed from Excel COM interface; from that signature you are currently using a method called "OPEN".
        In the same signature it is available another method called "OPENTEXT": this is the one used from Excel to load a CSV file. It is equivalent to the following Excel macro:

            Workbooks.OpenText Filename:=v_csv, Origin:=xlWindows, _
                StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
                ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False, _
                Space:=False, Other:=False, FieldInfo:=v_fieldinfo

        (TIP: I always use to record an Excel macro to start from when I need to define a new functionality in my module!)

        If you have loaded Excel signatures into Uniface automatically using /sti /mwr=com you should have it already available, otherwise you should add it manually to your signature using documentation.

        Hope it helps.

        Gianni

      CommentAdd your comment...
    3.  
      1
      0
      -1

      Muchas gracias Gianni, intentaré por ese modo pero en realidad no es lo que busco. Déjame ser un poco mas explicito.

      En ta actualidad ya cargo la hoja de cálculo con valores pero celda a celda lo que lo hace muy lento.:

      Es un loop por linea donde w_ccol = A1 → XXnn y su valor (w_xval) correspondiente

      ; p_activesheet->GET_RANGE(p_activecell, p_nmrangestart, -)
              $vActivesheet$->GET_RANGE($vActivecell$, w_ccol, -)
              if ($status < 0) return ($status)
      ;
      ; Put something in the active cell (range)
              $vActivecell$->SET_FORMULAR1C1(w_xval)
              if ($status < 0) return ($status)
      ;

      Lo que se desea es conocer vual es la función que me permita cargar una serie de valores en una sola llamada algo así como:

      $vActivesheet$->GET_RANGE($vActivecell$, w_ccol, w_ccol1, w_ccol2, w_ccol3, w_ccoln, .... -)

      $vActivecell$->SET_FORMULAR1C1(w_xval, w_xval1, w_xval2, w_xval3, ....)

      Básicamente es lo que no consigo y la documentación es muy escasa.


      Gracias

        CommentAdd your comment...
      1.  
        1
        0
        -1

        Hi Pedro Manuel,

        I understand you would like to pass to Excel a (large) bunch of informations having them saved in an Excel file format.

        I am sorry, but I cannot publish a working example as you requested (In my case I am the module maintainer but NOT the software owner...I cannot publish any part of it...sorry!). Anyhow I can give you a path to follow.

        Using examples already available, you could:
        1) Dump informations into a CSV file (using filedump and filedump/append) choosing a proper unique filename to support a multiuser installation.
        2) Build a Uniface functionality which:
            a) Starts an hidden Excel instance (if not already available)
            b) Asks to the Excel instance to load your CSV
            c) ... (many things could be implemented here!) ...
            d) Asks to the Excel instance to save the file as Excel format
            e) End the hidden Excel instance
        3) Delete the CSV file used to send informations to Excel

        Hope it helps.

        Gianni

          CommentAdd your comment...