Excel 数据导入dataGrid
感谢网上的同胞，提供了这一课的学习；
实现过程：
1.创建类：
2.提供类接口的数据；
类代码：
using System;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data;
using System.Collections;
namespace PutInDataSet
{
///
/// PutInDataSet 的摘要说明。
///
public class PutInDataSet
{
///
/// 传入的文件变量
///
private DataSet my_Ds;//存放文件的数据集
private string my_Err;//错误信息
private string my_TableName;//传入的文件名
private TableType my_TableType;//传入的文件类型
private string my_TablePath;//传入的文件路径
private int my_TableIndex;//表的索引
OleDbCommandBuilder my_Builder;//命令串
///
/// 数据库连接变量
///
private string my_StrConnection;//连接字符串
private string my_StrSelect;//select语句
///
/// 可以处理的文件类型
///
public enum TableType
{
MDB,XLS,DBF,DOC,TXT,XML,HTML
}
public PutInDataSet(string TablePath,string TableName,TableType TableType)
{
///
///获得传入的路径,文件名及文件类型；
///
this.my_TablePath=TablePath;//路径
this.my_TableName=TableName;//文件名
this.my_TableType=TableType;//文件类型
}
public DataSet Convert()
{
DataSet iRtn_Ds=new DataSet();
switch (this.my_TableType)
{
case TableType.DBF:
iRtn_Ds = this.DbfToDs();
break;
case TableType.MDB:
iRtn_Ds = this.MdbToDs();
break;
case TableType.XLS:
iRtn_Ds = this.XlsToDs();
break;
case TableType.XML:
iRtn_Ds = this.XmlToDs();
break;
}
return iRtn_Ds;
}
///
///将DBF文件放入DataSet
///
private DataSet DbfToDs()
{
//数据库连接定义
OdbcConnection my_conn; //数据连接
OdbcDataAdapter my_Adapter;//数据适配器
//数据库连接
this.my_StrConnection= "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" + this.my_TablePath;
this.my_StrSelect="Select * FROM " + this.my_TableName;
my_conn = new OdbcConnection(this.my_StrConnection);
my_conn.Open();
my_Adapter = new OdbcDataAdapter(this.my_StrSelect,my_conn);
this.my_Ds=new DataSet();
//填充数据集
my_Adapter.Fill(this.my_Ds,this.my_TableName);
return this.my_Ds;
}
///
///将MDB文件放入DataSet
///
private DataSet MdbToDs()
{
//数据库连接定义
OleDbConnection my_conn;
OleDbDataAdapter my_Adapter;
//数据库连接
this.my_StrConnection= "Provider=Microsoft.JET.OLEDB.4.0;data source=" + this.my_TablePath;
this.my_StrSelect="Select * FROM " + this.my_TableName;
my_conn = new OleDbConnection(this.my_StrConnection);
my_conn.Open();
my_Adapter = new OleDbDataAdapter(this.my_StrSelect,my_conn);
this.my_Ds=new DataSet();
//填充数据集
my_Adapter.Fill(this.my_Ds,this.my_TableName);
return this.my_Ds;
}
///
///将XML文件放入DataSet
///
private DataSet XmlToDs()
{
//填充数据集
this.my_Ds=new DataSet();
this.my_Ds.ReadXml(this.my_TablePath+this.my_TableName,XmlReadMode.ReadSchema);
this.my_Ds.DataSetName="XmlData";
return this.my_Ds;
}
///
///将Excel文件放入DataSet
///
private DataSet XlsToDs()
{
OleDbConnection my_conn;
OleDbDataAdapter my_Adapter;
//数据库连接
//this.my_StrConnection= "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source="+this.my_TablePath; //+this.my_TableName;此处觉的没必要取EXCLE的表名，故修改
this.my_StrConnection="Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + this.my_TablePath + ";" + "Extended Properties=Excel 8.0;";
this.my_StrSelect="Select * FROM [Sheet1$]";
my_conn = new OleDbConnection(this.my_StrConnection);
my_conn.Open();
my_Adapter = new OleDbDataAdapter(this.my_StrSelect,my_conn);
this.my_Builder=new OleDbCommandBuilder(my_Adapter);
this.my_Ds=new DataSet();
//填充数据集
my_Adapter.Fill(this.my_Ds,"ExcelData");
return this.my_Ds;
}
}
}
FORM 调用代码：
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
namespace PutInDataSet
{
///
/// Form1 的摘要说明。
///
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
private System.Windows.Forms.Button btn2;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.DataGrid dataGrid1;
///
/// 必需的设计器变量。
///
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Windows 窗体设计器支持所必需的
//
InitializeComponent();
//
// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
//
}
///
/// 清理所有正在使用的资源。
///
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows 窗体设计器生成的代码
///
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
///
private void InitializeComponent()
{
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.button1 = new System.Windows.Forms.Button();
this.btn2 = new System.Windows.Forms.Button();
this.label1 = new System.Windows.Forms.Label();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.SuspendLayout();
//
// dataGrid1
//
this.dataGrid1.AlternatingBackColor = System.Drawing.Color.Lavender;
this.dataGrid1.BackColor = System.Drawing.Color.WhiteSmoke;
this.dataGrid1.BackgroundColor = System.Drawing.Color.LightGray;
this.dataGrid1.BorderStyle = System.Windows.Forms.BorderStyle.None;
this.dataGrid1.CaptionBackColor = System.Drawing.Color.LightSteelBlue;
this.dataGrid1.CaptionForeColor = System.Drawing.Color.MidnightBlue;
this.dataGrid1.DataMember = "";
this.dataGrid1.FlatMode = true;
this.dataGrid1.Font = new System.Drawing.Font("Tahoma", 8F);
this.dataGrid1.ForeColor = System.Drawing.Color.MidnightBlue;
this.dataGrid1.GridLineColor = System.Drawing.Color.Gainsboro;
this.dataGrid1.GridLineStyle = System.Windows.Forms.DataGridLineStyle.None;
this.dataGrid1.HeaderBackColor = System.Drawing.Color.MidnightBlue;
this.dataGrid1.HeaderFont = new System.Drawing.Font("Tahoma", 8F, System.Drawing.FontStyle.Bold);
this.dataGrid1.HeaderForeColor = System.Drawing.Color.WhiteSmoke;
this.dataGrid1.LinkColor = System.Drawing.Color.Teal;
this.dataGrid1.Location = new System.Drawing.Point(8, 72);
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.ParentRowsBackColor = System.Drawing.Color.Gainsboro;
this.dataGrid1.ParentRowsForeColor = System.Drawing.Color.MidnightBlue;
this.dataGrid1.SelectionBackColor = System.Drawing.Color.CadetBlue;
this.dataGrid1.SelectionForeColor = System.Drawing.Color.WhiteSmoke;
this.dataGrid1.Size = new System.Drawing.Size(672, 392);
this.dataGrid1.TabIndex = 0;
//
// button1
//
this.button1.Location = new System.Drawing.Point(520, 16);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(104, 23);
this.button1.TabIndex = 1;
this.button1.Text = "button1";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// btn2
//
this.btn2.Location = new System.Drawing.Point(80, 16);
this.btn2.Name = "btn2";
this.btn2.TabIndex = 2;
this.btn2.Text = "文件";
this.btn2.Click += new System.EventHandler(this.btn2_Click);
//
// label1
//
this.label1.Location = new System.Drawing.Point(168, 24);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(312, 23);
this.label1.TabIndex = 3;
this.label1.Visible = false;
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(688, 477);
this.Controls.Add(this.label1);
this.Controls.Add(this.btn2);
this.Controls.Add(this.button1);
this.Controls.Add(this.dataGrid1);
this.Name = "Form1";
this.Text = "Form1";
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false);
}
#endregion
///
/// 应用程序的主入口点。
///
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void button1_Click(object sender, System.EventArgs e)
{
DataSet out_Ds=new DataSet();
PutInDataSet obj=new PutInDataSet(label1.Text,"",PutInDataSet.TableType.XLS);//调用PutInDataSet类
out_Ds=obj.Convert();//转换到DataSet中
this.dataGrid1.DataSource=out_Ds.Tables["ExcelData"]; //在DataGrid中显示
}
private void btn2_Click(object sender, System.EventArgs e)
{
OpenFileDialog openFile = new OpenFileDialog();
openFile.DefaultExt = "xls";
// The Filter property requires a search string after the pipe ( | )
openFile.Filter = "Excel documents (*.xls)|*.xls";
openFile.ShowDialog();
if( openFile.FileNames.Length > 0 )
{
label1.Text=openFile.FileName;
//label1.text=openFile.FileName;
//foreach( string filename in openFile.FileNames )
//{
// Insert code here to process the files.
//}
}
}
}
}
浙公网安备 33010602011771号