How to open excel file in datagridview in c#

Question

1.00/5 (1 vote)

See more:

I'm trying to open excel file in datagridview ,the problem when I choose file doesn't show all file in datagridview ?

C#

OpenFileDialog op = new OpenFileDialog(); //tool to open the file

            op.Filter = "ALL Files |*.*|Excel Files |*.XLSX"; //select the type of file that want open it

            if (op.ShowDialog() == DialogResult.OK) //user chose the file and press ok
            {
                con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + op.FileName + ";Extended Properties=Excel 12.0"); //path of file(excel),and specifec the connection

                da = new OleDbDataAdapter("Select * from[sheet1$]", con);//get the data

                dt = new DataTable();

                da.Fill(dt);//save or put the data in data table
                this.dataGridView1.DataSource = dt;//show the table in grid view

Posted 31-Dec-15 9:41am

_Tuba

Updated 31-Dec-15 9:44am

v2

Add a Solution

Comments

ZurdoDev 31-Dec-15 15:59pm

What do you mean?

_Tuba 31-Dec-15 16:13pm

I have excel file when open it by this code doesn't show all file in datagridview just show F1 column and 2 cell

ZurdoDev 31-Dec-15 18:35pm

1. Click Improve question and show how you are mapping your columns. Or are you autogenerating columns?
2. Try changing your connection string to having this instead:
Extended Properties="Excel 12.0 Xml;HDR=YES"

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2015-12-31T10:45:00

What does you Excel sheet look like?

Loading data from an Excel sheet will only work so long as the entire table starts in A1, has no empty columns between columns of data and no empty rows between rows of data.

If you're trying to load the entire sheet with data and text all over the place, it's not going to work. It will only load data that looks like a table.