i have the excel sheet with code like
VPUDB001 VPUDB002 VPUDB003 VPUDB004 VPUDB005 VPUDB006 VPUDB007 VPUDB008 VPUDB009 VPUDB010 VPUDB011 VPULN001 VPULN002 VPULN003 VPULN004 VPULN005 VPULN006 VPULN007 VPULN008 VPULN009
I want to display the number of rows starting with VPUDB and VPULN in excel.
private void browse_Click(object sender, EventArgs e)
{
DialogResult fileopen = openFileDialog1.ShowDialog();
string filename = openFileDialog1.FileName;
textBox1.Text = filename;
try
{
olecon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filename + "';Extended Properties=Excel 12.0");
olecon.Open();
DataTable dt = new DataTable();
dt = olecon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string[] excelsheetnames = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelsheetnames[i] = row["TABLE_NAME"].ToString();
string sheetnamealone = excelsheetnames[i].Remove(excelsheetnames[i].IndexOf('$'));
comboBox1.Items.Add(sheetnamealone);
i++;
}
//comboBox1.SelectedIndex = 1;
}
catch
{
}
finally
{
olecon.Close();
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
OleDbCommand cmd = new OleDbCommand("Select [code] FROM ["+comboBox1.SelectedItem+"$] ", olecon);
olecon.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
while (dReader.Read())
{
string s = dReader[0].ToString();
}
olecon.Close();
}
Here i browse the excel sheet and i count and display in combo box, then in combo selected event i need to count the particular sheet rows as said above.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
try linq like this will resolve your issue
var query = from row in dTable.AsEnumerable()
where ((row.Field<string>("columnname")).contains("VPUDB")
|| (row.Field<string>("columnname")).contains("VPULN"))
select row;
Method 2
Use a string comparison to see if it contains VPUDB or VPULN when you are initially counting for your combobox, and if it does, increment a counter.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0