前几天做了这样的导入，当时在网上找了很多例子，都不错。但是由于本机的一些设置原因，有一些代码不能执行，最后找到一个可行性的方法来实现这个功能；
看了一些Excel.Appliction方法导入，但是好像是需要一些配置，我照样配置了一下，可是还是不能用，无奈之下又换了种方法；
filePath为文件的路径
public static DataTable Import(string filePath)
{
DataTable rs = new DataTable();
bool canOpen = false;
OleDbConnection conn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filePath + ";" +
"Extended Properties=\"Excel 8.0;\"");
try//尝试数据连接是否可用
{
conn.Open();
conn.Close();
canOpen = true;
}
catch { }
if (canOpen)
{
//得到工作表的名称
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null);
string tablename = dt.Rows[0][2].ToString().Trim();
if (tablename.Length > 0)
{
OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [" +
tablename + "]", conn);
OleDbDataAdapter myData = new
OleDbDataAdapter(myOleDbCommand);
myData.Fill(rs);
conn.Close();
}
}
else//如何Excel的格式不是标准的需要转换一下(
只针对<table><tr><td></td></tr>)
{
System.IO.StreamReader tmpStream = File.OpenText(filePath);
string tmpStr = tmpStream.ReadToEnd();
tmpStream.Close();
rs = GetDataTableFromString(tmpStr);
tmpStr = "";
}
return rs;
}
//此方法来处理Excel的格式为表格（有tr、td）
private
static DataTable GetDataTableFromString(string tmpHtml)
{
string tmpStr=tmpHtml;
DataTable TB=new DataTable();
//先处理一下这个字符串，删除第一个<tr>之前合最后一个</tr>之后的部分
int index=tmpStr.IndexOf("<tr");
if(index>-1)
tmpStr=tmpStr.Substring(index);
else
return TB;
index=tmpStr.LastIndexOf("</tr>");
if(index>-1)
tmpStr=tmpStr.Substring(0,index+5);
else
return TB;
bool existsSparator=false;
char Separator=Convert.ToChar("^");
//如果原字符串中包含分隔符“^”则先把它替换掉
if(tmpStr.IndexOf(Separator.ToString())>-1)
{
existsSparator=true;
tmpStr=tmpStr.Replace("^","^$&^");
}
//先根据“</tr>”分拆
string[]
tmpRow=tmpStr.Replace("</tr>","^").Split(Separator);
for(int i=0;i<tmpRow.Length-1;i++)
{
DataRow newRow=TB.NewRow();
string tmpStrI=tmpRow[i];
if(tmpStrI.IndexOf("<tr")>-1)
{
tmpStrI=tmpStrI.Substring(tmpStrI.IndexOf("<tr"));
if(tmpStrI.IndexOf("display:none")<0||tmpStrI.IndexOf("display:none")>tmpStrI.IndexOf(">"))
{
if (i == 0)
tmpStrI = tmpStrI.Replace("</th>",
"^");//取表头
else
tmpStrI=tmpStrI.Replace("</td>","^");
string[] tmpField=tmpStrI.Split(Separator);
for(int j=0;j<tmpField.Length-1;j++)
{
tmpField[j]=RemoveString(tmpField[j],"<font>");
index=tmpField[j].LastIndexOf(">")+1;
if(index>0)
{
string
field=tmpField[j].Substring(index,tmpField[j].Length-index);
if(existsSparator)
field=field.Replace("^$&^","^");
if(i==0)
{
string tmpFieldName=field;
int sn=1;
while(TB.Columns.Contains(tmpFieldName))
{
tmpFieldName=field+sn.ToString();
sn+=1;
}
TB.Columns.Add(tmpFieldName);
}
else
{
newRow[j]=field;
}
}//end of if(index>0)
}
if(i>0)
TB.Rows.Add(newRow);
}
}
}
TB.AcceptChanges();
return TB;
}
/// <summary>
/// 从指定Html字符串中剔除指定的对象
/// </summary>
/// <param
name="tmpHtml">Html字符串</param>
/// <param
name="remove">需要剔除的对象--例如输入"<font>"则剔除"<font
???????>"和"</font>>"</param>
///
<returns></returns>
public static string RemoveString(string tmpHtml, string
remove)
{
tmpHtml = tmpHtml.Replace(remove.Replace("<",
"</"), "");
tmpHtml = RemoveStringHead(tmpHtml, remove);
return tmpHtml;
}
/// <summary>
/// 只供方法RemoveString()使用
/// </summary>
///
<returns></returns>
private static string RemoveStringHead(string tmpHtml, string
remove)
{
//为了方便注释，假设输入参数remove="<font>"
if (remove.Length < 1) return
tmpHtml;//参数remove为空：不处理返回
if ((remove.Substring(0, 1) != "<") ||
(remove.Substring(remove.Length - 1) != ">")) return
tmpHtml;//参数remove不是<?????>：不处理返回
int IndexS = tmpHtml.IndexOf(remove.Replace(">",
""));//查找“<font”的位置
int IndexE = -1;
if (IndexS > -1)
{
string tmpRight = tmpHtml.Substring(IndexS, tmpHtml.Length -
IndexS);
IndexE = tmpRight.IndexOf(">");
if (IndexE > -1)
tmpHtml = tmpHtml.Substring(0, IndexS) + tmpHtml.Substring(IndexS +
IndexE + 1);
if (tmpHtml.IndexOf(remove.Replace(">", ""))
> -1)
tmpHtml = RemoveStringHead(tmpHtml, remove);
}
return tmpHtml;
}
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;
}
注：需要导出格式为表格的Excel的，请查阅前面一篇文章便知