/// <summary>
/// 从Excel中获取数据.(无须指定表名,返回的DataSet包含此Excel中所有的表).
/// </summary>
/// <param name="filePath">Excel物理路径.</param>
/// <returns>DataSet:Excel中表的记录集合.</returns>
public DataSet ReadExcle(string filePath, out string msg)
{
string strSql = string.Empty;
msg = string.Empty;
DataSet ds = new DataSet();
DataTable dt = null;
OleDbDataAdapter adapter=null;
string strTableName = string.Empty;
if (filePath != string.Empty)
{
try
{
//判断是否为Xls文件.
if (!filePath.Substring(filePath.Length - 3, 3).Equals("xls"))
{
throw new Exception(ImportConst.ErrorMessage_NoExcelFile);
}
OleDbConnection conn = new OleDbConnection(string.Format(strConnectionString, filePath));
conn.Open();
//获取XML文件Schema信息.
DataTable dtschema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dtschema != null && dtschema.Rows.Count > 0)
{
//遍历Excel中所有的表
foreach (DataRow row in dtschema.Rows)
{
strTableName = row[2].ToString().Trim(new char[] { '\'' });
if (IsTable(strTableName))
{
dt = new DataTable(strTableName.Replace('$', ' '));
if (strTableName.IndexOf(" ") != -1)
{
strSql = string.Format("select top 4000 * from ['{0}']", strTableName);
}
else
{
strSql = string.Format("select top 4000 * from [{0}]", strTableName);
}
adapter = new OleDbDataAdapter(strSql, conn);
adapter.Fill(dt);
ds.Tables.Add(dt);
}
}
}
conn.Close();
}
catch (Exception Ex)
{
msg += Ex.Message + "$";
}
}
else
{
msg += ImportConst.ErrorMessage_NullPath + "$";
}
return ds; }
/// <summary>
/// 判定是否为Excel中包含记录的表.(排除掉打印区域等表格.e.g xx$print Area)
/// </summary>
/// <param name="tableName">Excel中获取到的表名.</param>
/// <returns></returns>
protected bool IsTable(string tableName)
{
bool blIsTable = false;
if (tableName != string.Empty)
{
int nIndex = tableName.LastIndexOf("$");
if (nIndex!=-1&&tableName.Length-1==nIndex)
{
blIsTable = true;
}
}
return blIsTable;
}
/// 从Excel中获取数据.(无须指定表名,返回的DataSet包含此Excel中所有的表).
/// </summary>
/// <param name="filePath">Excel物理路径.</param>
/// <returns>DataSet:Excel中表的记录集合.</returns>
public DataSet ReadExcle(string filePath, out string msg)
{
string strSql = string.Empty;
msg = string.Empty;
DataSet ds = new DataSet();
DataTable dt = null;
OleDbDataAdapter adapter=null;
string strTableName = string.Empty;
if (filePath != string.Empty)
{
try
{
//判断是否为Xls文件.
if (!filePath.Substring(filePath.Length - 3, 3).Equals("xls"))
{
throw new Exception(ImportConst.ErrorMessage_NoExcelFile);
}
OleDbConnection conn = new OleDbConnection(string.Format(strConnectionString, filePath));
conn.Open();
//获取XML文件Schema信息.
DataTable dtschema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dtschema != null && dtschema.Rows.Count > 0)
{
//遍历Excel中所有的表
foreach (DataRow row in dtschema.Rows)
{
strTableName = row[2].ToString().Trim(new char[] { '\'' });
if (IsTable(strTableName))
{
dt = new DataTable(strTableName.Replace('$', ' '));
if (strTableName.IndexOf(" ") != -1)
{
strSql = string.Format("select top 4000 * from ['{0}']", strTableName);
}
else
{
strSql = string.Format("select top 4000 * from [{0}]", strTableName);
}
adapter = new OleDbDataAdapter(strSql, conn);
adapter.Fill(dt);
ds.Tables.Add(dt);
}
}
}
conn.Close();
}
catch (Exception Ex)
{
msg += Ex.Message + "$";
}
}
else
{
msg += ImportConst.ErrorMessage_NullPath + "$";
}
return ds; }
/// <summary>
/// 判定是否为Excel中包含记录的表.(排除掉打印区域等表格.e.g xx$print Area)
/// </summary>
/// <param name="tableName">Excel中获取到的表名.</param>
/// <returns></returns>
protected bool IsTable(string tableName)
{
bool blIsTable = false;
if (tableName != string.Empty)
{
int nIndex = tableName.LastIndexOf("$");
if (nIndex!=-1&&tableName.Length-1==nIndex)
{
blIsTable = true;
}
}
return blIsTable;
}
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货