|
Hi All,
I have an excel file in this format, which I am trying to read using c#
04/05/2006 46 47
25 26 27
15 vgc 57
I am not able to read the values of row[1][0] and row[2][0], because in this column, the datatype of value in first row is date (04/05/2006).
In the same way, I am not able to read the row[2][1], because in this column, the datatype of value in first row is a numeric.
So, according to my perception, you need to have same datatype values in any particular column to read it in C#.
I am looking for any option to read these type of excel files with values of different datatypes in a particular column.
Any input will be highly appreciated.
FYI, This is the code snippet, I am using to read this excel file.
// Create connection string variable. Modify the "Data Source" parameter as appropriate for your environment.
String filePath = "D:\\ProjectInfo\\HU Mail\\test.xls";
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filePath + "; Extended Properties=\"Excel 8.0;HDR=NO;\"";
// Create connection object by using the preceding connection string.
OleDbConnection objConn = new OleDbConnection(sConnectionString);
// Open connection with the database.
objConn.Open();
DataTable dt = null;
// Get the data table containg the schema guid.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
String[] strSheetName = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach (DataRow row in dt.Rows)
{
strSheetName[i] = row["TABLE_NAME"].ToString();
i++;
}
string strComand = "select * from [" + strSheetName[2] + "]";
OleDbCommand objCmdSelect = new OleDbCommand(strComand, objConn);
// Create new OleDbDataAdapter that is used to build a DataSet based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
DataSet dsDeal = new DataSet();
// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(dsDeal, "LMPPrices");
for (int k = 0; k < dsDeal.Tables[0].Rows.Count; k++)
{
for (int j = 0; j < dsDeal.Tables[0].Columns.Count; j++)
{
Console.Write(dsDeal.Tables[0].Rows[k][j].ToString() + " -> ");
}
Console.WriteLine();
}
Console.ReadLine();
Best Regards,
|
|
|
|
|
This is kind of a hack-ish workaround, but how about reading every cell as a string and then using int.TryParse and DateTime.TryParse to determine what type each particular cell is?
|
|
|
|
|
|
|
Hi a have a problem whit the cancel lenghty opetations I must do it using the threads or asynchrone calls,witout bacground worker component in C# please help me to do it
Ira84
|
|
|
|
|
|
 First n foremost, try to write ur query in precise manner, so that person can get exact idea related to ur problem n base on it, u can get quick/better answer.
Regards
Chintan
www.visharadsoft.com
(Nothing is so purify as KNOWLEDGE)
|
|
|
|
|
Hi,
I'm trying to parse a database query entered into a TextBox in our webform using C#. Could someone tell me how to frame a reg expression, to extract all words between '"+, i.e.
if query is:
SELECT COL1, COL2 FROM TABLE1 WHERE COL1 = '" + str1 + "' AND COL2 = '" + str2 + "'
In the above expression, I want the values str1 and str2 in an arraylist.
Is this possible using regular expression, and is using regular expression the fastest (performance wise) way to achieve this?
Regards,
Blumen
|
|
|
|
|
\"\s*\+\s*([\w|\d|_]+)\s*\+\s*\" Will look for a quote, followed by some or no whitespace, then a plus, some more optional whitespace, then a group of letters, digits or underscores followed by some optional whitespace, a plus, optional whitespace and the closing quote.
Performance wise you will take a slight hit, if the regex isn't changing then mark it with RegexOptions.Compiled to compile it to IL. The benefit is that it will be easier to adapt if there (probably) is a bug or flaw in the regular expression or requirements change.
|
|
|
|
|
Hi,
Thank you for the response, I tried it but its not returning all instances of the words, and also returned word contains quotes and plus sign.
I'm using this code:
string pattern = @"""\s*\+\s*([\w|\d|_]+)\s*\+\s*""";
Regex re = new Regex(pattern);
MatchCollection matches = re.Matches(s);
string[] words = new string[matches.Count];
for (int i=0; i
|
|
|
|
|
blumenhause wrote: I tried it but its not returning all instances of the words
Can you provide an example which it's not matching?
blumenhause wrote: and also returned word contains quotes and plus sign.
That's probably because \w includes these or something, try changing the bit between the [] to:
[A-z|0-9|_] Which will be just any letter, number or underscore.
|
|
|
|
|
I am using the code i provided in my second interaction.
Ed.Poore wrote: Can you provide an example which it's not matching?
blumenhause wrote:
select col1, col2, col3 from table1 where col1 = '" + str1 + "' and col3 = '" + str2 + "'
Regards,
Blumen
|
|
|
|
|
Regex regex = new Regex(@"\""\s*\+\s*(?<varname>[A-z|0-9|_]+)\s*\+\s*\""", RegexOptions.Compiled);
string input = @"select col1, col2, col3 from table1 where col1 = '"" + str1 + ""' and col3 = '"" + str2 + ""'";
foreach (Match match in regex.Matches(input))
{
Console.WriteLine(match.Groups["varname"]);
}The above code works fine, notice that I've introduced a name for the group (?<varname>...) which makes the group easier to reference later on in the code. That I think it what you were not doing properly before.
|
|
|
|
|
I'm using Windows 2000 Advanced Server SP4 and I have tried the code snippet below:
WindowsIdentity winid = WindowsIdentity.GetCurrent();
AppDomain.CurrentDomain.SetPrincipalPolicy(PrincipalPolicy.WindowsPrincipal);
WindowsPrincipal prin = (WindowsPrincipal)Thread.CurrentPrincipal;
MessageBox.Show(prin.IsInRole(Environment.MachineName + @"\" + "test").ToString());
My computer is not a domain controller and I dont have active directory installed. I created a group on my local machine named 'test' and added administrator to that group as a member and logged on to windows with administrator account. So the code above returns True and displays it. This is fine but when I remove administator from the group 'test' the code still shows True. This persists until I delete the group itself. Then the code displays 'False'. What do you think is the cause of this behavior?
I also have a problem retrieving a specific user's groups. I thought the IdentityReferenceCollection class would be useful and I used the code below:
IdentityReferenceCollection irc = winid.Groups;
foreach (IdentityReference var in irc)
{
MessageBox.Show(var.Value);
}
But this only displays some numbers that might have meaning to windows itself. but not a visual appearance of the groups on my local machine. such as a string conataining the groups' names. Any idea?
|
|
|
|
|
|
Exporting SQL Server 2005 Reporting Services reports to PDF khuzwayom 57mins ago
Hi All
I would like to implement the functionality offered by SQL Server 2005 Reporting Services that exports the report to PDF, but I want to do it in my C# 2005 application.
Can I do this? How?
Please help.
khuzwayom
khuzym
khuzym
|
|
|
|
|
I want to have a loop that checks if a key has been pressed during that time.
The loop has a delay of Time.Sleep(25);
So the loop starts, check if a key is down then continues with the code, the time delay hits then it repeats.
The problem is I can't use ReadKey Read or ReadLine because these all stop the "flow" of the code causing it to physically halt, I want to code(loop) to continue if a key is pressed or not.
And System.Windows.Forms; isnt an option as far as I know.
Thanks guys.
|
|
|
|
|
Hi
try using different thread in order to check if a key was pressed
for example
static void Main(string[] args)
{
object key = new object();
bool stopLoop = false;
Thread t1 = new Thread(delegate()
{
while (Console.ReadLine() != "Q") ;
lock (key)
{
stopLoop = true;
}
Console.WriteLine("Stop Command Issued");
});
t1.Start();
while (true)
{
lock(key)
{
if (stopLoop == true)
break;
}
}
}
|
|
|
|
|
Wow thanks!
This is great, but is there anyway to use
ConsoleKeyInfo
.key
ConsoleKey
etc instead?
Thanks in advance ^0^
|
|
|
|
|
Nevermind I figured it out on my own, all I had to do was change/add these two lines.
^O^. Thanks!
ConsoleKeyInfo keyInput = new ConsoleKeyInfo();
while (Console.ReadKey().Key != ConsoleKey.Escape) ;
|
|
|
|
|
Ran into a slight problem.
This performs actions within the while without stopping to check for the key but how do you add additional keys?
So the loop goes constantly checking for an escape key press, and if it happens it breaks the loop.
but what if I want it to perform actions based on other keys while its looping?
If you got any info that would be great. =O)
-- modified at 16:12 Monday 18th June, 2007
Don't worry again I think I cracked it.
|
|
|
|
|
If anyone is interested in how this is done. I basically modified it so that it reads the key in the thread inside the loop over and over, and it does things according to whatever the key is. This is just an example.
static void Main(string[] args)
{
object key = new object();
bool stopLoop = false;
ConsoleKeyInfo keyInput = new ConsoleKeyInfo();
Thread t1 = new Thread(delegate()
{
while (keyInput.Key != ConsoleKey.Escape) // repeats the below code until...
{
keyInput = Console.ReadKey(); // Constantly checks for pressed key
if (keyInput.Key == ConsoleKey.UpArrow) // does things depending on key
{
Console.WriteLine("Up Arrow");
}
if (keyInput.Key == ConsoleKey.Escape) // the loop break
{
lock (key)
{
stopLoop = true;
}
}
}
});
t1.Start();
while (true)
{
Thread.Sleep(200); // Putting a delay in so it doesnt run at a billion mph
Console.Write("01"); // Writing numbers just to visually check the speed
lock (key) // This stops the loop if Escape is pressed as defined above.
{
if (stopLoop == true)
break;
}
}
}
}
}
|
|
|
|
|
Hello everybody!
My team and I is facing a big problem write now, and after two days of searching and testing we didn't succeed!
We have to close a Database File (SQLite) on our local machine, which was opend from our process.
This is kind of a workaround, because the Database crashes thanks to a bug in SQLite, and after that the file is locked!
What we found out is that it would be much more simple, if we wanted to close a locked file from across network shared computer.
Like this[^] very nice post, from Heath Steward shows.
In this case we could use netapi32.dll methods.
Maybe someone can point us the write direction.
-- modified at 6:44 Monday 18th June, 2007
As a workaround we now start a freeware program "unlocker" which works.
But this is the soulution we do not prefere (Which is all said by "workaround")!
Thanks for your time!
All the best,
Martin
-- modified at 6:48 Monday 18th June, 2007
|
|
|
|
|
hi gurus,
I have C# application with ms-access back-end, this application is running on remote side. means already deployed, now i have to make some changes in tables which i had already made through alter table in oledbcommand object, but when turn comes to make changes in query object i got stucked. does any body have any idea how can i alter query object of access at run time through c#.
|
|
|
|
|
Try this.
Smile: A curve that can set a lot of things straight!
(\ /)
(O.o)
(><)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
