question

AleenaJames-9342 avatar image
0 Votes"
AleenaJames-9342 asked AleenaJames-9342 published

embeded Charts in words do not refresh automatically without user clicking on edit data button

public void ChartFillTotalnumberofAlertsperPlatformonBasedonPriority(System.Data.DataTable dt, int chartindex)
{
if (ClsConnection.OpenConnection())
{


                 ReportGenerator u = new ReportGenerator();

                 u.OpenTheDocuemnt(destinationFile);


                 if (dt.Rows.Count > 0)

                 {

                     for (int j = 1, m = 2; j <= 31; j++, m++)
                     {
                         flag = 0;
                         for (int i = 0; i < dt.Rows.Count; i++)
                         {
                             int tgon = Convert.ToInt32(dt.Rows[i]["Triggered On"]);
                             if (tgon == j)
                             {
                                 tgpos = i;
                                 tg = j;
                                 flag = 1;
                                 break;
                             }
                         }
                         if (flag == 1)
                         {
                             u.UpdateChart("B", Convert.ToUInt16(m), dt.Rows[tgpos]["Count"].ToString(), false, chartindex);
                             u.UpdateChart("A", Convert.ToUInt16(m), dt.Rows[tgpos]["Triggered On"].ToString(), true, chartindex);
                         }
                         else
                         {
                             u.UpdateChart("B", Convert.ToUInt16(m), "0", false, chartindex);

                         }




                     }
                 }
                 u.CloseTheDocument();
             }



public void UpdateChart(string cellColumn, uint intRow, string newValue, bool axisValue, int chartindex)
{

         // Gets the Chart stream
         Stream stream = this.wordProcessingDocument.MainDocumentPart.ChartParts.ElementAt(chartindex).EmbeddedPackagePart.GetStream();

         // Open the internal spreadsheet doc for the chart
         using (SpreadsheetDocument wordSSDoc = SpreadsheetDocument.Open(stream, true))
         {
             // Navigate to the sheet where the chart data is located
             WorkbookPart workBookPart = wordSSDoc.WorkbookPart;
             Sheet theSheet = workBookPart.Workbook.Descendants<Sheet>().
               Where(s => s.Name == "Sheet1").FirstOrDefault();

             if (theSheet != null)
             {
                 Worksheet ws = ((WorksheetPart)workBookPart.GetPartById(theSheet.Id)).Worksheet;

                 // Get the cell which needs to be updated
                 Cell theCell = InsertCellInWorksheet(cellColumn, intRow, ws);

                 // Update the cell value
                 theCell.CellValue = new CellValue(newValue);
                 if (axisValue)
                 {
                     // We are updating the Series text
                     theCell.DataType = new EnumValue<CellValues>(CellValues.String);
                 }
                 else
                 {
                     // We are updating a numeric chart value
                     theCell.DataType = new EnumValue<CellValues>(CellValues.Number);
                 }

                 // Either one of these methods work. It is just to illustrate the different elements that the OpenXML goes through
                 this.ModifyChartDetailed(cellColumn, intRow, newValue, axisValue);
                 //this.ModifyChartSimplified(cellColumn, intRow, newValue, axisValue);

                 //var settingPart = this.wordProcessingDocument.MainDocumentPart.GetPartsOfType<DocumentSettingsPart>().First();
                 //var updateFields = new UpdateFieldsOnOpen();
                 //updateFields.Val = new OnOffValue(true);
                 //settingPart.Settings.PrependChild<UpdateFieldsOnOpen>(updateFields);
                 //settingPart.Settings.Save();
                   
                 ws.Save();

                 


             }



         }



     }

     #endregion

     #endregion

     #region Private Methods

     #region InsertCellInWorksheet
     /// <summary>
     /// Gets the cell where the updated value is going to be placed
     /// </summary>
     /// <param name="columnName">Corresponds to the Column that needs to be modified</param>
     /// <param name="rowIndex">Corresponds to the Row that needs to be modified</param>
     /// <param name="worksheet">WorkSheet to update</param>
     /// <returns> The cell where the new value is going to get updated to</returns>
     private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, Worksheet worksheet)
     {
         SheetData sheetData = worksheet.GetFirstChild<SheetData>();
         string cellReference = columnName + rowIndex;
         Row row;
         if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
         {
             row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).ElementAt(0);
         }
         else
         {
             row = new Row() { RowIndex = rowIndex };
             sheetData.Append(row);
         }

         if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
         {
             return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).ElementAt(0);
         }
         else
         {
             Cell refCell = null;
             foreach (Cell cell in row.Elements<Cell>())
             {
                 if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                 {
                     refCell = cell;
                     break;
                 }
             }

             Cell newCell = new Cell() { CellReference = cellReference };

             row.InsertBefore(newCell, refCell);

             worksheet.Save();
             return newCell;
         }
     }
     #endregion

     #region ModifyChartDetailed
     /// <summary>
     /// Updates the Cached Chart data in the Word document file
     /// </summary>
     /// <param name="cellColumn">Corresponds to the Column that needs to be modified</param>
     /// <param name="intRow">Corresponds to the Row that needs to be modified</param>
     /// <param name="cellValue">New value of the pointed cell</param>
     /// <param name="axisValue">Is the new value an Axis Value?</param>
     private void ModifyChartDetailed(string cellColumn, uint intRow, string cellValue, bool axisValue)
     {
         try
         {
             ChartPart c_p = this.mainDocPart.ChartParts.FirstOrDefault();
             Chart chart = c_p.ChartSpace.Descendants<Chart>().FirstOrDefault();
             PlotArea p_c = chart.PlotArea;
             Bar3DChart b3d = p_c.Descendants<Bar3DChart>().FirstOrDefault();
             BarChartSeries bs1 = b3d.Descendants<BarChartSeries>().Where(s => string.Compare(s.InnerText, "Sheet1!$" + cellColumn + "$1", true) > 0).First();
                
             if (axisValue)
             {
                 CategoryAxisData v1 = bs1.Descendants<CategoryAxisData>().FirstOrDefault();
                 StringReference sr = v1.Descendants<StringReference>().ElementAt(0);
                 StringCache sc = sr.Descendants<StringCache>().ElementAt(0);
                 StringPoint sp = sc.Descendants<StringPoint>().ElementAt(0);
                 NumericValue nv = sp.Descendants<NumericValue>().ElementAt(0);
                 nv.Text = cellValue;


             }
             else
             {
                 DocumentFormat.OpenXml.Drawing.Charts.Values v1 = bs1.Descendants<DocumentFormat.OpenXml.Drawing.Charts.Values>().FirstOrDefault();
                 NumberReference nr = v1.Descendants<NumberReference>().ElementAt(0);
                 NumberingCache nc = nr.Descendants<NumberingCache>().ElementAt(0);

                 NumericPoint np = nc.Descendants<NumericPoint>().ElementAt((int)intRow - 2);
                 NumericValue nv = np.Descendants<NumericValue>().ElementAt(0);
                 nv.Text = cellValue;
             }
             c_p.ChartSpace.Save();


         }
         catch
         {
             // Chart Element is not in a recognizable format. Most likely the defined Chart is incorrect. Ignore the chart creation.
             return;
         }
     }
     #endregion

     #region ModifyChartSimplified
     /// <summary>
     /// Updates the Cached Chart data in the Word document file
     /// </summary>
     /// <param name="cellColumn">Corresponds to the Column that needs to be modified</param>
     /// <param name="intRow">Corresponds to the Row that needs to be modified</param>
     /// <param name="cellValue">New value of the pointed cell</param>
     /// <param name="axisValue">Is the new value an Axis Value?</param>
     private void ModifyChartSimplified(string cellColumn, uint intRow, string cellValue, bool axisValue)
     {
         try
         {
             ChartPart c_p = this.mainDocPart.ChartParts.FirstOrDefault();
             BarChartSeries bs1 = c_p.ChartSpace.Descendants<BarChartSeries>().Where(s => string.Compare(s.InnerText, "Sheet1!$" + cellColumn + "$1", true) > 0).ElementAt(0);
             if (axisValue)
             {
                 NumericValue nv1 = bs1.Descendants<NumericValue>().ElementAt(0);
                 nv1.Text = cellValue;
             }

             else
             {
                 // 
                 DocumentFormat.OpenXml.Drawing.Charts.Values v1 = bs1.Descendants<DocumentFormat.OpenXml.Drawing.Charts.Values>().ElementAt(0);
                 NumericPoint np = v1.Descendants<NumericPoint>().ElementAt((int)intRow - 2);
                 NumericValue nv = np.Descendants<NumericValue>().ElementAt(0);
                 nv.Text = cellValue;
             }

         }
         catch
         {
             // Chart Element is not in a recognizable format. Most likely the defined Chart is incorrect. Ignore the chart creation.
             return;
         }
     }
     #endregion

     #endregion

microsoft-365-apps-publishing-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

0 Answers