Creating BarCharts and Pie Charts in PPTX file for data from SQL Server table

devroni · November 15, 2016, 9:13am

Hi,

I am new to ASPOSE slides.

I have a requirement to create complex bar charts and Pie charts and export it to a PPTX file so that I can use those graphs for my presentation during meetings.

So to do this, I am doing a POC by taking a small data table like EMP table as below.

EmpID EmpName Salary

===== ======== ====

1 A 10000

2 B 20000

3 C 30000

4 D 40000

5 E 50000

6 F 60000

Now using this data, I want to create a Bar chart and also a Pie Chart using ASPOSE Slides so that I can export it to a PPTX file. I need to create this using ASP.Net in visual studio. What I need to create, a screenshot of it is as attached here.

Hence, requesting you to share some sample codes so that I can complete this POC as soon as possible.

I have one more question :

using Aspose.Slides;

<pre class=“code-java” style=“padding: 0px; margin-top: 0px; margin-bottom: 0px; overflow: auto; font-family: “Courier New”, Courier, monospace; line-height: 1.3; color: rgb(51, 51, 51); font-size: 13.2367px; font-weight: bold; background-color: rgb(255, 255, 255);”>//Instantiate PresentationEx class that represents PPTX file
using(PresentationEx pres = new PresentationEx())
{
//Access first slide
SlideEx sld = pres.Slides[0];<pre class=“code-java” style=“padding: 0px; margin-top: 0px; margin-bottom: 0px; overflow: auto; font-family: “Courier New”, Courier, monospace; line-height: 1.3; color: rgb(51, 51, 51); font-size: 13.2367px; font-weight: bold; background-color: rgb(255, 255, 255);”>}

I am using : Aspose.Slides.dll version 2.7.0.0. It’s Product Version is : 2007.09.19

But for me it’s showing an error as : “The type or namespace <span style=“background-color: rgb(255, 255, 255); color: rgb(51, 51, 51); font-family: “Courier New”, Courier, monospace; font-size: 13.2367px; font-weight: bold;”>PresentationEx could not be found. Are you missing a using directive or an assembly reference ?”

I need to create PPTX file, not a PPT.

So am I using here a lower version of ASPOSE DLL ? If I am using a lower version, then please send me a link to download the latest version where it supports pptx file generation.

Regards,

Deb

Bangalore, India

+91-9740283783

Adnan.Ahmad · November 15, 2016, 2:25pm

Hi,

I have observed your requirements. I request you to please follow guidelines on this link to achieve your requirements. I hope you will find it helpful. For downloading new version of Aspose.Slides please visit this link or contact our sales team in case of license purchasing query.

Best Regards,

devroni · November 18, 2016, 4:47am

Thanks Adnan Ahmad for your very quick reply. Your suggestions helped.

Now

Presentation presentation = new Presentation();

ISlide slide = presentation.Slides[0];

IChart chart = slide.Shapes.AddChart(ChartType.ClusteredColumn, 0, 0, 500, 500);

-------

--------

presentation.Save(dataDir + “\” + “MyChart.pptx”, Aspose.Slides.Export.SaveFormat.Pptx);

As shown above, I am able to save a PPTX file to a folder.

Please help me, instead of saving to a folder, how can I give a download option so that the web user can download the PPTX file and save it to his client system.

Regards,

Deb

Adnan.Ahmad · November 18, 2016, 1:02pm

Hi,

I have observed your comments. You can try to save the file to a stream, and then load this stream to Google docs or save file on local PC load to Google docs, then delete this file. Please visit this link . I hope you will find it helpful.

Best Regards,

devroni · November 22, 2016, 3:42am

Thanks Adnan Ahmad,

I tried how you have suggested and that’s worked. Really great.

Now,

In my aspose slide, in X axis(horizontal) I have EmpNo and in Y axis(vertical) I have LoginTime. Login time I have in database as : 13:36:49 like this values which means 01:36:49 PM.

So In Y axis if I am showing this time then it starts from 00:00:00 to 23:59:59 and for the above example. So the vertical bar chart graph will be in between 1PM to 2PM in between in proper proportion.

Please advice how can I achieve this.

Also I need a RED colour horizontal bar within the graph showing 1PM or 13:00:00 as ideal login hour for everyday. So horizontal bar in red colour and then all employee IDs in horizontally where if employee has login before 1PM then his bar chart will be below this RED colour horizontal bar and if any employee logged in after 1PM then his barchart above RED colour horizontal bar.

Please advice how to achieve this as well.

Thanks a ton.

Regards,

Deb

Mobile : +91-9740-283-783

Adnan.Ahmad · November 22, 2016, 11:33am

Hi,

I have observed your comments. I request you to please share source code and sample project so that i can help you out in this issue.

Best Regards,

devroni · November 23, 2016, 3:09am

Hi Adnan Ahmad,

Please find my sample code attached. BIN folder is there but I have deleted the DLLs(Aspose.Slides.dll version 16.10.0.0 & Aspose.Cells.dll version 9.0.0.0). You can add these 2 DLLs again before executing this code. Database MDF and LDF file also in APP_DATA folder.

Also a screenshot of how I want my graph also attached.

In my graph, I have 3 portions.

a) Upper portion shows : Bar Chart(date in x axis & that day’s task completion time in Y axix.

b) Lower left portion ; a static text area with which contains some notes.

c) Lower right portion : a PIE chart that shows how many tasks in that month completed with-in the “ExpectedCompletedTime” for that corresponding date and how many crossed that “ExpectedCompletedTime” for that corresponding date.

Regards,

Deb

+91-9740-283-783

devroni · November 23, 2016, 3:18am

Hi Adnan Ahmad,

Please find my sample code attached. BIN folder is there but I have deleted the DLLs(Aspose.Slides.dll version 16.10.0.0 & Aspose.Cells.dll version 9.0.0.0). You can add these 2 DLLs again before executing this code. Database MDF and LDF file also in APP_DATA folder.

Also a screenshot of how I want my graph also attached.

In my graph, I have 3 portions.

a) Upper portion shows : Bar Chart(date in x axis & that day’s task completion time in Y axix.

b) Lower left portion ; a static text area with which contains some notes.

c) Lower right portion : a PIE chart that shows how many tasks in that month completed with-in the “ExpectedCompletedTime” for that corresponding date and how many crossed that “ExpectedCompletedTime” for that corresponding date.

Regards,

Deb

+91-9740-283-783

Adnan.Ahmad · November 24, 2016, 5:39am

Hi,

I have observed your comments. We will get back to you with feedback soon.

Best Regards,

devroni · November 24, 2016, 10:20am

Hi Adnan Ahmad,

Also, :

a) how can I change the font size of the X and Y axis text. I want to use smaller fonts.

b) I am using LayoutSlide.Name == “Row and Two Columns”. So how can I put 1 Barchat on top box section, 1 text field in below left box section and a PIE chart in the below right box section.

My requirement image that I have uploaded is showing how these 3 box sections are placed in one slide.

Please get me in achieving this little quickly

Regards,

Deb

+91-9740-283-783

devroni · November 25, 2016, 5:22am

Hi Adnan Ahmad,

Any suggestions on my query please.

Regards

Deb

devroni · November 25, 2016, 11:18am

Hi Adnan Ahmad,

I am proving a screenshot of what I still not able to do. Also the webpage(Default.aspx, Default.aspx.cs). In the screenshot, I have asked 3 questions.

Please help me in getting these 3 things done on priority. Thanks for your time.

Regards

Deb

Adnan.Ahmad · November 25, 2016, 3:18pm

Hi,

I have discussed your issue with our product team in details and got feedback from them. First of all you should be able to get desired result in PowerPoint. If you are able to do that in PowerPoint can you please share that with so that we can further investigate issue in details.

Best Regards,

devroni · November 28, 2016, 6:13am

Hi Adnan Ahmad,

The sample PPTX file is as attached. Also my Asp.Net web-application’s webpage(Default.aspx, Default.aspx.cs) as attached.

As you can see in the PPTX file, the graph is getting designed as required.

Hence my request is : Please help me enhancing the attached code to generate a PPTX file similarly to the attached PPTX file.

Regards,

Deb

+91-9740-283-783

Adnan.Ahmad · November 29, 2016, 9:49am

Hi,

I have worked with the source code shared by you using Aspose.Slides for .NET 16.10.0 and have been able to reproduce the issue. A ticket with ID SLIDESNET-38130 has been created in our issue tracking system to investigate and resolve the issue. This thread has been associated with the ticket so that we may share the notification with you once issue will be fixed.

We are sorry for your inconvenience,

devroni · December 1, 2016, 10:04am

Hi Adnan Ahmad,

Thanks for your response. Have you raised this ticket for : showing AM\PM values in chart axis ?

My requirement is as below :

From database I am getting time values as : 00:25, 06:45, 10:47, 13:05, 18:36, 22:23, 23:59 and these values I want to show as bar graph in a PPTX file using ASPOSE SLIDES dll 16.10.0.0

Please let me know :

How can I create AM\PM as MajorUnit Labels in Y axis. I mean : 06:00 AM, 11:00 AM, 12:00 PM, 05:00 PM, 11:00 PM etc, etc in Y axis.

Waiting to hear from you soon on this.

Regards,

Deb

+91-9740-283-783

Adnan.Ahmad · December 1, 2016, 1:43pm

Hi Deb,

I have shared your requirements with our product team. I request for your patience until issue gets resolved.

We are sorry for your inconvenience,

devroni · December 2, 2016, 3:28am

Ok Sure Adnan,

Please let me know once this gets fixed.

Regards,

Deb

devroni · December 19, 2016, 4:37am

Hi Adnan Ahmad,

Please let me know when I can expect a fix for this issue ?

Waiting to hear from you soon on this.

Regards,

Deb

+91-9740-283-783

Adnan.Ahmad · December 19, 2016, 12:48pm

Hi Deb,

Our product team investigated issue in details.

For your requirements “I want max till 24 hours and also the Y axis text should come as 0Hr, 5hr, 10Hr…24hr. I want to append Hr to Y axis.” To setup this format in slides use:

chart.Axes.VerticalAxis.IsNumberFormatLinkedToSource = false;

chart.Axes.VerticalAxis.NumberFormat = “[$-409]h:mm AM/PM;@”;

As we can see in Report.pptx time values stored as double value between 0 and 1 where 1 means 24 hours.

To achieve the same result you should use following code snippet:

for (int i = 0; i < dt.Rows.Count; i++)
{
double actualCompletedTime = 0;

if (!string.IsNullOrEmpty(dt.Rows[i]["ActualCompletedTime"].ToString().Trim()))
{
string[] splitTime = dt.Rows[i]["ActualCompletedTime"].ToString().Trim().Split(':');

TimeSpan time = new TimeSpan(Convert.ToInt32(splitTime[0]), Convert.ToInt32(splitTime[1]), Convert.ToInt32(splitTime[2]));
actualCompletedTime = Convert.ToDouble(time.TotalDays); //save time as double value to be able to apply number format below
}

series.DataPoints.AddDataPointForBarSeries(fact.GetCell(defaultWorksheetIndex, i + 1, 1, actualCompletedTime));
}

To setup fixed max value on axis use:

chart.Axes.VerticalAxis.IsAutomaticMaxValue = false;
chart.Axes.VerticalAxis.MaxValue = 24;

For the blue bars are not showing for all dates. Only alternative showing.

I want for all dates Bars should shown."

"There is no data in data base for the respective categories that's why they are empty.

See attached image "empty bars.png"

Best Regards,