Open and Read an Excel Spreadsheet into a ListView in Asp.NET
In this article, we will show how to fill a
ListView Control with the data loaded into a DataTable. You may use a DataTable bind it to a Grid Control to show the output of a query, but data binding of controls is not always the ideal method of accessing the data (You may encounter problems with the DataBinding). A DataTable maintains a copy of the entire resultset in the client systems memory in case you need to make changes to a row. Instead of using a bound grid and a DataTable, we can use the listview control with the view set to detail mode and fill it with the data from a DataTable.
Demo
Take one Button and one ListView on a windows form. And set some properties of the ListView as per given below.
// Set the view to show details.
listView1.View = View.Details;
// Allow the user to edit item text.
listView1.LabelEdit = true;
// Allow the user to rearrange columns.
listView1.AllowColumnReorder = true;
// Select the item and subitems when selection is made.
listView1.FullRowSelect = true;
// Display grid lines.
listView1.GridLines = true;
// Sort the items in the list in ascending order.
listView1.Sorting = SortOrder.Ascending;
After setting the above properties of ListView web form look as below figure.
Write below code on button ‘Display’
private void btnDisplay_Click(object sender, EventArgs e)
{
// Create Data Table for MS-Office 2007 or 2003
System.Data.DataTable dtExcel = new System.Data.DataTable();
//DataTable Name
dtExcel.TableName = "MyExcelData";
//Path of excel file
string SourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='F:\avi\information.xlsx';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
//connectin
OleDbConnection con = new OleDbConnection(SourceConstr);
//Query string
string query = "Select * from [Sheet1$]";
//DataAdapter object
OleDbDataAdapter data = new OleDbDataAdapter(query, con);
//fill record into DataTable
data.Fill(dtExcel);
// Attach Subitems to the ListView
listView1.Columns.Add("Name", 100, HorizontalAlignment.Left);
listView1.Columns.Add("Address", 100, HorizontalAlignment.Left);
listView1.Columns.Add("Contact", 100, HorizontalAlignment.Left);
listView1.Columns.Add("Email Id", 100, HorizontalAlignment.Left);
// Clear the ListView control
listView1.Items.Clear();
// Display items in the ListView control
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
DataRow drow = dtExcel.Rows[i];
// Only row that have not been deleted
if (drow.RowState != DataRowState.Deleted)
{
// Define the list items
ListViewItem lvi = new ListViewItem(drow["Name"].ToString());
lvi.SubItems.Add(drow["Address"].ToString());
lvi.SubItems.Add(drow["Contact"].ToString());
lvi.SubItems.Add(drow["Email Id"].ToString());
// Add the list items to the ListView
listView1.Items.Add(lvi);
}
}
}
Output
Leave Comment