Following things to do:
1. Have to add reference a Microsoft Excel Library
2. Using this at namespace:
using Excel = Microsoft.Office.Interop.Excel;
3. And have to add this:
Excel.Workbook workBook = app.Workbooks.Open(sFullPath, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
here sFullPath=File location where the excel file actuall has;
4. And then assign sheet to worksheet
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[sheetName];
5. And now have to search in the sheet for value...
6. If found then send it to customize data table.
if the data table got then just published where you want (In Grid View or Data Grid or Html Table)
in C# for a fixed sheet:
DataTable dtExcel = new DataTable();
dtExcel.Columns.Add("ID", typeof(string));
dtExcel.Columns.Add("Name", typeof(string));
dtExcel.Columns.Add("Gender", typeof(string));
Excel.ApplicationClass app = new Excel.ApplicationClass();
string sFullPath="D:\Text.xls";//for the file Test.xls
Excel.Workbook workBook = app.Workbooks.Open(sFullPath, 0, true, 5, "", "", true,Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[sheetName];
//Sheet Name depend on from which sheet you want to pick data
// and now its time to retrieve data and set into a data table
//here you can add some condition
while (((Excel.Range)workSheet.Cells[index, 2]).Value2 != null) //while the value is null
{
if (!((Excel.Range)workSheet.Cells[index, 2]).Value2.ToString().Trim().Equals(""))
{
DataRow dRowExcel = dtExcel.NewRow();
string EmpID = "";
dRowFxAl["EmpID"] = ((Excel.Range)workSheet.Cells[index, 1]).Value2.ToString().Trim();
if (dtEmp.Rows.Count > 0)
{
for (int Looper = 0; Looper < dtEmp.Rows.Count; Looper++)
{
dRowExcel["ID"] = ((Excel.Range)workSheet.Cells[index, 2]).Value2.ToString().Trim();
dRowExcel["Name"] = ((Excel.Range)workSheet.Cells[index, 3]).Value2.ToString().Trim();
dRowExcel["Gender"] = ((Excel.Range)workSheet.Cells[index, 4]).Value2.ToString().Trim();
dtExcel .Rows.Add(dRowExcel);
// ok now data table retrieve completed.....
// the logic is right but may b some syntax error......
Download Script
Live Demo
