Example #1
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;
        }
Example #2
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;
    }
Example #3
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);
                        }
                    }
                }
            }
        }
        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: ");
            string userName = Console.ReadLine();
            Console.Write("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);
            }
        }
Example #5
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;
 }
Example #6
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;
        }
Example #7
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;
    }
Example #8
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);
                        }
                    }
                }
            }
        }
Example #9
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;
        }
Example #10
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;
        }
Example #11
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;
                    }
                }
            }
        }
        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;
            }
        }
Example #13
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;
        }
Example #14
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;
 }
Example #15
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;
        }
Example #16
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;
        }
Example #17
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();
            }
        }
Example #18
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;
        }
 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;
 }
 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;
 }
    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;
    }
Example #22
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;
            }
        }
        /// <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;
            }
        }
Example #24
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;
        }
    }
Example #25
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);
                }
            }
        }
        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!");
            }
        }
Example #28
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"));
        }
Example #29
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;
        }
Example #30
-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;
        }