ifyour · December 4, 2020 06:10 · ifyour · Dec 3, 2020
diff --git a/crud_xlsx.html b/crud_xlsx.html
 <!DOCTYPE html>
 <html lang="cn">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />

    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/xlsx.full.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/jszip.min.js"></script>
    <!-- <script src="https://cdn.jsdelivr.net/npm/[email protected]/preact/standalone.umd.min.js"></script> -->

    <title>js file</title>
  </head>

  <body>
    <p>
      读取xlsx
      <input class="file" type="file" />
    </p>

    <button onclick="addCol()">添加列</button>
    <button onclick="addRow()">添加行</button>
    <button onclick="generateXlsxFile()">生成xlsx</button>
    <button onclick="generateZip()">生成zip</button>

    <div class="editTable"></div>
    <script>
      let columns = [{ title: 'edit0' }];
      let editParams = [{ edit0: '' }];

      document.querySelector('.file').addEventListener('change', readXlsx);

      function readXlsx(evt) {
        const file = evt.target.files[0];
        const reader = new FileReader();
        reader.onload = (e) => {
          const arr8 = new Uint8Array(e.target.result);
          const workbook = XLSX.read(arr8, { type: 'array' });

          // const data = Object.values(workbook.Sheets)[0]
          const data = workbook.Sheets.SheetJS;
          console.log(
            '🚀 ~ file: crud_xlsx.html ~ line 42 ~ readXlsx ~ data',
            data
          );

          columns = [];
          editParams = [];

          Object.keys(data).forEach((it, idx) => {
            if (!/^!/.test(it)) {
              const col = it.replace(/[0-9]+/, '');
              const row = parseInt(it.replace(/[a-zA-Z]+/, ''));
              const key = data[`${col}1`].v;
              if (row === 1) {
                columns.push({ title: data[it].v });
              } else {
                if (!editParams[row - 1]) {
                  editParams[row - 1] = {};
                }
                editParams[row - 1][key] = data[it].v;
              }
            }
          });
          console.log(
            '🚀 ~ file: crud_xlsx.html ~ line 62 ~ Object.keys ~ editParams',
            editParams
          );

          generate();
        };

        reader.readAsArrayBuffer(file);
      }

      function generateXlsx() {
        const wb = XLSX.utils.book_new();
        const newData = editParams.map((row) => {
          return columns.map((col) => row[col.title]);
        });
        newData.unshift(columns.map((it) => it.title));
        const ws = XLSX.utils.aoa_to_sheet(newData);
        XLSX.utils.book_append_sheet(wb, ws, 'SheetJS');
        const resp = XLSX.write(wb, {
          bookType: 'xlsx',
          bookSST: false,
          type: 'array',
        });
        return resp;
      }

      function generateZip() {
        const zip = new JSZip();
        const arr8 = generateXlsx();
        zip.file('test.xlsx', arr8, { binary: true });
        zip.generateAsync({ type: 'blob' }).then((resp) => {
          downFile(resp, 'test.zip');
        });
      }

      function generateXlsxFile() {
        // const arr8 = Uint8Array.from(array);
        const arr8 = generateXlsx();
        const blob = new Blob([arr8]);
        downFile(blob, 'test.xlsx');
      }

      function downFile(blob, name) {
        const a = document.createElement('a');
        a.style.display = 'none';
        a.href = URL.createObjectURL(blob);
        a.download = name;
        document.body.appendChild(a);
        a.click();
        URL.revokeObjectURL(a.href);
        document.body.removeChild(a);
      }

      function addCol() {
        columns.push({
          title: `edit${columns.length}`,
        });
        generate();
      }

      function addRow() {
        editParams.push({});
        generate();
      }

      function deleteRow(row) {
        editParams.splice(row, 1);
        generate();
      }

      function handleInputChange(evt) {
        const val = evt.target.value;
        const idx = evt.target.getAttribute('data-idx');
        const key = evt.target.getAttribute('data-key');
        editParams[idx][key] = val;
      }

      function generate() {
        const dom = document.querySelector('.editTable');
        const rowVal = editParams.map((it, idx) => {
          return columns.map((col) => {
            return {
              val: it[col.title],
              key: col.title,
            };
          });
        });

        const html = rowVal.reduce((next, it, idx) => {
          const newHtml = `
          <tr>
            ${it
              .map(
                (td) =>
                  `<td class="td"><input data-idx="${idx}" data-key="${
                    td.key
                  }" class="input" value="${td.val || ''}" /></td>`
              )
              .concat(
                `<td><button onclick="deleteRow(${idx})" ${
                  editParams.length === 1 ? 'disabled' : ''
                }>删除</button></td>`
              )
              .join('')}
          </tr>
        `;
          return next + '\n' + newHtml;
        }, '');

        dom.innerHTML = `
        <table border="1">
          <tr>
            ${columns
              .map((it) => `<td>${it.title}</td>`)
              .concat(`<td>操作</td>`)
              .join('')}
          </tr>
          ${html}
        </table>
      `;
        dom.querySelectorAll('.input').forEach((it) => {
          it.addEventListener('blur', handleInputChange);
        });
      }

      generate();
    </script>
  </body>
 </html>
	<!DOCTYPE html>
	<html lang="cn">
	<head>
	<meta charset="UTF-8" />
	<meta name="viewport" content="width=device-width, initial-scale=1.0" />

	<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/xlsx.full.min.js"></script>
	<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/jszip.min.js"></script>
	<!-- <script src="https://cdn.jsdelivr.net/npm/[email protected]/preact/standalone.umd.min.js"></script> -->

	<title>js file</title>
	</head>

	<body>
	<p>
	读取xlsx
	<input class="file" type="file" />
	</p>

	<button onclick="addCol()">添加列</button>
	<button onclick="addRow()">添加行</button>
	<button onclick="generateXlsxFile()">生成xlsx</button>
	<button onclick="generateZip()">生成zip</button>

	<div class="editTable"></div>
	<script>
	let columns = [{ title: 'edit0' }];
	let editParams = [{ edit0: '' }];

	document.querySelector('.file').addEventListener('change', readXlsx);

	function readXlsx(evt) {
	const file = evt.target.files[0];
	const reader = new FileReader();
	reader.onload = (e) => {
	const arr8 = new Uint8Array(e.target.result);
	const workbook = XLSX.read(arr8, { type: 'array' });

	// const data = Object.values(workbook.Sheets)[0]
	const data = workbook.Sheets.SheetJS;
	console.log(
	'🚀 ~ file: crud_xlsx.html ~ line 42 ~ readXlsx ~ data',
	data
	);

	columns = [];
	editParams = [];

	Object.keys(data).forEach((it, idx) => {
	if (!/^!/.test(it)) {
	const col = it.replace(/[0-9]+/, '');
	const row = parseInt(it.replace(/[a-zA-Z]+/, ''));
	const key = data[`${col}1`].v;
	if (row === 1) {
	columns.push({ title: data[it].v });
	} else {
	if (!editParams[row - 1]) {
	editParams[row - 1] = {};
	}
	editParams[row - 1][key] = data[it].v;
	}
	}
	});
	console.log(
	'🚀 ~ file: crud_xlsx.html ~ line 62 ~ Object.keys ~ editParams',
	editParams
	);

	generate();
	};

	reader.readAsArrayBuffer(file);
	}

	function generateXlsx() {
	const wb = XLSX.utils.book_new();
	const newData = editParams.map((row) => {
	return columns.map((col) => row[col.title]);
	});
	newData.unshift(columns.map((it) => it.title));
	const ws = XLSX.utils.aoa_to_sheet(newData);
	XLSX.utils.book_append_sheet(wb, ws, 'SheetJS');
	const resp = XLSX.write(wb, {
	bookType: 'xlsx',
	bookSST: false,
	type: 'array',
	});
	return resp;
	}

	function generateZip() {
	const zip = new JSZip();
	const arr8 = generateXlsx();
	zip.file('test.xlsx', arr8, { binary: true });
	zip.generateAsync({ type: 'blob' }).then((resp) => {
	downFile(resp, 'test.zip');
	});
	}

	function generateXlsxFile() {
	// const arr8 = Uint8Array.from(array);
	const arr8 = generateXlsx();
	const blob = new Blob([arr8]);
	downFile(blob, 'test.xlsx');
	}

	function downFile(blob, name) {
	const a = document.createElement('a');
	a.style.display = 'none';
	a.href = URL.createObjectURL(blob);
	a.download = name;
	document.body.appendChild(a);
	a.click();
	URL.revokeObjectURL(a.href);
	document.body.removeChild(a);
	}

	function addCol() {
	columns.push({
	title: `edit${columns.length}`,
	});
	generate();
	}

	function addRow() {
	editParams.push({});
	generate();
	}

	function deleteRow(row) {
	editParams.splice(row, 1);
	generate();
	}

	function handleInputChange(evt) {
	const val = evt.target.value;
	const idx = evt.target.getAttribute('data-idx');
	const key = evt.target.getAttribute('data-key');
	editParams[idx][key] = val;
	}

	function generate() {
	const dom = document.querySelector('.editTable');
	const rowVal = editParams.map((it, idx) => {
	return columns.map((col) => {
	return {
	val: it[col.title],
	key: col.title,
	};
	});
	});

	const html = rowVal.reduce((next, it, idx) => {
	const newHtml = `
	<tr>
	${it
	.map(
	(td) =>
	`<td class="td"><input data-idx="${idx}" data-key="${
	td.key
	}" class="input" value="${td.val \|\| ''}" /></td>`
	)
	.concat(
	`<td><button onclick="deleteRow(${idx})" ${
	editParams.length === 1 ? 'disabled' : ''
	}>删除</button></td>`
	)
	.join('')}
	</tr>
	`;
	return next + '\n' + newHtml;
	}, '');

	dom.innerHTML = `
	<table border="1">
	<tr>
	${columns
	.map((it) => `<td>${it.title}</td>`)
	.concat(`<td>操作</td>`)
	.join('')}
	</tr>
	${html}
	</table>
	`;
	dom.querySelectorAll('.input').forEach((it) => {
	it.addEventListener('blur', handleInputChange);
	});
	}

	generate();
	</script>
	</body>
	</html>