Project scenario:
1. Ant Design Vue uses forms to import presentation data
2. The project requirements are displayed as shown in the figure below, so it involves the processing and display of complex headers
3. This article mainly focuses on the processing and explanation of complex headers. Plug ins such as xlsx can be used for simple header import and export
Circulate the participant information corresponding to the product attribute and fill the display with tags
Functional analysis:
1. For the export effect, if the native program is used, the two-level complex header will not be exported, and the second level of the two-level header will be displayed by default, such as this;
Since there is no distinction between the first level header and the second level header, only one type of information will be displayed. However, such export will lead to information confusion and coverage, resulting in data loss and failure to achieve a rational effect.
Show the effect correctly
2. Import effect. If the parsed data is imported from this kind of complex header, the header of the form needs to be analyzed and logically processed to obtain the desired data set.
Project effect display: process the display data according to the required logic, including overwriting data, replacing data, replacing header parameters, replacing data containers, etc
Function realization:
npm introduction//Used to operate on xlsx data npm install xlsx //Custom export for table npm install file-saver npm install better-xlsx
Export function JS file:
This JS file can be used as a public method to place static files;
Program use (blogger project is in config file directory)
import ExportExcel from "@/config/excelpush";//Export get form import ReaderExcel from "@/config/getin";//Import read data
import { File } from 'better-xlsx';
import { saveAs } from 'file-saver';
function ExportExcel(column, dataSource, fileName = 'example') {
// New work spectrum
const file = new File();
dataSource = JSON.parse(JSON.stringify(dataSource));
// new table
let sheet = file.addSheet('sheet-test');
// Get the number of header rows
let depth = getDepth(column);
// Gets the number of columns in the header
let columnNum = getColumns(column);
// Number of new header rows
let rowArr = [];
for (let k = 0; k < depth; k++) {
rowArr.push(sheet.addRow());
}
// Fill cells according to the number of columns
rowArr.map(ele => {
for (let j = 0; j < columnNum; j++) {
let cell = ele.addCell();
cell.value = j;
}
});
// Initialize header
init(column, 0, 0);
// Flatten column s in order
let columnLineArr = [];
columnLine(column);
// Sort the data in the dataSource according to the column and convert it into a two-dimensional array
let dataSourceArr = [];
for(var x=0;x<dataSource.length;x++){
for(let info in dataSource[x]){
console.log(info)
//Here you can perform data operation on the exported table data
/*
if(info.indexOf('IsActive_')>=0){
console.log(dataSource[x][info])
dataSource[x][info]=dataSource[x][info]?'Winning the bid ':' ';
console.log(dataSource[x]);
}
*/
}
}
dataSource.map(ele => {
let dataTemp = [];
columnLineArr.map(item => {
dataTemp.push({
[item.dataIndex]: ele[item.dataIndex],
value: ele[item.dataIndex],
});
});
dataSourceArr.push(dataTemp);
});
// debugger;
// Drawing table data
dataSourceArr.forEach((item, index) => {
//Create the corresponding number of rows according to the data
let row = sheet.addRow();
row.setHeightCM(0.8);
//Create corresponding number of cells
item.map(ele => {
let cell = row.addCell();
if (ele.hasOwnProperty('num')) {
cell.value = index + 1;
} else {
cell.value = ele.value;
}
cell.style.align.v = 'center';
cell.style.align.h = 'center';
});
});
//Sets the width of each column
for (var i = 0; i < 4; i++) {
sheet.col(i).width = 20;
}
file.saveAs('blob').then(function(content) {
saveAs(content, fileName + '.xlsx');
});
// Flatten column s in order
function columnLine(column) {
column.map(ele => {
if (ele.children === undefined || ele.children.length === 0) {
columnLineArr.push(ele);
} else {
columnLine(ele.children);
}
});
}
// Initialize header
function init(column, rowIndex, columnIndex) {
column.map((item, index) => {
let hCell = sheet.cell(rowIndex, columnIndex);
// If there are no child elements, the column is full
if (item.title === 'operation') {
hCell.value = '';
return;
} else if (item.children === undefined || item.children.length === 0) {
// Add a cell to the first row
hCell.value = item.title;
hCell.vMerge = depth - rowIndex - 1;
hCell.style.align.h = 'center';
hCell.style.align.v = 'center';
columnIndex++;
// rowIndex++
} else {
let childrenNum = 0;
function getColumns(arr) {
arr.map(ele => {
if (ele.children) {
getColumns(ele.children);
} else {
childrenNum++;
}
});
}
getColumns(item.children);
hCell.hMerge = childrenNum - 1;
hCell.value = item.title;
hCell.style.align.h = 'center';
hCell.style.align.v = 'center';
let rowCopy = rowIndex;
rowCopy++;
init(item.children, rowCopy, columnIndex);
// Next cell start
columnIndex = columnIndex + childrenNum;
}
});
}
// Get header rows
function getDepth(arr) {
const eleDepths = [];
arr.forEach(ele => {
let depth = 0;
if (Array.isArray(ele.children)) {
depth = getDepth(ele.children);
}
eleDepths.push(depth);
});
return 1 + max(eleDepths);
}
function max(arr) {
return arr.reduce((accu, curr) => {
if (curr > accu) return curr;
return accu;
});
}
// Calculate the number of header columns
function getColumns(arr) {
let columnNum = 0;
arr.map(ele => {
if (ele.children) {
getColumns(ele.children);
} else {
columnNum++;
}
});
return columnNum;
}
}
export default ExportExcel;
Import function JS file:
This function requires the developer to obtain the information of the form for matching
The code is as follows:
export function readerExcel(f, headerCodes) {
//Custom function
var ret= function(arr){
var ress=[arr[0]];
for(var j=1;j<arr.length;j++){
var repeat= false;
let x=1;
for(var i=0;i<ress.length;i++){
if(arr[j]==ress[i]){
repeat=true;
ress.push(arr[j]+x);
x++
break;
}
}
if(!repeat){
ress.push(arr[j]);
}
}
return ress;
}
return new Promise(resolve => {
const res = [] // Assemble table data objects
let binary = ''
let wb // Read completed data
let outData
const reader = new FileReader()
reader.onload = function(e) {
const bytes = new Uint8Array(reader.result)
const length = bytes.byteLength
for (let i = 0; i < length; i++) {
binary += String.fromCharCode(bytes[i])
}
const XLSX = require('xlsx')
wb = XLSX.read(binary, {
type: 'binary'
})
const sheetArr = wb.Sheets // excel sheet
//This operation is to replace the empty position of the table with ""
wb.SheetNames.map((sheetName, sheetIndex) => {
const sheet2JSONOpts = {
/** Default value for null/undefined values */
defval: ''//Empty string assigned to defval
}
/*
{header:1,defval:''}
The operation here is to replace the header position of the table with ""
*/
outData = XLSX.utils.sheet_to_json(sheetArr[sheetName],{header:1,defval:''})
//outData is the parsed initial table data
//Here is the data model required by the blogger project, which can be changed by the developer - start
outData[1][0] = outData[0][0]
//The previous line of code is to deal with the processing scheme of the project presentation logic and replace the empty space in the header of the second level directory
let newArr_cs = []
newArr_cs = ret(outData[1]);
const arr = []
for(var x=1;x<outData.length;x++){
let obj = {}
for(var y=0;y<newArr_cs.length;y++){
obj[newArr_cs[y]] = outData[x][y];
}
arr.push(obj)
}
//Here is the data model required by the blogger project, which can be changed by the developer - end
res.push(arr)
})
resolve(res)
}
reader.readAsArrayBuffer(f)
})
}
export default readerExcel;
Content summary:
1. Display of exported data model;
2. Logical processing of imported data;