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

问题描述

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

'表范围与表 tblAllocations29 冲突'

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

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

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

在当前场景中，尝试打印三个数组时出现错误第一个数组有 17 条记录第二个数组有 29 条记录第三个数组有 6 条记录

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

范围是

A1  G18
A20 G50
A51 G58

控制器

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

这是数据显示成功时的样子

标签： c#excelepplus

解决方案

你的问题完全在于你的Print方法。您已经被创建了一个稍微过于复杂的行跟踪机制并将其与幻数结合起来。这会导致您将每个表格放置在比应有的位置高的第一行之后。表头和小计不是表格的一部分，因此您有几行错误的余地。如您所见，表格不能重叠，因此 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;
    }
}

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

问题描述

解决方案

推荐阅读