PowerShell to Generate Excel

This topic contains 8 replies, has 7 voices, and was last updated by  Fredrik Kacsmarck 1 week, 5 days ago.

  • Author
    Posts
  • #97866

    Srinu
    Participant

    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

  • #97869

    Olaf Soyk
    Participant

    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.

    • #97909

      Srinu
      Participant

      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

  • #97873

    postanote
    Participant

    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.

    'blogs.technet.microsoft.com/heyscriptingguy/2015/11/25/introducing-the-powershell-excel-module-2'

    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.

    'blogs.technet.microsoft.com/heyscriptingguy/2013/04/03/excel-spreadsheets'

    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'

  • #97875

    Srinu
    Participant

    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

  • #97879

    Joel Sallow
    Participant

    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.

  • #97896

    iain Barnetson
    Participant
  • #98043

    Mike White
    Participant

    Very informative. Also in case you have generated a CSV, you use Online free converter to convert CSV to XLS (onlineconvertfree.com/convert-format/csv-to-xls/. That takes less than few seconds. Alternatively Powershell can also be used for the same.

  • #98167

    Fredrik Kacsmarck
    Participant

    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.

You must be logged in to reply to this topic.