Graph linked to Table does not resize

Sep 29, 2010 at 12:51 PM

Hi Guys,

firstly a big thanks to Tim for making this available to everyone.

I have exactly the same situation as this was designed to solve, that being a template spreadsheet containing data and 1 or more graphs linked to it.  The only problem I cannot seem to solve right now is that my regular Graphs do not reflect the increased size of my table.  This is odd as Excel does this for you if you perform a row insert in the Table, but perhaps that is the application and nothing to do with the raw file ??

I think Pivot charts would solve this but unfortunately I cannot use them as the template is not mine.  Does anyone know how I might solve this problem as I am so close to a solution now :-)  Do I need to go lower and use the full SDK?  If I do, can anyone share some code on how to do this?

Thanks for any help you can give me.

Darren

Coordinator
Sep 29, 2010 at 3:19 PM

Hi Darren,

I don't have any personal experience of using graphs, so I can't offer a direct solution, but here are a few ideas:

[1] If you were using a pivot chart in the same situation, you need to link it to its source table by name (not by coordinates). In this scenario ExtremeML does not manipulate the pivot chart when it adds rows to the table, but Excel detects the size mismatch and updates the coordinates the next time it opens the workbook. You might try experimenting with the way your graphs are configured, to see if you can find a solution that forces Excel to do the work that ExtremeML is missing.

[2] If the above experiment doesn't work, it suggests that a graph is directly manipulated by Excel whenever its associated table is modified. If this is the case, you should be able to identify exactly what is happening using the comparison utility in the SDK Tool. Just compare your template to the output file produced after a very simple table modification (like inserting one new table row).

Armed with this information you will be better able to decide on a solution. One approach is to revert to the SDK and code your table manipulation mechanism from scratch. However, be warned that this is very complex and liable to break if a future version of a template contains something that was not foreseen in your code. In fact, the problem you have reported is almost certainly an example of this. ExtremeML has been coded to cover the most common template scenarios, but there are many template features supported by Excel that would either be ignored by ExtremeML or worse - their use would result in a non-compliant package after manipulation with ExtremeML.

If you successfully identify the missing data ingredient that is essential to your graphs, an alternative approach is to extend ExtremeML to support it. You can either do this by building a set of classes that supports graphs and link them into the existing table row insertion event mechanism or you can write some standalone code that finds the objects that represent your graphs and update them directly. As a guess, you will probably be updating the size of a defined range, although it may be altogether more complex.

After doing some further investigative work, feel free to post your findings here and I will try to advise further. I wish I could react to every situation like this by developing additional ExtremeML functionality, but it is very time consuming and (like you, I suspect) I have to earn my living at the same time.

Good luck,

Tim

Sep 30, 2010 at 1:35 PM

hi Tim,

thanks for the help above.  In the end I have dropped down to the full SDK level and locate the shared strings that hold the series names and replace them with the correct values.  This took some doing as I had no idea how complex the XML was until I used the Productivity tool to browse it !  This is perhaps not the most elegent solution, but it gets the job done and perhaps when I have time I can give it some polish.

One thing I have had to do is perform a double open, first using your toolkit, close it and then reopen it using the full SDK.  Is there anyway that I can access the underlying Workbook from your toolkit to avoid this double opening ??

For others benefit here is the code that I used to locate the string values and replace them.  This operates on the first sheet containing 1 or more graphs, but can be extended as you like.

>>>>

SpreadsheetDocument myWorkbook = null;
myWorkbook = SpreadsheetDocument.Open(dest, true);
WorkbookPart workbookPart = myWorkbook.WorkbookPart;

// number of rows added

int totalCount = 10;

foreach (ChartPart chartPart in workbookPart.WorksheetParts.First().DrawingsPart.ChartParts )
{
 // Change the ranges to accomodate the newly inserted data.
 foreach (Formula formula in chartPart.ChartSpace.Descendants<Formula>())
 {
  // axis labels
         if (formula.Text.Contains("$A$49:$A$49"))
         {
           string s = formula.Text.Split(':')[0];
                 formula.Text = s + ":$A$" + (49 + totalCount);
         }
 
  if (formula.Text.Contains("$E$49:$E$49"))
         {
          string s = formula.Text.Split(':')[0];
                 formula.Text = s + ":$E$" + (49 + totalCount);
         }
         if (formula.Text.Contains("$F$49:$F$49"))
         {
   string s = formula.Text.Split(':')[0];
                 formula.Text = s + ":$F$" + (49 + totalCount);
  }
 }

 chartPart.ChartSpace.Save();
}

myWorkbook.Close();

 <<<<<<<

To get to this point I made reference to the following site:

http://blogs.msdn.com/b/brian_jones/archive/2008/11/04/document-assembly-solution-for-spreadsheetml.aspx

 

Coordinator
Sep 30, 2010 at 2:13 PM

Hi Darren,

That looks like a good interim solution and would serve as a model to anyone who might be inclined to extend ExtremeML to include this functionality.

To answer your question about combining your two passes into a single pass, I would recommend you look at the GetWrappedContentObject() method, which is exposed publicly by the WrapperBase class, from which every ExtremeML wrapper class derives. For any wrapper instance, this method returns a System.Object which is the wrapped SDK object. By casting the returned value to the appropriate type, you should be able to manipulate it directly, as you have done in your code above.

However, I can't be sure how successful this will be, as there is a possibility that ExtremeML will overwrite your changes when it re-constructs the wrapped object during package disposal. This is generally how the wrapper model works, which is why the Wrapped property (which exposes the wrapped object in strongly typed form) is not public. But, in your scenario, it should work, simply because ExtremeML doesn't currectly have any support for the ChartPart class, so it does not directly wrap the entire hierarchy of objects in a WorksheetPart. If you implement this idea successfully, beware of any future upgrade of ExtremeML that includes support for ChartPart, as it will almost certainly break your code. (At the same time, it should directly deliver the functionality you are currently missing).

Cheers,

Tim

 

 

Mar 1, 2011 at 6:54 PM

It may help if you change Formula formula in chartPart.ChartSpace.Descendants<Formula>()) to Chart.Formula formula in chartPart.ChartSpace.Descendants<Chart.Formula>()).

In my case I had references such that chart resolved to Spreadsheet.Formula instead of Chart.Formula and so I could never get to the formulas.