none
OpenXML Document Assembly - Updating Chart Data RRS feed

  • Question

  • I am currently using OpenXML to generate a spreadsheet document template with a chart.

    The chart data is updated based on user requirements (all in code).

    I then assemble (via a SharePoint 2010 webpart) a word processing document that includes the chart.

    When I complete the assembly and open the word processing document, the chart object is there, but the chart data is still referencing the spreadsheet template data (not the updated chart/data).

    If I look at the spreadsheet document file, the data and the chart is updated.

    If I open the spreadsheet file and click save in Microsoft Excel and re-run the document assembly, the assembled word document has the correct (updated) chart.

    Does anyone have an idea for why it only works if I manually open the Excel file and save it?

    Thanks.

    P.S. - I have tried to programmatically open the Excel file and save it before assembling the document.  The code always fails and says that the file is being used by another process.


    Jason
    • Moved by Lie You Thursday, January 26, 2012 6:41 AM Move it for good discussion (From:Visual C# General)
    Tuesday, January 24, 2012 7:09 PM

Answers

  • Hi Jason,

    You have to update the cached values.  I've made a video that shows where the cached values are.  There is also some sample code that shows how to update the cached values.

    http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2011/03/28/132189.aspx

    Note that this code focuses on exactly how to update the cached values, not on management of charts in a spreadsheet.  It uses a artificially simple approach to find the chart in the spreadsheet.  For your real code, you will need to find the chart by whatever method is appropriate.

    -Eric

    • Proposed as answer by cjatmsModerator Monday, February 6, 2012 2:54 PM
    • Marked as answer by Bruce Song Friday, February 10, 2012 2:58 AM
    Saturday, February 4, 2012 11:12 PM

All replies

  • Hi Jason,

    Thank you for posting.

    I will help you involve others to help you. There might be some delay about the response. Appreciate your patience.


    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Thursday, January 26, 2012 8:17 AM
  • Hi Jason,

    It’s possible you have been using cached data when assembling your Word document. The following content from TechNet was written when Windows 2000 was released, but the concept is current with the latest versions of Windows:
    File Cache Performance and Tuning - Microsoft TechNet: Resources ...
    http://technet.microsoft.com/en-us/library/bb742613.aspx

    To help you with the message “the file is being used by another process” does this happen when your code is trying to open a file, and which file, or when it is trying to save a file, and which one. Is there more to the message, and if so could you please give us the exact wording.  Also, where is the code running – on the SharePoint server or in the client application of the local desktop?

    Thanks for your feedback.

    Regards,
    Chris Jensen
    Senior Technical Support Lead


    Chris Jensen
    Monday, January 30, 2012 10:19 PM
    Moderator
  • Hi Jason,

    You have to update the cached values.  I've made a video that shows where the cached values are.  There is also some sample code that shows how to update the cached values.

    http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2011/03/28/132189.aspx

    Note that this code focuses on exactly how to update the cached values, not on management of charts in a spreadsheet.  It uses a artificially simple approach to find the chart in the spreadsheet.  For your real code, you will need to find the chart by whatever method is appropriate.

    -Eric

    • Proposed as answer by cjatmsModerator Monday, February 6, 2012 2:54 PM
    • Marked as answer by Bruce Song Friday, February 10, 2012 2:58 AM
    Saturday, February 4, 2012 11:12 PM