C# Discussion Boards - CodeProject

Richard MacCutchan22-Oct-12 3:27

Richard MacCutchan

22-Oct-12 3:27

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.

yoni.kess22-Oct-12 6:47

yoni.kess

22-Oct-12 6:47

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

Richard MacCutchan22-Oct-12 7:31

Richard MacCutchan

22-Oct-12 7:31

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.

yoni.kess23-Oct-12 0:02

yoni.kess

23-Oct-12 0:02

and how is it done?

Richard MacCutchan23-Oct-12 0:18

Richard MacCutchan

23-Oct-12 0:18

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.

listBox Item Removal

electriac22-Oct-12 0:42

22-Oct-12 0:42

<pre lang="sql">//My list box contains some empty lines or lines just containing a "\t" and sometimes I want to remove one of these.
//If the selected line contains text the line is removed without problem but when the selected line is blank "", " ", or "\t"
// the removal goes to the first blank line and removes it rather than the selected line.

selLoc = ListBox1.SelectedIndex;
listBox1.Items.Remove(listBox1.SelectedItems[0]); 
// Removes The first occurance of empty line (not selected)

// and this is the same
listBox1.Items.Remove(listBox1.Items[selLoc]);      
// Removes The first occurance of empty line (not selected)

//How can I remove a blank selected line?</pre>

electriac22-Oct-12 1:07

22-Oct-12 1:07

Found solution

selLoc = listBox1.SelectedIndex;
listBox1.Items.RemoveAt(selLoc);

V.22-Oct-12 1:27

22-Oct-12 1:27

5 because you found the answer I was about to give you, yourself Big Grin | :-D

V.

(MQOTD Rules )

BobJanova22-Oct-12 4:22

BobJanova

22-Oct-12 4:22

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.

electriac22-Oct-12 5:48

22-Oct-12 5:48

thanks for the explanation. I didn't feel I had an answer although the fix worked.

DaveyM6922-Oct-12 11:41

DaveyM69

22-Oct-12 11:41

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..

Dave

Binging is like googling, it just feels dirtier.
Please take your VB.NET out of our nice case sensitive forum.
Astonish us. Be exceptional. (Pete O'Hanlon)

BTW, in software, hope and pray is not a viable strategy. (Luc Pattyn)

electriac23-Oct-12 10:22

23-Oct-12 10:22

Yeah! I will just add it to my list of MS idiosyncratic anomalies.

DaveyM6923-Oct-12 22:36

DaveyM69

23-Oct-12 22:36

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 Smile | :)

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.

BobJanova24-Oct-12 6:27

BobJanova

24-Oct-12 6:27

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.

DaveyM6924-Oct-12 21:30

DaveyM69

24-Oct-12 21:30

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
    {
        // listBox added in designer
        // buttonRemove added in designer
        // buttonRemove Click handler added in designer
        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;
    }
}

electriac24-Oct-12 12:35

24-Oct-12 12:35

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.

Braj_1223-Oct-12 2:36

Braj_12

23-Oct-12 2:36

Use listBox1.Items.RemoveAt(ListBox1.SelectedIndex); in place of your code then it will remove your selected item from List Box.

electriac23-Oct-12 10:24