Pure front-end generating Excel file operation-WebAssembly & web workers

 

Recently, there is a demand for large data screens in the business, which requires not only to display data, but also to provide data downloads for the selected date range. This article purely records the implementation plan as a note, and the implementation details are very incomplete.

Tool Library

The standard specification of the xlsx spreadsheet is detailed in: Full XML Schema . The following two libraries are based on this specification to realize the reading and writing of this format file (salute!∠(°ゝ°)).

SheetJS

SheetJS is a parser and writer for a variety of spreadsheet formats. A concise JS method is implemented through official specifications, related documents and test files. SheetJS emphasizes the robustness of parsing and writing, its cross-format feature is compatible with the unified JS specification, and the ES3/ES5 browser is backward compatible with IE6.

excelize

A public library written in Go language that can read and write spreadsheet-type files

More silent: webworker

Web Workers provide a simple way for web content to run scripts in a background thread. Threads can perform tasks without interfering with the user interface.

We implemented the process of SheetJS processing data and generating table data (book type data) from a separate thread. (Although starting another thread will not excessively affect the main UI thread from the experience, but the startup cost itself is relatively high).

The catalog of the component is as follows

NewDashboard
├── components
│   ├── LongCard
│   │   ├── echartsOption.ts
│   │   ├── index.tsx
│   │   └── style.module.less
│   └── ShortCard
│       ├── echartsOption.ts
│       ├── index.tsx
│       └── style.module.less
├── index.tsx                        # 在该文件与webworker通信
├── makeTable.ts                     # 在该文件实现webworker
└── style.module.less

The workers of samples given by mdn all load external code. In our organization directory, the worker should be implemented in a file and export a worker instance. Here you need to use URL.createObjectURL(blob) to construct an external link.

code show as below:

// @file makeTable.ts
const blob = new Blob(
  [
    `
    importScripts('https://g.alicdn.com/code/lib/xlsx/0.17.4/xlsx.full.min.js');
    const GOODS_EFFECT_TITLE = [
      '开播时间',
      '下播时间',
      '直播间',
      '商品名称',
      '商品',
      '点击人数',
      '成交人数',
      '粉丝成交比例',
      '引导成交金额',
    ];
    
    // 接收主进程的表格数据
    onmessage = function({ data }) {
      console.log('from main routine', data);
      const book = XLSX.utils.book_new();
      const sheet = XLSX.utils.aoa_to_sheet([GOODS_EFFECT_TITLE, ...data]);
      XLSX.utils.book_append_sheet(book, sheet, '工作表1');
      
      // book的数据回传给主进程
      postMessage({ book });
    };
`,
  ],
  { type: 'text/javascript' },
);

export const worker = new Worker(URL.createObjectURL(blob));

Pay attention to a few points:

1. Since there are no objects such as DOM and windows in the worker, there is no way to directly export the table elements as xlsx table data using the XLSX.utils.table_to_book method.
2. The importScript method loads all the listed resources in parallel, but the execution is synchronous. Here you need to load SheetJS resources into the worker.
3. Method of the main process:

  // @file index.tsx
import { worker } from './makeTable';

function download() {
   // aoa_to_sheet 方法需要一个二维数组来形成电子表格
    worker.postMessage([[1, 2, 3]]);
    worker.onmessage = ({ data }) => {
      window.XLSX.writeFile(data.book, '测试.xlsx');
    };
  }

Higher speed: WebAssembly

For network platforms, WebAssembly is of great significance-it provides a way to enable code written in various languages to run on the Web at near-native speeds. In this case, all client software that could not be run in this way before will be able to run on the Web.

We use Go language to compile to wasm file, the core code is as follows:

// wasm.go
func main() {
    c := make(chan struct{}, 0)
  // js全局方法makeExcel
    js.Global().Set("makeExcel", js.FuncOf(jsMakeExcel))
  // 确保Go程序不退出
    <-c 
}

func makeExcel() []uint8 {
    f := excelize.NewFile()
    f.SetCellValue("Sheet1", "开播时间", now.Format(time.ANSIC))
    f.SetCellValue("Sheet1", "直播间", 1111)
  // 在js环境中无法实现文件的操作
    // if err := f.SaveAs("simple.xlsx"); err != nil {
    //     log.Fatal((err))
    // }
    buf, _ := f.WriteToBuffer()
    res := make([]uint8, buf.Len())
    buf.Read(res)
    return res
}

func jsMakeExcel(arg1 js.Value, arg2 []js.Value) interface{} {
    buf := makeExcel()
    js_uint := js.Global().Get("Uint8Array").New(len(buf))
    js.CopyBytesToJS(js_uint, buf)
  //go的uint8无法直接回传，需要创建js环境的Uint8Array类型数据并回传
    return js_uint
}

Load the compiled wasm file into the js environment

1. Introduce the bridge code: https://github.com/golang/go/blob/master/misc/wasm/wasm_exec.js . At this time, there will be a global constructor under the window: Go
2. Boilerplate code-instantiate webassembly:

// WebAssembly.instantiateStreaming is not currently available in Safari 
if (WebAssembly && !WebAssembly.instantiateStreaming) {
      // polyfill
      WebAssembly.instantiateStreaming = async (resp, importObject) => {
        const source = await (await resp).arrayBuffer();
        return await WebAssembly.instantiate(source, importObject);
      };
    }

    const go = new Go();

    fetch('path/to/wasm.wasm')
      .then((response) => response.arrayBuffer())
      .then((bytes) => WebAssembly.instantiate(bytes, go.importObject))
      .then((res) => go.run(res.instance))

3. Realize file download

function download() {
   // 与普通方法一样调用go写入全局的方法，拿到刚刚回传的uint8array数据
         const buf = makeExcel();
   // 创建下载链接，注意文件类型，并下载文件
    const blob = new Blob([buf], {
      type: 'application/vnd.ms-excel',
    });
    const url = URL.createObjectURL(blob);
    console.log({ blob, str });
    const a = document.createElement('a');
    a.download = 'test.xlsx';
    a.href = url;
    a.click();
}

Both need and need

webworker and webassembly can be used together, to be added...