Import excel to datagridview in a specific manner

Question

0.00/5 (No votes)

See more:

Hi I am in the development of an application where I need to import excel to datagridview. I serached in google and came know the normal way of importing the excel to dataset and then filling the dataadapter and binding to datagridview, but I want to import in such a way that each column in excel is a row in my datagridview can anyone help me

Thanks in advance

Posted 30-Aug-11 23:58pm

CyborgForever

Add a Solution

2 solutions

Add a Solution

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

maestro_88 · Answer 1 · 2011-08-31T02:01:00

In your page load or button event enter the code:

C#

{

	dynamic DBConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ur excel file location") + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes\"");
	DBConnection.Open();

	string SQLString = "SELECT * FROM [ur excel sheet name$]";
	dynamic DBCommand = new OleDbCommand(SQLString, DBConnection);
	System.Data.IDataReader DBReader = DBCommand.ExecuteReader();

	gv.DataSource = DBReader;
	gv.DataBind();

	DBReader.Close();
	DBConnection.Close();
}

This code uses excel as DB and using a datareader binding the excel sheet values directy to gridview without hitting DB.

Hope this helps u..

maestro_88 · Answer 2 · 2011-08-31T00:36:00

C#

public void Exel2Sql()
{
OdbcConnection connection;
SqlBulkCopy bulkCopy;
string ConnectionString = @"server="servername";database=" ";uid=" ";pwd=" ";";
//Use your SQL servername ,userID and Password.
string connstr = @"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=filepath";
using (connection = new OdbcConnection(connstr))
{
OdbcCommand command = new OdbcCommand("Select * FROM [Sheet1$]", connection);
//you can change [Sheet1$] with your sheet name
connection.Open();
// Create DbDataReader to Data Worksheet
using (OdbcDataReader dr = command.ExecuteReader())
{
// Bulk Copy to SQL Server
using (bulkCopy = new SqlBulkCopy(ConnectionString))
{
bulkCopy.DestinationTableName = "Names";
//"Names" is the sql table where you want to copy all data.
bulkCopy.WriteToServer(dr);
}
dr.Close();
}
}
bulkCopy.Close();
connection.Close();
}

Use this code to move the data from excel to SQL and then bind the data in gridview .

Hope this helps you.

Import excel to datagridview in a specific manner

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Import excel to datagridview in a specific manner

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us