Hi,
I am currently working in a project with the aim of converting a legacy code
using interop.excel based code to a new one which is based on Aspose cells.
I will probably have several questions relating to this effort.
The first one is:
How would one translate the code below to Aspoe Cells?
oChart.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue).MaximumScale
Note: oChart is type of Microsoft.Office.Interop.Excel.Chart
I believe this is referring to the MaximumScale property/parameter of the ValueAxis of the
excel chart.
I tried to look up this using the net and also intelisense of VB Studio but I was not able
to find this.
(Unfortunately I am totally new to this so, please excuse me if this is so obvious.)
Thanks for all the help!
Kind regards,
Tamas
Hi Tamas,
Thanks for your posting and considering Aspose.Cells.
If I am not mistaken, I think, you want to change the Maximum Value of the Value Axis as shown in this screenshot.
You can change it using the Chart.ValueAxis.MaxValue property.
Please see the following code, I have attached the source and output files for your reference.
C#
//Create workbook object from source file
Workbook workbook = new Workbook(“source.xlsx”);
//Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Access the first chart in this worksheet
Chart chart = worksheet.Charts[0];
//Change the MaxValue of the Value Axis
chart.ValueAxis.MaxValue = 60;
//Save the workbook
workbook.Save(“output.xlsx”);
Screenshot:
Hi Shakeel,
Thank you, I think this might be it!
I would like to ask another question if I may.
I have an excel sheet. Based on it, I generated a pivot table and a pivot chart with a dotnet code.
I added an Nseries collection to the chart and I would like to change the chart type of some of the
data series to type “LineWithDataMarkes”.
It seems that my effort do not have any effect of the resulting chart.
(Only if I change the type of ALL the series to “LineWithDataMarkes”.)
Could you please help me what am I doing wrong?
I attached my dotnet code (solution) to this message and a screenshot about the resulting
and desired charts.
Thanks a lot!
Kind regards,
Tamas
Hi Tamas,
Thanks for your posting and using Aspose.Cells.
Please also provide us your expected output xlsx file as you have shown in the screenshot (desired pivot chart.jpg). You can create it manually using MS-Excel and post it here. It will help us look into your issue and we will be able to suggest you a sample code or workaround.
Hi,
Yes, of course, here it is the excel file that contains the chart we would like to generate.
Basically we would like to change the chart type of two of the series in the chart.
Thank you!
Kind regards,
Tamas
Hi Tamas,
Thanks for your sample file and using Aspose.Cells.
I have looked into your issue and I found if you do the following changes as highlighted in red, then your pivot chart is created as expected.
I have attached the output xlsx file generated by this code.
VB.NET
'Instantiating a Workbook object
Dim wb As Workbook = New Workbook(“F:\Shak-Data-RW\Downloads\test\Aspose06\Aspose06\bin\Debug\charttest.xlsx”)
Dim ps As Aspose.Cells.Worksheet = wb.Worksheets.Add(“PivotTable”)
'Getting the pivottables collection in the sheet
Dim pivotTables As Aspose.Cells.Pivot.PivotTableCollection = ps.PivotTables
'Adding a PivotTable to the worksheet
Dim index As Integer = pivotTables.Add("=Data!A1:D8", “A1”, “PivotTable1”)
'Accessing the instance of the newly added PivotTable
Dim pivotTable As Aspose.Cells.Pivot.PivotTable = pivotTables(index)
'Defining Rows for pivot table
Dim rowindex As Integer = pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Creation”)
Dim rf As PivotField = pivotTable.RowFields(rowindex)
'Defining Data column for pivot table
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “MY ID”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “Rate”)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “Hours”)
pivotTable.DataFields(“Hours”).Function = ConsolidationFunction.Sum
Dim datafield As PivotField = pivottable.Rowfields(“Data”)
If datafield isnot Nothing
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, datafield)
End If
pivotTable.CalculateRange()
Dim oChartXL As Aspose.Cells.Charts.Chart
Dim chartsheet As Aspose.Cells.Worksheet = wb.Worksheets.Insert(ps.Index + 1, SheetType.Worksheet, “Testchart”)
Dim chartindex As Integer = chartsheet.Charts.Add(Aspose.Cells.Charts.ChartType.Column, 0, 5, 28, 16)
oChartXL = chartsheet.Charts(chartindex)
'Setting the pivot chart data source
oChartXL.PivotSource = ps.name & “!” & pivotTable.name
oChartXL.HidePivotFieldButtons = False
Dim Area As String = Aspose.Cells.CellsHelper.CellIndexToName(pivottable.TableRange1.StartRow,pivottable.TableRange1.StartColumn) & “:” & Aspose.Cells.CellsHelper.CellIndexToName(pivottable.TableRange1.EndRow,pivottable.TableRange1.EndColumn)
oChartXL.NSeries.Add(“B2:D4”, True)
oChartXL.NSeries(0).Type = Charts.ChartType.LineWithDataMarkers
oChartXL.NSeries(1).Type = Charts.ChartType.LineWithDataMarkers
'oChartXL.NSeries(2).Type = Charts.ChartType.LineWithDataMarkers
'oChartXL.NSeries(3).Type = Charts.ChartType.ColumnStacked
wb.Save(“F:\Shak-Data-RW\Downloads\test\Aspose06\Aspose06\bin\Debug\outcharttest_new.xlsx”)
Hi Shakeel,
Sorry for the late reply!
Yes, it works if I do the correction you have recommended!
Thank you!
Tamas
Hi Tamas,
Thanks for your feedback and using Aspose.Cells.
It is good to know that your issue is resolved with the above code change. Let us know if you encounter any other issue, we will be glad to look into it and help you further.