There are many demands for front-end excel export, but there are not many good libraries on the market, and even fewer articles explaining complex usage scenarios.

This article will be in the form of text + demo source, and strive to explain the ultimate Excel export scheme to meet 99% of the usage scenarios.

If the project uses AntD, this is even easier because the Table itself already has the column and dataSource set, and you can quickly export Excel by parsing the column and dataSource.

Functions:

  • Simple table export
  • Add styles to tables (change background color, change font, size, color)
  • Set the row height and column width
  • The Table of Ant-Design is directly exported to Excel, and the column width in Excel is dynamically calculated according to the column width set in antD page
  • Multilevel table headers (row merge, column merge)
  • Put multiple tables in a sheet and implement different column widths for each table

Source code address: github.com/cachecats/e…

First, technology selection

xlsx

The top pick is XLSX, or SheetJS, the most downloaded and star library. I tried it out and it was great, but! Changing styles is not supported by default. To support changing styles, you need to use a paid version of it.

In order to be frugal, many people use another third-party library: xlsX-style, but it is extremely complicated to use and requires modifying the node_modules source code. This library was last updated 6 years ago. There are other third-party style extensions, of varying quality.

The cost of use and maintenance was high and had to be abandoned.

ExcelJS

ExcelJS weekly download 450K, Github star 9K, and have Chinese documents, many domestic developers are very friendly. Although the document is in the form of a README, which is not very readable, the emphasis is on the content, and the common functions are basically covered.

Last updated in 6 months, tried it out, integration is simple, and documentation is abundant, so I chose it.

Installation:

npm install exceljs
Copy the code

You’ll also need another library to download locally: file-Saver

npm install file-saver
Copy the code

2. Basic concepts

To understand the basic concepts, please refer to the official document github.com/exceljs/exc…

workbook

Workbook: an entire Excel spreadsheet.

Create a workbook using const workbook = new exceljs.workbook (). You can also set the workbook properties:

workbook.creator = 'Me';
workbook.lastModifiedBy = 'Her';
workbook.created = new Date(1985, 8, 30);
workbook.modified = new Date();
workbook.lastPrinted = new Date(2016, 9, 27);
Copy the code

worksheet

A worksheet is a sheet in an Excel spreadsheet.

Create a worksheet with const sheet = workbook.addWorksheet(‘My sheet ‘). Multiple worksheets can be added to each workbook.

Use the second argument to the addWorksheet function to specify options for the worksheet.

// Create worksheet with red label color const sheet = workbook.addWorksheet('My sheet ', {properties:{tabColor:{arGB :'FFC0000'}}}); Const sheet = workbook.addWorksheet('My sheet ', {views: [{showGridLines: false}]}); Const sheet = workbook.addWorksheet('My sheet ', {views:[{xSplit: 1, ySplit:1}]}); Const worksheet = workbook.addWorksheet('My Sheet', {pageSetup:{paperSize: 9, orientation:'landscape'} }); // create a worksheet with headerFooter const sheet = workbook.addWorksheet('My sheet ', {headerFooter:{firstHeader: "Hello Exceljs", firstFooter: "Hello World"} }); Const sheet = workbook.addWorksheet('My sheet ', {views:[{state: 'frozen', xSplit: 1, ySplit:1}]});Copy the code

columns

Worksheet. columns Allows you to set the header of the table.

// Add column headings and define column keys and widths // Note: these column structures are just a convenience for building workbooks, except for column widths, they will not be retained completely. worksheet.columns = [ { header: 'Id', key: 'id', width: 10 }, { header: 'Name', key: 'name', width: 32 }, { header: 'D.O.B.', key: 'DOB', width: 10, outlineLevel: 1 } ]; // Access a single column by key, letter, and column number based on 1 const idCol = worksheet.getColumn('id'); const nameCol = worksheet.getColumn('B'); const dobCol = worksheet.getColumn(3); // Set the column properties // Note: C1 cell value dobcol. header = 'Date of Birth'; // Note: this will override the C1:C2 cell value dobcol. header = ['Date of Birth', 'A.K.A. D.O.B.']; // From now on, this column will be indexed with "dob" instead of "dob" dobCol.key = 'dob'; dobCol.width = 15; // Hide column dobcol. hidden = true if needed;Copy the code

You can also perform various operations on columns.

Dobcol. eachCell(function(cell, rowNumber) {//... }); EachCell ({includeEmpty: true}, function(cell, rowNumber) {//... }); Worksheet.getcolumn (6). Values = [1,2,3,4,5]; / / add a sparse columns value worksheet. GetColumn (7). The values = [,, 2, 3, 5, and 7,,,, 11]. // clipping one or more columns (the right column moves to the left) // if the column attribute is defined, it will be sliced or moved accordingly // known problem: if concatenation causes any merged cells to move, the result may be unpredictable worksheet.splicecolumns (3,2); // Delete one column and insert two columns. // Note: columns 4 and above will be moved 1 column to the right. // Also: If there are more rows in the worksheet than the value in the column insert item, the row will still be inserted as if the value existed. Const newCol3Values = [1, 2, 3, 4, 5]; const newCol4Values = ['one', 'two', 'three', 'four', 'five']; worksheet.spliceColumns(3, 1, newCol3Values, newCol4Values);Copy the code

row

Rows, which can add one or more rows of data simultaneously, are the most frequently used attribute.

Columns worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)}); Worksheet. AddRow ({id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)}); Worksheet.addrow ([3, 'Sam', new Date()]); // Add multiple rows of data to worksheet.addrows (list); Worksheet. eachRow(function(row, rowNumber) { console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values)); }); Worksheet. eachRow({includeEmpty: true }, function(row, rowNumber) { console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values)); }); // Row. EachCell (function(cell, colNumber) {console.log(' cell '+ colNumber +' = '+ cell.value); }); // Iterate over all cells in a row (including empty cells) row.eachCell({includeEmpty: true }, function(cell, colNumber) { console.log('Cell ' + colNumber + ' = ' + cell.value); });Copy the code

3. Simple table export

All examples in this article use React + AntD.

Let’s use AntD’s Table to create a simple Table page with different column widths:

Click Export Excel and open it to get the following results:

As you can see, the exported Excel column width ratio is the same as the online table.

Stick source code:

// Simple demo import React, {useEffect, useState} from 'React' import {Button, Card, Table} from "antd"; import {ColumnsType} from "antd/lib/table/interface"; import * as ExcelJs from 'exceljs'; import {generateHeaders, saveWorkbook} from ".. /utils"; interface SimpleDemoProps { } interface StudentInfo { id: number; name: string; age: number; gender: string; } const SimpleDemo: React.FC<SimpleDemoProps> = () => { const [list, setList] = useState<StudentInfo[]>([]); useEffect(() => { generateData(); }, []) function generateData() { let arr: StudentInfo[] = []; for (let i = 0; i < 10; I++) {arr. Push ({id: I, name: ` xiao Ming ${I} ` number, age, and gender: I % 2 = = = 0? })} setList(arr); } const columns: ColumnsType<any> = [ { width: 50, dataIndex: 'id', key: 'id', title: 'ID', }, { width: 100, dataIndex: 'name', key: 'name', title: 'name',}, {width: 50, dataIndex: 'age', key: 'age', title: 'age',}, {width: 80, dataIndex: 'gender', key: 'gender', title: 'gender',},]; Function onExportBasicExcel() {// Create a workbook const workbook = new exceljs.workbook (); // Add sheet const worksheet = workbook.addWorksheet('demo sheet'); / / sets the default sheet line height worksheet. Properties. DefaultRowHeight = 20; // Set the column worksheet.columns = generateHeaders(columns); // Add row worksheet.addrows (list); // Export excel saveWorkbook(workbook, 'simple-demo.xlsx'); // Export excel saveWorkbook(workbook, 'simple-demo.xlsx'); } return (<Card> <h3> </h3> <Button type={'primary'} style={{marginBottom: Table tables ={columns} dataSource={list} /> </Card>); } export default SimpleDemoCopy the code

The actual exported code is only a few lines, focusing on the onExportBasicExcel method:

  1. Start by creating the workbook and sheet pages, which are two lines of fixed code. If multiple sheets are required, create multiple sheets. All subsequent operations on the table are operations on the Worksheet.
  2. Sets the default row height for the table. This step is not necessary, but the setup is more aesthetically pleasing. Otherwise, the height of a line with content is inconsistent with that of a line without content.
  3. Sets the column data (table header) and the data for each row.
  4. Export excel.

Parse the columns and dataSource of AntD Table

Since we are using AntD Table, we have already constructed the Table header and specific Table data, so we only need to parse.

GenerateHeaders () is a self-encapsulated method that converts the columns of a Table to ExcelJS header format:

import {ITableHeader} from "src/types"; import {ColumnsType} from "antd/lib/table/interface"; const DEFAULT_COLUMN_WIDTH = 20; Exceljs column export function generateHeaders(columns: any[]) {return columns? .map(col => {const obj: ITableHeader = {// Display name header: col.title, // use key to match data. Col. DataIndex, / / column wide width: col. Width / 5 | | DEFAULT_COLUMN_WIDTH,}; return obj; })}Copy the code

In ExcelJS, the header field represents the display header content, the key is the key used to match the data, and the width is the column width. There are corresponding fields in the column of the Table, which can be retrieved and assigned. Note that when you set the column width, the units of the online table and Excel may differ, and you need to divide by a coefficient so that the column width is not too wide. As for the specific division of how much, you can constantly experiment to get the best value, I tried to divide by 5 better effect.

The worksheet.addrows () method is used to add multiple rows of data to the Table. Since we have set the Table header above, the application knows which fields each column should match, so we pass in the Table dataSource directly.

You can also add data line by line via worksheet.addrow ().

Download the excel

SaveWorkbook () is also a self-wrapping method that accepts the workbook and filename to download Excel locally.

The download is done using the file-Saver library.

import {saveAs} from "file-saver"; import {Workbook} from "exceljs"; export function saveWorkbook(workbook: Workbook, fileName: String) {/ / export file workbook. XLSX. WriteBuffer (). Then ((data = > {const blob = new blob ([data] and {type: "}); saveAs(blob, fileName); }}))Copy the code

At this point, you can implement AntD Table export in just a few lines of code.

Fourth, modify the style

Cells, rows, and columns support a rich set of styles and formats that affect how cells are displayed.

Set the style by assigning the following properties:

  • numFmt
  • font
  • alignment
  • border
  • fill

Add background color

Let’s start by adding a background to the header. Since the header is the first row, we can get the header line by getRow(1) :

Let headerRow = worksheet.getrow (1); headerRow.fill = { type: 'pattern', pattern: 'solid', fgColor: {argb: 'dff8ff'}, }Copy the code

You can directly use row.fill to set the background color for the entire row, so that cells in the row that have no content will also have color, as shown:

There’s really no data from column E, but what if you just want to set a background for a non-empty cell?

Unfortunately, the row exposed method does not support this directly, but it does save the country by traversing all non-empty cells in the row and setting a background for each cell.

Headerrow. eachCell((cell, colNum) => {cell.fill = {type: 'pattern', pattern: 'solid', fgColor: {argb: 'dff8ff'}, } })Copy the code

Using cell control is more accurate, you can see that empty cells have no background color.

Modify font styles

You can set the font, size, color and other properties of a text. The supported properties are listed as follows:

Font properties describe The sample value
name Font name. ‘Arial’, ‘Calibri’, etc.
family Family of alternative fonts. An integer value. 1 – Serif, 2 – Sans Serif, 3 – Mono, Others – unknown
scheme Font scheme. ‘minor’, ‘major’, ‘none’
charset Font character set. An integer value. 1, 2, etc.
size Font size. An integer value. 9, 10, 12, 16, etc.
color Color description, an object containing ARGB values. { argb: ‘FFFF0000’}
bold The fontThe thickness true, false
italic The fonttilt true, false
underline Font underline style true, false, ‘none’, ‘single’, ‘double’, ‘singleAccounting’, ‘doubleAccounting’
strike Font deletion line true, false
outline Outline fonts true, false
vertAlign The vertical alignment ‘superscript’, ‘subscript’

As with setting the background color, you can set it by row or cell. The example will be set up via cell.

Modify the header font to Microsoft Yahei, size 12, color red, bold italic.

Headerrow. eachCell((cell, colNum) => {// Set background color cell.fill = {type: 'pattern', pattern: 'solid', fgColor: {arGB: 'dff8ff'},} // Set font cell.font = {bold: true, italic: true, size: 12, name: 'Microsoft Yahi ', color: {argb: 'ff0000'}, }; })Copy the code

Setting alignment

Valid alignment attributes:

horizontal vertical wrapText shrinkToFit indent readingOrder textRotation
left top true true integer rtl 0 to 90
center middle false false ltr -1 to -90
right bottom vertical
fill distributed
justify justify
centerContinuous
distributed

The default alignment for a table is down, and it is usually vertically centered, text left, and numbers right. The horizontal alignment is left aligned and the vertical alignment is center aligned for convenience.

// Add row let rows = worksheet.addrows (list); rows? ForEach (row => {// set font row.font = {size: 11, name: 'Microsoft yahei ',}; Row. Alignment = {vertical: 'middle', horizontal: 'left', wrapText: false,}; })Copy the code

AddRows () returns an array of rows to be added, and then loops through each row to set the font and alignment, completing the style customization for excel as a whole.

Of course, you can also set each cell to the same effect.

The same goes for setting borders, which I won’t cover here.

Complete export excel code with styles:

/ / export function onExportBasicExcelWithStyle () {/ / create workbook const workbook = new ExcelJs. The workbook (); // Add sheet const worksheet = workbook.addWorksheet('demo sheet'); / / sets the default sheet line height worksheet. Properties. DefaultRowHeight = 20; // Set the column worksheet.columns = generateHeaders(columns); // Add a background color to the header. GetRow (1) let headerRow = worksheet.getrow (1); Fill = {// type: 'pattern', // pattern: 'solid', // fgColor: {argb: Headerrow. eachCell((cell, colNum) => {// Set background color cell.fill = {type: 'pattern', pattern: 'solid', fgColor: {arGB: 'dff8ff'},} // Set cell. Font = {bold: true, italic: true, size: 12, name: 'Microsoft yahei ', color: {argb: 'ff0000'},}; Alignment = {vertical: 'middle', horizontal: 'left', wrapText: false,}; }) // addRows let rows = worksheet.addrows (list); // Set the style for each row rows? ForEach (row => {// set font row.font = {size: 11, name: 'Microsoft yahei ',}; Row. Alignment = {vertical: 'middle', horizontal: 'left', wrapText: false,}; }) // Export excel saveWorkbook(workbook, 'simple-demo.xlsx'); }Copy the code

Fifth, row merge & column merge

Take a look at the online form:

Export excel:

This table involves multiple levels of headers, row merges, and column merges.

It involves the following key and difficult points:

  • Table header parsing. Multilevel Table header with children, to parse the Table columns as the desired data structure.
  • Columns. A piece of content occupies more than one cell, and columns of multiple columns in a row are merged, such as grades and teacher comments columns.
  • Merger. The table header takes up two rows. All columns except the result should merge the two rows into one.
  • Rows and columns are merged simultaneously. If a cell has been merged once, it cannot be merged again, so if you have a cell with rows and columns that need to be merged, you must merge both rows and columns at once, not in two steps. Such as teacher comments column.
  • Table header and data style adjustment.

Post the complete code first

import React, {useEffect, useState} from 'react' import {Button, Card, Space, Table} from "antd"; import {ColumnsType} from "antd/lib/table/interface"; import {ITableHeader, StudentInfo} from ".. /types"; import * as ExcelJs from "exceljs"; import { addHeaderStyle, DEFAULT_COLUMN_WIDTH, DEFAULT_ROW_HEIGHT, generateHeaders, getColumnNumber, mergeColumnCell, mergeRowCell, saveWorkbook } from ".. /utils"; import {Worksheet} from "exceljs"; interface MultiHeaderProps { } const columns: ColumnsType<any> = [ { width: 50, dataIndex: 'id', key: 'id', title: 'ID',}, {width: 100, dataIndex: 'name', key: 'name', title: 'name',}, {width: 50, dataIndex: 'age', key: 'name',}, {width: 50, dataIndex: 'age', key: 'age', title: 'age',}, {width: 80, dataIndex: 'gender', key: 'gender',}, {dataIndex: 'score', key: 'score', title: 'score', children: [{width: 80, dataIndex: 'English ', key:' English ', title: 'English ',}, {width: 100, children: 80, dataIndex: 'math', key: 'math', title: 'math',}, {width: 80, dataIndex: 'physics', key: 'physics', title: 'physical'}]}, {width: 250, dataIndex: 'comment' key: 'comment' title: 'the teacher comments,},]; const MultiHeader: React.FC<MultiHeaderProps> = () => { const [list, setList] = useState<StudentInfo[]>([]); useEffect(() => { generateData(); }, []) function generateData() { let arr: StudentInfo[] = []; for (let i = 0; i < 5; I++) {arr. Push ({id: I, name: ` xiao Ming ${I} `, age: 8 + I, gender: I % 2 = = = 0? 'male' : 'female ', English: 80 + I, Math: 60 + I, physics: 70 + I, comment: 'Xiao Ming ${I} no. Student performance is very good, enthusiastic to help others, excellent grades, is the successor of socialism'})} setList(ARR); } function onExportMultiHeaderExcel() {// create a workbook const workbook = new exceljs.workbook (); // Add sheet const worksheet = workbook.addWorksheet('demo sheet'); / / sets the default sheet line height worksheet. Properties. DefaultRowHeight = 20; AntD Table columns const headers = generateHeaders(columns); Console. log({headers}) // first line header const names1: string[] = []; Const names2: string[] = []; // Const names2: string[] = []; Const headerKeys: string[] = []; Headers. ForEach (item => {if (item.children) { Header name requires two lines item.children. ForEach (child => {names1.push(item.header); names2.push(child.header); headerKeys.push(child.key); }); } else { const columnNumber = getColumnNumber(item.width); for (let i = 0; i < columnNumber; i++) { names1.push(item.header); names2.push(item.header); headerKeys.push(item.key); }}}); handleHeader(worksheet, headers, names1, names2); AddData2Table (worksheet, headerKeys, headers); Worksheet.columns = worksheet.columns. Map (col => ({... col, width: DEFAULT_COLUMN_WIDTH })); // Export excel saveWorkbook(workbook, 'simple-demo.xlsx'); // Export excel saveWorkbook(workbook, 'simple-demo.xlsx'); } function handleHeader( worksheet: Worksheet, headers: ITableHeader[], names1: string[], names2: String [],) {const isMultiHeader = headers? .some(item => item.children); If (isMultiHeader) {// Add header const rowHeader1 = worksheet.addrow (names1); const rowHeader2 = worksheet.addRow(names2); RowHeader1, {color: 'dff8ff'}); addHeaderStyle(rowHeader2, {color: 'dff8ff'}); mergeColumnCell(headers, rowHeader1, rowHeader2, names1, names2, worksheet); return; } // add header data const rowHeader = worksheet.addrow (names1); MergeRowCell (headers, rowHeader, worksheet); AddHeaderStyle (rowHeader, {color: 'dff8ff'}); } function addData2Table(worksheet: Worksheet, headerKeys: string[], headers: ITableHeader[]) { list? .forEach((item: any) => { const rowData = headerKeys? .map(key => item[key]); const row = worksheet.addRow(rowData); mergeRowCell(headers, row, worksheet); row.height = DEFAULT_ROW_HEIGHT; // Set the line style, wrapText: auto-wrap row.alignment = {vertical: 'middle', wrapText: false, shrinkToFit: false}; Font = {size: 11, name: 'Microsoft yahei'}; })} return (<Card> <h3> <Space style={{marginBottom: 10}}> <Button type={'primary'} onClick={onExportMultiHeaderExcel} </Button> </Space> <Table key={'id'} columns={columns} dataSource={list} /> </Card> ); } export default MultiHeaderCopy the code

GenerateHeaders () = generateHeaders() = generateHeaders() = generateHeaders()

Header parsing

We modify the generateHeaders() method from the previous section to add the logic with children. We also construct children when we have multilevel headers.

Exceljs column export function generateHeaders(columns: any[]) {return columns? .map(col => {const obj: ITableHeader = {// Display name header: col.title, // use key to match data. Col. DataIndex, / / column wide width: col. Width / 5 | | DEFAULT_COLUMN_WIDTH,}; if (col.children) { obj.children = col.children? .map((item: any) => ({ key: item.dataIndex, header: item.title, width: item.width, parentKey: col.dataIndex, })); } return obj; })}Copy the code

The constructed data structure is as follows:

Columns = generateHeaders(columns) {worksheet.columns = generateHeaders(columns) {worksheet.columns = generateHeaders(columns)} Write the header itself as a row of data. Each of the following lines of data also calculates and matches what content should be displayed where.

Let’s start with this code:

AntD Table columns const headers = generateHeaders(columns); // const names1: string[] = []; Const names2: string[] = []; // Const names2: string[] = []; Const headerKeys: string[] = []; Headers. ForEach (item => {if (item.children) { Header name requires two lines item.children. ForEach (child => {names1.push(item.header); names2.push(child.header); headerKeys.push(child.key); }); } else { const columnNumber = getColumnNumber(item.width); for (let i = 0; i < columnNumber; i++) { names1.push(item.header); names2.push(item.header); headerKeys.push(item.key); }}});Copy the code

This example has two levels of table headers, so you need two rows to set each level of table headers, named names1 and names2, which hold the displayed names, such as ID, name, age, and so on. You also need a headerKeys to store the matching keys for each column, such as ID, name, and age JSON keys.

Note that headerKeys is the second row header, because the second row is what is actually displayed.

With names1, names2, and headerKeys constructed, we can start generating the actual table headers:

function handleHeader( worksheet: Worksheet, headers: ITableHeader[], names1: string[], names2: String [],) {const isMultiHeader = headers? .some(item => item.children); If (isMultiHeader) {// Add header const rowHeader1 = worksheet.addrow (names1); const rowHeader2 = worksheet.addRow(names2); RowHeader1, {color: 'dff8ff'}); addHeaderStyle(rowHeader2, {color: 'dff8ff'}); mergeColumnCell(headers, rowHeader1, rowHeader2, names1, names2, worksheet); return; } // add header data const rowHeader = worksheet.addrow (names1); MergeRowCell (headers, rowHeader, worksheet); AddHeaderStyle (rowHeader, {color: 'dff8ff'}); }Copy the code

First determine whether there is a multi-level table header, single-row table header and multi-row table header execution logic is different.

Add the header to a row with worksheet.addrow (), and add the header twice for multiple rows. Then add a style to the header via addHeaderStyle(), which is its own wrapped method, in utils. And last but not least, merge cells,

Merge multiple columns in the same row

The method of merging cells is worksheet.mergecells (), which can be merged in a number of ways:

MergeCells ('A4:B5'); // mergeCells('A4:B5'); / /... Worksheet.getcell ('B5'). Value = 'Hello, World! '; expect(worksheet.getCell('B5').value).toBe(worksheet.getCell('A4').value); expect(worksheet.getCell('B5').master).toBe(worksheet.getCell('A4')); / /... Expect (worksheet.getcell ('B5').style).tobe (worksheet.getcell ('A4').style); worksheet.getCell('B5').style.font = myFonts.arial; expect(worksheet.getCell('A4').style.font).toBe(myFonts.arial); // unMergeCells will break the link style worksheet.unmergecells ('A4'); expect(worksheet.getCell('B5').style).not.toBe(worksheet.getCell('A4').style); expect(worksheet.getCell('B5').style.font).not.toBe(myFonts.arial); MergeCells ('K10', 'M12'); MergeCells (10,11,12,13); // press start row, start column, end column merge (equivalent to K10:M12).Copy the code

Let’s look at the algorithm that merges multiple columns in the same row. The key is to set an index, starting at 1, to represent the first column. Then loop the headers, if the current header has children, then each child has a column, and increment the index by 1. If there are no children, calculating the width of this data will take up several cells, i.e. several columns. This column number is the number of columns to be merged. After merging, the index value will be increased by 1.

Export function mergeRowCell(headers: ITableHeader[], row: row, worksheet: Worksheet) {// let colIndex = 1; headers.forEach(header => { const { width, children } = header; if (children) { children.forEach(child => { colIndex += 1; }); } else {const colNum = getColumnNumber(width); If (colNum > 1) {worksheet.mergecells (Number(row.number), colIndex, Number(row.number), colIndex + colNum - 1); } colIndex += colNum; }}); } export function getColumnNumber(width: number) {return math. round(width/DEFAULT_COLUMN_WIDTH); }Copy the code

To combine cells:

worksheet.mergeCells(Number(row.number), colIndex, Number(row.number), colIndex + colNum - 1);

The four parameters are the start row, start column, end row, and end column of the merge.

Run the row.number command to obtain the number of rows in the current row. Because multiple columns of the same row are merged, the start column is the index colIndex, and the end column is colIndex + colNum -1.

Merge rows and columns simultaneously

If you have multiple table headers, you need to handle both row and column merges, using the encapsulated mergeColumnCell method.

The basic idea is to judge the type of merger first, there are three cases:

  • Only row merge
  • Only column merge
  • Row and column merge at the same time

It then calculates the starting rows and columns, and the ending rows and columns.

Export function mergeColumnCell(headers: ITableHeader[], rowHeader1: Row, rowHeader2: Row, nameRow1: String [], nameRow2: string[], worksheet: worksheet,) {// Let pointer = -1; Namerow1. forEach((name, index) => {// If (index <= pointer) return; Const shouldVerticalMerge = name === nameRow2[index]; Const shouldHorizontalMerge = index! == nameRow1.lastIndexOf(name); pointer = nameRow1.lastIndexOf(name); If (shouldVerticalMerge && shouldHorizontalMerge) {// Merge worksheet.mergecells (Number(rowheader1.number), index + 1, Number(rowHeader2.number), nameRow1.lastIndexOf(name) + 1, ); } else if (shouldVerticalMerge && ! ShouldHorizontalMerge worksheet.mergecells (Number(rowheader1.number), index + 1, Number(rowHeader2.number), index + 1); } else if (! ShouldVerticalMerge &&shouldHorizontalMerge) {// Merge worksheet.mergecells (Number(rowheader1.number), index + 1, Number(rowHeader1.number), nameRow1.lastIndexOf(name) + 1, ); // eslint-disable-next-line no-param-reassign const cell = rowHeader1.getCell(index + 1); cell.alignment = { vertical: 'middle', horizontal: 'center' }; }}); }Copy the code

Add data row

When calculating the table header, we have obtained the list of key values for each column, headerKeys, through which we can retrieve the specific data corresponding to each column.

function addData2Table(worksheet: Worksheet, headerKeys: string[], headers: ITableHeader[]) { list? .forEach((item: any) => { const rowData = headerKeys? .map(key => item[key]); const row = worksheet.addRow(rowData); mergeRowCell(headers, row, worksheet); row.height = DEFAULT_ROW_HEIGHT; // Set the line style, wrapText: auto-wrap row.alignment = {vertical: 'middle', wrapText: false, shrinkToFit: false}; Font = {size: 11, name: 'Microsoft yahei'}; })}Copy the code

You loop through the list of data, then loop through headerKeys to retrieve the corresponding value, and then add the row to the table via worksheet.addrow. The mergeRowCell() method can be reused because it is possible for a single field to occupy multiple columns, so you also need to merge cells. Finally, style each row to get the final data.

Multiple tables in a sheet

AddRow worksheet.addRow worksheet.column worksheet.column worksheet.addRow worksheet.column

You can use the same method when working with multiple tables. Since each row of data is written by itself, it doesn’t matter how many tables there are, we only care about each row of data.

At the same time we did the row and column merge algorithm, can achieve each table each column can be customized width.

You can combine the above two examples and export them to a sheet to fulfill the requirement of placing multiple tables in a sheet.

conclusion

In addition to exporting XLSX, ExcelJS also supports exporting to CSV format. In addition, there are also set header footer, operation view, add formula, use rich text and other functions, very powerful.

The official document is also very detailed, do not understand the place directly read the document.

Source code address: github.com/cachecats/e…