Chart to worksheet programmatically -- coordinates?

  • Thread starter Thread starter Petr Prikryl
  • Start date Start date
P

Petr Prikryl

Hi,

I am exporting the Excel document and my goal is to
place several chart objects into the worksheet.
I know the range (rectangle) of cells where the chart
should be placed. How can I convert the cell coordinates
into points that are used in the ChartObjects' Add() method?

Details:

The MSDN Library contains the example with title
"Code: Add a Chart to a Worksheet Programmatically (C#)"

Excel.Worksheet thisWorksheet;
thisWorksheet = thisWorkbook.ActiveSheet as Excel.Worksheet;
Excel.ChartObjects charts =
(Excel.ChartObjects)thisWorksheet.ChartObjects(Type.Missing);

// Adds a chart at x = 100, y = 300, 500 points wide and 300 tall.
Excel.ChartObject chartObj = charts.Add(100, 300, 500, 300);
Excel.Chart chart = chartObj.Chart;

Instead of fixed values 100, 300, 500, 300 -- how can I get the coordinates
where visually the cells from range (say) "H4:J20" sit?

Thanks for your time and experience,
Petr
 
A Range object has Top, Left, Width and Height properties that correspond
directly to the same properties in the ChartObject. So (assuming you have
set the variable ChartRange to equal the range you are interested in filling):
Excel.ChartObject chartObj = charts.Add(ChartRange.Left, ChartRange.Top,
ChartRange.Width, ChartRange.Height);
Hope the syntax is OK; I don't work with C# - but I am sure you will get the
idea.
 
So this is how it would be done in VBA,

set r = Range("H4:J20")
activesheet.chartobjects.add r.left, r.top, _
r.width, r.height

I am sure you can adapt that to your situation.

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
Cells have Top, Left, Height, Width properties.
Click to expand...
 
Petr Prikryl said:
[...] how can I get the coordinates
where visually the cells from range (say) "H4:J20" sit?
Click to expand...
Click to expand...

A Range object has Top, Left, Width and Height properties that correspond
directly to the same properties in the ChartObject. So (assuming you have
set the variable ChartRange to equal the range you are interested in filling):
Excel.ChartObject chartObj = charts.Add(ChartRange.Left, ChartRange.Top,
ChartRange.Width, ChartRange.Height);
[...]
Click to expand...

Thanks, it works fine!
Thanks also to Tom Ogilvy who answered similarly.

Petr
 
Back
Top