Recently, there is a demand for large data screen in business, which requires not only displaying data, but also providing data download in the selected date range. This paper only records the implementation scheme as a note, and the implementation details are very incomplete.

Tool library

The standard specifications of xlsx spreadsheet are shown in: Full XML Schema . The following two libraries implement the reading and writing of such format files (salute! ∠ (°ゝ °)) based on this specification.

SheetJS

SheetJS is a parser and writer for a variety of spreadsheet formats. Implement a concise JS method through official specifications, relevant documents and test files. SheetJS emphasizes the robustness of parsing and writing, its cross format characteristics are compatible with the unified JS specification, and ES3/ES5 browsers are backward compatible with IE6.

excelize

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

More silent: webworker

Web   Worker provides a simple way for web content to run scripts in background threads. Threads can perform tasks without interfering with the user interface.

We will implement the process of SheetJS processing data and generating table data (book type data) as another thread. (although starting another thread will not excessively affect the main UI thread in terms of experience, its startup cost is relatively high).

The directory of this component is as follows

NewDashboard
├── components
│   ├── LongCard
│   │   ├── echartsOption.ts
│   │   ├── index.tsx
│   │   └── style.module.less
│   └── ShortCard
│       ├── echartsOption.ts
│       ├── index.tsx
│       └── style.module.less
├── index.tsx                        #  Communicate with webworker in this file
├── makeTable.ts                     #  Implement webworker in this file
└── style.module.less

The workers of samples given by mdn are loaded with external code. In our organization directory, worker should be implemented in a file and an instance of worker should be exported. Here, you need to construct an external chain with the help of URL.createObjectURL(blob).

The code is as follows:

// @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 = [
      'Start time',
      'Downcast time',
      'Live broadcasting room',
      'Trade name',
      'commodity',
      'Number of hits',
      'Number of transactions',
      'Transaction proportion of fans',
      'Guided transaction amount',
    ];
    
    //  Receive tabular data of the main process
    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, 'Worksheet 1');
      
      //  The data of book is returned to the main process
      postMessage({ book });
    };
`,
  ],
  { type: 'text/javascript' },
);

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

Several points to note:

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

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

function download() {
   //  aoa_to_sheet   Method requires a two-dimensional array to form a spreadsheet
    worker.postMessage([[1, 2, 3]]);
    worker.onmessage = ({ data }) => {
      window.XLSX.writeFile(data.book, 'test.xlsx');
    };
  }

Higher speed: WebAssembly

For Web platforms, Web assembly is of great significance - it provides a way for code written in various languages to run in the Web at a near native speed. In this case, any client software that could not be run in this way before will be able to run in the Web.

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

// wasm.go
func main() {
    c := make(chan struct{}, 0)
  //  js global method makeExcel
    js.Global().Set("makeExcel", js.FuncOf(jsMakeExcel))
  //  Ensure that the Go program does not exit
    <-c 
}

func makeExcel() []uint8 {
    f := excelize.NewFile()
    f.SetCellValue("Sheet1", "Start time", now.Format(time.ANSIC))
    f.SetCellValue("Sheet1", "Live broadcasting room", 1111)
  //  File operation cannot be realized in js environment
    // 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)
  //uint8 of go cannot be returned directly. You need to create Uint8Array type data of js environment and return it
    return js_uint
}

Load the compiled wasm file into the js environment

1. Introduce Bridge Code: https://github.com/golang/go/... . At this time, there will be a global constructor under the window: Go
2. Template 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))

1. Realize file download

function download() {
   //  Like the normal method, call go to write the global method to get the uint8 array data just returned
         const buf = makeExcel();
   //  Create a download link, pay attention to the file type, and download the file
    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 and

webworker and webassembly can be used together, to be added