Question

Bradley Rogers on Mon, 03 Oct 2016 21:23:57


I have a working project that uses MVC and a word document with content controls and markers and a dummy barchart.  Using openXML I modify the parts including the barchart to produce a custom report.  Works great (finally).

But then a problem, the Horizontal barcharts have 3 values of 0-100%

the value has a green color, then the other part, the remainder is grey.  so "80" shows as 0-80 green and 81-100 as grey and at the bottom, the value lands exactly at 100.

Now sometimes the value exceeds 100%.  What the Word barchart does is to shrink the bar, so if its 150% ?  it looks to be around 70% but has '150' written there.

In a totally different app I was able to get the correct response, dhtmlx for web, it will now display perfectly, scaling as needed to the largest number.

In word?  no it looks terrible.  Trying to modify it? I used the OpenXML SDK productivity tool 2.5, reflect the code and merge this into the project. 

In the code I 'find' the SDT or content control then instantiate some xelement representing it, and create a ChartPart chartPart that I pass into a void method, just as the reflected code, and modify it.

I finally got this to extend out the axis from 1.0 for 100% to the variable for X, whatever the larger of the 3 values is.  but? I can modify the axis OR modify the values of the 3 items not both

The dummy barchart on the word doc used as a template has the values set at 20, 50, 90.  if I modify the chartPart blob in my method, the data will not be updated

  C2.MaxAxisValue maxAxisV = new DocumentFormat.OpenXml.Drawing.Charts.MaxAxisValue() { Val = 2 };  
    var mdXDoc = mainDoc.GetXDocument();
    var cc2 = mdXDoc.Descendants(W.sdt)
    .FirstOrDefault(sdt => (string)sdt.Elements(W.sdtPr).Elements(W.tag).Attributes(W.val).First() == "Chart1");
                if (cc2 != null)
                {

                    var chartRid = "rId5";// (string)cc.Descendants(C.chart).Attributes(R.id).FirstOrDefault();
                    if (chartRid != null)
                    {
                        ChartPart chartPart = (ChartPart)mainDoc.GetPartById(chartRid);
                        UpdateChart(chartPart, chartData);
                        var newContent = cc2.Elements(W.sdtContent).Elements().Select(e => new XElement(e));
                        // cc.AddAfterSelf(newContent)
                        cc2.ReplaceWith(newContent);
                        mainDoc.PutXDocument();
                    }
                }

and

 ValueAxis valueAxis1 = new ValueAxis();
        AxisId axisId4 = new AxisId(){ Val = (UInt32Value)422883200U };

        Scaling scaling2 = new Scaling();
        Orientation orientation2 = new Orientation(){ Val = OrientationValues.MinMax };
        MaxAxisValue maxAxisValue1 = new MaxAxisValue(){ Val = 1.0001D };
        MinAxisValue minAxisValue1 = new MinAxisValue(){ Val = -1.0000000000000003E-4D };

        scaling2.Append(orientation2);
        scaling2.Append(maxAxisValue1);
        scaling2.Append(minAxisValue1);
        Delete delete2 = new Delete(){ Val = false };
        AxisPosition axisPosition2 = new AxisPosition(){ Val = AxisPositionValues.Bottom };

What does it take to modify parts of the barchart?  


Bradley :)


Sponsored



Replies

Deepak Panchal10 on Tue, 04 Oct 2016 09:20:33


Hi Bradley Rogers,

you had mentioned that ,"you take the word document with dummy bar chart"

did make this chart using open xml or manually by user interface?

you had mentioned that templates values are 20,50,90.

how you set it ? or how you try to change it?

did you try to change the value from user interface?

Regards

Deepak

Bradley Rogers on Tue, 04 Oct 2016 14:20:26


Hi Deepak

I just opened a normal Office 2013 word document and saved it as a .docx

I manually went into developer mode, made a content control then made a horizontal bar chart with 3 bars, it lets you edit the values and pulls up a tiny excel spreadsheet and you change the values there, then save.

If you delete or close that tiny spreadsheet?  It seems to be lost forever and you can never modify the values.  If you click on the barchart and try to edit values?  if that excel sheet ever got closed, then it will not open it.

What I do is use some code that looks for the content control called 'Chart1' and it does some magic with the XElement notation to get in at the xml level,  it finds the chart, which has I think 2 parts,  a ChartPart and a ChartData, which is an array or in this case  a 2D array?

I adapted code from the Eric White website and am able to get in and modify the barchart values from code, but cannot figure out how to stretch the barchart's MaxAxis value which is usually set at 1.00  meaning 100%

I know there are 2 chart sections you have to modify for any barchart, and you can see this in the reflected code.

What I did was open my word document, found the chart code, then copied all of it into my code, then where my code extracts the chart from the document, I pass this into a void method and modify the maxAxis value, then the code continues where it was to update the numbers in the bars?  no.  It does nothing to them. 

As if when I modify the chart in the void method? it locks out.  I have tried many variations, and tried to modify the contents of the bars from in this new reflected code I added?  but it has not worked.  and I cannot find out details of how the barchart unit "works" from a programming point.  and Eric White has no comment, says he did not do barcharts.

I really just want to find out how to modify the chart "blob" successfully, I can figure out which parts to change, but dont know why its not allowing more than 1 modification

Deepak Panchal10 on Wed, 05 Oct 2016 07:19:14


Hi Bradley Rogers,

here I think that you are talking about the demo mentioned in the link below.

Update Data behind an embedded Chart in an Open XML WordprocessingML Document

if you are trying to add the chart manually then try to set the maximum Axis value when you create a chart in the word document.

like mentioned in the picture below.

Change the scale of the horizontal (category) axis in a chart

and then try to modify the data > 100 and check it displays correctly or not.

Regards

Deepak

Bradley Rogers on Wed, 05 Oct 2016 18:12:25


Hi Deepak

Thank you for that video link, I had tried to find but had not.

yes I had referenced this video months ago, and got my working code from this;  but have spent many hours today reviewing this video and making detailed notes.  I found a couple errors I was making.

My barchart was a 0-100% barchart.  I was trying to force this to go 120% to no avail.  and in the code I may have been trying to modify the chartpart directly instead of making it into a memory stream then modifying that because the original cannot change its size

will be trying some major changes and reply with results

Deepak Panchal10 on Thu, 06 Oct 2016 04:16:09


Hi Bradley ,

we will wait for your testing results.

Regards

Deepak

Bradley Rogers on Fri, 07 Oct 2016 16:13:30


no the code does not work.  I wrote out every step of code from that video.  took many hours.

I carefully added the code.  at the end?

                            DocumentFormat.OpenXml.Drawing.Chart chart = chartPart.ChartSpace.Elements<DocumentFormat.OpenXml.Drawing.Chart>().First();
                            DocumentFormat.OpenXml.Drawing.Charts.BarChart bc = chart.Descendants<DocumentFormat.OpenXml.Drawing.Charts.BarChart>().FirstOrDefault();

No, it crashes here saying object contains no elements.  my chartPart reference is to the real, actual chart part, its assumed the rest of the objects are found firstordefault; 

it changes these items, but never rolls them back in to the stream, which was already closed.

I could not find any example of live code from Eric on this video.  If I take out the 2nd part of modifying the bar chart series and run?  it gets a corrupted word doc that cannot be opened.

 and using Eric's method there making a chart in Excel and embedding it?  it creates a blob of binary data apparently representing the chart???   so this means apparently its no longer an xml element but an encrypted blob


Bradley :)


Bradley Rogers on Mon, 10 Oct 2016 14:30:53


Am trying to find any working version of the code from that video. Tried many iterations.  Does not work.

As it is, it corrupts the Word Docx file. 

Bradley :)


Deepak Panchal10 on Tue, 11 Oct 2016 05:46:01


Hi Bradley,

from the description of the thread I can assume that you are using open xml to modify the chart properties.

do you have any scope to use the VSTO to create chart and modify it using VSTO.

if so you can try it. word object model provides a wide range of properties and methods.

Chart Object (Word)

Below is the code to create demo chart

private void ThisDocument_Startup(object sender, System.EventArgs e)
        {
            Word.Application word = null;

              word = new Word.Application();
              word.Visible = true;
              Word.Document doc = word.Documents.Add(ref missing, ref missing, ref missing, ref missing);

              Word.Chart wdChart = doc.InlineShapes.AddChart(Microsoft.Office.Core.XlChartType.xlBarStacked, ref missing).Chart;

              Word.ChartData chartData = wdChart.ChartData;

              Excel.Workbook dataWorkbook = (Excel.Workbook)chartData.Workbook;
              Excel.Worksheet dataSheet = (Excel.Worksheet)dataWorkbook.Worksheets[1];

              Excel.Range tRange = dataSheet.Cells.get_Range("A1", "B5");
              Excel.ListObject tbl1 = dataSheet.ListObjects["Table1"];
              tbl1.Resize(tRange);

              ((Excel.Range)dataSheet.Cells.get_Range("A2", missing)).FormulaR1C1 = "Bikes";
              ((Excel.Range)dataSheet.Cells.get_Range("A3", missing)).FormulaR1C1 = "Accessories";
              ((Excel.Range)dataSheet.Cells.get_Range("A4", missing)).FormulaR1C1 = "Repairs";
              ((Excel.Range)dataSheet.Cells.get_Range("A5", missing)).FormulaR1C1 = "Clothing";
              ((Excel.Range)dataSheet.Cells.get_Range("B2", missing)).FormulaR1C1 = "1000";
              ((Excel.Range)dataSheet.Cells.get_Range("B3", missing)).FormulaR1C1 = "2500";
              ((Excel.Range)dataSheet.Cells.get_Range("B4", missing)).FormulaR1C1 = "4000";
              ((Excel.Range)dataSheet.Cells.get_Range("B5", missing)).FormulaR1C1 = "3000";

              wdChart.ChartTitle.Font.Italic = true;
              wdChart.ChartTitle.Font.Size = 18;
              wdChart.ChartTitle.Font.Color = Color.Black.ToArgb();
              wdChart.ChartTitle.Text = "2007 Sales";
              wdChart.ChartTitle.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;

              wdChart.ChartTitle.Format.Line.ForeColor.RGB = Color.Black.ToArgb();

              wdChart.ApplyDataLabels(Word.XlDataLabelsType.xlDataLabelsShowLabel, missing, missing, missing, missing, missing, missing, missing, missing, missing);

              dataWorkbook.Application.Quit();

        }

then you can refer the object model to format the chart properties as per your requirement.

it is easy then open xml.

Regards

Deepak

Bradley Rogers on Tue, 11 Oct 2016 14:41:00


Hi Deepak

the software runs in MVC on a Windows Server machine somewhere on the network.  It cannot depend on any Office software.

I also use CLOSED XML which is apparently strictly for Excel, but if I can get the handle of the embedded excel file..  but then again, the problem is my code now? it modifies the values just fine.  It cannot extend the axis, I just need to push the axis, then its done.

So in my program it updates the values in a 0 - 100% stacked bar chart.  It was decided that the values may not stop at 100% because sometimes they go over and it now must show the value on a properly scaled bar chart graph. 

I looked at the video you linked to, that one uses a bar chart with an embedded excel spreadsheet, my code does not use one, it digs into a content control then the chart then finds the value from an XElement cast

I spent HOURS picking apart the steps in the video to make the changes to a barchart, when I do the simplest of change the word doc is corrupt.  I have also looked at the reflected code from the openxml SDK tool and put that code in to create a barchart.  I can extend the axis but cannot modify the bar values, its one or the other.

What I've done recently is to change the chart type from 0 to 100% to just regular, and then in the settings put the scale as Percent, with the step as 0%, then it shows as a 0 - 100% bar chart.

Using DHTMLX stacked H barchart, I was able to create a jquery type web chart easily.  if I could embed that chart into my word document?  that would work, have you ever done that?  this is a barchart jquery add in that runs in the MVC view in a .cshtml file

thanks

Bradley Rogers on Tue, 11 Oct 2016 19:56:35


How about this:  3 bar stacked horizontal bar chart 0-100% is good, but wont show values over 100%  so I switch to just a 3 bar stacked horiz bar chart.

it has blue for the value and grey for the remainder.  So its series 1 element 1 = blue value, element 2 = 100-blue value

so on each line the 2 stacked bars add to 100 every time.  PROBLEM:  the bar chart axis insists on expanding to 120%

it MUST stay on 100% and not go over for the Axis values if the values are less than 100.  have tried 100's of settings

Deepak Panchal10 on Mon, 17 Oct 2016 08:57:21


Hi Bradley ,

I can understand it is expanding to 120% every time.

but is it showing you correct data?

or still have the same issue like the last chart?

Regards

Deepak