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 == null) return; 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 == null) break; 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(); }
} }
|