XLSGate Functions Documentation

Following functions are part of XLSGate Library, most of Example code is written in pseudocode language, because XLSGate is a library which can be used universally from different Languages such as MQL, Easylanguage, C#, Ninjascript etc. It would be hard to make same example in many different languages, that is main reason why we are using pseudocode here.

Core Functions

bool ExcelInit(string Alias)

This is first function you have to call to get started. This function is establishing and initializing all variables. All functions will return either an error or return false if you miss to call this function. If this function returns false, you might have XLSApp not running. XLSApp has to run before you call this function. Read XLSApp section for more details. You have to mention an Alias, this Alias is the name of your Environment. This information will be used later, Alias should have minimum 3 characters and it should be unique in all your source code.

bool ExcelStart(string Filename)

This is second function you have to call to work with Excel. This function Starts Excel Application if its not started yet. This function should return true.

If you keep Filename empty string "", then new Workbook will be opened, if you specify a Filename, defined File will be loaded. Depending on client you might need to \\ in path. For example “c:\temp\table1.xls” might be changed as “c:\\temp\\table1.xls” if you have trouble loading file.

              // Pseudocode
              if (ExcelInit("ea1"))
                 if (ExcelStart(""))
                    Print("XLSApp is started successfully");

bool ExcelClose()

Use this function to close Excel Application. This function will close complete Workbook without saving. We expect true as return value

int ExcelGateVersion()

Returns Version information from XLSGate. Use this information to determine between different releases

bool ExcelPRO()

This function returns true if you run a PROFESSIONAL version of XLSgate, otherwise false

string ExcelVersion()

Returns Office version of Excel. For example you get “2013” as string if you run Microsoft Office 2013. You cannot call this function before ExcelStart has been called.

    // Pseudocode
    if (ExcelInit("ea1") && ExcelStart(""))
        {
        Print("XLSApp is started successfully");
        Print("Your Office version is : "+ExcelVersion());
        }
        else
        Print("Error by XLSgate init");

string ExcelAppPath()

Returns full path to XLSApp Application. You will need to have runned XLSApp at least once to be able to query this information.

bool ExcelRecalculate()

Recalculates active Worksheet. Its same as pressing F9 from user. Use this function only if you know the functionality.

bool ExcelSave(string Filename)

Saves Workbook under given Filename. Make sure path exists please.

bool ExcelWindowState(int State)

Sets Excel windows state. States: 0 = Normal, -1 = Minimized, +1 = Maximized. Please use this slow function carefully. You need to have Excel started before being able to use this function

Sheet Management

int ExcelSheetAdd(string Sheetname)

Adds new Excel Sheet to current Workbook. This call will change default Sheet to given Sheet automatically. It returns 0, the index of that Sheet and switch to that Sheet.

    // Pseudocode
    string Sheetname="Output1";
    if (ExcelSheetAdd(Sheetname)>0)
        Print(Sheetname+" added successfully.");
        else
        Print("Error by adding '"+Sheetname+"' as sheet. Check XLSApp");

bool ExcelSheetSelect(int Sheetindex)
bool ExcelSheetSelectByName(string Sheetname)

Use one of these functions to switch to Sheet, ExcelSheetSelect switches by Index, and second function switches by name (case-sensitive).Warning If you change the order of Sheets in Excel yourself, the index number will also change. We recommend you to use Sheetname to switch. If you rename Sheet manually in Excel, you will also have to change Sheetname to new name.

int ExcelSheetCount()
string ExcelSheetNameByIndex(int Sheetindex)

These functions are used to enumerate available Sheets on current Workbook. Important: Sheetindex starts with 1. First Sheet has always 1 as index.

    // List all available Sheets on current Workbook
    for(int i=1;i<ExcelSheetCount();i++)
        Print("Sheet #"+i+" = '"+ExcelSheetNameByIndex(i)+"'");

bool ExcelSheetRename(int Sheetindex,string NewName)

This function renames Sheet by Index. Attention: index starts with 1

Style & Color

int ExcelColor(string Colorname)

Returns RGB integer value of a given color. All color functions in other methods accept RGB Integer values. Color names can be found here. If you pass an unknown color, function will return -1 value. Colorname is case-insensitive

    // RGB Color convertion
    Print("RGB('Red') = "+ExcelColor("red")); // Returns 255
    Print("RGB('Blue') = "+ExcelColor("bLue")); // Returns 16711680
    Print("RGB('xlsgate') = "+ExcelColor("xlsgate")); // Returns -1

Many clients (trading apps or other software) support colors in rgb format. You should also use their color constants. This function is there to make your life easier. You could also use integer value of RED (255) to define Red, but this function makes things easier.

bool ExcelStyleColor(string Address,int Backcolor,int Forecolor)

Defines Backcolor or/and Forecolor of given Address.

    // Define Captions
    ExcelStyleColor("C2",ExcelColor("Blue"),ExcelColor("White"));
    ExcelStyleColor("D2",ExcelColor("Red"),ExcelColor("White"));
    ExcelStyleColor("E2",ExcelColor("Green"),ExcelColor("White"));
    ExcelStyleColor("F2",-1,ExcelColor("Red")); // skip Background
    ExcelStyleColor("G2",ExcelColor("Red"),-1); // skip Foreground

You can pass -1 as parameter if you want to skip setting color.

bool ExcelStyleFont(string Address,int Fontsize,bool Bold,int Backcolor,int Forecolor)

Defines Fontsize, Boldness, Backcolor and Forecolor of given Address.

    // Define Captions
    ExcelStyleFont("C2",22,true,ExcelColor("Blue"),ExcelColor("White"));
    ExcelStyleFont("D2",22,true,ExcelColor("Red"),ExcelColor("White"));
    ExcelStyleFont("E2",22,true,ExcelColor("Green"),ExcelColor("White"));
    ExcelStyleFont("F2",18,false,-1,-1); // just set Font
    ExcelStyleFont("G2",18,false,ExcelColor("Red"),-1); // skip Foreground

bool ExcelBorderAround(string Address,int Width,int Bordercolor,int Style)

Draws or Removes border around given Address. Width = 0 is used to Remove the border. Width between 1..4 is used to draw a border, Bordercolor is ExcelColor compatible, Styles are:

• 0 : Continious
• 1 : Dash
• 2 : DashDot
• 3 : DashDotDot
• 4 : Dot
• 5 : Double

This is slow function. Dont use it very excessive.

// Draw 3 borders
ExcelBorderAround("H5:J10",1,ExcelColor("Red"),0);
ExcelBorderAround("D3:J10",1,ExcelColor("Green"),0);
ExcelBorderAround("K3:J10",1,ExcelColor("Blue"),3);
// Remove them
ExcelBorderAround("H5:J10",0,0,0);
ExcelBorderAround("D3:J10",0,0,0);
ExcelBorderAround("K3:J10",0,0,0);

Input & Output

bool ExcelSetValue(string Address,double Value)
bool ExcelSetString(string Address,string Value)

Function sets value of given Address. If you want to define a formula, you can use ExcelSetString.

    ExcelSetValue("B1",8);
    ExcelSetValue("B2",5);
    ExcelSetString("A3","Sum:");
    ExcelSetString("A4","Mul:");
    ExcelSetString("B3","=A1+A2");
    ExcelSetString("B4","=A1*A2"); // Referencing active Sheet
    ExcelSetString("Sheet2!A1","2013 Report"); // Referencing other Sheet
    ExcelSetString("Sheet3!A1","2014 Report"); // Referencing other Sheet

Under certain conditions this function might return false. It indicates an error. If you try to export large number of data, use Array functions, they are 10000x faster and cause less errors.

ExcelSet functions also support Ranges instead of cell. So ExcelSetValue(“B4:D20”,9) will set whole range between B4->D20 to 9. Same applies to ExcelSetString(“B4:D20″,”ok”). You can even set complete column to specific value: ExcelSetValue(“B:B”,0) will put 0 to complete “B” column.

    ExcelSetValue("B1",8);
    ExcelSetValue("B:B",5); // Set complete B column to value 5
    ExcelSetString("C4:D30","void"); // Set C4:D30 Range to "void"
    ExcelSetString("A1:A20","=rand()"); // Put rand() excel function from A1:A20
    ExcelSetValue("Sheet2!A1:D30",9.99); // Referencing other Sheet

double ExcelGetValue(string Address)
string ExcelGetString(string Address)

Returns value of given address

    double B1 = ExcelGetValue("B1");
    double B2 = ExcelGetValue("B2");
    double Sum = B1 + B2;
    double Mult = B1 * B2;
    // Reading from other Sheet
    double Sheet2A1 = ExcelGetValue("Sheet2!A1");
    double Sheet2A2 = ExcelGetValue("Sheet2!A2");
    double Sheet2Sum = Sheet2A1 + Sheet2A2;

Under certain conditions this function might return false. It indicates an error.

bool ExcelSetFromArray(string Address,double[,] data,int count, int count1, int count2)
bool ExcelSetFromArrayV(string Address,double [] data,int count)
bool ExcelSetFromArrayH(string Address,double [] data,int count)

Please refer blog post for details

bool ExcelSetFromParsedV(string Address,string Values)
bool ExcelSetFromParsedH(string Address,string Values)

We have implemented these 2 functions for compatibility reasons. These functions are especially made for Platforms such as Tradestation, Multicharts. They dont support passing and working with Arrays, especially not with external DLL Files. We have tried to overcome these limitations. Platforms they support Arrays such as Metatrader, Multicharts.NET, C#, Ninjatrader should better use ExcelSetFromArray,ExcelSetFromArrayV,ExcelSetFromArrayH functions since they are much faster.

ExcelSetFromParsed function simply parses all values from “Values” string seperated with “;” and sends these values to Excel (V)ertically or (H)orizontally. Only numerical values are supported. Please do _not_ Address a range, the Address must be a single Cell. Several samples:

// Put 10 numbers horizontally
ExcelSetFromParsedH("H5","1;2;3;4;5;6;7;8;9;10");
// Put 10 numbers vertically
ExcelSetFromParsedV("H5","1;2;3;4;5;6;7;8;9;10");

Single ExcelSet* functions are slow! You need x miliseconds to publish 1 single numerical value to excel. Imagine you have 100 values to export, you would need to spend 100*x miliseconds. ExcelSetFromParsed* and ExcelSetFromArray* functions can do same within 1*x miliseconds. So depending on number of your numerical data you can publish them to excel with single call. Please also read this post

bool ExcelSetFromParsedAuto(string Address,string ValuesToParse)

This function is a more automated version of ExcelSetFromParsedV/H functions. This function automatically parses the second parameters by \t (Tab) for columns and \n (NewLine) for rows. The ExcelSetFromParsedV/H functions support only numbers, this function supports numbers, strings and formulas!

string DemoParsedText="\t1\t2\t3\t0\tFIRST ROW\n" +
  "\t6.7\t7.7\t8.8\t9.9\tSECOND ROW\n" +
  "SUM()\t=B1+B2\t=C1+C2\t=D1+D2\t=E1+E2\tSUMMARY ROW";
ExcelSetFromParsedAuto("A1",DemoParsedText);

Here is the output of this operation on excel:

Use this function to speedup operations. This function is compatible with all platforms. Basic idea is that you seperate columns with \t (Tab) and rows with \n chars. XLSGate Demo Application has a button where you can play with this function. Moreoever we have a post special to explain this function here

Button Management

Important, please read : You can add Buttons to Excel sheets and interact with them. First you have to call ExcelButtonAdd and define a Name for that button. Then you can keep calling ExcelButtonIsClicked(string Buttonname) function to determine if Button has been clicked from User. To be able to determine if the button has been clicked by User you need to call ExcelButtonIsClicked regularly. Some clients might not have this feature because some of them are running offline or at end-of-bar based. If you are Metatrader user, then you should place the query into OnTick(), in Multicharts/Tradestation you can capture this event from an indicator (because indicator will be evaluated on every price change). Simply: without being able to call ExcelButtonIsClicked at least every second, you will not be able to work with Buttons.

You can access buttons from other applications. For example if Tradestation has added a button, this button can be used from Metatrader.

bool ExcelButtonAdd(string Name, string Caption, int X, int Y, int Width, int Height)

This function adds the Button to active Sheet with given X,Y Coordinates and Width, Height. Caption is defined as second parameter. Name is important, every Button needs a free defined Name. You must make sure it returns true, otherwise check XLSApp for error message. You cannot add 2 buttons with same Buttonname. Even if this function returns false, this does not mean that button is not added. Buttons are persistent on Sheets. Even if you restart your client and initialize everything, that button will keep on Sheet and you can use other functions to access.

Video demonstration:

bool ExcelButtonColor(string Buttonname, int Backcolor,int Forecolor)

This function can set Back- and Forecolor of Button, Buttonname is the name from ExcelButtonAdd. If you want to skip setting one of the colors simply put -1

ExcelButtonColor("btn1",ExcelColor("Red"),ExcelColor("White"));
ExcelButtonColor("btn2",-1,ExcelColor("White")); // Don´t Background color
ExcelButtonColor("btn3",ExcelColor("Red"),-1); // Don´t set Foreground color

bool ExcelButtonCaption(string Buttonname, string NewCaption)

Sets Caption of Buttonname

bool ExcelButtonIsClicked(string Buttonname)

Determines if the button has been clicked from user. It is possible to ask Clicked Event across different Applications. So you could theoretically create the button from application A and ask clicked Event from application B and C. Please read information above near “Button Management” to understand requirements of clicked event capturing. Not every application is able to capture this event. Without this event, button is useless. Also check “Simple Button Management” video demonstration from ExcelButtonAdd to understand ExcelButtonIsClicked.

Address Functions

These functions are designed to convert between Address and Coordinates.

bool ExcelA2C(string Address,int& Col, int &Row)

Converts Address -> Coordinate. Returns true if Address is valid. Col and Row variables are references

    int X,Y;
    if (ExcelA2C("AB24",X,Y))
    Print("Address('Y24') coordinates : X = "+X+" , Y = "+Y);
    // Returns X = 28, Y = 24

string ExcelC2A(int Col, int Row)

Converts Coordinate -> Address. Col and Row begin by 1 and not 0!

string ExcelColRowAdd(string Address,int ColCount, int RowCount)string ExcelRowAdd(string Address,int RowCount)>string ExcelColAdd(string Address,int ColCount)

Adds Col- and RowCount to given address. These functions are especially useful if you export data and need to add row,col to address.

    Print(ExcelColRowAdd("B8",3,0));  // Returns "E8"
    Print(ExcelColRowAdd("B8",0,3));  // Returns "B11"
    Print(ExcelColRowAdd("B8",3,3));  // Returns "E11"
    Print(ExcelColRowAdd("'First Sheet'!B8",3,3));  // Returns "E11"
    Print(ExcelColAdd("B8",3));       // Returns "E8"
    Print(ExcelColAdd("Sheet3!B8",3));       // Returns "E8"
    Print(ExcelColAdd("'Sheet3'!B8",3));       // Returns "E8"
    Print(ExcelRowAdd("B8",3));       // Returns "B11"

Other Functions

bool ExcelOutput(string Line)

Outputs Line to XLSApp. You can use XLSApp as debug instance using this function. Your output will be displayed in different color to distinguish between XLSApp Logs and your Logs.