strExcel = "select * from [sheet1$]";[
这是从excel中导入数据到datagridview中的一句,但是这只是取默认表sheet1的数据,如果这个表的名字改了,就获取不到数据了,应该怎么解决,也就是说当表明动态变化时,我也能取相应表里面的数据
这是从excel中导入数据到datagridview中的一句,但是这只是取默认表sheet1的数据,如果这个表的名字改了,就获取不到数据了,应该怎么解决,也就是说当表明动态变化时,我也能取相应表里面的数据
而表名可以通过 Worksheet对象取到
1. see it as a database
strExcel = "select * from [" + tableName + "$]";
2.just as a excel file.
public void OpenWorkbook(string fileName)
{
m_Workbook = m_ExcelApplication.Workbooks.Open(fileName, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing); if (m_Workbook.Worksheets.Count > 0)
{
object index = 1;
m_Worksheet = m_Workbook.Worksheets[index] as Excel._Worksheet; }
} //then you can read or write it as Cell[x,y].
excelConn.Open();
DataTable sheetsName = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
foreach (DataRow dr in sheetsName.Rows)
{
Console.WriteLine(dr["TABLE_NAME"]);
}
#region 将指定的Excel对象转换成DataSet
/// <summary>
/// 将Excel对象转换成DataSet
/// </summary>
public static DataSet ExcelToTable(string fullfilename)
{
if (OK())
{ return null; }
string filename = fullfilename.Substring(fullfilename.LastIndexOf("\\") + 1);//取文件名
string filetype = filename.Substring(filename.LastIndexOf(".") + 1);//取扩展名
//***********************************************************************************读取
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";" + // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002),并且第一行作为数据返回,且以文本方式读取
"data source=" + fullfilename; string sql_F = "SELECT * FROM [{0}]";//查询语句
// 准备数据,导入DataSet
DataSet ds = new DataSet();
OleDbConnection conn = null;//创建连接
OleDbDataAdapter da = null;
DataTable tblSchema = null;
// 初始化连接,并打开
try
{
conn = new OleDbConnection(connStr);
conn.Open();
// 获取数据源的表定义元数据
tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// 初始化适配器
da = new OleDbDataAdapter();
foreach (DataRow row in tblSchema.Rows)
{
try
{
string tblNames = (string)row["TABLE_NAME"]; // 读取表名
da.SelectCommand = new OleDbCommand(String.Format(sql_F, tblNames), conn);
da.Fill(ds, tblNames);//填充表
}
catch
{
throw;
}
}
}
catch
{
throw;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return ds;
}
#endregion