好久没发帖了,已经在VB版发了,在这不再重述,能解决的一并给分,一万多可用分没用武之地,呵呵.http://community.csdn.net/Expert/topic/5049/5049065.xml?temp=.6038782
解决方案 »
- C#Winform导入excel文件到 access数据库
- 在线急求,c# 前台JS调用CS后台变量问题
- jdk1.7 现在到底出来没有??
- TextBox内容更改和同步写入
- 求教,怎么分割取值啊??
- 错误 1 项“obj\Debug\俄罗斯方块.first.resources”在“Resources”参数中指定了多次。“Resources”参数不支持重复项。
- asp中,页面加载时,跟帖如何加载并显示??
- DataGridViewComboBoxColumn的数据源问题,很难,或许我没有想好思路,请帮我看看,谢谢了!
- 关于savefiledialog
- 随意的一个 计算表达式字符串(如 1+2*3-4), 怎么计算其值
- 关于ASP.NET引用office控件的问题!
- 使用Microsoft Visual Studio 2005 无法"启动调试"怎么办?
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM ["+指定的Sheet名字+"$]", strConn);
DataSet myDataSet= new DataSet();
myCommand.Fill(myDataSet,"table1");
http://dev.csdn.net/develop/article/15/15544.shtm
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;
}
#endregion public 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
}
}
private static ArrayList GetExecl(ref ArrayList ary,string Url)
{
Object refmissing = System.Reflection.Missing.Value;
Excel._Application exc = new Excel.ApplicationClass();
exc.Visible = false;
Excel.Workbooks workbooks = exc.Workbooks;
workbooks._Open(Url,refmissing,refmissing,refmissing,refmissing,refmissing,refmissing,refmissing,refmissing,refmissing,refmissing,refmissing,refmissing);
for(int i=0;i<exc.Worksheets.Count;i++)
{
Excel.Worksheet sheet = (Excel.Worksheet)exc.Worksheets.get_Item(i+1);
ary.Add(sheet.Name.ToString());
}
exc.Application.Quit();
return ary;
}
你的使用Excel.ApplicationClass对象的方法是可以实现我的要求.但我在帖子已经说明我不想使用Excel的COM对象了.To kbxj406(羽儿)
你的方法只能在事先知道Excel文件中Sheet的名字才能读取数据.为什么呢?因为GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null)返回的表的顺序和Excel文件中实际的Sheet的顺序不是一一对应的,不信你可以试一下新建一个Excel文件,默认下返回的是"Sheet1","Sheet2","Sheet3".
但如果你把Excel文件里面的Sheet的顺序调乱一下,它返回的还是"Sheet1","Sheet2","Sheet3".我发现GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null)返回的表名是经过排序的.但我希望返回的顺序和Excel文件里面的顺序要一样.
tn的"TABLE_NAME"列可以得到表名(已包含"$")用tn.Rows[1]["TABLE_NAME"]做为表名,你看行不行(这句没试验,直接打的,可能有错)