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

Excel file in Windows DataGrid, DataGridView load with C # VSTO

25.10.2018 (👁13077)


 

This code example shows how to read an Excel Worksheet in an Excel VSTO Addin and transfer it into a data grid under Windows Forms or WPF

 

Subject:

Addin, VSTO WinForms DataGridView, C # VSTO, C # Code Example

 

By clicking on the Ribbonbar-> Button: Read_Excel the Excel worksheet is read and transferred to a DataGridView

 

Code in C # to load an Exce datasheet into a Windows Form DataGridView

public void read_Excel_File_into_DataGridView()

        {

            //----------------< read_Excel_File_into_DataGridView() >------------

            //< init >

            Worksheet worksheet = Globals.ThisAddIn.Application.ActiveSheet;

            DataGridView dataGridView = dataGrid_Excel;

            //</ init >

 

 

            Range usedRange = worksheet.UsedRange;

            int nColumnsMax = 0;

            String sText = "";

            if (usedRange.Rows.Count > 0)

            {

                //----< Read_Header >----

                for (int iColumn = 1; iColumn <= usedRange.Columns.Count; iColumn++)

                {

                    Microsoft.Office.Interop.Excel.Range cell = usedRange.Cells[1, iColumn] as Range;

                    String sValue = cell.Value2.ToString();

 

                    if (sValue == ""break;

                    dataGridView.Columns.Add("column_" + iColumn, 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 >

 

                    for (int iColumn = 1; iColumn <= nColumnsMax; iColumn++)

                    {

                        Microsoft.Office.Interop.Excel.Range cell = usedRange.Cells[iRow, iColumn] as Range;

                        String sValue = cell.Value2.ToString();

                        newRow.Cells[iColumn-1].Value = sValue;

                    }

                }

                //----</ Read_DataRows >----

            }

 

            //----------------</ read_Excel_File_into_DataGridView() >------------

        }

 

 

 

 

 

 

Complete C # code in the example

File: frmExcel_Data.cs

 

using System;

 

using System.Windows.Forms;

using Microsoft.Office.Interop.Excel;

 

namespace ExcelAddIn01

{

    public partial class frmExcel_Data : Form

    {

        public frmExcel_Data()

        {

            InitializeComponent();

        }

 

        private void frmExcel_Data_Load(object sender, EventArgs e)

        {

            read_Excel_File_into_DataGridView();

        }

 

 

        #region --Methods--

        //===================< Methods >===================

        public void read_Excel_File_into_DataGridView()

        {

            //----------------< read_Excel_File_into_DataGridView() >------------

            //< init >

            Worksheet worksheet = Globals.ThisAddIn.Application.ActiveSheet;

            DataGridView dataGridView = dataGrid_Excel;

            //</ init >

 

 

            Range usedRange = worksheet.UsedRange;

            int nColumnsMax = 0;

            String sText = "";

            if (usedRange.Rows.Count > 0)

            {

                //----< Read_Header >----

                for (int iColumn = 1; iColumn <= usedRange.Columns.Count; iColumn++)

                {

                    Microsoft.Office.Interop.Excel.Range cell = usedRange.Cells[1, iColumn] as Range;

                    String sValue = cell.Value2.ToString();

 

                    if (sValue == ""break;

                    dataGridView.Columns.Add("column_" + iColumn, 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 >

 

                    for (int iColumn = 1; iColumn <= nColumnsMax; iColumn++)

                    {

                        Microsoft.Office.Interop.Excel.Range cell = usedRange.Cells[iRow, iColumn] as Range;

                        String sValue = cell.Value2.ToString();

                        newRow.Cells[iColumn-1].Value = sValue;

                    }

                }

                //----</ Read_DataRows >----

            }

 

            //----------------</ read_Excel_File_into_DataGridView() >------------

        }

        //===================</ Methods >===================

        #endregion --/Methods--

 

    }

}

 

 

 

DataGridView in WinForm

 

In the example, a DataGridView is dragged into a Windows Form form.

The DataGridView was named dataGrid_Excel and bound in the load code.

this.dataGrid_Excel.Name = "dataGrid_Excel";

 

 

Design View Code

from frmExcel_Data.cs

namespace ExcelAddIn01

{

    partial class frmExcel_Data

    {

        

private System.ComponentModel.IContainer components = null;

 

        

protected override void Dispose(bool disposing)

        {

            if (disposing && (components != null))

            {

                components.Dispose();

            }

            base.Dispose(disposing);

        }

 

        #region Windows Form Designer generated code

 

        

/// </summary>

        private void InitializeComponent()

        {

            this.dataGrid_Excel = new System.Windows.Forms.DataGridView();

            ((System.ComponentModel.ISupportInitialize)(this.dataGrid_Excel)).BeginInit();

            this.SuspendLayout();

            // 

            // dataGrid_Excel

            // 

            this.dataGrid_Excel.AllowUserToAddRows = false;

            this.dataGrid_Excel.AllowUserToDeleteRows = false;

            this.dataGrid_Excel.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;

            this.dataGrid_Excel.Dock = System.Windows.Forms.DockStyle.Fill;

            this.dataGrid_Excel.Location = new System.Drawing.Point(0, 0);

            this.dataGrid_Excel.Name = "dataGrid_Excel";

            this.dataGrid_Excel.ReadOnly = true;

            this.dataGrid_Excel.RowTemplate.Height = 24;

            this.dataGrid_Excel.Size = new System.Drawing.Size(800, 450);

            this.dataGrid_Excel.TabIndex = 0;

            // 

            // frmExcel_Data

            // 

            this.AutoScaleDimensions = new System.Drawing.SizeF(8F, 16F);

            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;

            this.ClientSize = new System.Drawing.Size(800, 450);

            this.Controls.Add(this.dataGrid_Excel);

            this.Name = "frmExcel_Data";

            this.Text = "Excel Data";

            this.Load += new System.EventHandler(this.frmExcel_Data_Load);

            ((System.ComponentModel.ISupportInitialize)(this.dataGrid_Excel)).EndInit();

            this.ResumeLayout(false);

        }

        #endregion

 

        private System.Windows.Forms.DataGridView dataGrid_Excel;

    }

}

 

 

Data in the example

In Excel Copy

cellA1

cellB1

Cell R1C3

CellD1

22

33

="Cell " & "R" & ZEILE() & "C" & SPALTE()

44

222

333

C3

D3

 

In DataGridView Copy

 

22

33

="Cell " & "R" & ZEILE() & "C" & SPALTE()

44

 

222

333

C3

D3