Recently in C # write a WinForm program that creates Excel and exports the data to Excel.
Let me connect to OLE DB extended properties (extended attributes) of Excel Hdr=yes; Imex=2 has a deep understanding!
Value of the parameter HDR:
Hdr=yes, this means that the first row is the title, not as data use, if used Hdr=no, then the first row is not the title, as data to use. The system default is Yes
parameter Excel 8.0for Excel 97 to 2003 versions, use Excel 8.0,2007 or 2010 for both extended Properties=excel 12.0
IMEX (IMport EXport mode) settings
There are three modes of IMEX:
0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)
What I want to highlight here is the IMEX parameter, because different patterns represent different reading and writing behaviors:
when imex=0 is "Export mode", the Excel file opened by this mode can only be used for "write" purposes.
when Imex=1 is "Import Mode", the Excel file opened in this mode can only be used for "read" purposes.
when imex=2 is "connected mode", the Excel file opened in this mode can support both "read" and "write" purposes.
The meanings are as follows:
0---output mode;
1---input mode;
2----link mode (full update capability)
As described above, the following connection string should be readable and plug-in logged:
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;
This is not the case, however, when an INSERT INTO statement is executed, an exception occurs: "The operation must use an updatable query"! Here's what happens when I have an error: Windows 7,framework 4, 0,microsoft Office 2007,vs2010,c# WinForm; note that C # WinForm programs, not Web applications I just encountered this error when I thought there was no permission, but running with administrator is still the same error! Again, add the permissions with the following code, or the same error:
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);D irectoryinfo di = new DirectoryInfo (Path.getdirectoryname (Excelpath)); System.Security.AccessControl.DirectorySecurity dirsecurity = di. GetAccessControl ();d irsecurity.addaccessrule (New FileSystemAccessRule ("Everyone", Filesystemrights.fullcontrol, Accesscontroltype.allow));d Irsecurity.addaccessrule (New FileSystemAccessRule ("Users"), Filesystemrights.fullcontrol, Accesscontroltype.allow));d I. Setaccesscontrol (dirsecurity);
Or go to search to see how others are solved, but look at the way others solve the problem, to me here is the test does not pass! Guess or the problem of Imex value, instead of 1, then change to 0,
Nimah, the miracle has come! Then test the IMEX set to 4 or 10, the result is no problem, but 1 and 2 not, it is the rhythm of the pit dad.