Untitled

<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="utf-8" />
	<meta http-equiv="x-ua-compatible" content="ie=edge" />
	<meta name="viewport" content="width=device-width, initial-scale=1" />
	<title>Storage sorting</title>
	<script src="https://unpkg.com/read-excel-file@5.x/bundle/read-excel-file.min.js"></script>
	<script src="https://unpkg.com/write-excel-file@1.x/bundle/write-excel-file.min.js"></script>
</head>

<body>
	<div style="border:1px solid brown; max-width:300px; height:150px; margin-bottom:2em; text-align:center; padding-top:1em;" ondrop="DropHandler(event, this);" ondragover="DragOverHandler(event, this, 1);" ondragleave="DragOverHandler(event, this, 0);">
		Drag & drop xslx file<br><br>
		<input type="file" multiple="multiple" onchange="ParseDroppedFile(this)">
	</div>

	<button type="button" onclick="GenerateSpreadsheet()" style="margin-bottom:25px">Export</button><br>

	<textarea id="input"></textarea>
	<h2>1</h2><div id="1"></div>
	<h2>2</h2><div id="2"></div>
	<h2>3</h2><div id="3"></div>
	<h2>non_matching</h2><div id="non_matching"></div>

	<script>
	var global_buffer = [];

	let input = document.getElementById("input");
	input.addEventListener("input", function(e){
		sortNumbersToColumns(e.target.value);
	});

	document.addEventListener("DOMContentLoaded", function(event) {
		sortNumbersToColumns(input.value);
	});

	function sortNumbersToColumns(input_string) {
		let storage_pattern = [
			//prefix, suffix, start, end, column, shelf
			{suffix:"ja", column:1, shelf:15},
			{suffix:"hx", column:1, shelf:45},
			{prefix:"bia", column:3, shelf:24},
			{suffix:"y", end:6500, column:2, shelf:1},
			{suffix:"y", start:6501, column:2, shelf:99},
		];

		let input  = [];
		let output = {
			"1":{},
			"2":{},
			"3":{},
			"non_matching":{},
		};

		function is_numeric(str) {
			if (typeof str != "string") return false
			return !isNaN(str) && !isNaN(parseFloat(str))
		}

		input_string.split(/\r?\n/).forEach(function(item, index, array) {
			item = item.replace(/\s/g, '');
			if (item.length > 0) {
				let new_item = ["", "", ""];
				let cut      = item.length;

				for(let i=0; i<=3 && i<item.length; i++) {
					//console.log(item[i] + ": " + (is_numeric(item[i])));
					cut = i;

					if (is_numeric(item[i]))
						break;
				}

				new_item[0] = item.slice(0,cut);
				new_item[1] = item.slice(cut);
				cut         = new_item[1].length;

				for(let i=new_item[1].length-1; i>=0; i--) {
					//console.log(new_item[1][i] + ": " + (is_numeric(new_item[1][i])));
					if (!is_numeric(new_item[1][i]))
						cut = i;
				}

				if (cut != new_item[1].length) {
					new_item[2] = new_item[1].slice(cut);
					new_item[1] = new_item[1].slice(0,cut);
				}

				input.push(new_item);
				//console.log(new_item);
			}
		});

		input.forEach((registration_number) => {
			let matched = false;
			for(let i=0; i<storage_pattern.length && !matched; i++) {
				if (
					(!storage_pattern[i].hasOwnProperty("prefix") || registration_number[0]==storage_pattern[i].prefix)
					&&
					(!storage_pattern[i].hasOwnProperty("suffix") || registration_number[2]==storage_pattern[i].suffix)
					&&
					(!storage_pattern[i].hasOwnProperty("start") || is_numeric(registration_number[1]) && parseInt(registration_number[1]) >= storage_pattern[i].start)
					&&
					(!storage_pattern[i].hasOwnProperty("end") || is_numeric(registration_number[1]) && parseInt(registration_number[1]) <= storage_pattern[i].end)
				) {
					if (!output[storage_pattern[i].column].hasOwnProperty(storage_pattern[i].shelf)) {
						output[storage_pattern[i].column][storage_pattern[i].shelf] = [];
					}

					output[storage_pattern[i].column][storage_pattern[i].shelf].push(registration_number.join(""));
					matched = true;
				}
			}

			if (!matched) {
				output.non_matching.push(registration_number.join(""));
			}
		});

		Object.keys(output).forEach(column => {
			document.getElementById(column).innerHTML = "";

			Object.keys(output[column]).forEach(shelf => {
				output[column][shelf].sort();
				document.getElementById(column).innerHTML += "<b>"+shelf+"</b>: " + output[column][shelf].join(", ") + "<br>";
			});
		});

		global_buffer = output;
		//console.log(output);
	}

	// functions for importing excel file
	function DropHandler(event, field) {
		event.preventDefault();
		ParseDroppedFile(event.dataTransfer);
		field.style.backgroundColor = "transparent";
	}

	function DragOverHandler(event, field, enable) {
		event.preventDefault();
		field.style.backgroundColor = enable ? "coral" : "transparent";
	}

	function ParseDroppedFile(input) {
		readXlsxFile(input.files[0]).then(function(rows) {
			let text = "";

			rows.forEach(row => {
				if (row.length >= 7) {
					text += row[6] + "\n";
				}
			});

			sortNumbersToColumns(text);
		});
	}

	function GenerateSpreadsheet() {
		if (global_buffer.length == 0)
			return;

		let excel_rows = [[]];
		let parallel_list = [];
		let columns = [];

		Object.keys(global_buffer).forEach(function(column, index, array) {
			excel_rows[0].push({value:column, fontWeight:'bold'});
			columns.push({width:column.length + 1});
			parallel_list.push([]);

			Object.keys(global_buffer[column]).forEach(shelf => {
				global_buffer[column][shelf].forEach(registration_number => {
					parallel_list[index].push(shelf+": " + registration_number);
				});
			});
		});

		let j = 0;
		let null_count = 0;

		while(true) {
			let excel_row = [];

			for(let i=0; i<parallel_list.length; i++) {
				let current_value = '';

				if (parallel_list[i][j]) {
					current_value = parallel_list[i][j];
				} else {
					null_count++;
				}

				excel_row.push({type:String, value:current_value});

				if (current_value.length > columns[i].width) {
					columns[i].width = current_value.length;
				}
			}

			if (null_count < parallel_list.length) {
				excel_rows.push(excel_row);
				null_count = 0;
				j++;
			} else {
				break;
			}
		}

		//console.log(excel_rows);

		var today = new Date();
		var dd    = String(today.getDate()).padStart(2, '0');
		var mm    = String(today.getMonth() + 1).padStart(2, '0');
		var hh    = String(today.getHours()).padStart(2, '0');
		var min   = String(today.getMinutes()).padStart(2, '0');
		var yyyy  = today.getFullYear();

		writeXlsxFile(excel_rows, {
			columns,
			fileName: 'lista' + yyyy + '_' + mm + '_' + dd + '-' + hh + '-' + min + '.xlsx'
		})
	}
	</script>
</body>
</html>