locked
How can I get the chart to display the latest data on open? RRS feed

  • Question

  • I have a Word document that has a chart. 

    The chart's data is updated on the server using the Open XML Format SDK tool. When the document is opened the chart does not show the latest data.

    When the user right-clicks the chart and chooses "Edit Data" then the data is displayed and the chart is updated.

    Is there a mechanism by which the Chart's data can be refreshed automatically without user intervention?

    Siggy

    Sunday, May 2, 2010 6:54 PM

Answers

  • Hi Siggy,

    Thanks for your question.

    The Word application reads data and draws the chart based on the data stored under "c: chart", below is an example of a series in a bar chart:

    <c:ser>

    <c:idx val="2"/>

    <c:order val="2"/>

    <c:tx>

    <c:strRef>

    <c:f>Sheet1!$D$1</c:f>

    <c:strCache>

    <c:ptCount val="1"/>

    <c:pt idx="0">

    <c:v>Series 3</c:v>

    </c:pt>

    </c:strCache>

    </c:strRef>

    </c:tx>

    <c:invertIfNegative val="0"/>

    <c:cat>

    <c:strRef>

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

    <c:strCache>

    <c:ptCount val="4"/>

    <c:pt idx="0">

    <c:v>Category 1</c:v>

    </c:pt>

    <c:pt idx="1">

    <c:v>Category 2</c:v>

    </c:pt>

    <c:pt idx="2">

    <c:v>Category 3</c:v>

    </c:pt>

    <c:pt idx="3">

    <c:v>Category 4</c:v>

    </c:pt>

    </c:strCache>

    </c:strRef>

    </c:cat>

    <c:val>

    <c:numRef>

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

    <c:numCache>

    <c:formatCode>General</c:formatCode>

    <c:ptCount val="4"/>

    <c:pt idx="0">

    <c:v>2</c:v>

    </c:pt>

    <c:pt idx="1">

    <c:v>2</c:v>

    </c:pt>

    <c:pt idx="2">

    <c:v>3</c:v>

    </c:pt>

    <c:pt idx="3">

    <c:v>5</c:v>

    </c:pt>

    </c:numCache>

    </c:numRef>

    </c:val>

    </c:ser>

    The cached data is stored in "c:v", and if you don't edit it, there will be no change. I have also tried to delete the "c:numCache", however, the corresponding data will not be displayed rather than being updated when opening it in Word. Unfortunately I can't find any property which defines the "auto update" like "Cell.CellFormula.CalculateCell.Value" in SpreadsheetML either. Since all the recaculation and content updating are done by the application, I guess if you don't "Edit Data", the Excel engine will not be activated and thus the content will not be updated.

    For your scenario, I suggest taking a look at Word Automation Service or Excel Services which needs SharePoint being installed and could do such kinds of automatic content updating.

    Hope this helps. If you have any question, please let me know.

    Thanks,

    Lu

     

    • Marked as answer by Siggy01 Tuesday, May 4, 2010 6:52 PM
    Tuesday, May 4, 2010 6:02 AM

All replies

  • Hi Siggy,

    Thanks for your question.

    The Word application reads data and draws the chart based on the data stored under "c: chart", below is an example of a series in a bar chart:

    <c:ser>

    <c:idx val="2"/>

    <c:order val="2"/>

    <c:tx>

    <c:strRef>

    <c:f>Sheet1!$D$1</c:f>

    <c:strCache>

    <c:ptCount val="1"/>

    <c:pt idx="0">

    <c:v>Series 3</c:v>

    </c:pt>

    </c:strCache>

    </c:strRef>

    </c:tx>

    <c:invertIfNegative val="0"/>

    <c:cat>

    <c:strRef>

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

    <c:strCache>

    <c:ptCount val="4"/>

    <c:pt idx="0">

    <c:v>Category 1</c:v>

    </c:pt>

    <c:pt idx="1">

    <c:v>Category 2</c:v>

    </c:pt>

    <c:pt idx="2">

    <c:v>Category 3</c:v>

    </c:pt>

    <c:pt idx="3">

    <c:v>Category 4</c:v>

    </c:pt>

    </c:strCache>

    </c:strRef>

    </c:cat>

    <c:val>

    <c:numRef>

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

    <c:numCache>

    <c:formatCode>General</c:formatCode>

    <c:ptCount val="4"/>

    <c:pt idx="0">

    <c:v>2</c:v>

    </c:pt>

    <c:pt idx="1">

    <c:v>2</c:v>

    </c:pt>

    <c:pt idx="2">

    <c:v>3</c:v>

    </c:pt>

    <c:pt idx="3">

    <c:v>5</c:v>

    </c:pt>

    </c:numCache>

    </c:numRef>

    </c:val>

    </c:ser>

    The cached data is stored in "c:v", and if you don't edit it, there will be no change. I have also tried to delete the "c:numCache", however, the corresponding data will not be displayed rather than being updated when opening it in Word. Unfortunately I can't find any property which defines the "auto update" like "Cell.CellFormula.CalculateCell.Value" in SpreadsheetML either. Since all the recaculation and content updating are done by the application, I guess if you don't "Edit Data", the Excel engine will not be activated and thus the content will not be updated.

    For your scenario, I suggest taking a look at Word Automation Service or Excel Services which needs SharePoint being installed and could do such kinds of automatic content updating.

    Hope this helps. If you have any question, please let me know.

    Thanks,

    Lu

     

    • Marked as answer by Siggy01 Tuesday, May 4, 2010 6:52 PM
    Tuesday, May 4, 2010 6:02 AM
  • Thanks for your detailed response Lu.

    It is a pity that my code needs to write the same data into 2 different locations in 2 different formats (ie to the spreadsheet and the Word chart element) when the Word application already has the logic to update the chart element from the spreadsheet.

    I will follow-up on your suggested links.

    Siggy

    Tuesday, May 4, 2010 6:52 PM