Excel and DataGridView in .Net

Stumble del.icio.us Reddit MyWeb! Facebook Google bookmark

Here is a sample code to populate data from excel sheet into DataGridView like this:

One can populate the data in multiple ways one of which is explained here. Note the DataGridView.AutoGenerateColumn property which is not visible in designer because of it Browsable attribute being false. Thus if you have 10 columns in excel but you are interested in showing only 3 columns, here is how you can do it...

// Code for form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1 ()
{
InitializeComponent();
}

private void Form1_Load (object sender,EventArgs e)
{
DataTable sampleDataTable = new DataTable();

OleDbConnection aConnection = new OleDbConnection(
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Praveen K. Jha\Documents\book1.xls;Extended Properties=Excel 8.0");

aConnection.Open();

OleDbDataAdapter oleDbCommand = new
OleDbDataAdapter("Select * from [ExcelSheetNameContainingData$]"
, aConnection);
oleDbCommand.Fill(sampleDataTable);
// Now set the DataPropertyName which will tell which excel column maps to which datagridview column
dataGridView1.Columns[0].DataPropertyName= "ExcelSheetColumn1Name";
// At the same time set the header cell text too
dataGridView1.Columns[0].HeaderText ="ExcelSheetColumn1Name";

dataGridView1.Columns[1].DataPropertyName= "ExcelSheetColumn2Name";
dataGridView1.Columns[1].HeaderText ="ExcelSheetColumn2Name";

dataGridView1.Columns[2].DataPropertyName= "ExcelSheetColumn3Name";
dataGridView1.Columns[2].HeaderText ="ExcelSheetColumn3Name";
// Don't allow to automatically generate the columns
dataGridView1.AutoGenerateColumns = false;
dataGridView1.DataSource = sampleDataTable;
}
}
}

Here is the code for the designer part of the form which has the datagridview.

// Code for form1.designer.cs
namespace WindowsApplication1
{
partial class Form1
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.IContainer components = null;

/// <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 Windows Form 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.dataGridView1 = new System.Windows.Forms.DataGridView();
this.Column1 = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.Column2 = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.Column3 = new System.Windows.Forms.DataGridViewTextBoxColumn();
((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit();
this.SuspendLayout();
//
// dataGridView1
//
this.dataGridView1.AllowUserToAddRows = false;
this.dataGridView1.AllowUserToDeleteRows = false;
this.dataGridView1.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.Fill;
this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
this.dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] {
this.Column1,
this.Column2,
this.Column3});
this.dataGridView1.Location = new System.Drawing.Point(45,38);
this.dataGridView1.MultiSelect = false;
this.dataGridView1.Name = "dataGridView1";
this.dataGridView1.ReadOnly = true;
this.dataGridView1.RowHeadersVisible = false;
this.dataGridView1.SelectionMode = System.Windows.Forms.DataGridViewSelectionMode.FullRowSelect;
this.dataGridView1.ShowCellErrors = false;
this.dataGridView1.ShowRowErrors = false;
this.dataGridView1.Size = new System.Drawing.Size(355,221);
this.dataGridView1.TabIndex = 0;
//
// Column1
//
this.Column1.HeaderText = "Column1";
this.Column1.Name = "Column1";
this.Column1.ReadOnly = true;
//
// Column2
//
this.Column2.HeaderText = "Column2";
this.Column2.Name = "Column2";
this.Column2.ReadOnly = true;
//
// Column3
//
this.Column3.HeaderText = "Column3";
this.Column3.Name = "Column3";
this.Column3.ReadOnly = true;
//
// Form1
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F,13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(474,356);
this.Controls.Add(this.dataGridView1);
this.Name = "Form1";
this.Text = "Form1";
this.Load += new System.EventHandler(this.Form1_Load);
((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit();
this.ResumeLayout(false);

}

#endregion

private System.Windows.Forms.DataGridView dataGridView1;
private System.Windows.Forms.DataGridViewTextBoxColumn Column1;
private System.Windows.Forms.DataGridViewTextBoxColumn Column2;
private System.Windows.Forms.DataGridViewTextBoxColumn Column3;
}
}

Hope this helps!!