excel LoadFromCollection水平加载

hujrc8aj  于 2023-04-07  发布在  其他
关注(0)|答案(4)|浏览(143)

使用EPPlus我想水平加载数据。

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

默认行为为垂直,此代码将导致:

这就是我需要的:

使用EPPlus的缺点是,他们的documentation是粗略的。

ttcibm8c

ttcibm8c1#

如果你做了这样的事情:

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

这在输出中给了我这个:

请记住,如果您关心性能,比如使用非常大的集合,您最好编写自己的代码,因为LoadFrom*方法确实会增加开销以应对多种情况。

myzjeezk

myzjeezk2#

如果我注定要循环自己,我可以写代码:

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;
}

当你从一个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; 
}

其结果是:

ijnw1ujt

ijnw1ujt3#

下面是一个扩展方法:

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

    ...
            
}
3npbholx

3npbholx4#

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

相关问题