🌼 presentation layer

Here I am using antD Upload component

Reference the ANTD section of the code

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

<Upload {. props} fileList={state.fileList}>
    <Button type="primary" >Excel import</Button>
</Upload>

<Button type="primary" onClick={handleExport}>Excel export</Button>
Copy the code

🌴 business layer

First, the job:

  • Import Excel: users upload Excel tables and convert the table contents into JSON objects for the convenience of back-end processing, and the data is stored in the database in the back-end;
  • Excel export: Obtain the data in json format on the back end, convert the data into a workbook object on the front end, convert the generated objects into Excel tables, download and export;

Here are the technical details

✨ Core plug-in XLSX

Install XLSX: NPM install XLSX –save-dev

The core API is described as follows:

  • Xlsx. read(data,type) // Parse Excel data

  • Workbook.sheets [workbook.sheetNames [0]] workbook.sheets [workbook.sheetNames [0]] workbook.sheets [workbook.sheetNames [0]] workbook.sheets [workbook.sheetNames [0]

  • Xlsx.utils.sheet_to_json (wb.sheets [wb.sheetNames [0]], {header:1,defval: “}) // Convert the workbook object to an array of JSON objects, note that if defval is not set to ‘ ‘, the default value is empty

  • Xlsx.utils.json_to_sheet (json) // Convert the JSON object to a workbook object

// The workbook understands:
{
    SheetNames: ['sheet1'.'sheet2'].Sheets: {
        // worksheet
        'sheet1': {
            // cell
            'A1': {... },// cell
            'A2': {... },... },// worksheet
        'sheet2': {
            // cell
            'A1': {... },// cell
            'A2': {... },... }}}Copy the code

🍒 excel import

Core code:

const f = file;
const reader = new FileReader();
reader.onload = function (e) {
    try{
        const datas = e.target.result;
        const workbook = XLSX.read(datas, {type: "binary"});/ / analytical datas
        const first_worksheet = workbook.Sheets[workbook.SheetNames[0]].// is the first sheet in the workbook
        const jsonArr = XLSX.utils.sheet_to_json(first_worksheet, {header: 1.defval:' '}); // Convert the workbook object to an array of JSON objects
        handleImpotedJson(jsonArr)// Array processing
        message.success('Excel upload parsed successfully! ')}catch(e){
      message.error('Incorrect file type! Or file parsing error ')}}; reader.readAsBinaryString(f);Copy the code

To understand:

  1. FileReader object instantiationfileObjects in theonloadEvent
  2. XLSX. Read parsingdata
  3. Xlsx.utils.sheet_to_json (first_worksheet, {header: 1,defval: “”}) converts the parsed workbook object toJSONobject

🍇 excel export

Core code:

const downloadExcel = () = >{
    const json = handleExportedJson(data)
    const sheet = XLSX.utils.json_to_sheet(json);
    openDownloadDialog(sheet2blob(sheet,"Sheet1"), "Download file.xls")}const handleExportedJson = (array) = >{... }// Processing Json data
const openDownloadDialog = (url, saveName) = >{... }// Open the download
const sheet2blob = (sheet, sheetName) = >{... }// Convert to bloB type
Copy the code

To understand:

  1. After being treatedjsonFormat data
  2. XLSX. Utils. Json_to_sheet (json) convertssheetWorkbook object
  3. Sheet2blob (Sheet,saveName) Converts a workbook object toblob
  4. OpenDownloadDialog create bloB address through<a>The tag implements the download action

🍑 Excel export plugin (js-export-excel)

The reason why I didn’t put self-implementing code before is because I found that there are good plugins, the code is very simple.

Core code:

// Export the file directly
let dataTable = [];  // The data content in the Excel file
let option = {};  // Option stands for the Excel file
dataTable  = data;  / / the data source
option.fileName = "Download file";  // Excel file name
console.log("data===",dataTable)
option.datas = [
    {
        sheetData: dataTable,  // The data source in the Excel file
        sheetName: 'Sheet1'.// Name of the sheet page in the Excel file
        sheetFilter: ['id'.'name'.'belong'.'step'.'tag'].// Column data to be displayed in the Excel file
        sheetHeader: ['project id'.'Project name'.'Company'.'Project Phase'.'Item Label'].// The header name of each column in the Excel file}]let toExcel = new ExportJsonExcel(option);  // Generate the Excel file
toExcel.saveExcel();  // Download the Excel file
Copy the code

For the basic use of this plug-in, also support export Blob, support compression, see the official website for details

Explain the core option:

  • FileName download fileName (default: download)

  • Datas data:

Multiple sheet / * * /
/* Each sheet is an object */
[{
    sheetData: []./ / data
    sheetName:' '.// (not required) Sheetname, sheet1 by default
    sheetFilter: [].// (Not required) column filtering (only works if data is object)
    sheetHeader: []// The first line is the heading
    columnWidths: [] // (not required) column width, corresponding to column order
}]
Copy the code

Browser support: IE 10+ I tested down demo in Chrom, Safari, IE are available.

🌸 Effect

Also don’t know can see GitHub demo source code

🍀 epilogue

This is a simple business implementation, carefully summarized. 💗 Thank you for seeing this ~ 💗, if you feel good, please click 👍

Just finished the busy graduation thing, things are not so much, and can slowly post, preview the next article, about my “graduation design”, let’s see a rendering:

I think this can be changed to 🌟 blog, after all, it has been developed for a long time, and I think it will be useful 😂