using ClosedXML.Excel;
using DocumentFormat.OpenXml.Spreadsheet;
using ExcelExample.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelExample.Excel
{
    public class AddExcelClass
    {
        public DataView GetDataView(string path)
        {
            OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;data source='"+ path + "';Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"");
           connection.Open();
            OleDbDataAdapter dataAdapter;
            dataAdapter = new OleDbDataAdapter("select * from [Sheet1$A1:D]", connection);
            DataTable dataTable = new DataTable();  
            dataAdapter.Fill(dataTable);
            return dataTable.DefaultView;
        }
        public List<UserModel> GetUsers(string path)
        {
            List<UserModel> users = new List<UserModel>();
            using(var workbook = new XLWorkbook(path))
            {
                var worksheet = workbook.Worksheet(1);
                var FirestCell = worksheet.FirstCellUsed();
                var lastCell = worksheet.LastCellUsed();
                if(FirestCell != null && lastCell !=null)
                {
                    var FirestRow = FirestCell.Address.RowNumber;
                    var LastRow = lastCell.Address.RowNumber;
                    var FirestColumn = FirestCell.Address.ColumnNumber;
                    var LastColumn = lastCell.Address.ColumnNumber;
                  
                   for(int row=FirestRow+1;row<=LastRow;row++)
                    {
                        UserModel userModel = new UserModel();
                        userModel.Name = worksheet.Cell(row,1).Value.ToString();
                      userModel.Family = worksheet.Cell(row,2).Value.ToString();
                      userModel.Email = worksheet.Cell(row,3).Value.ToString();
                      userModel.Number = worksheet.Cell(row,4).Value.ToString();
                        users.Add(userModel);   
                    }
                }
            }
            return users;
        }


    }
}
