Import to Gridview from Excel Sheet (xlsx)

To import from an xlsx file to a Gridview, the whole code will be same as the post before, but the connection string should be changed.
It should be made: -


OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" +
Server.MapPath("~/App_Data/Running.xlsx") + ";" + "Extended Properties=\"Excel 12.0;HDR=Yes\"");

Rest is same.
The code will run properly.

Now the challenge is to select the path of the file at runtime.

38 visitors

Import to Gridview from Excel Sheet (xls)

Step 1 – Create Excel worksheet
1) Open Microsoft Excel and create a new Worksheet.
2) The sample data is as follows: - (any sample data)
We will keep the default name for the Worksheet (Sheet1).
3) Let us name and save the excel file as Running.xls.
Note: You can download the Excel file along with the code files from the Downloads section at the end of this article.

Step 2 –Display Excel Data using GridView Control
1) Start Visual Studio 2005.
2) Select Create Website and choose the Template ASP.NET Web Site. We can choose the language as C#/VB. Set the name for the project as ExcelGV.
3) Add the existing Excel file (Running.xls) that we created to the App_data folder.
4) Add a new .aspx file. Choose language C#/VB and, depending on the language, we will set the appropriate names: ExcelGVCS.aspx for C# or ExcelGVVB for VB.NET.
4) Drag and drop the GridView control on the .aspx page.
5) We will add the following statement in code behind above the namespace section.

-----------------------------------------------------------------------------------
using System.Data.OleDb;
------------------------------
protected void Page_Load(object sender, EventArgs e)
{
OleDbConnection DBConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/App_Data/Running.xls") + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes\"");
DBConnection.Open();
string SQLString = "SELECT * FROM [Sheet1$]";
OleDbCommand DBCommand = new OleDbCommand(SQLString, DBConnection);
IDataReader DBReader = DBCommand.ExecuteReader();
GridView1.DataSource = DBReader;
GridView1.DataBind();
DBReader.Close();
DBConnection.Close();
}
-----------------------------------------------------------------------------------
38 visitors

GridView to Excel Sheet

Excel Sheet to GridView: -
Write this code in button click event: -
-------------------------------------------------------------------------------------
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
myDataGrid.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
-------------------------------------------------------------------------------------