CODEWITHSUNDEEP
excelopenxml
Yaron
Yaron

Reputation: 2749

Excel open xml sdk - Controling chart position

I'm trying to figure out how to get the position of an existing chart on the sheet and understand what range (columns and rows) it "hides"...
I have the chart part and chart element.

Upvotes: 0

Views: 1232

Answers (1)

petelids
petelids

Reputation: 12815

A chart has two parts - the chart itself and a Drawing element that contains the location information for the chart. Therefore to find out where a chart is you need to find the DrawingsPart. The actual location is stored in a TwoCellAnchor which contains the zero-based column and row indices of the top-left and bottom-right cells of the drawing object. As a drawing object doesn't have to be right on the boundary of a cell there is also an offset to show where in the cell the drawing object starts. This value is in EMU's unless the number is immediately followed by a unit identifier.

The following code will iterate all ChartParts in the DrawingsPart within all WorksheetParts and output the TwoCellAnchor information.

public static void FindCharts(string filename)
{
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false))
    {
        foreach (WorksheetPart wsp in spreadsheetDocument.WorkbookPart.WorksheetParts)
        {
            if (wsp.DrawingsPart != null)
            {
                foreach (ChartPart chart in wsp.DrawingsPart.ChartParts)
                {
                    TwoCellAnchor tca = wsp.DrawingsPart.WorksheetDrawing.Descendants<TwoCellAnchor>().FirstOrDefault();
                    if (tca != null)
                    {
                        Console.WriteLine("A Chart starts at Column {0} ({1}), Row {2} ({3}) and ends at Column {4} ({5}), Row {6} ({7})",
                                            tca.FromMarker.ColumnId.Text,
                                            tca.FromMarker.ColumnOffset.Text,
                                            tca.FromMarker.RowId.Text,
                                            tca.FromMarker.RowOffset.Text,
                                            tca.ToMarker.ColumnId.Text,
                                            tca.ToMarker.ColumnOffset.Text,
                                            tca.ToMarker.RowId.Text,
                                            tca.ToMarker.RowOffset.Text
                                            );
                    }
                    else
                    {
                        Console.WriteLine("Couldn't find position of chart {0}", chart.ChartSpace.LocalName.ToString());
                    }
                }
            }
        }
    }
}

Upvotes: 4

Related Questions