Formating colors and styles during Export-Excel

[Ntinsky]

Greetings,

First of all congrats for the great work.
I have been playing with the cmdlet for a while now and i was able to write a PS script that will make use of the Import-Excel in order to create some domain users

I have a standard template where i put info there as shown in the picture. The template has the formatting shown in the picture below

I convert this file to CSV, process it's data which create a new domain user/s based on the column data. The first row of the file act as headers for the script

I then update the CSV file with the randomly generated password for each user, created the user account/s
and i then convert it back to .xlsx fornat by using the Export-Excel cmdlet

The output file though has no formatting for every columns which makes sense of course.

Is it possible to set the same formatting for that file, like in the original file (1st picture)?

Thank you in advance and keep up the great work

Kind Regards

[dfinke]

Thanks for trying ImportExcel @Ntinsky.

You need to set the coloring and formatting when you set it back to the xlsx. It does not know abt the template or formatting.

Check out Set-ExcelRange. When you do the Export-Excel you can use -PassThru this lets you do more granular work on the workbook/sheets etc. You need to Close-ExcelPackage to save the changes.

Off the top of my head, may not be 💯

$xlpkg = $data | Export-Excel -PassThru

Set-ExcelRange -Worksheet $xlpkg.Sheet1 -BackgroundColor Yellow -Range "A1"

Close-ExcelPackage $xlpkg 

[Ntinsky]

Thank you for the feedback. It was a bit tricky with the parameters but it worked