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.