PowerShell to Generate Excel

PowerShell Help
1

Hi,

Can you please help me with below requirement in PowerShell.

Input for Power shell script is – Excel File which consists single column , column name is EmpName.
Sample values are
AARON, JEFFERY
AARON, KARINA
ABBATE, JOSEPH L
ABBATEMARCO, JAMES J
ABDALLAH, ZAID

Output is: Create a new excel file with 2 columns EmpName and EmpShortName. Here Name columns values are same as input file and ShortName value are first char of first name and all characters of last name.
Example:
EmpName EmpShortName
AARON, JEFFERY jaaron
AARON, KARINA kaaron
ABBATE, JOSEPH L jabbate
ABBATEMARCO, JAMES J jabbatemarco
ABDALLAH, ZAID zabdallah

2

Srinu,

hi and welcome to the Powershell.org forum.

This forum is more for scripting questions rather than script requests. If you already wrote some code you can post this code here (formatted as code please) and we will be happy to try to help you with this. But we do not write ready to use code on request, sorry.

3

Ditto to what Olaf has stated.

So, if you have not looked at the available data points on PowerShell when using Excel to come up with code that you may need help with, a quick web search would point you to such help, to get you started. For example.

Introducing the PowerShell Excel Module

The PowerShell Excel Module is a brand new, exciting, and better way to interact with Microsoft Excel from Windows PowerShell. Plus for bonus points, you don’t need Excel installed on the target machine to create the spreadsheet. Many users of this module generate Excel spreadsheets on servers, and then others in the company pick up the reports from a central server. Or for the last step in their script, they can mail the .xlsx file.

‘Introducing the PowerShell Excel Module - Scripting Blog [archived]’

Excel Spreadsheets

Creating a spreadsheet

Creating an Excel spreadsheet should be a simple act, in theory. But if you don’t happen to be in the U.S., there’s a slight issue in the shape of a bug in versions of Excel 2007 and earlier that can prevent this from working. After reading this, it won’t matter where you live. If you’re using Excel 2010, the first version in Listing 1 can be used wherever you live and work.

Problem

We need to create an Excel spreadsheet from within a Windows PowerShell script.

Solution

The Excel.application COM object can be used to create a spreadsheet.

‘Excel Spreadsheets - Scripting Blog [archived]’

PowerShell: Generate Real Excel XLSX Files without Excel

Problem Exploration

I had the need to store data into a Microsoft Excel compatible file.

Attempt 1: Use the Excel COM object model.
This is not a good solution because:
PowerShell runs very often on Servers or clients without a Microsoft Office / Excel installation.
The use of the Excel COM Object can cause errors inside a Scheduled Task.

Excel can read and store CSV data.

Attempt 2: Use CSV data (with Export-CSV)
This is not a good solution either because:
CSV is not just another type of Excel file. On opening a CSV data file, Microsoft Excel converts data automatically. This is not acceptable.
If Microsoft Excel outputs an Excel worksheet into a CSV file, the output does not always follow the CSV format rules. Excel only places quotes around certain fields not on all fields. This leads to unreadable CSV files.

‘social.technet.microsoft.com/wiki/contents/articles/19601.powershell-generate-real-excel-xlsx-files-without-excel.aspx’

4

I tried this

Specify the path to the Excel file and the WorkSheet Name

$FilePath = “D:\2018\Book2.xlsx”
$SheetName = “Sheet1”

Create an Object Excel.Application using Com interface

$objExcel = New-Object -ComObject Excel.Application

Disable the ‘visible’ property so the document won’t open in excel

$objExcel.Visible = $false

Open the Excel file and save it in $WorkBook

$WorkBook = $objExcel.Workbooks.Open($FilePath)

Load the WorkSheet ‘Sheet1’

$WorkSheet = $WorkBook.sheets.item($SheetName)

[pscustomobject][ordered]@{
EmpName = $WorkSheet.Range(“A1”).Text

But getting error in the last line. Once it worked need to write a logic get another column form EmpName column

5

As this is a fairly simple dataset, I highly recommend simply exporting the sheet from Excel as a CSV file instead. That way, you can quickly and easily import all the data with the Import-Csv cmdlet and parse the data from there.

There is no need to overcomplicate this by delving into Excel COM Objects.

6

I use importexcel

7

Below logic is working as expected. Please let me know if there is any better way of doing the same.

Specify the path to the Excel file and the WorkSheet Name

$FilePath = “D:\2018\Book2.xlsx” # Update source File Path
$SheetName = “Sheet1”

Create an Object Excel.Application using Com interface

$objExcel = New-Object -ComObject Excel.Application

Disable the ‘visible’ property so the document won’t open in excel

$objExcel.Visible = $false

Open the Excel file and save it in $WorkBook

$WorkBook = $objExcel.Workbooks.Open($FilePath)

Load the WorkSheet ‘BuildSpecs’

$WorkSheet = $WorkBook.sheets.item($SheetName)

$Worksheet.Range(‘A1’).CurrentRegion | Where-Object {
$.text -notmatch “EmpName”
} | ForEach-Object {
$name = $.text
$split = $name -split ", "
[pscustomobject]@{
“EmpName” = $name
“EmpShortName” = $split[1][0]+$split[0]
}
}|Export-Csv -NoTypeInformation employees_updated.csv # Update New File Name

8

Very informative. Also in case you have generated a CSV, you use Online free converter to convert CSV to XLS (Convert CSV to XLS online, free .CSV to .XLS changing. That takes less than few seconds. Alternatively Powershell can also be used for the same.

9

Another alternative is using EPPlus .NET library directly.
ImportExcel is using the same library and it’s free to download.
Didn’t need all the functions in ImportExcel and IIRC there was some things (e.g. validation fields) that were not implemented at the time but was available in the .NET library.

If you want to try it without using ImportExcel I wrote a blog post about it.

http://psfredrik.chiloma.com/2016/10/26/creating-excel-files-with-powershell-and-epplus/

However, if the dataset is simple and not a lot of logic or re-iterations then I would just create a .csv file and then convert it to Excel.
Otherwise, the ImportExcel module or using EPPlus directly.