Powershell and C# : add a parameter script in a pipeline

Question

I started learning PowerShell and C# recently. I would like to use it in a web app with visual studio.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Management.Automation;
using System.Management.Automation.Runspaces;
using System.Collections.ObjectModel;


namespace Extraction_test.Controllers
{
    public class HomeController : Controller
    {
               public string Index() //PowershellExtract()
        {
            Runspace runspace = RunspaceFactory.CreateRunspace();
            runspace.Open();
            Pipeline pipeline = runspace.CreatePipeline();
            pipeline.Commands.AddScript(@"$filepath=""C:\Users\myusernamefolder\Downloads\ReadExcel\Test.xlsx"";");
            pipeline.Commands.Add("Param([string]$filepath;)");
            string scriptpath = "[System.Threading.Thread]::CurrentThread.CurrentCulture = [System.Globalization.CultureInfo] \"en-US\";" +
                    "$Excel = New-Object -ComObject Excel.application;" +
                    "$Excel.visible=$True;" +
                    "$Workbook=$Excel.Workbooks.open($filepath);" +
                    "$Worksheet=$WorkbookSheets.item(\"Feuil1\");" +
                    "$Worksheet.activate();" +
                    "$col = 4;" +
                    "$lines = @(5,6,7,9);" +
                    "foreach ($line in $lines) { $global:output = $Worksheet.Cells.Item($line,$col).Value(); }" +
                    "$global:test='test string value'; " +
                    "$workbook.Close();" +
                    "$Excel.Quit();";
            pipeline.Commands.AddScript(scriptpath);
            Collection<PSObject> results = pipeline.Invoke();
            var result = runspace.SessionStateProxy.PSVariable.GetValue("test");
            //var result = runspace.SessionStateProxy.PSVariable.GetValue("test");
            return result.ToString();

        }
    }
}

However, I couldn't find anywhere a way to add a parameter script in a pipeline

pipeline.Commands.Add("Param([string]$filepath;)");

Thanks in advance!

Also, my main goal is to extract the $output in the return. But at first, I am testing with $test — UserToulouse31
– UserToulouse31, Commented Mar 2, 2018 at 7:40
However, with PowerShell, I didn't find any solutions to retrieve my result without runspace and pipeline. I found that I had to use var result = runspace.SessionStateProxy.PSVariable.GetValue("test"); Unless there is a way to retrieve the result with Powershell and runspace — UserToulouse31
– UserToulouse31, Commented Mar 2, 2018 at 8:20
I tried it but I have this result System.Collections.ObjectModel.Collection1[System.Management.Automation.PSObject] Is there a way to specify which output I would like to return? For example, $test — UserToulouse31
– UserToulouse31, Commented Mar 2, 2018 at 10:30

Niels · Accepted Answer · 2018-03-02 11:54:32Z

2

if you create the Command object separately, you can add a CommandParameter to it:

Pipeline pipeline = runspace.CreatePipeline();    
string scriptFile = Path.Combine(ScriptDir, scriptpath);
Command scriptCommand = new Command(scriptFile);

CommandParameter commandParm = new CommandParameter("name", "value");
scriptCommand.Parameters.Add(commandParm);
pipeline.Commands.Add(scriptCommand);
pipeline.Invoke()

answered Mar 2, 2018 at 11:54

Niels

1,0759 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

UserToulouse31 Over a year ago

Thank you for the answer. I implemented it in my code but how do you define ScriptDir? Visual Studio doesn't seem to recognize it.

Niels Over a year ago

That would be a string that holds the path to the script: string ScriptDir="C:\\scripts". scriptpath would then be the name, like string scriptpath= "pizza.ps1". Sorry for the confusing variable names.

Collectives™ on Stack Overflow

Powershell and C# : add a parameter script in a pipeline

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related