30785

search for text in excel file c#

Question:

I'm writing a windows application with c# and in one form I want to use a n excel file in this way.

In the form when the user types the Employee ID in a text box I wanna search the excel file and based on the ID show other information for the employee in some other text boxes(name text box, last name text box ,status textbox ,and food count text box)

please tell me how can I retrieve the info from it .

public List <string>[] RetrieveColumnGeneral(Excel.Worksheet sheet,string FindWhat) { int columnCount = sheet.UsedRange.Columns.Count; List<string>[] columnValue = new List<string>[columnCount]; Excel.Range rngResult = null; Excel.Range rng = null; int index = 0; int rowCount = sheet.UsedRange.Rows.Count; Excel.Range FindRange = null; for (int columnIndex = 1; columnIndex <= sheet.UsedRange.Columns.Count;columnIndex++ ) { FindRange = sheet.UsedRange.Columns[columnIndex] as Excel.Range; FindRange.Select(); rngResult = FindRange.Find(What: FindWhat, LookIn: Excel.XlFindLookIn.xlValues, LookAt: Excel.XlLookAt.xlPart, SearchOrder: Excel.XlSearchOrder.xlByRows); if (rngResult != null) { columnValue[index] = new List<string>(); for (int rowIndex = 1; rowIndex <= sheet.UsedRange.Rows.Count;rowIndex++ ) { rng = sheet.UsedRange[rowIndex, columnIndex] as Excel.Range; if (rng.Value != null) { columnValue[index].Add(rng.Value.ToString()); } } index++; } } Array.Resize ( ref columnValue , index); return columnValue; }

thanks I found this code . Will you tell me whether this will solve my problem ?(also by some modifications.)

Answer1:

Try this,

You have to passed Sheet object, but in below code SQL statement fire and get all records in DataSet object. string sql = "SELECT * FROM [" + selectedWorksheetName + "]"; var adapter = new OleDbDataAdapter(sql, excelObject.Connection); adapter.Fill(activityDataSet, "Results"); if (activityDataSet.Tables[0] != null) { //here you will check which data get based on your columns }

Answer2:

Try some thing like this.

var ds = new DataSet(); var adapter = new OleDbDataAdapter("SELECT * FROM [" + newSheetName + "]", ConnectionString); adapter.Fill(ds, newSheetName);

Then use the above dataset to filter the rows.

DataRow dataRow = (from DataRow dr in ds.Tables[0].Rows where dr["Id"] == textbox.Text select dr).FirstOrDefault(); if(dataRow != null) { // Fill values from this dataRow }

<strong>EDIT</strong>

// If excelsheet ends with .xls string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ExcelData\QB Accounts.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; // If excelsheet ends with .xlsx string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\ExcelData\CSENG01.xlsx;Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";

Recommend

  • Table layout panel column/row count
  • DefaultTableModel in a JFrame only creating on Row - JAVA
  • ExpandableListView with ViewPager combination as its child
  • Find that start date and end dates are available using python pandas
  • python oneline to create matrix of given order
  • Microsoft Interop: Excel Column Names
  • Poco ODBC and while SQL loop
  • Export DataGridView Data with Images into Excel, HTML, or Word with Proper Table Formatting
  • How to show the row where QPushButton is clicked in QTableWidget
  • Reading a range of excel sheet data using POI
  • How to refresh QTableView when it is driven by model
  • Unknown name “update_sheet_properties”
  • Loop through listboxes in excel vba
  • I get the Jtable but no data [duplicate]
  • iText PdfPTableEventForwarder not getting called when expected,
  • retrieving data from mysql and putting it on JTables
  • Add decimal point & set cursor position to DataGridView cell text on KeyPress event
  • Change the datagridview color specified with column
  • How does one get the UserForm where MSForms.ComboBox Object is located?
  • How to stretch QTableView last column header
  • QComboBox and app.setStyle(“cleanlooks”)
  • Getting data from SQLite database to String Android
  • Increment inside angular expression
  • Compare two columns if another two columns are matching in excel macro
  • Excel Solver Ignoring Constraint in VBA
  • Why Is My Azure SQL Database Table Permanently Locked?
  • Generating random unique data takes too long and eats 100% CPU
  • QStandardItemModel::removeRows() does not work in my use case
  • Bootstrap navbar stays expanded on route change
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Do I've to free mysql result after storing it?
  • Rearranging Cells in UITableView Bug & Saving Changes
  • Transpose CSV data with awk (pivot transformation)
  • Linker errors when using intrinsic function via function pointer
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Proper way to use connect-multiparty with express.js?
  • LevelDB C iterator
  • Sorting a 2D array using the second column C++
  • How can i traverse a binary tree from right to left in java?
  • Conditional In-Line CSS for IE and Others?