ImportExcel/Public/Add-ExcelChart.ps1

function Add-ExcelChart {
    [CmdletBinding(DefaultParameterSetName = 'Worksheet')]
    [OutputType([OfficeOpenXml.Drawing.Chart.ExcelChart])]
    param(
        [Parameter(ParameterSetName = 'Worksheet', Mandatory = $true)]
        [OfficeOpenXml.ExcelWorksheet]$Worksheet,
        [Parameter(ParameterSetName = 'PivotTable', Mandatory = $true)]
        [OfficeOpenXml.Table.PivotTable.ExcelPivotTable]$PivotTable ,
        [String]$Title,
        [OfficeOpenXml.Drawing.Chart.eChartType]$ChartType = "ColumnStacked",
        [OfficeOpenXml.Drawing.Chart.eTrendLine[]]$ChartTrendLine,
        $XRange,
        $YRange,
        [int]$Width = 500,
        [int]$Height = 350,
        [int]$Row = 0,
        [int]$RowOffSetPixels = 10,
        [int]$Column = 6,
        [int]$ColumnOffSetPixels = 5,
        [OfficeOpenXml.Drawing.Chart.eLegendPosition]$LegendPosition,
        $LegendSize,
        [Switch]$LegendBold,
        [Switch]$NoLegend,
        [Switch]$ShowCategory,
        [Switch]$ShowPercent,
        [String[]]$SeriesHeader,
        [Switch]$TitleBold,
        [Int]$TitleSize ,
        [String]$XAxisTitleText,
        [Switch]$XAxisTitleBold,
        $XAxisTitleSize ,
        [string]$XAxisNumberformat,
        $XMajorUnit,
        $XMinorUnit,
        $XMaxValue,
        $XMinValue,
        [OfficeOpenXml.Drawing.Chart.eAxisPosition]$XAxisPosition        ,
        [String]$YAxisTitleText,
        [Switch]$YAxisTitleBold,
        $YAxisTitleSize,
        [string]$YAxisNumberformat,
        $YMajorUnit,
        $YMinorUnit,
        $YMaxValue,
        $YMinValue,
        [OfficeOpenXml.Drawing.Chart.eAxisPosition]$YAxisPosition,
        [Switch]$PassThru
    )
    try {
        if ($PivotTable) {
            $Worksheet = $PivotTable.Worksheet
            $chart = $Worksheet.Drawings.AddChart(("Chart" + $PivotTable.Name ), $ChartType, $PivotTable)
        }
        else {
            $ChartName = 'Chart' + (Split-Path -Leaf ([System.IO.path]::GetTempFileName())) -replace 'tmp|\.', ''
            $chart = $Worksheet.Drawings.AddChart($ChartName, $ChartType)
            $chartDefCount = @($YRange).Count
            if ($chartDefCount -eq 1) {
                $Series = $chart.Series.Add($YRange, $XRange)
                if ($ChartTrendLine) {
                    if ($ChartType -notmatch "stacked|3D$|pie|Doughnut|Cone|Cylinder|Pyramid") {
                        foreach ($trendLine in $ChartTrendLine) {
                            $null = $Series.TrendLines.Add($trendLine)
                        }
                    }
                    else {
                        Write-Warning "Chart trend line is not supported for chart type: $ChartType"
                    }
                }
                if ($SeriesHeader) { $Series.Header = $SeriesHeader }
                else { $Series.Header = 'Series 1' }
            }
            else {
                for ($idx = 0; $idx -lt $chartDefCount; $idx += 1) {
                    if ($Yrange.count -eq $xrange.count) {
                        $Series = $chart.Series.Add($YRange[$idx], $XRange[$idx])
                    }
                    else {
                        $Series = $chart.Series.Add($YRange[$idx], $XRange)
                    }
                    if ($SeriesHeader.Count -gt 0) {
                        if ($SeriesHeader[$idx] -match '^=') { $Series.HeaderAddress = $SeriesHeader[$idx] -replace '^=', '' }
                        else { $Series.Header = $SeriesHeader[$idx] }
                    }
                    else { $Series.Header = "Series $($idx)" }
                }
            }
        }
        if ($Title) {
            $chart.Title.Text = $Title
            if ($TitleBold) { $chart.Title.Font.Bold = $true }
            if ($TitleSize) { $chart.Title.Font.Size = $TitleSize }
        }
        if ($NoLegend) { $chart.Legend.Remove() }
        else {
            if ($PSBoundParameters.ContainsKey('LegendPosition')) { $chart.Legend.Position = $LegendPosition }
            if ($PSBoundParameters.ContainsKey('LegendBold')) { $chart.Legend.Font.Bold = [boolean]$LegendBold }
            if ($LegendSize) { $chart.Legend.Font.Size = $LegendSize }
        }

        if ($XAxisTitleText) {
            $chart.XAxis.Title.Text = $XAxisTitleText
            if ($PSBoundParameters.ContainsKey('XAxisTitleBold')) {
                $chart.XAxis.Title.Font.Bold = [boolean]$XAxisTitleBold
            }
            if ($XAxisTitleSize) { $chart.XAxis.Title.Font.Size = $XAxisTitleSize }
        }
        if ($XAxisPosition) { Write-Warning "X-axis position is not being set propertly at the moment, parameter ignored" }
        #$chart.ChartXml.chartSpace.chart.plotArea.catAx.axPos.val = $XAxisPosition.ToString().substring(0,1)}
        if ($XMajorUnit) { $chart.XAxis.MajorUnit = $XMajorUnit }
        if ($XMinorUnit) { $chart.XAxis.MinorUnit = $XMinorUnit }
        if ($null -ne $XMinValue) { $chart.XAxis.MinValue = $XMinValue }
        if ($null -ne $XMaxValue) { $chart.XAxis.MaxValue = $XMaxValue }
        if ($XAxisNumberformat) { $chart.XAxis.Format = (Expand-NumberFormat $XAxisNumberformat) }
        
        # Fix for category axis (used in line charts, etc.): EPPlus doesn't serialize MajorUnit/MinorUnit to XML for category axes
        # We need to manually add these elements to the XML if they were specified
        if ($XMajorUnit -or $XMinorUnit) {
            $chartXml = $chart.ChartXml
            $nsManager = New-Object System.Xml.XmlNamespaceManager($chartXml.NameTable)
            $null = $nsManager.AddNamespace("c", "http://schemas.openxmlformats.org/drawingml/2006/chart")
            $catAx = $chartXml.SelectSingleNode("//c:catAx", $nsManager)
            
            if ($catAx) {
                # Category axis exists - need to add majorUnit/minorUnit elements
                if ($XMajorUnit) {
                    $existingMajorUnit = $catAx.SelectSingleNode("c:majorUnit", $nsManager)
                    if (-not $existingMajorUnit) {
                        $majorUnitElement = $chartXml.CreateElement("c", "majorUnit", "http://schemas.openxmlformats.org/drawingml/2006/chart")
                        $null = $majorUnitElement.SetAttribute("val", $XMajorUnit)
                        # Insert after scaling element or at the beginning
                        $scalingNode = $catAx.SelectSingleNode("c:scaling", $nsManager)
                        if ($scalingNode) {
                            $null = $catAx.InsertAfter($majorUnitElement, $scalingNode)
                        } else {
                            $null = $catAx.PrependChild($majorUnitElement)
                        }
                    } else {
                        $null = $existingMajorUnit.SetAttribute("val", $XMajorUnit)
                    }
                }
                
                if ($XMinorUnit) {
                    $existingMinorUnit = $catAx.SelectSingleNode("c:minorUnit", $nsManager)
                    if (-not $existingMinorUnit) {
                        $minorUnitElement = $chartXml.CreateElement("c", "minorUnit", "http://schemas.openxmlformats.org/drawingml/2006/chart")
                        $null = $minorUnitElement.SetAttribute("val", $XMinorUnit)
                        # Insert after majorUnit if it exists, otherwise after scaling
                        $majorUnitNode = $catAx.SelectSingleNode("c:majorUnit", $nsManager)
                        if ($majorUnitNode) {
                            $null = $catAx.InsertAfter($minorUnitElement, $majorUnitNode)
                        } else {
                            $scalingNode = $catAx.SelectSingleNode("c:scaling", $nsManager)
                            if ($scalingNode) {
                                $null = $catAx.InsertAfter($minorUnitElement, $scalingNode)
                            } else {
                                $null = $catAx.PrependChild($minorUnitElement)
                            }
                        }
                    } else {
                        $null = $existingMinorUnit.SetAttribute("val", $XMinorUnit)
                    }
                }
            }
        }

        if ($YAxisTitleText) {
            $chart.YAxis.Title.Text = $YAxisTitleText
            if ($PSBoundParameters.ContainsKey('YAxisTitleBold')) {
                $chart.YAxis.Title.Font.Bold = [boolean]$YAxisTitleBold
            }
            if ($YAxisTitleSize) { $chart.YAxis.Title.Font.Size = $YAxisTitleSize }
        }
        if ($YAxisPosition) { Write-Warning "Y-axis position is not being set propertly at the moment, parameter ignored" }
        #$chart.ChartXml.chartSpace.chart.plotArea.valAx.axPos.val= $YAxisPosition.ToString().substring(0,1)}
        if ($YMajorUnit) { $chart.YAxis.MajorUnit = $YMajorUnit }
        if ($YMinorUnit) { $chart.YAxis.MinorUnit = $YMinorUnit }
        if ($null -ne $YMinValue) { $chart.YAxis.MinValue = $YMinValue }
        if ($null -ne $YMaxValue) { $chart.YAxis.MaxValue = $YMaxValue }
        if ($YAxisNumberformat) { $chart.YAxis.Format = (Expand-NumberFormat $YAxisNumberformat) }
        if ($null -ne $chart.Datalabel) {
            $chart.Datalabel.ShowCategory = [boolean]$ShowCategory
            $chart.Datalabel.ShowPercent = [boolean]$ShowPercent
        }

        $chart.SetPosition($Row, $RowOffsetPixels, $Column, $ColumnOffsetPixels)
        $chart.SetSize($Width, $Height)

        if ($PassThru) { return $chart }
    }
    catch { Write-Warning -Message "Failed adding Chart to worksheet '$($Worksheet).name': $_" }
}