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: Setzt Kunden Daten aus Excel Datei in Word Datei ein

26.10.2018 (👁21664)


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 == nullreturn;

            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==nullbreak;

                    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 == nullreturn// 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>(); }

        }

    }

}