Microsoft Excell ERROR: "found a problem with some content"

[RaysCars]

When using Export-excel with values that have formulas, Excel states: "found a problem with some content in ExcelSheetName.XLSX. Do you want us to recover as much as possible?"

This occurs 9/10 times. However, it never occurs when using Converto-CSV and opening the CSV in excel.

[dfinke]

need a repro of data etc

[Cyb3r4rch3r]

Seeing the same error. Original export line:

$result | Select-Object Name,DistingushedName,IsHighValue,IsACLProtected,@{n='Users';e={$_.Users -join ','}},@{n='Groups';e={$_.Groups -join ','}},@{n='AccessRights';e={$_.AccessRights -join ' | '}},@{n='TransitiveMembers';e={$_.TransitiveMembers -join ','}} | Export-Excel -Path ".\$($target)_OU_ACLs_Full.xlsx" -WorksheetName "$(($ou.Properties.DistinguishedName -split ",OU=")[1])" -Append -TableStyle Medium16 -Title "$($ou.Properties.DistinguishedName)" -TitleBold

Tested several times commenting out one parameter at a time. This seems to be related to the -Title parameter. Once that parameter was commented out, the file was created an opened as expected in 5 subsequent tests.

Environment: Windows 11 Enterprise PowerShell 7.3.9

[scriptingstudio]

A couple of quick work arounds:

• remove excel file before calling Export-Excel
• use ClearSheet parameter

[RaysCars]

@scriptingstudio - If you're saying to delete the excel file before using Export-Excel, then that defeats the purpose of creating an excel file with many worksheets. Do I misunderstand you?

[dfinke]

@RaysCars I need a simple repro I can run so I can look at the issue. A zip file with the current xlsx with data and a ps1 reproing the issue.

[RaysCars]

attached is one example:

On Fri, Nov 17, 2023 at 8:17 AM Doug Finke @.***> wrote:

@RaysCars https://github.com/RaysCars I need a simple repro I can run so I can look at the issue. A zip file with the current xlsx with data and a ps1 reproing the issue.

— Reply to this email directly, view it on GitHub https://github.com/dfinke/ImportExcel/issues/1532#issuecomment-1816411746, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAIHR2ZATP27D3QERF2NYNDYE5PU5AVCNFSM6AAAAAA6QHTNPSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJWGQYTCNZUGY . You are receiving this because you were mentioned.Message ID: @.***>

[dfinke]

Sorry, don't see the attachment

[a-missico]

RecoverIssue.zip

I duplicated the issue to a very simple case. I am converting a list of "fields" defined in XML to Excel. I ran into this issue a few years ago, and using -ReZip and -ClearSheet avoided the "recover" message.

Export-Excel 7.8.9

PSVersion 5.1.22621.3672
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.22621.3672
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

The following reproduces the issue.

$_fieldDefinitionFilePath = 'A:\Temp\FieldDefinition.xml'

$_excelFilePath = 'A:\Temp\FieldDefinition.xlsx'

$_fields = [xml](Get-Content -Path $_fieldDefinitionFilePath) 

$_fieldListing = $_fields.StaticResouce.Fields.Field | ForEach-Object {
    [pscustomobject]@{

        Name   = [string]$_.Name
        #Name = 'fred'

        # this will work 
        # Name = 'fred'
        ##

        # this will fail
        # Name   = [string]$_.Name
        ##
    }
} 

Remove-Item -Path  $_excelFilePath

Export-Excel -InputObject $_fieldListing -Path $_excelFilePath

Excel Log

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <logFileName>error214040_01.xml</logFileName>
    <summary>Errors were detected in file 'A:\Temp\FieldDefinition.xlsx'</summary>
    <removedRecords>
        <removedRecord>Removed Records: Formula from /xl/worksheets/sheet1.xml part</removedRecord>
    </removedRecords>
</recoveryLog>

I did the example at https://dfinke.github.io/powershell/2019/07/31/Creating-beautiful-Powershell-Reports-in-Excel.html with no issues

This example also works

Get-Process | Where Company | Export-Excel A:\Temp\ps.xlsx -Show -IncludePivotTable -PivotRows Company -PivotData @{Handles=”sum”} -IncludePivotChart -ChartType PieExploded3D

[a-missico]

Found the issue. I ran into this question, and it reminded me about values that begin with = which are interpreted as formulas. I inserted ` to the Name, and the recovery message disappeared

FROM
<Field Number="7672" Name="= Borrower's closing cost" IsBorrowerSet="false" PRS="true" BRW="true" AuditLogged="true"/>
TOL
<Field Number="7672" Name="'= Borrower's closing cost" IsBorrowerSet="false" PRS="true" BRW="true" AuditLogged="true"/>

https://stackoverflow.com/questions/71335236/removed-records-formula-from-xl-worksheets-sheet1-xml-part

[dfinke]

Wow. So I understand, the value being inserted is "= Borrower's closing cost" and you put a single quote ' in frot of the =. "'= Borrower's closing cost"

I recall that from way back is how you indicate to Excel the next parts are not a formula etc.

Good stuff!