1 Attachment(s)
[RESOLVED] Excel & reading cells content
I have functions that use
OleDbCommand , OleDbDataReader and OleDbCommand
to read values of cells within an excel sheet.
it works fine for everything else , however in some cases like:
Row 12-15 ,Column Z , it returns null and i have no idea why
What is about these cells that is giving me the grief ..
Cheers
Code:
OleDbCommand query = new OleDbCommand(strSql, connection);
reader = query.ExecuteReader();
// blaa blaa
while (reader.Read())
{
foreach(string column in columns)
{
iColumn++;
string x = null;
try
{
x = reader[column].ToString();
Re: Excel & reading cells content
Quote:
Originally Posted by Saeed
it works fine for everything else , however in some cases like:
Row 12-15 ,Column Z , it returns null and i have no idea why
What is about these cells that is giving me the grief ..
What is the data in the problematic cells? If you could give us that information it might be possible to find a solution.
Re: Excel & reading cells content
Hi thanks for the reply
sample xls is attached within the zip file.
Rows 12-15 ,Column Z
Re: Excel & reading cells content
Quote:
Originally Posted by
Saeed
Hi thanks for the reply
sample xls is attached within the zip file.
Rows 12-15 ,Column Z
Thanks. I opened the zip. I don't normally don't open attachments here unless absolutely necessary. In this case I was confident doing so would be useful. I'm not sure what others think about it but to me it seems like it better to put content on the post rather than on the attachment. In my case it was quick to open the attachment but I was not inclined to doing so from the beginning...Anyway the problem is that the ADO.NET is infering the schema based on the content in the first few rows. In the first set of rows we have what look to be integers there. Then we have rows with numbers and letters hence the problem.
Try and do one or all of the following:
1. Change the format of the whole column to 'Text' in Excel.
2. See if it makes a difference by calling 'reader.GetValue()'. This is a long shot really...:)
Re: Excel & reading cells content
Have tried all that. removing locks on cell formulas , sheet
again once pasted from other cells its OK , as soon as you modify it
you wont get anything back
Re: Excel & reading cells content
Ok for those that may have the same problem here is my conclusion:
DO NOT use Ole
use Microsoft.Office.Interop.Excel
Using that I managed to read/fetch what was in the spreadsheet regardless of what other internal attaributes may affect Ole in not offering back what is in the XLS.
Cheers