SharePoint Excel Service : How to resolve "the workbook cannot be opened" error?

Run following commands for each web application that is associated with Excel Services:

$w = Get-SPWebApplication -identity http://<WebApplication>
$w.GrantAccessToProcessIdentity("<Domain>\<Username>")


In future, for the additional content databases, you must rerun these cmdlets to ensure that Excel Services has access to the new databases.


Source: https://docs.microsoft.com/en-us/sharepoint/administration/configure-excel-services

SharePoint Application Page: How to export Gridview data to Excel file using ActiveX?

Note: This feature works only in IE. Also, you would need to enable ActiveX in IE settings and add the URL in trusted sites.

Prerequisite 1: IE Settings -> Internet Options -> Security -> Trusted Sites -> Custom Levels -> ActiveX Settings -> Enable/Prompt for ActiveX not marked as safe.

Prerequisite 2: IE Settings -> Internet Options -> Security -> Trusted Sites -> Add URL


Javascript:

<script language="javascript" type="text/javascript">
function exportTable() {
var x = document.getElementById('table_id').rows;
var xls = new ActiveXObject("Excel.Application");
xls.visible = true
xls.Workbooks.Add
for (i = 0; i < x.length; i++) {
var y = x[i].cells;
for (j = 0; j < y.length; j++) {
xls.cells(i + 1, j + 1).value = y[j].innerText;
}
}
}
</script>


HTML


<input type='button' value='Export To Excel' onclick="exportTable();"/>