xamPivotGrid を Excel スプレッドシートにバインド

    private void LoadExcelFileData(object sender, RoutedEventArgs e)
    {
        OpenFileDialog openFileDialog = new OpenFileDialog();
        DialogResult showDialog = openFileDialog.ShowDialog();
        if (showDialog == DialogResult.OK)
        {
            Stream stream = openFileDialog.OpenFile();
            FlatDataSource dataSource = CreateDataSource(stream);
            this.pivotGrid.DataSource = dataSource;
            this.dataSelector.DataSource = dataSource;
            stream.Close();
        }
    }

    Private Sub LoadExcelFileData( _
                    ByVal sender As Object, _
                    ByVal e As RoutedEventArgs)
        Dim openFileDialog As New OpenFileDialog()
        Dim showDialog As DialogResult = openFileDialog.ShowDialog()
        If showDialog = DialogResult.OK Then
            Dim stream As Stream = openFileDialog.OpenFile()
            Dim dataSource As FlatDataSource = CreateDataSource(stream)
            Me.pivotGrid.DataSource = dataSource
            Me.dataSelector.DataSource = dataSource
            stream.Close()
        End If
    End Sub

     private void LoadExcelFileData(object sender, RoutedEventArgs e)
     {
         Stream stream =
			new FileStream("Path\\to\\File\\DataToAnalyze.xls", FileMode.Open);
         FlatDataSource dataSource = CreateDataSource(stream);
         this.pivotGrid.DataSource = dataSource;
         this.dataSelector.DataSource = dataSource;
         stream.Close();
     }

    Private Sub LoadExcelFileData( _
                    ByVal sender As Object, _
                    ByVal e As RoutedEventArgs)
        Dim stream As Stream = New FileStream("Path\to\File\DataToAnalyze.xls", FileMode.Open)
        Dim dataSource As FlatDataSource = CreateDataSource(stream)
        Me.pivotGrid.DataSource = dataSource
        Me.dataSelector.DataSource = dataSource
        stream.Close()
    End Sub

        private FlatDataSource CreateDataSource(Stream fileStream)
        {
            FlatDataSource flatDataSource = new FlatDataSource();
            ExcelDataConnectionSettings excelDataConnectionSettings =
                new ExcelDataConnectionSettings
                {
                    FileStream = fileStream,
                    GeneratedTypeName = "ExcelData",
                    WorksheetName = "Sheet1"
                };
            string excelStringFormat = "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)";
            string systemValueFormat = "{0:C3}";
            ExcelDataConnectionSettings.AddExcelCellFormatMapping(
				excelStringFormat, systemValueFormat);
            flatDataSource.Cube = DataSourceBase.GenerateInitialCube("ExcelData");
            //以下の 3 行は
            //行と列の階層を選択します
            //メジャーも選択します。自身の名前を使用する
            //必要があります
            flatDataSource.Rows =
               DataSourceBase.GenerateInitialItems("[Region].[Region]");
            flatDataSource.Columns =
               DataSourceBase.GenerateInitialItems("[Date].[Date]");
            flatDataSource.Measures =
               DataSourceBase.GenerateInitialItems("Units");
            //これらをスキップしてデータ セレクターを使用することも可能です
            flatDataSource.ConnectionSettings = excelDataConnectionSettings;
            flatDataSource.CubesSettings.Add(
                new CubeMetadata
                {
                    DataTypeFullName = "ExcelData",
                    DisplayName = "Sheet1 Data"
                });
            HierarchyDescriptor stringDataDescriptor =
            new HierarchyDescriptor
                {
                    AppliesToPropertiesOfType = typeof(string)
                };
            stringDataDescriptor.AddLevel<string>(s => "All","All values");
            stringDataDescriptor.AddLevel<string>(s => s, "Members");
            HierarchyDescriptor dateTimeDescriptor =
            new HierarchyDescriptor
                {
                    AppliesToPropertiesOfType = typeof(DateTime)
                };
            dateTimeDescriptor.AddLevel<DateTime>(date => "All Dates", "All Values");
            dateTimeDescriptor.AddLevel<DateTime>(date => date.Year, "Years");
            dateTimeDescriptor.AddLevel<DateTime>(date => date.Date, "Members");
            flatDataSource.HierarchyDescriptors.Add(stringDataDescriptor);
            flatDataSource.HierarchyDescriptors.Add(dateTimeDescriptor);
            return flatDataSource;
        }

    Private Function CreateDataSource(ByVal fileStream As Stream) As FlatDataSource
        Dim flatDataSource As New FlatDataSource()
        Dim excelDataConnectionSettings As _
            New ExcelDataConnectionSettings()
        excelDataConnectionSettings.FileStream = fileStream
        excelDataConnectionSettings.GeneratedTypeName = "ExcelData"
        excelDataConnectionSettings.WorksheetName = "Sheet1"
        Dim excelStringFormat As String = _
            """$""#,##0.00_);[Red]\(""$""#,##0.00\)"
        Dim systemValueFormat As String = "{0:C3}"
        ExcelDataConnectionSettings.AddExcelCellFormatMapping( _
            excelStringFormat, systemValueFormat)
        flatDataSource.Cube = _
            DataSourceBase.GenerateInitialCube("ExcelData")
        '以下の 3 行は
        '行と列の階層を選択します
        'メジャーも選択します。自身の名前を使用する
        '必要があります
        flatDataSource.Rows = _
            DataSourceBase.GenerateInitialItems("[Region].[Region]")
        flatDataSource.Columns = _
            DataSourceBase.GenerateInitialItems("[Date].[Date]")
        flatDataSource.Measures = _
            DataSourceBase.GenerateInitialItems("Units")
        'これらをスキップしてデータ セレクターを使用することも可能です
        flatDataSource.ConnectionSettings = excelDataConnectionSettings
        Dim cubeMetadata As New CubeMetadata()
        cubeMetadata.DataTypeFullName = "ExcelData"
        cubeMetadata.DisplayName = "Sheet1 Data"
        flatDataSource.CubesSettings.Add(cubeMetadata)
        Dim stringDataDescriptor As New HierarchyDescriptor()
        stringDataDescriptor.AppliesToPropertiesOfType = GetType(String)
        stringDataDescriptor.AddLevel( _
                Of String)(Function(s) "All", "All values")
        stringDataDescriptor.AddLevel( _
                Of String)(Function(s) s, "Members")
        Dim dateTimeDescriptor As New HierarchyDescriptor()
        dateTimeDescriptor.AppliesToPropertiesOfType = GetType(DateTime)
        dateTimeDescriptor.AddLevel( _
                Of DateTime)(Function(d) "All Dates", "All Values")
        dateTimeDescriptor.AddLevel( _
                Of DateTime)(Function(d) d.Year, "Years")
        dateTimeDescriptor.AddLevel( _
                Of DateTime)(Function(d) d.Date, "Members")
        flatDataSource.HierarchyDescriptors.Add(stringDataDescriptor)
        flatDataSource.HierarchyDescriptors.Add(dateTimeDescriptor)
        Return flatDataSource
    End Function