One of the important functionalities of Microsoft Excel is the capability of connecting to outside data sources and retrieve data into an Excel file. SpreadSheetWEB provides the same functionality through custom worksheet functions of SQLOpen and SQLGet. SQLOpen function creates a dynamic connection to the desired database server or file while SQLGet uses the connection created by SQLOpen to retrieve the data.
You can use SQLOpen to gather data from
SpreadSheetWEB SQLOpen and SQLGet provide us with this functionality. SQLOpen and SQLGet are custom worksheet functions. SQLOpen function creates a dynamic connection to the desired database server or file while SQLGet uses the connection created by SQLOpen to retrieve the data.
The Function headers are
Connection Number is the number returned by SQLOpen. It is best to use a cell reference to where SQLOpen resides because the number returned by SQLOpen is dynamic.
Row Number is the index to the row of the record set that is requested. If omitted all rows are returned as an array.
Column Number is the index to the column of the record set that is requested. If omitted all columns are returned as an array. If both Row Number and Column Number are omitted, whole record set will be returned as an array.
Include Header Columns indicates if name of the columns should be included with the returned results. Note that all indexes will shift one depending on the value of this parameter.
Return Extended Error Info indicates if the function should return a string describing the reason of the error if the function is not successful. Default behavior of this function is to return #VALUE! Error if the function is not successful.
If connection number is < 1 or > maximum valid connection number returned by SQLOpen #VALUE! is returned or "Illegal parameter. Connection number, column number or row number is not valid." If the record set of the specified connection number doesn't exist #VALUE! Is returned or "Either table is empty or table does not exist." If a row number or column number is specified which is < 0 or > record set/columns #VALUE! is returned or "Illegal parameter. Connection number, column number or row number is not valid." The error is returned for every cell if row number or column number is not specified and an array is returned.
As stated before you can use SQLOpen to connect to any ODBC data source
Examples are:
=SQLOpen("SELECT * FROM Customers WHERE Country ='USA'", "Driver={SQL Server};Server=local;Database=Northwind;Trusted_Connection=yes")
=SQLOpen("SELECT ContactName ContactTitle Adress FROM Customers", "Driver={SQL Server};Server=local;Database=Northwind;Trusted_Connection=yes")
=SQLOpen("SELECT * FROM Customers", "Driver={SQL Server};Server=local;Database=Northwind;Trusted_Connection=yes")
=SQLOpen("SELECT * FROM Customers", "DSN=Northwind")
=SQLOpen ("SELECT * FROM Customers", ""Provider=sqloledb; Data Source=local; Initial Catalog=Northwind; Integrated Security=SSPI"")
Using Username and Password
=SQLOpen ("SELECT * FROM Customers", ""Provider=sqloledb; Data Source=local; Initial Catalog=Northwind; UserId = myUserName; Password=myPassword"")
=SQLOpen ("SELECT TOP 100 [Au_ID] , [Author] , [Year Born] FROM Authors”, Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb")
SQLOpen (“SELECT TOP 100 [Au_ID] FROM Authors”, Driver= {Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc, csv, tab, txt ;)
SQLOpen (“SELECT TOP 100 [AU_ID] FROM Authors”, Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c: \txtFilesFolder\;Extended Properties="text; HDR=Yes; FMT=Delimited";)
SQLOpen (“SELECT TOP 100 [AU_ID] FROM Authors”, Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c: \txtFilesFolder\;Extended Properties="text; HDR=Yes; FMT=Fixed";)
=SQLOPEN ("SELECT * FROM [Sales$]", "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & A1 & "; Extended Properties=Excel 8.0 ;")
Where [A1] in the excel file has the address you would like to connect e.g. C:\MyExcelFile.xls
Where [Sales$] is the workbook sheet you would like to connect
If the value returned by SQLOpen = 0, this means either the Sql query has errors or the database location is not found.
Once a database connection is established and a query is executed, SQLGet function can be used to parse the results into individual cells
=SQLGet (A1)
Returns the whole record set as an array. This assumes that there is a SQLOpen statement in cell A1.
=SQLGet (A1, 1, 1)
Returns only a single result which is the upper left corner. This assumes that there is a SQLOpen statement in cell A1.
=SQLGet (A1, 2, 1)
Returns the second column with column including column names. This assumes that there is a SQLOpen statement in cell A1.
To see all the columns from the database you could also use feature Microsoft Excel provides known as Arrays.
Example
To see all the records from employee table assuming there is a SQLOpen Statement in CELL A1 we can write a SQLGet Statement such as
{SQLGet (A1, ,,1)}
Please note: To see all the columns make sure you select all the cells which will be filled by the query in the excel file also make sure after writing the SQLGet Statement you need to press cntrl+Shift+Enter in order to create excel arrays.
Let’s say we have a table where we store Total Sales from Orders Placed by the customers to get the total from all the sales we could use the following SQLGet Statement assuming Cell A1 has our SQLOpen Statement.
Example
=SUM (SQLGet (A1))
The above statement will return sum of the A1 column as passed in the SQLOpen Statement as Sql query.
You also have the ability to embed the SQLGet Statements in to already existing Excel Formulas
Example
Lets say you want to calculate by mixing some values from the database with already present values in the excel file for e.g. in Cell B and you would like to have a total of CELLB1 and some value from the database in CELLC1
We can write such a statement in CellC1 assuming there is a SQLOpen statement in CELLA1
=Sum (B1+SQLGet (A1, 2, 1, TRUE))
At run time the SQLGet Statement will be filled by the value you queried through Sql query and available to your application for necessary processing.
If you want to access your data which is saved to the online database in the control panel, you need a way to tell where your database is and which table you would like to connect to get the information.
SpreadsheetWEBInternalConnection can be written in a cell as a formula for e.g. =SpreadsheetWEBInternalConnection in CELL A1 of the worksheet, what this variable does is it will automatically locate the database server for your application. So at this point we have automatic information for our database location but we still need to connect to the table and retrieve the information, In order to do that we use the Application ID’s please look at the screen shot below.
The Application ID here is: 2675e39b-afaf-411e-a1af-98580c44f484. This is the table information we need to connect to our saved data in the control panel.
Now that we have both information we can try to connect to our application using this format, assuming there is an Application Called “Customer Satisfaction Feed Back Form 01” and we have saved all the input retrieved from our users which can be found in the application tab in the control panel.
To connect to this data we need a cell reference say cell A1 which has a Statement as for e.g.
=SpreadsheetWEBInternalConnection
And an Application ID as Table which is in this example: 2675e39b-afaf-411e-a1af-98580c44f484.
We can construct our SQLOpen Statement which would bring all the records from the table as
SQLOPEN (“SELECT * FROM [2675e39b-afaf-411e-a1af-98580c44f484], A1) Please note: The A1 is actually a cell reference in Excel file with the following statement =SpreadsheetWEBInternalConnection