i have datagridview import excel file.my excel columns name,id,sex,grade,seat no .what want filter columns (multi column filter) in datagridview except name , id via textbox. i.e. when type single word in text box want filter columns of sex,grade , seat no @ same time. here excel importing code datagridview....
private sub openfiledialog1_fileok(byval sender object, byval e system.componentmodel.canceleventargs) handles openfiledialog1.fileok dim filepath string = openfiledialog1.filename dim extension string = path.getextension(filepath) dim header string = if(rbheaderyes.checked, "yes", "no") dim constr string, sheetname string constr = string.empty select case extension case ".xls" 'excel 97-03 constr = string.format(excel03constring, filepath, header) exit select case ".xlsx" 'excel 07 constr = string.format(excel07constring, filepath, header) exit select end select 'get name of first sheet. using con new oledbconnection(constr) using cmd new oledbcommand() cmd.connection = con con.open() dim dtexcelschema datatable = con.getoledbschematable(oledbschemaguid.tables, nothing) sheetname = dtexcelschema.rows(0)("table_name").tostring() con.close() end using end using 'read data first sheet. using con new oledbconnection(constr) using cmd new oledbcommand() using oda new oledbdataadapter() dim dt new datatable() cmd.commandtext = (convert.tostring("select * [") & sheetname) + "]" cmd.connection = con con.open() oda.selectcommand = cmd oda.fill(dt) con.close() 'populate datagridview. datagridview1.datasource = dt end using end using end using end sub
you should bind datatable bindingsource, add in designer, , bind grid. can filter data setting filter property of bindingsource. it's sql clause so, in sql, can use , and or operators combine multiple criteria, e.g.
mybindingsource.filter = string.format("column1 '%{0}%' or column2 '%{0}%'", mytextbox.text) just note can use text columns, not numbers or dates or else.
Comments
Post a Comment