adding multiple charts to presentation using multiple worksheets to get data

[Miranda Johnson]

I am trying to pull chart data from a spreadsheet that is not the 0 index sheet in the workbook.   

No matter if I ask for it by index or by name, if it is not the very first spreadsheet in the workbook it will not create a chart

I also mentioned it in this post, item #3 of 4.   https://www.syncfusion.com/forums/132529/3-different-items-text-above-table-in-slide-font-size-of-text-in-table-cell-adding-chart-from    However, I am not getting any response on my inquiry

Attachment: PowerpointController_c828bbe9.zip

[Kumaravel Kulandai Samy]

  

Hi Miranda, 

Please find the details below, 

My next issue is in creating the 2nd chart.   This can have up to 18 different charts on it but I have not gotten the 2nd one to work yet.  I believe I am referencing the correct worksheet (name Q4 index 3)   I get no errors at build and none when I run, but when I try to open it says PowerPoint was unable to display some of the text, images or objects because they have become corrupted.

We have checked this. But we cannot reproduce the issue at our end. We have attached the sample that we tried to reproduce the issue at our end.    Sample: com/downloads  http://www.syncfusion./support/directtrac/general/ze/Sample363496840.zip    Screenshot of the generated document

  

Please let us know if you need any further assistance in this. 

Regards, 

Kumaravel.K 

[Miranda Johnson]

Did you try it with my code?

Your code has you inputting information at design time.  Nothing I am writing can be done at design time.   It must be determined at run time as it is always dynamic data and as athe programmer I will not know how many rows there are.   

valueaxis.Add(destworksheet.Range["B2"].DisplayText);

            valueaxis.Add(destworksheet.Range["B3"].DisplayText);

            valueaxis.Add(destworksheet.Range["B4"].DisplayText);

            valueaxis.Add(destworksheet.Range["B5"].DisplayText);

            valueaxis.Add(destworksheet.Range["B6"].DisplayText);

            categoryaxis.Add(destworksheet.Range["A2"].DisplayText);

            categoryaxis.Add(destworksheet.Range["A3"].DisplayText);

            categoryaxis.Add(destworksheet.Range["A4"].DisplayText);

            categoryaxis.Add(destworksheet.Range["A5"].DisplayText);

            categoryaxis.Add(destworksheet.Range["A6"].DisplayText);

            //Creates Powerpoint Presentation

            IPresentation presentation = Presentation.Create();

            ISlide slide = presentation.Slides.Add(SlideLayoutType.Blank);          

            IPresentationChart chart = slide.Charts.AddChart(100, 10, 700, 500);

            //Sets chart Title

            chart.ChartTitle = "Chart Title";   

            // Sets chart type      

            chart.ChartType = OfficeChartType.Bar_Clustered; 

            //Sets chart data range value        

            chart.ChartData.SetValue(2, 1, categoryaxis[0]);

            chart.ChartData.SetValue(3, 1, categoryaxis[1]);

            chart.ChartData.SetValue(4, 1, categoryaxis[2]);

            chart.ChartData.SetValue(5, 1, categoryaxis[3]);

            chart.ChartData.SetValue(6, 1, categoryaxis[4]);

            chart.ChartData.SetValue(2, 2,Convert.ToDouble(valueaxis[0]));

            chart.ChartData.SetValue(3, 2, Convert.ToDouble(valueaxis[1]));

            chart.ChartData.SetValue(4, 2,Convert.ToDouble( valueaxis[2]));

            chart.ChartData.SetValue(5, 2,Convert.ToDouble( valueaxis[3]));

            chart.ChartData.SetValue(6, 2, Convert.ToDouble(valueaxis[4]));

Try it with dynamic data and see if you can duplicate it.  

// Add a chart

Stream stream = new FileStream(file, FileMode.Open);

stream.Position = 0;

//Gets the 1st worksheet

IWorksheet worksheet0 = destworkbook.Worksheets[0];

///this however, will not work

//Gets the 4th worksheet

//IWorksheet worksheet0 = destworkbook.Worksheets[3];

//neither will this

//IWorksheet worksheet0 = destworkbook.Worksheets["Q4"];

worksheet0.UsedRangeIncludesFormatting = false;

//Get The range of values

var data1 = worksheet0.UsedRange;

//create new slide showing chart

ISlide chart1slide = presentation.Slides.Add(SlideLayoutType.Blank);

IPresentationChart chart1 = chart1slide.Charts.AddChart(stream, 1, data1.Address, new Rectangle(30, 15, 850, 227)); 

chart1.ChartType = Syncfusion.OfficeChart.OfficeChartType.Bar_Clustered;

chart1.HasLegend = false;

chart1.ChartTitle = "Average Answer";

stream.Dispose();

[Kumaravel Kulandai Samy]

Hi Miranda, 

Please find the details as below. 

It must be determined at run time as it is always dynamic data and as the programmer I will not know how many rows there are. 

  

We can achieve this by using the UsedRange property as below. We have attached the sample below. 

 

destworksheet.UsedRangeIncludesFormatting = false; 

var value = destworksheet.UsedRange;       

 

In addition, please find that the UsedRange property takes the whole range in the worksheet 

 

For example: 

 

If you have data like above in your worksheet, then the range in the UsedRange will be considered as B2:E5. This will be a limitation in using this API. Please let us know whether this suits your requirement. 

As per the Microsoft Office behavior, if we have string type data other than in first row or first column in a worksheet, then the chart will not be displayed. 

  

For the worksheet “Q10” in the provided excel document you are not getting the chart because of the string content in column index 1 (titled with “Response text”) 

We have created the sample using the UsedRange property for the worksheet “Q4” but we cannot reproduce the issue in our end. 

Sample: 

http://www.syncfusion.com/downloads/support/directtrac/general/ze/Sample3-1122864138.zip 

Output document screenshot: 

 

 

In addition, we have created the sample for creating a chart in PowerPoint presentation for the data in database. In this sample,  

1.       We have used an XML file as a data base  

2.       We have exported the data from XML file to a DataTable  

3.       Populated the data in DataTable to an excel file  

4.       We have created the chart from that excel file.  

Sample: 

http://www.syncfusion.com/downloads/support/directtrac/general/ze/Sample4313512469.zip 

Please let us know if you need any further assistance in this. 

Regards, 

Kumaravel 

[Miranda Johnson]

You are not duplicating because you are not doing it the same way I did.   

In your first response you knew the range of values beforehand  and coded that in at design time.   If you had replaced this using the dynamic method , var value = destworksheet.UsedRange, then you may have duplicated.     

In your second example where you do use that method you are creating a single worksheet.  Try to create multiple worksheets in the workbook and then use an index other than 0 to create the chart

I came up with a solution but it should not be required.  I am deleting the worksheets after I use them to create each chart.  This way it is always reading from the 0 index for the next chart

[Kumaravel Kulandai Samy]

Hi Miranda, 

We were able to reproduce the issue at our end. A support incident to track the status of this defect has been created under your account. Please log on to our support website to check for further updates  

https://www.syncfusion.com/account/login?ReturnUrl=%2fsupport%2fdirecttrac%2fincidents                                

Please let me know if you have any questions. 

Regards, 

Kumaravel