|
Why not really simplify it?
private Boolean SheetExists(string strFilename)
{
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strFilename + ";Extended Properties=Excel 8.0;";
using (OleDbConnection objConn = new OleDbConnection(connString))
{
objConn.Open();
using (System.Data.DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
{
foreach (DataRow row in dt.Rows)
{
if (row["TABLE_NAME"].ToString() == "Sheet1$")
{
return true;
}
}
return false;
}
}
}
|
|
|
|
|
Good question, and great answer. I'll give this a try when I get to work. Thank you!
|
|
|
|
|
How could I modify this code so I could check for 2 sheet names, for example, Sheet1 and Sheet2?
Thanks.
|
|
|
|
|
Just change the conditional in the loop.
From:
if (row["TABLE_NAME"].ToString() == "Sheet1$")
{
return true;
}
To:
string tableName = row["TABLE_NAME"].ToString();
if (tableName == "Sheet1$" ||
tableName == "Sheet2$")
{
return true;
}
If, instead, you have an array or List<string> containing multiple sheet names (for example, called SheetNames), you could do it like:
if (SheetNames.Contains(row["TABLE_NAME"].ToString()))
{
return true;
}
Unless he list of sheet names is used in several places in the code, the direct comparisons in the tableName == ... case is probably the way to go.
|
|
|
|
|
That's great, thanks. Maybe you could help with a similar issue, as I'm really not sure on the best way to approach this...
I have this :-
switch (SuppCode)
{
case "COR1":
Sheet1Name = "Sheet1";
Sheet2Name = null;
Sheet3Name = null;
break;
case "HAV1":
Sheet1Name = "Summary";
Sheet2Name = "Unit_Rates";
Sheet3Name = "Other_Charges";
break;
}
Basically, I can't figure out how to check for the correct sheet names based on the Switch. There will never be more than 3 sheets to check for, but there might be less than 3 - hence the null. Sheet1Name, etc are used in other functions, so I'd like to keep them...
Any thoughts?
|
|
|
|
|
It should be just like my "middle" case:
string tableName = row["TABLE_NAME"].ToString();
if (tableName == Sheet1Name ||
tableName == Sheet2Name ||
tableName == Sheet3Name)
{
return true;
}
It doesn't matter if any of the Sheet?Name values are null, the individual comparisons will be false which is exactly the behavior you want.
|
|
|
|
|
Ok, as a test I entered :-
foreach (DataRow row in dt.Rows)
{
string tableName = row["TABLE_NAME"].ToString();
if (tableName == Sheet1Name + "$" ||
tableName == Sheet2Name + "$" ||
tableName == Sheet3Name + "$")
{
Exists = true;
}
}
if (Exists == true)
{
MessageBox.Show("Match");
}
else
MessageBox.Show("No Match");
and even when Sheet3Name isn't in the spreadsheet...
if (Exists == true)
...is called.
Where am I going wrong?
|
|
|
|
|
Ok, this does what I want :-
if (Sheet1Name != null)
{
foreach (DataRow row in dt.Rows)
{
if (row["TABLE_NAME"].ToString() == Sheet1Name+"$")
{
Exists = true;
}
}
}
if (Sheet2Name != null)
{
Exists = false;
foreach (DataRow row in dt.Rows)
{
if (row["TABLE_NAME"].ToString() == Sheet2Name + "$")
{
Exists = true;
}
}
}
if (Sheet3Name != null)
{
Exists = false;
foreach (DataRow row in dt.Rows)
{
if (row["TABLE_NAME"].ToString() == Sheet3Name + "$")
{
Exists = true;
}
}
}
if (Exists == true)
{
MessageBox.Show("Match");
}
else
MessageBox.Show("No Match");
...but surely there must be a more efficient way of coding this?
|
|
|
|
|
See my previous posting.
Also, this has a problem.
If Sheet2Name != null then the checking for Sheet1Name is ignored! (Exists is reset to false!)
Likewise if Sheet3Name != null then the checking for Sheet1Name and Sheet2Name are ignored.
This also, requires 3 loops through the dt.Rows.
|
|
|
|
|
Matt,
Out of curiosity (as I'll be using your approach) if Sheet2Name != null why is checking for Sheet1Name ignored?
As I read it (if I have 1 sheetname to check), Sheet 1 is checked and Exists is set to true. Sheet2Name and Sheet3Name will be Null, and therefore their loops will be skipped, leaving Exists at true. No?
|
|
|
|
|
I'm assuming that you need to check if all of the non-null sheet names are present (as I said in the previous posting). Your first posting with the Sheet?Name variables indicated that 1, 2 or all 3 Sheet?Name variables could be non-null.
So you first check if the Sheet1Name is present, if so, you set Exists = true.
Then, if Sheet2Name != null the first thing you do is set Exists = false. At this point you have no information about whether Sheet1Name was present or not. You are effectively ignoring the Sheet1Name status.
You can easily remedy this by adding Exists && before the Sheet2Name != null (and Sheet3Name case).
This implicitly propagates the previous sheet name checking to the next checking step. It also, avoids pointless effort if an earlier step fails.
|
|
|
|
|
Yes, it's clear your method is better.
Previously I was setting Exists to false to "reset" it, so to speak. My thinking was, that if Sheet2Name != Null, then I wanted Exists to equal false unless Sheet2Name was present.
I can now see where I was going wrong, and how your approach is more efficient. I'll use it going forward.
Thanks again.
|
|
|
|
|
I think I see the problem.
The code I gave was to check if any of Sheet1Name, Sheet2Name or Sheet3Name are in the DB. Are you trying to check if all of non-null values of Sheet1Name, Sheet2Name or Sheet3Name are represented as rows in the DB?
If that is the case, you will need more complicated checking and keeping track of matching each of the sheet names separately.
Try something like:
The code as you have shown it will throw a NullReferenceException if any of Sheet1Name, Sheet2Name or Sheet3Name are null. You can use the ?? operator to make this safe:
bool Exists1 = Sheet1Name == null;
bool Exists2 = Sheet2Name == null;
bool Exists3 = Sheet3Name == null;
string Sheet1NameSuffix = (Sheet1Name ?? "") + "$";
string Sheet2NameSuffix = (Sheet2Name ?? "") + "$";
string Sheet3NameSuffix = (Sheet3Name ?? "") + "$";
foreach (DataRow row in dt.Rows)
{
string tableName = row["TABLE_NAME"].ToString();
if (tableName == Sheet1NameSuffix)
{
Exists1 = true;
}
if (tableName == Sheet2NameSuffix)
{
Exists2 = true;
}
if (tableName == Sheet3NameSuffix)
{
Exists3 = true;
}
}
if (Exists1 && Exists2 && Exists3)
{
MessageBox.Show("Match");
}
else
MessageBox.Show("No Match");
|
|
|
|
|
Matt, your approach looks far more "tidy" than mine 
It works perfectly now. Thank you so much for your time, I've learnt a lot! Enjoying C# so much more than VBA, and your advice has helped with that too!
Thanks again! 
|
|
|
|
|
Dear Expert,
I had successfull develop class for data collection however its display in command prompt, however how do i display this info in form textbox1, following is my class code
------------------------------------------------------------------------------------
using System;
using System.Management;
namespace remoteconnect
{
class Program
{
static void Main(string[] args)
{
ManagementScope scope =
new ManagementScope(
"\\\\computername\\root\\cimv2");
scope.Connect();
ObjectQuery query = new ObjectQuery(
"SELECT * FROM win32_computersystem");
ManagementObjectSearcher searcher =
new ManagementObjectSearcher(scope, query);
ManagementObjectCollection queryCollection = searcher.Get();
foreach (ManagementObject m in queryCollection)
{
if (m.Properties["IsValid"].Value != null && m.Properties["IsValid"].Value.ToString() == "True")
m.Properties[""].Value.ToString();
}
}
}
}
----------------------------------------------------------------------------------------------------
In Form yet nothing but created texbox and button
----------------------------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace remoteconnect
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
}
}
}
----------------------------------------------------------------------------------------------------------
would you please assist in regards....
|
|
|
|
|
You could use a ListBox[^] or similar control.
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
in this case what will be the cript
to trigger the data in listbox
|
|
|
|
|
That is something you need to decide based on your program requirements. You could do it when the program starts and the form first loads, or you could do it in response to some external event such as a button press.
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
Did you want a click on the Button to trigger the reading of the Properties collection, and the writing of some data in each Property to the TextBox ? That's a guess
Let's say you name your Button "WriteQueryCollectionBtn," and your TextBox "PropertyTextBx" ...
In that case, perhaps your Button's Click EventHandler might look like:
private void WriteQueryCollectionBtn_Click(object sender, EventArgs e)
{
foreach (ManagementObject m in queryCollection)
{
if (m.Properties["IsValid"].Value != null
&& m.Properties["IsValid"].Value.ToString() == "True")
{
PropertyTextBx.Text += m.Properties[""].Value.ToString() + Environment.NewLine;
}
}
}Note that for this code to work in the Form, you would have to also reference the System.Management library with a 'using statement.
It's not clear here if you are going to "connect" and retrieve the data once, when the application starts (as you are doing now), or:
If you are going to want to pull-down the data every time the Button is clicked: my guess is that you'd want to refresh the data each time the Button is clicked, which will require you re-architect your code shown here.
The question here is: does the data change, or is it always the same ?
Again, this reply is pretty much a "blind guess" at what you might want to do. You'll have to be much more specific about your goals here, and the nature of the data, if you want more specific feedback.
good luck, Bill
|
|
|
|
|
I have a problem reading lines and showing them from a text file in a ListBox using Windows Form Application.
I made some code but now I'm stucked in a place where I made a comment.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
namespace LlogariaBankare
{
public partial class KlientForm : Form
{
public string pathi = "Klientet.txt";
public KlientForm()
{
InitializeComponent();
}
private void KlientForm_Load(object sender, EventArgs e)
{
this.ShtoButon.Enabled = true;
this.AzhuroButon.Enabled = false;
this.FshijButon.Enabled = false;
}
private void ShtoButon_Click(object sender, EventArgs e)
{
StreamWriter swKlientet = new StreamWriter(pathi, true);
swKlientet.WriteLine(emriBox.Text + "\t" + mbiemriBox.Text + "\t" +
datelindjaBox.Text + "\t" + vendlindjaBox.Text + "\t" +
xhirollogariaBox.Text + "\t");
swKlientet.Close();
}
private void ListBox_SelectedIndexChanged(object sender, EventArgs e)
{
}
}
}
|
|
|
|
|
like StreamWriter you can use StreamReader to read text files.
Check the examples here MSDN StremReader class[^]
Jibesh.V.P
India
|
|
|
|
|
You should not be putting the file reading code in the SelectedIndexChanged event or it will be repeated every time the selection changes. You should initialise your ListBox after the form is first loaded, or in some other method.
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
OK, now i konw how to read a text file and put it in a listbox, but the problem is that when I type a new line it doesn't show me that new line in Listbox until i debug again the form.
I solved the problem creating a method on form load.
Do I need to create a new method to reload the text file again or what ever.
Cheers in advance for your reply.
|
|
|
|
|
dr_iton wrote: Do I need to create a new method to reload the text file again or what ever.
Not necessarily, but you do need to create a method that can add any extra information as it is entered into the program. These controls can be updated at any time by adding or removing entries from them, you do not need to reload them from the beginning.
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
After all I have the following problem.
I can remove the lines from listbox, but those lines are not deleted from text file, how to delete a specific line from a text file.
Cheers in advance for your reply.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
