locked
ADD-WorkSheet Excel RRS feed

  • Question

  • Import-Module ImportExcel $oArray01 = @() For ($i=0; $i -le 10; $i++) { $i $NewObject01 = new-object PSObject $NewObject01 | add-member -membertype NoteProperty -name "Number" -Value $i $oArray01 += $NewObject01 $oArray01 | Export-Excel -Path "C:\temp\MyExcel.xlsx" }

    Add-workSheet fails

    I use powershell V5, the above code works fine. How do I add another workSheet and on cell(1,1)="test"?

    How to auto fit columns?

    I hope I am clear

    Thursday, September 6, 2018 2:49 AM

Answers

  • #Just run the on powershell with Excel module and you would get requested what I want
    $AllNumberProperties=@()
    For ($i=0; $i -le 10; $i++) {
        $NumberProperties = @{
    				# I am doing thro custom object because in my case it is not a straight Select statement and export
    				myNumber = $i
    						}
    	
    	$AllNumberProperties+=New-Object PSObject –Property  $NumberProperties
    
        }
    	
    $AllNumberProperties | Select myNumber | Export-Excel -AutoSize -Path "C:\test\myNumber.xlsx" -WorkSheetname Number
    
    Import-Excel -Path "C:\test\myNumber.xlsx" -WorksheetName "Number" |  Export-Excel -Path "C:\test\ExportNumber.xlsx" -WorkSheetname Number
    $xl2 = Export-Excel -Path "C:\test\ExportNumber.xlsx" -PassThru
    # I am not able to add worksheet to Existing Excel but I can export and able to do this
    $xl2.Workbook.Worksheets.Add("Details")
    $xl2.Workbook.Worksheets.Delete("Sheet1")
    $Sheet = $xl2.Workbook.Worksheets["Details"]
    # I am able to write to a cell
    $sheet.Cells["A1"].value = "test"
    Close-ExcelPackage $xl2
    You guys are not usefull

    • Marked as answer by asif300 Sunday, September 9, 2018 5:06 PM
    Sunday, September 9, 2018 2:15 AM

All replies

  • You are asking a collection of questions and have no code that uses the module referenced.  I suggest reading the module documentation to learn how the commands work.

    The code you have posted uses no excel commands except trying to export nothing.

    Before trying to use advanced modules you will need to learn PowerShell.  Once you have learned basic PowerShell you will be able to understand why your code is useless and cannot be modified to help you.


    \_(ツ)_/

    Thursday, September 6, 2018 3:14 AM
  • Start here for a tutorial on importexcel module:

    https://www.youtube.com/watch?v=U3Ne_yX4tYo&list=PL5uoqS92stXioZw-u-ze_NtvSo0k0K0kq


    \_(ツ)_/

    Thursday, September 6, 2018 3:17 AM
  • I have this final script working fine. I am using Powershell V5 Excel Module

    $AllLicenseProperties | Select FirstName, LastName, Department, UserPrincipalName, AccountSkuID, Lic | Export-Excel -AutoSize -Path "C:\scripts\LicenseFinal.xlsx" -WorkSheetname License

    Now I wanted to add a worksheet called details (LicenseFinal.xlsx) and on A1 cell write "Test"

    I went on help add-worksheet and set-column... couldn't find answer

    Please help

    • Merged by jrv Saturday, September 8, 2018 3:36 AM DUPLICATE
    Saturday, September 8, 2018 3:26 AM
  • You cannot add a worksheet to a cell.


    \_(ツ)_/

    Saturday, September 8, 2018 3:33 AM
  • #Just run the on powershell with Excel module and you would get requested what I want
    $AllNumberProperties=@()
    For ($i=0; $i -le 10; $i++) {
        $NumberProperties = @{
    				# I am doing thro custom object because in my case it is not a straight Select statement and export
    				myNumber = $i
    						}
    	
    	$AllNumberProperties+=New-Object PSObject –Property  $NumberProperties
    
        }
    	
    $AllNumberProperties | Select myNumber | Export-Excel -AutoSize -Path "C:\test\myNumber.xlsx" -WorkSheetname Number
    
    Import-Excel -Path "C:\test\myNumber.xlsx" -WorksheetName "Number" |  Export-Excel -Path "C:\test\ExportNumber.xlsx" -WorkSheetname Number
    $xl2 = Export-Excel -Path "C:\test\ExportNumber.xlsx" -PassThru
    # I am not able to add worksheet to Existing Excel but I can export and able to do this
    $xl2.Workbook.Worksheets.Add("Details")
    $xl2.Workbook.Worksheets.Delete("Sheet1")
    $Sheet = $xl2.Workbook.Worksheets["Details"]
    # I am able to write to a cell
    $sheet.Cells["A1"].value = "test"
    Close-ExcelPackage $xl2
    You guys are not usefull

    • Marked as answer by asif300 Sunday, September 9, 2018 5:06 PM
    Sunday, September 9, 2018 2:15 AM