preamble
Front-end export form has a variety of programs, but once the form style is complex, then you have to use the code to write the style of excel, or more trouble. Each time the style is not the same, you have to rewrite, then use the advantages of the form template is reflected, you want to export different styles of forms directly modify the form template can be.
programmatic
I looked for two options:
1, the use of xlsx-template, the use of template syntax in xlsx placeholder fill after editing and export.
2, the use of exceljs, read the template, the use of rows and columns of coordinates positioning editing and export.
I've tried both, the first option is similar to this post of mine (/?id=27) used in docxtemplater, just docx replaced by xlsx, but xlsx-template is not as compatible with the browser side as docxtemplater, you have to deal with fs, path, and so on, and of course there are old brothers (/p/85c844d96cfb) Solved it by changing the project configuration. It's still a bit of a hassle and doesn't always work for older projects, so I used the second option.
move
1、Install exceljs and file-saver
npm i exceljs
npm i file-saver
2. xlsx templates are placed in the public directory of the project.
3, use fetch to read the xlsx template under public.
let response = await fetch('. /static/xlsx/'); //read the file
4, will read the data converted to buffer and then use exceljs load data.
let data = await (); //binary conversion
const workbook = new ();
await (data); //retrievebuffer
const worksheet = (1); //retrieve第一张表
5, the use of exceljs () to the specified cell value, getCell parameters for the rows and columns, such as modifying the first line of the first column of data for the test.
('1A').value = 'test'
6, use exceljs writeBuffer () read the form for the buffer and then use file-saver saveAs download.
await ().then(async (buffer) => {
let blob = new Blob([buffer], { type: 'application/octet-stream' });
await saveAs(blob, '');
= false;
});
The full methodology is as follows:
async exportExcel() {
= true;
let response = await fetch('./static/xlsx/'); //Read file
let data = await (); //binary conversion
const workbook = new ();
await (data); //retrievebuffer
const worksheet = (1); //retrieve第一张表
let cols = []; //column//A~Z
for (let i = 65; i < 91; i++) {
((i));
}
let row = []; //go on the rampage1~116
for (let i = 1; i < 117; i++) {
(i);
}
//Coordinate positioning to update data
(async (r) => {
(async (c) => {
if (r >= 9 && r <= 15 && c >= 'B' && c <= 'R') {
(`${c}${r}`).value = `${c}${r}`;
}
if (r >= 17 && r <= 30 && c >= 'B' && c <= 'X') {
(`${c}${r}`).value = `${c}${r}`;
}
});
});
({ includeEmpty: true }, (row, rowNumber) => {
// (rowNumber, );
});
//downloading
await ().then(async (buffer) => {
let blob = new Blob([buffer], { type: 'application/octet-stream' });
await saveAs(blob, '');
= false;
});
}
The final download of the exported form is below:
concluding remarks
You need to pay attention to asynchronous processing when reading data during use.
Link to original article:/?id=71