Untitled

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using ClosedXML.Excel;

namespace ImportExcelUsingClosedXML
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
            //Persons
            List<Person>  persons = ImportExcel5<Person>(Environment.CurrentDirectory + "\\Data\\person.xlsx", "Persons");
            // Now 'products' contains the data from the Excel file
            foreach (var person in persons)
            {
               
                Console.WriteLine($"Name: {person.Name}, Lastname: {person.LastName}, State: {person.State}, Street: {person.Street}");
            }
            //Products
            List<Product> products = ImportExcel5<Product>(Environment.CurrentDirectory + "\\Data\\Products.xlsx", "Products");
            // Now 'products' contains the data from the Excel file
            foreach (var product in products)
            {
                Console.WriteLine($"Name: {product.Name}, Price: {product.Price}, Units: {product.Units}");
            }
        }
        // viet ham va class duoi nay

        // hàm này ok
        public List<T> ImportExcel5<T>(string excelPath, string sheetName) where T : new()
        {
            List<T> dataList = new List<T>();
            using (XLWorkbook workbook = new XLWorkbook(excelPath))
            {
                IXLWorksheet worksheet = workbook.Worksheet(sheetName);

                if (worksheet != null)
                {
                    var columnNames = worksheet.FirstRow().CellsUsed().Select(c => c.Value.ToString()).ToList();

                    for (int i = 2; i <= worksheet.LastRowUsed().RowNumber(); i++)
                    {
                        T obj = new T();

                        for (int j = 1; j <= worksheet.LastColumnUsed().ColumnNumber(); j++)
                        {
                            PropertyInfo propertyInfo = typeof(T).GetProperty(columnNames[j - 1]);

                            if (propertyInfo != null)
                            {
                                var cell = worksheet.Cell(i, j);

                                try
                                {
                                    // Check if the cell is empty
                                    if (!cell.IsEmpty())
                                    {
                                        // Handle conversion based on the property type
                                        if (propertyInfo.PropertyType == typeof(decimal))
                                        {
                                            // Handle decimal conversion
                                            var stringValue = cell.Value.ToString();
                                            var convertedValue = Convert.ToDecimal(stringValue, CultureInfo.InvariantCulture);
                                            propertyInfo.SetValue(obj, convertedValue);
                                        }
                                        else if (propertyInfo.PropertyType == typeof(double))
                                        {
                                            // Handle double conversion
                                            var stringValue = cell.Value.ToString();
                                            var convertedValue = Convert.ToDouble(stringValue, CultureInfo.InvariantCulture);
                                            propertyInfo.SetValue(obj, convertedValue);
                                        }
                                        else
                                        {
                                            // Use Convert.ChangeType for other types
                                            var convertedValue = Convert.ChangeType(cell.Value.ToString(), propertyInfo.PropertyType);
                                            propertyInfo.SetValue(obj, convertedValue);
                                        }
                                    }
                                }
                                catch (Exception ex)
                                {
                                    // Handle conversion errors
                                    Console.WriteLine($"Error converting value '{cell.Value}' to type '{propertyInfo.PropertyType}': {ex.Message}");
                                }
                            }
                        }

                        dataList.Add(obj);
                    }
                }
            }

            return dataList;
        }
        // hàm này đang lỗi
        public List<T> ImportExcel<T>(string excelPath, string sheetName)
        {
            List<T> list = new List<T>();
            Type typeOfObject = typeof(T);
            using (IXLWorkbook workbook = new XLWorkbook(excelPath))
            {
                var worksheet = workbook.Worksheets.Where(w => w.Name == sheetName).First();
                var properties = typeOfObject.GetProperties();
                //header colume text
                var colums = worksheet.FirstRow().Cells().Select((v, i) => new { value = v.Value, Index = i + 1 }); // index trong closedXml bắt đầu bằng 1 chứ ko phải bắt đầu =0
                foreach (IXLRow row in worksheet.RowsUsed().Skip(1)) // skip đầu dùng làm header rồi
                {
                    T obj = (T)Activator.CreateInstance(typeOfObject);

                    // dang bị lỗi chổ change type
                    foreach (var prop in properties)
                    {
                        int colIndex = colums.SingleOrDefault(c => c.value.ToString() == prop.Name.ToString()).Index;
                        var val = row.Cell(colIndex).Value;
                        var type = prop.PropertyType;
                        prop.SetValue(obj, Convert.ChangeType(val, type)); // dang lỗi chổ này
                    }



                    list.Add(obj);

                }

            }

            return list;
        }
       
       


    }
    public class Product
    {
        public string Name { get; set; }
        public decimal Price { get; set; }
        public int Units { get; set; }
        public decimal Price2 { get; set; }
        public string Name2 { get; set; }
        public string Name3 { get; set; }
        public string Name4 { get; set; }
    }
 
    public class Person
    {
        public string Name { get; set; }
        public string LastName { get; set; }
        public string Street { get; set; }
        public string State { get; set; }
        public string Zip { get; set; }
    }

}