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

 

 

转载于:https://www.cnblogs.com/qixue/archive/2012/01/13/2321674.html