How to append multiple sheets on a excel file(.xlsx format) in Powershell?

Question

_{Monday, October 28, 2019 1:01 PM}

Currently I have a script in which I generate reports and save it in an .xlsx format. But my problem is when I try to generate a second report and onward, it overwrites the entire report instead of appending to it as a second sheet and onwards. Anyways Ill show you guys a very partial script.

$excel2 = New-Object -ComObject excel.application;
$excel2.visible=$false;
$serverInfoSheet= $excel2.Workbooks.Open("C:\Temp\Tasks[$i].csv"); # i is just a number starting from 1, I have the counter already in my full script
$serverInfoSheet.Activesheet.Cells.EntireColumn.Autofit();
$ServerInfosheet.SaveAs("C:\Temp\report.xlsx",51) 
$excel2.Quit()

How can I modify so that the report accepts multiple sheets?

All replies (3)

_{Monday, October 28, 2019 2:53 PM}

Hi,

   Use below script block to export CSV data into excel file

$csvs = Get-ChildItem "C:\Temp\*.CSV"
$excelapp = new-object -comobject Excel.Application
$excelapp.sheetsInNewWorkbook = $csvs.Count
$xlsx = $excelapp.Workbooks.Add()
$sheet = 1
foreach ($csv in $csvs) {
    $row = 1
    $column = 1
    $worksheet = $xlsx.Worksheets.Item($sheet)
    $worksheet.Name = $csv.Name
    $file = (Get-Content $csv)
    foreach ($line in $file) {
        $linecontents = $line -split ',(?!\s*\w+")'
        foreach ($cell in $linecontents) {
            $worksheet.Cells.Item($row, $column) = $cell
            $column++
        }
        $column = 1
        $row++
    }
    $sheet++
}
$xlsx.SaveAs("C:\Temp\report.xlsx",51)
$excelapp.quit()

you can change the CSV files path. if you want to use files like  Tasks[$i].csv then use file name in Get-Content command.

For Reference https://stackoverflow.com/questions/49324636/multiple-csv-files-into-a-xlsx-file-but-different-sheets-using-powershell

_{Monday, October 28, 2019 5:35 PM}

Currently I have a script in which I generate reports and save it in an .xlsx format. But my problem is when I try to generate a second report and onward, it overwrites the entire report instead of appending to it as a second sheet and onwards. Anyways Ill show you guys a very partial script.

$excel2 = New-Object -ComObject excel.application;
$excel2.visible=$false;
$serverInfoSheet= $excel2.Workbooks.Open("C:\Temp\Tasks[$i].csv"); # i is just a number starting from 1, I have the counter already in my full script
$serverInfoSheet.Activesheet.Cells.EntireColumn.Autofit();
$ServerInfosheet.SaveAs("C:\Temp\report.xlsx",51) 
$excel2.Quit()

How can I modify so that the report accepts multiple sheets?

Just add a sheet to the workbook and insert the CSV.

Here is an example script:

$missing = [System.Reflection.Missing]::Value
$inputfolder = 'c:\test'
$mergedwb = 'c:\test\mytest.xlsx'
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $true
$wb1 = $xl.WorkBooks.add()

Get-ChildItem $inputfolder\*.csv |
    ForEach-Object{
        Write-Host "Moving $_" -ForegroundColor green
        $xl.WorkBooks.OpenText($_)
        $wb2 = $xl.Workbooks.Item($_.Name)
        $wb2.Sheets.Item($_.Basename).Move($missing,$wb1.Sheets.Item($wb1.Sheets.count))
    }

$wb1.Sheets.Item('sheet1').Delete()
$wb1.SaveAs($mergedwb)
$wb1.Close()
$xl.Quit()

\(ツ)_/

_{Friday, November 8, 2019 1:07 PM}

Hi,

Was your issue resolved?

If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

If no, please reply and tell us the current situation in order to provide further help.

Best Regards,

Lee

Please remember to mark the replies as answers if they help.
If you have feedback for TechNet Subscriber Support, contact [email protected].