Export data to Ms Excel

It’s often necessary to convert data stored in your database to another form, such as an Excel workbook. You may wish to take advantage of some of Excel’s unique analytical capabilities or simply share data with someone unfamiliar with databases. Whatever the reason, the conversion process is fairly straightforward and quick.
To export data from your database to Excel, you must create a script file (*.cd).
In the file, you must have the query that will call the data from the tables you want, and the commands that will export the data to Excel.
Let’s take a few moments to walk through the process, step-by-step.

1. Open the application.
2. Right-click and select "Developer".
3. Select “Scripts” and then select “Create”
4. In the window, you must write your code. The example below shows the code for exporting three columns, customer name, address, and phone, from the table customer.

proc exp_customer()

       start_sql “CUSTOMER” “KOSMOS”
             SELECT
             CUSTOMER.FIRST_NAME, CUSTOMER.LAST_NAME,
             CUSTOMER.ADDRESS, CUSTOMER.PHONE
             FROM
             CUSTOMER  
             ORDER BY
             CUSTOMER.LAST_NAME, CUSTOMER.FIRST_NAME 
       end_sql

       call RunEmbSQL(“CUSTOMER”)

       q = QueryByName(“CUSTOMER”)
       rcount = TRecordCount(q)
 
       call DisconnectExcel()     this command closes ms excel

       call OpenExcelDoc("C:\CUSTOMER.XLS")    this command opens the excel file (this file must be created, the data is always exported into an existing excel file)

       call SetExCell2("A1";”CUSTOMERS”;ftString)  this command puts the value CUSTOMERS in A1 cell

       line = 0  
      
       call TFirst(q)
       for i = 1 to rcount

             name = strcat(CUSTOMER.LAST_NAME;” “;CUSTOMER.FIRST_NAME)

             line = line + 1

             call SetExCell1(line;2;name;ftString)
             call SetExCell1(line;3;DATA.ADDRESS;ftString) this command puts the field address in C1 cell, the first parameter is the line, the second is the column, the third is the value you want to put in the cell, the fourth it the type of the value, ftString for strings and ftFloat for numbers 
             call SetExCell1(line;4;DATA.PHONE;ftString)

            call TNext(q)
       next

       call FreeEmbSQL(“CUSTOMER”)

       call ShowExcel()  this command opens the excel and the file

end

5. Save changes and close the window.
6. Call the procedure. 
    (You can call the procedure from the menu or with a button.
     The command you use is  "call procedure name()".
     For example "call exp_customer()"   )