c# - EP Plus - 错误表范围与表冲突

Question

我正在使用 EP plus 和 c# 应用程序构建导出到 excel 的功能。我目前收到错误。

'Table range collides with table tblAllocations29'

在我下面的代码逻辑中，我循环访问一个包含键和集合作为值的数据结构。

我遍历每个键并再次遍历属于该键的每个集合。

我基本上需要打印每个集合的表格信息及其总数。

在当前情况下，我在尝试打印时遇到错误三个数组第一个数组有 17 条记录第二个数组有 29 条记录第三个数组有6条记录

我已经记下它在调试时创建的范围

范围是

A1  G18
A20 G50
A51 G58

Controller

[HttpGet]
[SkipTokenAuthorization]
public HttpResponseMessage DownloadFundAllocationDetails(int id, DateTime date)
{
    var ms = GetStrategy(id);

    DateTime d = new DateTime(date.Year, date.Month, 1).AddMonths(1).AddDays(-1);
    if (ms.FIRM_ID != null)
    {
        var firm = GetService<FIRM>().Get(ms.FIRM_ID.Value);
        IEnumerable<FIRMWIDE_MANAGER_ALLOCATION> allocationsGroup = null;
        var allocationsGrouped = GetAllocationsGrouped(EntityType.Firm, firm.ID, d);


         string fileName = string.Format("{0} as of {1}.xlsx", "test", date.ToString("MMM, yyyy"));
         byte[] fileContents;
         var newFile = new FileInfo(fileName);
         using (var package = new OfficeOpenXml.ExcelPackage(newFile))
         {
            FundAllocationsPrinter.Print(package, allocationsGrouped);
            fileContents = package.GetAsByteArray();
         }

         var result = new HttpResponseMessage(HttpStatusCode.OK)
         {
             Content = new ByteArrayContent(fileContents)
         };

         result.Content.Headers.ContentDisposition =
            new ContentDispositionHeaderValue("attachment")
            {
                 FileName = fileName
            };

         result.Content.Headers.ContentType =
            new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

         return result;
    }

    return null;

    #endregion
}

我已经编写了以下将尝试导出的实用程序。当有两个数组集合时它有时会起作用，而在处理三个数组集合时它会失败。谁能告诉我问题是什么

FundsAllocationsPrinter.cs

public class FundAllocationsPrinter
{
    public static void Print(ExcelPackage package, ILookup<string, FIRMWIDE_MANAGER_ALLOCATION> allocation)
    {
        ExcelWorksheet wsSheet1 = package.Workbook.Worksheets.Add("Sheet1");
        wsSheet1.Protection.IsProtected = false;
        int count = 0;
        int previouscount = 0;
        var position = 2;
        int startposition = 1;
        IEnumerable<FIRMWIDE_MANAGER_ALLOCATION> allocationGroup = null;

        foreach (var ag in allocation)
        {
            allocationGroup = ag.Select(a => a);
            var allocationList = allocationGroup.ToList();
            count = allocationList.Count();

            using (ExcelRange Rng = wsSheet1.Cells["A" + startposition + ":G" + (count + previouscount + 1)])
            {
                ExcelTableCollection tblcollection = wsSheet1.Tables;
                ExcelTable table = tblcollection.Add(Rng, "tblAllocations" + count);

                //Set Columns position & name  
                table.Columns[0].Name = "Manager Strategy";
                table.Columns[1].Name = "Fund";
                table.Columns[2].Name = "Portfolio";
                table.Columns[3].Name = "As Of";
                table.Columns[4].Name = "EMV (USD)";
                table.Columns[5].Name = "Percent";
                table.Columns[6].Name = "Allocations";

                wsSheet1.Column(1).Width = 45;
                wsSheet1.Column(2).Width = 45;
                wsSheet1.Column(3).Width = 55;
                wsSheet1.Column(4).Width = 15;
                wsSheet1.Column(5).Width = 25;
                wsSheet1.Column(6).Width = 20;
                wsSheet1.Column(7).Width = 20;

                // table.ShowHeader = true;
                table.ShowFilter = true;
                table.ShowTotal = true;
                //Add TotalsRowFormula into Excel table Columns  
                table.Columns[0].TotalsRowLabel = "Total Rows";
                table.Columns[4].TotalsRowFormula = "SUBTOTAL(109,[EMV (USD)])";
                table.Columns[5].TotalsRowFormula = "SUBTOTAL(109,[Percent])";
                table.Columns[6].TotalsRowFormula = "SUBTOTAL(109,Allocations])";

                table.TableStyle = TableStyles.Dark10;
            }

            foreach (var ac in allocationGroup)
            {
                wsSheet1.Cells["A" + position].Value = ac.MANAGER_STRATEGY_NAME;
                wsSheet1.Cells["B" + position].Value = ac.MANAGER_FUND_NAME;
                wsSheet1.Cells["C" + position].Value = ac.PRODUCT_NAME;
                wsSheet1.Cells["D" + position].Value = ac.EVAL_DATE.ToString("dd MMM, yyyy");
                wsSheet1.Cells["E" + position].Value = ac.UsdEmv;
                wsSheet1.Cells["F" + position].Value = Math.Round(ac.GroupPercent,2);
                wsSheet1.Cells["G" + position].Value = Math.Round(ac.WEIGHT_WITH_EQ,2);
                position++;
            }
            position++;
            previouscount = position;
            // position = position + 1;
            startposition = position;
            position++;
        }
    }
}

数据显示成功后的样子

Answer 1

您的问题完全出在您的 Print 方法中。您已经被创建一个稍微过于复杂的行跟踪机制并将其与魔数(Magic Number)组合所困扰。这会导致您将每个表放置在第一行之后高于应有的位置。标题和小计不是表格的一部分，因此您有几行错误余地。如您所见，表格不能重叠，因此在您用尽余地后，EPPlus 开始对您咆哮。

您需要做的就是跟踪您正在写入的当前行，并考虑表头和表脚(小计)占用的空间(如果您使用它们)。

你声明这些:

int count = 0;
int previouscount = 0;
var position = 2;
int startposition = 1;

但是要写入正确的行，您只需要:

var rowNumber = 1;

这将正确地开始将您的数据写入 Excel 工作表的第一行。当您编写表格行时，您将仅跟踪和递增 rowNumber。但是每个表格的页眉和页脚呢？如果您在表格的第一行开始书写，您将覆盖页眉，如果您不考虑页眉和页脚，您将开始像您看到的那样发生冲突。所以让我们这样做:

var showFilter = true;
var showHeader = true;
var showTotals = true;
var rowAdderForHeader = Convert.ToInt32(showHeader);
var rowAdderForFooter = Convert.ToInt32(showTotals);

这些都是不言自明的，您将在需要时使用 rowAdders 跳转页眉或页脚。 rowNumber 将始终是您创建表格和写入数据的当前行。您在定义表时使用 count，但我们已将其与其他任何内容无关，因此我们将其移动:

var allocationList = allocationGroup.ToList();

//Moved here
var count = allocationList.Count();

您的 using 语句变为:

using (ExcelRange Rng = wsSheet1.Cells["A" + rowNumber + ":G" + (count + rowNumber)])

接下来，您的帖子中未提及，但您将遇到以下问题:

ExcelTableCollection tblcollection = wsSheet1.Tables;
ExcelTable table = tblcollection.Add(Rng, "tblAllocations" + count);

您的表名必须是唯一的，但您很可能会得到具有相同计数的多个分配，这将导致 EPPlus 向您抛出重复表名的异常。因此，您还需要跟踪当前表的索引:

var rowNumber = 1;
var tableIndex = 0;

//...
foreach (var ag in allocation)
{
    tableIndex += 1;
    //...
}

并用它来确保唯一的表名:

ExcelTableCollection tblcollection = wsSheet1.Tables;
ExcelTable table = tblcollection.Add(Rng, "tblAllocations" + tableIndex);

我们使用格式控制变量:

// table.ShowHeader = true;
table.ShowFilter = true;
table.ShowTotal = true;

//Changes to
table.ShowHeader = showHeader;
table.ShowFilter = showFilter;
table.ShowTotal = showTotals;

这里有一个小错别字:

table.Columns[6].TotalsRowFormula = "SUBTOTAL(109,Allocations])";

//Should be:
table.Columns[6].TotalsRowFormula = "SUBTOTAL(109,[Allocations])";

完成表定义后，您可以开始使用 foreach 循环写入数据。为了防止覆盖表头(如果存在)，我们必须前进一行。我们还必须为每个 FIRMWIDE_MANAGER_ALLOCATION 前进一行。如果您正在使用小计，我们必须在循环完成后前进一行，以便正确定位下一个表格:

rowNumber += rowAdderForHeader; 
foreach (var ac in allocationGroup)
{
    //...
    rowNumber += 1;
}
rowNumber += rowAdderForFooter;

就是这样。我们现在仅使用一个变量即可正确跟踪我们的位置，如果您的表格上有页眉或页脚，我们会根据需要修改位置。

下面是一个完整的工作示例，只要通过Nuget添加EPPlus包就可以在LinqPad中运行。它创建随机数量的分配组，每个分配组具有随机数量的分配，然后导出它们。将输出文件路径更改为适合您的路径:

void Main()
{
    var dataGenerator = new DataGenerator();
    var allocations = dataGenerator.Generate();
    var xlFile = new FileInfo(@"d:\so-test.xlsx");

    if (xlFile.Exists)
    {
        xlFile.Delete();
    }

    using(var xl = new ExcelPackage(xlFile))
    {
        FundAllocationsPrinter.Print(xl, allocations);
        xl.Save();
    }
}

// Define other methods and classes here

public static class FundAllocationsPrinter
{
    public static void Print(ExcelPackage package, ILookup<string, FIRMWIDE_MANAGER_ALLOCATION> allocation)
    {
        ExcelWorksheet wsSheet1 = package.Workbook.Worksheets.Add("Sheet1");
        wsSheet1.Protection.IsProtected = false;

        IEnumerable<FIRMWIDE_MANAGER_ALLOCATION> allocationGroup = null;

        var rowNumber = 1;
        int tableIndex = 0;

        var showFilter = true;
        var showHeader = true;
        var showTotals = true;
        var rowAdderForHeader = Convert.ToInt32(showHeader);
        var rowAdderForFooter = Convert.ToInt32(showTotals);

        foreach (var ag in allocation)
        {
            tableIndex += 1;
            Console.WriteLine(tableIndex);

            allocationGroup = ag.Select(a => a);
            var allocationList = allocationGroup.ToList();
            var count = allocationList.Count();

            using (ExcelRange Rng = wsSheet1.Cells["A" + rowNumber + ":G" + (count + rowNumber)])
            {
                ExcelTableCollection tblcollection = wsSheet1.Tables;
                ExcelTable table = tblcollection.Add(Rng, "tblAllocations" + tableIndex);

                //Set Columns position & name  
                table.Columns[0].Name = "Manager Strategy";
                table.Columns[1].Name = "Fund";
                table.Columns[2].Name = "Portfolio";
                table.Columns[3].Name = "As Of";
                table.Columns[4].Name = "EMV (USD)";
                table.Columns[5].Name = "Percent";
                table.Columns[6].Name = "Allocations";

                wsSheet1.Column(1).Width = 45;
                wsSheet1.Column(2).Width = 45;
                wsSheet1.Column(3).Width = 55;
                wsSheet1.Column(4).Width = 15;
                wsSheet1.Column(5).Width = 25;
                wsSheet1.Column(6).Width = 20;
                wsSheet1.Column(7).Width = 20;

                table.ShowHeader = showHeader;
                table.ShowFilter = showFilter;
                table.ShowTotal = showTotals;
                //Add TotalsRowFormula into Excel table Columns  
                table.Columns[0].TotalsRowLabel = "Total Rows";
                table.Columns[4].TotalsRowFormula = "SUBTOTAL(109,[EMV (USD)])";
                table.Columns[5].TotalsRowFormula = "SUBTOTAL(109,[Percent])";
                table.Columns[6].TotalsRowFormula = "SUBTOTAL(109, [Allocations])";

                table.TableStyle = TableStyles.Dark10;
            }

            //Account for the table header
            rowNumber += rowAdderForHeader; 

            foreach (var ac in allocationGroup)
            {
                wsSheet1.Cells["A" + rowNumber].Value = ac.MANAGER_STRATEGY_NAME;
                wsSheet1.Cells["B" + rowNumber].Value = ac.MANAGER_FUND_NAME;
                wsSheet1.Cells["C" + rowNumber].Value = ac.PRODUCT_NAME;
                wsSheet1.Cells["D" + rowNumber].Value = ac.EVAL_DATE.ToString("dd MMM, yyyy");
                wsSheet1.Cells["E" + rowNumber].Value = ac.UsdEmv;
                wsSheet1.Cells["F" + rowNumber].Value = Math.Round(ac.GroupPercent, 2);
                wsSheet1.Cells["G" + rowNumber].Value = Math.Round(ac.WEIGHT_WITH_EQ, 2);
                rowNumber++;
            }
            //Account for the table footer
            rowNumber += rowAdderForFooter;
        }
    }
}

public class FIRMWIDE_MANAGER_ALLOCATION
{
    public FIRMWIDE_MANAGER_ALLOCATION(string name, Random rnd)
    {
        Name = name;
        MANAGER_STRATEGY_NAME = "strategy name";
        MANAGER_FUND_NAME = "fund name";
        PRODUCT_NAME = "product name";
        EVAL_DATE = DateTime.Now;
        UsdEmv = (decimal)rnd.NextDouble() * 100000000;
        GroupPercent = (decimal)rnd.NextDouble() * 100;
        WEIGHT_WITH_EQ = 0;
    }

    public string Name { get; set; }
    public string MANAGER_STRATEGY_NAME { get; set; }
    public string MANAGER_FUND_NAME { get; set; }
    public string PRODUCT_NAME { get; set; }
    public DateTime EVAL_DATE { get; set; }
    public decimal UsdEmv { get; set; }
    public decimal GroupPercent { get; set; }
    public decimal WEIGHT_WITH_EQ { get; set; }
}

public class DataGenerator
{
    public static Random rnd = new Random();

    public ILookup<string, FIRMWIDE_MANAGER_ALLOCATION> Generate()
    {
        var data = new List<FIRMWIDE_MANAGER_ALLOCATION>();
        var itemCount = rnd.Next(1, 100);

        for (var itemIndex = 0; itemIndex < itemCount; itemIndex++)
        {
            var name = Path.GetRandomFileName();
            data.AddRange(GenerateItems(name));
        }
        return data.ToLookup(d => d.Name, d => d); 
    }

    private IEnumerable<FIRMWIDE_MANAGER_ALLOCATION> GenerateItems(string name)
    {
        var itemCount = rnd.Next(1,100);
        var items = new List<FIRMWIDE_MANAGER_ALLOCATION>();

        for (var itemIndex = 0; itemIndex < itemCount; itemIndex++)
        {
            items.Add(new FIRMWIDE_MANAGER_ALLOCATION(name, rnd));
        }
        return items;
    }
}