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