十分钟上手 xlsx,4 种方法实现 Excel 导入导出


theme: qklhk-chocolate
highlight: androidstudio


前言

如果你和我一样经常和管理页面打交道,那么 Excel导入数据数据导出Excel 这两个需求一定是逃不掉的。

相信大多数人也知道 xlsx 这个 npm 库,但是文档内容实在是太多,太冗余了。很多人的需求就是一个简单的导入导出,但文档愣是把整个 xlsx 系统给解释了一遍,看了半天也没找到 Demo, 找到 Demo 也跑不太起来。

网上的博客要不扔个 xlsx.utils.sheet_to_json 就完了,要不写很多很长的代码(很多循环那种),唉。就让这篇文章终结这个需求吧!

本篇文章主要内容为

  • 前端处理导入导出
  • 后端处理导入导出
  • 一些简单的组件封装

代码都放在 Github 的 learn-xlsx 上,除此之外,我还用 Jest 写了 单元测试,用 Cypresssuperteste2e 测试,感兴趣的可以 clone 下来按需白嫖哦~

image

需求

我们再来梳理一下需求:

  • 提供一个 Excel 文件,将里面的内容导出成 JSON 数组
  • 提供一个 JSON 数组,生成 Excel 文件并下载

基础知识

首先,在用 xlsx 这个 npm 库前,还是要清楚一些基本的 Office Excel 知识。

看图意会:

image

当我们新建一个 Excel 文档时,这个文档就是 Workbook,而一个 Workbook 下会有多个 sheet。

前端导入 Excel 数据

来看第一个需求:给定一个 Excel 文件,导入里面的数据。

一般来说,需要导入的 Excel 文件都不会一个 Workbook 里有好几十个 Sheet 的,比较常见就一个 Sheet。毕竟管理后台一般就只会导入一个表的数据。所以,下面我都以一个 Sheet 的情况来实现,多个 Sheet 的情况你们自己实现加个循环就好了。

先来实现一个从 File 的 Array Buffer 中读取 Excel 的工具函数:

/**
 * 从 excel 文件读取数据
 * @param excelRcFileBuffer excel 文件
 */
export function importExcelFromBuffer<Item = any>(excelRcFileBuffer: ArrayBuffer): Item[] {
  // 读取表格对象
  const workbook = xlsx.read(excelRcFileBuffer, {type: 'buffer'});
  // 找到第一张表
  const sheetNames = workbook.SheetNames;
  const sheet1 = workbook.Sheets[sheetNames[0]];
  // 读取内容
  return xlsx.utils.sheet_to_json(sheet1);
}

非常直白,就不多废话了。不过,这里估计有人会有疑问:为什么我的入参选择了 ArrayBuffer 呢而不是 File 呢?

其实用 RcFile 或者 File 作为入参也是可以的,只不过我发现在用 Jest 写单元测试时,fs.readFileSync 的返回值只能是 ArrayBuffer,所以这里做了妥协。

下一步:拿到 Excel 文件,并获取其 ArrayByffer。这里我用 Ant Design 的 Upload 组件来获取文件:

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[]>([]);

  // 获取文件并解析
  const localExcelToData = async (options: RcCustomRequestOptions) => {
    const {file, onSuccess, onError} = options;

    try {
      // xlsx 导入 excel
      const excelData = importExcelFromBuffer<ExcelRamenReview>(await (file as RcFile).arrayBuffer());
      // 设置 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">前端Excel转Data</Button>
      </Dragger>
    </Modal>
  )
}

没有用通用的 <input type="file"> 而直接使用 Ant Design 的 <Upload> 组件,是因为我觉得大家一般是要用 Ant Design 来做开发的,直接给业务实践比说理论更实用。

上面还有以下细节:

  • 使用 customRequest 来获取 File,然后将其 buffer 传入 importExcelFromBuffer 函数解析
  • customRequest 里调用了 onSuccess 这个回调,如果不调用它,Upload 组件的就会一直显示 loading 状态,非常烦人
  • accept 里填入 Excel 文件的 MIME Type,用户只能选择 Excel 文件来 “上传”,用户友好
  • 这里我对 Modal 进行二次封装,属于锦上添花(主要目前我的业务要这样设计),可不管

使用如下:

<Button type="primary" onClick={() => setLocalModalVisible(true)}>
  前端Excel转Data
</Button>
<LocalImportModal
  title="前端Excel转Data"
  visible={localModalVisible}
  onCancel={() => setLocalModalVisible(false)}
  onSubmit={data => setDataSource(data)}
/>

前端导出 Excel 文件

导出的难点在于写成 Excel 之后要立马下载,幸好 xlsx 的 xlsx.writeFile 直接帮我们实现这一步了。

/**
 * 导出 excel 文件
 * @param array JSON 数组
 * @param sheetName 第一张表名
 * @param fileName 文件名
 */
export function exportExcelFile(array: any[], sheetName = '表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);
}

使用的时候一个调用就可以了:

<Button
  disabled={dataSource.length === 0}
  onClick={() => exportExcelFile(dataSource)}
  type="primary"
>
  前端Data转Excel
</Button>

后端导入 Excel 数据

为什么又要整个后端的导入导出呢?原因有三:

  • 第一,xlsx 这个库还是挺大的,前端能不装这么大的库就不装了
  • 第二,假如此时别的管理后台又要做数据导入导出,那上面的代码又要重新实现一次,我们更希望可以把这些通用的逻辑收敛到一个地方
  • 第三,后端本来就是干脏活累活的地方,并不委屈

导入 Excel 数据的逻辑也很简单:用 multer 负责文件上传,拿到文件 File 后和上面的导入如法炮制即可。

先把上面的 File to Data 实现一遍,在 utils 里添加 importExcelFromBuffer 函数:

/**
 * 读取 excel
 * @param fileBuffer
 * @returns {unknown[]}
 */
function importExcelFromBuffer(fileBuffer) {
  // 获取 workbook
  const workbook = xlsx.read(fileBuffer, { type: 'buffer' });
  // 获取第一张表名
  const firstSheetName = workbook.SheetNames[0];
  // 获取第一张表
  const firstSheet = workbook.Sheets[firstSheetName]
  // 获取数据
  return xlsx.utils.sheet_to_json(firstSheet);
}

再来实现个路由:

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 })
});

最后前端使用 Ant Design 的 Upload 组件上传 Excel 文件:

const ServerImportModal: FC<Props> = (props) => {
  const {onImport, onSubmit, onOk, onCancel, ...modalProps} = props;

  const [excelData, setExcelData] = useState<any[]>([]);

  // 将文件转到服务端再解析数据
  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>后端Excel转Data</Button>
      </Dragger>
    </Modal>
  )
}

同样的,我这里也对 Modal 进行二次封装,你也可以选择先忽略它。前端需要注意这些点:

  • action 则为我们刚刚实现的 /excel_to_data 接口
  • name 为文件名
  • onChange 为上传状态变化的回调,这里直接抄 Ant Design 的文档就好了,不 BB

使用时,和上面也是差不多的:

<Button type="primary" danger onClick={() => setServerModalVisible(true)}>
  后端Excel转Data
</Button>
<ServerImportModal
  title="后端Excel转Data"
  visible={serverModalVisible}
  onCancel={() => setServerModalVisible(false)}
  onSubmit={data => setDataSource(data)}
/>

后端导出 Excel 文件

本质上就是传入 JSON 数组,生成 Excel 文件直接下载,这里的难点还是在于 直接下载

不慌,先实现最基础 JSON 数组转 Excel 文件的工具函数:

/**
 * 将数据转成 excel
 * @param array
 * @param sheetName
 * @returns {any}
 */
function exportExcelFromData(array, sheetName = '表1') {
  const jsonWorkSheet = xlsx.utils.json_to_sheet(array);
  const workBook = {
    SheetNames: [sheetName],
    Sheets: {
      [sheetName]: jsonWorkSheet,
    }
  };
  return xlsx.write(workBook, {type: 'binary'});
}

和之前实现的差不多,唯一不同点是 xlsx.writeFile 变成了 xlsx.write,返回的是文件的二进制内容。注意:xlsx.writeFile 的返回值是 undefined

再来实现路由:

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

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

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

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

这里的关键点是 Content-Type 设置为 Excel 的 MIME Type,这样返回的内容就是 Excel 的二进制内容了。

最后,来看看前端是怎么接住这个 Excel 文件的:

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");
}

接住 Excel 文件的关键点在于设定 axios 的 responseTypeblob,这个属性是一个经常被人忽略的属性,实际上功能非常强大,比如可以设置为 stream 来操作 来做一些高级玩法。

回到主题,拿到二进制文件后,直接用 file-saver 这个库来实现 直接下载 功能。

直接下载 也不是什么黑科技,其实就是创建一个临时的 <a> 标签,把 url 填到 href,再用 JS 点击一下:

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

不过,这里的场景不允许我们用上面的方法,因为生成的 Excel 文件都以 二进制 返回了,而不是一个 URL,所以只能用 file-saver 来实现直接下载。

到此,Excel 的导入导出,前端与后端的实现都 O 了。

数据清洗

一般来说,用户上传的 Excel 文件表头都是中文的,所以用 xlsx 直接解析出来对象的 key 都是中文,比如:

image
{
  "姓名": 'Jack',
  "年龄": 11
}

而我们的程序一般都以英文作为 key,比如:

{
  "name": 'Jack',
  "age": 11
}

所以在数据导入的时候还要有一步将这些 key 从中文传成中英文,而在导出 Excel 时则将英文转成中文:

/**
 * 转换 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;

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

      return prev;
    }, {});
  })
}

在拼装和组装对象时,reduce 是一个非常好用的函数。使用的时候,我们只需要传入 原数据key 的映射关系 即可:

// key 的映射关系
const keyMaps = {
  姓名: 'name',
  年龄: 'age'
}

// 中文传英文
const data = convertKeys<ExcelRamenReview, RamenReview>(excelData, keyMaps)

总结

最后总结一下:

  • Workbook 就是 Excel 文档,一个 Workbook 下有多个 Sheet,一般来说只操作第一个 Sheet
  • xlsx 这个库只需要关注 writeFile, readFile, write, sheet_to_jsonjson_to_sheet 就够用了
  • 直接下载功能 可以用 file-saver 一步到位,也可以使用添加临时 <a> 标签来模拟下载行为。但由于在接住 Excel 的时候,返回的是临时文件的二进制,所以,用 file-saver 会比较方便
  • 前端要接住二进制的文件,需要在 axiosresponseType 设置为 blob
  • Ant Design 的 Upload 组件非常强大,要善用其给的 props,比如 accept, action, name, customRequest 等,比如前端解析就是用 customRequest “假上传” 来获取 Excel 文件

总的来说,xlsx 这个库还是挺简单的,只是文档比较复杂,像样的 Demo 没几个。

上面也仅实现了 一个 Sheet 的情况,对于多个 Sheet 的情况,大家做个 For 循环就可以了。一般来说管理后面的 Excel 导入也没多少花里胡哨的操作,上面这 4 种情况基本能包含 90% 的应用场景了。

我把上面这 4 种场景的实现都放在 Github 的 learn-xlsx 上了。除此之外,我还用 Jest 写了 单元测试,用 Cypresssuperteste2e 测试,感兴趣的可以 clone 下来直接白嫖哦~

推荐阅读更多精彩内容