Delete worksheet from excel workbook using powershell-CodePudding

Below is the code I have which is creating graph and saving the graph as image

$excel = New-Object -ComObject excel.application

$outputXLSX = "C:\report_10_02.xlsx"
$data = Import-Excel -Path $outputXLSX

$cd = New-ExcelChartDefinition -XRange Name -YRange Count -ChartType ColumnStacked3D -Height 300 -Title "Latency Count" -Width 1000 -SeriesHeader Count
$data | Export-Excel $outputXLSX -ExcelChartDefinition $cd -AutoNameRange -WorksheetName "Sheet2" 
$macros_wb = $excel.Workbooks.open($outputXLSX)

$chart_worksheets = @("Sheet2")

$OutputType = "JPG"

foreach ($item in $chart_worksheets)
    {
        $macros_ws = $macros_wb.WorkSheets.item($item)
        $macros_ws.activate()
        $excelchart = $macros_ws.ChartObjects(1)
        $Excel.Goto($excelchart.TopLeftCell,$true) 
        $ImagePath = "C:\Imagee.jpg"
        if ($excelchart.Chart.Export($ImagePath, $OutputType)) #Export returns true/false for success/failure
            {Write-Output "Exported $ImagePath"}
        else
            {Write-Output "Failure Exporting $ImagePath"}
    }


$WorkSheet = $macros_wb.sheets.item($chart_worksheets)
#Deleting the worksheet
$WorkSheet.Delete()
#Saving the worksheet
$macros_wb.Save()
$macros_wb.close($true)
$excel.Quit()

The excel workbook is having 2 worksheet in which I want to delete Sheet2. I tried Delete() but it is not deleting the sheet.

Please let me know what is wrong here

CodePudding user response：

Ok, so you have an Excel file containing a chart you want to export to a JPG file and afterwards delete the worksheet containing that chart.

Using COM objects you can do this like so:

$outputXLSX      = "C:\report_10_02.xlsx"
$chart_worksheet = "Sheet2"
$ImagePath       = "C:\Imagee.jpg"
$OutputType      = "JPG"

$excel = New-Object -ComObject excel.application
$excel.DisplayAlerts = $false
$macros_wb = $excel.Workbooks.open($outputXLSX)
$macros_ws = $macros_wb.WorkSheets.item($chart_worksheet)
$macros_ws.activate()
$excelchart = $macros_ws.ChartObjects(1)
$excel.Goto($excelchart.TopLeftCell,$true) 
if ($excelchart.Chart.Export($ImagePath, $OutputType)) {Write-Host "Exported $ImagePath"}
else {Write-Warning "Failure Exporting $ImagePath"}

$macros_ws.Delete()
#Saving the worksheet
$macros_wb.Save()
$macros_wb.Close($true)

$excel.Quit()
# important, remove the used COM objects from memory
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($macros_ws)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($macros_wb)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

CodePudding user response：

I'm not sure why are you using ComObject if you already have the ImportExcel Module installed, it doesn't require manipulation with ComObject. Here is how you can remove a Worksheet from an Excel file:

$path = 'path/to/excelfile.xlsx'
$workSheetToRemove = 'worksheetName'
Remove-Worksheet -WorksheetName $workSheetToRemove -FullName $path

If you're not sure what's the name of the Worksheet you want to remove, you use:

Get-ExcelFileSummary $path