Possible to have data and charts on separate worksheets?

[Catalyst8487]

I'm working with one of the examples, MultipleCharts.ps1...

#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message  "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore

$data = @"
ID,Product,Quantity,Price,Total
12001,Nails,37,3.99,147.63
12002,Hammer,5,12.10,60.5
12003,Saw,12,15.37,184.44
12010,Drill,20,8,160
12011,Crowbar,7,23.48,164.36
"@

$c1 = New-ExcelChartDefinition -YRange "Price"   -XRange "Product" -Title "Item price"   -NoLegend -Height 225
$c2 = New-ExcelChartDefinition -YRange "Total   "-XRange "Product" -Title "Total sales"  -NoLegend -Height 225 -Row 9  -Column 15
$c3 = New-ExcelChartDefinition -YRange "Quantity"-XRange "Product" -Title "Sales volume" -NoLegend -Height 225 -Row 15

$data | ConvertFrom-Csv | Export-Excel -Path  $xlSourcefile -AutoFilter -AutoNameRange -AutoSize -ExcelChartDefinition $c1,$c2,$c3  -Show

This creates a single worksheet w/ both the data and the charts. I'd like to separate these out with the data on one sheet and charts on a separate - My real dataset is much larger with about two dozen columns and hundreds of rows... Dropping charts onto the same worksheet would not be ideal since they would be way far to the right, way down at the bottom, or on top of the data.

[dfinke]

Try this:

$xlSourcefile = "$PSScriptRoot\spike.xlsx"
Remove-Item $xlSourcefile -ErrorAction Ignore

$data = ConvertFrom-Csv @"
ID,Product,Quantity,Price,Total
12001,Nails,37,3.99,147.63
12002,Hammer,5,12.10,60.5
12003,Saw,12,15.37,184.44
12010,Drill,20,8,160
12011,Crowbar,7,23.48,164.36
"@

$excel = $data | Export-Excel -Path $xlSourcefile -AutoFilter -AutoNameRange -AutoSize -PassThru

$null = Add-Worksheet -ExcelPackage $excel -WorksheetName "Charts" -Activate

Add-ExcelChart -Worksheet $excel.Charts -YRange "Sheet1!Price" -XRange "Sheet1!Product" -Title "Item price" -NoLegend -Height 225
Add-ExcelChart -Worksheet $excel.Charts -YRange "Sheet1!Total" -XRange "Sheet1!Product" -Title "Total sales" -NoLegend -Height 225 -Row 9 -Column 15
Add-ExcelChart -Worksheet $excel.Charts -YRange "Sheet1!Quantity" -XRange "Sheet1!Product" -Title "Sales volume" -NoLegend -Height 225 -Row 15

Close-ExcelPackage $excel -Show

[Catalyst8487]

Thank you @dfinke! The "Sheet1!" part on XRange and YRange was what I was missing. I figured it had to be something simple.

[dfinke]

@Catalyst8487 you're welcome. That is Excel "majik" not intuitive if you haven't played with Excel for a while.

Also, you probably saw, this doesn't use an excel definition passed to Export-Excel. You need to create sheet and then Add-ExcelChart. The New-ExcelChartDefinition only writes to the current sheet.