|
I suppose, but I prefer to limit reliance on third-party products.
|
|
|
|
|
I have the following function which I got online, and have changed to suit my needs :-
private Boolean SheetExists(string strFilename)
{
Boolean Match = false;
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strFilename + ";Extended Properties=Excel 8.0;";
objConn = new OleDbConnection(connString);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
for (int j = 0; j < excelSheets.Length; j++)
{
if (excelSheets[j] == "Sheet1$")
{
Match = true;
}
}
if (Match == true)
return true;
else
return false;
}
finally
{
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
Basically it checks if sheet1 exists in a workbook. I'm currently using the variable "Match" to obtain a "return true" or a "return false". I'm sure it possible to modify the code so that it returns the true/false directly, I just can't figure out how.
Any ideas? Thanks
|
|
|
|
|
Modify your test so it breaks out of the loop as soon as a match is found
for (int j = 0; j < excelSheets.Length; j++)
{
if (excelSheets[j] == "Sheet1$")
{
Match = true;
break;
}
}
//delete the following lines after the loop
if (Match == true)
return true;
else
return false;
// add this line after the finally block
return Match;
[edit]
You could actually check the names in your foreach block to make it more efficient.
[/edit]
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
Brilliant - now that I see it, it makes perfect sense. Taking some time to get used to C# from VBA 
Thank you for your help 
|
|
|
|
|
My pleasure; good luck with the rest of the project.
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
Just saw your edit about checking the names. Good idea. I changed the code to
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
if (excelSheets[i] == "Sheet1$")
{
Match = true;
break;
}
i++;
}
Thanks again!
|
|
|
|
|
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
