Exemplo n.º 1
0
    public int GetExcelDistictBTNCOunt_Sales(string sFileName)
    {
        Int32 NORec;
        DataSet objDataset1 = new DataSet();
        int count = 0;
        OleDbConnection objConn = new OleDbConnection();
        try
        {
             string FileExt = System.IO.Path.GetExtension(sFileName);
             if (FileExt == ".xls")
             {
                 //Excell connection
                 string Xls_Con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFileName + ";Extended Properties=\"Excel 8.0;HDR=YES; IMEX=1;ImportMixedTypes=Text\"";
                 objConn.ConnectionString = Xls_Con;
                 //Dim objConn As New OleDbConnection(Xls_Con)
                 objConn.Open();
                 DataTable ExcelSheets = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                 string SpreadSheetName = "[" + ExcelSheets.Rows[0]["TABLE_NAME"].ToString() + "]";

                 OleDbDataAdapter objAdapter1 = new OleDbDataAdapter("SELECT distinct(Phoneno) FROM " + SpreadSheetName, objConn);
                 objAdapter1.Fill(objDataset1, "XLData");

                 count = Convert.ToInt32(objDataset1.Tables[0].Rows.Count);
             }
             else if (FileExt == ".xlsx")
             {
                 //Excell connection
                 string Xls_Con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sFileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                 objConn.ConnectionString = Xls_Con;
                 //Dim objConn As New OleDbConnection(Xls_Con)
                 objConn.Open();
                 DataTable ExcelSheets = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                 string SpreadSheetName = "[" + ExcelSheets.Rows[0]["TABLE_NAME"].ToString() + "]";

                 OleDbDataAdapter objAdapter1 = new OleDbDataAdapter("SELECT distinct(Phoneno) FROM " + SpreadSheetName, objConn);
                 objAdapter1.Fill(objDataset1, "XLData");

                 count = Convert.ToInt32(objDataset1.Tables[0].Rows.Count);
             }
            objConn.Close();
        }
        catch (Exception ex)
        {
            throw ex;
            //Redirecting to error message page

            // Redirect(ConstantClass.StrErrorPageURL);
        }
        return count;
    }
Exemplo n.º 2
0
        public static Dictionary<string, TableInfo> getSchema(OleDbConnection connection)
        {
            Dictionary<string, TableInfo> infos_dict = new Dictionary<string, TableInfo>();
            DataTable dt;
            dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            foreach (DataRow row in dt.Rows)
            {

                if (row["TABLE_TYPE"] as string == "TABLE")
                {
                    TableInfo info = new TableInfo { TableName = row["TABLE_NAME"] as string };
                    infos_dict[info.TableName] = info;
                }
            }

            int i = 0;
            dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);
            foreach (DataRow row in dt.Rows)
            {
                string table_name = row["TABLE_NAME"] as string;
                if (infos_dict.ContainsKey(table_name))
                {
                    i++;
                    infos_dict[table_name].PrimaryKey = row["COLUMN_NAME"] as string;
                }
            }

            if (i != infos_dict.Count) throw new System.ApplicationException();

            dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);
            foreach (DataRow row in dt.Rows)
            {
                string fk_table_name = row["FK_TABLE_NAME"] as string;
                if (infos_dict.ContainsKey(fk_table_name))
                {
                    FKey key = new FKey
                    {
                        Name = row["FK_COLUMN_NAME"] as string,
                        Table = row["PK_TABLE_NAME"] as string,
                        Column = row["PK_COLUMN_NAME"] as string
                    };
                    infos_dict[fk_table_name].FKeys.Add(key);
                }
            }

            infos_dict["Users"] = infos_dict["USERS"];
            infos_dict.Remove("USERS");

            return infos_dict;
        }
Exemplo n.º 3
0
        private static DataTable GetExcelTable(string excelFilename)
        {
            string fileType = System.IO.Path.GetExtension(excelFilename);

            if (string.IsNullOrEmpty(fileType))
            {
                return(null);
            }
            string connectionString;

            if (fileType == ".xls")
            {
                connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1'", excelFilename);
            }
            else
            {
                connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'", excelFilename);
            }
            DataSet ds = new DataSet();
            string  tableName;

            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                connection.Open();
                DataTable table = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                tableName = table.Rows[0]["Table_Name"].ToString();
                tableName = tableName.Replace("'", "");
                string           strExcel = "select * from " + "[" + tableName + "A3:D]";
                OleDbDataAdapter adapter  = new OleDbDataAdapter(strExcel, connectionString);
                adapter.Fill(ds, tableName);
                connection.Close();
            }
            return(ds.Tables[tableName]);
        }
Exemplo n.º 4
0
        /// <summary>
        /// 读取Excel返回一个数据表
        /// </summary>
        /// <param name="con">OleDb数据连接</param>
        public DataTable ExcelToDataTable(OleDbConnection conn)
        {
            System.Data.DataTable dt = null;
            try
            {
                //打开连接
                if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                //获取Excel的第一个Sheet名称
                string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();

                //查询sheet中的数据
                string strSql = "select * from [" + sheetName + "]";
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];

                return dt;
            }
            catch (Exception exc)
            {
                throw exc;
                //MessageBox.Show(exc.Message);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
Exemplo n.º 5
0
    public DataSet GetDataSet(string filepath, string excelFileExtension)
    {
        try {
            System.Data.OleDb.OleDbConnection oledbcon = null;
            string strConn = string.Empty;
            switch (excelFileExtension.Trim()) {
                case "xls":
                    oledbcon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;MaxScanRows=0;\"");
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties=Excel 8.0;";
                    break;
                case "xlsx":
                    oledbcon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath
                   + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1'");
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath
                   + ";Extended Properties=Excel 12.0;";
                    break;
            }

            //excel
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
            string sheetName = dtSheetName.Rows[0]["TABLE_NAME"].ToString();
            System.Data.OleDb.OleDbDataAdapter oledbAdaptor = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", oledbcon);
            //select
            DataSet ds = new DataSet();
            oledbAdaptor.Fill(ds);
            oledbcon.Close();
            return ds;
        } catch (Exception ex) {
            throw ex;
        }
    }
Exemplo n.º 6
0
        public static void Main()
        {
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\ExcelDocs\trainers.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES/'";

            OleDbConnection dbConnection = new OleDbConnection(connectionString);

            dbConnection.Open();

            using (dbConnection)
            {
                var excelSchema = dbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                var sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();

                OleDbCommand command = new OleDbCommand("INSERT INTO [" + sheetName + "] VALUES (@name, @scores)", dbConnection);

                command.Parameters.AddWithValue("@name", "Pesho Goshov");
                command.Parameters.AddWithValue("@scores", "11");

                try
                {
                    for (var i = 0; i < 5; i++)
                    {
                        var queryResult = command.ExecuteNonQuery();
                        Console.WriteLine("({0} row(s) affected)", queryResult);
                    }
                }
                catch (OleDbException exception)
                {
                    Console.WriteLine("SQL Error occured: " + exception);
                }
            }
        }
Exemplo n.º 7
0
 public static DataSet ReadFile(string filepath)
 {
     var result = new DataSet();
     var connectionstring = GetConnectionString((filepath.Contains("xlsx") ? ExcelFormat.Excel2007 : ExcelFormat.Excel2003), filepath);
     using (var connection = new OleDbConnection(connectionstring))
     {
         try
         {
             connection.Open();
             using (var datatable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
             {
                 if (datatable.Rows.Count == 0)
                     throw new Exception("Excel file doesn't contain sheet");
                 var sheetname = datatable.Rows[0]["TABLE_NAME"].ToString();
                 /// Only read First Sheet
                 using (var adapter = new OleDbDataAdapter(string.Format("select * from [{0}]", sheetname), connection))
                 {
                     adapter.Fill(result);
                 }
             }
         }
         catch (Exception err)
         {
             throw err;
         }
         finally
         {
             connection.Close();
         }
     }
     return result;
 }
Exemplo n.º 8
0
        public static List<string> GetWorksheetList(string filePath)
        {
            List<string> result = new List<string>();

            if (FileHelper.CheckFileExists(filePath))
            {
                DataTable temp = null;

                using (OleDbConnection objOleDbConnection =
                    new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + filePath))
                {
                    objOleDbConnection.Open();

                    temp = objOleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                    objOleDbConnection.Close();
                }

                for (int i = 0; i < temp.Rows.Count; i++)
                {
                    string name = temp.Rows[i][2].ToString().Trim().TrimEnd('$');

                    if (!result.Contains(name))
                    {
                        result.Add(name);
                    }
                }
            }

            return result;
        }
Exemplo n.º 9
0
        static void Main()
        {
            string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=../../score_db.xlsx;Extended Properties='Excel 12.0 xml;HDR=Yes';";
            var excelConnection = new OleDbConnection(excelConnectionString);
            excelConnection.Open();

            DataTable excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();

            Console.Write("Username: "******"Score: ");
            int score = int.Parse(Console.ReadLine());

            OleDbCommand excelCommand = new OleDbCommand(@"INSERT INTO [" + sheetName + @"]
                                                           VALUES (@name, @score)", excelConnection);

            excelCommand.Parameters.AddWithValue("@name", userName);
            excelCommand.Parameters.AddWithValue("@age", score);

            using (excelConnection)
            {
                Console.WriteLine("\nINSERTING INTO EXCEL FILE DATABASE");
                Console.WriteLine("-----------------------------------\n");
                var queryResult = excelCommand.ExecuteNonQuery();
                Console.WriteLine("({0} row(s) affected)", queryResult);
            }
        }
Exemplo n.º 10
0
    public DataSet GetExcelDistictBTNCOunt_Sales(string sFileName)
    {
        Int32 NORec;
        DataSet objDataset1 = new DataSet();
        int count = 0;
        OleDbConnection objConn = new OleDbConnection();
        try
        {
            //Excell connection
            string Xls_Con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFileName + ";Extended Properties=\"Excel 8.0;HDR=YES; IMEX=1;ImportMixedTypes=Text\"";
            objConn.ConnectionString = Xls_Con;
            //Dim objConn As New OleDbConnection(Xls_Con)
            objConn.Open();
            DataTable ExcelSheets = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            string SpreadSheetName = "[" + ExcelSheets.Rows[0]["TABLE_NAME"].ToString() + "]";

            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter("SELECT distinct([CPhone]),SaleDate FROM " + SpreadSheetName + "", objConn);

            objAdapter1.Fill(objDataset1, "XLData");

            //count = Convert.ToInt32(objDataset1.Tables[0].Rows.Count);
            objConn.Close();
        }
        catch (Exception ex)
        {

        }
        return objDataset1;
    }
Exemplo n.º 11
0
        public static IEnumerable<DataRow> GetRowsFromDataSheets(OleDbConnection connection)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("Must provide a valid OleDbConnection object.", "connection");
            }
            
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            
            DataTable schema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            var allDataRows = new List<DataRow>();

            foreach (DataRow sheet in schema.Rows)
            {
                string sheetName = sheet.Field<string>("TABLE_NAME");

                DataTable sheetData = new DataTable();

                OleDbDataAdapter sheetAdapter = new OleDbDataAdapter(String.Format("select * from [{0}]", sheetName), connection);

                sheetAdapter.Fill(sheetData);

                var sheetDataRows = sheetData.AsEnumerable();

                allDataRows.AddRange(sheetDataRows);
            }

            connection.Close();

            return allDataRows;
        }
Exemplo n.º 12
0
        static void Main()
        {
            var connectionStringFor2007OrNewer = "Provider = Microsoft.ACE.OLEDB.12.0; Extended Properties = Excel 12.0 XML; Data Source = ../../../scores.xlsx;";
            //var connectionStringForOlder = "Provider = Microsoft.Jet.OLEDB.4.0; Extended Properties = Excel 8.0; Data Source = ../../../scores.xlsx;";

            using (var dbCon = new OleDbConnection(connectionStringFor2007OrNewer))
            {
                dbCon.Open();
                var docName = dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                var cmd = new OleDbCommand("SELECT * FROM [" + docName + "]", dbCon);

                using (var oleDbAdapter = new OleDbDataAdapter(cmd))
                {
                    var dataSet = new DataSet();
                    oleDbAdapter.Fill(dataSet);

                    using (var reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            var name = reader["Name"];
                            var score = reader["Score"];
                            Console.WriteLine("{0}: {1}", name, score);
                        }
                    }
                }
            }
        }
        public List<Destination> SelectExcelFilesFromZip(string path)
        {
            var destinations = new List<Destination>();
            using (ZipArchive archive = ZipFile.Open(path, ZipArchiveMode.Update))
            {
                foreach (ZipArchiveEntry entry in archive.Entries)
                {
                    if (entry.FullName.EndsWith(".xlsx"))
                    {
                        entry.ExtractToFile(Path.Combine(extractPath, entry.Name));
                        string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(extractPath, entry.Name) + ";Extended Properties='Excel 12.0 xml;HDR=Yes';";

                        OleDbConnection connection = new OleDbConnection(connectionString);

                        using (connection)
                        {
                            connection.Open();
                            var excelSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                            var sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();
                            destinations = this.ReadExcelData(connection, sheetName);
                        }
                    }
                }

                return destinations;
            }
        }
        public void ReadExcelSheet(string fileName, string sheetName, Action<DataTableReader> actionForEachRow)
        {
            var connectionString = string.Format(ExcelSettings.Default.ExcelConnectionString, fileName);

            using (var excelConnection = new OleDbConnection(connectionString))
            {
                excelConnection.Open();

                if (sheetName == null)
                {
                    var excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (excelSchema != null)
                    {
                        sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();
                    }
                }

                var excelDbCommand = new OleDbCommand(@"SELECT * FROM [" + sheetName + "]", excelConnection);

                using (var oleDbDataAdapter = new OleDbDataAdapter(excelDbCommand))
                {
                    var dataSet = new DataSet();
                    oleDbDataAdapter.Fill(dataSet);

                    using (var reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            actionForEachRow(reader);
                        }
                    }
                }
            }
        }
        public static void WriteInExcel(string testerName, string placeName, string placeArea, float slope, string date, string testResult)
        {
            OleDbConnection excelConnection = new OleDbConnection(OutputFileConnectionString);

            using (excelConnection)
            {
                excelConnection.Open();

                DataTable tableSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                string sheetName = tableSchema.Rows[0]["TABLE_NAME"].ToString();

                OleDbCommand excelDbCommand = new OleDbCommand(
                    "INSERT INTO [" + sheetName + "] VALUES (@TesterName, @PlaceName, @PlaceArea, @Slope, @Date, @TestResult)", excelConnection);

                excelDbCommand.Parameters.AddWithValue("@TesterName", testerName);
                excelDbCommand.Parameters.AddWithValue("@PlaceName", placeName);
                excelDbCommand.Parameters.AddWithValue("@PlaceArea", placeArea);
                excelDbCommand.Parameters.AddWithValue("@Slope", slope);
                excelDbCommand.Parameters.AddWithValue("@Date", date);
                excelDbCommand.Parameters.AddWithValue("@testResult", testResult);

                excelDbCommand.ExecuteNonQuery();

                Console.WriteLine("Added new row in Excel output file!");
            }
        }
Exemplo n.º 16
0
        /// <summary>
        /// 读取Excel文件的所有Sheet
        /// </summary>
        /// <param name="filepath">文件路径</param>
        /// <param name="filename">文件名称</param>
        /// <returns>DataTable</returns>
        public static DataTable ReadExcelSheets(string fileName)
        {
            DataTable dt = new DataTable();

            System.Data.DataSet itemDS = new DataSet();
            if (fileName.Trim().ToUpper().EndsWith("XLS") || fileName.Trim().ToUpper().EndsWith("XLSX"))
            {
                string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HdR=NO;\"";
                System.Data.OleDb.OleDbConnection conn = null;
                try
                {
                    conn = new System.Data.OleDb.OleDbConnection();
                    conn.ConnectionString = connStr;
                    conn.Open();
                    dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                }
                catch (Exception ex)
                { }
                finally
                {
                    //释放
                    conn.Close();
                }
                //创建连接
            }
            return(dt);
        }
Exemplo n.º 17
0
        /// <summary>
        /// 解析Excel到DataTable
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataTable GetDataTabelExcelContent(string filePath)
        {
            //excel2007,兼容2003
            string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';";

            System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
            myConn.Open();
            //获取excel第一标签名
            DataTable    dt          = new DataTable();
            DataTable    schemaTable = myConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string       tableName   = schemaTable.Rows[0][2].ToString().Trim(); //标签名
            string       strCom      = "SELECT *  FROM [" + tableName + "]";     //查询语句
            OleDbCommand ocmd        = null;

            try
            {
                ocmd = new OleDbCommand(strCom, myConn);
                OleDbDataAdapter oda = new OleDbDataAdapter(ocmd);
                oda.Fill(dt);
            }
            catch (OleDbException oex)
            {
                dt = null;
                throw oex;
            }
            finally
            {
                myConn.Close();
                ocmd.Dispose();
            }
            return(dt);
        }
Exemplo n.º 18
0
    /// <summary>
    /// 取出sheet
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns></returns>
    public string getFirstSheetName(string filePath)
    {
        string ret     = "";
        string strConn = sConnstring;

        //"Extended Properties=Excel 8.0;";

        strConn = string.Format(strConn, filePath);

        System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
        conn.Open();
        using (DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
        {
            ////取得工作表數量,法一
            //foreach (DataRow dr in dt.Rows)
            //{
            //    Console.WriteLine((String)dr["TABLE_NAME"]);
            //}
            //取得工作表數量,法二
            int TableCount = dt.Rows.Count;
            for (int i = 0; i < TableCount; i++)
            {
                ret = dt.Rows[i][2].ToString().Trim().TrimEnd('$');
                conn.Close();
                return(ret);
            }
        }
        conn.Close();
        return(ret);
    }
Exemplo n.º 19
0
        /// <summary>  
        /// 获取Excel文件数据表列表  
        /// </summary>  
        public static ArrayList GetExcelTables(string ExcelFileName)
        {
            DataTable dt = new DataTable();
            ArrayList TablesList = new ArrayList();
            if (File.Exists(ExcelFileName))
            {
                using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName))
                {
                    try
                    {
                        conn.Open();
                        dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    }
                    catch (Exception exp)
                    {
                        throw exp;
                    }

                    //获取数据表个数  
                    int tablecount = dt.Rows.Count;
                    for (int i = 0; i < tablecount; i++)
                    {
                        string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');
                        if (TablesList.IndexOf(tablename) < 0)
                        {
                            TablesList.Add(tablename);
                        }
                    }
                }
            }
            return TablesList;
        }
Exemplo n.º 20
0
        /// <summary>
        /// 连接ACCESS数据库
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns>数据表表名</returns>
        public string[] ACSconection(string filePath)
        {
            string[] strTable = null;

            try
            {
                acsCon = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0; Data Source= " + filePath);
                acsCon.Open();
                DataTable shemaTable = acsCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                int n = shemaTable.Rows.Count;
                strTable = new string[n];
                int m = shemaTable.Columns.IndexOf("TABLE_NAME");
                for (int i = 0; i < n; i++)
                {
                    DataRow m_DataRow = shemaTable.Rows[i];
                    strTable[i] = m_DataRow.ItemArray.GetValue(m).ToString();
                }

                return strTable;
            }
            catch
            {
                return strTable;
            }
        }
Exemplo n.º 21
0
 public DataView ExceltoDataView(string strFilePath)
 {
     DataView dv;
     try
     {
         OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + strFilePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'");
         conn.Open();
         object[] CSs0s0001 = new object[4];
         CSs0s0001[3] = "TABLE";
         DataTable tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, CSs0s0001);
         string tableName = Convert.ToString(tblSchema.Rows[0]["TABLE_NAME"]);
         if (tblSchema.Rows.Count > 1)
         {
             tableName = "sheet1$";
         }
         string sql_F = "SELECT * FROM [{0}]";
         OleDbDataAdapter adp = new OleDbDataAdapter(string.Format(sql_F, tableName), conn);
         DataSet ds = new DataSet();
         adp.Fill(ds, "Excel");
         dv = ds.Tables[0].DefaultView;
         conn.Close();
     }
     catch (Exception)
     {
         Exception strEx = new Exception("請確認是否使用模板上傳(上傳的Excel中第一個工作表名稱是否為Sheet1)");
         throw strEx;
     }
     return dv;
 }
Exemplo n.º 22
0
        public void loads(string SlnoAbbreviation)
        {
            string path = System.IO.Path.GetFullPath(@SlnoAbbreviation);

            oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
              path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
            oledbConn.Open();
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            DataSet ds = new DataSet();

            cmd.Connection = oledbConn;
            cmd.CommandType = CommandType.Text;

            dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (dt == null)
            {

            }

            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;

            foreach (DataRow row in dt.Rows)
            {
                excelSheets[i] = row["TABLE_NAME"].ToString();
                i++;
            }
            for (int j = 0; j < excelSheets.Length; j++)
            {
                drfile.Items.Add(excelSheets[j]);
            }
            drfile.Items.Insert(0, new ListItem("~Select~", "0"));
        }
Exemplo n.º 23
0
        public static DataTable ExcelToTable(string excelFilename, bool isTitleOrDataOfFirstRow)
        {
            string HDR = string.Empty;//如果第一行是数据而不是标题的话, 应该写: "HDR=No;"

            if (isTitleOrDataOfFirstRow)
            {
                HDR = "YES";//第一行是标题
            }
            else
            {
                HDR = "NO";//第一行是数据
            }
            string  connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=35;Extended Properties=Excel 8.0;HDR=\"{1}\";Persist Security Info=False", excelFilename, HDR);
            DataSet ds = new DataSet();
            string  tableName;

            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                connection.Open();
                DataTable table = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                tableName = table.Rows[0]["Table_Name"].ToString();
                string           strExcel = "select * from " + "[" + tableName + "]";
                OleDbDataAdapter adapter  = new OleDbDataAdapter(strExcel, connectionString);
                adapter.Fill(ds, tableName);
                connection.Close();
            }
            return(ds.Tables[tableName]);
        }
Exemplo n.º 24
0
        static void Main()
        {
            string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=../../score_db.xlsx;Extended Properties='Excel 12.0 xml;HDR=Yes';";
            var excelConnection = new OleDbConnection(excelConnectionString);
            excelConnection.Open();

            DataTable excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();

            OleDbCommand excelCommand = new OleDbCommand("SELECT * FROM [" + sheetName + "]", excelConnection);

            using (excelConnection)
            {
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(excelCommand))
                {
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);

                    using (DataTableReader reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            var userName = reader["Name"];
                            var score = reader["Score"];

                            Console.WriteLine(userName + " -> " + score);
                        }
                    }
                }
            }
        }
Exemplo n.º 25
0
 private static async Task<DataSet> SetDataSet(string path)
 {
     var ds = new DataSet();
     var connectString = "";
     //connection strings for excel files from and rest code of this method based on
     //http://www.aspsnippets.com/Articles/Read-and-Import-Excel-File-into-DataSet-or-DataTable-using-C-and-VBNet-in-ASPNet.aspx
     if (Path.GetExtension(path).Equals(".xlsx"))
         connectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";
     else if (Path.GetExtension(path).Equals(".xls"))
         connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0 Xml;HDR=YES;IMEX=1;\"";
     using (var oleDbConnection = new OleDbConnection(connectString))
     {
         await oleDbConnection.OpenAsync();
         var dt = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
         if (dt == null)
             return null;
         var excelSheets = new String[dt.Rows.Count];
         var t = 0;
         foreach (DataRow row in dt.Rows)
         {
             excelSheets[t] = row["TABLE_NAME"].ToString();
             t++;
         }
         var query = string.Format("Select * from [{0}]", excelSheets[0]);
         using (var dataAdapter = new OleDbDataAdapter(query, oleDbConnection))
         {
             dataAdapter.Fill(ds);
         }
         oleDbConnection.Close();
     }
     return ds;
 }
Exemplo n.º 26
0
        public List<List<object>> Parse(string filename)
        {
            const string fileType = ".xlsx";
            var fileFullName = HttpContext.Current.Server.MapPath("~/Storage/" + filename + fileType);
            var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";

            var adapter = new OleDbDataAdapter();
            var conn = new OleDbConnection(connectionString);
            conn.Open();

            DataTable excelSheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                new object[] { null, null, null, "TABLE" });

            const int workSheetNumber = 0;
            string spreadSheetName = excelSheets.Rows[workSheetNumber]["TABLE_NAME"].ToString();

            string strQuery = "select * from [" + spreadSheetName + "] ";
            adapter.SelectCommand = new OleDbCommand(strQuery, conn);
            var dsExcel = new DataSet();
            adapter.Fill(dsExcel);
            conn.Close();
            var result = new List<List<object>>();
            var listDataRow = dsExcel.Tables[0].Rows.Cast<DataRow>().ToList();
            foreach (var item in listDataRow)
            {
                var row = new List<object>();
                for (var i = 0; i < item.ItemArray.Count(); i++)
                {
                    var value = (item.ItemArray[i] == DBNull.Value) ? null : item.ItemArray[i];
                    row.Add(value);
                }
                result.Add(row);
            }
            return result;
        }
Exemplo n.º 27
0
        private static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
        {
            string HDR = hasHeaders ? "Yes" : "No";
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                             FileName + ";Extended Properties=\"Excel 8.0;HDR=" +
                             HDR + ";IMEX=1\"";

            DataSet output = new DataSet();

            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();

                DataTable schemaTable = conn.GetOleDbSchemaTable(
                  OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                foreach (DataRow schemaRow in schemaTable.Rows)
                {
                    string sheet = schemaRow["TABLE_NAME"].ToString();

                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                    cmd.CommandType = CommandType.Text;

                    DataTable outputTable = new DataTable(sheet);
                    output.Tables.Add(outputTable);
                    new OleDbDataAdapter(cmd).Fill(outputTable);
                }
            }
            return output;
        }
Exemplo n.º 28
0
        /// <summary>
        /// 从EXCEL中获取数据(放入dataset中)
        /// </summary>
        /// <param name="postedFile"></param>
        /// <param name="context"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static DataSet GetDataFromUploadFile(this HttpPostedFile postedFile, HttpContext context, string tableName)
        {
            string directory = context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY);

            if (!Directory.Exists(directory))
                Directory.CreateDirectory(directory);

            string filename = postedFile.FileName;
            //将文件上传至服务器
            postedFile.SaveAs(context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename);

            string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";

            //string sqlin = "SELECT * FROM [" + "ConstructPlanDevice" + "$]";
            //OleDbCommand oleCommand = new OleDbCommand(sqlin, new OleDbConnection(conn));

            //OleDbDataAdapter adapterIn = new OleDbDataAdapter(oleCommand);
            //DataSet dsIn = new DataSet();
            //adapterIn.Fill(dsIn, tableName);

            OleDbConnection conn1 = new OleDbConnection(conn);
            conn1.Open();
            string name = conn1.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][2].ToString().Trim();
            OleDbDataAdapter odda = new OleDbDataAdapter("select * from ["+name+"]", conn1);
            DataSet dsIn1 = new DataSet();
            odda.Fill(dsIn1, tableName);
            conn1.Close();
            return dsIn1;
        }
Exemplo n.º 29
0
        public static List<string> GetWorksheetColumnList(string filePath, string worksheetName)
        {
            List<string> result = new List<string>();

            if (FileHelper.CheckFileExists(filePath))
            {
                DataTable temp = null;

                using (OleDbConnection objOleDbConnection =
                    new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + filePath))
                {
                    objOleDbConnection.Open();

                    temp = objOleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, worksheetName, null });

                    objOleDbConnection.Close();
                }

                for (int i = 0; i < temp.Rows.Count; i++)
                {
                    result.Add(temp.Rows[i]["Column_Name"].ToString().Trim());
                }
            }

            return result;
        }
Exemplo n.º 30
0
 /// <summary>
 /// Get Metadata information about the tables in a schema in the current database
 /// </summary>
 /// <param name="schema">Name of the schema in the database.</param>
 /// <returns></returns>
 public override SchemaTablesMetaData QuerySchemaDefinition(string schema)
 {
     SchemaTablesMetaData result = new SchemaTablesMetaData();
     result.schemaName = schema;
     try
     {
         using (OleDbConnection connector = new OleDbConnection(connectionString))
         {
             connector.Open();
             using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, schema, null, "TABLE" }))
             {
                 foreach (DataRow row in dt.Rows)
                 {
                     TableMetaData table = new TableMetaData();
                     table.tableName = row[2].ToString();
                     result.AddTable(table);
                 }
             }
             connector.Close();
         }
     }
     catch (OleDbException ex)
     {
         Console.Out.WriteLine("Exception fetching schema metadata: {0}", ex.Message);
     }
     return result;
 }
Exemplo n.º 31
0
        //加载Excel
        public static DataSet LoadExcel(string filePath)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();

                DataTable OleTables = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] {null, null, null, "TABLE"});
                DataSet OleDsExcle = new DataSet();

                foreach (DataRow dr in OleTables.Rows)
                {
                    String TableName = (String)dr["TABLE_NAME"];

                    String sql = String.Format("SELECT * FROM  [{0}]",TableName);

                    OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);

                    OleDaExcel.Fill(OleDsExcle, TableName);
                }
                OleConn.Close();
                return OleDsExcle;
            }
            catch
            {
                return null;
            }
        }
Exemplo n.º 32
0
        public List<SheetInfo> GetSheetlist()
        {
            if (excelpath == null||excelpath.Length<=0)
                return null;
            List<SheetInfo> list = new List<SheetInfo>();
            string connStr = "";
            string sql_F = "Select * FROM [{0}]";
            string fileType = System.IO.Path.GetExtension(excelpath);
            if (fileType == ".xls")
                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelpath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            else
                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelpath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";

            OleDbConnection conn = new OleDbConnection(connStr);
            OleDbDataAdapter da = null;
            try
            {
                conn.Open();
                string sheetname = "";
                DataTable dtSheetName = 
                    conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                da = new OleDbDataAdapter();
                for (int i = 0; i < dtSheetName.Rows.Count;i++ )
                {
                    sheetname = (string)dtSheetName.Rows[i]["TABLE_NAME"];
                    if (sheetname.Contains("$") )
                    {
                        SheetInfo info = new SheetInfo();
                        info.SheetName = sheetname.Replace("$", "");

                        da.SelectCommand = new OleDbCommand(String.Format(sql_F, sheetname), conn);
                        DataSet dsItem = new DataSet();
                        da.Fill(dsItem, sheetname);
                        int cnum = dsItem.Tables[0].Columns.Count;
                        int rnum = dsItem.Tables[0].Rows.Count;
                        info.StartRange = "A1";
                        char c = (char)('A' + cnum - 1);
                        info.EndRange = c + Convert.ToString(rnum);
                        list.Add(info);
                    }
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "错误消息");
                return null; 
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    if(da!=null)
                        da.Dispose();
                    conn.Dispose();
                }
            }

            return list;
        }
    public static DataTable LoadExcelToDataTable(string filename)
    {
        DataTable dtResult = new DataTable();
        //连接字符串
        string sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=Excel 12.0;";

        //string sConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + @";Extended Properties=""Excel 12.0;HDR=YES;""";

        //String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
        OleDbConnection myConn = new OleDbConnection(sConnectionString);
        myConn.Open();
        DataTable sheetNames = myConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        string sheetName = (sheetNames.Rows.Count > 0) ? sheetNames.Rows[0][2].ToString() : "";

        if (String.IsNullOrEmpty(sheetName))
        {
            return dtResult;
        }
        string strCom = " SELECT * FROM [" + sheetName + "]";

        OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
        myCommand.Fill(dtResult);
        myConn.Close();
        return dtResult;
    }
Exemplo n.º 34
0
        /// <summary>
        /// Read the specified Excel file and returns the content
        /// </summary>
        /// <param name="excelFile"></param>
        /// <returns></returns>
        public DataTable Read(string excelFile)
        {
            string connectionString = string.Empty;

            string fileExtension = Path.GetExtension(excelFile);
            if (fileExtension == ".xls")
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
            }
            else if (fileExtension == ".xlsx")
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";
            }

            using (var conn = new OleDbConnection(connectionString))
            {
                conn.Open();

                using (OleDbCommand command = conn.CreateCommand())
                {
                    DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    string sheetName = dtSheet.Rows[0]["TABLE_NAME"].ToString();

                    command.CommandText = string.Format("SELECT * FROM [{0}]", sheetName);

                    using (OleDbDataAdapter da = new OleDbDataAdapter(command))
                    {
                        var dt = new DataTable();

                        da.Fill(dt);
                        return dt;
                    }
                }
            }
        }
Exemplo n.º 35
0
    /// <summary>
    /// 取出sheet
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns></returns>
    public string getFirstSheetName(string filePath)
    {
        string ret     = "";
        string strConn = "Driver={Microsoft Text Driver (*.txt; *.csv)}; " +
                         "Dbq= " + filePath + ";Extensions=csv,txt ";

        strConn = string.Format(strConn, filePath);

        System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
        conn.Open();
        using (DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
        {
            ////取得工作表數量,法一
            //foreach (DataRow dr in dt.Rows)
            //{
            //    Console.WriteLine((String)dr["TABLE_NAME"]);
            //}
            //取得工作表數量,法二
            int TableCount = dt.Rows.Count;
            for (int i = 0; i < TableCount; i++)
            {
                ret = dt.Rows[i][2].ToString().Trim().TrimEnd('$');
                conn.Close();
                return(ret);
            }
        }
        conn.Close();
        return(ret);
    }
Exemplo n.º 36
0
        //读取数据
        private string ReadFile(string filePath, ref DataTable dtSource)
        {
            string strCon = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1'", filePath);

            System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
            try
            {
                Conn.Open();
                var    Sheet1     = "Sheet1";
                string sSheetName = "";

                if (String.IsNullOrEmpty(Sheet1))
                {
                    List <string> lstSheetNames = new List <string>();
                    DataTable     dtSheets      = new DataTable();
                    dtSheets = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                    for (int i = 0; i < dtSheets.Rows.Count; i++)
                    {
                        if (!dtSheets.Rows[i]["TABLE_NAME"].ToString().Contains("_"))
                        {
                            lstSheetNames.Add(dtSheets.Rows[i]["TABLE_NAME"].ToString());
                        }
                    }
                    sSheetName = lstSheetNames[0];
                }
                else
                {
                    sSheetName = Sheet1 + "$";
                }

                DataSet ds     = new DataSet();
                string  strCom = String.Format("SELECT * FROM [{0}]", sSheetName);
                System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
                myCommand.Fill(ds, "ExcelTable");
                dtSource = ds.Tables[0];
            }
            catch (Exception e)
            {
                return("读取文件数据时出错:" + e.Message);
            }
            finally
            {
                Conn.Close();
                try
                {
                    FileInfo fi = new FileInfo(filePath); //删除临时文件
                    fi.Delete();
                }
                catch {}
            }

            return("");
        }
        public DataTable ReadTableExcel(string strFileName)
        {
            string ConnectionString = "";

            System.Data.OleDb.OleDbDataAdapter adapter;
            System.Data.DataSet ds = new System.Data.DataSet();

            try
            {
                if (strFileName.Trim().EndsWith(".xlsx"))
                {
                    ConnectionString =
                        string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", strFileName);
                }
                else if (strFileName.Trim().EndsWith(".xls"))
                {
                    try
                    {
                        ConnectionString =
                            string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFileName);
                    }
                    catch (Exception)
                    {
                        ConnectionString =
                            string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", strFileName);
                    }
                }

                System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString);
                conn.Open();
                DataTable sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                foreach (DataRow dr in sheets.Rows)
                {
                    string sht = dr[2].ToString().Replace("'", "");
                    adapter = new OleDbDataAdapter("select * from [" + sht + "]", conn);
                    adapter.Fill(ds);

                    if (conn != null)
                    {
                        adapter.Dispose();
                        conn.Close();
                    }
                    break;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            return(ds.Tables[0]);
        }
        public static string[] GetExcelWorksheets(string sFilePath)
        {
            System.Data.OleDb.OleDbConnection oleConnection = null;
            string[] sWorksheets = null;

            try
            {
                oleConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + sFilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"");
                oleConnection.Open();

                //select just TABLE in the Object array of restrictions.
                //Remove TABLE and insert Null to see tables, views, and other objects.
                object[]  objArrRestrict = new object[] { null, null, null, "TABLE" };
                DataTable schemaTbl      = oleConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, objArrRestrict);

                ArrayList aryTableNames = new ArrayList();
                // Display the table name from each row in the schema
                foreach (DataRow row in schemaTbl.Rows)
                {
                    string sTable = Convert.ToString(row["TABLE_NAME"]).Trim();
                    if (sTable.IndexOf("$_") < 0)
                    {
                        aryTableNames.Add(sTable);
                    }
                }
                oleConnection.Close();

                if (aryTableNames.Count <= 0)
                {
                    throw new Exception("Excel spreadsheet contains no valid worksheets!");
                }

                sWorksheets = (string[])aryTableNames.ToArray(typeof(string));
            }
            catch (Exception ex)
            {
                string sErrorMessage = ex.Message;
                if (ex.InnerException != null)
                {
                    sErrorMessage = sErrorMessage + "  Details: " + ex.InnerException.Message;
                }

                throw new Exception(sErrorMessage);
            }
            finally
            {
                oleConnection.Close();
            }

            return(sWorksheets);
        }
Exemplo n.º 39
0
        private object GetPrimaryKeyContrantName(SqlHelper sqlHelper, string dataBaseName, string tableName)
        {
            var con = new System.Data.OleDb.OleDbConnection(sqlHelper.DbConnectionString);

            con.Open();
            var schemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Primary_Keys,
                                                      new Object[] { null, null, tableName });

            con.Close();
            object pk = null;

            pk = schemaTable == null ? null : schemaTable.Rows[0].ItemArray[7].ToString();
            return(pk ?? "");
        }
Exemplo n.º 40
0
        /// <summary>
        /// 将线路站点数据导入导入到系统中
        /// </summary>
        /// <param name="FileName"></param>
        public static void importTrainLineDataToSystem(String FileName)
        {
            String FileName1 = HttpContext.Current.Server.MapPath("~/Attachment/" + FileName);

            if (File.Exists(FileName1))
            {
                //得到线路站点的数据
                JTable tab1 = new JTable("TrainLine");
                tab1.OrderBy = "LineName";
                DataTable dt0 = tab1.SearchData(null, -1, "*").Tables[0];


                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName1 + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
                System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);

                Conn.Open();
                DataTable dt = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    String tableName = dt.Rows[i][2].ToString().Trim();
                    foreach (DataRow dr0 in dt0.Rows)
                    {
                        String AStation = dr0["AStation"].ToString();
                        String BStation = dr0["BStation"].ToString();
                        int    miles0   = int.Parse(dr0["Miles"].ToString());

                        DataTable dt1 = GetLineStationTable(Conn, tableName, dr0["LineName"].ToString(),
                                                            out AStation, out BStation, out miles0);

                        if (dt1.Rows.Count > 0)   //表示有数据
                        {
                            dr0["Astation"] = AStation;
                            dr0["Bstation"] = BStation;
                            dr0["miles"]    = miles0;

                            //更新线路中的数据
                            Line.SetMiddleStation(dr0["LineID"].ToString(), dt1);
                        }
                    }
                    tab1.Update(dt0);       //更新老站点中的相关数据
                }


                Conn.Close();

                //得到线路的数据
                tab1.Close();
            }
        }
Exemplo n.º 41
0
        /// <summary>
        /// Get all available table names from .xls data source
        /// </summary>
        public List <String> RetrieveAllTables()
        {
            // clear the old tables list firstly
            m_tables.Clear();

            // get all table names from data source
            DataTable schemaTable = m_objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                                                  new object[] { null, null, null, "TABLE" });

            for (int i = 0; i < schemaTable.Rows.Count; i++)
            {
                m_tables.Add(schemaTable.Rows[i].ItemArray[2].ToString().TrimEnd('$'));
            }


            return(m_tables);
        }
Exemplo n.º 42
0
        /// <summary>
        /// 读取Excel文件
        /// </summary>
        /// <param name="filepath">文件路径</param>
        /// <returns>DataTable</returns>
        public static DataTable ReadExcel(string filename, bool isHdR)
        {
            string hdr = "N0";

            if (isHdR)
            {
                hdr = "YEX";
            }
            System.Data.DataSet itemDS = new DataSet();
            if (filename.Trim().ToUpper().EndsWith("XLS") || filename.Trim().ToUpper().EndsWith("XLSX"))
            {
                string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;HdR=" + hdr + ";\"";
                System.Data.OleDb.OleDbConnection conn       = null;
                System.Data.OleDb.OleDbCommand    oledbCommd = null;
                try
                {
                    conn = new System.Data.OleDb.OleDbConnection();
                    conn.ConnectionString = connStr;
                    conn.Open();
                    DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                    //判断连接Excel sheet名
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr      = dt.Rows[i];
                        string  sqlText = "select * from [" + dr["TABLE_NAME"] + "]";
                        oledbCommd = new System.Data.OleDb.OleDbCommand(sqlText, conn);
                        oledbCommd.CommandTimeout = 100000;
                        //执行
                        System.Data.OleDb.OleDbDataAdapter oledbDA = new System.Data.OleDb.OleDbDataAdapter(oledbCommd);
                        oledbDA.Fill(itemDS);
                    }
                }
                catch
                { }
                finally
                {
                    //释放
                    oledbCommd.Dispose();
                    conn.Close();
                }
                //创建连接
            }
            return(itemDS.Tables[0]);
        }
Exemplo n.º 43
0
        //加载Excel
        public static DataTable getExcel(string filePos)
        {
            string  connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=35;Extended Properties=Excel 8.0;Persist Security Info=False", filePos);
            DataSet ds = new DataSet();
            string  tableName;

            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                connection.Open();
                DataTable table = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                tableName = table.Rows[0]["Table_Name"].ToString();
                string           strExcel = "select * from " + "[" + tableName + "]";
                OleDbDataAdapter adapter  = new OleDbDataAdapter(strExcel, connectionString);
                adapter.Fill(ds, tableName);
                connection.Close();
            }
            return(ds.Tables[tableName]);
        }
Exemplo n.º 44
0
        public object GetPrimaryKey(SqlHelper sqlHelper, string dataBaseName, string tableName)
        {
            var con = new System.Data.OleDb.OleDbConnection(sqlHelper.DbConnectionString);

            con.Open();
            var schemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Primary_Keys,
                                                      new Object[] { null, null, tableName });

            con.Close();
            if (schemaTable == null || schemaTable.Rows.Count == 0)
            {
                return("");
            }
            object pk = null;

            pk = schemaTable.Rows[0].ItemArray[3].ToString().FirstLetterToUpper();
            return(pk);
        }
Exemplo n.º 45
0
        private void LoadExcelFile()
        {
            if (String.IsNullOrEmpty(FileName))
            {
                throw new Exception("Select file");
            }

            OleDbConnection  connExcel = new OleDbConnection();
            OleDbCommand     cmdExcel  = new OleDbCommand();
            OleDbDataAdapter oda       = new OleDbDataAdapter();

            connExcel           = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + FileName + ";Extended Properties=Excel 12.0;");
            cmdExcel.Connection = connExcel;
            connExcel.Open();
            System.Data.DataTable dt = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (dt == null)
            {
                throw new Exception("File read error");
            }

            foreach (DataRow row in dt.Rows)
            {
                string t = row["TABLE_NAME"].ToString();
                EI.Add(new ExcelInfo {
                    ID = t, Sheet = CleanUpSheetName(t)
                });
            }

            CleanUpSheetName(EI[0].ID);

            connExcel.Close();

            GridControl1.BeginUpdate();
            GridControl1.DataSource = null;
            GridView1.Columns.Clear();

            GridControl1.DataSource = Ext.ToDataTable(EI);

            GridView1.Columns["ID"].Visible = false;

            GridView1.ClearSelection();
            GridControl1.EndUpdate();
        }
Exemplo n.º 46
0
        public void GetTable(string Apath, ComboBox ComBox)
        {
            string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Apath + ";Persist Security Info=True";

            System.Data.OleDb.OleDbConnection tem_OleConn = new System.Data.OleDb.OleDbConnection(connstr);
            tem_OleConn.Open();
            DataTable tem_DataTable = tem_OleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            tem_OleConn.Close();
            ComBox.Items.Clear();
            for (int i = 0; i < tem_DataTable.Rows.Count; i++)
            {
                ComBox.Items.Add(tem_DataTable.Rows[i][2]);
            }
            if (ComBox.Items.Count > 0)
            {
                ComBox.SelectedIndex = 0;
            }
        }
Exemplo n.º 47
0
        public static DataTable import(string arquivo)
        {
            string ext     = string.Empty;
            string aspas   = "******"";
            string Conexao = string.Empty;

            for (int i = arquivo.Length - 1; i < arquivo.Length; i--)
            {
                if (arquivo[i] != '.')
                {
                    ext += arquivo[i];
                }
                else
                {
                    ext += "."; break;
                }
            }
            ext = Reverse(ext);
            if (ext == ".xls")
            {
                Conexao = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + arquivo + ";" + "Extended Properties=" + aspas + "Excel 8.0;HDR=YES" + aspas;
            }
            if (ext == ".xlsx")
            {
                Conexao = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + arquivo + ";" + "Extended Properties=" + aspas + "Excel 12.0;HDR=YES" + aspas;
            }
            System.Data.OleDb.OleDbConnection Cn = new System.Data.OleDb.OleDbConnection();
            Cn.ConnectionString = Conexao; Cn.Open();
            object[]  Restricoes = { null, null, null, "TABLE" };
            DataTable DTSchema   = Cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, Restricoes);

            if (DTSchema.Rows.Count > 0)
            {
                string Sheet = DTSchema.Rows[0]["TABLE_NAME"].ToString();
                System.Data.OleDb.OleDbCommand Comando = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + Sheet + "]", Cn);
                DataTable Dados = new DataTable();
                System.Data.OleDb.OleDbDataAdapter DA = new System.Data.OleDb.OleDbDataAdapter(Comando);
                DA.Fill(Dados);
                Cn.Close(); return(Dados);
            }
            return(null);
        }
Exemplo n.º 48
0
        public bool IsHasTable(string sTableName)
        {
            bool b = false;

            Open();
            DataTable schemaTable = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });

            if (schemaTable != null)
            {
                for (int i = 0; i < schemaTable.Rows.Count; i++)
                {
                    string sName = schemaTable.Rows[i]["TABLE_NAME"].ToString();
                    if (sName == sTableName)
                    {
                        b = true;
                    }
                }
            }
            return(b);
        }
Exemplo n.º 49
0
        /// <summary>
        /// 读取XLS数据到数据库
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        public static DataSet xsldata(string filepath, String SheetName)
        {
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

            System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);

            Conn.Open();
            DataTable dt        = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            String    tableName = dt.Rows[0][2].ToString().Trim();

            string strCom = String.Format("SELECT * FROM [{0}]", tableName);


            System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
            DataSet ds = new DataSet();

            myCommand.Fill(ds);
            Conn.Close();
            return(ds);
        }
Exemplo n.º 50
0
        public static DataTable ExcelToTable(string excelFilename, bool isTitleOrDataOfFirstRow)
        {
            if (!File.Exists(excelFilename))
            {
                return(null);
            }
            string HDR = string.Empty;//如果第一行是数据而不是标题的话, 应该写: "HDR=No;"
            string connectionString = string.Empty;

            if (isTitleOrDataOfFirstRow)
            {
                HDR = "YES";//第一行是标题
            }
            else
            {
                HDR = "NO";//第一行是数据
            }
            if (Is2007Excel(excelFilename))
            {
                connectionString = connstring07.Replace("{path}", excelFilename).Replace("{HDR}", HDR);
            }
            else
            {
                connectionString = connstring03.Replace("{path}", excelFilename).Replace("{HDR}", HDR);
            }
            DataSet ds = new DataSet();
            string  tableName;

            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                connection.Open();
                DataTable table = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                tableName = table.Rows[0]["Table_Name"].ToString();
                string           strExcel = "select * from " + "[" + tableName + "]";
                OleDbDataAdapter adapter  = new OleDbDataAdapter(strExcel, connectionString);
                adapter.Fill(ds, tableName);
                connection.Close();
            }
            return(ds.Tables[tableName]);
        }
        private DataSet GetData()
        {
            String filepath = Server.MapPath("/TrainWeb/Train/cjl.xls");
            string strCon   = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

            System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);

            Conn.Open();
            DataTable dt        = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            String    tableName = dt.Rows[0][2].ToString().Trim();

            tableName = "客运长交路机车牵引费单价表$";
            string strCom = String.Format("SELECT * FROM [{0}]", tableName);


            System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
            DataSet ds = new DataSet();

            myCommand.Fill(ds);
            Conn.Close();
            return(ds);
        }
Exemplo n.º 52
0
        public static string connectionString = "";//数据库连接字符串
        #region OleDb读取Excel
        /// <summary>
        /// 将 Excel 文件转成 DataTable 后,再把 DataTable中的数据写入表Products
        /// </summary>
        /// <param name="serverMapPathExcelAndFileName"></param>
        /// <param name="excelFileRid"></param>
        /// <returns></returns>
        public static int WriteExcelToDataBase(string excelFileName)
        {
            int             rowsCount = 0;
            OleDbConnection objConn   = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFileName + ";" + "Extended Properties=Excel 8.0;");

            objConn.Open();
            try
            {
                DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                string    sheetName   = string.Empty;
                for (int j = 0; j < schemaTable.Rows.Count; j++)
                {
                    sheetName = schemaTable.Rows[j][2].ToString().Trim();//获取 Excel 的表名,默认值是sheet1
                    DataTable excelDataTable = ExcelToDataTable(excelFileName, sheetName, true);
                    if (excelDataTable.Columns.Count > 1)
                    {
                        SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction);
                        sqlbulkcopy.DestinationTableName = "Products";//数据库中的表名


                        sqlbulkcopy.WriteToServer(excelDataTable);
                        sqlbulkcopy.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                objConn.Close();
                objConn.Dispose();
            }
            return(rowsCount);
        }
Exemplo n.º 53
0
    /// <summary>
    /// 获取Excel工作表名
    /// </summary>
    /// <param name="excelFilename">Excel文件绝对路径</param>
    /// <returns></returns>
    public List <string> GetExcelTableName(string excelFilename)
    {
        List <string> lstName          = new List <string>();
        string        connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=35;Extended Properties=Excel 8.0;Persist Security Info=False", excelFilename);
        DataSet       ds = new DataSet();

        using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))
        {
            connection.Open();
            DataTable table = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            connection.Close();
            if (table != null && table.Rows.Count > 0)
            {
                foreach (DataRow dr in table.Rows)
                {
                    if (dr["Table_Name"] != DBNull.Value)
                    {
                        lstName.Add(dr["Table_Name"].ToString());
                    }
                }
            }
        }
        return(lstName);
    }
Exemplo n.º 54
0
        public static IEnumerable <T> LoadFromFile <T>(string fullFileName, GetObjectFromReader <T> getObjectFromReaderFunc, string sheetNameFirst = "")
        {
            string          conString = FormatXlsConnectionString(fullFileName);
            IEnumerable <T> result    = null;

            using (var con = new System.Data.OleDb.OleDbConnection(conString))
            {
                using (var cmd = new System.Data.OleDb.OleDbCommand())
                {
                    cmd.Connection = con;

                    if (string.IsNullOrEmpty(sheetNameFirst))
                    {
                        con.Open();

                        var dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        sheetNameFirst = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

                        con.Close();
                    }

                    cmd.CommandType = CommandType.TableDirect;
                    cmd.CommandText = sheetNameFirst;

                    con.Open();
                    using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if (reader != null)
                        {
                            result = GetObjectListFromReader <T>(reader, getObjectFromReaderFunc);
                        }
                    }
                }
            }
            return(result);
        }
            // Constructor.
            // The fields of this class is set up according to the type of the connection
            public QuoteUtil(DbConnection connection)
            {
                // default is double quotes.
                // currently only oledb provide quotes information, others will use default
                prefix    = "\"";
                suffix    = "\"";
                separator = ".";

                // connection must be open in order to initialize this class
                STrace.Assert(connection.State == ConnectionState.Open, "the connection is not open");

                System.Data.OleDb.OleDbConnection oleDbConnection =
                    connection as System.Data.OleDb.OleDbConnection;
                if (oleDbConnection != null)
                {
                    // special case SQL Server
                    string   connectionString = connection.ConnectionString;
                    string   providerToken    = string.Empty;
                    string[] tokens           = connectionString.Split(new char[] { ';' });
                    foreach (string token in tokens)
                    {
                        // navigate to "Provider = "
                        if (token.Trim().StartsWith("Provider",
                                                    StringComparison.OrdinalIgnoreCase))
                        {
                            if (token.IndexOf("SQLOLEDB",
                                              StringComparison.OrdinalIgnoreCase) != -1 ||
                                token.IndexOf("SQLNCLI",
                                              StringComparison.OrdinalIgnoreCase) != -1)
                            {
                                prefix           = "[";
                                suffix           = "]";
                                escapedPrefix    = "\\[";
                                escapedSuffix    = "\\]";
                                escapedSeparator = "\\.";
                                SpeicalWordID    = SpecialWordID_HardBrackets;
                                return;
                            }
                        }
                    }

                    DataTable literalTable = oleDbConnection.GetOleDbSchemaTable(
                        OleDbSchemaGuid.DbInfoLiterals, null);

                    if (literalTable != null)
                    {
                        DataRow[] rows = literalTable.Select("LiteralName = 'Quote_Prefix'");

                        if (rows.Length > 0)
                        {
                            prefix = rows[0]["LiteralValue"].ToString();
                            if (prefix == null)
                            {
                                prefix = string.Empty;
                            }
                        }
                        else
                        {
                            prefix = string.Empty;
                        }

                        rows = literalTable.Select("LiteralName = 'Quote_Suffix'");
                        if (rows.Length > 0)
                        {
                            suffix = rows[0]["LiteralValue"].ToString();
                            if (suffix == null)
                            {
                                suffix = string.Empty;
                            }
                        }
                        else
                        {
                            suffix = string.Empty;
                        }

                        rows = literalTable.Select("LiteralName = 'Schema_Separator'");
                        if (rows.Length > 0)
                        {
                            separator = rows[0]["LiteralValue"].ToString();
                            if (separator == null)
                            {
                                separator = string.Empty;
                            }
                        }
                        else
                        {
                            separator = string.Empty;
                        }
                    }
                }
                // escape those symbols which are meaningful in regular expression, such as [].
                escapedPrefix    = prefix;
                escapedSuffix    = suffix;
                escapedSeparator = separator;
                if (prefix == "[")
                {
                    escapedPrefix = "\\[";
                    escapedSuffix = "\\]";
                    SpeicalWordID = SpecialWordID_HardBrackets;
                }
                else
                {
                    SpeicalWordID = SpecialWordID_DoubleQuotes;
                }
                if (separator == ".")
                {
                    escapedSeparator = "\\.";
                }
            }
Exemplo n.º 56
0
        private void button1_Click(object sender, EventArgs e)
        {
            checkedListBox1.Items.Clear();
            OpenFileDialog dialog = new OpenFileDialog();

            dialog.Filter = "Excel文件|*.xls|Excel文件|*.xlsx";
            List <string> combobox_list = new List <string>();

            if (dialog.ShowDialog() == DialogResult.OK)
            {
                richTextBox1.Text = dialog.FileName;
                filePath          = richTextBox1.Text;
            }

            if (filePath != "")
            {
                string postfix = richTextBox1.Text.Substring(richTextBox1.Text.LastIndexOf("."));
                if (postfix == ".xls")
                {
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + richTextBox1.Text + ";" + "Extended Properties=Excel 8.0;";
                }
                else
                {
                    strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", richTextBox1.Text);
                }
                jilu(strConn);
                using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(strConn))
                {
                    jilu("47-");
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        jilu("54-" + ex.Message);
                        throw;
                    }
                    //connection.Open();
                    jilu("48-");
                    DataTable table = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    jilu("49-");
                    string[] strTableNames = new string[table.Rows.Count];
                    string   excel_useless = "";
                    for (int k = 0; k < table.Rows.Count; k++)
                    {
                        strTableNames[k] = table.Rows[k]["TABLE_NAME"].ToString();

                        if (strTableNames[k].ToString().Trim().Substring(strTableNames[k].Length - 1, 1) == "$")
                        {
                            excel_useless = strTableNames[k].Substring(0, strTableNames[k].Length - 1);
                            if (excel_useless != "基础数据" && excel_useless != "修订记录表" && excel_useless != "填写说明表" && excel_useless != "教材目录" && excel_useless != "通用设置")
                            {
                                combobox_list.Add(strTableNames[k].ToString().Trim().Substring(0, strTableNames[k].Length - 1).ToString().Trim());
                            }
                            combobox_list.Sort();
                        }
                        else if (strTableNames[k].Substring(strTableNames[k].Length - 2, 2) == "$'")
                        {
                            excel_useless = strTableNames[k].Substring(1, strTableNames[k].Length - 3);
                            if (excel_useless != "基础数据" && excel_useless != "修订记录表" && excel_useless != "填写说明表" && excel_useless != "教材目录" && excel_useless != "通用设置")
                            {
                                combobox_list.Add(strTableNames[k].Substring(1, strTableNames[k].Length - 3).ToString().Trim());
                            }
                        }
                    }
                    this.checkedListBox1.Items.AddRange(combobox_list.ToArray());
                }
            }
        }
Exemplo n.º 57
0
        /// <summary>
        /// 是否存在表或视图
        /// </summary>
        /// <param name="type">"U"或"V"</param>
        /// <param name="name">表名或视图名</param>
        public static bool Exists(string type, string name, ref DbBase helper)
        {
            if (type == "U" && tableCache.Count > 0)
            {
                string key = "TableCache:" + helper.dalType + "." + helper.DataBase;
                if (tableCache.ContainsKey(key))
                {
                    return(tableCache[key].ContainsKey(name));
                }
            }
            int    result = 0;
            string exist  = string.Empty;

            helper.IsAllowRecordSql = false;
            DalType dalType = helper.dalType;

            name = SqlFormat.Keyword(name, helper.dalType);
            switch (dalType)
            {
            case DalType.Access:
                try
                {
                    System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(helper.Con.ConnectionString);
                    con.Open();
                    result = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, SqlFormat.NotKeyword(name), "Table" }).Rows.Count;
                    con.Close();
                }
                catch (Exception err)
                {
                    Log.WriteLogToTxt(err);
                }
                break;

            case DalType.MySql:
                if (type != "V" || (type == "V" && name.ToLower().StartsWith("v_")))    //视图必须v_开头
                {
                    exist = string.Format(ExistMySql, SqlFormat.NotKeyword(name), helper.DataBase);
                }
                break;

            case DalType.Oracle:
                exist = string.Format(ExistOracle, (type == "U" ? "TABLE" : "VIEW"), name);
                break;

            case DalType.MsSql:
                exist = string.Format(helper.Version.StartsWith("08") ? Exist2000 : Exist2005, name, type);
                break;

            case DalType.SQLite:
                exist = string.Format(ExistSqlite, (type == "U" ? "table" : "view"), SqlFormat.NotKeyword(name));
                break;

            case DalType.Sybase:
                exist = string.Format(ExistSybase, SqlFormat.NotKeyword(name), type);
                break;

            case DalType.Txt:
            case DalType.Xml:
                string   folder = helper.Con.DataSource + Path.GetFileNameWithoutExtension(name);
                FileInfo info   = new FileInfo(folder + ".ts");
                result = (info.Exists && info.Length > 10) ? 1 : 0;
                if (result == 0)
                {
                    info   = new FileInfo(folder + (dalType == DalType.Txt ? ".txt" : ".xml"));
                    result = (info.Exists && info.Length > 10) ? 1 : 0;
                }
                break;
            }
            if (exist != string.Empty)
            {
                helper.IsAllowRecordSql = false;
                result = Convert.ToInt32(helper.ExeScalar(exist, false));
            }
            return(result > 0);
        }
        public Int32 exportexcel(string filename, string filePath, string tablename, long cmpcode)
        {
            string conStr = "";
            string extension;

            extension = Path.GetExtension(filename);
            switch (extension)
            {
            case ".xls":     //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;

            case ".xlsx":     //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
            }
            try
            {
                conStr = String.Format(conStr, filePath, "Yes");
                System.Data.OleDb.OleDbConnection  connExcel = new System.Data.OleDb.OleDbConnection(conStr);
                System.Data.OleDb.OleDbCommand     cmdExcel  = new System.Data.OleDb.OleDbCommand();
                System.Data.OleDb.OleDbDataAdapter oda       = new System.Data.OleDb.OleDbDataAdapter();
                DataTable dt = new DataTable();
                cmdExcel.Connection = connExcel;
                //Get the name of First Sheet
                connExcel.Open();
                DataTable dtExcelSchema;
                dtExcelSchema = connExcel.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                connExcel.Close();
                string       myexceldataquery     = "SELECT * From [" + SheetName + "]";
                string       ssqlconnectionstring = ConfigManager.GetNewSqlConnection.ConnectionString;
                OleDbCommand oledbcmd             = new OleDbCommand(myexceldataquery, connExcel);
                connExcel.Open();
                cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
                oda.SelectCommand    = cmdExcel;

                oda.Fill(dt);
                dt.Columns.Add("CmpCode", typeof(System.Int64));

                foreach (DataRow row in dt.Rows)
                {
                    row["CmpCode"] = cmpcode;
                }
                // OleDbDataReader dr = oledbcmd.ExecuteReader();
                SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
                bulkcopy.DestinationTableName = tablename;
                bulkcopy.WriteToServer(dt);
                //while (dt)
                //{
                //    bulkcopy.WriteToServer(dr);
                //}
                // dr.Close();
                connExcel.Close();
                return(1);
            }
            catch (Exception e)
            {
                return(2);
            }
        }
Exemplo n.º 59
0
        // [CustomAuthentication("ReportUploader", "Create,Edit,Delete")]
        public ActionResult Import(HttpPostedFileBase excelfile)
        {
            string Value = string.Empty;

            //   Excelimport (excelfile);
            if (excelfile == null || excelfile.ContentLength == 0)
            {
                ViewBag.Error = "Upload the excel file !....";
                return(View("Index"));
            }

            else
            {
                if (excelfile.FileName.EndsWith("xsl") || excelfile.FileName.EndsWith("xlsx"))
                {
                    string path = "";
                    //   string path = Server.MapPath("~/Content/" + excelfile.FileName + DateTime.Now.ToString("MM.dd.yyyy-hh.mm.ss"));
                    try
                    {
                        //path = Server.MapPath("~/UploadSheet/" + excelfile.FileName + DateTime.Now.ToString("MM.dd.yyyy"));
                        path = Server.MapPath("~/UploadSheet/");
                        if (System.IO.File.Exists(path))
                        {
                            System.IO.File.Delete(path);
                        }
                        excelfile.SaveAs(path + Path.GetFileName(excelfile.FileName));
                        path = path + Path.GetFileName(excelfile.FileName);
                        // excelfile.SaveAs(path);
                    }
                    catch (Exception ex)
                    {
                    }
                    System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; " + "data source='" + path + "';" + "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ");
                    try
                    {
                        myConnection.Open();
                        DataTable mySheets = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        DataSet   ds       = new DataSet();
                        DataTable dt;

                        for (int i = 0; i < mySheets.Rows.Count; i++)
                        {
                            dt = makeDataTableFromSheetName(path, mySheets.Rows[i]["TABLE_NAME"].ToString());
                            ds.Tables.Add(dt);
                        }
                        if (ds.Tables.Count > 0)
                        {
                            CreateReport(ds);
                            return(View());
                        }
                        //StatusLabel.Text += "<br />Report status: File processed!";
                    }
                    catch (Exception ex)
                    {
                        //btnDownloadReport.Visible = false;
                        //grdReport.DataSource = null;
                        //grdReport.DataBind();
                        var x = "<br />Report status: The file could not be processed. The following error occured: " + ex.Message;
                    }
                    finally
                    {
                        myConnection.Close();
                    }
                }
            }
            return(View());
        }
Exemplo n.º 60
-1
        private static int WriteToExcelFile(string connExcel, OleDbConnection dbCon)
        {
            var excelSchema = dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            var sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();

            OleDbCommand command = new OleDbCommand("INSERT INTO [" + sheetName +
                "] VALUES(@name, @score)", dbCon);

            string name = "Evlogy Christov";
            int score = 34;
            command.Parameters.AddWithValue("@name", name);
            command.Parameters.AddWithValue("@score", score);
            int queryResult = command.ExecuteNonQuery();

            command.Parameters.Clear();
            name = "Christian Zaklev";
            score = 32;
            command.Parameters.AddWithValue("@name", name);
            command.Parameters.AddWithValue("@score", score);
            queryResult += command.ExecuteNonQuery();

            // a ready-made way to "invent" new parameters:
            for (int i = 5; i < 10; i++)
            {
                command.Parameters.Clear();
                command.Parameters.AddWithValue("@name", "Student" + i);
                command.Parameters.AddWithValue("@score", i);
                queryResult += command.ExecuteNonQuery();
            }

            return queryResult;
        }