This section describes adding an Excel DataSource with a variety of alternative ranges.
Start with an Excel file EmpInfo.xls
that consists of
Emp_Id
, Emp_Name
,
Dept_Name
and Emp_Sal
as shown in Figure 5.3, “EmpInfo.xls”.
The following procedure defines an Excel DataSource containing all the Employee details:
Before adding an Excel DataSource, the range must be defined in the Excel file.
Open the EmpInfo.xls
file.
Select the Name -> Define under the Insert menu. The Define Name dialog box pops up.
Enter name as Employee
and enter the range as follows,
in the Refers to text box.
=Sheet1!$A$1:$D$11
Here, $1 corresponds to the first cell in the file and
there are a total of ll records in this Excel file including the header.
$11 corresponds to the last cell containing the final record. The Define
Name
dialog box appears as shown in Figure 5.4, “Define Name”. Click
OK in the Define Name dialog box. Save the Excel file.
Launch Elixir Repertoire.
Choose a filesystem or folder and use the popup menu to select Add -> DataSource.
The DataSource Wizard appears. Select Excel DataSource and click Next.
The Define Excel DataSource screen appears. Enter
the name as Excel-1
.
Enter the URL for EmpInfo.xls
in the text box provided.
Alternatively, by clicking the button on the right of
the text field select the EmpInfo.xls
file from the
File Open dialog.
Enter Employee
in the Range field.
Select the First Row Header check box and click Next.
The screen appears as shown in Figure 5.5, “Completed DataSource Wizard”.
Click Infer Schema. Change the
Data Type of Emp_Id
and Emp_Sal
from Double to Integer
Click Finish in the DataSource Wizard.
The Excel Data Source is added to the repository. Click the Load Data menu.
The Excel file is loaded displaying the employee details present in the Excel sheet as shown in Figure 5.6, “Result”.
This time we will work with the file EmpDetail.xls
. This
consists of employee details in three worksheets as shown in Figure 5.7, “EmpDetail.xls”.
To get the values across all the sheets:
Before adding an Excel DataSource, the range must be defined in the Excel file.
Open Empdetail.xls
file.
Select the Name -> Define under the Insert menu. The Define Name dialog box pops up.
Enter name as EmpSheets
and enter range as follows,
in the Refers to text box:
=Sheet1:Sheet3!$A$1:$D$11
The above syntax includes the cells in sheet1, sheet3 and all the sheets between them. After setting the values, the Define Name dialog window appears as shown in Figure 5.8, “Define Name”. Click OK in the Define Name dialog box and save the Excel file.
Launch Elixir Data Designer.
Choose a filesystem or folder and use the popup menu to select Add -> DataSource.
The DataSource Wizard appears. Select Excel DataSource and click Next.
The Define Excel DataSource screen appears. Enter
the name as Excel-2
.
Enter the URL for EmpDetail.xls
in the text box provided.
Alternatively, by clicking the browse button to the right of
the text field, select the EmpDetail.xls
file from the
File Open dialog.
Enter EmpSheets
in the Range field.
Select the First Row Header check box and click Next.
Click Infer Schema as shown in Figure 5.9, “Completed DataSource Wizard”. Change
the Data Type of Emp_Id
and Emp_Sal
from Double to Integer.
Click Finish in the DataSource Wizard.
The Excel DataSource is added to the repository.
Double click the Excel-2.ds
data source. Click the
Load Data menu.
The Excel file is loaded displaying the employee details present in all the Excel sheets as shown in Figure 5.10, “Result”.
To control the behavior of Elixir Data Designer dynamically, dynamic parameters are used. The parameters are specified in place of a value or values while setting the properties of a datasource or Composite element, and the value is assigned during the generation process. The generation process may prompt for parameter entry.
Parameters are defined as name-value pairs, where the name must be unique in the scope of the parameter. Refer to Appendix A, Dynamic Parameters for further information.
If we need a different range of data to be loaded each time, then changing the ranges values in the DataSource Wizard will be a time consuming process. Dynamic parameters can be specified for the Range in the DataSource Wizard and the values are obtained when the DataSource is used.
The file EmpDet.xls
consists of employee details in two
worksheets as shown in Figure 5.11, “EmpDet.xls”.
If you want to fetch the values in all the sheets of the Excel file using different range values, use the following process:
Before adding an Excel DataSource, the ranges must be defined in the Excel file.
Open the EmpDet.xls
file.
Select Name -> Define under the Insert menu. The Define Name dialog box pops up.
Enter name as Emp1_Small
and enter the range as follows
in the Refers to text box. Then click Add.
=Sheet1!$A$1:$D$6
Enter the name as Emp1_All
and the range as follows
in the Refers to text box and click Add.
=Sheet1!A$1:D$11
Enter the name as Emp2
and the range as follows in the Refers to text box and click Add.
=Sheet2!$A$1:$D$3
Enter the name as Entire_Sheets
and the range as follows
in the Refers to text box and click Add.
After adding the columns, the dialog window
appears as shown in Figure 5.12, “Define Name”. Click Ok in
the Define Name dialog window.
=Sheet1:Sheet2!A$1:D$11
Save the EmpDet.xls
file.
Launch Elixir Repertoire.
Using the filesystem or folder popup menu, select Add -> DataSource.
The DataSource Wizard appears. Select Excel DataSource and click Next.
The Define Excel DataSource screen appears. Enter
the name as Excel-3
.
Enter the path of the EmpDet.xls
file in the field provided.
Alternatively, by clicking the browse button on the right of
the text field, select the EmpDet.xls
file from the
Open dialog.
Enter ${Range}
in the Range field.
Select the First Row Header check box and click Next.
In the screen that appears as shown in Figure 5.13, “Completed DataSource Wizard”, click Infer Schema.
Enter any range
specified above in the Dynamic Parameters dialog box that appears and click
Finish.
The schema will be inferred. Change the Data Type of Emp_Id
and Emp_Sal
to Integer.
Finally, click Finish.
Click Finish in the DataSource Wizard.
The Excel data source is added to the repository.
Double click Excel-3.ds
. Click the Load Data
menu.
The Dynamic parameter dialog box pops up as shown in Figure 5.14, “Dynamic Parameters”. Enter one of the range names previously defined (Emp1_Small, Emp1_All etc.). Click Finish.
The Emp1_Small
range is used to fetch only the selected
part of the data from sheet1 of the Excel file as shown
in Figure 5.15, “Emp1_Small Result”. The Emp1_All
range is used to
fetch all
the data in Excel file sheet1 as shown in Figure 5.16, “Emp1_All Result”.
Similarly, the Emp2
range is used to fetch only
the selected part of the data from sheet2 of the Excel file.
The Entire_Sheets
range is used to fetch all the
records from both the sheets.