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: Adds customer data from Excel file to Word file

26.10.2018 (๐Ÿ‘21392)


 

Function:

The article describes how a Word Excel Data Addin works and contains the download of the word addin as a .zip file.

At runtime, the add-in exchanges data fields in a Word file with data in an Excel file.

So it works like a data selector for Word forms.

 

Service:

 

Open Excel Address Data

In the Word Menu Ribbon: Add-ins there is a button: Select Excel Data.

Clicking on the button opens a selection window with data from an Excel file.

The Excel file and the Excel spreadsheet can be set in the ribbon bar in the Excel file and Excel sheet fields.

 

Insert data, Apply

If you click on the button: Apply data in the dialog form,

then all series fields are exchanged as placeholders in the Word document with data of the current line with Excel sheet.

 

The program searches the Word file for form letter fields and reads the form letter field content.

 

Subject:

Word Addin, which

at runtime, Programmatically

Download and code example

 

 

 

Form Code

For developers who want to customize the addin, you can download the form frmExcel_Data.cs or copy the C # code into your own application.

 

C # code

File 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

The Ribbonbar contains a button for opening and two text box fields in the design mode.

 

 

 

 

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 to App.config

In app.config the settings for the settings file are stored in VSTO and Windows Forms applications.

 

Xml code of the 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

The Settings file stores the settings and changes to the file names and the Excel spreadsheet.

 

 

Ribbon Design View Code

To create the 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>(); }

        }

    }

}