CODEWITHSUNDEEP
c#excelchartsepplus
Funk247
Funk247

Reputation: 330

Adding multiple charts to EPPlus Worksheet, struggling to get layout correct

I'm currently creating Excel spreadsheets via EPPlus, the data is basically split into, section, questions, responses, with each being a subset of the other, I don't know how many questions each section might have, so everything needs to be done via loops.

What I want is for the document to contain a worksheet that shows a chart for each question giving a breakdown of the answers.

I'm extremely close to getting it working correctly but something in my code is causing issues and I can't work out what it is.

The data I'm currently working with holds 10 questions, all the charts are drawn but the very last one is positioned directly over the top of the first. I'm also having issues with getting the text results positioned correctly but since the code for that depends on that for the charts I'm hoping that sorting the charts will help me sort the results.

Here is my code:

 Int32 chartPaddingX = 50;
 Int32 chartPaddingY = 200;
 Int32 chartW = 320;
 Int32 chartH = 200;
 Int32 chartXCount = 0;
 Int32 chartYCount = 1;
 Int32 marginTop = 0;
 Int32 marginLeft = 0;
 Int32 resultsControlVar = 0;
 Int32 numCharts = 0;
 Int32 rnStart = 14;
 chartXCount = 0;
 chartYCount = 0;
 chartPaddingX = 50;

 rowNum = y = 14;
 colNum = 2;


 String title = section.First().title.ToString();
 ExcelWorksheet ws = pkg.Workbook.Worksheets.Add(title);

 int idx = 0;

 foreach (var question in results.questions.Where(q => q.section_guid == section.Key))
 {
     var chart = ws.Drawings.AddChart(question.question.ToString(), OfficeOpenXml.Drawing.Chart.eChartType.ColumnClustered);
     chart.SetSize(chartW, chartH);
     chart.Title.Text = question.question.ToString();
     chart.Legend.Add();

     marginLeft = (chartW + chartPaddingX) * chartXCount;

     // SET CHART PLACEMENT
     if (numCharts == 0)
     {
         marginTop = 50;
     }
     else if ((numCharts <= 3) && (numCharts != 0))
     {
         marginTop = 50;
     }
     else if (numCharts > 3)
     {
         marginTop = ((chartH + chartPaddingY) * chartYCount) + 50;
     }
     else
     {
         marginTop = (chartH + chartPaddingY) * chartYCount;
     }

     if (chartXCount >= 3)
     {
         chart.SetPosition(marginTop, 50);
         chartYCount++;
         chartXCount = 0;
         resultsControlVar = 1;
     }
         else
     {
         chart.SetPosition(marginTop, marginLeft + 50);
     }

     chartXCount++;
     numCharts++;

     ...... Add results data and chart series ......

      if (resultsControlVar == 1)
      {
          rowNum = rnStart;
          rowNum = rowNum + 20;
          rnStart = rowNum;
          colNum = 2;
          resultsControlVar = 0;
      }
      else
      {
          rowNum = rnStart;
          colNum = colNum + 6;
      }
  } // close foreach

Upvotes: 1

Views: 729

Answers (2)

Funk247
Funk247

Reputation: 330

Accepted Ernie's answer as he got there before me and had some useful tips for debugging. Many thanks for your time Ernie.

I actually managed to solve the issue this morning before checking back here, this is what I came up with:

foreach (var question in results.questions.Where(q => q.section_guid == section.Key))
{
// SET CHART PLACEMENT
if (chartXCount != chartXMax)
{
    if (numCharts == 0)
    {
       chartX = ((chartW + chartPaddingX) * chartXCount) + 50;
       chartY = ((chartH + chartPaddingY) * chartYCount) + 50;
    }
    else
    {
       chartX = ((chartW + chartPaddingX) * chartXCount) + 50;
    }
 chart.SetPosition(chartY, chartX);
 chartXCount++;
 }
 else
 {
     chartX = ((chartW + chartPaddingX) * chartXCount) + 50;
     chart.SetPosition(chartY, chartX);

     chartYCount++;
     resultsControlVar = 1;
 }

 numCharts++;

 ... DOING SOME OTHER BITS N BOBS ...

 // INCREMENT Y VALUE
 if (resultsControlVar == 1) // INCREMENT THE YPOS VALUE OF THE CHART
 {
     rowNum = rnStart;
     rowNum = rowNum + 20;
     rnStart = rowNum;
     colNum = 2;
     chartY = ((chartH + chartPaddingY) * chartYCount) + 50;
     resultsControlVar = 0;
     chartXCount = 0;
  }
  else                      // RESETS THE ROWNUM FOR DATA VALUES
  {
     rowNum = rnStart;
     colNum = colNum + 6;
  }
}

Upvotes: 0

Ernie S
Ernie S

Reputation: 14250

Check your algo logic, it is not giving the left/top you might be expecting. If you strip it down to the bare minimum:

[TestMethod]
public void Check_Multi_Test()
{
    Int32 chartPaddingX = 50;
    Int32 chartPaddingY = 200;
    Int32 chartW = 320;
    Int32 chartH = 200;
    Int32 chartXCount = 0;
    Int32 chartYCount = 1;
    Int32 marginTop = 0;
    Int32 marginLeft = 0;
    Int32 numCharts = 0;
    chartXCount = 0;
    chartYCount = 0;


    //foreach (var question in results.questions.Where(q => q.section_guid == section.Key))
    for (var i = 0; i < 10; i++)
    {
        marginLeft = (chartW + chartPaddingX)*chartXCount;

        // SET CHART PLACEMENT
        if (numCharts == 0)
        {
            marginTop = 50;
        }
        else if ((numCharts <= 3) && (numCharts != 0))
        {
            marginTop = 50;
        }
        else if (numCharts > 3)
        {
            marginTop = ((chartH + chartPaddingY)*chartYCount) + 50;
        }
        else
        {
            marginTop = (chartH + chartPaddingY)*chartYCount;
        }

        if (chartXCount >= 3)
        {
            //chart.SetPosition(marginTop, 50);
            Console.WriteLine("{{i: {0}, numCharts: {1}, left: {2}, top: {3}}}", i, numCharts, 50, marginTop); //Console.Writeline will simulate chart placement
            chartYCount++;
            chartXCount = 0;
        }
        else
        {
            //chart.SetPosition(marginTop, marginLeft + 50);
            Console.WriteLine("{{i: {0}, numCharts: {1}, left: {2}, top: {3}}}", i, numCharts, marginLeft + 50, marginTop); //Console.Writeline will simulate chart placement
        }

        chartXCount++;
        numCharts++;
    }
}

and output left/top to the console you can easily see the error starting with iteration 3:

{i: 0, numCharts: 0, left: 50,  top: 50}
{i: 1, numCharts: 1, left: 420, top: 50}
{i: 2, numCharts: 2, left: 790, top: 50}
{i: 3, numCharts: 3, left: 50,  top: 50}
{i: 4, numCharts: 4, left: 420, top: 450}
{i: 5, numCharts: 5, left: 790, top: 450}
{i: 6, numCharts: 6, left: 50,  top: 450}
{i: 7, numCharts: 7, left: 420, top: 850}
{i: 8, numCharts: 8, left: 790, top: 850}
{i: 9, numCharts: 9, left: 50,  top: 850}

It should not need to be so complex actually:

//foreach (var question in results.questions.Where(q => q.section_guid == section.Key))
for (var i = 0; i < 10; i++)
{
    marginLeft = (chartW + chartPaddingX)*chartXCount + 50;
    marginTop = (chartH + chartPaddingY)*chartYCount + 50;

    //chart.SetPosition(marginTop, marginLeft);  //Console.Writeline will simulate chart placement
    Console.WriteLine("{{i: {0}, numCharts: {1}, left: {2}, top: {3}}}", i, numCharts, marginLeft, marginTop);

    if (chartXCount >= 2)
    {
        chartYCount++;
        chartXCount = 0;
    }
    else
        chartXCount++;

    numCharts++;
}

Gives you this:

{i: 0, numCharts: 0, left: 50,  top: 50}
{i: 1, numCharts: 1, left: 420, top: 50}
{i: 2, numCharts: 2, left: 790, top: 50}
{i: 3, numCharts: 3, left: 50,  top: 450}
{i: 4, numCharts: 4, left: 420, top: 450}
{i: 5, numCharts: 5, left: 790, top: 450}
{i: 6, numCharts: 6, left: 50,  top: 850}
{i: 7, numCharts: 7, left: 420, top: 850}
{i: 8, numCharts: 8, left: 790, top: 850}
{i: 9, numCharts: 9, left: 50,  top: 1250}

Upvotes: 1

Related Questions