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 |