[RESOLVED] Excel & reading cells content

[Saeed]

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

            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();

[nelo]

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.

[Saeed]

Hi thanks for the reply

sample xls is attached within the zip file.

Rows 12-15 ,Column Z

[nelo]

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

[Saeed]

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

[Saeed]

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