Readdy Write  
0,00 €
Your View Money
Views: Count
Self 20% 0
Your Content 60% 0

Users by Links 0
u1*(Content+Views) 10% 0
Follow-Follower 0
s2*(Income) 5% 0

Count
Followers 0
Login Register as User

Word Addin: Lade Excel Datei in WPF DatenGrid

31.10.2018 (👁25622)


 

Die folgende Code-Anleitung zeigt, wie man in einem Word-Addin Daten aus einer Excel Datei schnell als Übersicht in ein WPF DataGrid laden kann.

Der Trick dabei ist, dass ein Windows WPF Control sehr schnell und modern ist.

Die Code Dateien in C#, XAML und Winforms können in eigene Anwendung eingebettet werden und die Dateien sind im Dateianhang zum Download als Beispiel geladen.

Betrifft:

Word Add-In , Office Addin Excel Daten laden, DataGrid DatenGrid, DataGridView

Datengrid mit DataSource laden und binden

Zudem ist ein async Task WPF.DoEvents zum Aktualisieren der Anzeige eingebaut

Enthält:

C# Code zur Erstellung und Laden eines WPF DataGrid mit Daten aus Excel

Start-Code aus Ribbonbar des WPF Windows

XAML Code des DataGrids und WPF Controls in VSTO Office Add-in

Zip.Datei des Projekts

 

C# Code Datei

Datei: FormExcel_Data.xaml.cs

using System;

using System.Data;

using System.Threading;

using System.Threading.Tasks;

using System.Windows;

using System.Windows.Controls;

using System.Windows.Threading;

using Excel = Microsoft.Office.Interop.Excel;

 

 

namespace addin_WPF_DataGrid_load_Excel

{

    //----------------------------------< Window >----------------------------------

    public partial class FormExcel_Data : Window

    {

        //----------------------------------< Class >----------------------------------

        //< variables >

        private DataGrid _dataGrid;

        private ProgressBar _progressbar;

        private Excel.Application _excel_App;

        private Excel.Worksheet _worksheet;

        private Excel.Workbook _workbook;

 

 

        //</ variables >

 

        //< leeres Delegate erstellen > 

        private delegate void DoEvents_EmptyDelegate();

        //</ leeres Delegate erstellen > 

 

        #region Region: Form

        //===================< Form >===================

        public FormExcel_Data()

        {

            InitializeComponent();

            _progressbar = ctlProgress;

            _dataGrid = ctlDataGrid;

        }

 

        private void Window_Loaded(object sender, RoutedEventArgs e)

        {

            //------------< Window_Loaded() >------------

            _progressbar.Value = 0;

            _worksheet = open_Excel_File() as Excel.Worksheet ;

            if (_worksheet == nullreturn;

            read_Excel_File_into_DataGridView();

            //------------</ Window_Loaded() >------------

        }

 

        //===================</ Form >===================

        #endregion /Region: Form

 

 

 

 

        #region --Methods Excel--

        //===================< Methods Excel >===================

        public Excel.Worksheet open_Excel_File()

        {

            //-----------------< open_Excel_File() >-----------------

            //< init >

            string sFilename = Settings1.Default.Excel_Filename;

            if (sFilename.IndexOf(":") < 0)

            {

                string sWordPath = Globals.ThisAddIn.Application.ActiveDocument.Path;

                sFilename = sWordPath + "\\" + sFilename;

            }

            //</ init >

            lblStatus.Content = "load " + sFilename + "..";

            _excel_App = new Microsoft.Office.Interop.Excel.Application();

            try

            {

                //----< open_Excel >----

                _workbook = _excel_App.Workbooks.Open(sFilename, UpdateLinks: false, ReadOnly: true, AddToMru: false);

                //----</ open_Excel >----

                return _workbook.Worksheets[1];

                

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

                return null;

            }

            

            //-----------------</ open_Excel_File() >-----------------

        }

 

        public async void read_Excel_File_into_DataGridView()

        {

            //----------------< read_Excel_File_into_DataGridView() >------------

            Excel.Range usedRange = _worksheet.UsedRange;

            

            //*fast Excel-Read: 

            //< create 2D Array >

            //*from excel with cell-content-object

            object[,] values = usedRange.Value2;

            _progressbar.Maximum = usedRange.Columns.Count;

 

            int nColumnsMax = 0;

            if (usedRange.Rows.Count > 0)

            {

                //< build datasouce >

                DataTable dataTable = new DataTable();

                //</ build datasouce >

 

                nColumnsMax = usedRange.Columns.Count;

                //----< Read_Header >----

                for (int iColumn = 1; iColumn <= nColumnsMax; iColumn++)

                {

                    string sValue = Convert.ToString(values[1, iColumn]);

                    if (sValue == "" || sValue == nullbreak;

                    dataTable.Columns.Add(sValue);

                    

                }

                //----</ Read_Header > ----

 

                //----< Read_DataRows >----

                

                for (int iRow = 2; iRow <= usedRange.Rows.Count; iRow++)

                {

                    if (iRow > 4000) break;

                    if(iRow % 50==0)

                    { 

                        _progressbar.Value = iRow;

                        lblStatus.Content = iRow + "/" + usedRange.Rows.Count;

                        await DoEvents();

 

                    }

                    //_progressbar.UpdateLayout();

                    //_progressbar.Dispatcher.Invoke(() => _progressbar.Value = iRow);

 

                    await Task.Run(() =>

                    {

                        //----< Row >----

                        //< add_Row >

                        DataRow row = dataTable.NewRow();

                        //</ add_Row >

 

                        

                        for (int iColumn = 1; iColumn <= nColumnsMax; iColumn++)

                        {

                            //----< read_cells_to_table >----

                            //< read >

                            string sValue = Convert.ToString(values[iRow, iColumn]);

                            if (iColumn == 1)

                            {

                                if (sValue == "" || sValue == null) { iRow=usedRange.Rows.Count; }

                            }

                            //</ read >

 

                            //< write >

                            row[iColumn - 1] = sValue;

                            //</ write >

                            //--</ Transfer Cells > --

                            //----</ read_cells_to_table >----

                        }

                        dataTable.Rows.Add(row);

                        //----</ Row >----

                    });

                }

                //----</ Read_DataRows >----

 

                //< show Data >

                _dataGrid.AutoGenerateColumns = true;

                _dataGrid.DataContext = dataTable;

                //</ show Data >

            }

 

 

            //return true;

            //----------------</ read_Excel_File_into_DataGridView() >------------

        }

 

        //===================</ Methods >===================

        #endregion /Methods

 

        protected async Task<bool> DoEvents()

        {

            //----< DoEvents() >---- 

            //* Diese Funktion uebernimmt die Unterbrechnung zur Anzeige und Eventbearbeitung in C#, WPF beim langen Loop Berechnungen 

            //* mit einer Dispatcher 

            //* EmptyDelegate im Header definieren 

            //* using System.Windows.Threading; im Header festlegen 

 

            //Dispatcher.CurrentDispatcher.Invoke(DispatcherPriority.Background, new DoEvents_EmptyDelegate(delegate { }));

            await Task.Run(()=> {

                Thread.Sleep(1);

            });

            return true;

            //----</ DoEvents() >---- 

        }

 

        //----------------------------------</ Class >----------------------------------

    }

    //----------------------------------</ Window >----------------------------------

}

 

 

 

 

XAML Datei

Datei: FormExcel_Data.xaml

 

<Window x:Class="addin_WPF_DataGrid_load_Excel.FormExcel_Data"

             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

             xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 

             xmlns:d="http://schemas.microsoft.com/expression/blend/2008" 

             xmlns:local="clr-namespace:addin_WPF_DataGrid_load_Excel"

             mc:Ignorable="d" 

             d:DesignHeight="450" d:DesignWidth="800" Loaded="Window_Loaded" 

        Title="Excel Data"

        >

    <Grid>

        <Grid.RowDefinitions>

            <RowDefinition Height="20"/>

            <RowDefinition Height="*"/>

            <RowDefinition Height="20"/>

        </Grid.RowDefinitions>

 

        <DataGrid x:Name="ctlDataGrid" VerticalAlignment="Stretch" HorizontalAlignment="Stretch"

                 AutoGenerateColumns="True"

                 GridLinesVisibility="All"

                 Background="#F0f0f0"

                 ItemsSource="{Binding}" Grid.Row="1" 

                 />

 

        <StatusBar Grid.Row="2" >

 

 

            <StatusBarItem HorizontalContentAlignment="Stretch" VerticalContentAlignment="Stretch" Padding="0" >

                <ProgressBar x:Name="ctlProgress" Background="Red" Maximum="1" Value="0" />

            </StatusBarItem>

        </StatusBar>

        <Label x:Name="lblStatus" Content="0/0"  Grid.Row="2" VerticalAlignment="Stretch" Padding="0"></Label>

 

    </Grid>

</Window>

 

 

 

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.Office.Tools.Ribbon;

 

namespace addin_WPF_DataGrid_load_Excel

{

    public partial class Ribbon1

    {

        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)

        {

 

        }

 

        private void BtnAddress_Select_Click(object sender, RibbonControlEventArgs e)

        {

            //-----------------< change_Excel_SerialFields() >-----------------

            FormExcel_Data frm = new FormExcel_Data();

            frm.Show();

            //-----------------</ change_Excel_SerialFields() >-----------------

        }

 

        private void tbxExcel_Filename_TextChanged(object sender, RibbonControlEventArgs e)

        {

            Settings1.Default.Excel_Filename = tbxExcel_Filename.Text;

            Settings1.Default.Save();

        }

    

    }

}