|
|
Connecting to Excel |
|
|
|
|
|
||
|
Connecting to Excel |
|
|
|
|
|
|
Connecting to Excel
|
|
|
Connecting to Excel |
|
|
|
|
|
||
|
Connecting to Excel |
|
|
|
|
|
|
Creating a Web Service that connects to an Excel spreadsheet can be quite useful if used in conjunction with Crystal Xcelsius dashboards.
| • | Set up a connection to your Excel Spreadsheet using the Connection String Hints, you can use OLEDB or ODBC. |
| • | Define the Datasource as the path to your Excel Spreadsheet. |
| • | Next create a new Query. |
When you are creating a Query using an Excel connection , you will not be able to use the Simple Query Wizard if you leave the worksheet named as the default name(see Using Named Ranges for a workaround).This is because the Table returned from the spreadsheet will have a non-standard character in the name.If left as the default sheet name , it will be returned as Sheet1$ (see below).This will cause an error if you select Sheet1$.
To get around this problem you need to enter the SQL string manually as follows enclosing the worksheet name in square brackets.
SELECT ~column1~,~column2~ From [Sheet1$]
It's worth noting here as well that the Excel spreadsheet you are working with should have clearly marked column titles, these are used to identify the columns in DWS.
In the example shown the column 'Product' is selected from the Excel spreadsheet.
The test button will then return the values held in the Excel spreadsheet.
Values returned by DWS. Values in Excel spreadsheet.
Limitations.
There are however limitations in using Excel , the column names can only be defined in Row 1 of the Excel spreadsheet. If you entered for example Product in Row 9 , the DWS specified above will not work, the column name must come from Row 1. The way around this is to use Named Ranges in Excel.Named Ranges will be seen as Table names by the Simple Query Wizard ,so take care not to use special characters in the name.
Using Named Ranges
When in Excel you can highlight an area of your Worksheet and give that area a name (see below).
Here an area of the spreadsheet containing the Product Sales has been highlighted and given a name of 'ProductSales'. once this has been done, save the Excel spreadsheet and the Named Range will now be available to use, along with the column names defined in row 1 of the Named Range.
Use the Simple Query Wizard to select the Table (Named Range) , the Column names will also be available for selection.
The Named Range appears in the list of Tables using the Simple Query Wizard.
Columns as defined in Row 1 of Named Range.