xxxxxxxxxx
using System;
using System.Collections.Generic;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.XSSF.UserModel;
namespace WorkingWithExcel
{
public class Program
{
public static void Main()
{
try
{
//The following Collection object is going to hold the Excel Sheet Data
List<Employee> listEmployees = new List<Employee>();
//Set the Excel File Path
string FilePath = @"C:\Users\HP\source\repos\WorkingWithExcel\WorkingWithExcel\ExcelFiles\MyExcelFile.xlsx";
//For Import we need to create an instance of XSSFWorkbook class
XSSFWorkbook xssfWorkbook;
using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
{
xssfWorkbook = new XSSFWorkbook(file);
}
//The Excel File Might have multiple sheets
//So create an ISheet object by specifying the appropriate sheet name or index position
//ISheet sheet = xssfWorkbook.GetSheet("Sheet1"); //Based on the Sheet Name
ISheet sheet = xssfWorkbook.GetSheetAt(0); //Based on the Index Position (0-Based Index Position)
//If First Row is Header, then Initialize the row = 1 else row = 0 inside the for loop
//As our Excel Sheet Contains the First Row as a Header, we are setting row = 1
//The following Loop will start from the First data Rows till the last data row in the sheet
for (int row = 1; row <= sheet.LastRowNum; row++)
{
//null is when the row only contains empty cells
if (sheet.GetRow(row) != null)
{
//If the Row is not empty, then Fetch cell value and populate them with Employee Object
Employee emp = new Employee()
{
//For Accessing Numeric Cell Value we need to use NumericCellValue Property
//For Accessing String Cell Value we need to use StringCellValue Property
//For Accessing Boolean Cell Value we need to use BooleanCellValue Property
//First Cell is ID
ID = (int)sheet.GetRow(row).GetCell(0).NumericCellValue,
//Second Cell is Name
Name = sheet.GetRow(row).GetCell(1).StringCellValue,
//Third Cell is Address
Address = sheet.GetRow(row).GetCell(2).StringCellValue,
//Fourth Cell is Email
Email = sheet.GetRow(row).GetCell(3).StringCellValue,
//Fifth Cell is IsPermanent
IsPermanent = sheet.GetRow(row).GetCell(4).BooleanCellValue,
//Sixth Cell is Mobile
Mobile = sheet.GetRow(row).GetCell(5).StringCellValue,
//Seventh Cell is RegdNo
RegdNo = (int)sheet.GetRow(row).GetCell(6).NumericCellValue,
//Eighth Cell is Salary
Salary = (int)sheet.GetRow(row).GetCell(7).NumericCellValue,
};
//Add the employee data into listEmployees Collection
listEmployees.Add(emp);
}
}
//Once you import the data from the Excel File to your .NET Object, then you can do whatever you want
//Like Save the Data into the Database, Display the Data, Modify the Data, etc
//Here, we are going to Display the Excel Sheet Data
foreach (var emp in listEmployees)
{
Console.WriteLine($"ID: {emp.ID}, Name: {emp.Name}, Address: {emp.Address}, Salary: {emp.Salary}");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadKey();
}
}
public class Employee
{
public int ID { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string Email { get; set; }
public string Mobile { get; set; }
public bool IsPermanent { get; set; }
public int RegdNo { get; set; }
public int Salary { get; set; }
}
}
xxxxxxxxxx
string con =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp\test.xls;" +
@"Extended Properties='Excel 8.0;HDR=Yes;'";
using(OleDbConnection connection = new OleDbConnection(con))
{
connection.Open();
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
using(OleDbDataReader dr = command.ExecuteReader())
{
while(dr.Read())
{
var row1Col0 = dr[0];
Console.WriteLine(row1Col0);
}
}
}