Пример #1
0
        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);
            }
        }
Пример #2
0
        // 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);
            }
        }