public class Utils
{
public static DataSet LoadDataFromExcel(string fileName)
{
try
{
List<string> names = new List<string>();
System.Data.DataTable dtNames = GetExcelTableName(fileName);
for (int i = 0; i < dtNames.Rows.Count; i++)
{
if (dtNames.Rows[i]["Table_Name"].ToString() == "_xlnm#_FilterDatabase") continue;
names.Add(dtNames.Rows[i]["Table_Name"].ToString());
}
string strConn = GetConString(fileName);
DataSet OleDsExcle = new DataSet();
OleDbConnection OleConn = new OleDbConnection(strConn);
OleDbDataAdapter OleDaExcel = null; OleConn.Open();
for (int i = 0; i < names.Count; i++)
{
DataSet dsTmp = new DataSet();
String sql = String.Empty;
sql += String.Format("SELECT * FROM [{0}];", names[i]);
OleDaExcel = new OleDbDataAdapter(sql, OleConn);
OleDaExcel.Fill(dsTmp, names[i]);
OleDsExcle.Tables.Add(dsTmp.Tables[0].Copy());
}
OleConn.Close();
return OleDsExcle;
}
catch
{
return null;
}
}
public static System.Data.DataTable GetExcelTableName(string fileName)
{
try
{
if (System.IO.File.Exists(fileName))
{
OleDbConnection _ExcelConn = new OleDbConnection(GetConString(fileName));
_ExcelConn.Open();
System.Data.DataTable _Table = _ExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
_ExcelConn.Close();
return _Table;
}
return null;
}
catch
{
return null;
}
}
private static string GetConString(string fileName)
{
return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ fileName + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
{
public static DataSet LoadDataFromExcel(string fileName)
{
try
{
List<string> names = new List<string>();
System.Data.DataTable dtNames = GetExcelTableName(fileName);
for (int i = 0; i < dtNames.Rows.Count; i++)
{
if (dtNames.Rows[i]["Table_Name"].ToString() == "_xlnm#_FilterDatabase") continue;
names.Add(dtNames.Rows[i]["Table_Name"].ToString());
}
string strConn = GetConString(fileName);
DataSet OleDsExcle = new DataSet();
OleDbConnection OleConn = new OleDbConnection(strConn);
OleDbDataAdapter OleDaExcel = null; OleConn.Open();
for (int i = 0; i < names.Count; i++)
{
DataSet dsTmp = new DataSet();
String sql = String.Empty;
sql += String.Format("SELECT * FROM [{0}];", names[i]);
OleDaExcel = new OleDbDataAdapter(sql, OleConn);
OleDaExcel.Fill(dsTmp, names[i]);
OleDsExcle.Tables.Add(dsTmp.Tables[0].Copy());
}
OleConn.Close();
return OleDsExcle;
}
catch
{
return null;
}
}
public static System.Data.DataTable GetExcelTableName(string fileName)
{
try
{
if (System.IO.File.Exists(fileName))
{
OleDbConnection _ExcelConn = new OleDbConnection(GetConString(fileName));
_ExcelConn.Open();
System.Data.DataTable _Table = _ExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
_ExcelConn.Close();
return _Table;
}
return null;
}
catch
{
return null;
}
}
private static string GetConString(string fileName)
{
return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ fileName + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
}
}