CODEWITHSUNDEEP
c#excellistepplus
Ion
Ion

Reputation: 559

Use 'LoadfromCollection' with a list containing another list inside

My problem is that I have a list that contains a few strings and inside this list another list of decimals, something like this:

         public class excelInventario
            {
              public excelInventario() { cols = new List<decimal>); }
              public string codigo { get; set; }       
              public string nombre { get; set;}        .
              public List<decimal> cols { get; set; }  //Lista de columnas
              public decimal suma { get; set; }
              public decimal stock { get; set; }
              public decimal diferencia { get; set; }
              public decimal precio { get; set; }
            }

and now I need to put this in Excel. The problem is that when I use the method LoadFromCollection(MyList) the strings appear well in Excel, but the list of decimals is not put correctly, but:

System.Collections.Generic.List`1[System.Decimal].

Can I adapt this method or do I need to use a loop and put "manually" the row values one by one?

I suspect this second option it will be inefficient.

---------------EDIT TO ADD MORE CODE--------------

int tamcolumnas=excelin[0].cols.Count;
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet hoja = package.Workbook.Worksheets.Add("Comparativo unidades contadas VS stock");
hoja.Cells["A1"].Value = "CODART";
hoja.Cells["B1"].Value = "NOMBRE";
for(int i=0;i<tamcolumnas;i++)
{ hoja.Cells[1, i+3].Value = "COL"+(i+1); }
var MyList = new List<excelInventario>();
hoja.Cells.LoadFromCollection(MyList,true);
hoja.Cells[2, 3].LoadFromArrays(MyList.Select((r) => r.cols.Cast<object>).ToArray()));

in this last line is where fails.

Say:

System.ArgumentOutOfRangeException

The specified argument is outside the range of valid values.

Upvotes: 3

Views: 2759

Answers (1)

Ernie S
Ernie S

Reputation: 14270

Since those are Lists the closest you can get to automation is the LoadFromArray since those are not true objects. Its not exactly pretty since it requires casting so check for performance hits. Otherwise, it may be best to use plain old loops. Here is what I mean:

[TestMethod]
public void ListOfList_Test()
{
    //http://stackoverflow.com/questions/33825995/how-to-use-loadfromcollection-in-epplus-with-a-list-containing-another-list-insi
    //Throw in some data
    var MyList = new List<TestExtensions.excelInventario>();

    for (var i = 0; i < 10; i++)
    {
        var row = new TestExtensions.excelInventario
        {
            codigo = Path.GetRandomFileName(),
            nombre = i.ToString(),
            cols = new List<decimal> {i, (decimal) (i*1.5), (decimal) (i*2.5)}
        };
        MyList.Add(row);
    }

    //Create a test file
    var fi = new FileInfo(@"c:\temp\ListOfList.xlsx");
    if (fi.Exists)
        fi.Delete();

    int tamcolumnas = 10; // excelin[0].cols.Count;
    using (ExcelPackage package = new ExcelPackage(fi))
    {
        ExcelWorksheet hoja = package.Workbook.Worksheets.Add("Comparativo unidades contadas VS stock");
        hoja.Cells["A1"].Value = "CODART";
        hoja.Cells["B1"].Value = "NOMBRE";
        for (int i = 0; i < tamcolumnas; i++)
        {
            hoja.Cells[1, i + 3].Value = "COL" + (i + 1);
        }
        //var MyList = new List<TestExtensions.excelInventario>();
        hoja.Cells.LoadFromCollection(MyList, true); 
      //hoja.Cells[2, 3].LoadFromArrays(MyList.Select((r) => r.cols.Cast<object>).ToArray()));
        hoja.Cells[2, 3].LoadFromArrays(MyList.Select((r) => r.cols.Cast<object>().ToArray()));

        package.Save();
    }
}

Upvotes: 3

Related Questions