Exported Excel file cannot be opened because the file format or file extension is not valid in Win10

[Marianne Lee (Nagarro)]

We used to be able to export issues to Excel file format from the Jira Issue Navigator (search and filters). After our company upgraded our PCs to Windows 10 and Office 365 few months ago, we are no longer able to open the Excel files exported from Jira.  We're running Jira Server 7.10.2.

The error message encountered is:

"Excel cannot open the file 'XXXXXXX.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

Is Jira exporting to Excel format which is office 97-2003?

JRASERVER-62196 - Filter result Excel(XLS) export is not usable anymore, need XLSX format support

JRASERVER-60667 - File format does not match extension

I have searched but could not find a suitable solution.  I have tried to rename the file extension, open in safe mode, open and repair, all to no avail. 

Has anyone else experienced the same problem?  Is there a way to fix this problem?

Regards,
Marianne

[Jean-François Gillet]

The following workaround can be used while waiting an official fix :

1) Copy and rename the exported timesheet file to your desktop (call the file 't.html')

2) Run this powershell script using powershell 7 (I haven't tested it elsewhere):

---------- Start of code ----------

$infile = Get-ChildItem -Path "C:\Users\$Env:UserName\Desktop" -Filter "t.html"

$outfile = "C:\Users\$Env:UserName\Desktop\t-out.xlsx"

$Excel = New-Object -Com Excel.Application

$Excel.DisplayAlerts = $false

$Workbook = $Excel.Workbooks.Open($infile)

    if ($tabname) {

        $Sheet = $Workbook.Worksheets.Item(1)      

        $Sheet.Name = $tabname;

    }

$Workbook.SaveAs($outfile, 51)

$Workbook.Close()

$Excel.quit()

---------- End of code ----------

You can save this code to a .ps1 file and run it using pwsh .exe(=Power shell 7).

3) The 't-out.xlsx' file can be opened with excel 2019 without any error message (I hope it works with Office 365)

Remark: I'm not a developer but this code can be easily adapted to match with your environment or you needs.

[Aron Gombas _Midori_]

It's an older story. The problem is that the "xlsx" file exported from Jira is actually an HTML file with a table, which older Excel versions were able to import. So it was never a native Excel file, but the file extension instruction Excel to import it. Then, Microsoft disallowed this trick for security reasons.

There are every sort of hacky workarounds, or there is the Better Excel Exporter app that produces real Excel files.

(Disclaimer: I am developer of this paid app.)

[Marianne Lee (Nagarro)]

Thanks @Aron Gombas _Midori_ I found out that export to Excel format had been deprecated long time ago since Jira 7.2. Will advise my users to use other available formats.

[Joe Pitt]

Open excel and go to the options/trust center/trust center settings/file block settings and look for any file for some earlier versions of excel being blocked. I had a similar problem where older versions of excel were blocked for security reasons. 

[Marianne Lee (Nagarro)]

Thanks, @Joe Pitt I'm checking that with the Windows group in the operations team, I've not got any reply yet.