none
How to Modify Embedded Chart Range RRS feed

Answers

  • Hi Jeff,

    According to your description, you want to change the used ranged of a chart which embedded in Word document.

    I made a document as follows:

    Since you know how to insert or modify the embedded worksheet, I skip this step.

    I found the related xml via Open XML SDK Productivity Tool as follows:

    If you want to modify the used range, you need to loop all Formula Class (c:f) elements and change the following XML:

    <c:f>Sheet1!$D$2:$D$5</c:f>

    to

    <c:f>Sheet1!$D$2:$D$6</c:f>

    After that, you could find the used range and the chart were modified.
    Hope this could give you help.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 25, 2013 3:50 PM
    Moderator
  • Hi Ana,

    I made a test for you.

    I create a workbook embed an chart which uses current worksheet range as follows:

    The related XML of Chart data range is RadarChartSeries (The figure below shows the structure of the related elements), you should focus on Values under every RadarChartSeries element.

    In my opinion, RadarChartSeries(c:ser) represent every row in the chart data range excluded the header (header stores in CategoryAxisData (c:cat)), so if there is are two rows, there will be two RadarChartSeries type of nodes under RadarChart (c:radarChart).

    Finally, if you want to add or remove rows of chart data range, you need to add or remove RadarChartSeries. If you want to change the column of chart data range, you need to loop all RadarChartSeries nodes, change the Formula Class (c:f) and add or remove NumericPoint (c:pt).

    Hope this will give you help.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 17, 2014 1:23 AM
    Moderator

All replies

  • Hi Jeff,

    According to your description, you want to change the used ranged of a chart which embedded in Word document.

    I made a document as follows:

    Since you know how to insert or modify the embedded worksheet, I skip this step.

    I found the related xml via Open XML SDK Productivity Tool as follows:

    If you want to modify the used range, you need to loop all Formula Class (c:f) elements and change the following XML:

    <c:f>Sheet1!$D$2:$D$5</c:f>

    to

    <c:f>Sheet1!$D$2:$D$6</c:f>

    After that, you could find the used range and the chart were modified.
    Hope this could give you help.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 25, 2013 3:50 PM
    Moderator
  • Hi George,

    I have more questions about this. I have opened an existing XSLX file with the OpenXML and using it as template. It contains a single Sheet (Sheet1) with a single Chart. So I don't want to redraw the chart.

    The code is already capable of filling in the cells with the headers and values and those are correctly drawn in the radar chart.

    Because the number of columns that should be used depends on a category selected by the user on the website, I just want to adjust the data range. This looks so obvious when you use Excels GUI! One does only need to change the Chart data range in the Select Data Source popup.

    I've followed the hierarchy and found the ChartSpace object that contains the chart itself (class DocumentFormat.OpenXML.Drawing.Charts.Chart)

    WorkSheetParts [0] / WorkSheet / WorkSheetPart / DrawingPart / ChartParts [0] / ChartSpace [1]

    Its first child is the PlotArea which has the RadarChart as child. But I still can't find the Formula or <c:f>. 

    Even if I did, I still don't know how to change it: should I change the InnerText? The InnerXML? Both? Or something else?

    Thanks a lot,

    Ana Paula


    Monday, January 6, 2014 1:38 PM
  • Hi George,

    I have more questions about this. I have opened an existing XSLX file with the OpenXML and using it as template. It contains a single Sheet (Sheet1) with a single Chart. So I don't want to redraw the chart.

    The code is already capable of filling in the cells with the headers and values and those are correctly drawn in the radar chart.

    Because the number of columns that should be used depends on a category selected by the user on the website, I just want to adjust the data range. This looks so obvious when you use Excels GUI! One does only need to change the Chart data range in the Select Data Source popup.

    I've followed the hierarchy and found the ChartSpace object that contains the chart itself (class DocumentFormat.OpenXML.Drawing.Charts.Chart)

    WorkSheetParts [0] / WorkSheet / WorkSheetPart / DrawingPart / ChartParts [0] / ChartSpace [1]

    Its first child is the PlotArea which has the RadarChart as child. But I still can't find the Formula or <c:f>. 

    Even if I did, I still don't know how to change it: should I change the InnerText? The InnerXML? Both? Or something else?

    Thanks a lot,

    Ana Paula

    Update: I've found a list of formulas calling the descendants of the ChartSpace. And I've found an example showing how to edit the formula's Text (http://blogs.msdn.com/b/brian_jones/archive/2008/11/04/document-assembly-solution-for-spreadsheetml.aspx)

    SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true);
    WorkbookPart wbPart = document.WorkbookPart;
    ChartPart chartPart = wbPart.WorksheetParts.First().Worksheet.WorksheetPart.DrawingsPart.ChartParts.First();

    var firstColumn = "$A";
    var firstLabel = "$B$1";
    var firstResultColumn = "$B";

    foreach (var formula in chartPart.ChartSpace.Descendants<DocumentFormat.OpenXml.Drawing.Charts.Formula>())
    {
       if (!formula.Text.Contains(firstColumn)) // product name
       {
          if (formula.Text.Contains(firstLabel)) // attribute labels
          {
             formula.Text = sheetName + "!" + firstLabel + ":" + "$" + lastColumn + "$1";
          }
          else // results row
          {
             string row = formula.Text.Split('$').Last();
             formula.Text = sheetName + "!" + firstResultColumn + "$" + row + ":$" + lastColumn + "$" + row;
          }
       }
    }

    lastColumn is the calculated letter based on the number of attributes + 1. (1 = A, 2 = B, 3 = C, etc...)

    But his list includes 3 formulas for each row on my table: its label, a range of the columns' labels and a range with the values themselves. The example I've followed, pointed to a ChartSpace within the ChartParts instead of the WorkSheetParts. But on my file, the ChartParts was empty.

    The funny thing is, even if I remove the empty formulas from the ChartSpace (when the user select less than 5 products to compare) the data range seems to be correctly updated. Unfortunately, the legend still uses all initial 5 products, leaving "empty" references in the legend showing the unused colors.

    That is why I believe I am changing the wrong ranges... Not the ones associated to the Chart Object, but the ones created for the drawing within the Chart itself.

    Then I ask again: how do I (correctly) change the data range associated to an existing Chart? Or is there another shortcut to change the range used in the Legend?

    Regards, Ana Paula.


    Monday, January 13, 2014 4:45 PM
  • Hi Ana,

    I made a test for you.

    I create a workbook embed an chart which uses current worksheet range as follows:

    The related XML of Chart data range is RadarChartSeries (The figure below shows the structure of the related elements), you should focus on Values under every RadarChartSeries element.

    In my opinion, RadarChartSeries(c:ser) represent every row in the chart data range excluded the header (header stores in CategoryAxisData (c:cat)), so if there is are two rows, there will be two RadarChartSeries type of nodes under RadarChart (c:radarChart).

    Finally, if you want to add or remove rows of chart data range, you need to add or remove RadarChartSeries. If you want to change the column of chart data range, you need to loop all RadarChartSeries nodes, change the Formula Class (c:f) and add or remove NumericPoint (c:pt).

    Hope this will give you help.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 17, 2014 1:23 AM
    Moderator