How Do I Create My First Spreadsheet?

Goals of this Exercise:

  1. Create a new Spreadsheet
  2. Create a reference to cells A1 to A3 (A1:A3)
  3. Set the content of cell A1 of the first sheet to "Value A1"
  4. Set the content of cell A2 of the first sheet to "Value A2"
  5. Set the content of cell A3 of the first sheet to "Value A3"
  6. Save the Spreadsheet as a MS Excel file named "First_Excel_Exercise.xlsx"

Required vocabulary:

  • XCEL_Application_Show
  • XCEL_Worbook_Create
  • XCEL_Range_Ref_Add
  • XCEL_Range_Values_Set
  • XCEL_WorkBook_SaveAsFile

Showing the MS Excel Application

On Windows, the XCEL_Application_Show is required for showing MS Excel on screen.

On Mac, this command is forcing the MS Application to come on the top of other opened applications.

For testing the result of our work it could be useful to have the things appear on screen and see if all is happening as expected.

Creating the new Spreadsheet

To create a new worksheet, we are using the XCEL_Worbook_Create command.

No parameter is required for this command.

If the workbook has been created with success "the result" is containing the name of the new workbook. This workbook name will be used as a reference to communicate with the workbook object.

XCEL_Workbook_New
put the result into tNewWorkbook

Preparing a reference to a range in the Spreadsheet

In the Excel Library a reference to a range in an Excel Spreadsheet, is an object we have to define in the Excel Library. This object once created, can be reused anytime we want in the project, until it is removed from memory by the commands XCEL_Range_Ref_Remove or XCEL_Range_Ref_CleanAll.

A range object is defined by:

  • the name of the reference we can use with other Excel Library command.
  • the workbook reference: Can be the number or the name of any opened workbook. In our example the reference is contained by the tNewWorkbook variable.
  • the worksheet reference: Can be the number or the name of any opened sheet. In this example we will communicate with the first worksheet, so the reference will be 1
  • the range reference: an Excel range reference in the A1 reference style. The reference can be a simple cell (A1) ; a range of cells (A1:A3) or a multiple area selection (A1:C3,D7:E3). We can also use a name defined in MS Excel and associated to a range.

As our goal is to work with cells A1 to A3, we are creating the following reference:

put XCEL_Range_Ref_Add("RangeA1_A3", tNewWorkbook, 1, "A1:A3") into tTheRange

Creating Values in the Spreadsheet

Now we have created a reference to the range A1:A3 of the first worksheet of our new workbook tNewWorkbook, we can fill the 3 cells A1, A2 and A3 by using the XCEL_Range_Values_Set command.

The command XCEL_Range_Values_Set is requiring the following parameters:

  • A reference to a range. This is the name of the object reference we have created by using the XCEL_Range_Ref_Add function. In this Exercise the tTheRange variable contains the name of the reference
  • A list of values in the tsv form: columns are seperated by tabs and lines by returns.
put "Value A1" & cr & "Value A2" & cr & "Value A3" into tCellsData
XCEL_Range_Values_Set tTheRange, tCellsData

Saving the Spreadsheet as a MS Excel File

It is now time to save our work in a file. For accomplishing this, we are using the XCEL_WorkBook_SaveAsFile command.

This command requires the following parameters:

  • the workbook reference: Can be the number or the name of any opened workbook. In our example the tNewWorkbook variable.
  • the file path. This can be a full path or a file name. In our example the name for the Spreadsheet file is "First_Excel_Exercise.xlsx"
  • the file format. We are using the keyword "native" to save the Spreadsheet in the native MS Excel format (xlsx)
XCEL_WorkBook_SaveAsFile tNewWorkbook, "First_Excel_Exercise.xlsx", "native"

Final Code 

   local tNewWorkbook, tTheRange, tCellsData
   
   XCEL_Workbook_New
   put the result into tNewWorkbook   

   XCEL_Application_Show
      
   put XCEL_Range_Ref_Add("RangeA1_A3", tNewWorkbook, 1, "A1:A3") into tTheRange
   
   put "Value A1" & cr & "Value A2" & cr & "Value A3" into tCellsData
   XCEL_Range_Values_Set tTheRange, tCellsData
   
   XCEL_WorkBook_SaveAsFile tNewWorkbook, "First_Excel_Exercise.xlsx", "native"
Click to copy

4 Comments

Charles Szasz

I have collected data from LC fields and used tab a delimiter. How do I past the data into one row of an excel spreadsheet?

Zryip - ASG Soft

Hi Charles,

We need 2 commands for accomplishing this:
- XCEL_Range_Ref_Add for defining a range inside the Excel Lib
- XCEL_Range_Values_Set for "pasting" the data into the spreadsheet row

Suppose you have collected data for five columns and you want to paste these informations into the third row.
In the Excel notation this range is "A3:D3", but how to use it with the Excel Lib?

In the Excel Lib, we have thought a range, as an alias you can reuse with different commands. The owner of this range is the workbook and inside the workbook, the worksheet. (In Excel several workbooks can be opened at a time, each containing at least one worksheet)
The XCEL_Range_Ref_Add function is allowing users to define a range inside the library. It accepts 4 parameters:
- first, is the name of the range object to use inside the Lib
- second, is the reference to a workbook opened inside Excel. This reference can be a name or number
- third, is the reference to a worksheet of the workbook. This reference can be a name or number
- fourth, is the range in the Excel Notation

The goal of this function, is you have not the need to repeat the workbook, the worksheet and the reference every time, but just the name you defined by using XCEL_Range_Ref_Add.

put XCEL_Range_Ref_Add("myRange","myWorkbookRef","myWorksheetRef","A3:D3") into tMyRange

Now we can add the data inside the worksheet:
XCEL_Range_Values_Set tMyRange, tTheData

Final example script:
on mouseUp
put "Data1 " & tab & "Data2" & tab & "Data3" & tab & "Data4" & tab & "Data5" into tTheData
put XCEL_Range_Ref_Add("myRange","myWorkbookRef","myWorksheetRef","A3:D3") into tMyRange
XCEL_Range_Values_Set tMyRange, tTheData
end mouseUp

Note by using other command available in the Excel Lib you can create something more dynamic, by extending the range from column A to another column (XCEL_uCellRef) and by looking for the next unfilled row (XCEL_Column_LastFilledCell_Get). Have a look to the documentation for more informations.

Charles Ayton

If I used a spreadsheet as detailed above, would my distibutable project work on user's computer without Excel installed ?

Mark Wieder

The spreadsheet isn't an application, so the user's computer would need something that can handle spreadsheets. Not necessarily Excel though... I use the free LibreOffice with no problems.

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.