Export Formula

[derDaywalker]

Hello,

I'm just starting to play with PowerShell and Export-Excel. But im struggeling to export even a simple formular. Simple example:

"=SUM(2;2)" | Export-Excel -now

This leads to a error message in excel:

We found a problem with some content in 'tmp...xlsx'. Do you want us to try to recover as much as we can?

Hitting yes leads to a empty sheet with the error message:

Removed Records: Formula from /xl/worksheets/sheet1.xml part

Maybe Export-Excel does everything correct and it's just Excel itself which don't like "importing" formulas. But I'm not sure if that's the case because I'm new to excel, powershell and export-excel at all.

The overall goal is not to implement a simple SUM function, but to add a rather long AND(A2=true;B2=true;C2=false;...;Q2=true) formula in order to check if according to a decision matrix the values captured from a system are compliant. There might be around 500-600 rows and this formula should apply to each row. The ultimate goal is then to use a filter within excel to filter for rows which are not compliant according to the decision matrix (hide but not delete the rows which are compliant). I'll also use conditional format to highlight the individual values which are not compliant.

I know that I might just evaluate the compliance within the PowerShell Script and just export true or false to that complicance cell and filter it that way, but I was wondering if export-excel is able to export formulas at all or if its just a excel problem.

cheers

[dfinke]

Check out some videos I've done

https://www.youtube.com/playlist?list=PL5uoqS92stXioZw-u-ze_NtvSo0k0K0kq

[derDaywalker]

Thank you very much for your quick response!
I already saw most of the vidoes (execpt of the last two), but now watched the others too. In the last video you show the formula as a total of a column, but I need the total of a row (preferably not at the end but maybe in the 3. column considering the next 12 columns, so A=name, B=id, C=compliant, D=online, E=...., Q=compaction).

But the problem is, that in each row I need the formula (evaluating only the current row). So for instance, if the decision matrix says that all three columns (online, compression and compaction) needs to be true, then D2 and D3 should contain true as value, while the others (D4 - D6) should contain the value false.

[lemrvls]

I don't think there is an option for rows included in the Export-Excel.
You can use Set-ExcelRange like this for a sum in the last cell of the 1st data line :

#data
$pkg = $data | Export-Excel $xlsx_path -PassThru
$ws = $pkg.Workbook.Worksheets["Sheet1"]

#you can do a foreach here
$address_cell = "E2"
$Formula_range = "A2:D2"
$Formula = "Sum(", $Formula_range, ")" -join ""
Set-ExcelRange -Address $ws.Cells[$address_cell] -Formula $Formula -Bold

#save
Close-ExcelPackage $pkg

[derDaywalker]

Hi lemrvls,

thank you very much for your answer. I tried it, with sum it works, but with AND it doesn't work:

Edit:
I guess, the formula was just wrong, instead of semi-colon ";" I have to use comma ",":

Edit2:
It seems that it works also the "normal" way and that after all just the formula was wrong from the beginning (like I said, usage of , instead of ; is required):

And now it works like a charm:

Thank you all for pointing me into the right direction such that I knew that in general formulas are working. It was just my stupidity with the syntax.

[lemrvls]

Glad I helped you look in the right direction.

And btw you can actually use the -Style operator in Export-Excel along the New-ExcelStyle command:

#I do this for my extra-long headers with short values in their rows
$ExcelStyle = New-ExcelStyle -Range $range_headers -HorizontalAlignment Center -TextRotation 90 -WrapText -AutoSize

#then pass the ExcelStyle to the Export cmd
$data | select $tab_all_properties | Export-Excel $xlsx -AutoSize -AutoFilter -BoldTopRow -FreezeTopRow -FreezeFirstColumn -TableName "table" -TableStyle "Medium9" -TableTotalSettings $TableTotalSettings -ConditionalFormat $ConditionalText -Style $ExcelStyle

Next I'll try if I can pass multiple rules in -Style.

All these tools are crazy good, thanks Doug or anyone who worked on it.

edit for 2 ExcelStyle :

#2 ExcelStyle
$ExcelStyle1 = New-ExcelStyle -Range $range_headers -HorizontalAlignment Center -TextRotation 90 -WrapText -AutoSize
$ExcelStyle2 = New-ExcelStyle -Range "C1:C1" -HorizontalAlignment Center -TextRotation 90 -WrapText -AutoSize

#then pass the 2 ExcelStyle to the Export cmd
$data | select $tab_all_properties | Export-Excel $xlsx -AutoSize -AutoFilter -BoldTopRow -FreezeTopRow -FreezeFirstColumn -TableName "table" -TableStyle "Medium9" -TableTotalSettings $TableTotalSettings -ConditionalFormat $ConditionalText -Style $ExcelStyle1, $ExcelStyle2

This is simple and effective, perfection.

[ninmonkey]

quote: I need the formula (evaluating only the current row).

Row Context in Excel Formulas

You can reference the "current row" using structured references.

1] Name your tables using -TableName I used UserList

Export-Excel -WorksheetName 'employees' -TableName 'UserList'

2] You can refer to the current row of a table using structured references, like UserList[@...] ( see screenshot )

= UserList[@TotalSales] -  UserList[@Id]

---

Row Context in Powershell

If you're looking for the row context in powershell, here's an example. All you need to edit is the hashtable.

0..10 | ForEach-Object {
    random.user | ForEach-Object { 
        $curRow = $_
        $curRow | Add-Member -Force -ea Ignore -PassThru -NotePropertyMembers @{
            # silly example showing the 'row context' when adding 'columns'
            IsBasicColor = $curRow.Color -match 'red|orange|green|blue'
            IsShort      = $_.Color.length -lt 7 -and $_.LastName.length -lt 7  

        }
    }
} | Format-Table