code-boxx · October 12, 2024 17:40
diff --git a/0-JS-ADD-EXCEL.MD b/0-JS-ADD-EXCEL.MD
diff --git a/1-demo.css b/1-demo.css
 /* NOT IMPORTANT */
 * {
  font-family: Arial, Helvetica, sans-serif;
  box-sizing: border-box;
 }
 body {
  max-width: 400px;
 }
 form {
  padding: 20px;
  border: 1px solid #ededed;
  background: #f2f2f2;
 }
 input, select {
  display: block;
  width: 100%;
  padding: 10px;
 }
 input[type=file], select {
  border: 1px solid #c9c9c9;
  background: #fff;
 }
 input[type=submit] {
  margin-top: 20px;
  border: 0;
  color: #fff;
  background: #3c56b7;
  cursor: pointer;
 }
diff --git a/1-demo.html b/1-demo.html
 <!DOCTYPE html>
 <html>
  <head>
    <title>Excel Append</title>
    <meta charset="utf-8">
    <link rel="stylesheet" href="0-demo.css">
    <!-- https://cdnjs.com/libraries/xlsx -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
    <script src="1-append.js"></script>
    <!--
    <script src="2-prepend.js"></script>
    <script src="3-insert.js"></script>
    -->
  </head>
  <body>
    <form onsubmit="return demo()">
      <input id="picker" type="file" accept=".xls,.xlsx" required>
      <input type="submit" value="Go">
    </form>
  </body>
 </html>
diff --git a/2-append.js b/2-append.js
 function demo () {
  // (A) FILE PICKER & READER
  var picker = document.getElementById("picker"),
      reader = new FileReader();

  // (B) EXCEL ADD ROWS
  reader.addEventListener("loadend", evt => {
    // (B1) GET WORKSHEET
    var workbook = XLSX.read(evt.target.result, {type: "binary"}),
        worksheet = workbook.Sheets[workbook.SheetNames[0]];

    // (B2) APPEND NEW ROW & FORCE DOWNLOAD
    XLSX.utils.sheet_add_aoa(worksheet, [["NEW", "ROW"]], {origin: -1});
    XLSX.writeFile(workbook, "demoA.xlsx");
  });
  reader.readAsArrayBuffer(picker.files[0]);
  return false;
 }
diff --git a/3-prepend.js b/3-prepend.js
 function demo () {
  // (A) FILE PICKER & READER
  var picker = document.getElementById("picker"),
      reader = new FileReader();

  // (B) EXCEL ADD ROWS
  reader.addEventListener("loadend", evt => {
    // (B1) GET WORKSHEET
    var workbook = XLSX.read(evt.target.result, {type: "binary"}),
        worksheet = workbook.Sheets[workbook.SheetNames[0]];

    // (B2) PREPEND NEW ROW
    var data = XLSX.utils.sheet_to_json(worksheet, {header: 1})
    delete(worksheet); delete(workbook);
    data.splice(0, 0, ["NEW", "ROW"]);

    // (B3) SAVE UPDATED
    workbook = XLSX.utils.book_new();
    worksheet = XLSX.utils.aoa_to_sheet(data);
    XLSX.utils.book_append_sheet(workbook, worksheet);
    XLSX.writeFile(workbook, "demoB.xlsx");
  });
  reader.readAsArrayBuffer(picker.files[0]);
  return false;
 }
diff --git a/4-insert.js b/4-insert.js
 function demo () {
  // (A) FILE PICKER & READER
  var picker = document.getElementById("picker"),
      reader = new FileReader();

  // (B) EXCEL ADD ROWS
  reader.addEventListener("loadend", evt => {
    // (B1) GET WORKSHEET
    var workbook = XLSX.read(evt.target.result, {type: "binary"}),
        worksheet = workbook.Sheets[workbook.SheetNames[0]];

    // (B2) INSERT AT EXACT ROW
    var data = XLSX.utils.sheet_to_json(worksheet, {header: 1})
    delete(worksheet); delete(workbook);
    data.splice(2, 0, ["NEW", "ROW"]);

    // (B3) SEARCH & INSERT
    var at = null;
    for (let [i,r] of Object.entries(data)) {
      if (r.includes("Jon Doe")) { at = i; break; }
    }
    if (at !== null) { data.splice(at, 0, ["ANOTHER", "ROW"]); }

    // (B4) SAVE UPDATED
    workbook = XLSX.utils.book_new();
    worksheet = XLSX.utils.aoa_to_sheet(data);
    XLSX.utils.book_append_sheet(workbook, worksheet);
    XLSX.writeFile(workbook, "demoC.xlsx");
  });
  reader.readAsArrayBuffer(picker.files[0]);
  return false;
 }
diff --git a/x-dummy.csv b/x-dummy.csv
	/* NOT IMPORTANT */
	* {
	font-family: Arial, Helvetica, sans-serif;
	box-sizing: border-box;
	}
	body {
	max-width: 400px;
	}
	form {
	padding: 20px;
	border: 1px solid #ededed;
	background: #f2f2f2;
	}
	input, select {
	display: block;
	width: 100%;
	padding: 10px;
	}
	input[type=file], select {
	border: 1px solid #c9c9c9;
	background: #fff;
	}
	input[type=submit] {
	margin-top: 20px;
	border: 0;
	color: #fff;
	background: #3c56b7;
	cursor: pointer;
	}
	<!DOCTYPE html>
	<html>
	<head>
	<title>Excel Append</title>
	<meta charset="utf-8">
	<link rel="stylesheet" href="0-demo.css">
	<!-- https://cdnjs.com/libraries/xlsx -->
	<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
	<script src="1-append.js"></script>
	<!--
	<script src="2-prepend.js"></script>
	<script src="3-insert.js"></script>
	-->
	</head>
	<body>
	<form onsubmit="return demo()">
	<input id="picker" type="file" accept=".xls,.xlsx" required>
	<input type="submit" value="Go">
	</form>
	</body>
	</html>
	function demo () {
	// (A) FILE PICKER & READER
	var picker = document.getElementById("picker"),
	reader = new FileReader();

	// (B) EXCEL ADD ROWS
	reader.addEventListener("loadend", evt => {
	// (B1) GET WORKSHEET
	var workbook = XLSX.read(evt.target.result, {type: "binary"}),
	worksheet = workbook.Sheets[workbook.SheetNames[0]];

	// (B2) APPEND NEW ROW & FORCE DOWNLOAD
	XLSX.utils.sheet_add_aoa(worksheet, [["NEW", "ROW"]], {origin: -1});
	XLSX.writeFile(workbook, "demoA.xlsx");
	});
	reader.readAsArrayBuffer(picker.files[0]);
	return false;
	}
	Joa Doe	[email protected]
	Job Doe	[email protected]
	Joe Doe	[email protected]
	Joi Doe	[email protected]
	Jon Doe	[email protected]
	Joy Doe	[email protected]