Created
August 22, 2023 10:00
-
-
Save nviet/caf9035502fa52942c638e82374530eb to your computer and use it in GitHub Desktop.
Convert external link in Excel file to human-readable format
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<!-- The below code is licensed under the terms of the MIT license. Copyright (c) Viet Nguyen --> | |
<!DOCTYPE HTML> | |
<html lang="en"> | |
<head> | |
<meta http-equiv="content-type" content="text/html; charset=utf-8"> | |
<title>Convert external link in Excel file to human-readable format</title> | |
<style> | |
div.container | |
{ | |
display: table; | |
margin-right: auto; | |
margin-left: auto; | |
text-align: left; | |
border: 1px solid #000; | |
padding: 10px; | |
} | |
</style> | |
</head> | |
<body> | |
<div class="container"> | |
<h2>Convert external link in Excel file to human-readable format</h2> | |
<p>Steps to follow:</p> | |
<ol> | |
<li>Remove the workbook password (if any) and save the Excel file in XLSX format</li> | |
<li>Rename the saved file from *.xlsx to *.zip</li> | |
<li>Extract the ZIP file</li> | |
<li>Click the button below and select a XML file located under the <em><Extracted directory>\xl\externalLinks\</em></li> | |
<li>The tool will generate another XML file, which can be opened in Excel</li> | |
</ol> | |
<input type="file" id="fileInput"> | |
</div> | |
</body> | |
</html> | |
<script> | |
document.getElementById("fileInput").addEventListener("change", function selectedFileChanged() | |
{ | |
if (this.files.length === 0) | |
{ | |
console.log("No file selected."); | |
return; | |
} | |
const reader = new FileReader(); | |
// https://stackoverflow.com/a/27927981 | |
reader.fileName = this.files[0].name; | |
reader.onload = function fileReadCompleted(readerEvt) | |
{ | |
var xml = parseXml(reader.result); | |
var sheetCount = xml.evaluate("count(//ns:sheetData)", xml, nsResolver, XPathResult.ANY_TYPE, null); | |
if(sheetCount.numberValue > 0) | |
{ | |
var currentDate = new Date(); | |
var createdDate = currentDate.getFullYear() + "-" | |
+ (currentDate.getMonth()) + "-" | |
+ currentDate.getDate() + "T" | |
+ currentDate.getHours() + ":" | |
+ currentDate.getMinutes() + ":" | |
+ currentDate.getSeconds() + "Z"; | |
var xmlOutput = "<?xml version=\"1.0\"?>\n"; | |
xmlOutput += "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\n"; | |
xmlOutput += " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\n"; | |
xmlOutput += " xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\n"; | |
xmlOutput += " xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\n"; | |
xmlOutput += " xmlns:html=\"http://www.w3.org/TR/REC-html40\">\n"; | |
xmlOutput += " <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">\n"; | |
xmlOutput += " <Author>viet.im</Author>\n"; | |
xmlOutput += " <LastAuthor>viet.im</LastAuthor>\n"; | |
xmlOutput += " <Created>" + createdDate + "</Created>\n"; | |
xmlOutput += " </DocumentProperties>\n"; | |
// Get sheet names | |
var sheetNames = []; | |
var sheetNameCollection = xml.evaluate("//ns:sheetName", xml, nsResolver, XPathResult.ANY_TYPE, null); | |
var sheetNameCurrent = sheetNameCollection.iterateNext(); | |
while (sheetNameCurrent) | |
{ | |
sheetNames.push(sheetNameCurrent.getAttribute("val")); | |
sheetNameCurrent = sheetNameCollection.iterateNext(); | |
} | |
// Loop through sheets | |
var i = 0; | |
var sheetCollection = xml.evaluate("//ns:sheetData", xml, nsResolver, XPathResult.ANY_TYPE, null); | |
var sheetCurrent = sheetCollection.iterateNext(); | |
while (sheetCurrent) | |
{ | |
xmlOutput += " <Worksheet ss:Name=\"" + sheetNames[i] + "\">\n"; | |
xmlOutput += " <Table>\n"; | |
i++; | |
var rowCount = xml.evaluate("count(.//ns:row)", sheetCurrent, nsResolver, XPathResult.ANY_TYPE, null); | |
if(rowCount.numberValue > 0) | |
{ | |
// Loop through rows in a sheet | |
var rowCollection = xml.evaluate(".//ns:row", sheetCurrent, nsResolver, XPathResult.ANY_TYPE, null); | |
var rowCurrent = rowCollection.iterateNext(); | |
while (rowCurrent) | |
{ | |
xmlOutput += " <Row ss:Index=\"" + rowCurrent.getAttribute("r") + "\">\n"; | |
// Loop through cells in a row | |
var cellCollection = xml.evaluate(".//ns:cell", rowCurrent, nsResolver, XPathResult.ANY_TYPE, null); | |
var cellCurrent = cellCollection.iterateNext(); | |
while (cellCurrent) | |
{ | |
xmlOutput += " <Cell ss:Index=\"" + rowLetterToIndex(cellCurrent.getAttribute("r")) + "\"><Data ss:Type=\"String\">" + cellCurrent.textContent + "</Data></Cell>\n"; | |
var cellCurrent = cellCollection.iterateNext(); | |
} | |
xmlOutput += " </Row>\n"; | |
var rowCurrent = rowCollection.iterateNext(); | |
} | |
} | |
sheetCurrent = sheetCollection.iterateNext(); | |
xmlOutput += " </Table>\n"; | |
xmlOutput += " </Worksheet>\n"; | |
} | |
xmlOutput += "</Workbook>"; | |
download(xmlOutput, "converted_" + readerEvt.target.fileName, "application/octet-stream"); | |
} | |
}; | |
reader.readAsText(this.files[0]); | |
}); | |
// https://stackoverflow.com/a/9906193 | |
function rowLetterToIndex(row) | |
{ | |
row = row.replace(/[0-9]/g, ''); | |
var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', i, j, result = 0; | |
for (i = 0, j = row.length - 1; i < row.length; i += 1, j -= 1) | |
{ | |
result += Math.pow(base.length, j) * (base.indexOf(row[i]) + 1); | |
} | |
return result; | |
} | |
// https://stackoverflow.com/a/8412989 | |
function parseXml(xmlStr) | |
{ | |
var parseXml; | |
if (typeof window.DOMParser != "undefined") | |
{ | |
return new window.DOMParser().parseFromString(xmlStr, "text/xml"); | |
} else if (typeof window.ActiveXObject != "undefined" && new window.ActiveXObject("Microsoft.XMLDOM")) | |
{ | |
var xmlDoc = new window.ActiveXObject("Microsoft.XMLDOM"); | |
xmlDoc.async = "false"; | |
xmlDoc.loadXML(xmlStr); | |
return xmlDoc; | |
} else | |
{ | |
throw new Error("No XML parser found"); | |
} | |
} | |
// https://stackoverflow.com/a/40796315 | |
function nsResolver(prefix) | |
{ | |
var ns = | |
{ | |
"ns": "http://schemas.openxmlformats.org/spreadsheetml/2006/main" | |
}; | |
return ns[prefix] || null; | |
} | |
// https://stackoverflow.com/a/30832210 | |
function download(data, filename, type) | |
{ | |
var file = new Blob([data], {type: type}); | |
if (window.navigator.msSaveOrOpenBlob) | |
{ | |
// IE10+ | |
window.navigator.msSaveOrOpenBlob(file, filename); | |
} else | |
{ | |
// Others | |
var a = document.createElement("a"), url = URL.createObjectURL(file); | |
a.href = url; | |
a.download = filename; | |
document.body.appendChild(a); | |
a.click(); | |
setTimeout(function() | |
{ | |
document.body.removeChild(a); | |
window.URL.revokeObjectURL(url); | |
}, 0); | |
} | |
} | |
</script> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment