#Requires -Version 5.1
<#
    JbTecWiz Support Centre -- generated fix script

    Fault : A document will not open -- Protected View, "file format is not valid", or a corrupt file
    Fix   : Find out what the file actually is
    Source: https://jbtecwiz.com/support/win-office-file-wont-open

    Run as     : PowerShell as the affected user
    Expect     : 15 minutes
    Risk       : low
    Reversible : yes

    WHEN THIS IS THE RIGHT FIX
    The file came out of a reporting system, an accounts package or a web
    export. This is the cause people spend longest missing.

    HOW TO UNDO IT
    Rename the file back. Nothing inside it has been modified.

    This script walks the fix one step at a time and asks before each
    one. Steps with no command are things you do yourself -- it prints
    those and waits. Run with -DryRun to print without executing.
#>

[CmdletBinding()]
param(
    # Print every step and command without running anything.
    [switch]$DryRun,
    # Do not ask before each step. Read the script first if you use this.
    [switch]$Unattended
)

$ErrorActionPreference = 'Stop'
$script:Failed = 0

# - THIS SCRIPT WILL NOT RUN UNTIL YOU EDIT IT -
# The commands below contain values only you can supply. Search the
# script for each one, replace it, then delete this block.
$script:MustEdit = @(
    'C:\path\to\file.xls'   # a placeholder file path
)
if ($script:MustEdit.Count) {
    Write-Host ""
    Write-Host '  This script needs editing before it can run.' -ForegroundColor Yellow
    Write-Host '  Replace each of these with a real value:' -ForegroundColor Yellow
    $script:MustEdit | ForEach-Object { Write-Host ("    " + $_) -ForegroundColor Yellow }
    Write-Host ""
    Write-Host '  Then delete the $script:MustEdit block near the top.'
    Write-Host ""
    exit 2
}

function Write-Rule { param([string]$Text)
    Write-Host ''
    Write-Host ('-' * 70) -ForegroundColor DarkGray
    if ($Text) { Write-Host $Text -ForegroundColor Cyan }
}

function Show-Prose { param([string]$Text, [string]$Colour = "Gray")
    if (-not $Text) { return }
    $words = $Text -split "\s+"; $line = "  "
    foreach ($w in $words) {
        if (($line.Length + $w.Length) -gt 74) { Write-Host $line -ForegroundColor $Colour; $line = "  " }
        $line += "$w "
    }
    if ($line.Trim()) { Write-Host $line -ForegroundColor $Colour }
}

function Invoke-Step {
    param(
        [int]$Number, [string]$Do, [string]$Why,
        [scriptblock]$Command, [switch]$Manual, [string]$Shell = "powershell"
    )
    Write-Rule "  Step $Number of 5"
    Show-Prose $Do "White"
    if ($Why) { Write-Host ""; Show-Prose $Why "DarkGray" }

    if ($Manual) {
        Write-Host ''
        Write-Host '  -> Do this yourself, then press Enter to carry on.' -ForegroundColor Yellow
        if (-not $Unattended -and -not $DryRun) { [void](Read-Host) }
        return
    }

    Write-Host ''
    foreach ($l in ($Command.ToString().Trim() -split "`n")) {
        Write-Host ("    " + $l.Trim()) -ForegroundColor Green
    }
    Write-Host ''

    if ($DryRun) { Write-Host "  (dry run -- not executed)" -ForegroundColor DarkGray; return }
    if (-not $Unattended) {
        $a = Read-Host "  Run this step? [Y]es / [S]kip / [Q]uit"
        if ($a -match "^[Qq]") { Write-Host "  Stopped at your request."; exit 0 }
        if ($a -match "^[Ss]") { Write-Host "  Skipped." -ForegroundColor DarkGray; return }
    }
    try { & $Command }
    catch {
        $script:Failed++
        Write-Host ("  Step $Number failed: " + $_.Exception.Message) -ForegroundColor Red
        Show-Prose "The rest of the fix may depend on this. Read the write-up before carrying on." "Red"
        if (-not $Unattended) {
            $c = Read-Host "  Carry on anyway? [y/N]"
            if ($c -notmatch "^[Yy]") { exit 1 }
        }
    }
}

Write-Rule
Write-Host '  A document will not open -- Protected View, "file format is not valid", or a corrupt file' -ForegroundColor White
Write-Host '  Find out what the file actually is' -ForegroundColor Cyan
Write-Host ''
Write-Host '  Risk: low   Reversible   15 minutes'
Write-Rule

if (-not $Unattended -and -not $DryRun) {
    $go = Read-Host '  Ready? [y/N]'
    if ($go -notmatch "^[Yy]") { Write-Host "  Nothing was changed."; exit 0 }
}

Invoke-Step -Number 1 -Do 'Read the first bytes of the file and compare them with what the extension claims.' -Why '50 4B 03 04 is a zip, so the file is really a modern .xlsx or .docx. D0 CF 11 E0 is the old compound format, a genuine .xls or .doc. Anything starting 3C (a ''<'' character) is HTML or XML with a spreadsheet extension, which is what most reporting exports produce.' -Command {
    Get-Content -Path 'C:\path\to\file.xls' -Encoding Byte -TotalCount 8 | ForEach-Object { $_.ToString('X2') }
}

Invoke-Step -Number 2 -Do 'If it is HTML, rename it to .html and open it in a browser to confirm, then let Excel import it properly via Data -> From Web or Data -> From Text/CSV rather than opening it directly.' -Manual

Invoke-Step -Number 3 -Do 'If it is a zip, rename it to the correct modern extension -- .xlsx, .docx or .pptx -- and open it.' -Manual

Invoke-Step -Number 4 -Do 'If it is the old compound format with a modern extension, rename it to .xls or .doc.' -Why 'Office decides how to parse a file from its extension first. Correcting the extension is not a workaround here -- it is telling Office the truth about the file.' -Manual

Invoke-Step -Number 5 -Do 'Where a system produces these repeatedly, fix the export at source rather than renaming files forever.' -Manual

Write-Rule "  Confirm it worked"
Show-Prose 'The file opens with its content intact and the columns and formatting where they should be.' 'White'

Write-Rule
if ($script:Failed -gt 0) {
    Write-Host ("  Finished with " + $script:Failed + " failed step(s).") -ForegroundColor Yellow
    Show-Prose 'Read the full write-up at https://jbtecwiz.com/support/win-office-file-wont-open' 'Yellow'
} else {
    Write-Host '  Finished.' -ForegroundColor Green
}
Write-Host ''
Show-Prose 'To undo: Rename the file back. Nothing inside it has been modified.' 'DarkGray'
Write-Rule