Eric White's Blog

Hi Eric,
your video https://www.youtube.com/watch?v=xOJ_AgXgL-M talks about modifying bar charts. still struggling with this.

I have gone thru the video step by step, duplicating the code there. All I get is a corrupted word document.

Is there a code snippet for this? I run this and it seems to modify bar chart data but refuses to open saying the file is corrupt.

  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);
                            string externalDataRid = "rId3";
                            //  ExternalData ed = chartPart.ChartSpace.Elements<ExternalData>().FirstOrDefault();

                            C2.ExternalData ed = chartPart.ChartSpace.Elements<C2.ExternalData>().FirstOrDefault();

                            if (ed != null)
                            {
                                externalDataRid = ed.Id;
                            }
                            EmbeddedPackagePart epp = (EmbeddedPackagePart)chartPart.Parts
                                .Where(pt => pt.RelationshipId == externalDataRid).FirstOrDefault()
                                .OpenXmlPart;

                         //   using (Stream str = epp.GetStream())

                            using(Stream str2 = epp.GetStream())
                            using (MemoryStream ms = new MemoryStream())
                            {
                                CopyStream(str2, ms);
                                using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(ms, true))
                                {
                                    ss.Sheet ws = (ss.Sheet)spreadsheetDoc.WorkbookPart
                                        .Workbook.Sheets.FirstOrDefault();
                                    string sheetId = ws.Id;
                                    WorksheetPart wsp = (WorksheetPart)spreadsheetDoc.WorkbookPart.Parts
                                        .Where(pt => pt.RelationshipId == sheetId).FirstOrDefault()
                                        .OpenXmlPart;
                                    ss.SheetData sd = wsp.Worksheet.Elements<ss.SheetData>().FirstOrDefault();
                                    ss.Row secondRow = sd.Elements<ss.Row>().Skip(2).FirstOrDefault();
                                    if(secondRow != null)
                                    {
                                        ss.CellValue cell1value = secondRow.Elements<ss.Cell>().ElementAt(0)
                                            .Elements<ss.CellValue>().FirstOrDefault();
                                        cell1value.Text = "33";

                                        ss.CellValue cell2value = secondRow.Elements<ss.Cell>().ElementAt(1)
                                           .Elements<ss.CellValue>().FirstOrDefault();
                                        cell2value.Text = "44";
                                        //ss.CellValue cell3value = secondRow.Elements<ss.Cell>().ElementAt(3)
                                        //   .Elements<ss.CellValue>().FirstOrDefault();
                                        //cell3value.Text = "77";
                                    }
                                }
                                using (Stream s = epp.GetStream())
                                    ms.WriteTo(s);

                            }

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

                            if (bc != null)
                            {
                                C2.BarChartSeries bcs = bc.Elements<C2.BarChartSeries>().First();
                                if (bcs != null)
                                {
                                    C2.Values values = bcs.Descendants<C2.Values>().First();
                                    C2.NumberingCache nc = values.Descendants<C2.NumberingCache>().First();

                               //     C2.NumericValue nv1 = nc.Elements<C2.NumericPoint>().ElementAt(0)
                                        C2.NumericValue nv1 = nc.Elements<C2.NumericPoint>().ElementAt(0)
                                        .Elements<C2.NumericValue>().FirstOrDefault();
                                    nv1.Text = "33";
                                  //  C2.NumericValue nv2 = nc.Elements<C2.NumericPoint>().ElementAt(1)
                                          C2.NumericValue nv2 = nc.Elements<C2.NumericPoint>().ElementAt(1)
                                       .Elements<C2.NumericValue>().FirstOrDefault();
                                    nv2.Text = "44";
                                    //C2.NumericValue nv3 = nc.Elements<C2.NumericPoint>().ElementAt(2)
                                    //   .Elements<C2.NumericValue>().FirstOrDefault();
                                    //nv3.Text = "77";

                                }

                            }

I am only opening up 1 word doc. I have the barchart inside a content control, the other code taking a different approach with XElements does modify the chart data and worked until I had to modify the axis. Im trying this approach.

also Im wondering why it seems to create a binary file for the chart data? you make a barchart in Excel, copy and say Embed chart. I did this exactly as your example shows. look in the xml tool and its got a binary literal string blob where the data is:

 #region Binary Data
        private string embeddedPackagePart1Data = "UEsDBB AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAUhuAfOUZZQC8ysA0MzhfW1CPTo59wJuRBz4LeDwR2X1gQwIQ2NBhuPnqAQSx2S5w293jnxoJElj7uFDStnwjldSRHIKV8Z9YOStoSMIuMaLCuHN2SD8aOEZuZ3QBv3RqnxlYJkKnx4FTXZ4BvN19YvPq1dZKdFjri0RVFJUFYua8pAhs6DUFgChFpnccxqUZm97xP8uBh5HIY9G2n2F4U7fAT63sDj9XILUaYDiGGrAftOexTtIpfCg3oPniqjdwPftTt8KC/WjQXe3lye91aogyvJY0Aeh96YdOaj4Hns+PHPYdeY7upspx7jztye1dbjPxgyBjYH6RSAAAAAAAAA

but this was not there in my original word doc used for a template. I got the embedded RID as rId3

it should be so simple but week after week still cannot solve this.