Excel Datei in Windows DataGrid, DataGridView laden mit C# VSTO

Date 25.10.2018

Download:

Dieses Code Beispiel zeigt, wie man in einem Excel VSTO Addin ein Excel Worksheet ausliest und überträgt in ein Datengrid unter Windows Forms oder WPF

Betrifft:

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

Beim Klick auf den Ribbonbar->Button: Read_Excel wird das Excel Arbeitsblatt gelesen und übertragen in ein DataGridView

Code in C# zum Laden eines Exce-Datenblattes in ein 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() >------------

}

Vollständiger C# Code im Beispiel

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

Im Beispiel ist ein DataGridView in ein Windows Form Formular gezogen.

Das DataGridView wurde mit dataGrid_Excel benannt und im Lade-Code angebunden.

this.dataGrid_Excel.Name = "dataGrid_Excel";

Design-View Code

von 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;

}

Daten im Beispiel

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