Spire.Presentation is a professional PowerPoint® compatible library that enables developers to create, read, write, modify, convert and Print PowerPoint documents. Get free and professional technical support for Spire.Presentation for .NET, Java, Android, C++, Python.

Thu Feb 17, 2022 3:04 pm

Hello,
When I have got four categories but I give only data for three of them. The output file has data in the fourth category that was not provided in the program.

Case 1:
01.PNG


Template Presentation File:

Source Code:

Code: Select all
using Spire.Presentation;
using Spire.Presentation.Charts;
using Spire.Presentation.Drawing;
using System;
using System.Collections.Generic;
using System.Linq;

namespace PptxTester
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load template presentation
            Presentation templatePresentation = new Presentation();
            templatePresentation.LoadFromFile("slides_as_template.pptx");

            //Create New Presentation
            Spire.Presentation.Presentation presentation = new Spire.Presentation.Presentation();
            presentation.Slides.RemoveAt(0);

            //Create Temporary Blank Presentation
            var blankPresentation = new Spire.Presentation.Presentation();

            //Get slide from template presentation. index:1
            ISlide cloneSlide = templatePresentation.Slides.ToArray().ElementAtOrDefault(1);

            //---
            //Get Chart                       
            IChart chart = cloneSlide.Shapes.ToArray().FirstOrDefault(x => x.Name == "Chart 14") as IChart;

            int seriesCount = 4;
            int categoryCount = 4;

            chart.ChartData[1, 0].Value = "January";
            chart.ChartData[2, 0].Value = "February";
            chart.ChartData[3, 0].Value = "March";
            chart.ChartData[4, 0].Value = "April";

            chart.ChartData[0, 1].Value = "Charts";
            chart.ChartData[0, 2].Value = "Tables";
            chart.ChartData[0, 3].Value = "Pictures";
            chart.ChartData[0, 4].Value = "Textboxes";

            chart.ChartData[1, 1].Value = 11;
            chart.ChartData[1, 2].Value = 15;
            chart.ChartData[1, 3].Value = 4;
            chart.ChartData[1, 4].Value = 7;           

            chart.ChartData[2, 1].Value = 25;
            chart.ChartData[2, 2].Value = 60;
            chart.ChartData[2, 3].Value = 77;
            chart.ChartData[2, 4].Value = 30;

            chart.ChartData[3, 1].Value = 40;
            chart.ChartData[3, 2].Value = 50;
            chart.ChartData[3, 3].Value = 65;
            chart.ChartData[3, 4].Value = 44;

            //***

            var startIndexAddSeries = chart.Series.Count + 1;
            for (int i = startIndexAddSeries; i <= 4; i++)
            {
                chart.Series.Append(chart.ChartData[0, i]);
            }
            if (chart.Series.Count > seriesCount)
            {
                for (int i = chart.Series.Count - 1; i >= seriesCount; i--)
                {
                    chart.Series.RemoveAt(i);
                }
            }
            chart.Categories.CategoryLabels = chart.ChartData["A2", "A" + (categoryCount + 1)];
            for (int i = 0; i < seriesCount; i++)
            {
                string letter = GetColumnName(i + 1);
                chart.Series[i].Values = chart.ChartData[letter + "2", letter + (categoryCount + 1)];
            }

            //Insert slide to the blank presentation
            blankPresentation.Slides.Append(cloneSlide);

            //Save and launch to view the PPTX document.
            blankPresentation.SaveToFile("clear-data-08.pptx", Spire.Presentation.FileFormat.Pptx2010);
        }

        public static string GetColumnName(int index)
        {
            const string letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

            var value = "";

            if (index >= letters.Length)
                value += letters[index / letters.Length - 1];

            value += letters[index % letters.Length];

            return value;
        }
    }
}


Case 2:
To fix the above problem which is described in case 1 I added this line of code to clear chart data. It works.

Code: Select all
chart.ChartData.Clear(0, 0, categoryCount + 1, seriesCount + 1);


But this line brokes the chart edit data functionality. We are starting to get a "The linked file isn't available" error from the PowerPoint application when we click on the edit data.

02.PNG


Source Code:

Code: Select all
using Spire.Presentation;
using Spire.Presentation.Charts;
using Spire.Presentation.Drawing;
using System;
using System.Collections.Generic;
using System.Linq;

namespace PptxTester
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load template presentation
            Presentation templatePresentation = new Presentation();
            templatePresentation.LoadFromFile("slides_as_template.pptx");

            //Create New Presentation
            Spire.Presentation.Presentation presentation = new Spire.Presentation.Presentation();
            presentation.Slides.RemoveAt(0);

            //Create Temporary Blank Presentation
            var blankPresentation = new Spire.Presentation.Presentation();

            //Get slide from template presentation. index:1
            ISlide cloneSlide = templatePresentation.Slides.ToArray().ElementAtOrDefault(1);

            //---
            //Get Chart                       
            IChart chart = cloneSlide.Shapes.ToArray().FirstOrDefault(x => x.Name == "Chart 14") as IChart;

            int seriesCount = 4;
            int categoryCount = 4;

            //This line brokes the chart edit data functionality. We get a "The linked file isn't available" error from the PowerPoint application when we click on the edit data.
            chart.ChartData.Clear(0, 0, categoryCount + 1, seriesCount + 1);

            chart.ChartData[1, 0].Value = "January";
            chart.ChartData[2, 0].Value = "February";
            chart.ChartData[3, 0].Value = "March";
            chart.ChartData[4, 0].Value = "April";

            chart.ChartData[0, 1].Value = "Charts";
            chart.ChartData[0, 2].Value = "Tables";
            chart.ChartData[0, 3].Value = "Pictures";
            chart.ChartData[0, 4].Value = "Textboxes";

            chart.ChartData[1, 1].Value = 11;
            chart.ChartData[1, 2].Value = 15;
            chart.ChartData[1, 3].Value = 4;
            chart.ChartData[1, 4].Value = 7;           

            chart.ChartData[2, 1].Value = 25;
            chart.ChartData[2, 2].Value = 60;
            chart.ChartData[2, 3].Value = 77;
            chart.ChartData[2, 4].Value = 30;

            chart.ChartData[3, 1].Value = 40;
            chart.ChartData[3, 2].Value = 50;
            chart.ChartData[3, 3].Value = 65;
            chart.ChartData[3, 4].Value = 44;

            //***

            var startIndexAddSeries = chart.Series.Count + 1;
            for (int i = startIndexAddSeries; i <= 4; i++)
            {
                chart.Series.Append(chart.ChartData[0, i]);
            }
            if (chart.Series.Count > seriesCount)
            {
                for (int i = chart.Series.Count - 1; i >= seriesCount; i--)
                {
                    chart.Series.RemoveAt(i);
                }
            }
            chart.Categories.CategoryLabels = chart.ChartData["A2", "A" + (categoryCount + 1)];
            for (int i = 0; i < seriesCount; i++)
            {
                string letter = GetColumnName(i + 1);
                chart.Series[i].Values = chart.ChartData[letter + "2", letter + (categoryCount + 1)];
            }

            //Insert slide to the blank presentation
            blankPresentation.Slides.Append(cloneSlide);

            //Save and launch to view the PPTX document.
            blankPresentation.SaveToFile("clear-data-08.pptx", Spire.Presentation.FileFormat.Pptx2010);
        }

        public static string GetColumnName(int index)
        {
            const string letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

            var value = "";

            if (index >= letters.Length)
                value += letters[index / letters.Length - 1];

            value += letters[index % letters.Length];

            return value;
        }
    }
}


Also if I use these code to clear chart data I am still getting a "The linked file isn't available" error from the PowerPoint application when we click on the edit data.

Code: Select all
            for (int i = 0; i <= seriesCount; i++)
            {
                for (int j = 0; j <= categoryCount; j++)
                {
                     chart.ChartData[i, j].Value = string.Empty;
                }
            }


How can I get the chart correctly without losing edit data functionality on the PowerPoint?
Thanks
Last edited by omurertanis on Mon Feb 21, 2022 11:49 am, edited 1 time in total.

omurertanis
 
Posts: 22
Joined: Tue Mar 30, 2021 7:56 pm

Fri Feb 18, 2022 5:37 am

Hello,

Thank you for your inquiry.
1) The fourth category of the result file you mentioned has data because the result file automatically references the data of the fourth category in the source file. This is because you only modified the data for the first three categories, but did not delete the data for the fourth category.
2) After investigation, we found that when clearing the original data of the chart, the data type of the cell was also cleared, which caused our product to recognize the object type when recognizing the category data, but the correct type should be the string type. Please use the modified code below.
Code: Select all
...
int seriesCount = 4;
 int categoryCount = 4;

 chart.ChartData.Clear(0, 0, categoryCount + 1, seriesCount +1);

 chart.ChartData[1, 0].Text = "January";
 chart.ChartData[2, 0].Text = "February";
 chart.ChartData[3, 0].Text = "March";
 chart.ChartData[4, 0].Text = "April";

 chart.ChartData[0, 1].Text = "Charts";
 chart.ChartData[0, 2].Text = "Tables";
 chart.ChartData[0, 3].Text = "Pictures";
 chart.ChartData[0, 4].Text = "Textboxes";

 chart.ChartData[1, 1].NumberValue = 11;
 chart.ChartData[1, 2].NumberValue = 15;
 chart.ChartData[1, 3].NumberValue = 4;
 chart.ChartData[1, 4].NumberValue = 7;

 chart.ChartData[2, 1].NumberValue = 25;
 chart.ChartData[2, 2].NumberValue = 60;
 chart.ChartData[2, 3].NumberValue = 77;
 chart.ChartData[2, 4].NumberValue = 30;

 chart.ChartData[3, 1].NumberValue = 40;
 chart.ChartData[3, 2].NumberValue = 50;
 chart.ChartData[3, 3].NumberValue = 65;
 chart.ChartData[3, 4].NumberValue = 44;

 //***

 var startIndexAddSeries = chart.Series.Count + 1;
...

Alternatively, the code to clear the data can also be modified to:
Code: Select all
chart.ChartData.Clear(1, 1, categoryCount + 1, seriesCount +1);

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Fri Feb 18, 2022 1:20 pm

Hello,
This line
Code: Select all
chart.ChartData.Clear(1, 1, categoryCount + 1, seriesCount + 1);
fixed my problem with my first template Pptx file and data. But I am still getting that "The linked file isn't available" error from the PowerPoint application for this template file.
I shared the template file and sample code.

Can you check it again for this situation?

01.jpg


Template File:

Source Code:

Code: Select all
using Spire.Presentation;
using Spire.Presentation.Charts;
using Spire.Presentation.Drawing;
using System;
using System.Collections.Generic;
using System.Linq;

namespace PptxTester
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load template presentation
            Presentation templatePresentation = new Presentation();
            templatePresentation.LoadFromFile("template.pptx");

            //Create New Presentation
            Spire.Presentation.Presentation presentation = new Spire.Presentation.Presentation();
            presentation.Slides.RemoveAt(0);

            //Create Temporary Blank Presentation
            var blankPresentation = new Spire.Presentation.Presentation();

            //Get slide from template presentation. index:1
            ISlide cloneSlide = templatePresentation.Slides.ToArray().ElementAtOrDefault(2);

            //---
            //Get Chart                       
            IChart chart = cloneSlide.Shapes.ToArray().FirstOrDefault(x => x.Name == "Content Placeholder 15") as IChart;

            int seriesCount = 1;
            int categoryCount = 9;

            //This line brokes the chart edit data functionality. We get "The linked file isn't available" error from powerpoint application when we click to the edit data.
            //chart.ChartData.Clear(0, 0, categoryCount + 1, seriesCount + 1);
            chart.ChartData.Clear(1, 1, categoryCount + 1, seriesCount + 1);

            chart.ChartData[1, 0].Value = "Sausage (Net)";
            chart.ChartData[2, 0].Value = "Breakfast Sausage";
            chart.ChartData[3, 0].Value = "Fresh Sausage (Subnet)";
            chart.ChartData[4, 0].Value = "Fully-Cooked";
            chart.ChartData[5, 0].Value = "Sausage (Subnet)";
            chart.ChartData[6, 0].Value = "Summer sausage";
            chart.ChartData[7, 0].Value = "Fresh, uncooked ground beef";
            chart.ChartData[8, 0].Value = "Meat snacks";
            chart.ChartData[9, 0].Value = "Refrigerated snacks that include meat";

            chart.ChartData[0, 1].Value = "Costco";

            chart.ChartData[1, 1].Value = 0.69;
            chart.ChartData[2, 1].Value = 0.69;
            chart.ChartData[3, 1].Value = 0.79;
            chart.ChartData[4, 1].Value = 0.33;
            chart.ChartData[5, 1].Value = 0.33;
            chart.ChartData[6, 1].Value = 0.33;
            chart.ChartData[7, 1].Value = 0.33;
            chart.ChartData[8, 1].Value = 0.33;
            chart.ChartData[9, 1].Value = 0.33;


            //***

            var startIndexAddSeries = chart.Series.Count + 1;
            for (int i = startIndexAddSeries; i <= 4; i++)
            {
                chart.Series.Append(chart.ChartData[0, i]);
            }
            if (chart.Series.Count > seriesCount)
            {
                for (int i = chart.Series.Count - 1; i >= seriesCount; i--)
                {
                    chart.Series.RemoveAt(i);
                }
            }
            chart.Categories.CategoryLabels = chart.ChartData["A2", "A" + (categoryCount + 1)];
            for (int i = 0; i < seriesCount; i++)
            {
                string letter = GetColumnName(i + 1);
                chart.Series[i].Values = chart.ChartData[letter + "2", letter + (categoryCount + 1)];
            }

            //Insert slide to the blank presentation
            blankPresentation.Slides.Append(cloneSlide);

            //Save and launch to view the PPTX document.
            blankPresentation.SaveToFile("clear-data-02.pptx", Spire.Presentation.FileFormat.Pptx2010);
        }

        public static string GetColumnName(int index)
        {
            const string letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

            var value = "";

            if (index >= letters.Length)
                value += letters[index / letters.Length - 1];

            value += letters[index % letters.Length];

            return value;
        }
    }
}
Last edited by omurertanis on Mon Feb 21, 2022 11:49 am, edited 1 time in total.

omurertanis
 
Posts: 22
Joined: Tue Mar 30, 2021 7:56 pm

Mon Feb 21, 2022 1:48 am

Hello,

Thanks for your feedback.
Please use the following modified code to solve the issue you mentioned.
Code: Select all
 static void Main(string[] args)
 {
     //Load template presentation
     Presentation templatePresentation = new Presentation();
     templatePresentation.LoadFromFile("template.pptx");

     //Create New Presentation
     Spire.Presentation.Presentation presentation = new Spire.Presentation.Presentation();
     presentation.Slides.RemoveAt(0);

     //Create Temporary Blank Presentation
     var blankPresentation = new Spire.Presentation.Presentation();

     //Get slide from template presentation. index:1
     ISlide cloneSlide = templatePresentation.Slides.ToArray().ElementAtOrDefault(2);

     //---
     //Get Chart                       
     IChart chart = cloneSlide.Shapes.ToArray().FirstOrDefault(x => x.Name == "Content Placeholder 15") as IChart;

     int seriesCount = 1;
     int categoryCount = 9;

     chart.ChartData.Clear(0, 0, categoryCount + 1, seriesCount + 1);

     chart.ChartData[1, 0].Text = "Sausage (Net)";
     chart.ChartData[2, 0].Text = "Breakfast Sausage";
     chart.ChartData[3, 0].Text = "Fresh Sausage (Subnet)";
     chart.ChartData[4, 0].Text = "Fully-Cooked";
     chart.ChartData[5, 0].Text = "Sausage (Subnet)";
     chart.ChartData[6, 0].Text = "Summer sausage";
     chart.ChartData[7, 0].Text = "Fresh, uncooked ground beef";
     chart.ChartData[8, 0].Text = "Meat snacks";
     chart.ChartData[9, 0].Text = "Refrigerated snacks that include meat";

     chart.ChartData[0, 1].Text = "Costco";

     chart.ChartData[1, 1].NumberValue = 0.69;
     chart.ChartData[2, 1].NumberValue = 0.69;
     chart.ChartData[3, 1].NumberValue = 0.79;
     chart.ChartData[4, 1].NumberValue = 0.33;
     chart.ChartData[5, 1].NumberValue = 0.33;
     chart.ChartData[6, 1].NumberValue = 0.33;
     chart.ChartData[7, 1].NumberValue = 0.33;
     chart.ChartData[8, 1].NumberValue = 0.33;
     chart.ChartData[9, 1].NumberValue = 0.33;


     //***

     var startIndexAddSeries = chart.Series.Count + 1;
     for (int i = startIndexAddSeries; i <= 4; i++)
     {
         chart.Series.Append(chart.ChartData[0, i]);
     }
     if (chart.Series.Count > seriesCount)
     {
         for (int i = chart.Series.Count - 1; i >= seriesCount; i--)
         {
             chart.Series.RemoveAt(i);
         }
     }
     chart.Categories.CategoryLabels = chart.ChartData["A2", "A" + (categoryCount + 1)];
     for (int i = 0; i < seriesCount; i++)
     {
         string letter = GetColumnName(i + 1);
         chart.Series[i].Values = chart.ChartData[letter + "2", letter + (categoryCount + 1)];
     }

     //Insert slide to the blank presentation
     blankPresentation.Slides.Append(cloneSlide);

     //Save and launch to view the PPTX document.
     blankPresentation.SaveToFile("clear-data-02.pptx", FileFormat.Pptx2010);
 }

 public static string GetColumnName(int index)
 {
     const string letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

     var value = "";

     if (index >= letters.Length)
         value += letters[index / letters.Length - 1];

     value += letters[index % letters.Length];

     return value;
 }

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Mon Feb 21, 2022 11:50 am

It works. Thank you.

omurertanis
 
Posts: 22
Joined: Tue Mar 30, 2021 7:56 pm

Tue Feb 22, 2022 1:11 am

Hello,

Glad to hear that.
If you encounter other issues related to our products in the future, please feel free to contact us.
Wish you all the best!

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Return to Spire.Presentation