Excel automation with RepoDoc and PowerShell
With RepoDoc it’s easy to generate CSV files from your repository data and by using RepoDoc’s generator profiles it’s possible to open these files as Excel workbooks.
Following template outputs a list of classes from the repository together with package name, element name and element stereotype.
PackageName,ElementName,ElementStereotype
[Package]
[Element $Element.Type=="Class"]
$Package.Name,$Element.Name,$Element.Stereotype
[/Element]
[/Package]
The PowerShell script below takes the generated CSV file and converts it to an Excel workbook and afterwards it opens the workbook.
$global:xl=$null;
$global:wb=$null;
try
{
[System.Threading.Thread]::CurrentThread.CurrentCulture=[System.Globalization.CultureInfo]'en-US';
$csvpath='$DG.DocumentFilePath';
$xl=New-Object -com "Excel.Application";
$wb=$xl.workbooks.open($csvpath);
$xlsxpath=$csvpath.Replace('.csv','.xlsx');
$wb.SaveAs($xlsxpath,51);
$xl.Quit();
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb);
$wb=$null;
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl);
$xl=$null;
$xl=New-Object -com "Excel.Application";
$wb = $xl.Workbooks.Open($xlsxpath);
$xl.Visible = $true;
}
catch
{
if ($wb -ne $null)
{
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb);
}
if ($xl -ne $null)
{
$xl.Quit();
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl);
}
Write-Host $_.Exception.Message;
return -1;
}
In RepoDoc, you need to create a new generator profile. Enter powershell as the command and use the following one liner as arguments.
-ExecutionPolicy Bypass -Command "$global:xl=$null; $global:wb=$null; try { [System.Threading.Thread]::CurrentThread.CurrentCulture=[System.Globalization.CultureInfo]'en-US'; $csvpath='$DG.DocumentFilePath'; $xl=New-Object -com "Excel.Application"; $wb=$xl.workbooks.open($csvpath); $xlsxpath=$csvpath.Replace('.csv','.xlsx'); $wb.SaveAs($xlsxpath,51); $xl.Quit(); [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb); $wb=$null; [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl); $xl=$null; $xl=New-Object -com "Excel.Application"; $wb = $xl.Workbooks.Open($xlsxpath); $xl.Visible = $true; } catch {if ($wb -ne $null) {[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb);} if ($xl -ne $null) {$xl.Quit(); [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl);} Write-Host $_.Exception.Message; return -1;}"
The generator profile configuration and a workbook example are pictured below.
