This article will show you how you can get or retrieve excel
file sheet name using ADO.Net in asp.net using c#.net.
Some of my previous articles are as follows: Search and Display Data In MVC WebGrid in
Asp.Net MVC Using C#.Net, Excel File Upload Or Import and Display In
GridView Using C# In Asp.Net, Export GridView Data To Excel Sheet Using
C#.Net In Windows Application, Asp.Net MVC Export Data to Excel File Of
WebGrid Using C#.Net.
After creating excel we will create an asp.net page.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ImportExcel.aspx.cs" Inherits="WebApplication7.ImportExcel" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Get Or Retrieve
Excel File Sheet Name Using ADO.Net in Asp.net Using C#</title>
</head>
<body>
<form id="form1" runat="server">
<div id="divSheetname"
runat="server">
</div>
</form>
</body>
</html>
|
Now first we will create the connection string to read the
excel sheet.
string excelFilePath = @"ExcelFile\StudentData.xls";
String strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" +
Server.MapPath(excelFilePath) + ";Extended
Properties='Excel 8.0;HDR=Yes'";
OleDbConnection con = new OleDbConnection(strExcelConn);
con.Open();
|
In above code I have defined the path of the excel sheet and
prepared the connectionstring. I have passed the connection string to OleDbConnection
provider to establish the connection. In above code one of the most important
thing is that you must use Server.MapPath
to read the file.
After making the connection I have read the excel file
sheets.
DataTable dtExcelSheet = new DataTable();
dtExcelSheet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; i <
dtExcelSheet.Rows.Count; i++)
{
sheetname = sheetname + dtExcelSheet.Rows[i]["TABLE_NAME"].ToString()
+ "<br/>";
}
divSheetname.InnerHtml =
sheetname;
|
Above code after reading all the sheets I have bind it the
div control to display it. Here is the complete code.
string excelFilePath = @"ExcelFile\StudentData.xls";
String strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" +
Server.MapPath(excelFilePath) + ";Extended
Properties='Excel 8.0;HDR=Yes'";
OleDbConnection con =
new OleDbConnection(strExcelConn);
con.Open();
//Read data sheet name
string sheetname = "";
DataTable dtExcelSheet = new DataTable();
dtExcelSheet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; i <
dtExcelSheet.Rows.Count; i++)
{
sheetname = sheetname + dtExcelSheet.Rows[i]["TABLE_NAME"].ToString()
+ "<br/>";
}
divSheetname.InnerHtml =
sheetname;
|
Now we Have done run the page to check the output.
About the Author
0 comments:
Please let me know your view