Free Support Forum - aspose.com

Aspose Chart question

Hello, We are looking in to replacing existing chartcontrol we have with Aspose Chart.

We need to create the chart from a datatable. Please see code below. Please let us know how below can be done using aspose components, thsi chartcontrol will be on the asp.net web page with an export to excel button on it.

We will also need output this chart to excel using aspose components. Basically output chart to excel with data populating from dataset. Please advise.

Thx

Dim ds As DataSet = CreateDataSet()

If ds.Tables(0).Rows.Count > 0 Then

Dim chart As LineChart = New LineChart

chart.Line.Color = Color.Empty

chart.Fill.Color = Color.Green

chart.LineMarker = New DiamondLineMarker(8, Color.Green, Color.Green)

chart.Legend = "Test A"

Dim chart1 As ColumnChart = New ColumnChart

chart1.MaxColumnWidth = 10

chart1.Fill.Color = Color.FromArgb(50, Color.Navy)

chart1.Shadow.Visible = True

chart1.Legend = "Test B"

For Each row As DataRow In ds.Tables(0).Rows

If Not row("Test A") Is System.DBNull.Value Then

chart.Data.Add(New ChartPoint(row("dt").ToString, CDec(row("TestA"))))

End If

If Not row("Test B") Is System.DBNull.Value Then

chart1.Data.Add(New ChartPoint(row("dt").ToString, CDec(row("Test B"))))

End If

Next

'chartControl1.Width = ((ds.Tables(0).Rows.Count) * 35)

chartControl1.Charts.Add(chart)

chartControl1.Charts.Add(chart1)

ChartControl1.RedrawChart()

End If

Hi,

Thanks for your inquiry.

Do you want to create native excel charts or you want sophisticated charts. If you want to create sophisticated charts, you may use our other component i.e…, Aspose.Chart in Aspose.Report family. Using it, you may create charts and take/save the image of the chart too (you may contact Aspose.Report team in their forum). But if you want native excel charts (editable excel charts) in excel format, you may use Aspose.Cells for .NET component for your need. The component supports almost all the standard and custom charts that MS Excel supports. The component also supports to directly export/import datatable/dataview/datareader or array to/from excel worksheet. To export a datatable to excel sheet, you may use Cells.ImportDataTable() method, see the document for your reference:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/importing-data-to-worksheets.html

I think there is simple steps involved for your case. You can export a datatable to worksheet to fill the cells as a source for the chart. Then, you may create the chart using Aspose.Cells for .NET chart APIs.

I have created a sample line chart that used some static data in the cells of a worksheet as its data source range.

Sample code:

Dim workbook As New Workbook()
'Set default font
Dim style As Style = workbook.DefaultStyle
style.Font.Name = “Tahoma”
workbook.DefaultStyle = style
Dim cells As Cells = workbook.Worksheets(0).Cells
'Put sample values/data into cells, you may import a whole datatable
'to cells using Cells.ImportDataTable() method.
cells(“A1”).PutValue(“Region”)
cells(“A2”).PutValue(“France”)
cells(“A3”).PutValue(“Germany”)
cells(“A4”).PutValue(“England”)

cells(“B1”).PutValue(2002)
cells(“C1”).PutValue(2003)
cells(“D1”).PutValue(2004)
cells(“E1”).PutValue(2005)
cells(“F1”).PutValue(2006)

cells(“B2”).PutValue(40000)
cells(“C2”).PutValue(45000)
cells(“D2”).PutValue(50000)
cells(“E2”).PutValue(55000)
cells(“F2”).PutValue(70000)

cells(“B3”).PutValue(10000)
cells(“C3”).PutValue(25000)
cells(“D3”).PutValue(40000)
cells(“E3”).PutValue(55000)
cells(“F3”).PutValue(80000)

cells(“B4”).PutValue(65000)
cells(“C4”).PutValue(50000)
cells(“D4”).PutValue(35000)
cells(“E4”).PutValue(30000)
cells(“F4”).PutValue(20000)

Dim sheet As Worksheet = workbook.Worksheets(0)
'Set the name of worksheet
sheet.Name = “Line”
sheet.IsGridlinesVisible = False
'Create chart
Dim chartIndex As Integer = 0
chartIndex = sheet.Charts.Add(ChartType.Line, 5, 1, 29, 10)
Dim chart As Chart = sheet.Charts(chartIndex)
chart.MajorGridLines.IsVisible = False
'Set Properties of chart title
Dim title As Title = chart.Title
title.Text = “Sales By Region For Years”
title.TextFont.Color = Color.Black
title.TextFont.IsBold = True
title.TextFont.Size = 12
title.Rotation = 14
title.TextHorizontalAlignment = TextAlignmentType.Center
title.TextVerticalAlignment = TextAlignmentType.Center
'Set Properties of nseries
Dim nseries As NSeries = chart.NSeries
nseries.Add(“B2:F4”, False)
nseries.CategoryData = “B1:F1”
nseries.IsColorVaried = True
cells = workbook.Worksheets(0).Cells
Dim aseries As ASeries
For i As Integer = 0 To nseries.Count - 1
aseries = nseries(i)
aseries.Name = cells(i + 1, 0).Value.ToString()
aseries.MarkerStyle = ChartMarkerType.Diamond
aseries.MarkerBackgroundColor = Color.Teal
aseries.MarkerSize = 8
aseries.Line.Weight = WeightType.MediumLine
aseries.Line.Style = LineType.DashDot


Next i
'Set Properties of categoryaxis title
chart.CategoryAxis.Title.Text = “Year(2002-2006)”
chart.CategoryAxis.Title.TextFont.Color = Color.Black
chart.CategoryAxis.Title.TextFont.IsBold = True
chart.CategoryAxis.Title.TextFont.Size = 10
'Set the legend position type.
chart.Legend.Position = LegendPositionType.Top
'Save the excel file
workbook.Save(“f:\test\sample_test_chart2.xls”)

The output file is also attached.

We recommend you to brows/check the charting APIs in Aspose.Cells library, so that you may create and format your desired charts for your requirement.

For your further reference, please check the chart demos:
http://www.aspose.com/demos/.net-components/aspose.cells/vb.net/chart-types/default.aspx

There is a tutorial video for the chart:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/creating-a-chart.html

Also, check different documents in the section:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/creating-charts.html


If you still find any issue, please create your desired chart in MS Excel and save the file in MS Excel manually. Post the file here, we will tell you how you may create the chart using Aspose.Cells for .NET APIs.

Thank you.

Thanks this helps, I was able to export the chart using aspose.cells, However I cannot see the nseries on the chart even though there is data, I am using import datatable method to get the data and values are populating correctly on the excelsheet, Please see the attached output but should give you an idea

Private Sub CreateStaticReport(ByVal workbook As Workbook)

Dim sheet As Worksheet = workbook.Worksheets(0)

Dim cells As Cells = sheet.Cells

sheet.IsGridlinesVisible = False

'Create chart

Dim chartIndex As Integer = sheet.Charts.Add(ChartType.Column, 7, 5, 40, 40)

Dim chart As Aspose.Cells.Chart = sheet.Charts(chartIndex)

Dim nseries As NSeries = chart.NSeries

nseries.Add("B2:D34", True)

nseries.CategoryData = "A2:A34"

chart.NSeries(0).Type = ChartType.Line

chart.NSeries(0).Name = "Series - 1"

chart.NSeries(0).Line.Color = Color.Black

chart.NSeries(0).MarkerStyle = ChartMarkerType.Diamond

chart.NSeries(0).MarkerBackgroundColor = Color.Green

chart.NSeries(1).Type = ChartType.Column

chart.NSeries(1).Name = "Series - 2"

chart.NSeries(2).Type = ChartType.Line

chart.NSeries(2).Name = "Series - 3"

cells = workbook.Worksheets(0).Cells

Dim aseries As ASeries

'Set the legend position type

chart.Legend.Position = LegendPositionType.Top

chart.MajorGridLines.IsVisible = False

'Set properties of chart title

chart.Title.Text = "Monitor Chart"

chart.Title.TextFont.IsBold = True

chart.Title.TextFont.Color = Color.Black

chart.Title.TextFont.Size = 12

'Set properties of categoryaxis title

chart.CategoryAxis.Title.Text = "RptDt"

chart.CategoryAxis.Title.TextFont.Color = Color.Black

chart.CategoryAxis.Title.TextFont.IsBold = True

chart.CategoryAxis.Title.TextFont.Size = 10

'Set properties of valueaxis title

'chart.ValueAxis.Title.Text = "Orig ADC"

chart.ValueAxis.Title.TextFont.Name = "Arial"

chart.ValueAxis.Title.TextFont.Color = Color.Black

chart.ValueAxis.Title.TextFont.IsBold = True

chart.ValueAxis.Title.TextFont.Size = 10

chart.ValueAxis.Title.Rotation = 90

End Sub

Hi,

Well, I think one possible reason the series are not rendered on the chart is the 3 series data is not numeric rather text/string. You can see the green triangle (“Numbers Stored as Text”) attached to each cell in B,C and D columns. You got to convert these string values to numeric values. I think there is possibility that on the back end (data source table(s)) the base columns’ data type is not numeric rather these might be string/text etc. There are some options that you might do to fix the issue, choose one.

1) You may convert string data type(for these columns) to numeric data type on the back end.

2) I think you may convert the string values to numeric values while importing data table to the worksheet using some appropriate overloaded method e.g
Cells.ImportDataTable(DataTable dt, bool isFieldNameShown, int FirstRow, int FirstCol, bool insertRows, bool convertStringToNumber)

Please put “true” for the last parameter in the method.

3) You can simply import the datatable as it is and then convert the string values to numeric in Columns B,C and D, e.g

[C#]
Cells cells = worksheet.Cells;
//Format the column B to numeric column.
for (int i = 1; i < cells.MaxDataRowInColumn(1); i++)
{

cells[i, 1].PutValue(cells[i, 1].StringValue, true);

}


[VB]
Dim cells As Cells = worksheet.Cells
'Format the column B to numeric column.
For i As Integer = 1 To cells.MaxDataRowInColumn(1) - 1

cells(i, 1).PutValue(cells(i, 1).StringValue, True)

Next i



Now you may create your desired chart, it should render the series data fine.


Thank you.

Thanks I was able to render the chart but I am not able to display decimal values on the chart datatable, I used option 1 you suggested to changed the database values to float because didn't see any option to change to decimal values on other 2 options. In the datatable it always rounds to integer, How can I render chart with decimal values, Importdatatable is outputting as decimal values to chart

Hi,

I am not completely sure about the problem you are facing. “but I am not able to display decimal values on the chart datatable”…do you mean to say that you are also rendering the data table for the chart attached to the chart, and this data table does not display the values as given in the imported data source in the worksheet. Please create a simple console Application to implement the task. You may create your own datatable dynamically or use MS Access’s database table to fill some data and then import the datatable to the worksheet before creating the chart, zip the project including the excel file(s) and post it here. We will check it soon.


Thank you.

Yes, I am also rendering the datatable for the chart and this chart datatable is not showing the decimal values, the numbers appears rounded. Data for the chart is imported to the same worksheetsheet using importdatatable method). After chart is rendered if I manually change the cell format to custom "0.0" from "general" then datatable is automatically refreshed to show the correct value as 22.8 instead of 23 which is what I need.

tried to change the cell format values using the code below. The column I am testing is 18 i.e R

For i As Integer = 2 To cells.MaxDataRowInColumn(18) - 1

cells(i, 18).PutValue(cells(i, 18).FloatValue, True)

Next

Also tried

sheet.Cells.Columns(18).Style.Custom = "0.0"

I just need to format the cells to custom 0.0 instead of "general" format with which it is output to worksheet so that cahrt can use the custom format , please let me know.

Hi,

Please try to use the code (given below) to apply “0.0” custom number formats to the column.


Dim style As Style
Dim flag As StyleFlag
’Create the style object(adding new to the workbook styles collection).
style = wb.Styles(wb.Styles.Add())
'Set the custom number formatting for your need.
style.Custom = "0.0"
flag = New StyleFlag()
'Apply the number format(on).
flag.NumberFormat = True
’Apply the style formatting to the first whole column.
sheet.Cells.ApplyColumnStyle(18, style, flag)

For reference: http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/formatting-rows-columns.html

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/setting-display-formats-of-numbers-dates.html


Thank you.


Thank you for your reply and we are able to use Aspose in our applications as we required, while we are on the final process of placing the order I have a temporary license.

How do I incorporate this for the application currently using the trial version( Trial version is expiring soon) I hope I do not have to download the .total again. Please let me know asap.

Also once we purchase Aspose how do we upgrade from temp license?

Hi

Well, you just need to use latest version or fix with your license. By default our downloads works in evaluation/trial mode, but when you use a license (temporary or permanent) with it, it will become a licensed version. I have attached the latest fix for your requirement(you don’t need to install Aspose.Total installer anymore).

If your temporary license is expiring soon, you need to just purchase our fully functional license. You may take help from our Purchase forum:
http://www.aspose.com/community/forums/aspose.purchase/220/showforum.aspx


Thank you.