Como crea un archivo Excel

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

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

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

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

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

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