Create a chart in Excel on the specified worksheet or on a separate chartsheet.
#Include <ExcelChart.au3>
_XLChart_ChartCreate($oExcel, $vWorksheet, $iChartType, $sSizeByCells, $sChartName, $sXValueRange, $vDataRange, $vDataName[, $bShowLegend = True[, $sTitle = ""[, $sXTitle = ""[, $sYTitle = ""[, $sZTitle = ""[, $bShowDatatable = False[, $bScreenUpdate = False]]]]]]])
Parameters
$oExcel | Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() |
$vWorksheet | Worksheet number or name where the chart should be created (eg. 1) |
$iChartType | Chart type number to be used (see ExcelChartConstants.au3 for details, eg. $xl3DColumn) |
$sSizeByCells | The left-hand top and right-hand bottom corner of the chart (eg. "B2:K24"). 0 = create the chart on a separate chartsheet. This sheet will be inserted before the sheet specified by $vWorksheet |
$sChartName | Name of the chart or the chartsheet (if $sSizeByCells = 0) |
$sXValueRange | Category (X) axis label range always a single range (eg. "=Sheet1!R2C1:R6C1") |
$vDataRange | The values range. Either a single range or an one-dimensional one based array |
$vDataName | Header name of the range. Either a single range or an one-dimensional one based array |
$bShowLegend | Optional: Set to True to show the legend or False to hide it (default = True) |
$sTitle | Optional: Chart Title. If empty, no title will be displayed (default = "") |
$sXTitle | Optional: X Axis title (default = "") |
$sYTitle | Optional: Y Axis title (default = "") |
$sZTitle | Optional: Y Axis title (default = "") |
$bShowDatatable | Optional: Set to True to show the data table or False to hide it (default = False) |
$bScreenUpdate | Optional: Set to False to disable screen updating during chart creation to enhance performance (default = False) |
Return Value
Success: Object identifier of the created chart, sets @extended to:
Remarks
The COM error handler will be initialized only if there doesn't already exist another error handler.
Related
Example
#AutoIt3Wrapper_UseX64=n
#AutoIt3Wrapper_AU3Check_Parameters= -d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6
#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y
#include-once
#include <ExcelChart.au3>
; *****************************************************************************
; Create example environment
; *****************************************************************************
Global $aExcel = _XLChart_Example()
If @error = 2 Then Exit MsgBox(16, "Excel Chart Example Script", "The installed Excel version is not supported by this UDF!" & @CRLF & "Version must be >= 12 (Excel 2007).")
If @error <> 0 Then Exit MsgBox(16, "Excel Chart Example Script", "Error " & @error & " returned by _XLChart_Example on line " & @ScriptLineNumber)
; *****************************************************************************
; Example 1
; 3D COLUMN: Without legend, with data table
; Requires arrays for $DataName and $DataRange
; *****************************************************************************
Global $Graph_position = "=_XLChart_Example!A8:F26"
Global $XValueRange = "=_XLChart_Example!R2C1:R6C1"
Global $DataRange[3] = [2, "=_XLChart_Example!R2C2:R6C2", "=_XLChart_Example!R2C3:R6C3"]
Global $DataName[3] = [2, "=_XLChart_Example!B1", "=_XLChart_Example!C1"]
Global $oChart = _XLChart_ChartCreate($aExcel[0], "_XLChart_Example", $xl3DColumn, $Graph_position, "", $XValueRange, $DataRange, $DataName, False, "Sales", "Date", "Location", "Quantity")
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
; *****************************************************************************
; Example 2
; 3D PYRAMID COLUMN: With legend
; Requires arrays for $DataName and $DataRange
; *****************************************************************************
$Graph_position = "H8:M26"
$oChart = _XLChart_ChartCreate($aExcel[0], 1, $xlPyramidCol, $Graph_position, "", $XValueRange, $DataRange, $DataName, False, "Sales", "Date", "", "Quantity")
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
; *****************************************************************************
; Example 3
; 3D STACKED AREA: Without legend
; *****************************************************************************
$Graph_position = "A28:F46"
$oChart = _XLChart_ChartCreate($aExcel[0], "_XLChart_Example", $xl3DAreaStacked, $Graph_position, "", $XValueRange, $DataRange, $DataName, True, "Sales", "Date")
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate'! on line " & @ScriptLineNumber)
; *****************************************************************************
; Example 4
; OPEN-HIGH-LOW-CLOSE Chart
; *****************************************************************************
_ExcelWriteCell($aExcel[0], "Date", 28, 14)
_ExcelWriteCell($aExcel[0], "02/24/2012", 29, 14)
_ExcelWriteCell($aExcel[0], "02/23/2012", 30, 14)
_ExcelWriteCell($aExcel[0], "02/22/2012", 31, 14)
_ExcelWriteCell($aExcel[0], "02/21/2012", 32, 14)
_ExcelWriteCell($aExcel[0], "02/17/2012", 33, 14)
$aExcel[0].ActiveSheet.Columns(14).AutoFit
_ExcelWriteCell($aExcel[0], "Open", 28, 15)
_ExcelWriteCell($aExcel[0], "68.682", 29, 15)
_ExcelWriteCell($aExcel[0], "69.342", 30, 15)
_ExcelWriteCell($aExcel[0], "69.203", 31, 15)
_ExcelWriteCell($aExcel[0], "69.204", 32, 15)
_ExcelWriteCell($aExcel[0], "69.485", 33, 15)
$aExcel[0].ActiveSheet.Columns(15).AutoFit
_ExcelWriteCell($aExcel[0], "High", 28, 16)
_ExcelWriteCell($aExcel[0], "68.826", 29, 16)
_ExcelWriteCell($aExcel[0], "69.375", 30, 16)
_ExcelWriteCell($aExcel[0], "69.463", 31, 16)
_ExcelWriteCell($aExcel[0], "69.364", 32, 16)
_ExcelWriteCell($aExcel[0], "69.598", 33, 16)
$aExcel[0].ActiveSheet.Columns(16).AutoFit
_ExcelWriteCell($aExcel[0], "Low", 28, 17)
_ExcelWriteCell($aExcel[0], "68.263", 29, 17)
_ExcelWriteCell($aExcel[0], "68.654", 30, 17)
_ExcelWriteCell($aExcel[0], "69.085", 31, 17)
_ExcelWriteCell($aExcel[0], "68.891", 32, 17)
_ExcelWriteCell($aExcel[0], "69.251", 33, 17)
$aExcel[0].ActiveSheet.Columns(17).AutoFit
_ExcelWriteCell($aExcel[0], "Close", 28, 18)
_ExcelWriteCell($aExcel[0], "68.403", 29, 18)
_ExcelWriteCell($aExcel[0], "68.882", 30, 18)
_ExcelWriteCell($aExcel[0], "69.307", 31, 18)
_ExcelWriteCell($aExcel[0], "68.135", 32, 18)
_ExcelWriteCell($aExcel[0], "69.450", 33, 18)
$aExcel[0].ActiveSheet.Columns(18).AutoFit
$Graph_position = "H28:M46"
$XValueRange = "=_XLChart_Example!29R2C14:R33C14"
Global $DataRange[5] = [4, "=_XLChart_Example!R29C15:R33C15", "=_XLChart_Example!R29C16:R33C16", "=_XLChart_Example!R29C17:R33C17", "=_XLChart_Example!R29C18:R33C18"]
Global $DataName[5] = [4, "=_XLChart_Example!O1", "=_XLChart_Example!P1", "=_XLChart_Example!Q1", "=_XLChart_Example!R1"]
Global $oChart1 = _XLChart_ChartCreate($aExcel[0], 1, $xlStockOHLC, $Graph_position, "", $XValueRange, $DataRange, $DataName, Default, "Open-High-Low-Close Chart")
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate'! on line " & @ScriptLineNumber)
; Format chart
_XLChart_FillSet($oChart1.Chartgroups(1).UpBars, -4) ; Set the UpBars to green
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
_XLChart_FillSet($oChart1.Chartgroups(1).DownBars, -3) ; Set the DownBars to red
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
_XLChart_ColumnGroupSet($oChart1.ChartGroups(1), 250) ; Make the bars smaller (by making the gap between the bars bigger)
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
; *****************************************************************************
; Example 5
; 3D EXPLODED PIE: With legend. Create the chart on a separate chart sheet
; *****************************************************************************
MsgBox(16, "Excel Chart Example Script", "Now creating a pie chart on a separate chart sheet!" & @CRLF & "Please press enter!")
$XValueRange = "=_XLChart_Example!R2C1:R6C1"
$DataRange = "=_XLChart_Example!R2C2:R6C2"
$DataName = "=_XLChart_Example!B1"
Global $Title = _ExcelReadCell($aExcel[0], "B1")
$oChart = _XLChart_ChartCreate($aExcel[0], 1, $xl3DPieExploded, 0, "Example Chart", $XValueRange, $DataRange, $DataName, True, $Title)
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)