< Back
Add-ExcelChart
Post
NAME Add-ExcelChart
SYNOPSIS
Creates a chart in an existing Excel worksheet.
SYNTAX
Add-ExcelChart -Worksheet <ExcelWorksheet> [-Title <String>] [-ChartType {Area | Line | Pie | Bubble |
ColumnClustered | ColumnStacked | ColumnStacked100 | ColumnClustered3D | ColumnStacked3D | ColumnStacked1003D |
BarClustered | BarStacked | BarStacked100 | BarClustered3D | BarStacked3D | BarStacked1003D | LineStacked |
LineStacked100 | LineMarkers | LineMarkersStacked | LineMarkersStacked100 | PieOfPie | PieExploded | PieExploded3D
| BarOfPie | XYScatterSmooth | XYScatterSmoothNoMarkers | XYScatterLines | XYScatterLinesNoMarkers | AreaStacked |
AreaStacked100 | AreaStacked3D | AreaStacked1003D | DoughnutExploded | RadarMarkers | RadarFilled | Surface |
SurfaceWireframe | SurfaceTopView | SurfaceTopViewWireframe | Bubble3DEffect | StockHLC | StockOHLC | StockVHLC |
StockVOHLC | CylinderColClustered | CylinderColStacked | CylinderColStacked100 | CylinderBarClustered |
CylinderBarStacked | CylinderBarStacked100 | CylinderCol | ConeColClustered | ConeColStacked | ConeColStacked100 |
ConeBarClustered | ConeBarStacked | ConeBarStacked100 | ConeCol | PyramidColClustered | PyramidColStacked |
PyramidColStacked100 | PyramidBarClustered | PyramidBarStacked | PyramidBarStacked100 | PyramidCol | XYScatter |
Radar | Doughnut | Pie3D | Line3D | Column3D | Area3D}] [-ChartTrendLine {Exponential | Linear | Logarithmic |
MovingAvgerage | Polynomial | Power}] [-XRange <Object>] [-YRange <Object>] [-Width <Int32>] [-Height <Int32>]
[-Row <Int32>] [-RowOffSetPixels <Int32>] [-Column <Int32>] [-ColumnOffSetPixels <Int32>] [-LegendPosition {Top |
Left | Right | Bottom | TopRight}] [-LegendSize <Object>] [-LegendBold] [-NoLegend] [-ShowCategory] [-ShowPercent]
[-SeriesHeader <String[]>] [-TitleBold] [-TitleSize <Int32>] [-XAxisTitleText <String>] [-XAxisTitleBold]
[-XAxisTitleSize <Object>] [-XAxisNumberformat <String>] [-XMajorUnit <Object>] [-XMinorUnit <Object>] [-XMaxValue
<Object>] [-XMinValue <Object>] [-XAxisPosition {Left | Bottom | Right | Top}] [-YAxisTitleText <String>]
[-YAxisTitleBold] [-YAxisTitleSize <Object>] [-YAxisNumberformat <String>] [-YMajorUnit <Object>] [-YMinorUnit
<Object>] [-YMaxValue <Object>] [-YMinValue <Object>] [-YAxisPosition {Left | Bottom | Right | Top}] [-PassThru]
[<CommonParameters>]
Add-ExcelChart -PivotTable <ExcelPivotTable> [-Title <String>] [-ChartType {Area | Line | Pie | Bubble |
ColumnClustered | ColumnStacked | ColumnStacked100 | ColumnClustered3D | ColumnStacked3D | ColumnStacked1003D |
BarClustered | BarStacked | BarStacked100 | BarClustered3D | BarStacked3D | BarStacked1003D | LineStacked |
LineStacked100 | LineMarkers | LineMarkersStacked | LineMarkersStacked100 | PieOfPie | PieExploded | PieExploded3D
| BarOfPie | XYScatterSmooth | XYScatterSmoothNoMarkers | XYScatterLines | XYScatterLinesNoMarkers | AreaStacked |
AreaStacked100 | AreaStacked3D | AreaStacked1003D | DoughnutExploded | RadarMarkers | RadarFilled | Surface |
SurfaceWireframe | SurfaceTopView | SurfaceTopViewWireframe | Bubble3DEffect | StockHLC | StockOHLC | StockVHLC |
StockVOHLC | CylinderColClustered | CylinderColStacked | CylinderColStacked100 | CylinderBarClustered |
CylinderBarStacked | CylinderBarStacked100 | CylinderCol | ConeColClustered | ConeColStacked | ConeColStacked100 |
ConeBarClustered | ConeBarStacked | ConeBarStacked100 | ConeCol | PyramidColClustered | PyramidColStacked |
PyramidColStacked100 | PyramidBarClustered | PyramidBarStacked | PyramidBarStacked100 | PyramidCol | XYScatter |
Radar | Doughnut | Pie3D | Line3D | Column3D | Area3D}] [-ChartTrendLine {Exponential | Linear | Logarithmic |
MovingAvgerage | Polynomial | Power}] [-XRange <Object>] [-YRange <Object>] [-Width <Int32>] [-Height <Int32>]
[-Row <Int32>] [-RowOffSetPixels <Int32>] [-Column <Int32>] [-ColumnOffSetPixels <Int32>] [-LegendPosition {Top |
Left | Right | Bottom | TopRight}] [-LegendSize <Object>] [-LegendBold] [-NoLegend] [-ShowCategory] [-ShowPercent]
[-SeriesHeader <String[]>] [-TitleBold] [-TitleSize <Int32>] [-XAxisTitleText <String>] [-XAxisTitleBold]
[-XAxisTitleSize <Object>] [-XAxisNumberformat <String>] [-XMajorUnit <Object>] [-XMinorUnit <Object>] [-XMaxValue
<Object>] [-XMinValue <Object>] [-XAxisPosition {Left | Bottom | Right | Top}] [-YAxisTitleText <String>]
[-YAxisTitleBold] [-YAxisTitleSize <Object>] [-YAxisNumberformat <String>] [-YMajorUnit <Object>] [-YMinorUnit
<Object>] [-YMaxValue <Object>] [-YMinValue <Object>] [-YAxisPosition {Left | Bottom | Right | Top}] [-PassThru]
[<CommonParameters>]
DESCRIPTION
Creates a chart.
It is possible to configure the type of chart, the range of X values (labels) and Y values, the title, the legend,
the ranges for both axes, the format and position of the axes.
Normally the command does not return anything, but if -passthru is specified the chart is returned so that it can
be customized.
PARAMETERS
-Worksheet <ExcelWorksheet>
An existing Sheet where the chart will be created.
Required? true
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-PivotTable <ExcelPivotTable>
Instead of specify X and Y ranges, get data from a PivotTable by passing a PivotTable Object.
Required? true
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-Title <String>
The title for the chart.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-ChartType <eChartType>
One of the built-in chart types, such as Pie, ClusteredColumn, Line etc. Defaults to "ColumnStacked".
Required? false
Position? named
Default value ColumnStacked
Accept pipeline input? False
Accept wildcard characters? false
-ChartTrendLine <eTrendLine[]>
{{ Fill ChartTrendLine Description }}
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XRange <Object>
The range of cells containing values for the X-Axis - usually labels.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YRange <Object>
The range(s) of cells holding values for the Y-Axis - usually "data".
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-Width <Int32>
Width of the chart in Pixels; defaults to 500.
Required? false
Position? named
Default value 500
Accept pipeline input? False
Accept wildcard characters? false
-Height <Int32>
Height of the chart in Pixels; defaults to 350.
Required? false
Position? named
Default value 350
Accept pipeline input? False
Accept wildcard characters? false
-Row <Int32>
Row position of the top left corner of the chart. ) places at the top of the sheet, 1 below row 1 and so on.
Required? false
Position? named
Default value 0
Accept pipeline input? False
Accept wildcard characters? false
-RowOffSetPixels <Int32>
Offset to position the chart by a fraction of a row.
Required? false
Position? named
Default value 10
Accept pipeline input? False
Accept wildcard characters? false
-Column <Int32>
Column position of the top left corner of the chart; 0 places at the edge of the sheet 1 to the right of
column A and so on.
Required? false
Position? named
Default value 6
Accept pipeline input? False
Accept wildcard characters? false
-ColumnOffSetPixels <Int32>
Offset to position the chart by a fraction of a column.
Required? false
Position? named
Default value 5
Accept pipeline input? False
Accept wildcard characters? false
-LegendPosition <eLegendPosition>
Location of the key, either left, right, top, bottom or TopRight.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-LegendSize <Object>
Font size for the key.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-LegendBold [<SwitchParameter>]
Sets the key in bold type.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-NoLegend [<SwitchParameter>]
If specified, turns of display of the key. If you only have one data series it may be preferable to use the
title to say what the chart is.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowCategory [<SwitchParameter>]
Attaches a category label, in charts which support this.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowPercent [<SwitchParameter>]
Attaches a percentage label, in charts which support this.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-SeriesHeader <String[]>
Specify explicit name(s) for the data series, which will appear in the legend/key. The contents of a cell can
be specified in the from =Sheet9!Z10 .
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-TitleBold [<SwitchParameter>]
Sets the title in bold face.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-TitleSize <Int32>
Sets the point size for the title.
Required? false
Position? named
Default value 0
Accept pipeline input? False
Accept wildcard characters? false
-XAxisTitleText <String>
Specifies a title for the X-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XAxisTitleBold [<SwitchParameter>]
Sets the X-axis title in bold face.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-XAxisTitleSize <Object>
Sets the font size for the axis title.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XAxisNumberformat <String>
A number formatting string, like "#,##0.00", for numbers along the X-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XMajorUnit <Object>
Spacing for the major gridlines / tick marks along the X-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XMinorUnit <Object>
Spacing for the minor gridlines / tick marks along the X-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XMaxValue <Object>
Maximum value for the scale along the X-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XMinValue <Object>
Minimum value for the scale along the X-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XAxisPosition <eAxisPosition>
Position for the X-axis (Top or Bottom).
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YAxisTitleText <String>
Specifies a title for the Y-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YAxisTitleBold [<SwitchParameter>]
Sets the Y-axis title in bold face.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-YAxisTitleSize <Object>
Sets the font size for the Y-axis title
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YAxisNumberformat <String>
A number formatting string, like "#,##0.00", for numbers on the Y-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YMajorUnit <Object>
Spacing for the major gridlines / tick marks on the Y-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YMinorUnit <Object>
Spacing for the minor gridlines / tick marks on the Y-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YMaxValue <Object>
Maximum value on the Y-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YMinValue <Object>
Minimum value on the Y-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YAxisPosition <eAxisPosition>
Position for the Y-axis (Left or Right).
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-PassThru [<SwitchParameter>]
Add-Excel chart doesn't normally return anything, but if -PassThru is specified it returns the newly created
chart to allow it to be fine tuned.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
OfficeOpenXml.Drawing.Chart.ExcelChart
NOTES
-------------------------- EXAMPLE 1 --------------------------
PS\\> $Excel = ConvertFrom-Csv @"
Product, City, Sales
Apple, London, 300
Orange, London, 400
Banana, London, 300
Orange, Paris, 600
Banana, Paris, 300
Apple, New York, 1200
"@ | Export-Excel -Path test.xlsx -PassThru
Add-ExcelChart -Worksheet $Excel.Workbook.Worksheets[1] -ChartType "Doughnut" -XRange "A2:B7" -YRange "C2:C7"
-width 500
Close-ExcelPackage -Show $Excel
The first command expands a multi-line string into 6 rows of data which is exported to new Excel file; leaving an
ExcelPackage object in $excel The second command adds a chart - the cell ranges are explicitly specified.
Note that the XRange (labels) is TWO columns wide and the chart will combine the name of the product and the name
of the City to create the label.
The width of the chart is set explictly, the default legend is used and there is no Chart title.
-------------------------- EXAMPLE 2 --------------------------
PS\\> $Excel = Invoke-Sum (Get-Process) Company Handles, PM, VirtualMemorySize | Export-Excel $path -AutoSize
-ExcelChartDefinition $c -AutoNameRange -PassThru
Add-ExcelChart -Worksheet $Excel.Workbook.Worksheets[1] -Title "VM use" -ChartType PieExploded3D -XRange
"Name" -YRange "VirtualMemorySize" -NoLegend -ShowCategory
Close-ExcelPackage $Excel -Show
The first line exports information and creates named ranges for each column.
The Second line uses the ranges to specify a chart - the labels come from the range "Name" and the data from the
range "VirtualMemorySize"
The chart is specified as a 3D exploded PIE chart, with a title of "VM Use" and instead of a legend the the pie
slices are identified with a label.
-------------------------- EXAMPLE 3 --------------------------
PS\\> $Excel = Invoke-Sum (Get-Process) Company Handles, PM, VirtualMemorySize | Export-Excel test.xlsx -TableName
Processes -PassThru
Add-ExcelChart -Worksheet $Excel.Workbook.Worksheets[1] -Title Stats -ChartType LineMarkersStacked -XRange
"Processes[Name]" -YRange "Processes[PM]", "Processes[VirtualMemorySize]" -SeriesHeader 'PM', 'VMSize'
Close-ExcelPackage $Excel -Show
The first line exports information to a table in new file; and captures the excel Package object in $Excel
The second line creates a chart on the first page of the work sheet, using the notation "TableName[ColumnnName]"
to refer to the data, the labels come Name column in the table, and the data series from its PM and
VirtualMemorySize columns. The display names for these in the header are set to 'PM' and 'VMSize'.
-------------------------- EXAMPLE 4 --------------------------
PS\\> $excel = 0..360 | ForEach-Object {[pscustomobject][ordered]@{x = $_; Sinx = "=Sin(Radians(x)) "}} |
Export-Excel -AutoNameRange -Path Text.xlsx -WorkSheetname SinX -PassThru
Add-ExcelChart -Worksheet $excel.Workbook.Worksheets["Sinx"] -ChartType line -XRange "X" -YRange "Sinx" -Title
"Graph of Sine X" -TitleBold -TitleSize 14 \\`
-Column 2 -ColumnOffSetPixels 35 -Width 800 -XAxisTitleText "Degrees" -XAxisTitleBold
-XAxisTitleSize 12 -XMajorUnit 30 -XMinorUnit 10 -XMinValue 0 -XMaxValue 361 -XAxisNumberformat "000" \\`
-YMinValue -1.25 -YMaxValue 1.25 -YMajorUnit 0.25 -YAxisNumberformat "0.00" -YAxisTitleText "Sine"
-YAxisTitleBold -YAxisTitleSize 12 \\`
-SeriesHeader "Sin(x)" -LegendSize 8 -legendBold -LegendPosition Bottom
Close-ExcelPackage $Excel -Show
The first line puts numbers from 0 to 360 into a sheet, as the first column, and a formula to calculate the Sine
of that number of number of degrees in the second column. It creates named-ranges for the two columns - "X" and
"SinX" respectively
The Add-ExcelChart command adds a chart to that worksheet, specifying a line chart with the X values coming from
named-range "X" and the Y values coming from named-range "SinX". The chart has a title, and is positioned to the
right of column 2 and sized 800 pixels wide
The X-axis is labelled "Degrees", in bold 12 point type and runs from 0 to 361 with labels every 30, and minor
tick marks every 10. Degrees are shown padded to 3 digits.
The Y-axis is labelled "Sine" and to allow some room above and below its scale runs from -1.25 to 1.25, and is
marked off in units of 0.25 shown to two decimal places.
The key will for the chart will be at the bottom in 8 point bold type and the line will be named "Sin(x)".
RELATED LINKS
SYNOPSIS
Creates a chart in an existing Excel worksheet.
SYNTAX
Add-ExcelChart -Worksheet <ExcelWorksheet> [-Title <String>] [-ChartType {Area | Line | Pie | Bubble |
ColumnClustered | ColumnStacked | ColumnStacked100 | ColumnClustered3D | ColumnStacked3D | ColumnStacked1003D |
BarClustered | BarStacked | BarStacked100 | BarClustered3D | BarStacked3D | BarStacked1003D | LineStacked |
LineStacked100 | LineMarkers | LineMarkersStacked | LineMarkersStacked100 | PieOfPie | PieExploded | PieExploded3D
| BarOfPie | XYScatterSmooth | XYScatterSmoothNoMarkers | XYScatterLines | XYScatterLinesNoMarkers | AreaStacked |
AreaStacked100 | AreaStacked3D | AreaStacked1003D | DoughnutExploded | RadarMarkers | RadarFilled | Surface |
SurfaceWireframe | SurfaceTopView | SurfaceTopViewWireframe | Bubble3DEffect | StockHLC | StockOHLC | StockVHLC |
StockVOHLC | CylinderColClustered | CylinderColStacked | CylinderColStacked100 | CylinderBarClustered |
CylinderBarStacked | CylinderBarStacked100 | CylinderCol | ConeColClustered | ConeColStacked | ConeColStacked100 |
ConeBarClustered | ConeBarStacked | ConeBarStacked100 | ConeCol | PyramidColClustered | PyramidColStacked |
PyramidColStacked100 | PyramidBarClustered | PyramidBarStacked | PyramidBarStacked100 | PyramidCol | XYScatter |
Radar | Doughnut | Pie3D | Line3D | Column3D | Area3D}] [-ChartTrendLine {Exponential | Linear | Logarithmic |
MovingAvgerage | Polynomial | Power}] [-XRange <Object>] [-YRange <Object>] [-Width <Int32>] [-Height <Int32>]
[-Row <Int32>] [-RowOffSetPixels <Int32>] [-Column <Int32>] [-ColumnOffSetPixels <Int32>] [-LegendPosition {Top |
Left | Right | Bottom | TopRight}] [-LegendSize <Object>] [-LegendBold] [-NoLegend] [-ShowCategory] [-ShowPercent]
[-SeriesHeader <String[]>] [-TitleBold] [-TitleSize <Int32>] [-XAxisTitleText <String>] [-XAxisTitleBold]
[-XAxisTitleSize <Object>] [-XAxisNumberformat <String>] [-XMajorUnit <Object>] [-XMinorUnit <Object>] [-XMaxValue
<Object>] [-XMinValue <Object>] [-XAxisPosition {Left | Bottom | Right | Top}] [-YAxisTitleText <String>]
[-YAxisTitleBold] [-YAxisTitleSize <Object>] [-YAxisNumberformat <String>] [-YMajorUnit <Object>] [-YMinorUnit
<Object>] [-YMaxValue <Object>] [-YMinValue <Object>] [-YAxisPosition {Left | Bottom | Right | Top}] [-PassThru]
[<CommonParameters>]
Add-ExcelChart -PivotTable <ExcelPivotTable> [-Title <String>] [-ChartType {Area | Line | Pie | Bubble |
ColumnClustered | ColumnStacked | ColumnStacked100 | ColumnClustered3D | ColumnStacked3D | ColumnStacked1003D |
BarClustered | BarStacked | BarStacked100 | BarClustered3D | BarStacked3D | BarStacked1003D | LineStacked |
LineStacked100 | LineMarkers | LineMarkersStacked | LineMarkersStacked100 | PieOfPie | PieExploded | PieExploded3D
| BarOfPie | XYScatterSmooth | XYScatterSmoothNoMarkers | XYScatterLines | XYScatterLinesNoMarkers | AreaStacked |
AreaStacked100 | AreaStacked3D | AreaStacked1003D | DoughnutExploded | RadarMarkers | RadarFilled | Surface |
SurfaceWireframe | SurfaceTopView | SurfaceTopViewWireframe | Bubble3DEffect | StockHLC | StockOHLC | StockVHLC |
StockVOHLC | CylinderColClustered | CylinderColStacked | CylinderColStacked100 | CylinderBarClustered |
CylinderBarStacked | CylinderBarStacked100 | CylinderCol | ConeColClustered | ConeColStacked | ConeColStacked100 |
ConeBarClustered | ConeBarStacked | ConeBarStacked100 | ConeCol | PyramidColClustered | PyramidColStacked |
PyramidColStacked100 | PyramidBarClustered | PyramidBarStacked | PyramidBarStacked100 | PyramidCol | XYScatter |
Radar | Doughnut | Pie3D | Line3D | Column3D | Area3D}] [-ChartTrendLine {Exponential | Linear | Logarithmic |
MovingAvgerage | Polynomial | Power}] [-XRange <Object>] [-YRange <Object>] [-Width <Int32>] [-Height <Int32>]
[-Row <Int32>] [-RowOffSetPixels <Int32>] [-Column <Int32>] [-ColumnOffSetPixels <Int32>] [-LegendPosition {Top |
Left | Right | Bottom | TopRight}] [-LegendSize <Object>] [-LegendBold] [-NoLegend] [-ShowCategory] [-ShowPercent]
[-SeriesHeader <String[]>] [-TitleBold] [-TitleSize <Int32>] [-XAxisTitleText <String>] [-XAxisTitleBold]
[-XAxisTitleSize <Object>] [-XAxisNumberformat <String>] [-XMajorUnit <Object>] [-XMinorUnit <Object>] [-XMaxValue
<Object>] [-XMinValue <Object>] [-XAxisPosition {Left | Bottom | Right | Top}] [-YAxisTitleText <String>]
[-YAxisTitleBold] [-YAxisTitleSize <Object>] [-YAxisNumberformat <String>] [-YMajorUnit <Object>] [-YMinorUnit
<Object>] [-YMaxValue <Object>] [-YMinValue <Object>] [-YAxisPosition {Left | Bottom | Right | Top}] [-PassThru]
[<CommonParameters>]
DESCRIPTION
Creates a chart.
It is possible to configure the type of chart, the range of X values (labels) and Y values, the title, the legend,
the ranges for both axes, the format and position of the axes.
Normally the command does not return anything, but if -passthru is specified the chart is returned so that it can
be customized.
PARAMETERS
-Worksheet <ExcelWorksheet>
An existing Sheet where the chart will be created.
Required? true
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-PivotTable <ExcelPivotTable>
Instead of specify X and Y ranges, get data from a PivotTable by passing a PivotTable Object.
Required? true
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-Title <String>
The title for the chart.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-ChartType <eChartType>
One of the built-in chart types, such as Pie, ClusteredColumn, Line etc. Defaults to "ColumnStacked".
Required? false
Position? named
Default value ColumnStacked
Accept pipeline input? False
Accept wildcard characters? false
-ChartTrendLine <eTrendLine[]>
{{ Fill ChartTrendLine Description }}
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XRange <Object>
The range of cells containing values for the X-Axis - usually labels.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YRange <Object>
The range(s) of cells holding values for the Y-Axis - usually "data".
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-Width <Int32>
Width of the chart in Pixels; defaults to 500.
Required? false
Position? named
Default value 500
Accept pipeline input? False
Accept wildcard characters? false
-Height <Int32>
Height of the chart in Pixels; defaults to 350.
Required? false
Position? named
Default value 350
Accept pipeline input? False
Accept wildcard characters? false
-Row <Int32>
Row position of the top left corner of the chart. ) places at the top of the sheet, 1 below row 1 and so on.
Required? false
Position? named
Default value 0
Accept pipeline input? False
Accept wildcard characters? false
-RowOffSetPixels <Int32>
Offset to position the chart by a fraction of a row.
Required? false
Position? named
Default value 10
Accept pipeline input? False
Accept wildcard characters? false
-Column <Int32>
Column position of the top left corner of the chart; 0 places at the edge of the sheet 1 to the right of
column A and so on.
Required? false
Position? named
Default value 6
Accept pipeline input? False
Accept wildcard characters? false
-ColumnOffSetPixels <Int32>
Offset to position the chart by a fraction of a column.
Required? false
Position? named
Default value 5
Accept pipeline input? False
Accept wildcard characters? false
-LegendPosition <eLegendPosition>
Location of the key, either left, right, top, bottom or TopRight.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-LegendSize <Object>
Font size for the key.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-LegendBold [<SwitchParameter>]
Sets the key in bold type.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-NoLegend [<SwitchParameter>]
If specified, turns of display of the key. If you only have one data series it may be preferable to use the
title to say what the chart is.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowCategory [<SwitchParameter>]
Attaches a category label, in charts which support this.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-ShowPercent [<SwitchParameter>]
Attaches a percentage label, in charts which support this.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-SeriesHeader <String[]>
Specify explicit name(s) for the data series, which will appear in the legend/key. The contents of a cell can
be specified in the from =Sheet9!Z10 .
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-TitleBold [<SwitchParameter>]
Sets the title in bold face.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-TitleSize <Int32>
Sets the point size for the title.
Required? false
Position? named
Default value 0
Accept pipeline input? False
Accept wildcard characters? false
-XAxisTitleText <String>
Specifies a title for the X-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XAxisTitleBold [<SwitchParameter>]
Sets the X-axis title in bold face.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-XAxisTitleSize <Object>
Sets the font size for the axis title.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XAxisNumberformat <String>
A number formatting string, like "#,##0.00", for numbers along the X-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XMajorUnit <Object>
Spacing for the major gridlines / tick marks along the X-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XMinorUnit <Object>
Spacing for the minor gridlines / tick marks along the X-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XMaxValue <Object>
Maximum value for the scale along the X-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XMinValue <Object>
Minimum value for the scale along the X-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-XAxisPosition <eAxisPosition>
Position for the X-axis (Top or Bottom).
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YAxisTitleText <String>
Specifies a title for the Y-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YAxisTitleBold [<SwitchParameter>]
Sets the Y-axis title in bold face.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-YAxisTitleSize <Object>
Sets the font size for the Y-axis title
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YAxisNumberformat <String>
A number formatting string, like "#,##0.00", for numbers on the Y-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YMajorUnit <Object>
Spacing for the major gridlines / tick marks on the Y-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YMinorUnit <Object>
Spacing for the minor gridlines / tick marks on the Y-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YMaxValue <Object>
Maximum value on the Y-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YMinValue <Object>
Minimum value on the Y-axis.
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-YAxisPosition <eAxisPosition>
Position for the Y-axis (Left or Right).
Required? false
Position? named
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-PassThru [<SwitchParameter>]
Add-Excel chart doesn't normally return anything, but if -PassThru is specified it returns the newly created
chart to allow it to be fine tuned.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
OfficeOpenXml.Drawing.Chart.ExcelChart
NOTES
-------------------------- EXAMPLE 1 --------------------------
PS\\> $Excel = ConvertFrom-Csv @"
Product, City, Sales
Apple, London, 300
Orange, London, 400
Banana, London, 300
Orange, Paris, 600
Banana, Paris, 300
Apple, New York, 1200
"@ | Export-Excel -Path test.xlsx -PassThru
Add-ExcelChart -Worksheet $Excel.Workbook.Worksheets[1] -ChartType "Doughnut" -XRange "A2:B7" -YRange "C2:C7"
-width 500
Close-ExcelPackage -Show $Excel
The first command expands a multi-line string into 6 rows of data which is exported to new Excel file; leaving an
ExcelPackage object in $excel The second command adds a chart - the cell ranges are explicitly specified.
Note that the XRange (labels) is TWO columns wide and the chart will combine the name of the product and the name
of the City to create the label.
The width of the chart is set explictly, the default legend is used and there is no Chart title.
-------------------------- EXAMPLE 2 --------------------------
PS\\> $Excel = Invoke-Sum (Get-Process) Company Handles, PM, VirtualMemorySize | Export-Excel $path -AutoSize
-ExcelChartDefinition $c -AutoNameRange -PassThru
Add-ExcelChart -Worksheet $Excel.Workbook.Worksheets[1] -Title "VM use" -ChartType PieExploded3D -XRange
"Name" -YRange "VirtualMemorySize" -NoLegend -ShowCategory
Close-ExcelPackage $Excel -Show
The first line exports information and creates named ranges for each column.
The Second line uses the ranges to specify a chart - the labels come from the range "Name" and the data from the
range "VirtualMemorySize"
The chart is specified as a 3D exploded PIE chart, with a title of "VM Use" and instead of a legend the the pie
slices are identified with a label.
-------------------------- EXAMPLE 3 --------------------------
PS\\> $Excel = Invoke-Sum (Get-Process) Company Handles, PM, VirtualMemorySize | Export-Excel test.xlsx -TableName
Processes -PassThru
Add-ExcelChart -Worksheet $Excel.Workbook.Worksheets[1] -Title Stats -ChartType LineMarkersStacked -XRange
"Processes[Name]" -YRange "Processes[PM]", "Processes[VirtualMemorySize]" -SeriesHeader 'PM', 'VMSize'
Close-ExcelPackage $Excel -Show
The first line exports information to a table in new file; and captures the excel Package object in $Excel
The second line creates a chart on the first page of the work sheet, using the notation "TableName[ColumnnName]"
to refer to the data, the labels come Name column in the table, and the data series from its PM and
VirtualMemorySize columns. The display names for these in the header are set to 'PM' and 'VMSize'.
-------------------------- EXAMPLE 4 --------------------------
PS\\> $excel = 0..360 | ForEach-Object {[pscustomobject][ordered]@{x = $_; Sinx = "=Sin(Radians(x)) "}} |
Export-Excel -AutoNameRange -Path Text.xlsx -WorkSheetname SinX -PassThru
Add-ExcelChart -Worksheet $excel.Workbook.Worksheets["Sinx"] -ChartType line -XRange "X" -YRange "Sinx" -Title
"Graph of Sine X" -TitleBold -TitleSize 14 \\`
-Column 2 -ColumnOffSetPixels 35 -Width 800 -XAxisTitleText "Degrees" -XAxisTitleBold
-XAxisTitleSize 12 -XMajorUnit 30 -XMinorUnit 10 -XMinValue 0 -XMaxValue 361 -XAxisNumberformat "000" \\`
-YMinValue -1.25 -YMaxValue 1.25 -YMajorUnit 0.25 -YAxisNumberformat "0.00" -YAxisTitleText "Sine"
-YAxisTitleBold -YAxisTitleSize 12 \\`
-SeriesHeader "Sin(x)" -LegendSize 8 -legendBold -LegendPosition Bottom
Close-ExcelPackage $Excel -Show
The first line puts numbers from 0 to 360 into a sheet, as the first column, and a formula to calculate the Sine
of that number of number of degrees in the second column. It creates named-ranges for the two columns - "X" and
"SinX" respectively
The Add-ExcelChart command adds a chart to that worksheet, specifying a line chart with the X values coming from
named-range "X" and the Y values coming from named-range "SinX". The chart has a title, and is positioned to the
right of column 2 and sized 800 pixels wide
The X-axis is labelled "Degrees", in bold 12 point type and runs from 0 to 361 with labels every 30, and minor
tick marks every 10. Degrees are shown padded to 3 digits.
The Y-axis is labelled "Sine" and to allow some room above and below its scale runs from -1.25 to 1.25, and is
marked off in units of 0.25 shown to two decimal places.
The key will for the chart will be at the bottom in 8 point bold type and the line will be named "Sin(x)".
RELATED LINKS