C# EPPLUS Chart SetPosition Method Row Offset does not work

Question

I am creating a chart to export to excel. I need to create several, so I would offset them using the SetPosition() method with the 4 parameters:

SetPosition(int row, int rowoffset in pixel, int col, int coloffset in pixel)

thus

chart.SetPosition(startRow, 350*i, 0, 50);

The problem is that the second row offset parameter stretches the chart by 350*i pixels higher. This must be a bug since the col offset 4th parameter works fine and as expected.

I need to use startRow to start at a specific row cell in the sheet, so I need to get the row offset to work somehow.

Any ideas?

Answer 1

The RowOffset and ColumnOffset have given me trouble as well and I avoid using them in that function. If you look at the source could you can see it doing alot of match with the chart height/width so it seems to do more then just set and top/left position. I have not gone through the effort to fully reverse engineer it.

I just do the math myself. Knowing the default row height in pixels is the only thing that you have to watch out for since this can be customized by the user which you cannot know at design time Here is what I do:

using (var pck = new ExcelPackage(fi))
{
    var workbook = pck.Workbook;
    var worksheet = workbook.Worksheets.Add("Sheet1");
    worksheet.Cells.LoadFromDataTable(datatable, true);

    //Set specific values to make predictable
    const int EXCELCHARTWIDTH = 375; 
    const int EXCELCHARTHEIGHT = 350;

    //Have to assume a standard screen dpi but can be customized by the user
    const double EXCELDEFAULTROWHEIGHT = 20.0; 

    var startCell = (ExcelRangeBase)worksheet.Cells["A1"];
    for (var i = 0; i < 10; i++)
    {
        var chart = worksheet.Drawings.AddChart("chart " + i, eChartType.Pie);
        chart.SetSize(EXCELCHARTWIDTH, EXCELCHARTHEIGHT);
        chart.SetPosition(startCell.Start.Row, 0, startCell.Start.Column, 0);

        var chartcellheight = (int)Math.Ceiling(EXCELCHARTHEIGHT / EXCELDEFAULTROWHEIGHT);
        startCell = startCell.Offset(chartcellheight, 0);
    }

    pck.Save();
}