connection string for Excel/Access 2010

Excel 2010 连接字符串

 

case 1: ConnectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'", ExcelPath); break;
case 2: ConnectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No;IMEX=1'", ExcelPath); break;

 

Access 2010 连接字符串

 

string conString = string.Format (@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Persist Security Info=False;", accdbPath );

 

Excel - 读取一个sheet到内存

 

        /// <summary>
        /// Read a Sheet in to memory as a Dataset
        /// </summary>
        /// <param name="sheet">Sheet Name</param>
        /// <param name="flagUseheader">if value is 1, the first row was read as the header; else if value is 2, the first row was read as not the header</param>
        /// <returns>ds</returns>
        public System.Data.DataSet GetDataSetFromExcel(string sheet, int flagUseheader)
        {
            if (flagUseheader != 1 && flagUseheader != 2)
            {
                return null;//throw new ArgumentOutOfRangeException("HDR_INVALIDE");
            }

            if (!System.IO.File.Exists (ExcelPath ))
            {
                return null;//throw new ArgumentNullException("EXCEL_PATH_NULL");
            }
            
            string ConnectionString = string.Empty;
            switch (flagUseheader)
            {
                case 1: ConnectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'", ExcelPath); break;
                case 2: ConnectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No;IMEX=1'", ExcelPath); break;
            }

            DataSet ds = new DataSet();
            OleDbDataAdapter olDataAdapter = new OleDbDataAdapter(string.Format(@"select * from [{0}$]", sheet ), ConnectionString );
            try
            {
                olDataAdapter.Fill(ds);
            }
            catch { ds = null; }
            finally { olDataAdapter.Dispose();}
            
            return ds;
        }

 

Access - 更新Item 表里的列值

 

        /// <summary>
        /// update column
        /// </summary>
        /// <param name="item">custom class{ItemName, SectionID}</param>
        public void UpdateFunctionalGroupID(ClsItem item)
        {            
            string commandText = string.Format (@"update Item set FunctionalGroup='{0}' where ItemName='{1}'",item.SectionID ,item.ItemName );
            string conString = string.Format (@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Persist Security Info=False;", accdbPath );
            OleDbConnection oledbconn=new OleDbConnection (conString );
            OleDbCommand oledbCmd = new OleDbCommand(commandText, oledbconn);
            oledbconn.Open();
            try
            {
                int affectedCount = oledbCmd.ExecuteNonQuery();

                oledbCmd.Dispose();
                oledbconn.Close();
                if (affectedCount == 0)
                    ALNotUpdated.Add(item.ItemName);
            }
            catch {
                ALNotUpdated.Add(item.ItemName);
            }
        }