EPPlus – Leer tabla de Excel

Esta crónica ha sido analizado por especialistas así aseguramos la exactitud de nuestro contenido.

Solución:

No estoy seguro de por qué, pero ninguna de las soluciones anteriores funciona para mí. Entonces, compartiendo lo que funcionó:

public void readXLS(string FilePath)

    FileInfo existingFile = new FileInfo(FilePath);
    using (ExcelPackage package = new ExcelPackage(existingFile))
    
        //get the first worksheet in the workbook
        ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
        int colCount = worksheet.Dimension.End.Column;  //get Column Count
        int rowCount = worksheet.Dimension.End.Row;     //get row count
        for (int row = 1; row <= rowCount; row++)
        
            for (int col = 1; col <= colCount; col++)
            
                Console.WriteLine(" Row:" + row + " column:" + col + " Value:" + worksheet.Cells[row, col].Value?.ToString().Trim());
            
        
    

No hay nativos, pero ¿y si usas lo que puse en esta publicación?

Cómo analizar las filas de Excel de nuevo a tipos usando EPPlus

Si desea apuntarlo a una mesa, solo deberá modificarlo. Algo como esto debería hacerlo:

public static IEnumerable ConvertTableToObjects(this ExcelTable table) where T : new()

    //DateTime Conversion
    var convertDateTime = new Func(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 start = table.Address.Start;
    var end = table.Address.End;
    var cells = new List();

    //Have to use for loops insteadof worksheet.Cells to protect against empties
    for (var r = start.Row; r <= end.Row; r++)
        for (var c = start.Column; c <= end.Column; c++)
            cells.Add(table.WorkSheet.Cells[r, c]);

    var groups = cells
        .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))
                
                    if (!string.IsNullOrWhiteSpace(val?.ToString()))
                    
                        //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;

Aquí hay un método de prueba:

[TestMethod]
public void Table_To_Object_Test()

    //Create a test file
    var fi = new FileInfo(@"c:tempTable_To_Object.xlsx");

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

        package.Save();
    

Dio esto en la consola:

1JohnMale
2MariaFemale
3DanielUnknown

Solo tenga cuidado si su campo de identificación es un número o string en Excel ya que la clase espera un string.

Esta es mi versión de trabajo. Tenga en cuenta que el código de los resolutores no se muestra, pero es un giro en mi implementación que permite que las columnas se resuelvan aunque tengan un nombre ligeramente diferente en cada hoja de trabajo.

public static IEnumerable ToArray(this ExcelWorksheet worksheet, List 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();

  // 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);

Te mostramos reseñas y calificaciones

Si te animas, eres capaz de dejar una división acerca de qué te ha parecido este artículo.

---

---

Utiliza Nuestro Buscador