Answer 1

Hi Peter,

Not sure how you display the data, but I see some problems in your code such as lacking a quote at the end of the connectionString and no need to explicitly call con.Open() to use dataset storing the data .

Here is a sample code that works with a datagridview to show the data from an Excel sheet:

            string connectString =
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\\testit.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";
            OleDbConnection conn = new OleDbConnection(connectString);
            OleDbDataAdapter da = new OleDbDataAdapter("Select * From [Sheet1$]", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            dataGridView1.DataSource = dt;

Please try again and if have any problem please let me know.

Thanks.

Figo Fei
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com 

Answer 2

Connection is not my problem.

I can read  the data, but the data I read is not correct.

Can you try this to see if you get the same result.

In Cell A1 through A8 key in a date and format those cells as date

in Cell A9 key in a number

Then read the spreadsheet and see if you get a number in row[8][0]

Answer 3

Hi Peter,

It doesn't matter how we set the format in the Excel sheet. I got the correct format as always, can you let me know your display and data retrieving code?

Or can you just create a new project with the code I provided to see if there is any problem?

Thanks.

Figo Fei
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com 

Answer 4

Hi Peter,

It doesn't matter how we set the format in the Excel sheet. I got the correct format as always, can you let me know your display and data retrieving code?

Or can you just create a new project with the code I provided to see if there is any problem?

Thanks.

---

Figo Fei
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com 

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

I've created the project using your code and I am getting the same results, although I can not duplicate it when I create the spreadsheet, but all of the spreadhseets comming from a customer having the same problem so I am still stuck.

I can send you the spreadsheet if you want.

Answer 5

Thanks for your response, Peter.

You can send it to v-fifei<at>microsoft<dot>com (with this thread's url)

Thanks.

Figo Fei
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com 

Answer 6

Thank You

I have emailed the problem spreadsheet.

Peter

Answer 7

Hi Peter,

I've recieved your mail, Thanks.

As the format of your sheet's G column is inconsistent, I suggest you to read  the specific range instead of the whole sheet.

The code looks like:

            string connectString =
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\testit.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";

            OleDbConnection conn = new OleDbConnection(connectString);
            OleDbDataAdapter da = new OleDbDataAdapter("Select * From [Sheet1$G19:G45]", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);

or

            conn.Open();
            OleDbCommand cmd = new OleDbCommand("Select * From [Sheet1$G19:G45]", conn);
            OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            while (reader.Read())
            {
                object item = reader.GetValue(0);
                textBox1.AppendText(item.ToString() + Environment.NewLine);
            }
            reader.Close();

HTH.

Thanks.

Figo Fei
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com 

Answer 8

Thank for the suggestion, it might be the workaround.

The problem is I have to convert the entire worksheet to .CSV format and Excel is not installed on the computer.

Answer 9

Hi Peter,

For more details about the excel  development issue, you can also use http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads for more suggestions.

Have a nice day.

Figo Fei
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com 

Answer 10

Hi Peter,

For more details about the excel  development issue, you can also use http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads for more suggestions.

Have a nice day.

---

Figo Fei
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg@microsoft.com 

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Thank you for your help!