Click here to Skip to main content
15,037,168 members
Please Sign up or sign in to vote.
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

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.
   
v3
Comments
CyborgForever 31-Aug-11 6:42am
   
can it be done directly to datagridview with the help of datatable instead of first hitting the DB
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..
   
v4

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900