Is there any way to specify the indent of text in a cell?

[iambic69]

ImportExcel has heaps of options to control formatting, but indent seems to be missing, or am I the one missing something?

[dfinke]

$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@

$xlfile = ".\test.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue

$xlpkg = $data | Export-Excel $xlfile -PassThru

Set-ExcelRange -Worksheet $xlpkg.Sheet1 -Range 'A:A' -HorizontalAlignment Center
Set-ExcelRange -Worksheet $xlpkg.Sheet1 -Range 'B:B' -HorizontalAlignment Right

Close-ExcelPackage $xlpkg -Show

Horizontal Alignment Options

[iambic69]

Thanks for your swift reply, Doug. That covers cell alignment, but what I'm looking for is cell indent (set using the Increase/Decrease Indent buttons in the Excel ribbon). Excel seems to store that in a style.

$xlfile = ".\test.xlsx"
$excel = Open-ExcelPackage $xlfile
$id = $excel.Sheet1.Cells["A3"].StyleID
$excel.Workbook.Styles.CellXfs[$id]

test.xlsx

Outputs:

I can't see any options to control the indent for a cell when exporting.

[dfinke]

that's diff than outline?

Nothing specific in the PowerShell functions to do that.

try:

$excel.Workbook.Styles.CellXfs[$id] | get-member

See if there is a {get; set} for indent indicating a r/w property. There may be something like set_indent that lets you set the value.

[iambic69]

Thanks. Yes, different to outline, and yes, there is a setter. I wasn't sure how the whole styles thing worked, but I see that the underlying OfficeOpenXml library must do a clever copy-or-match-existing-on-write thing. Indent can be set just by doing:

$excel.Sheet1.Cells["A5"].Style.Indent = 5

... which creates a new style in Workbook.Styles.CellXfs, if needed. That gives me a workaround.

Adding it to ImportExcel would involve adding an Indent parameter to everywhere that HorizontalAlignment is supported:

• New-ExcelStyle
• Set-ExcelRow
• Set-ExcelColumn
• Set-ExcelRange

... and implementing it in Set-ExcelRange like this:

            if ($PSBoundParameters.ContainsKey('Indent')) {
                $Range.Style.Indent = $Indent
            }

Does that sound about right?

[dfinke]

Thanks for diving in @iambic69!
That sounds right. I would need to review. Not sure if you are asking so you can do a PR or just update your local copy.

For a PR. Pester tests would need to be updated and added.

[iambic69]

I was thinking I might do a PR. I'll see if I can find the time. :)

Thanks again!