Install dependencies
<script lang="javascript" src="dist/xlsx.full.min.js"></script>
Copy the codeor
npm i xlsx
Copy the codeI. Import and analysis of Excel files
- This import is a background management organization template file
- We rely on VUE and Element to build a demo
<! 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.0" />
<title>Pure front-end Excel parsing and download</title>
<link
rel="stylesheet"
href="https://unpkg.com/element-ui/lib/theme-chalk/index.css"
/>
<! -- import Vue before Element -->
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/vue.js"></script>
<! -- import JavaScript -->
<script src="https://unpkg.com/element-ui/lib/index.js"></script>
<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>
</head>
<body>
<div id="app">
<el-upload
class="upload-demo"
ref="upload"
action="xlsx.html"
:auto-upload="false"
:on-change="getMyExcel"
>
<el-button size="small" type="primary">Select the file</el-button>
</el-upload>
<el-button size="small" type="primary" style="margin-top: 10px"
></el-button ></div>
</body>
<script>
new Vue({
el: "#app".data: function () {
return { visible: false };
},
methods: {
getMyExcel(file, fileList) {
console.log(file); ,}}});</script>
</html>
Copy the codeThe onchang event of Element’s Uopload component can be used to obtain the file as shown in the figure below:
- After obtaining the files, we used SheetJS to parse them
getMyExcel(file, fileList) {
// Use FileReader to convert the data stream
let reader = new FileReader();
reader.onload = function (e) {
let data = e.target.result;
// Use XLSX to parse out the workbook, also known as the imported Excel file
let wb = XLSX.read(data, { type: "binary" });
console.log(wb);
};
//readAsBinaryString Requires arguments to File or Blob objects
reader.readAsBinaryString(file.raw);
// console.log(XLSX.read(file, { type: "array" }));
},
Copy the codeThe following results can be obtained:
SheetNames holds all of the sheets in the workbook and for this demo we’ll focus on the first sheet in SheetNames, sheet1.
In the worksheet we only need to pay attention to these three properties:
! mergesAn array of range objects corresponding to merged cells in a worksheet, c and R being abbreviations for columns and rows, respectively
! refThe range of the worksheet, that is, the coordinates from top left to bottom right
- All cells that have content
The V attribute for each cell is the corresponding content
Having the coordinates and contents of each cell completes our parsing requirements. Official also provides some convenient API, need to go to the official website to see:
2. Export excel files
- First we need to create a WB workbook
let wb = XLSX.utils.book_new();
- Create the contents of the worksheet array, must be two-dimensional, otherwise insert error ~!
let ws_data = [
["S"."h"."e"."e"."t"."J"."S"],
[1.2.3.4.5]];`
Copy the code- Create a worksheet, write the content, and insert it into the workbook.
let ws_name = "Template file";
let ws = XLSX.utils.aoa_to_sheet(ws_data);
XLSX.utils.book_append_sheet(wb, ws, ws_name);
Copy the codeAt this point we can print wb and look:
- The export file
Xlsx. writeFile(wb, filename, write_opts) writes WB to a specific file named filename. In a browser-based environment, this function forces the browser to download. Xlsx. writeFileAsync(filename, WB, O, cb) writes WB to a specific file named filename. If o is omitted, the write function takes the third argument as the callback function.
XLSX. WriteFile (wb, "background configuration template. XLSX ", {bookType:" XLSX "});
Export contents are as follows:
- supplement
If the exported file needs to merge cells need to be added to the worksheet! Merges attribute, given the scope of the merge can be configured if the style needs to be configured, professional version above, paid.
The complete code
<! 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.0" />
<title>Pure front-end Excel parsing and download</title>
<link
rel="stylesheet"
href="https://unpkg.com/element-ui/lib/theme-chalk/index.css"
/>
<! -- import Vue before Element -->
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/vue.js"></script>
<! -- import JavaScript -->
<script src="https://unpkg.com/element-ui/lib/index.js"></script>
<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>
</head>
<body>
<div id="app">
<el-upload
class="upload-demo"
ref="upload"
action="xlsx.html"
:auto-upload="false"
:on-change="getMyExcel"
>
<el-button size="small" type="primary">Select the file</el-button>
</el-upload>
<el-button
size="small"
type="primary"
style="margin-top: 10px"
@click="downLoad"
></el-button ></div>
</body>
<script>
new Vue({
el: "#app".data: function () {
return {};
},
methods: {
getMyExcel(file, fileList) {
// Use FileReader to convert the data stream
let reader = new FileReader();
reader.onload = function (e) {
let data = e.target.result;
// Use XLSX to parse out the workbook, also known as the imported Excel file
let wb = XLSX.read(data, { type: "binary" });
console.log(wb);
};
//readAsBinaryString Requires arguments to File or Blob objects
reader.readAsBinaryString(file.raw);
// console.log(XLSX.read(file, { type: "array" }));
},
downLoad() {
let wb = XLSX.utils.book_new();
// Create an array of the contents of the worksheet
let ws_data = [
["S"."h"."e"."e"."t"."J"."S"],
[1.2.3.4.5]];let ws_name = "Template file";
// Write to the worksheet
let ws = XLSX.utils.aoa_to_sheet(ws_data);
// Insert workbook
XLSX.utils.book_append_sheet(wb, ws, ws_name);
XLSX.writeFile(wb, "Background configuration template.xlsx", { bookType: "xlsx"}); ,}}});</script>
</html>
Copy the codeRefer to the link
Sheetjs official website: github.com/sheetjs/she…
Sheetjs 中文 版 : github.com/liyutg/shee…