How Do I Create My First Spreadsheet?
Goals of this Exercise:
- Create a new Spreadsheet
- Create a reference to cells A1 to A3 (A1:A3)
- Set the content of cell A1 of the first sheet to "Value A1"
- Set the content of cell A2 of the first sheet to "Value A2"
- Set the content of cell A3 of the first sheet to "Value A3"
- Save the Spreadsheet as a MS Excel file named "First_Excel_Exercise.xlsx"
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"
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"