nviet/convert_Excel_externalLinks.html

## convert_Excel_externalLinks.html
<!-- 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>&lt;Extracted directory&gt;\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>
	<!-- 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>