LoadFromCollection horizontally

Question

Using EPPlus I want to load data horizontally.

var randomData = new[] { "Foo", "Bar", "Baz" }.ToList();        
ws.Cells["B4"].LoadFromCollection(randomData);

Default behaviour is vertically, this code will result in:

This is what I need:

Downside of using EPPlus, their documentation is sketchy.

Answer 1

If I am condemned to looping myself, I can write the code:

public byte[] TestExcellGeneration_HorizontalLoadFromCollection()
{
    byte[] result = null;
    using (ExcelPackage pck = new ExcelPackage())
    {
        var foo = pck.Workbook.Worksheets.Add("Foo");
        var randomData = new[] { "Foo", "Bar", "Baz" }.ToList();
        //foo.Cells["B4"].LoadFromCollection(randomData);

        int startColumn = 2; // "B";
        int startRow = 4;
        for(int i = 0; i < randomData.Count; i++)
        {
            foo.Cells[startRow, startColumn + i].Value = randomData[i];
        }

        result = pck.GetAsByteArray();
    }            
    return result;
}

And when you call this from a TestMethod:

[TestMethod]
public void TestExcellGeneration_HorizontalLoadFromCollection()
{            
    var excelFileBytes = (new MyExcelGenerator()).TestExcellGeneration_HorizontalLoadFromCollection();
    OpenExcelFromTempFile(excelFileBytes);
}

private void OpenExcelFromTempFile(byte[] data)
{
    string tempPath = System.IO.Path.GetTempFileName();
    System.IO.File.WriteAllBytes(tempPath, data);
    Application excelApplication = new Application();
    _Workbook excelWorkbook;
    excelWorkbook = excelApplication.Workbooks.Open(tempPath);
    excelApplication.Visible = true; 
}

It results in:

Answer 2

What if you did something like this:

var randomData = new[] { "Foo", "Bar", "Baz" }.ToList();
//ws.Cells["B4"].LoadFromCollection(randomData);
ws.Cells["B4"].LoadFromArrays(new List<string[]>(new[] { randomData.ToArray() }));

Which gives me this in the output:

Bear in mind that if you are concerned about performance, say with very large collections, you are better off writing your own code anyway as the LoadFrom* methods do add overhead to account for multiple scenarios.

Answer 3

Here is an extension method:

public static void LoadFromCollectionHorizontally<T>(this ExcelWorksheet excelWorksheet, IEnumerable<T> objects, string cellAddress = "A1")
{
   List<object[]> valuesHorizontally = new List<object[]>();
            
   if (typeof(T).IsClass)
   {
       var properties = typeof(T)
              .GetProperties(BindingFlags.Instance | BindingFlags.Public)
              .Where(p => !Attribute.IsDefined(p, typeof(EpplusIgnore)));
            
       foreach (var prop in properties)
       {
            var values = new List<object>();
            foreach (T item in objects)
            {
               values.Add(prop.GetValue(item));
            }
            valuesHorizontally.Add(values.ToArray());
       }
   }
   else
   {
       valuesHorizontally.Add(objects.Cast<ValueType>().ToArray());
   }
            
    var startingCellRange = excelWorksheet.Cells[cellAddress];
    var filledUpCellRange = startingCellRange.LoadFromArrays(valuesHorizontally);

    ...
            
}

Answer 4

public static class EpPlusExtensions
{
    public static void LoadFromCollectionHorizontal<T>(this ExcelWorksheet worksheet, IEnumerable<T> data, int startRow, int startColumn)
    {
        for (var i = 0; i < data.Count(); i++)
        {
            worksheet.Cells[startRow, startColumn + i].Value = data.ElementAt(i);
        }
    }
}