PowerShell script run from c# app

Jul 29 2015 9:25 AM

Hi - I have an application written in c# that executes PowerShell scripts.
It performs well except when I try to write out database query results to an Excel worksheet. I am not sure at all what is preventing this.

Using debug statements I am able to create the Excel application and workbook. The worksheet is renamed correctly, but the Paste and Save have no effect.

Here is what I have in c# code:
                  
            Pipeline pipeline = null;
            StringBuilder results = new StringBuilder();
                //make sure file exists before trying to run it
                System.IO.FileInfo fi = new System.IO.FileInfo(scriptfile);//path to script

                if (!fi.Exists)
                {
                    string smsg = string.Format("Script file {0} does not exist.\r\n\r\n Action can't be completed.",scriptfile);

                    MessageBox.Show(smsg, Application.ProductName);
                    return;
                }               
              
                using (new sb.Impersonator("username", "domain", "password"))
                {
                    RunspaceConfiguration config = RunspaceConfiguration.Create();

                    //create powershell runspace
                    Runspace cmdlet = RunspaceFactory.CreateRunspace(config);

                    cmdlet.ApartmentState = System.Threading.ApartmentState.STA;
                    cmdlet.ThreadOptions = PSThreadOptions.UseCurrentThread;

                    cmdlet.Open();

                    RunspaceInvoke scriptInvoker = new RunspaceInvoke(cmdlet);

                    scriptInvoker.Invoke("Set-ExecutionPolicy RemoteSigned -Scope CurrentUser");

                    // create a pipeline and load it with command object
                    pipeline = cmdlet.CreatePipeline();

                    Command cmd = new Command(scriptfile);

                    if (paramList.Length > 0)
                    {
                        foreach (object prm in paramList)
                        {
                            cmd.Parameters.Add(prm.ToString());
                        }
                    }

                    pipeline.Commands.Add(cmd);

                    pipeline.Commands.Add("Out-String");

                    // this will format the output
                    IEnumerable<PSObject> output = pipeline.Invoke();

                    // process each object in the output and append to stringbuilder 
                    foreach (PSObject obj in output)
                    {
                        results.AppendLine(obj.ToString());
                    }
                }

 ------------------------------------------------------------------------

I can run the same PowerShell script  in either PowerShell or in Quest PowerGui and have it perform as expected.
I typically only write out data in PowerShell. The snippet that follows is often the type of thing I want to be able to do in the scripts run by the c# app.

$app = New-Object -ComObject Excel.Application

$app.Visible = $false

$wb = $app.Workbooks.Add()


$ws1 = $wb.WorkSheets.Item(1)
$ws1.Name = "File Audit"

//$clpbrdStr contains the accumulated results I want to put in sheet
[System.Windows.Forms.Clipboard]::SetText($clpbrdStr)
$rng = $ws1.Range("A1" )

$wb.ActiveSheet.Paste($rng, $false)


I go on and save the workbook and dispose of the application and workbook when done...
I don't seem to get error messages coming back in the results variable.

One other thing to note is that I run the c# code in a BackGroundWorker thread.

Do I need to import something into my c# app or via the PowerShell script itself to get Excel automation to work? Is it a permissions issue?

Thanks much for any insight.
 

Answers (1)