我發生錯誤時的環境:Windows 7,Framework 4、0,Microsoft Office 2007,VS2010,c# WinForm;部分代碼:
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;Persist Security Info=False;" + "data source=" + @excelPath + ";Extended Properties='Excel 12.0; HDR=yes; IMEX=2'"; OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = strConn; try { OleDbCommand cmd = null; try { cmd = new OleDbCommand("Insert Into [Sheet1$] Values('abc', 'bac', '0', '123456', 'test','測試','aa')", conn);//(A,B,C,D,E,F,G) cmd.ExecuteNonQuery(); } catch (System.Exception ex) { textBox1.Text += ("插入數據失敗:" + ex.Message); textBox1.Text += ("\r\n"); }遇到此錯誤的時候第一想到的就是沒有權限,但使用管理員身份運行依然是相同的錯誤!又通過以下代碼添加權限,還是一樣的錯誤:
FileInfo fi = new FileInfo(excelPath); System.Security.AccessControl.FileSecurity fileSecurity = fi.GetAccessControl(); fileSecurity.AddAccessRule(new FileSystemAccessRule("Everyone", FileSystemRights.FullControl, AccessControlType.Allow)); fileSecurity.AddAccessRule(new FileSystemAccessRule("Users", FileSystemRights.FullControl, AccessControlType.Allow)); fi.SetAccessControl(fileSecurity); DirectoryInfo di = new DirectoryInfo(Path.GetDirectoryName(excelPath)); System.Security.AccessControl.DirectorySecurity dirSecurity = di.GetAccessControl(); dirSecurity.AddAccessRule(new FileSystemAccessRule("Everyone", FileSystemRights.FullControl, AccessControlType.Allow)); dirSecurity.AddAccessRule(new FileSystemAccessRule("Users", FileSystemRights.FullControl, AccessControlType.Allow)); di.SetAccessControl(dirSecurity);知識補習,這裡的連接字符串多了:Extended Properties='Excel 12.0; HDR=yes; IMEX=2'
參數HDR的值:
HDR=Yes,這代表第一行是標題,不做為數據使用 ,如果用HDR=NO,則表示第一行不是標題,做為數據來使用。系統默認的是YES