I pull a lot of data out of PPM via web services. I normally use either C# or Java depending on the client's need.
When I looked at this post, I was thinking I would just toss up some of my code to give you an example on how to get PPM data into Excel. But if you aren't a developer or have access to a Java/C# compiler, that might not be to useful!
Instead I tossed together a simple script to demonstrate how to do it. Since I have to use PowerShell at one of my client, I chose to use it (not really fond of PowerShell but it can do some pretty heavy lifting).
The script is dependent on ClosedXML which has a dependence on Microsoft Open Document SDK. These dll need to be in the same directory as the script -- unless you change the path in the script.
The script flow is:
- Load the DLLs
- Create a new spreadsheet in memory.
- Login to PPM
- Run a NSQL Query
- Loop through the results writing them to the spreadsheet
- Save the spreadsheet.
One needs to update the $url to their instance, along with a username and password.
V/r,
Gene
try
{
[Reflection.Assembly]::LoadFile("$PSScriptRoot\ClosedXML.dll");
[Reflection.Assembly]::LoadFile("$PSScriptRoot\DocumentFormat.OpenXml.dll");
$workBook = new-object ClosedXML.Excel.XLWorkbook;
$workSheet = $workBook.Worksheets.Add("QueryData");
$url = "https:
$proxy = New-WebServiceProxy $url
$type = $proxy.GetType().Namespace
$loginType = ($type + '.Login');
$login = new-object ($loginType);
$authType = ($type + '.Auth');
$auth = new-object ($authType);
$queryType = ($type + '.test_last_loggedQuery');
$query = New-Object($queryType);
$query.Code = 'test_last_logged';
$login.Username = "admin";
$login.Password = "password";
$auth.SessionID = $proxy.Login($login);
$proxy.AuthValue = $auth;
$queryResult = $proxy.Query($query);
$column = 1;
$row = 2;
foreach ($record in $queryResult.Records) {
$recordType = $record.GetType();
$recordProperties = $recordType.GetProperties();
foreach ($property in $recordProperties) {
if ($row -eq 2) {$workSheet.Cell(1, $column).Value = $property.Name;}
$workSheet.Cell($row, $column++).Value = $property.GetValue($record, $NULL);
}
$column = 1;
$row++;
}
If (Test-Path "$PSScriptRoot\test.xlsx") {Remove-Item "$PSScriptRoot\test.xlsx";}
$workBook.SaveAs("$PSScriptRoot\test.xlsx");
}
catch [Exception]
{
Write-Host $_.Exception.Message;
exit 1
}