G7 Commands: X¶
Next, some examples will be provided that show how to read from and write to Excel files. For details on each member of this family of commands, see the corresponding entries in this Reference Manual.
Example 1: In this example, the file “XLTEST.XLS” is created in the current directory. The time series are written to the spreadsheet by giving the starting cell, the direction to write the data (down or right), the name of the series, and the range of periods to write.
fdate 1975 2010 # Create sample data. f Year = 1974 + @cum(t, 1.0, 0.0) f Data = 1975 / (t - 1975) xl create xltest.xls # Start Excel server, create xltest.xls workbook. xl open worksheet 1 # Open worksheet 1. xl write A 1 "Writing text to file:" xl write A 3 "Year" # Record label for the year xl write A 4 down Year 1976 2010 # Record the year xl write B 3 "Data" # Record series name xl write B 4 down Data 1976 2010 # Write series 'Data' xl close # Close the workbook.Example 2: In this example, we re-open the same file, and read one of the series back into G7 with a different name.
xl open xltest.xls # Open the workbook. xl open worksheet 1 # Open worksheet 3. xl read A 1 "" # Read the string in position A1; string # will be printed on screen. xl read B 4 right Year2 1976 2010 # Read data into workspace. xl exit # Close workbook, close connection to # Excel server.Example 3: In this example, we must be working with a Vam file, and it must be opened and declared to be the default. The data read from the spreadsheet are stored as vector elements in the Vam file. Writing to the Vam file, instead of to the G7 workspace bank, is forced by providing a bank leter (‘c’) corresponding to the open Vam bank in front of the name of the vector. Note that columns may be specified either by the Excel column letters or by the column number.
xl open C0301e.xls xl open worksheet 1 do{ xl read %1 27 down c.gdpN%2 1990 1990 }(3-4 6-7 9-10)(1-6)m xl exitExample 4: In this final example, we show the use of the “xl matread” command. The command must all be on one line, even though it seems to span two lines in this example. First the blocks of data in the Excel spreadsheet are specified by listing their rows and columns. Next, the name of the matrix is given in which the data should be sotred, along with the matrix rows and columns. Finally, the year is given for which the matrix should be stored.
xl open C0319e.xls xl open worksheet 1 xl matread c(2-18) r(14-17, 19-20, 22-24,26-29,31-32, 34-35)... c.AM c(1-17) r(1-17) 2000Related Topics: 123toG, p123
An xl create workbook command may provide the name for a new Excel filename. If no name is provided, then a new workbook will be created with the default name. The option “workbook” may be abbreviated “wb”.
The default filetype is XLS. Available file types include
File Types
AddIn (.xlam)
WorkbookNormal (.xls)
CSV (.csv)
SYLK (.slk)
CSVMac (.csv)
Template (.xltx)
CSVMSDOS (.csv)
TextMac (.txt)
CSVWindows (.csv)
TextMSDOS (.txt)
DBF2 (.dbf)
TextPrinter (.txt)
DBF3 (.dbf)
TextWindows (.txt)
DBF4 (.dbf)
WK1 (.wk1)
DIF (.dif)
WK1ALL (.wk1)
Excel2 (.xls)
WK1FMT (.wk1)
Excel2FarEast (.xls)
WK3 (.wk3 )
Excel3 (.xls)
WK4 (.wk4)
Excel4 (.xls)
WK3FM3 (.wk3)
Excel5 (.xls)
WKS (.wks)
Excel7 (.xls)
WQ1 (.wq1)
Excel9795 (.xls)
UnicodeText (.txt)
Excel4Workbook (.xls)
Html (.html)
IntlAddIn (.xla)
XLS (.xls)
IntlMacro (.xlsm)
If no extension is provided with the filename, then the appropriate extension will be determined according to the file type and will be appended to the file name.
An xl create worksheet command may provide a name for a new worksheet to be added to the open workbook. The instruction “worksheet” may be replaced with “ws”. An xl create chart command may provide a name for a new chart sheet to be added to the open workbook. The instruction “chart” may be replaced with “ch”. If “before” or “after” is specified, then the new sheet will be inserted to the left or right of the currently-active sheet. See also the xl name command.
xl background <color>Set the background color. Available colors are listed in the xl font <G7RMxlfont> section.
- off:
remove border.
- color:
border color, chosen from the list of Excel colors.
- weight:
border weight, chosen from hairline, thin, medium, or thick.
- position:
border position, chosen from border[default], left, right, top, bottom, horizontal, vertical, diagonalup, or diagonaldown.
- linestyle:
border linestyle, chosen from continuous, dash, dashdot, dashdotdot, dot, double, or slantdashdot.
The command also may be used to recover the title of an existing graph; if no arguments are given, then the graph title subsequently is available using the %xls keyword. If a title is given, then the title will be added to the active chart; the title must be surrounded by quotation marks. If the title is followed by the “font” keyword, then font options may be specified including color, typeface, size, single or double underline, bold, and italic.
Available graph styles include:
Graph Styles
ColumnClustered
Bubble3DEffect
ColumnStacked
StockHLC
ColumnStacked100
StockOHLC
3DColumnClustered
StockVHLC
3DColumnStacked
StockVOHLC
3DColumnStacked100
CylinderColClustered
BarClustered
CylinderColStacked
BarStacked
CylinderColStacked100
BarStacked100
CylinderBarClustered
3DBarClustered
CylinderBarStacked
3DBarStacked
CylinderBarStacked100
3DBarStacked100
CylinderCol
LineStacked
ConeColClustered
LineStacked100
ConeColStacked
LineMarkers
ConeColStacked100
LineMarkersStacked
ConeBarClustered
LineMarkersStacked100
ConeBarStacked
PieOfPie
ConeBarStacked100
PieExploded
ConeCol
3DPieExploded
PyramidColClustered
BarOfPie
PyramidColStacked
XYScatterSmooth
PyramidColStacked100
XYScatterSmoothNoMarkers
PyramidBarClustered
XYScatterLines
PyramidBarStacked
XYScatterLinesNoMarkers
PyramidBarStacked100
AreaStacked
PyramidCol
AreaStacked100
3DColumn
3DAreaStacked
Line
3DAreaStacked100
3DLine
DoughnutExploded
3DPie
RadarMarkers
Pie
RadarFilled
XYScatter
Surface
3DArea
SurfaceWireframe
Area
SurfaceTopView
Doughnut
SurfaceTopViewWireframe
Radar
Bubble
Each row of conditions must begin with “value”; “formula” will be offered later. Operators must be <=, <, ==, !=, >, or >=. If a second condition is specified and the first operator is < or <=, then the second operator must be > or >= (not between), or if the first operator is > or >=, then the second operator must be < or <= (between); otherwise, any second operator and condition will be ignored. A condition may be a number, string, or cell address. Available format settings include font and background; the “font” or “background” keyword must preceed the format specification, where specifications may be chosen from the relevant list. Font options include color, bold, italic, and single or double underline; border is not implemented yet; background options include color. The number of conditions must match the number of rows.
- orientation:
set the page orientation to landscape or portrait.
- area:
set the print area with “area <fr><fc><lr><lc>”, or turn off the print area with “off”.
- print:
print the current page.
If a file type is specified that is different than the current setting, then the spreadsheet will be saved as the new file type. The default filetype is .XLS. Available file types include
File Types
AddIn (.xlam)
WorkbookNormal (.xls)
CSV (.csv)
SYLK (.slk)
CSVMac (.csv)
Template (.xltx)
CSVMSDOS (.csv)
TextMac (.txt)
CSVWindows (.csv)
TextMSDOS (.txt)
DBF2 (.dbf)
TextPrinter (.txt)
DBF3 (.dbf)
TextWindows (.txt)
DBF4 (.dbf)
WK1 (.wk1)
DIF (.dif)
WK1ALL (.wk1)
Excel2 (.xls)
WK1FMT (.wk1)
Excel2FarEast (.xls)
WK3 (.wk3 )
Excel3 (.xls)
WK4 (.wk4)
Excel4 (.xls)
WK3FM3 (.wk3)
Excel5 (.xls)
WKS (.wks)
Excel7 (.xls)
WQ1 (.wq1)
Excel9795 (.xls)
UnicodeText (.txt)
Excel4Workbook (.xls)
Html (.html)
IntlAddIn (.xla)
XLS (.xls)
IntlMacro (.xlsm)
If no extension is provided with the filename, then the appropriate extension will be determined according to the file type and will be appended to the file name.
The first specification will print floating point values or integers to the spreadsheet when numbers are provided as the text argument. Previously, numbers were written as strings, though printing of strings still can be forced with “’<number>”.
The second specification writes a time series into the Excel worksheet, starting at the given location, going up or down a column, or across a row, for the dates given. If no dates are specified, then the current tdates settings will be employed.
- xl vecwrite <vector> < v(index) > < c(cols) > < r(rows) > < direction > [< start > [ end ]]
G7 can print entire vectors of data for multiple years using a single command. The vecwrite command must be used with a VAM bank. This command writes text or data to an open Excel worksheet, where:
- Vector:
The root name of a vector stored in a vam bank. Bank letters are allowed.
- Index:
The range of vector elements that are to be printed.
- Cols:
The spreadsheet columns for the first period of data, given as a group of letters or numbers.
- Rows:
The spreadsheet rows for the first period of data, given as a group of numbers. Either cols or rows must have a single element, and the other must have the same number of elements as contained in index.
- direction:
Either d(own) or r(ight), starting with the row or column specified with the cols and row entries. NOTE: this is the direction indexed by time, so that if data for a particular series are to be written across a row, then “right” should be specified.
- Start:
The starting date, where the date is in G7 format. If dates are not provided, the desired dates are assumed to be those of the current tdates setting.
- End:
The ending date, where the date is in G7 format.
Examples:
xl vecwrite a.output v(1-10) c(B) r(5-14) right 2008 2010
xl formula A 5 “=sum(A1:A4)”
- clear:
Clear the fonts set by the setfont command.
- bold:
Set text to bold face.
- italic:
Set text to italic.
- underline:
See the list of underlining options.
- justify|center|right|left:
Horizontal alignment.
- top|vcenter|bottom|vjustify:
Vertical alignment.
- color:
See the list of available text colors.
- type:
face See the list of available type faces.
- X or sizeX:
Set the font size to integer X.
Examples:
xl setfont bold italic ~ right top "red" "arial" size14 xl font A 1 D 10 "Courier New" 10 "blue" xl font A 6 AZ 6 bold format("yyyy")
- textwrap [off]
Turn text wrapping on or off for the specified cells.
- format(<format>|off)
This setting allows specification of general, number, currency, accounting, date, short date, long date, time, percentage, fraction, scientific, text, off, or custom settings. Complex and multiple word settings must be wrapped in “”. The “off” and custom settings must use the “format(<>)” syntax; other settings may be stated simply as “general”, “number”, or otherwise.
- separator(“<separator>”|off]
Turn on or off the use of a separator for numeric cells. A custom separator may be specified, or the “(“<separator>”)” argument may be omitted to employ the default separator. The setting will be stored, but it only will be applied to cells if a format specification has been or will be given. Precision still is controlled by the G7 format command.
- Underline
Underlining may be specified in several ways.
underline [-]UnderlineSingle-Single underlining.underline =UnderlineDouble=Double underlining.underline _UnderlineSingleAccounting_Single accounting underlining.underline [ ~ ]UnderlineDoubleAccounting~Double accounting underlining.underline nUnderlineNoneNo underlining.
Available font colors include:
Font Colors
“None”
“Medium Gray”
“Aqua”
“Mint green”
“Black”
“Navy blue”
“Blue”
“Olive green”
“Cream”
“Purple”
“Dark Gray”
“Red”
“Fuchsia”
“Silver”
“Gray”
“Sky blue”
“Green”
“Teal”
“Lime green”
“White”
“Light Gray”
“Yellow”
“Maroon”
“RGB(<int1>,<int2>,<int3>)”
System fonts may be specified by employing the “TF(<system_font)” function (or “typeface(<system_font>)”) in <settings>. Multiple-word font names must be surrounded by quotation marks. For example, if the Adobe Garamond Pro font is installed, then it may be specified in the xl font command as “TF(“Adobe Garamond Pro”)”. Available standard font types include:
Font Types
“Agency FB”
“Gill Sans Ultra Bold Condensed”
“Agency FB Bold”
“Gloucester MT Extra Condensed”
“Algerian”
“Goudy Old Style”
“Arial”
“Goudy Old Style Bold”
“Arial Black”
“Goudy Old Style Italic”
“Arial Black Italic”
“Goudy Stout”
“Arial Bold”
“Haettenschweiler”
“Arial Bold Italic”
“Harlow Solid Italic”
“Arial Italic”
“Harrington”
“Arial Narrow”
“High Tower Text”
“Arial Narrow Bold”
“High Tower Text Italic”
“Arial Narrow Bold Italic”
“Impact”
“Arial Narrow Italic”
“Imprint MT Shadow”
“Arial Rounded MT Bold”
“Informal Roman”
“Arial Unicode MS”
“Jokerman”
“Baskerville Old Face”
“Juice ITC”
“Batang”
“Kristen ITC”
“Bauhaus 93”
“Kunstler Script”
“Bell MT”
“Lucida Bright”
“Bell MT Bold”
“Lucida Bright Demibold”
“Bell MT Italic”
“Lucida Bright Demibold Italic”
“Berlin Sans FB”
“Lucida Bright Italic”
“Berlin Sans FB Bold”
“Lucida Calligraphy Italic”
“Berlin Sans FB Demi Bold”
“Lucida Fax Demibold”
“Bernard MT Condensed”
“Lucida Fax Demibold Italic”
“Blackadder ITC”
“Lucida Fax Italic”
“Bodoni MT”
“Lucida Fax Regular”
“Bodoni MT Black”
“Lucida Handwriting Italic”
“Bodoni MT Black Italic”
“Lucida Sans Demibold Italic”
“Bodoni MT Bold”
“Lucida Sans Demibold Roman”
“Bodoni MT Bold Italic”
“Lucida Sans Italic”
“Bodoni MT Condensed”
“Lucida Sans Regular”
“Bodoni MT Condensed Bold”
“Lucida Sans Typewriter Bold”
“Bodoni MT Condensed Bold Italic”
“Lucida Sans Typewriter Bold Oblique”
“Bodoni MT Condensed Italic”
“Lucida Sans Typewriter Oblique”
“Bodoni MT Italic”
“Lucida Sans Typewriter Regular”
“Bodoni MT Poster Compressed”
“Magneto Bold”
“Book Antiqua”
“Maiandra GD”
“Book Antiqua Bold”
“Map Symbols”
“Book Antiqua Bold Italic”
“Matura MT Script Capitals”
“Book Antiqua Italic”
“Mistral”
“Bookman Old Style”
“Modern No. 20”
“Bookman Old Style Bold”
“Monotype Corsiva”
“Bookman Old Style Bold Italic”
“MS Mincho”
“Bookman Old Style Italic”
“MS Outlook”
“Bradley Hand ITC”
“MT Extra”
“Britannic Bold”
“Niagara Engraved”
“Broadway”
“Niagara Solid”
“Brush Script MT Italic”
“OCR A Extended”
“Californian FB”
“Old English Text MT”
“Californian FB Bold”
“Onyx”
“Californian FB Italic”
“Palace Script MT”
“Calisto MT”
“Palatino Linotype”
“Calisto MT Bold”
“Palatino Linotype Bold”
“Calisto MT Bold Italic”
“Palatino Linotype Bold Italic”
“Calisto MT Italic”
“Palatino Linotype Italic”
“Castellar”
“Papyrus”
“Centaur”
“Parchment”
“Century”
“Perpetua”
“Century Gothic”
“Perpetua Bold”
“Century Gothic Bold”
“Perpetua Bold Italic”
“Century Gothic Bold Italic”
“Perpetua Italic”
“Century Gothic Italic”
“Perpetua Titling MT Bold”
“Century Schoolbook”
“Perpetua Titling MT Light”
“Century Schoolbook Bold”
“Playbill”
“Century Schoolbook Bold Italic”
“PMingLiU”
“Century Schoolbook Italic”
“Poor Richard”
“Chiller”
“Pristina”
“Colonna MT”
“Rage Italic”
“Comic Sans MS”
“Ravie”
“Comic Sans MS Bold”
“Rockwell”
“Cooper Black”
“Rockwell Bold”
“Copperplate Gothic Bold”
“Rockwell Bold Italic”
“Copperplate Gothic Light”
“Rockwell Condensed”
“Courier New”
“Rockwell Condensed Bold”
“Courier New Bold”
“Rockwell Extra Bold”
“Courier New Bold Italic”
“Rockwell Italic”
“Courier New Italic”
“Script MT Bold”
“Curlz MT”
“Showcard Gothic”
“Edwardian Script ITC”
“SimSun”
“Elephant”
“Snap ITC”
“Elephant Italic”
“Stencil”
“Engravers MT”
“Symbol”
“Eras Bold ITC”
“Tahoma”
“Eras Demi ITC”
“Tahoma Bold”
“Eras Light ITC”
“Tempus Sans ITC”
“Eras Medium ITC”
“Times”
“Felix Titling”
“Times New Roman”
“Footlight MT Light”
“Times New Roman Bold”
“Forte”
“Times New Roman Bold Italic”
“Franklin Gothic Book”
“Times New Roman Italic”
“Franklin Gothic Book Italic”
“Trebuchet MS”
“Franklin Gothic Demi”
“Trebuchet MS Bold”
“Franklin Gothic Demi Cond”
“Trebuchet MS Bold Italic”
“Franklin Gothic Demi Italic”
“Trebuchet MS Italic”
“Franklin Gothic Heavy”
“Tw Cen MT”
“Franklin Gothic Heavy Italic”
“Tw Cen MT Bold”
“Franklin Gothic Medium”
“Tw Cen MT Bold Italic”
“Franklin Gothic Medium Cond”
“Tw Cen MT Condensed”
“Franklin Gothic Medium Italic”
“Tw Cen MT Condensed Bold”
“Freestyle Script”
“Tw Cen MT Condensed Extra Bold”
“French Script MT”
“Tw Cen MT Italic”
“Garamond”
“Verdana”
“Garamond Bold”
“Verdana Bold”
“Garamond Italic”
“Verdana Bold Italic”
“Gigi”
“Verdana Italic”
“Gill Sans MT”
“Viner Hand ITC”
“Gill Sans MT Bold”
“Vivaldi Italic”
“Gill Sans MT Bold Italic”
“Vladimir Script”
“Gill Sans MT Condensed”
“Wide Latin”
“Gill Sans MT Ext Condensed Bold”
“Wingdings”
“Gill Sans MT Italic”
“Wingdings 2”
“Gill Sans Ultra Bold”
“Wingdings 3”
The second specification reads a time series from the Excel worksheet, starting at the given location and proceeding in the direction specified, for the dates given. <direction> may be right, down, left, or up.
The third specification introduces the ability to read a range of dates and then to recover the date and its components with keywords and to read a corresponding range of data with possibly non-contiguous dates. The set of dates are stored in Excel date format, where a single date may be recovered with the %xldate keyword. The same may be recovered in G7 date format with %xlgdate, or the date components may be recovered as %xlyear, %xlquarter, %xlmonth, and %xlday. If a range of cells is specified, then the range must contain either a single row or column. Before reading a subsequent data set using this date range, the frequency should be specified with the xl setfrequency command to prevent possible ambiguity, particularly with quarterly data. Finally, data may be read with a second xl read command that employs the stored date range.
Example:
xl read A 1 date H 1 xl setfrequency 4 xl read A 2 right gdp "xldates"
- xl vecread <c(columns)> <r(rows)> <direction> <vector> <v(elements)> [<start date> [<end date>]]
G7 can read entire vectors of data for multiple years using a single command. The vecread command must be used with a VAM bank. This command reads text or data from an open Excel worksheet, where:
- Vector:
The root name of a vector stored in a vam bank. Bank letters are allowed.
- Elements:
The range of vector elements that are to be printed.
- Cols:
The spreadsheet columns for the first period of data, given as a group of letters or numbers.
- Rows:
The spreadsheet rows for the first period of data, given as a group of numbers. Either cols or rows must have a single element, and the other must have the same number of elements as contained in index.
- direction:
Either d(own) or r(ight), starting with the row or column specified with the cols and row entries. NOTE: this is the direction indexed by time, so that if data for a particular series are to be written across a row, then “right” should be specified.
- Start:
The starting date, where the date is in G7 format. If dates are not provided, the desired dates are assumed to be those of the current tdates setting.
- End:
The ending date, where the date is in G7 format.
Examples:
xl vecread c(B) r(7 9 11-18 20 21 23-29 31-43 45 47-50 52-58 60-64 66 68-77 79-153 155-157 159-236 238-268) right cr v(1-247) 2000 2008
xl print missing “N/A”
- xl replace <value>
This command sets a replacement value for missing values in the spreadsheet file. If G7 reads a cell that matches an entry set with xl missing, then the value is replaced by value. By default, this replacement value is the G7 missing value code (-0.0000001). <value> must be a number. See also the xl missing command.
Deprecated Features
