#StackBounty: #c# #excel EP6 – Error Table range collides with table

Bounty: 50

I am building an export to excel functionality using EP6 and c# application. I am currently getting the error.
‘Table range collides with table tblAllocations29’

In my code logic below, I am looping through a data structure that contains key and collection as a value.

I looping across each key and once again loop through each collection belonging to that key.

I basically need to print tabular information for each collection along with its totals.

In the current scenario, I am getting the error when it is trying to print
three arrays
The first array has 17 records
The second array has 29 records
The third array has 6 records

I have taken a note of the ranges it is creating while debugging

The ranges are

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
        }

I have written the following utility that will try and export. It works sometimes when there are two array collections and it failed when processing three. Could someody tell me what the problems is

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

}

}

This is how the data looks when it is displayed sucessfully

enter image description here


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.