Next搭配SheetJS 读写文件

安装

yarn add https://cdn.sheetjs.com/xlsx-0.20.0/xlsx-0.20.0.tgz

使用

import * as XLSX from 'xlsx';
​
/* load 'fs' for readFile and writeFile support */
/* 加载fs模块用于支持读写文件 */
import * as fs from 'fs';
XLSX.set_fs(fs);
​
/* load 'stream' for stream support */
/* 加载steam模块用于stream流操作 */
import { Readable } from 'stream';
XLSX.stream.set_readable(Readable);
​
/* load the codepage support library for extended support with older formats  */
/* 用于支持老版本 */
import * as cpexcel from 'xlsx/dist/cpexcel.full.mjs';
XLSX.set_cptable(cpexcel);

导出

创建工作簿（整个excel文件）

我们可以通过XLSX.utils.book_new创建新的工作簿

const workbook = XLSX.utils.book_new();

创建工作表

我们可以使用已清洗的数据集，通过XLSX.utils.json_to_sheet生成一个工作表

const worksheet = XLSX.utils.json_to_sheet(rows);

XLSX.utils.book_new 创建一个新工作簿并将 XLSX.utils.book_append_sheet工作表追加到该工作簿。新工作表将称为“日期”：

const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Dates");

修改指定位置数据

	A	B
1	name	brithday
2	Wang	2001-1-1
3	Li	2002-1-1
4	Huang	1999-1-1

说明：以上的表格模拟Excel中的表格，标题位于A1和B1的位置，分别为name和birthday

需求：两个标题字符串修改为首字母大写的形式

实现：

XLSX.utils.sheet_add_aoa可以将文本值写入从单元格 A1 开始的现有工作表

XLSX.utils.sheet_add_aoa(worksheet, [["Name", "Birthday"]], { origin: "A1" });

调整列宽

某些名称的长度超过默认列宽。列宽是通过设置"!cols"工作表属性来设置的

以下行将列 A 的宽度设置为大约 10 个字符：

worksheet["!cols"] = [ { wch: 10 } ];

说明：

worksheet["!cols"]可以用来配置每一列的属性，所以数组中的第一个元素是设置列A的宽度

导出文件

XLSX.writeFile 创建一个电子表格文件并尝试将其写入系统。在浏览器中，它将尝试提示用户下载文件。在 NodeJS 中，它将写入本地目录。

XLSX.writeFile(workbook, "Presidents.xlsx", { compression: true });

导入

读取文件

const workbook = XLSX.read(file);

readAPI官方文档：docs.sheetjs.com/docs/api/pa…

如果需要Node读取本地文件，需要添加解析类型，其他的解析的类型根据数据类型进行选择：

const workbook = xlsx.read(path.join(__dirname, "./PortfolioSummary.xls"), {
  type: "file",
});

获取工作表名

const workbook = xlsx.read(path.join(__dirname, "./嘻嘻.xlsx"), {
  type: "file",
});
console.log(workbook.SheetNames) // [ "(●'◡'●)", '┭┮﹏┭┮' ]

获取具体工作表信息

var first_sheet = workbook.Sheets[workbook.SheetNames[0]];

工作表转化为HTML

sheet_to_html工具函数可以从工作表生成HTML表

// 生成HTML
const tableHTML = XLSX.utils.sheet_to_html(worksheet);
// 前端使用
<div dangerouslySetInnerHTML={{tableHTML}}/> 

获取工作表行数据

var first_sheet = workbook.Sheets[workbook.SheetNames[0]];
const raw_data = xlsx.utils.sheet_to_json(first_sheet);
console.log(raw_data);

[  { name: '小明', birthday: '1949-10-1' },  { name: 'John Adams', birthday: '1735-10-19' }]

此处需要注意header这个配置，如果设置了{ header: 1 }这个参数，那么会生成二维数组的形式Array<any[]>，如下：

const raw_data = xlsx.utils.sheet_to_json(first_sheet, { header: 1 });
console.log(raw_data);

[  [ 'name', 'birthday' ],
  [ '小明', '1949-10-1' ],
  [ 'John Adams', '1735-10-19' ]
]

根据字母列得到索引

所需数据位于列 I 中。可以使用 XLSX.utils.decode_col得到列索引。

["A", "B", "I"].map((col) => {
  console.log(xlsx.utils.decode_col(col));
  return col;
});// 0 1 8

Next中的应用

引用静态资源

官网文档：docs.sheetjs.com/docs/demos/…

1. 编写 webpack loader 用于转换excel文件

   // base64-loader.js
   function loader(content) {
     /* since `loader.raw` is true, `content` is a Buffer */
     return `export default '${content.toString("base64")}'`;
   }
   /* ensure the function receives a Buffer */
   loader.raw = true;
   module.exports = loader;
   

2. 配置webpack

   // next.config.js
   module.exports = {
     webpack: (config) => {
       /* add to the webpack config module.rules array */
       config.module.rules.push({
         /* `test` matches file extensions */
         test: /.(numbers|xls|xlsx|xlsb)/,
         /* use the loader script */
         use: [ { loader: './base64-loader' } ]
       });
       return config;
     }
   };
   

3. 创建别名，方便引用

   可以在jsconfig.json 或tsconfig.json创建别名

   // tsconfig.json
   {
       "compilerOptions": {
           paths:{
               "baseUrl": ".",
               "paths": {
                   "@/*": ["*"]
               }
           }
   ​
       }
   }
   

   页面可以直接导入文件。官方强烈建议将文件存储在 data 文件夹中。

   import 语句将 sheetjs.xlsx 文件拉取为 Base64 字符串

   import base64 from '@/data/sheetjs.xlsx';
   // ....
   const wb = read(base64, { type: "base64" });
   

   读取模板文件并传输给前端

   import { SuccessModule } from "^config/server/module";
   import { NextRequest, NextResponse } from "next/server";
   // @ts-ignore
   import excelFile from "^data/1.xls";
   import * as XLSX from "xlsx";
   ​
   export async function POST(req: NextRequest) {
       const body = await req.json();
       // 获取body参数
       let { arbitraryNodes, arbitraryDisc } = body;
       // 读取模板文件
       const workbook = XLSX.read(excelFile, {
           type: "base64",
       });
       // 选择工作表
       const sheet = workbook.Sheets[workbook.SheetNames[0]];
       // 修改数据
       XLSX.utils.sheet_add_aoa(sheet, [[arbitraryNodes, , arbitraryDisc, ,]], {
           origin: "A4",
       });
   ​
       // 转化
       const buffer = XLSX.write(workbook, {
           type: "buffer",
           bookType: "xls",
       });
   ​
       const bufferString = buffer.toString("base64");
       return NextResponse.json(bufferString);
   }
   

   前端接收并下载

   let res = await request("/xxx/export", {
       method: "POST",
       data: {
           "arbitraryNodes": 1,
           "arbitraryDisc": 2
       }
   })
   ​
   function base64ToArrayBuffer(base64: string) {
       var binary_string = window.atob(base64);
       var len = binary_string.length;
       var bytes = new Uint8Array(len);
       for (var i = 0; i < len; i++) {
           bytes[i] = binary_string.charCodeAt(i);
       }
       return bytes.buffer;
   }
   const blob = new Blob([base64ToArrayBuffer(res)], {
       type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8'
   });
   let downloadElement = document.createElement('a');
   let href = window.URL.createObjectURL(blob);
   downloadElement.href = href;
   downloadElement.download = `测试表格.xls`;
   document.body.appendChild(downloadElement);
   downloadElement.click();
   document.body.removeChild(downloadElement);
   window.URL.revokeObjectURL(href);