Contents of the Data.xls fileProvider: IDtProvider;
Provider: Prognoz.Platform.Interop.Dt.IDtProvider;
The Provider property determines table data source containing areas.
Rest properties of the interface are used to set up parameters of areas containing in data source.
Executing the example requires that the file system contains the C:\Data.xls file including data split into areas. Data in file is located in the sheet named Sheet 1. The file system must also contain the C:\DataTransform.txt file where data from C:\Data.xls will be exported.
Add a link to the Dt system assembly.
Sub UserProc;
Var
XlsProvider: IDtExcelProvider;
UnpivotProvider: IDtUnpivotProvider;
FixCells: Array Of Integer;
TextConsumer: IDtTextConsumer;
ProviderFields, ConsumerFields: IDtFieldDefinitions;
i, Row, Col: integer;
Field, NewField: IDtFieldDefinition;
Begin
// Create object to import data from Microsoft Excel files
XlsProvider := New DtExcelProvider.Create;
// Determine file with data
XlsProvider.File := "C:\Data.xls";
// Determine version of the Microsoft Excel driver
XlsProvider.DriverVersion := "Excel 8.0";
// Determine query for data sampling
XlsProvider.Query := "Select * From [Sheet1$]";
// Create object to work with table source split into areas
UnpivotProvider := New DtUnpivotProvider.Create;
// Set the Microsoft Excel file as table data source
UnpivotProvider.Provider := XlsProvider;
// Set parameters of the A4 area
UnpivotProvider.DataBottom := 10;
UnpivotProvider.DataRight := 9;
UnpivotProvider.DataTop := 7;
UnpivotProvider.DataWidth := 3;
// Set parameters of the A2 area
UnpivotProvider.HeadBottom := 4;
UnpivotProvider.HeadColumn := 1;
UnpivotProvider.HeadTop := 1;
// Set parameters of the A3 area
UnpivotProvider.LeftBegin := 1;
UnpivotProvider.LeftEnd := 3;
// Set fixed cells
FixCells := New Integer[2, 1];
FixCells[0, 0] := 1;
FixCells[1, 0] := 1;
UnpivotProvider.FixedCells := FixCells;
// Get fields from data source
UnpivotProvider.FieldsFromFile;
// Open data source
UnpivotProvider.Open;
// Create object to export data to text file
TextConsumer := New DtTextConsumer.Create;
// Determine output file
TextConsumer.File := "C:\DataTransform.txt";
// Set export parameters
TextConsumer.WriteHeader := True;
// Set exported fields
ProviderFields := UnpivotProvider.Fields;
ConsumerFields := TextConsumer.Fields;
For i := 0 To ProviderFields.Count - 1 Do
Field := ProviderFields.Item(i);
NewField := ConsumerFields.Add;
NewField.DataType := Field.DataType;
NewField.Name := Field.Name;
NewField.Precision := Field.Precision;
NewField.Size := Field.Size;
End For;
// Open and clear export file
TextConsumer.Open;
TextConsumer.Clear;
// Export from table data source split into areas
TextConsumer.PutProvider(UnpivotProvider);
// Get matching of the cell in output file and the cell in the source file
UnpivotProvider.ToSource(1, 0, Row, Col);
Debug.WriteLine("In output file in the cell in the row with the 1 index and " +
"in the column with the 0 index in the source file corresponds the cell in row with index "
+ Row.ToString + " and in column with index " + Col.ToString);
// Get matching of the cell in the source file and the cell in the output file
UnpivotProvider.FromSource(7, 1, Row, Col);
Debug.WriteLine("In the source file the cell in the row with the 7 index and " +
"the column with the 1 index in the output file corresponds the cell in the row with index "
+ Row.ToString + " in the column with index " + Col.ToString);
// Close the data source and exported file
UnpivotProvider.Close;
TextConsumer.Close;
End Sub UserProc;
After executing the example the data from the C:\Data.xls file will be transformed and exported in the C:\DataTransform.txt file.
The requirements and result of the Fore.NET example execution match with those in the Fore example.
Imports Prognoz.Platform.Interop.Dt;
…
Public Shared Sub UserProc();
Var
XlsProvider: IDtExcelProvider;
UnpivotProvider: IDtUnpivotProvider;
FixCells: Array[1, 2] Of Integer;
TextConsumer: IDtTextConsumer;
ProviderFields, ConsumerFields: IDtFieldDefinitions;
i, Row, Col: integer;
Field, NewField: IDtFieldDefinition;
Begin
// Create object for data import from Microsoft Excel files
XlsProvider := New DtExcelProvider.Create();
// Determine file with data
XlsProvider.File := "C:\Data.xls";
// Determine version of the Microsoft Excel driver
XlsProvider.DriverVersion := "Excel 8.0";
// Set query for data sampling
XlsProvider.Query := "Select * From [Sheet1$]";
// Create object to work with table source split into areas
UnpivotProvider := New DtUnpivotProvider.Create();
// Determine the Microsoft Excel file as table data source
UnpivotProvider.Provider := XlsProvider;
// Set parameters of the A4 area
UnpivotProvider.DataBottom := 10;
UnpivotProvider.DataRight := 9;
UnpivotProvider.DataTop := 7;
UnpivotProvider.DataWidth := 3;
// Set parameters of the A2 area
UnpivotProvider.HeadBottom := 4;
UnpivotProvider.HeadColumn := 1;
UnpivotProvider.HeadTop := 1;
// Set parameters of the A3 area
UnpivotProvider.LeftBegin := 1;
UnpivotProvider.LeftEnd := 3;
// Set fixed cells
FixCells[0, 0] := 1;
FixCells[0, 1] := 1;
UnpivotProvider.FixedCells := FixCells;
// Get fields from data source
UnpivotProvider.FieldsFromFile();
// Open data source
UnpivotProvider.Open();
// Create object to data export in text file
TextConsumer := New DtTextConsumer.Create();
// Determine source file
TextConsumer.File := "C:\DataTransform.txt";
// Set export parameters
TextConsumer.WriteHeader := True;
// Set exported fields
ProviderFields := UnpivotProvider.Fields;
ConsumerFields := TextConsumer.Fields;
For i := 0 To ProviderFields.Count - 1 Do
Field := ProviderFields.Item[i];
NewField := ConsumerFields.Add();
NewField.DataType := Field.DataType;
NewField.Name := Field.Name;
NewField.Precision := Field.Precision;
NewField.Size := Field.Size;
End For;
// Open and clear export file
TextConsumer.Open();
TextConsumer.Clear();
// Export from table data source split into areas
TextConsumer.PutProvider(UnpivotProvider);
// Get matching of the cell in output file and the cell in the source file
UnpivotProvider.ToSource(1, 0, Var Row, Var Col);
System.Diagnostics.Debug.WriteLine("In output file cell in the row with the 1 index and " +
"column with the 0 index in the source file corresponds to the row with the index "
+ Row.ToString() + " and column with the index " + Col.ToString());
// Get matching of the cell in source file and the cell in the output file
UnpivotProvider.FromSource(7, 1, Var Row, Var Col);
System.Diagnostics.Debug.WriteLine("In the source file the cell in the row with the 7 index and " +
"the column with the 1 index in the output file corresponds the cell in the row with the index "
+ Row.ToString() + " and the column with the index " + Col.ToString());
// Close data source and exported file
UnpivotProvider.Close();
TextConsumer.Close();
End Sub;
See also:
