To be honest, the company is so bored that I don’t know why I’m writing a blog about it

I. Environment construction

For the moment, I will not say that I use my own simple scaffolding to build, and there is a lot of code unrelated to Excel, I suggest that you use your own scaffolding better. This is my Github project address nuggets Demo. Baidu create-React-app and other official recommended scaffolding bar. This section only explains the core functions of Excel.

Second, Excel import

1. Create a TSX file

I’m not really familiar with TS, so I’m happy with the TSX. The empty suffix ts is actually JS (grammar). A total of one page, page address TSX file.

2. Reference some antD components

For the sake of simplicity and clarity we refer directly to some components of ANTD

import { Button,Table,Upload } from 'antd';

<Upload {. uploadProps} >
    <Button type="primary" >Excel import</Button>
</Upload>

<Button type="primary" onClick={this.handleExportAll}>Excel export data</Button>

<Button type="primary" onClick={this.handleExportDocument}>Excel exports format files</Button>

<Table columns={columns} dataSource={data} bordered></Table>
Copy the code

3. Core plug-insxlsxintroduce

1. Install

npm install xlsx --save-dev
Copy the code

2. Introduction to using API

1.XLSX.read(data,type)

Try parsing data the type of data parsingCopy the code

2.workbook.Sheets[workbook.SheetNames[0]]

Is an ordered list of worksheets in a workbookCopy the code

3.XLSX.utils.sheet_to_json(first_worksheet, { header: 1 });

Convert workbook objects into JSON object arraysCopy the code

More apis can be found on the XLSX Git website

3. Import the implementation

1. First write antD into react Render

/* Component parts */<Upload {. uploadProps} >
    <Button type="primary" >Excel import</Button>
</Upload>
Copy the code
/*js */ const uploadProps={onRemove: file => {this.setState(state => ({data:[], fileList:[]})); /* uploadProps={onRemove: file => {this.setState(state => ({data:[], fileList:[]})); }, accept: ".xls,.xlsx,application/vnd.ms-excel", beforeUpload: (file) => { const _this=this; const f = file; const reader = new FileReader(); reader.onload = function (e) { const datas = e.target.result; const workbook = XLSX.read(datas, { type: 'binary' }); // Try to parse datas const first_worksheet = workbook.sheets [workbook.sheetnames [0]]; // Is an ordered list of worksheets in a workbook const jsonArr = xlsx.utils.sheet_to_json (first_worksheet, {header: 1}); // Convert workbook object to JSON object array _this.handleIMPOtedJSON (jsonArr, file); }; reader.readAsBinaryString(f); return false; }, fileList, };Copy the code
  1. onRemoveIs the action triggered when a file is removed, in this case wiping data.
  2. acceptIs the type of file that accepts uploads.
  3. beforeUploadIs the hook before uploading a file. If false is returned, upload will stop.
  4. fileListIt’s a list of files.

See antd Upload’s official website for more APIS

2. Core code understanding

There are only a few lines

const f = file;
const reader = new FileReader();
reader.onload = function (e) {
    const datas = e.target.result;
    const workbook = XLSX.read(datas, {
        type: 'binary'
    });// Try to parse datas

    const first_worksheet = workbook.Sheets[workbook.SheetNames[0]].// is an ordered list of worksheets in a workbook
    const jsonArr = XLSX.utils.sheet_to_json(first_worksheet, { header: 1 });// Convert workbook objects to JSON object arrays
    _this.handleImpotedJson(jsonArr, file);
};
reader.readAsBinaryString(f);
Copy the code

1. First the FileReader object instantiates a File object

2. Process the file object onload event

3. Parse data using xlsx. read

4. First_worksheet is parsed data

5 .XLSX.utils.sheet_to_json(first_worksheet, { header: 1 }); Convert workbook objects to JSON objects

6. The handleImpotedJson method converts a JSON object into a table by performing a series of operations

handleImpotedJson = (array, file) => { const header = array[0]; // Header data [" name ",...]  const entozh = this.formatTitleOrFileld('title', 'dataIndex'); // Convert an array of table fields to an object, such as {" name" :"name",... } const firstRow = header.map(item => entozh[item]); ["name",...]  const newArray = [...array]; newArray.splice(0, 1); Const json = newarray.map ((item, index) => {const newitem = {}; item.forEach((im, i) => { const newKey = firstRow[i] || i; newitem[newKey] = im }) return newitem; }); // Const formatData = json.map(item => ({name: item.name, age: item.age, address: Item.address,})) this.setState({data: formatData, fileList: [file]}); return formatData; } formatTitleOrFileld = (a, b) => { const entozh = {}; this.state.columns.forEach(item => { entozh[item[a]] = item[b] }) return entozh; }Copy the code

3. The renderings

Third, Excel export

1. Excel exports table data

1. Code first

<Button type="primary" onClick={this.handleExportAll}>Excel export data</Button>HandleExportAll = (e) => {const entoen = {"name":" name", "age":" age", "address":" address"} const nowData = this.state.data; const json = nowdata.map((item) => { return Object.keys(item).reduce((newData, key) => { const newKey = entozh[key] || key newData[newKey] = item[key] return newData }, {}) }); const sheet = XLSX.utils.json_to_sheet(json); This. OpenDownloadDialog (this.sheet2blob(sheet,undefined), 'all information.xlsx'); } openDownloadDialog = (url, saveName) => { if (typeof url == 'object' && url instanceof Blob) { url = URL.createObjectURL(url); Var aLink = document.createElement('a'); aLink.href = url; aLink.download = saveName || ''; / / HTML 5 additional attributes, save the file name is specified, the can not suffixes, note that file:/// mode will not take effect var event; if (window.MouseEvent) event = new MouseEvent('click'); else { event = document.createEvent('MouseEvents'); event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null); } aLink.dispatchEvent(event); } sheet2blob = (sheet, sheetName) => { sheetName = sheetName || 'sheet1'; var workbook = { SheetNames: [sheetName], Sheets: {} }; workbook.Sheets[sheetName] = sheet; Var wopts = {bookType: 'XLSX ', // The file type to generate bookSST: Type: 'binary'} type: 'binary'} type: 'binary'} var wbout = XLSX.write(workbook, wopts); var blob = new Blob([s2ab(wbout)], { type: "application/octet-stream" }); Function s2ab(s) {var buf = new ArrayBuffer(s.length); var view = new Uint8Array(buf); for (var i = 0; i ! = s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF; return buf; } return blob; }Copy the code

2. Core code explanation

1.XLSX.utils.json_to_sheet

Literally, you can convert a workbook object into a JSON objectCopy the code

2.openDownloadDialog

This method creates an A tag to download files using the download attribute of the A tagCopy the code

3.sheet2blob

We can convert workbook objects to what we needCopy the code

3. The renderings

2. Export the table in standard format

In fact, the same as above only save the table header data on the line, the only difference is

let nowdata = [
            {"name":""},
            {"age":""},
            {"address":""},
            
];
Copy the code

Convert the data object in state to an empty array.

If you don’t understand, you can ask me on wechat