This article mainly introduces how to use Exceljs, file-Saver, and JsZip to download zip packages containing multi-layer folders, multiple Excel files, and multiple sheets for each Excel. This article describes in detail how to achieve the analysis of Antd Table, assemble data and adjust the style of the Table, interested in the first look. This article will pick up where we left off, focusing on higher abstractions of methods, and downloading zip packages for multilevel folders. Source code address: github.com/cachecats/e…
Implementation effect
The final download isZip zip, contains multiple folders, each folder can be nested in unlimited subfolders, Excel files can be freely placed in the root directory, or subfolders.
The realization effect is shown as follows:
Method of use
It is also very simple to use, after a high encapsulation, just pass in the method parameters as the rules:
downloadFiles2ZipWithFolder({
zipName: 'Zip package'.folders: [{folderName: 'Folder 1'.files: [{filename: 'test'.sheets: [{
sheetName: 'test'.columns: columns,
dataSource: list
}]
},
{
filename: 'test2'.sheets: [{
sheetName: 'test'.columns: columns,
dataSource: list
}]
},
]
},
{
folderName: 'Folder 2'.files: [{filename: 'test'.sheets: [{
sheetName: 'test'.columns: columns,
dataSource: list
}]
},
{
filename: 'test2'.sheets: [{
sheetName: 'test'.columns: columns,
dataSource: list
}]
},
]
},
{
folderName: 'Folder 2/ folder 2-1'.files: [{filename: 'test'.sheets: [{
sheetName: 'test'.columns: columns,
dataSource: list
}]
},
{
filename: 'test2'.sheets: [{
sheetName: 'test'.columns: columns,
dataSource: list
}]
},
]
},
{
folderName: 'Folder 2/ folder 2-1/ folder 2-1-1'.files: [{filename: 'test'.sheets: [{
sheetName: 'test'.columns: columns,
dataSource: list
}]
},
{
filename: 'test2'.sheets: [{
sheetName: 'test'.columns: columns,
dataSource: list
}]
},
]
},
{
folderName: ' '.files: [{filename: 'test'.sheets: [{
sheetName: 'test'.columns: columns,
dataSource: list
},
{
sheetName: 'test2'.columns: columns,
dataSource: list
}
]
},
{
filename: 'test2'.sheets: [{
sheetName: 'test'.columns: columns,
dataSource: list
}]
},
]
}
]
})
Copy the codeThree methods are encapsulated here to meet the export requirements in different scenarios:
downloadExcel: Export ordinary single file Excel, preset style, can contain multiple sheets.downloadFiles2Zip: Export multiple Excel files to a zip package without nested folders.downloadFiles2ZipWithFolder: Export a zip package containing multiple levels of subfolders, each level containing multiple Excel files.
First, encapsulate the ordinary download and export Excel method
Let’s encapsulate a common, pre-defined, out-of-the-box export method that users can simply call without worrying about the details:
function onExportExcel() {
downloadExcel({
filename: 'test'.sheets: [{
sheetName: 'test'.columns: columns,
dataSource: list
}]
})
}
Copy the codeAs above, the downloadExcel method is called directly, which passes in an object as an argument with the filename and sheets properties.
- Filename: indicates the filename. Don’t take
.xlsxSuffix, the suffix name is automatically added. - Sheets: sheet array. Passing in several sheets objects creates several sheets.
The definition of the Sheet object:
export interface ISheet {
// Sheet's name
sheetName: string;
// Column of the table in this sheet is of the same type as column of ANTD
columns: ColumnType<any> [];// Table data
dataSource: any[];
}
Copy the codeThe core code
DownloadExcel method
export interface IDownloadExcel {
filename: string;
sheets: ISheet[];
}
export interface ISheet {
// Sheet's name
sheetName: string;
// Column of the table in this sheet is of the same type as column of ANTD
columns: ColumnType<any> [];// Table data
dataSource: any[];
}
/** * Download and export a simple table *@param params* /
export function downloadExcel(params: IDownloadExcel) {
// Create a workbook
const workbook = newExcelJs.Workbook(); params? .sheets? .forEach((sheet) = > handleEachSheet(workbook, sheet));
saveWorkbook(workbook, `${params.filename}.xlsx`);
}
function handleEachSheet(workbook: Workbook, sheet: ISheet) {
/ / add a sheet
const worksheet = workbook.addWorksheet(sheet.sheetName);
// Set the default line height for sheet. Set default row height to autosplit cell conflict
// worksheet.properties.defaultRowHeight = 20;
/ / set column
worksheet.columns = generateHeaders(sheet.columns);
handleHeader(worksheet);
handleData(worksheet, sheet);
}
export function saveWorkbook(workbook: Workbook, fileName: string) {
// Export the file
workbook.xlsx.writeBuffer().then((data: any) = > {
const blob = new Blob([data], {type: ' '});
saveAs(blob, fileName);
});
}
Copy the codeThe generateHeaders method sets the columns of the table. The handleHeader method handles the header, setting its height, background color, font, and so on. The handleData method processes each row of specific data. The implementation of these three methods has been introduced in the previous article, if you need to know more please view the source: github.com/cachecats/e…
The resulting Excel looks like this, with the column width dynamically calculated based on the passed width and the cell height automatically spread out based on the content.
2. Export a zip package containing multiple Excel files
If you don’t need a multilevel directory and just want to pack multiple Excel files into a compressed package, you can use itdownloadFiles2ZipThis method results in the following directory structure:
The parameter structure is as follows. Multiple Excel files can be exported, and each Excel file can contain multiple sheets.
export interface IDownloadFiles2Zip {
// File name of the compressed package
zipName: string;
files: IDownloadExcel[];
}
export interface IDownloadExcel {
filename: string;
sheets: ISheet[];
}
export interface ISheet {
// Sheet's name
sheetName: string;
// Column of the table in this sheet is of the same type as column of ANTD
columns: ColumnType<any> [];// Table data
dataSource: any[];
}
Copy the codeUse the sample
function onExportZip() {
downloadFiles2Zip({
zipName: 'Zip package'.files: [{filename: 'test'.sheets: [{sheetName: 'test'.columns: columns,
dataSource: list
},
{
sheetName: 'test2'.columns: columns,
dataSource: list
}
]
},
{
filename: 'test2'.sheets: [{
sheetName: 'test'.columns: columns,
dataSource: list
}]
},
{
filename: 'test3'.sheets: [{
sheetName: 'test'.columns: columns,
dataSource: list
}]
}
]
})
}
Copy the codeThe core code
Each fille object is handled through the handleEachFile() method, and each file is essentially an Excel file, or workbook. Create a workbook for each Excel and write the data, then write it to a compressed file through the JsZip library, and finally export the compressed file using the saveAs method provided by the File-Saver library. Note in lines 12 and 13 that the handleEachFile() method returns a Promise and needs to wait until all the asynchronous methods have executed before executing the generate ZIP method below, otherwise the file may be missed.
import {saveAs} from 'file-saver';
import * as ExcelJs from 'exceljs';
import {Workbook, Worksheet, Row} from 'exceljs';
import JsZip from 'jszip'
/** * Export multiple files as zip packages */
export async function downloadFiles2Zip(params: IDownloadFiles2Zip) {
const zip = new JsZip();
// After each file has been written, it is regenerated into a zip file
constpromises = params? .files? .map(async param => await handleEachFile(param, zip, ' '))
await Promise.all(promises);
zip.generateAsync({type: "blob"}).then(blob= > {
saveAs(blob, `${params.zipName}.zip`)})}async function handleEachFile(param: IDownloadExcel, zip: JsZip, folderName: string) {
// Create a workbook
const workbook = newExcelJs.Workbook(); param? .sheets? .forEach((sheet) = > handleEachSheet(workbook, sheet));
/ / generates a blob
const data = await workbook.xlsx.writeBuffer();
const blob = new Blob([data], {type: ' '});
if(folderName) { zip.folder(folderName)? .file(`${param.filename}.xlsx`, blob)
} else {
// Write a file to zip
zip.file(`${param.filename}.xlsx`, blob); }}function handleEachSheet(workbook: Workbook, sheet: ISheet) {
/ / add a sheet
const worksheet = workbook.addWorksheet(sheet.sheetName);
// Set the default line height for sheet. Set default row height to autosplit cell conflict
// worksheet.properties.defaultRowHeight = 20;
/ / set column
worksheet.columns = generateHeaders(sheet.columns);
handleHeader(worksheet);
handleDataWithRender(worksheet, sheet);
}
Copy the codeRender Cell processing for rendering
Another point to note in data processing is that some cells are rendered by the render function, which may perform a series of complex calculations. Therefore, if the column contains render, dataIndex cannot be directly evaluated. It is correct to get the value of the render function after execution. For example, the columns of a Table are as follows:
const columns: ColumnsType<any= > [{width: 50.dataIndex: 'id'.key: 'id'.title: 'ID'.render: (text, row) = > <div><p>{row.id + 20}</p></div>}, {width: 100.dataIndex: 'name'.key: 'name'.title: 'name'}, {width: 50.dataIndex: 'age'.key: 'age'.title: 'age'}, {width: 80.dataIndex: 'gender'.key: 'gender'.title: 'gender',},];Copy the codeThe first column passes in the render functionrender: (text, row) => <div><p>{row.id + 20}</p></div>After the calculation, the ID column should display the original ID + 20.
The original id of the constructed data is 0-4, and the page should display 20-24, as shown below:
It is only right that the exported Excel should look exactly as it appears on the page.
Click the “Export ZIP” button, unzip and open one of the downloaded Excel files. Verify that the content displayed is exactly the same as the online table.
So how does it work?
Basically seehandleDataWithRender()Methods:
/**
* 如果 column 有 render 函数，则以 render 渲染的结果显示
* @param worksheet
* @param sheet* /
function handleDataWithRender(worksheet: Worksheet, sheet: ISheet) {
const {dataSource, columns} = sheet;
constrowsData = dataSource? .map(data= > {
returncolumns? .map(column= > {
// @ts-ignore
constrenderResult = column? .render? .(data[column.dataIndex], data);if (renderResult) {
// If it is not object, there is no package label
if (typeofrenderResult ! = ="object") {
return renderResult;
}
// If it is object, the label is wrapped, and the value is fetched step by step
return getValueFromRender(renderResult);
}
// @ts-ignore
returndata[column.dataIndex]; })})/ / add line
const rows = worksheet.addRows(rowsData);
// Set the style for each line
addStyleToData(rows);
}
// recursively fetch the value in render
// @ts-ignore
function getValueFromRender(renderResult: any) {
if(renderResult? .type) {letchildren = renderResult? .props? .children;if(children? .type) {return getValueFromRender(children);
} else {
returnchildren; }}return ' '
}
Copy the codeworksheet.addRows()You can add data objects, or you can add a two-dimensional array consisting of each column of each row. Since we control what each cell displays ourselves, we take the second approach, passing in a two-dimensional array to construct the row.
The structure is shown in the figure below:
cycledataSourceandcolumnsRender function (); render function (); render function ();const renderResult = column? .render? .(data[column.dataIndex], data);
Note that render needs to pass in two parameters, one is text, and the other is the data object of this row. We can both determine the value of the parameter, so we pass it in directly.
And then determinerenderResultIf it is object, it is a ReactNode wrapped in HTML tags and needs to recursively fetch the final rendered value. If the type is not object, it is a Boolean or string type that is not wrapped in a label and can be displayed directly.
Since we use recursion to fetch the last rendered value, no matter how many tags are nested, the value will be correctly fetched.
3. Export the ZIP package containing multiple sub-folders and multiple Excel files
If files and folders are deeply nested, you can use this commanddownloadFiles2ZipWithFolder()Methods.
The file structure is as follows:
The core code
export interface IDownloadFiles2ZipWithFolder {
zipName: string;
folders: IFolder[];
}
export interface IFolder {
folderName: string;
files: IDownloadExcel[];
}
export interface IDownloadExcel {
filename: string;
sheets: ISheet[];
}
export interface ISheet {
// Sheet's name
sheetName: string;
// Column of the table in this sheet is of the same type as column of ANTD
columns: ColumnType<any> [];// Table data
dataSource: any[];
}
/** * Export compressed packages that support multi-level folders *@param params* /
export async function downloadFiles2ZipWithFolder(params: IDownloadFiles2ZipWithFolder) {
const zip = new JsZip();
constoutPromises = params? .folders? .map(async folder => await handleFolder(zip, folder))
await Promise.all(outPromises);
zip.generateAsync({type: "blob"}).then(blob= > {
saveAs(blob, `${params.zipName}.zip`)})}async function handleFolder(zip: JsZip, folder: IFolder) {
console.log({folder})
let folderPromises: Promise<any= > [] [];constpromises = folder? .files? .map(async param => await handleEachFile(param, zip, folder.folderName));
await Promise.all([...promises, ...folderPromises]);
}
Copy the codeCompared with the previous method downloadFiles2Zip, the data structure of the parameter has more layers of folders, and the other logic is basically unchanged. So downloadFiles2ZipWithFolder method can realize downloadFiles2Zip all functions.
Use the sample
As shown in the example at the beginning of this article, to make it easier to see the structure, delete the files value of each object and get the following structure after simplification:
downloadFiles2ZipWithFolder({
zipName: 'Zip package'.folders: [{folderName: 'Folder 1'.files: []}, {folderName: 'Folder 2'.files: []}, {folderName: 'Folder 2/ folder 2-1'.files: []}, {folderName: 'Folder 2/ folder 2-1/ folder 2-1-1'.files: []}, {folderName: ' '.files: []}]})Copy the codeFolders are always a one-dimensional array, no matter how many layers of folders are nested, and folders are not nested within each item. Multi-level directories are implemented through the file name folderName.
folderNameIs an empty string, then itsfilesIn the top level directory of the zip package, not in any subfiles.folderNameIs a common string, for example:1 folder, thefolderNameCreate a new folder for the file name and attach it tofilesPut it in this folder.folderNameIs a string with a slash, such as:Folder 2/ folder 2-1/ folder 2-1-1, then create n folders in sequence and keep the nesting relationship, and finally change itsfilesPut it in the last folder.
To view demo complete code, source address: github.com/cachecats/e…