Funktion:
Der Beitrag beschreibt die Funktionsweise eines Word-Excel-Daten Addins und enthält den Download des word-Addin als .zip Datei.
Das Add-In tauscht zur Laufzeit Datenfelder in einer Word-Datei ein mit Daten in einer Excel-Datei.
Somit funktioniert es wie ein Datenauswahl für Word-Formulare.
Bedienung:
Excel-Address-Daten öffnen
Im Word-Menü-Ribbon: Add-Ins befindet sich ein Button: Excel Daten auswählen.
Beim Klick auf den Button wird ein Auswahl-Fenster mit Daten aus einer Excel-Datei geöffnet.
Die Excel-Datei und das Excel-Arbeitsblatt können in der Ribbonbar in den Feldern Excel-Datei und Excel-Blatt eingestellt werden.
Daten einfügen, Übernehmen
Wenn man in dem Dialog-Formular den Button: Daten übernehmen klickt,
dann werden alle Serienfelder als Platzhalter im Word-Dokument mit Daten der aktuellen Zeile mit Excel-Blatt ausgetauscht.
Das Programm durchsucht dabei die Word-Datei nach Serienbrief-Feldern und liest den Serienbrief-Feld-Inhalt aus.
Betrifft:
Word Addin, welches
zur Laufzeit, Programmatically
Download und Code Beispiel
Formular-Code
Für Entwickler, welche das Addin anpassen wollen, kann hier das Formular frmExcel_Data.cs heruntergeladen werden oder der C# Code in eigene Anwendungen übernommen werden.
C# Code
Datei frmExcel_Data.cs Code
using Microsoft.Office.Interop.Word; using System;
using System.Windows.Forms; using Excel = Microsoft.Office.Interop.Excel; using Word = Microsoft.Office.Interop.Word;
namespace word_Addin_MailMerger { public partial class frmExcel_Data : Form { //< variables > private DataGridView _dataGridView; private Excel.Application _excel_App; private Excel.Worksheet _worksheet; private Excel.Workbook _workbook;
Document _doc = null; Word.Application _app = null; //</ variables >
#region Region: Form //===================< Form >=================== public frmExcel_Data() { InitializeComponent(); _dataGridView = dataGrid_Excel; _dataGridView.AllowUserToAddRows = false; _doc = Globals.ThisAddIn.Application.ActiveDocument; _app = Globals.ThisAddIn.Application; }
private void frmExcel_Data_Load(object sender, EventArgs e) {
} private void frmExcel_Data_Shown(object sender, EventArgs e) { _worksheet = open_Excel_File(); if (_worksheet == null) return; read_Excel_File_into_DataGridView(); lblStatus.Text = ""; try { _workbook.Close(); _excel_App.Quit(); } finally {
} } //===================</ Form >=================== #endregion /Region: Form
#region --Buttons-- //===================< Buttons >=================== private void btnSelect_Click(object sender, EventArgs e) { replace_Serial_Fields(); Close(); } //===================</ Buttons >=================== #endregion --/ Buttons--
#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.Text = "opening excel file.."; _excel_App = new Microsoft.Office.Interop.Excel.Application(); try { //----< open_Excel >---- _workbook = _excel_App.Workbooks.Open(sFilename);//, UpdateLinks: false, ReadOnly: true, AddToMru: false); //_worksheet = _workbook.Worksheets[Settings1.Default.Excel_Sheet]; //----</ open_Excel >---- return _workbook.Worksheets[Settings1.Default.Excel_Sheet]; ; } catch (Exception ex) { MessageBox.Show(ex.Message); return null; } //-----------------</ open_Excel_File() >----------------- }
public void read_Excel_File_into_DataGridView() { //----------------< read_Excel_File_into_DataGridView() >------------
//< init > DataGridView dataGridView = dataGrid_Excel; //</ init >
Excel.Range usedRange = _worksheet.UsedRange; int nColumnsMax = 0; if (usedRange.Rows.Count > 0) { //----< Read_Header >---- for (int iColumn = 1; iColumn <= usedRange.Columns.Count; iColumn++) { Excel.Range cell = usedRange.Cells[1, iColumn] as Excel.Range; String sValue = Convert.ToString(cell.Value2);
if (sValue == "" || sValue==null) break; dataGridView.Columns.Add("column_" + iColumn, sValue); lblStatus.Text = "load header " + sValue ; nColumnsMax = iColumn; } //----</ Read_Header >----
//----< Read_DataRows >---- for (int iRow = 2; iRow <= usedRange.Rows.Count; iRow++) { //< add_Row > int iNewRow = dataGridView.Rows.Add(new DataGridViewRow()); DataGridViewRow newRow = dataGridView.Rows[iNewRow]; //</ add_Row > lblStatus.Text = "load row" + iRow + "..";
for (int iColumn = 1; iColumn <= nColumnsMax; iColumn++) { Microsoft.Office.Interop.Excel.Range cell = usedRange.Cells[iRow, iColumn] as Excel.Range; String sValue = Convert.ToString(cell.Value2); if (iColumn == 1) { if (sValue == "" || sValue == null) return; // break; }
newRow.Cells[iColumn-1].Value = sValue; } } //----</ Read_DataRows >---- }
//----------------</ read_Excel_File_into_DataGridView() >------------ }
//===================</ Methods >=================== #endregion /Methods
#region --Methods Word -- //===================< Methods Word >=================== private void replace_Serial_Fields() { //-----------------< suche_Address_Felder() >----------------- //*Serienbrieffelder=ActiveDocument.FormFields[] //--< @Loop: SerialFields >-- foreach (object varField in _doc.MailMerge.Fields) { //«KDNR» MailMergeField field = (MailMergeField) varField ; string sField_Content = field.Code.Text; int posField = sField_Content.IndexOf("MERGEFIELD "); if ( posField > -1) // MERGEFIELD SANr { //---< IsMail_Field >--- //< get fieldname > posField = posField + "MERGEFIELD ".Length; string sField = sField_Content.Substring(posField); sField = sField.Trim(); //</ get fieldname >
//< get grid > int intColumn = get_Column_with_Header(sField); //</ get grid >
if(intColumn>=0) { string sReplace = _dataGridView.Rows[1].Cells[intColumn].Value.ToString();
//< replace field > field.Select(); _app.Selection.Text = sReplace; //field.Delete(); //</ replace field > } //---</ IsMail_Field >--- } } //--</ @Loop: SerialFields >-- //-----------------</ suche_Address_Felder() >----------------- }
private int get_Column_with_Header(string sHeader) { //-----------------< get_Column_with_Header() >----------------- //< init > DataGridView dataGridView = dataGrid_Excel; //</ init >
//--< @Loop: SerialFields >-- foreach (DataGridViewColumn col in dataGridView.Columns) { if (col.HeaderText == sHeader) return col.Index; } //--</ @Loop: SerialFields >-- return -1; //-----------------</ get_Column_with_Header() >----------------- }
//===================</ Methods Word >=================== #endregion /Methods Word } } |
DesignView Ribbon1.cs
Die Ribbonbar enthält im Entwurfsmode einen Button zum Öffnen und zwei Textbox Felder.
using System; using Microsoft.Office.Tools.Ribbon;
//----< using >---- using Word = Microsoft.Office.Interop.Word; //*word document using System.Windows.Forms; //*word OpenfileDialog using Office = Microsoft.Office.Core; //*lock ratio width height using Microsoft.Office.Interop.Word;
//----</ using >----
namespace word_Addin_MailMerger { public partial class Ribbon1 {
#region Region: Ribbonbar //===================< RibbonBar >=================== private void Ribbon1_Load(object sender, RibbonUIEventArgs e) { tbxExcel_Filename.Text = Settings1.Default.Excel_Filename; tbxExcel_Sheet.Text = Settings1.Default.Excel_Sheet; } //===================</ RibbonBar >=================== #endregion /Ribbonbar
#region --Button-- //===================< Buttons >=================== private void BtnAddress_Select_Click(object sender, RibbonControlEventArgs e) { change_Excel_SerialFields(); }
//===================</ Buttons >=================== #endregion /Buttons
#region --Controls-- //===================< Controls >=================== private void tbxExcel_Filename_TextChanged(object sender, RibbonControlEventArgs e) { Settings1.Default.Excel_Filename = tbxExcel_Filename.Text; Settings1.Default.Save(); } private void tbxExcel_Sheet_TextChanged(object sender, RibbonControlEventArgs e) { Settings1.Default.Excel_Sheet = tbxExcel_Sheet.Text; Settings1.Default.Save(); } //===================</ Controls >=================== #endregion /Controls
#region --Methods Excel-- //===================< Methods Excel >=================== private void change_Excel_SerialFields() { //-----------------< change_Excel_SerialFields() >----------------- frmExcel_Data frm = new frmExcel_Data(); frm.Show(); //-----------------</ change_Excel_SerialFields() >----------------- }
//===================</ Methods Excel >=================== #endregion /Methods Excel
} }
|
App.config
Code zu App.config
In der app.config wird in VSTO und Windows Forms Anwendungen die Einstellungen zur Settings-Datei eingelagert.
Xml Code der app.config
<?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> <sectionGroup name="userSettings" type="System.Configuration.UserSettingsGroup, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" > <section name="word_Addin_MailMerger.Settings1" type="System.Configuration.ClientSettingsSection, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" allowExeDefinition="MachineToLocalUser" requirePermission="false" /> </sectionGroup> </configSections> <userSettings> <word_Addin_MailMerger.Settings1> <setting name="Excel_Filename" serializeAs="String"> <value /> </setting> <setting name="Excel_Sheet" serializeAs="String"> <value /> </setting> </word_Addin_MailMerger.Settings1> </userSettings> </configuration> |
Settings1.Settings
In der Settings-Datei werden die Einstellungen und Änderungen der Dateinamen und des Excel-Datenblatts gespeichert.
Ribbon Design View Code
Zum ERstellen des Ribbonbar
namespace word_Addin_MailMerger { partial class Ribbon1 : Microsoft.Office.Tools.Ribbon.RibbonBase { /// <summary> /// Required designer variable. /// </summary> private System.ComponentModel.IContainer components = null;
public Ribbon1() : base(Globals.Factory.GetRibbonFactory()) { InitializeComponent(); }
/// <summary> /// Clean up any resources being used. /// </summary> /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param> protected override void Dispose(bool disposing) { if (disposing && (components != null)) { components.Dispose(); } base.Dispose(disposing); }
#region Component Designer generated code
/// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.tab1 = this.Factory.CreateRibbonTab(); this.group_Bericht = this.Factory.CreateRibbonGroup(); this.BtnAddress_Select = this.Factory.CreateRibbonButton(); this.group_Setup = this.Factory.CreateRibbonGroup(); this.tbxExcel_Filename = this.Factory.CreateRibbonEditBox(); this.tbxExcel_Sheet = this.Factory.CreateRibbonEditBox(); this.tab1.SuspendLayout(); this.group_Bericht.SuspendLayout(); this.group_Setup.SuspendLayout(); this.SuspendLayout(); // // tab1 // this.tab1.ControlId.ControlIdType = Microsoft.Office.Tools.Ribbon.RibbonControlIdType.Office; this.tab1.Groups.Add(this.group_Bericht); this.tab1.Groups.Add(this.group_Setup); this.tab1.Label = "TabAddIns"; this.tab1.Name = "tab1"; // // group_Bericht // this.group_Bericht.Items.Add(this.BtnAddress_Select); this.group_Bericht.Label = "Excel Data"; this.group_Bericht.Name = "group_Bericht"; // // BtnAddress_Select // this.BtnAddress_Select.ControlSize = Microsoft.Office.Core.RibbonControlSize.RibbonControlSizeLarge; this.BtnAddress_Select.Label = "Excel Daten auswählen"; this.BtnAddress_Select.Name = "BtnAddress_Select"; this.BtnAddress_Select.OfficeImageId = "AddressBook"; this.BtnAddress_Select.ShowImage = true; this.BtnAddress_Select.Click += new Microsoft.Office.Tools.Ribbon.RibbonControlEventHandler(this.BtnAddress_Select_Click); // // group_Setup // this.group_Setup.Items.Add(this.tbxExcel_Filename); this.group_Setup.Items.Add(this.tbxExcel_Sheet); this.group_Setup.Label = "setup"; this.group_Setup.Name = "group_Setup"; // // tbxExcel_Filename // this.tbxExcel_Filename.Label = "Excel Datei:"; this.tbxExcel_Filename.Name = "tbxExcel_Filename"; this.tbxExcel_Filename.Text = "WE-BUCH NEU ab 01.06.2018.xlsx"; this.tbxExcel_Filename.TextChanged += new Microsoft.Office.Tools.Ribbon.RibbonControlEventHandler(this.tbxExcel_Filename_TextChanged); // // tbxExcel_Sheet // this.tbxExcel_Sheet.Label = "Excel Blatt:"; this.tbxExcel_Sheet.Name = "tbxExcel_Sheet"; this.tbxExcel_Sheet.Text = "Retoureerfassung"; this.tbxExcel_Sheet.TextChanged += new Microsoft.Office.Tools.Ribbon.RibbonControlEventHandler(this.tbxExcel_Sheet_TextChanged); // // Ribbon1 // this.Name = "Ribbon1"; this.RibbonType = "Microsoft.Word.Document"; this.Tabs.Add(this.tab1); this.Load += new Microsoft.Office.Tools.Ribbon.RibbonUIEventHandler(this.Ribbon1_Load); this.tab1.ResumeLayout(false); this.tab1.PerformLayout(); this.group_Bericht.ResumeLayout(false); this.group_Bericht.PerformLayout(); this.group_Setup.ResumeLayout(false); this.group_Setup.PerformLayout(); this.ResumeLayout(false);
}
#endregion
internal Microsoft.Office.Tools.Ribbon.RibbonTab tab1; internal Microsoft.Office.Tools.Ribbon.RibbonGroup group_Bericht; internal Microsoft.Office.Tools.Ribbon.RibbonGroup group_Setup; internal Microsoft.Office.Tools.Ribbon.RibbonButton BtnAddress_Select; internal Microsoft.Office.Tools.Ribbon.RibbonEditBox tbxExcel_Filename; internal Microsoft.Office.Tools.Ribbon.RibbonEditBox tbxExcel_Sheet; }
partial class ThisRibbonCollection { internal Ribbon1 Ribbon1 { get { return this.GetRibbon<Ribbon1>(); } } } } |