i'm trying figure out why behavior i'm seeing , "documented" behavior different. i've read both of these articles:read , write excel documents using oledb , working ms excel(xls / xlsx) using mdac , oledb , text second link.
if read in second link says:
to retrieve schema information of excel workbook :
you can worksheets present in excel workbook using getoledbschematable. use following snippet.
datatable dtschema = null; dtschema = conobj.getoledbschematable( oledbschemaguid.tables, new object[] { null, null, null, "table" });
here dtschema hold list of workbooks. have 2 workbooks : wb1, wb2. above code return list of wb1, wb1$,wb2,wb2$. need filter out $ elements.
however when run code "wb1$ , wb2$". can remove $ in code i'm trying make sure i'm not going have code breaks when put on different computers/os/environment , behaves documented. can tell or if changed since these written or if i'm missing key piece. note being developed in vs2015, windows 7 pro, , office 2010 installed.
//connection string //string connstring = "provider=microsoft.ace.oledb.12.0;data source=" + path + ";extended properties='excel 8.0;hdr=no;imex=1';"; // blank space cannot appear in office 2007 , last version. , need pay attention on semicolon. //string connstring = provider = microsoft.jet.oledb.4.0; data source = " + path + "; extended properties = 'excel 8.0;hdr=no;imex=1'; "; //this connection string appropriate office 2007 , older version. can select suitable connection string according office version or our program. using (oledbconnection conn = new oledbconnection(_connectionstring)) { conn.open(); //datatable sheetnames = conn.getoledbschematable(oledbschemaguid.tables, new object[] { null, null, null, "table" }); //get sheets name datatable sheetnames = conn.getoledbschematable(oledbschemaguid.tables, null); //get sheets name // loop through sheets data foreach (datarow dr in sheetnames.rows) { string sheetname = dr["table_name"].tostring(); //if (!sheetname.endswith("$")) // continue; debug.print(sheetname); } return sheetnames; thanks
dbl
