Binding Charts with Database

[mzulfiqar]

Hi,

I want to create a chart with values coming through database. Right now from your examples it seems it can only be done through excel sheet and i have to populate excel sheet first then do the chart stuff.

-Majid

[Jane.Bai]

Hello Majid,

Thank you for your post.
There's no need to populate Excel first, and you could use the below sample code to bind chart with the data from a database.

Code: Select all

//initialize an instance of Presentation class
Presentation ppt = new Presentation();
RectangleF rect = new RectangleF(40, 100, 550, 320);
IChart chart = ppt.Slides[0].Shapes.AppendChart(ChartType.ColumnClustered, rect);

//clear the default dummy data
chart.ChartData.Clear(0, 0, 5, 5);
chart.Series.Clear();
chart.Categories.Clear();

using (OleDbDataAdapter da = new OleDbDataAdapter(oleDbCommand))
{
    DataTable dt = new DataTable();
    da.Fill(dt);
    InitChartData(chart, dt);
    ppt.SaveToFile("14355.pptx",FileFormat.Ppsx2010);
}

private void InitChartData(IChart chart, DataTable dataTable)
{
    //set series name
    ChartSeriesFormatCollection ctf = chart.Series;
    for (int c = 0; c < dataTable.Columns.Count; c++)
    {
        chart.ChartData[0, c].Text = dataTable.Columns[c].Caption;
        if (c > 0)
        {
            ctf.Append(chart.ChartData[0, c]);
        }
    }
    ChartCategoryCollection catg = chart.Categories;
    for (int r = 0; r < dataTable.Rows.Count; r++)
    {
        //set category
        catg.Append(chart.ChartData[r + 1, 0]);

        //set chartData value
        object[] data = dataTable.Rows[r].ItemArray;

        for (int c = 0; c < data.Length; c++)
        {
            chart.ChartData[r + 1, c].Value = data[c];
        }
        //set series value
        for (int i = 0; i < ctf.Count; i++)
        {
            chart.Series[i].Values.Add(chart.ChartData[r + 1, i + 1]);
        }
    }
}

Sincerely,
Jane
E-iceblue support team

[mzulfiqar]

Hi Jane,

Thanks for your reply, solves my problem.

-Majid

[Jane.Bai]

Hi Majid,

Thank you for your feedback.
Just feel free to contact us if you need any assistance.

Sincerely,
Jane
E-iceblue support team

[dbrewerton]

Greetings, I have to say I love your Presentation component and have been learning a great deal of how it works. However, I am having a problem. I tried using the code above and what happens is I clear the dummy chart data and try pulling the data in. When I run that clear dummy data part, the slide blows up. Here's the code related to that part of my application.

Code: Select all

#region Chart1
                RectangleF rec = new RectangleF(25, 40, 325, 225);
                IChart chart1 = ppt.Slides[0].Shapes.AppendChart(Spire.Presentation.Charts.ChartType.BarClustered, rec);
                chart1.ChartTitle.TextProperties.Text = V2.ToString();
                chart1.ChartTitle.TextProperties.IsCentered = false;
                chart1.ChartTitle.Height = 30;
                chart1.HasTitle = true;

                // clear the default dummy data
                chart1.ChartData.Clear(0, 0, 5, 5);
                chart1.Series.Clear();
                chart1.Categories.Clear();

                // Lines of Code by Location
                SqlCommand sqlcmdLOC = new SqlCommand(sqlQueryLOC, sqlconn);
                using (SqlDataAdapter da1 = new SqlDataAdapter(sqlcmdLOC))
                {
                    DataTable dt1 = new DataTable();
                    da1.Fill(dt1);
                    InitChartData(chart1, dt1);
                    sqlconn.Close();
                }
#endregion


InitChartData helper:

Code: Select all

#region InitChartData Helper
        private static void InitChartData(IChart chart1, DataTable dt1)
        {
            //set series name
            ChartSeriesFormatCollection ctf = chart1.Series;
            for (int c = 0; c < dt1.Columns.Count; c++)
            {
                chart1.ChartData[0, c].Text = dt1.Columns[c].Caption;
                if (c > 0)
                {
                    ctf.Append(chart1.ChartData[0, c]);
                }
            }
            ChartCategoryCollection catg = chart1.Categories;
            for (int r = 0; r < dt1.Rows.Count; r++)
            {
                //set category
                catg.Append(chart1.ChartData[r + 1, 0]);

                //set chartData value
                object[] data = dt1.Rows[r].ItemArray;

                for (int c = 0; c < data.Length; c++)
                {
                    chart1.ChartData[r + 1, c].Value = data[c];
                }
                //set series value
                for (int i = 0; i < ctf.Count; i++)
                {
                    chart1.Series[i].Values.Add(chart1.ChartData[r + 1, i + 1]);
                }
            }
        }
#endregion


[rachel.lei]

Hi Dan,

Thanks for your inquiry.
I tested the below code with the latest Spire.Presentation Pack Version:5.1, but didn't encounter any issue. And I also uploaded my output file for your reference. Sorry I'm a little confused about "When I run that clear dummy data part, the slide blows up" you said. What's your problem? To help us investigate your issue more accurately, please provide more detailed information about your issue, such as some screenshots and your desired output. Look forward to your resposonse.

Code: Select all

static void Main(string[] args)
        {
            Presentation presentation = new Presentation();

            RectangleF rect = new RectangleF(40, 100, 550, 320);
            IChart chart1 = presentation.Slides[0].Shapes.AppendChart(ChartType.BarClustered, rect);
            chart1.ChartTitle.TextProperties.Text = "sales";
            chart1.ChartTitle.TextProperties.IsCentered = true;
            chart1.ChartTitle.Height = 30;
            chart1.HasTitle = true;

            chart1.ChartData.Clear(0, 0, 5, 5);
            chart1.Series.Clear();
            chart1.Categories.Clear();

            //Create a datetable
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("Country", Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("Jun", Type.GetType("System.Int32")));
            dt.Columns.Add(new DataColumn("Aug", Type.GetType("System.Int32")));
            dt.Rows.Add("Cuba", "6000", "3200");
            dt.Rows.Add("Mexico", "8000", "2000");
            dt.Rows.Add("France", "9000", "4000");
            dt.Rows.Add("German", "8500", "2300");

            InitChartData(chart1, dt);
            presentation.SaveToFile("output.pptx", FileFormat.Pptx2013);

        }

        private static void InitChartData(IChart chart1, DataTable dt1)
        {
            //set series name
            ChartSeriesFormatCollection ctf = chart1.Series;
            for (int c = 0; c < dt1.Columns.Count; c++)
            {
                chart1.ChartData[0, c].Text = dt1.Columns[c].Caption;
                if (c > 0)
                {
                    ctf.Append(chart1.ChartData[0, c]);
                }
            }
            ChartCategoryCollection catg = chart1.Categories;
            for (int r = 0; r < dt1.Rows.Count; r++)
            {
                //set category
                catg.Append(chart1.ChartData[r + 1, 0]);

                //set chartData value
                object[] data = dt1.Rows[r].ItemArray;

                for (int c = 0; c < data.Length; c++)
                {
                    chart1.ChartData[r + 1, c].Value = data[c];
                }
                //set series value
                for (int i = 0; i < ctf.Count; i++)
                {
                    chart1.Series[i].Values.Add(chart1.ChartData[r + 1, i + 1]);
                }
            }
        }

Sincerely,
Rachel
E-iceblue support team

[dbrewerton]

The clearing of the dummy data came from Jane.Bai posting earlier in this thread. So, the only significant difference I see between my code and your example is the call to output the PowerPoint from inside that block that generates the powerpoint file. I'll give that a try and get back to you.

[dbrewerton]

Ok, I got myself further along. When I used the data set from the chart code that is working it rendered fine. So, I switched my data query to the one that should be used and the slide failed to render properly. This is the data that I have coming into the chart. Do you see anything wrong with it?

Team Name Project_Name Last_Scan_Date LOC RLS HighVuln MedVuln
Team1 Application1 03/14/2019 2795641 100 867 801
Team2 Application2 09/23/2019 1411731 100 603 1624
Team3 Application3 10/16/2018 242261 100 212 137
Team3 Application4 02/21/2018 14883 100 141 11
Team3 Application5 12/11/2018 125351 100 129 73

[dbrewerton]

Never mind, I figured out what the problem was. I was calling the wrong database view so the chart was overly complex. All good now. Thank you for tolerating my brain burp.

[rachel.lei]

Hi Dan,

Thanks for your feedback.
Glad to hear that your issue has been resolved. Just feel free to contact us if you need any assistance.
Wish you all the best!

Sincerely,
Rachel
E-iceblue support team