IDtUnpivotProvider.Provider

Syntax

Description

The Provider property determines table data source containing areas.

Comments

Rest properties of the interface are used to set up parameters of areas containing in data source.

Example

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.

Contents of the Data.xls file

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.