preface

If you work with admin pages as much as I do, the need for Excel to import data and Excel to export data is inevitable.

Most of you know XLSX as an NPM library, but the documentation is too much and redundant. Many people’s demand is a simple import and export, but the document is the entire XLSX system to explain again, look for a long time did not find the Demo, find the Demo is not very good.

Blogs on the web either throw out xlsx.utils.sheet_to_json or write lots of long code (lots of loops). Let this article put an end to that need!

The main contents of this article are as follows

  • The front-end handles imports and exports
  • The backend handles imports and exports
  • Some simple component encapsulation

The code is stored on Github’s Learn-Xlsx. In addition, I also wrote unit tests using Jest, and did E2E tests using Cypress and Supertest. If you are interested, you can clone them as needed

demand

Let’s sort out the requirements again:

  • Provide an Excel file and export the contents into a JSON array
  • Provide a JSON array, generate an Excel file and download it

Basic knowledge of

First of all, before using the NPM library XLSX, it is important to know some basic knowledge of Office Excel.

Photo exhibition:

When we create a new Excel document, that document is the Workbook, and a Workbook has multiple sheets under it.

Front-end import Excel data

Let’s look at the first requirement: given an Excel file, import the data in it.

Generally speaking, the Excel file that needs to be imported will not have dozens of sheets in a Workbook. It is more common to have only one Sheet. After all, the admin background usually only imports data from one table. So, I’m going to do it with one Sheet, and you can do it with multiple sheets and just add a loop.

Let’s implement a utility function that reads Excel from a File’s Array Buffer:

/** * Read data from excel file *@param ExcelRcFileBuffer Excel file */
export function importExcelFromBuffer<Item = any> (excelRcFileBuffer: ArrayBuffer) :Item[] {
  // Read the table object
  const workbook = xlsx.read(excelRcFileBuffer, {type: 'buffer'});
  // find the first table
  const sheetNames = workbook.SheetNames;
  const sheet1 = workbook.Sheets[sheetNames[0]].// Read the content
  return xlsx.utils.sheet_to_json(sheet1);
}
Copy the code

It’s pretty straightforward. I won’t talk too much. Why did I choose ArrayBuffer instead of File as my input parameter?

It is possible to use RcFile or File as the input parameter, but I found that when writing unit tests using Jest, fs.readfilesync returned only ArrayBuffer, so I made a compromise here.

Next step: Get the Excel file and get its ArrayByffer. Here I use Ant Design’s Upload component to get the file:

const excelMimeType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';

const LocalImportModal: FC<Props> = (props) = > {
  const{onImport, onSubmit, onCancel, onOk, ... modalProps} = props;const [excelData, setExcelData] = useState<any[] > ([]);// Get the file and parse it
  const localExcelToData = async (options: RcCustomRequestOptions) => {
    const {file, onSuccess, onError} = options;

    try {
      // XLSX imports Excel
      const excelData = importExcelFromBuffer<ExcelRamenReview>(await (file as RcFile).arrayBuffer());
      / / set the data
      setExcelData(excelData);

      if (onImport) onImport(data);

      if (onSuccess) onSuccess(data, new XMLHttpRequest());
    } catch (e) {
      if (onError) onError(e)
    }
  }

  const innerOnOk = (e: React.MouseEvent<HTMLElement>) = > {
    if (onSubmit) onSubmit(excelData);
    if (onOk) onOk(e);
    if (onCancel) onCancel(e);
  }

  return (
    <Modal onCancel={onCancel} onOk={innerOnOk} {. modalProps} >
      <Dragger accept={excelMimeType} customRequest={localExcelToData}>
        <Button type="primary">Front-end Excel to Data</Button>
      </Dragger>
    </Modal>)}Copy the code

I didn’t use the generic and used Ant Design’s

component directly because I felt that most people wanted to use Ant Design for development and it was more practical to use the business practice rather than the theory.

It also has the following details:

  • usecustomRequestTo get File, and then put itbufferThe incomingimportExcelFromBufferFunction analysis
  • incustomRequestIn the callonSuccessThis callback, if not called, will always display loading state in the Upload component, which is very annoying
  • acceptFill it with Excel filesMIME Type, users can only select Excel files to “upload”, user-friendly
  • Here I do double encapsulation of Modal, which is the icing on the cake (mainly my business is designed in this way), but no matter

Use as follows:

<Button type="primary" onClick={()= >SetLocalModalVisible (true)}> Front End Excel to Data</Button>
<LocalImportModal
  title="Front-end Excel to Data"
  visible={localModalVisible}
  onCancel={()= > setLocalModalVisible(false)}
  onSubmit={data => setDataSource(data)}
/>
Copy the code

Front-end export Excel files

The difficulty is that you need to download it as soon as you write it in Excel. Fortunately, XLSX’s Xlsx. writeFile does this for you directly.

/** * Export excel file *@param Array JSON array *@param SheetName First table name *@param FileName indicates the fileName */
export function exportExcelFile(array: any[], sheetName = 'table 1', fileName = 'example.xlsx') {
  const jsonWorkSheet = xlsx.utils.json_to_sheet(array);
  const workBook: WorkBook = {
    SheetNames: [sheetName],
    Sheets: {
      [sheetName]: jsonWorkSheet,
    }
  };
  return xlsx.writeFile(workBook, fileName);
}
Copy the code

A single call will do:

<Button
  disabled={dataSource.length= = =0}
  onClick={()= >ExportExcelFile (dataSource)} type="primary"</Button>
Copy the code

Back-end import Excel data

Why the whole backend import and export? There are three reasons:

  • First, the XLSX library is quite large, the front-end can not install such a large library will not install
  • Second, if at this time other management background to do data import and export, the above code has to be implemented again, we hope to be able to convergence of these general logic to a place
  • Third, the back end is the place to do the dirty work

The logic for importing Excel data is also simple: use Multer to upload files, and do the same with the import File.

ImportExcelFromBuffer = importExcelFromBuffer = importExcelFromBuffer = importExcelFromBuffer

/** * Read excel *@param fileBuffer
 * @returns {unknown[]}* /
function importExcelFromBuffer(fileBuffer) {
  / / get the workbook
  const workbook = xlsx.read(fileBuffer, { type: 'buffer' });
  // Get the first table name
  const firstSheetName = workbook.SheetNames[0];
  // Get the first table
  const firstSheet = workbook.Sheets[firstSheetName]
  // Get data
  return xlsx.utils.sheet_to_json(firstSheet);
}
Copy the code

Let’s implement another route:

var express = require('express');
var multer  = require('multer')
var {importExcelFromBuffer} = require('.. /utils')

var upload = multer()
var router = express.Router();

var excelMimeType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';

router.post('/excel_to_data', upload.single('excel'), (req, res) = > {
  const data = importExcelFromBuffer(req.file.buffer);

  res.json({ data })
});
Copy the code

Finally, the front end uses Ant Design’s Upload component to Upload an Excel file:

const ServerImportModal: FC<Props> = (props) = > {
  const{onImport, onSubmit, onOk, onCancel, ... modalProps} = props;const [excelData, setExcelData] = useState<any[] > ([]);// Forward the file to the server and parse the data
  const serverExcelToData = (info: UploadChangeParam) = > {
    const { status, response } = info.file;
    if (status === 'done') {
      if (onImport) {
        onImport(response.data);
      }
      setExcelData(response.data);
    } else if (info.file.status === 'error') {
      console.error('error', info.file.name); }}const innerOnOk = (e: React.MouseEvent<HTMLElement>) = > {
    if (onSubmit) onSubmit(excelData);
    if (onOk) onOk(e);
    if (onCancel) onCancel(e);
  }

  return (
    <Modal onOk={innerOnOk} onCancel={onCancel} {. modalProps} >
      <Dragger
        action={` ${baseURL} /excel_to_data`}
        name="excel"
        accept={excelMimeType}
        onChange={serverExcelToData}
      >
        <Button type="primary" danger>Back-end Excel to Data</Button>
      </Dragger>
    </Modal>)}Copy the code

And again, I’m doing double encapsulation of Modal, so you can just ignore it. The front end needs to be aware of these points:

  • actionIs the /excel_to_data interface we just implemented
  • nameFor the file name
  • onChangeTo upload state change callbacks, simply copy the Ant Design documentation

When used, it is similar to the above:

<Button type="primary" danger onClick={()= >SetServerModalVisible (true)}> Back end Excel to Data</Button>
<ServerImportModal
  title="Back-end Excel to Data"
  visible={serverModalVisible}
  onCancel={()= > setServerModalVisible(false)}
  onSubmit={data => setDataSource(data)}
/>
Copy the code

Export Excel files from the backend

Essentially, you pass in a JSON array, generate an Excel file and download it directly, but the challenge here is to download it directly.

Don’t panic, first implement the most basic JSON array to Excel file tool function:

/** * Convert data to Excel *@param array
 * @param sheetName
 * @returns {any}* /
function exportExcelFromData(array, sheetName = 'table 1') {
  const jsonWorkSheet = xlsx.utils.json_to_sheet(array);
  const workBook = {
    SheetNames: [sheetName],
    Sheets: {
      [sheetName]: jsonWorkSheet,
    }
  };
  return xlsx.write(workBook, {type: 'binary'});
}
Copy the code

WriteFile is now xlsx.write, which returns the binary contents of the file. Note: the return value of xlsx.writeFile is undefined.

To implement routing:

var excelMimeType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';

router.post('/data_to_excel'.(req, res) = > {
  const {data} = req.body;

  const fileBuffer = exportExcelFromData(data, 'table 1');

  res.writeHead(200, { 'Content-Type': excelMimeType });
  res.end(new Buffer(fileBuffer, 'binary'));
})
Copy the code

The key point here is that content-Type is set to Excel’S MIME Type, so that the Content returned is Excel’s binary Content.

Finally, let’s see how the front end holds the Excel file:

import {saveAs} from 'file-saver'

const http = axios.create({baseURL});

const serverDataToExcel = async() = > {const response = await http.post('/data_to_excel', {
    data: dataSource,
  }, { responseType: 'blob' })

  saveAs(response.data, "test.xlsx");
}
Copy the code

The key to catching an Excel file is setting axios’s responseType to blob. This property is an oft-overlooked property that can actually be very powerful, such as setting it to stream to manipulate streams for advanced gameplay.

Back to the theme, once you’ve picked up the binary, use the file-Saver library to download it directly.

Create a temporary tag, put the URL in href, and use JS to click:

function downloadURI(uri, name) {
    var link = document.createElement("a");
    link.setAttribute('download', name);
    link.href = uri;
    document.body.appendChild(link);
    link.click();
    link.remove();
}
Copy the code

However, this scenario doesn’t allow us to use the above method, because the generated Excel file is returned as a binary instead of a URL, so file-Saver is the only way to download directly.

To this point, Excel import and export, front-end and back-end implementation are O.

Data cleaning

Generally speaking, the header of the Excel file uploaded by users is In Chinese, so the key of the object directly resolved by XLSX is in Chinese, for example:

{
  "Name": 'Jack',
  "Age": 11
}
Copy the code

Our programs generally use English as key, such as:

{
  "name": 'Jack',
  "age": 11
}
Copy the code

Therefore, there is a step to transfer these keys from Chinese to Chinese when importing data, and from English to Chinese when exporting Excel:

/** * convert key *@param excelData
 * @param keysMap* /
export function convertKeys<Raw = any.Target = any> (excelData: Raw[], keysMap: Record<string.string>) :Target[] {
  return excelData.map(excelItem= > {
    return Object.entries(excelItem).reduce((prev: any, curt) = > {
      const [curtKey, curtValue] = curt;

      / / update the key
      const mappedKey = keysMap[curtKey];
      if (mappedKey) {
        prev[mappedKey] = curtValue;
      } else {
        prev[curtKey] = curtValue;
      }

      returnprev; }, {}); })}Copy the code

Reduce is a very useful function for assembling and assembling objects. To use it, we just need to pass in the mapping between the original data and the key:

// Key mapping
constKeyMaps = {name:'name'Age:'age'
}

// Chinese to English
const data = convertKeys<ExcelRamenReview, RamenReview>(excelData, keyMaps)
Copy the code

conclusion

To sum up:

  • A Workbook is an Excel document. There are multiple sheets in a Workbook. Generally, only the first Sheet is operated
  • xlsxThis library just needs attentionwriteFile.readFile.write.sheet_to_json 和 json_to_sheetWill do the trick
  • Direct download functionYou can usefile-saverOne step in place can also be used to add temporarily<a>Tag to simulate download behavior. But since the binary of the temporary file is returned when you catch Excel, so,file-saverIt would be more convenient
  • The front end to catch binary files needs to be inaxios 的 responseTypeSet toblob
  • Ant Design’s Upload component is very powerful, so take advantage of what it gives youprops, such asaccept.action.name.customRequestAnd so on, such as front-end parsing is usedcustomRequest“Fake upload” to get Excel files

Overall, the XLSX library is quite simple, but the documentation is complex and there are few decent demos.

In the case of multiple sheets, we can do a For loop. Generally speaking, managing the Excel import is not a lot of bells and whistles, and the above four cases cover 90% of the application scenario.

I put all four of these scenarios on Github’s Learn-Xlsx. In addition, I also used Jest to write unit tests and Cypress and Supertest to do E2E tests. If you are interested, you can clone it directly