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: Load Excel file into WPF DataGrid

31.10.2018 (👁29173)


 

The following code guide shows how to quickly load data from an Excel file into a WPF DataGrid as an overview in a Word add-in.

The trick is that a Windows WPF Control is very fast and modern.

 

The code files in C #, XAML and Winforms can be embedded in your own application and the files are loaded as an example in the file attachment for download.

 

 

Subject:

Word Add-In, Office Addin Excel Load Data, DataGrid DataGrid, DataGridView

Load and bind data grid with DataSource

In addition, an async task WPF.DoEvents is built in to refresh the display

 

contains:

C # code for creating and loading a WPF DataGrid with data from Excel

Start-Code from Ribbonbar of the WPF Windows

XAML Code of DataGrid and WPF Controls in VSTO Office Add-in

Zip file of the project

 

C # code file

File: 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 file

File: 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();

        }

    

    }

}