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); } }