访问excel的类: using System; using System.Data; using System.Windows.Forms; using System.Data.OleDb;namespace Test { /// <summary> /// ExcelOperator 的摘要说明。 /// </summary> public class ExcelOperator {#region 私有成员/// <summary> /// 要操作的Excel文件名称 /// </summary> private string m_ExcelFileName; #region 数据库相关
http://support.microsoft.com/kb/306572/zh-cn?spid=548&sid=global
2、访问sql,写入数据;
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;namespace Test
{
/// <summary>
/// ExcelOperator 的摘要说明。
/// </summary>
public class ExcelOperator
{#region 私有成员/// <summary>
/// 要操作的Excel文件名称
/// </summary>
private string m_ExcelFileName; #region 数据库相关
/// <summary>
/// 链接对象
/// </summary>
private System.Data.OleDb.OleDbConnection m_Conn = null;
/// <summary>
/// 提取数据命令对象
/// </summary>
private System.Data.OleDb.OleDbCommand m_CommSelect = null;
/// <summary>
/// 更新数据命令对象
/// </summary>
private System.Data.OleDb.OleDbCommand m_CommUpdate = null;
/// <summary>
/// 数据适配器对象
/// </summary>
private System.Data.OleDb.OleDbDataAdapter m_da = null; #endregion#endregion#region 属性
public string ExcelFileName
{
get{return this.m_ExcelFileName;}set
{
this.m_ExcelFileName = value;
this.m_Conn.ConnectionString = GetConnectionString(value);}
}
#endregion#region 构造函数/// <summary>
/// 带参数的构造
/// </summary>
/// <param name="FileName">Excel文件名</param>
public ExcelOperator(string FileName)
{
this.m_ExcelFileName = FileName;///构造数据库对象
///
this.m_Conn = new OleDbConnection(GetConnectionString(FileName));this.m_CommSelect = new OleDbCommand();
this.m_CommUpdate = new OleDbCommand();this.m_CommSelect.Connection = this.m_Conn;
this.m_CommUpdate.Connection = this.m_Conn;this.m_da = new OleDbDataAdapter();this.m_da.SelectCommand = this.m_CommSelect;
this.m_da.UpdateCommand = this.m_CommUpdate;
}
/// <summary>
/// 不带参数的构造
/// </summary>
public ExcelOperator()
{
///构造数据库对象
///
this.m_Conn = new OleDbConnection();this.m_CommSelect = new OleDbCommand();
this.m_CommUpdate = new OleDbCommand();this.m_CommSelect.Connection = this.m_Conn;
this.m_CommUpdate.Connection = this.m_Conn;this.m_da = new OleDbDataAdapter();this.m_da.SelectCommand = this.m_CommSelect;
this.m_da.UpdateCommand = this.m_CommUpdate;}#endregion
#region 方法
#region 私有方法
/// <summary>
/// 根据EXCEL文件名,返回连接字符串
/// </summary>
/// <param name="FileName">Excel文件名</param>
/// <returns>连接字符串</returns>
private static string GetConnectionString(string FileName)
{
return
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=" + FileName + ";" +
@"Extended Properties=Excel 8.0"/* + Convert.ToChar(34).ToString() +
@"Excel 8.0;"+ "Imex=2;HDR=No;" + Convert.ToChar(34).ToString()*/;
}
#endregion#region 公有方法/// <summary>
/// 返回Excel表中的Sheets
/// </summary>
/// <returns>Sheets的名称</returns>
public string[] GetExcelSheets()
{
System.Data.DataTable dt = null;string[] res = null;
try
{
//打开数据库链接
this.m_Conn.Open();//将Sheets获取到表中
dt = this.m_Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
if(null != dt)
{
res = new string[dt.Rows.Count];for(int i = 0;i<dt.Rows.Count;i++)
{
res[i] = dt.Rows[i]["TABLE_NAME"].ToString();
res[i] = res[i].Substring(0,res[i].Length-1);
}this.m_Conn.Close();
}
else
{
res = null;
}
}
catch(System.Data.OleDb.OleDbException e)
{
this.m_Conn.Close();MessageBox.Show("获取Excel文件工作表失败",e.Message);
return null;}return res;
}
#endregionpublic DataSet ReadODBC(string SheetName)
{
this.m_CommSelect.CommandText = @"SELECT * FROM ["+SheetName+"$]";
DataSet myDataSet = new DataSet();
try
{
this.m_da.Fill(myDataSet);
return myDataSet;
}
catch(System.Data.ConstraintException exp)
{
MessageBox.Show(exp.Message);
return null;
}
catch(System.Data.OleDb.OleDbException exp)
{
MessageBox.Show(exp.Message);
return null;
}
catch(System.Runtime.InteropServices.COMException exp)
{
MessageBox.Show(exp.Message);
return null;
}
catch(Exception exp)
{
MessageBox.Show(exp.Message);
return null;
}
}#endregion
}
}