|
Hi All,
i need assistance with reading an excel file, writing it to a datagrid (this i already have in my code) and then for each line in the datagrid i need the following.
if a cell value is xxx in column y then do....
for example:
i have a column called "vehicle/pedestrian" now i want the following:
for each line in the datagrid if cell value in this column (vehicle......) then do.......
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using System.Runtime.InteropServices;
namespace Datagridtest
{
public partial class Form1 : Form
{
private void BrowseBtn_Click(object sender, EventArgs e)
{
FilePathBrowse.Filter = "SIT Files (*.xls) | *.Xls";
if (FilePathBrowse.ShowDialog() == DialogResult.OK)
{
if (FilePathBrowse.FileName != "")
FilePath.Text = FilePathBrowse.FileName;
}
}
private DataTable dt;
public static string BuildExcelConnectionString(string Filename, bool FirstRowContainsHeaders)
{
return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties=\"Excel 8.0;HDR={1};\"",
Filename.Replace("'", "''"),
FirstRowContainsHeaders ? "Yes" : "No");
}
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string fileName = FilePath.Text;
if (!File.Exists(fileName))
{
MessageBox.Show("Cannot find file");
return;
}
string connStr = BuildExcelConnectionString(fileName, true);
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();
using (OleDbCommand cmd = new OleDbCommand("Select * From [Sheet1$]", conn))
{
using (OleDbDataReader dr = cmd.ExecuteReader())
{
if (dt != null)
dt.Dispose();
dt = new DataTable();
dt.Load(dr);
}
}
}
dataGridView1.DataSource = dt;
}
}
}
the excel i have is something like this
point Long Lat FOV [pedestrian / vehicle] [single / multiple] Target Location LOS/Detection Freetext
1 -73.882644 40.692344 5 Vehicle Multi TGT Road MIN DET Range Your Fee text here
2 -73.882903 40.69259 2 Pedestrian Single TGT sidewalk DET from PREV to this PT Your Fee text here
thanks alot.
Jonathan
|
|
|
|
|
If you use a OleDbDataAdapter[^] to read the worksheet you can get it to populate the datagridview automatically for you, something like:
OleDbCommand cmd = new OleDbCommand(string.Format("Select * from [Sheet1$]");
cmd.Connection = oleDbConnection;
OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
adpt.Fill(ds, "[Sheet1$]");
if (ds.Tables.Count == 1)
{
dgView.DataSource = ds.Tables[0];
}
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
Hi
thanks for the prompt reply but i didnt understand it.
my code is currently working with reading the excel file and putting it in the datagrid.
what i am missing is that for each line that contains a certain value in a cell in a certain colum todo something.
with the code you posted how can it be done?
thanks
|
|
|
|
|
Once you have loaded the Excel file into the DataGridView then you can process the individual items you are interested in, by looking at the relevant cells in the grid, setting flags, adding columns or rows etc.
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
|
By writing some code, how do you expect? I have shown you how to get your data into your DataGridView, but I have no idea what you want to do with that data. You need to work out which cells you need to check and which cells you need to modify, and write the code to accomplish it. All the methods that can help you can be found in the DataGridView class[^]
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
<pre lang="sql">
selLoc = ListBox1.SelectedIndex;
listBox1.Items.Remove(listBox1.SelectedItems[0]);
listBox1.Items.Remove(listBox1.Items[selLoc]);
|
|
|
|
|
Found solution
selLoc = listBox1.SelectedIndex;
listBox1.Items.RemoveAt(selLoc);
|
|
|
|
|
5 because you found the answer I was about to give you, yourself 
|
|
|
|
|
The reason that this happens is because Remove uses equality checking to work out which line you meant – and the first line with the same text will match. (I really don't think it should do that, but that's a separate discussion.) The answer, as you discovered, is to use RemoveAt.
|
|
|
|
|
thanks for the explanation. I didn't feel I had an answer although the fix worked.
|
|
|
|
|
BobJanova wrote: I really don't think it should do that, but that's a separate discussion
... I agree, and you are correct about the equality check. It should use reference checking IMO but then that wouldn't work with boxed value types. Hmm..
|
|
|
|
|
Yeah! I will just add it to my list of MS idiosyncratic anomalies.
|
|
|
|
|
I've just had a quick play with this and actually, it does use reference equality. What's happening is due to string interning - if you don't know what that is have a google 
So, when you add your strings to the list box, if they are the same text then they are actually the same reference, therefore it will remove the first one it finds.
A quick solution is to wrap a string in another class and use that in place of string.
public class MyString
{
private string value;
public MyString(string value)
{
this.value = value;
}
public static implicit operator string(MyString myString)
{
return myString.value;
}
public static implicit operator MyString(string value)
{
return new MyString(value);
}
public override string ToString()
{
return value;
}
}
MyString one = " ";
MyString two = "2";
MyString three = " ";
listBox.Items.Add(one);
listBox.Items.Add(two);
listBox.Items.Add(three);
Selecting the third item and calling listBox.Items.Remove(listBox.SelectedItem); successfully removes the third item.
|
|
|
|
|
Oh, my mistake then.
E: Although your test doesn't actually test what you think it does. The MyString class has no equality checking, so it will always use reference equality. Try adding Equals, ==, != and Hashcode to it and see what the result is.
|
|
|
|
|
Yes, that was deliberate as the OP was wanting reference checking so two identical strings were treated as different objects. Even with those added it behaves as expected - it's just the interning of the string class that screws it!
Example below. Warning: No null checking on value
using System;
using System.Windows.Forms;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
listBox.Items.Add(new MyString(" "));
listBox.Items.Add(new MyString("2"));
listBox.Items.Add(new MyString(" "));
}
private void buttonRemove_Click(object sender, EventArgs e)
{
if (listBox.SelectedItem != null)
listBox.Items.Remove(listBox.SelectedItem);
}
}
}
public class MyString : IEquatable<MyString>
{
private string value;
public MyString(string value)
{
this.value = value;
}
public static implicit operator MyString(string value)
{
return new MyString(value);
}
public static implicit operator string(MyString myString)
{
return myString.value;
}
public static bool operator ==(MyString first, MyString other)
{
if (object.ReferenceEquals(first, other))
return true;
if (object.ReferenceEquals(null, first) || object.ReferenceEquals(null, other))
return false;
return first.value == other.value;
}
public static bool operator !=(MyString first, MyString other)
{
return !(first == other);
}
public override bool Equals(object obj)
{
return Equals(obj as MyString);
}
public bool Equals(MyString other)
{
if (object.ReferenceEquals(null, other))
return false;
return value.Equals(other.value);
}
public override int GetHashCode()
{
return value.GetHashCode();
}
public override string ToString()
{
return value;
}
}
|
|
|
|
|
Since my list box is populated from several functions and has over 50,000 lines it would seem the simple "RemoveAt" solves the problem. But thanks for the clarification and example.
|
|
|
|
|
Use listBox1.Items.RemoveAt(ListBox1.SelectedIndex); in place of your code then it will remove your selected item from List Box.
|
|
|
|
|
As I discovered but the listBox1.Remove is logical. Why create this special case.
|
|
|
|
|
how to call base class constructor during creation of derive class object?
|
|
|
|
|
public class BaseClass
{
public BaseClass(string something)
{
}
}
public class DerivedClass : BaseClass
{
public DerivedClass(string something, int other)
: base(something)
{
}
}
This is real basic stuff, which you would pick up really easily with a good book. I can suggest anything by Jon Skeet.
|
|
|
|
|
Hi All
I am developing a windows application in C#. One of its module need to send and receive data from USB port.
How to do this in c#? Is there any Namespase or thrd party dll available?
Regards
Willington
|
|
|
|
|
Plenty[^] if you care to look.
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
Hi.. I have googled.. but couldn't find any solution... 
|
|
|
|
|
The first article in the list of links I found for you has sample code.
One of these days I'm going to think of a really clever signature.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
