Juan Velasquez
asked on
How do I display years across the x-axis
Hello,
I am using the following code to create a graph in a excel worksheet from vb.net. The code preceding the code listed below creates the table from which the graph is dereived. I'm having trouble having the years appear on the x-axis .
I am using the following code to create a graph in a excel worksheet from vb.net. The code preceding the code listed below creates the table from which the graph is dereived. I'm having trouble having the years appear on the x-axis .
Dim oChart As Excel.Chart
Dim MyCharts As Excel.ChartObjects
Dim MyCharts1 As Excel.ChartObject
MyCharts = excelSheet.ChartObjects
'set chart location
MyCharts1 = MyCharts.Add(150, 290, 400, 250)
oChart = MyCharts1.Chart
'use the follwoing line if u want
'to draw chart on the default location
'oChart.Location(Excel.XlChartLocation.xlLocationAsObject, excelSheet.Name)
Dim R As Integer = datTable.Rows.Count + 1
'MessageBox.Show(R.ToString)
With oChart
'set data range for chart
Dim chartRange As Excel.Range
chartRange = excelSheet.Range("A1", "N" & R)
'MessageBox.Show(excelSheet.Range("A2").Value.ToString)
.SetSourceData(chartRange)
'set how you want to draw chart i.e column wise or row wise
'.PlotBy = Excel.XlRowCol.xlColumns
.PlotBy = Excel.XlRowCol.xlRows
.SeriesCollection(1).Delete()
'set data lables for bars
.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone)
'set legend to be displayed or not
.HasLegend = True
'set legend location
.Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight
'select chart type
.ChartType = Excel.XlChartType.xlLine
'chart title
.HasTitle = True
.ChartTitle.Text = "Test"
'set titles for Axis values and categories
Dim xlAxisCategory, xlAxisValue As Excel.Axes
xlAxisCategory = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text = "Forecast Years"
xlAxisValue = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text = "Metrics"
'oChart.SeriesCollection(1).Delete()
MessageBox.Show("Chart Completed")
End With
'MyCharts1.BringToFront()
'Set file format number to 56 if excel version <> 2007
If Not filename.EndsWith(".xlsx") Then
excelWorkbook.SaveAs(filename, 56)
Else
excelWorkbook.SaveAs(filename)
End If
MsgBox("File generated successfully at " & filename)
End If
End If
test.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER