作者:乔山办公网日期:
返回目录:excel表格制作
GetOleDbSchemaTable是个查看数据库内表结构的,表结构本身是以表的形式展现出来,所以返回的是DataTable,
你可以把这个dt绑定到表格控件,看了其内容你就知道后面为什么要那么写了
--返回Excel表中的名称(传入路径)
private static string GetSheetName(string filePath)
{
string sheetName = "";
System.IO.FileStream tmpStream = File.OpenRead(filePath);
byte[] fileByte = new byte[tmpStream.Length];
tmpStream.Read(fileByte, 0, fileByte.Length);
tmpStream.Close();
byte[] tmpByte = new byte[]{Convert.ToByte(11),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),
Convert.ToByte(11),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),
Convert.ToByte(30),Convert.ToByte(16),Convert.ToByte(0),Convert.ToByte(0)};
int index = GetSheetIndex(fileByte, tmpByte);
if (index > -1)
{
index += 16 + 12;
System.Collections.ArrayList sheetNameList = new System.Collections.ArrayList();
for (int i = index; i < fileByte.Length - 1; i++)
{
byte temp = fileByte[i];
if (temp != Convert.ToByte(0))
sheetNameList.Add(temp);
else
break;
}
byte[] sheetNameByte = new byte[sheetNameList.Count];
for (int i = 0; i < sheetNameList.Count; i++)
sheetNameByte[i] = Convert.ToByte(sheetNameList[i]);
sheetName = System.Text.Encoding.Default.GetString(sheetNameByte);
}
return sheetName;
}
/// <summary>
/// 只供方法GetSheetName()使用
/// </summary>
/// <returns></returns>
private static int GetSheetIndex(byte[] FindTarget, byte[] FindItem)
{
int index = -1;
int FindItemLength = FindItem.Length;
if (FindItemLength < 1) return -1;
int FindTargetLength = FindTarget.Length;
if ((FindTargetLength - 1) < FindItemLength) return -1;
for (int i = FindTargetLength - FindItemLength - 1; i > -1; i--)
{
System.Collections.ArrayList tmpList = new System.Collections.ArrayList();
int find = 0;
for (int j = 0; j < FindItemLength; j++)
{
if (FindTarget[i + j] == FindItem[j]) find += 1;
}
if (find == FindItemLength)
{
index = i;
break;
}
}
return index;
}
}
有上e799bee5baa6e79fa5e98193e59b9ee7ad94339可得到 sheetName
string strCom = "SELECT * FROM [" + sheetName + "$]";
(表达式)?x:y;首先计算表达式的值,若为ture,则"(表达式)?x:
string filepath = “c:/test.xls”;
string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
OleDbConnection MyConn = new OleDbConnection(strcon);
MyConn.Open();
string sheetname = "";
System.Data.DataTable dt = MyConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
for (int i = 0; i < dt.Rows.Count; i++)
{//循环取得所有表名
sheetname = dt.Rows[i]["TABLE_NAME"].ToString();
sheetnamelist.Add(sheetname);
}
string CurrSheet = sheetnamelist[0].ToString();//取得第一个表名
具体可能参考我空 间的一篇文章 《C#读写Excel文件》e799bee5baa6e4b893e5b19e335