c# - Search entire workbook and fetch out values -

in excel workbook

the first sheet tab contains

number name code    subject 100    mark abc mathematics 101    john xyz physics  second sheet tab contains  number name code    subject 103    mark def chemistry 104    john ghi biology 

i want pass code(which going unique) parameter , search entire excel workbook

and fetch name , subject..

ie..select name,subject myexcelworkbook code='abc'

i able sheet names, column count etc. not able search thro' entire excel workbook , required values

        const string filename="c:\\filename.xls";          oledbconnectionstringbuilder connectionstringbuilder = new oledbconnectionstringbuilder();         connectionstringbuilder.provider = "microsoft.ace.oledb.12.0";         connectionstringbuilder.datasource = filename;         connectionstringbuilder.add("mode", "read");          const string extendedproperties = "excel 12.0;imex=1;hdr=yes";         connectionstringbuilder.add("extended properties", extendedproperties);         using (oledbconnection objconn = new oledbconnection(connectionstringbuilder.connectionstring))        {             objconn.open();              microsoft.office.interop.excel.application xlsapp = new microsoft.office.interop.excel.application();             workbook wb = xlsapp.workbooks.open(filename, 0, true, 5, "", "", true, xlplatform.xlwindows, "\t", false, false, 0, true);              sheets sheets = wb.worksheets;              (int =1 ; <= wb.worksheets.count; i++)             {                  messagebox.show(wb.sheets[i].name.tostring()); - gives sheet names inside workbook             }            worksheet ws = (worksheet)sheets.get_item(1); - gives elements of specified sheet tab       }    //to elements inside specific sheet of excel workbook/get column names          oledbcommand objcmdselect = new oledbcommand("select * [" + "sheet1" + "$]", objconn);           oledbdataadapter objadapter1 = new oledbdataadapter();           objadapter1.selectcommand = objcmdselect;           dataset objdataset1 = new dataset();           objadapter1.fill(objdataset1);          string columnnames = string.empty;         // each datatable, print columnname. use dataset.rows iterate row data...         foreach (system.data.datatable table in objdataset1.tables)         {             foreach (datacolumn column in table.columns)             {                 if (columnnames.length > 0)                 {                     columnnames = columnnames + environment.newline + column.columnname;                 }                 else                 {                     columnnames = column.columnname;                 }              }         }  

can share ideas, can find out unique data inside excel workbook , fetch out needed values based on that? in advance.

if workbooks structured indicate , querying multiple students then, @andy g suggests, might find easiest data form of record set run queries on through linq or sql or whatever prefer. aren't wishing modify excel workbook @ better approach.

alternatively, can use excel api, trying use. can enumerate through worksheets, running find on each. la:

internal void getyourdata() {     //... code relevant workbook , relevant/new excel application      tuple<string, string> pupil;     string searchterm = "abc";      //get cell of match     range match = findfirstoccurrenceinworkbook(workbook, searchterm);     if (match != null)     {         //do whatever - per data structure, easiest use .offset(row, column) property         pupil = new tuple<string, string>((string)match.offset(0, -1).value, (string)match.offset(0, 1).value);     }      //... code whatever results }  internal static range findfirstoccurrenceinworkbook(workbook workbook, string searchterm) {     if (workbook == null) throw new argumentnullexception("workbook");     if (searchterm == null) throw new argumentnullexception("searchterm");      sheets wss = workbook.worksheets;     range match = null;      foreach (worksheet ws in wss)     {         range cells = ws.cells;         //add more args needed - example         match = cells.find(                 what: searchterm,                 after: type.missing,                 lookin: xlfindlookin.xlformulas,                 lookat: xllookat.xlpart);         system.runtime.interopservices.marshal.releasecomobject(cells);         system.runtime.interopservices.marshal.releasecomobject(ws);         if (match != null)         {             break;         }     }     system.runtime.interopservices.marshal.releasecomobject(wss);     gc.collect();     gc.waitforpendingfinalizers();     gc.collect();      return match; } 

also, can't use com objects use without generating orphaned references -> prevent excel application closing. need explicitly assign object properties want use workbook.worksheets variable call system.runtime.interopservices.marshal.releasecomobject(object) when variable go out of scope.