EPPlus - Excel-Tabelle Lesen

Es gibt keine Muttersprache, sondern was ist, wenn Sie, was ich in diesem post:

Zum analysieren von excel-Zeilen zurück, die auf Typen mit EPPlus

Wenn Sie möchten, zeigen Sie es an eine Tabelle nur wird es geändert werden müssen. So etwas sollte es tun:

public static IEnumerable<T> ConvertTableToObjects<T>(this ExcelTable table) where T : new()
{
    //DateTime Conversion
    var convertDateTime = new Func<double, DateTime>(excelDate =>
    {
        if (excelDate < 1)
            throw new ArgumentException("Excel dates cannot be smaller than 0.");

        var dateOfReference = new DateTime(1900, 1, 1);

        if (excelDate > 60d)
            excelDate = excelDate - 2;
        else
            excelDate = excelDate - 1;
        return dateOfReference.AddDays(excelDate);
    });

    //Get the properties of T
    var tprops = (new T())
        .GetType()
        .GetProperties()
        .ToList();

    //Get the cells based on the table address
    var groups = table.WorkSheet.Cells[table.Address.Start.Row, table.Address.Start.Column, table.Address.End.Row, table.Address.End.Column]
        .GroupBy(cell => cell.Start.Row)
        .ToList();

    //Assume the second row represents column data types (big assumption!)
    var types = groups
        .Skip(1)
        .First()
        .Select(rcell => rcell.Value.GetType())
        .ToList();

    //Assume first row has the column names
    var colnames = groups
        .First()
        .Select((hcell, idx) => new { Name = hcell.Value.ToString(), index = idx })
        .Where(o => tprops.Select(p => p.Name).Contains(o.Name))
        .ToList();

    //Everything after the header is data
    var rowvalues = groups
        .Skip(1) //Exclude header
        .Select(cg => cg.Select(c => c.Value).ToList());


    //Create the collection container
    var collection = rowvalues
        .Select(row =>
        {
            var tnew = new T();
            colnames.ForEach(colname =>
            {
                //This is the real wrinkle to using reflection - Excel stores all numbers as double including int
                var val = row[colname.index];
                var type = types[colname.index];
                var prop = tprops.First(p => p.Name == colname.Name);

                //If it is numeric it is a double since that is how excel stores all numbers
                if (type == typeof(double))
                {
                    //Unbox it
                    var unboxedVal = (double)val;

                    //FAR FROM A COMPLETE LIST!!!
                    if (prop.PropertyType == typeof(Int32))
                        prop.SetValue(tnew, (int)unboxedVal);
                    else if (prop.PropertyType == typeof(double))
                        prop.SetValue(tnew, unboxedVal);
                    else if (prop.PropertyType == typeof(DateTime))
                        prop.SetValue(tnew, convertDateTime(unboxedVal));
                    else
                        throw new NotImplementedException(String.Format("Type '{0}' not implemented yet!", prop.PropertyType.Name));
                }
                else
                {
                    //Its a string
                    prop.SetValue(tnew, val);
                }
            });

            return tnew;
        });


    //Send it back
    return collection;
}

Hier ist eine test-Methode:

[TestMethod]
public void Table_To_Object_Test()
{
    //Create a test file
    var fi = new FileInfo(@"c:\temp\Table_To_Object.xlsx");

    using (var package = new ExcelPackage(fi))
    {
        var workbook = package.Workbook;
        var worksheet = workbook.Worksheets.First();
        var ThatList = worksheet.Tables.First().ConvertTableToObjects<ExcelData>();
        foreach (var data in ThatList)
        {
            Console.WriteLine(data.Id + data.Name + data.Gender);
        }

        package.Save();
    }
}

Gab dies in der Konsole:

1JohnMale
2MariaFemale
3DanielUnknown

Nur vorsichtig sein, wenn Sie Id-Feld ist eine Zahl oder eine Zeichenkette in excel, da sich die Klasse erwartet einen string.

InformationsquelleAutor Ernie

Dies ist meine funktionierende version. Beachten Sie, dass die Resolver-code wird nicht angezeigt, sondern sind ein spin auf meine Implementierung ermöglicht die Spalten aufgelöst werden, auch wenn Sie benannt sind etwas anders in jedem Arbeitsblatt.

public static IEnumerable<T> ToArray<T>(this ExcelWorksheet worksheet, List<PropertyNameResolver> resolvers) where T : new()
{

  //List of all the column names
  var header = worksheet.Cells.GroupBy(cell => cell.Start.Row).First();

  //Get the properties from the type your are populating
  var properties = typeof(T).GetProperties().ToList();


  var start = worksheet.Dimension.Start;
  var end = worksheet.Dimension.End;

  //Resulting list
  var list = new List<T>();

  //Iterate the rows starting at row 2 (ie start.Row + 1)
  for (int row = start.Row + 1; row <= end.Row; row++)
  {
    var instance = new T();
    for (int col = start.Column; col <= end.Column; col++)
    {
      object value = worksheet.Cells[row, col].Text;

      //Get the column name zero based (ie col -1)
      var column = (string)header.Skip(col - 1).First().Value;

      //Gets the corresponding property to set
      var property = properties.Property(resolvers, column);

      try
      {
        var propertyName = property.PropertyType.IsGenericType
          ? property.PropertyType.GetGenericArguments().First().FullName
          : property.PropertyType.FullName;


        //Implement setter code as needed. 
        switch (propertyName)
        {
          case "System.String":
            property.SetValue(instance, Convert.ToString(value));
            break;
          case "System.Int32":
            property.SetValue(instance, Convert.ToInt32(value));
            break;
          case "System.DateTime":
            if (DateTime.TryParse((string) value, out var date))
            {
              property.SetValue(instance, date);
            }
            property.SetValue(instance, FromExcelSerialDate(Convert.ToInt32(value)));
            break;
          case "System.Boolean":
            property.SetValue(instance, (int)value == 1);
            break;
        }
      }
      catch (Exception e)
      {
        //instance property is empty because there was a problem.
      }

    } 
    list.Add(instance);
  }
  return list;
}

//Utility function taken from the above post's inline function.
public static DateTime FromExcelSerialDate(int excelDate)
{
  if (excelDate < 1)
    throw new ArgumentException("Excel dates cannot be smaller than 0.");

  var dateOfReference = new DateTime(1900, 1, 1);

  if (excelDate > 60d)
    excelDate = excelDate - 2;
  else
    excelDate = excelDate - 1;
  return dateOfReference.AddDays(excelDate);
}

InformationsquelleAutor jwize

Ich habe einen Fehler auf der ersten Antwort, so dass ich mich geändert habe einige code-Zeile.

Bitte versuchen Sie meinen neuen code, es funktioniert für mich.

using OfficeOpenXml;
using OfficeOpenXml.Table;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;

public static class ImportExcelReader
{
    public static List<T> ImportExcelToList<T>(this ExcelWorksheet worksheet) where T : new()
    {
        //DateTime Conversion
        Func<double, DateTime> convertDateTime = new Func<double, DateTime>(excelDate =>
        {
            if (excelDate < 1)
            {
                throw new ArgumentException("Excel dates cannot be smaller than 0.");
            }

            DateTime dateOfReference = new DateTime(1900, 1, 1);

            if (excelDate > 60d)
            {
                excelDate = excelDate - 2;
            }
            else
            {
                excelDate = excelDate - 1;
            }

            return dateOfReference.AddDays(excelDate);
        });

        ExcelTable table = null;

        if (worksheet.Tables.Any())
        {
            table = worksheet.Tables.FirstOrDefault();
        }
        else
        {
            table = worksheet.Tables.Add(worksheet.Dimension, "tbl" + ShortGuid.NewGuid().ToString());

            ExcelAddressBase newaddy = new ExcelAddressBase(table.Address.Start.Row, table.Address.Start.Column, table.Address.End.Row + 1, table.Address.End.Column);

            //Edit the raw XML by searching for all references to the old address
            table.TableXml.InnerXml = table.TableXml.InnerXml.Replace(table.Address.ToString(), newaddy.ToString());
        }

        //Get the cells based on the table address
        List<IGrouping<int, ExcelRangeBase>> groups = table.WorkSheet.Cells[table.Address.Start.Row, table.Address.Start.Column, table.Address.End.Row, table.Address.End.Column]
            .GroupBy(cell => cell.Start.Row)
            .ToList();

        //Assume the second row represents column data types (big assumption!)
        List<Type> types = groups.Skip(1).FirstOrDefault().Select(rcell => rcell.Value.GetType()).ToList();

        //Get the properties of T
        List<PropertyInfo> modelProperties = new T().GetType().GetProperties().ToList();

        //Assume first row has the column names
        var colnames = groups.FirstOrDefault()
            .Select((hcell, idx) => new
            {
                Name = hcell.Value.ToString(),
                index = idx
            })
            .Where(o => modelProperties.Select(p => p.Name).Contains(o.Name))
            .ToList();

        //Everything after the header is data
        List<List<object>> rowvalues = groups
            .Skip(1) //Exclude header
            .Select(cg => cg.Select(c => c.Value).ToList()).ToList();

        //Create the collection container
        List<T> collection = new List<T>();
        foreach (List<object> row in rowvalues)
        {
            T tnew = new T();
            foreach (var colname in colnames)
            {
                //This is the real wrinkle to using reflection - Excel stores all numbers as double including int
                object val = row[colname.index];
                Type type = types[colname.index];
                PropertyInfo prop = modelProperties.FirstOrDefault(p => p.Name == colname.Name);

                //If it is numeric it is a double since that is how excel stores all numbers
                if (type == typeof(double))
                {
                    //Unbox it
                    double unboxedVal = (double)val;

                    //FAR FROM A COMPLETE LIST!!!
                    if (prop.PropertyType == typeof(int))
                    {
                        prop.SetValue(tnew, (int)unboxedVal);
                    }
                    else if (prop.PropertyType == typeof(double))
                    {
                        prop.SetValue(tnew, unboxedVal);
                    }
                    else if (prop.PropertyType == typeof(DateTime))
                    {
                        prop.SetValue(tnew, convertDateTime(unboxedVal));
                    }
                    else if (prop.PropertyType == typeof(string))
                    {
                        prop.SetValue(tnew, val.ToString());
                    }
                    else
                    {
                        throw new NotImplementedException(string.Format("Type '{0}' not implemented yet!", prop.PropertyType.Name));
                    }
                }
                else
                {
                    //Its a string
                    prop.SetValue(tnew, val);
                }
            }
            collection.Add(tnew);
        }

        return collection;
    }
}

Wie man diese Funktion aufrufen? bitte sehen Sie sich folgenden code;

private List<FundraiserStudentListModel> GetStudentsFromExcel(HttpPostedFileBase file)
    {
        List<FundraiserStudentListModel> list = new List<FundraiserStudentListModel>();
        if (file != null)
        {
            try
            {
                using (ExcelPackage package = new ExcelPackage(file.InputStream))
                {
                    ExcelWorkbook workbook = package.Workbook;
                    if (workbook != null)
                    {
                        ExcelWorksheet worksheet = workbook.Worksheets.FirstOrDefault();
                        if (worksheet != null)
                        {
                            list = worksheet.ImportExcelToList<FundraiserStudentListModel>();
                        }
                    }
                }
            }
            catch (Exception err)
            {
                //save error log
            }
        }
        return list;
    }

FundraiserStudentListModel hier:

 public class FundraiserStudentListModel
{
    public string Name { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
}

InformationsquelleAutor Sadik Ali