public static string[] getTableName(string FilePath)
{
#if !MONO
try
{
string strConn;
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath +
";Extended Properties=\"Excel 12.0;HDR=YES\"";
//strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection OleDbConn = new OleDbConnection(strConn);
OleDbConn.Open();
DataTable dt = OleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
List <string> sl = new List <string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (!dt.Rows[i]["TABLE_NAME"].ToString().StartsWith("_xlnm"))
{
sl.Add((String)dt.Rows[i]["TABLE_NAME"]);
}
}
OleDbConn.Close();
return(sl.ToArray());
}
catch (Exception ex)
#endif
{
var names = Excel2.getTableName(FilePath);
return(names);
}
}
// Methods
public static DataSet importExcelToDataSet(string FilePath, string TableName, bool dooutall)
{
#if !MONO
try
{
string strConn;
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath +
";Extended Properties=\"Excel 12.0;HDR=YES\"";
//strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection OleDbConn = new OleDbConnection(strConn);
OleDbConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + TableName + "]", OleDbConn);
DataSet myDataSet = new DataSet();
try
{
myCommand.Fill(myDataSet);
}
catch (Exception ex)
{
throw new Exception("该Excel文件的工作表的名字不正确," + ex.Message);
}
OleDbConn.Close();
return(myDataSet);
}
catch (Exception ex)
#endif
{
var ds = Excel2.importExcelToDataSet(FilePath, TableName);
for (int i = 0; i < ds.Tables.Count; i++)
{
var table = ds.Tables[0];
var newtable = new DataTable(table.TableName);
var row = table.Rows[0];
foreach (DataColumn column in table.Columns)
{
var obj = row[column.ColumnName];
var newcol = new DataColumn(column.ColumnName);
if (obj is int)
{
newcol.DataType = typeof(int);
}
else if (obj is float)
{
newcol.DataType = typeof(float);
}
else if (obj is double)
{
newcol.DataType = typeof(double);
newcol.DataType = typeof(int);
foreach (DataRow row1 in table.Rows)
{
try
{
double value = (double)row1[column.ColumnName];
if (value - (int)value > 0)
{
newcol.DataType = typeof(float);
}
}
catch
{
}
}
}
else if (obj is DateTime)
{
newcol.DataType = typeof(DateTime);
}
else
{
newcol.DataType = typeof(string);
}
if (dooutall)
{
newcol.DataType = typeof(string);
}
newtable.Columns.Add(newcol);
}
var tableclone = newtable.Clone();
if (newtable != null)
{
newtable.BeginLoadData();
foreach (DataRow myrow in table.Rows)
{
try
{
newtable.ImportRow(myrow);
}
catch (Exception)
{
StringBuilder sb = new StringBuilder();
foreach (var o in myrow.ItemArray)
{
sb.Append(o.ToString() + ",");
}
Console.WriteLine("Lost:" + sb.ToString());
}
}
newtable.EndLoadData();
}
ds.Tables.RemoveAt(0);
if (TableName == null)
{
ds.Tables.Add(newtable);
}
else if (table.TableName == TableName)
{
ds.Tables.Clear();
ds.Tables.Add(newtable);
break;
}
else
{
i--;
}
}
return(ds);
}
}
