One, foreword
The recent project involved excel export, which really took a lot of time. At first, there was no need to modify the style level, so I chose Xlsx. JS, without too much consideration of style modification. But as the project, and put forward the customer needs in accordance with the format changes the demand of the style, so can only to look up any information related to modify excel style, wanted to use directly XLSX. Js, but apparently not XLSX. The basis of js version only high wide and merge cells more fundamental changes, to more complex style modification upgrade pro version, It’s not our STYLE to spend money on this. After a lot of research, we found that the only plugin that can export XLSX files and satisfy the required STYLE changes is xLSX-style (there may be a suitable tool but I don’t know), but xLSX-style seems to have been out of service for a long time. So there are still many problems in the process of getting started.
Second, sorting out needs
In my project, I need to export the page antd form, at the same time also need to deal with the data statistics in order to achieve the effect, export excel content to be decided according to the demand, the style needs to be modified on the width, height, background color, font, font size, merged cells, cell borders, fonts, colors, fonts, bold center, etc. It also needs to be compatible with IE11. According to the priority of the requirements, we will sort it
- 1. Excel export
- 2. Compatible with IE11
- 3. Statistical data
- 4, modify the width and height, merge cells
- 5. Modify other styles
To sum up the contents of the watch is not much, but it doesn’t really simple to implement, export and compatible ie11 in before they are complete, we will speak later, but the emphasis is on the statistical data, that is to say, we’re not going to 1 reduction antd forms, we need to define your own data, to calculate, then export, So we have to integrate all the data and then calculate it into a two-dimensional array that XLSX accepts. Of course, XLSX also supports direct export through the table tag, which we don’t use here.
Three, early preparation
- 1.
npm install xlsx-styleImport the plug-in into the project - 2,
import XLSX from 'xlsx-style'Introduce XLSX into the component - 3,
This relative module was not found:./cptable in./node_modules/[email protected]@xlsx-style/dist/cpexcelPerfect error - 4, find line 807 \node_modules\xlsx-style\dist\cpexcel
var cpt = require('./cpt' + 'able');replacevar cpt = cptable;Save and continue. - 5,
Error: Can't resolve 'fs' in 'E:\\xlsx-style'Perfect error - The fs module is the server side module. Although Webpack can load fs code to the front end, but the browser cannot explain and run it. Therefore, to fix this error, Either try to modify the code so that the operation of calling FS occurs on the server side, or try to install a browser plug-in that enables the browser to recognize FS, so that FS can be recognized and run in the browser. In our requirements, only the export of Excel is involved, so fs module should not be used. So we chose to add the relevant configuration items to the configuration file so that fs is not used, so we added them to the webpack.config.js configuration
node: { fs: 'empty', }, everyone’s project scaffolding may be different, configuration can be put where you can try, here can only tell you at the top of the configuration item below. - Xlsx-style = xlsx-style = xlsx-style = xlsx-style = xlsx-style = xlsx-style DefaultCellStyle is the default style for all cells, so we change it to the overall style we want, which greatly reduces the time we need to modify the style later.
- 8, extend the function of modifying line height: Xlsx-style does not contain the function to modify the line height, here we want to extend it, of course not write, because xlsx-style comes from XLSXJS, so the source code is very similar, we NPM install XLSXJS, and then find the corresponding method to modify the line height, copy the extension to xlsx-style can be specific as follows
Xlsx-style = xlsx-style = xlsx-style = xlsx-style = xlsx-style // write_ws_xml_data copied from xlsx.js in the XLSX folder Var PPI = 96, PPI = DEF_PPI; function px2pt(px) { return px * 96 / PPI; } function pt2px(pt) { return pt * PPI / 96; } function write_ws_xml_data(ws, opts, idx, wb) { var o = [], r = [], range = safe_decode_range(ws['! ref']), cell="", ref, rr = "", cols = [], R=0, C=0, rows = ws['!rows']; var dense = Array.isArray(ws); var params = ({r:rr}), row, height = -1; for(C = range.s.c; C <= range.e.c; ++C) cols[C] = encode_col(C); for(R = range.s.r; R <= range.e.r; ++R) { r = []; rr = encode_row(R); for(C = range.s.c; C <= range.e.c; ++C) { ref = cols[C] + rr; var _cell = dense ? (ws[R]||[])[C]: ws[ref]; if(_cell === undefined) continue; if((cell = write_ws_xml_cell(_cell, ref, ws, opts, idx, wb)) ! = null) r.push(cell); } if(r.length > 0 || (rows && rows[R])) { params = ({r:rr}); if(rows && rows[R]) { row = rows[R]; if(row.hidden) params.hidden = 1; height = -1; if (row.hpx) height = px2pt(row.hpx); else if (row.hpt) height = row.hpt; if (height > -1) { params.ht = height; params.customHeight = 1; } if (row.level) { params.outlineLevel = row.level; } } o[o.length] = (writextag('row', r.join(""), params)); } } if(rows) for(; R < rows.length; ++R) { if(rows && rows[R]) { params = ({r:R+1}); row = rows[R]; if(row.hidden) params.hidden = 1; height = -1; if (row.hpx) height = px2pt(row.hpx); else if (row.hpt) height = row.hpt; if (height > -1) { params.ht = height; params.customHeight = 1; } if (row.level) { params.outlineLevel = row.level; } o[o.length] = (writextag('row', "", params)); } } return o.join(""); }Copy the code- 9. If defaultCellStyle is not found, there may be no corresponding method for converting a 2d array to a worksheet object, which can be copied from the official document to the corresponding code block and then extended to xlsx.util, as shown in the following figure
Function aoA_to_sheet (data){var defaultCellStyle = {font: {name: "Meiryo UI", sz: 11, color: { auto: 1 } }, border: { top: { style:'thin', color: { auto: 1 } }, left: { style:'thin', color: { auto: 1 } }, right: { style:'thin', color: { auto: 1 } }, bottom: { style:'thin', color: {auto: 1}}, alignment: {/// wrapText: 1, // Horizontal: "center", vertical: "center", indent: 0}}; function dateNum(date){ let year = date.getFullYear(); let month = (date.getMonth()+1)>9? date.getMonth()+1:'0'+(date.getMonth()+1); let day = date.getDate()>9? date.getDate():'0'+date.getDate(); return year+'/'+month+'/'+day; }; const ws = {}; const range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }}; for(let R = 0; R ! == data.length; ++R) { for(let C = 0; C ! == data[R].length; ++C) { if(range.s.r > R) range.s.r = R; if(range.s.c > C) range.s.c = C; if(range.e.r < R) range.e.r = R; if(range.e.c < C) range.e.c = C; Const cell = {v: data[R][C], s: defaultCellStyle}; const cell_ref = XLSX.utils.encode_cell({c:C,r:R}); /* TEST: proper cell types and value handling */ if(typeof cell.v === 'number') cell.t = 'n'; else if(typeof cell.v === 'boolean') cell.t = 'b'; else if(cell.v instanceof Date) { cell.t = 'n'; cell.z = XLSX.SSF._table[14]; cell.v = dateNum(cell.v); } else cell.t = 's'; ws[cell_ref] = cell; } } /* TEST: proper range */ if(range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range); return ws; };Copy the code- 10. Extend the function of generating Excel from DOM nodes: Find the table_to_sheet() method from the utils of XLSXJS, copy it to xlsx-style, and port all the other function variables referenced to it to xlsx-style until it works properly. Let’s try it out with a little demo, and it works fine.
- 11. Expand the function of generating Excel according to two-dimensional array: same as above, find aoA_to_sheet method in original XLSX for transplantation.
Export Excel and compatible with IE11
- 1. Export Excel process using XLSX: Convert data to Excel based on a two-dimensional array (AOA_to_sheet) or dom node (table_to_sheet) Blob -> createObjectURL(blob) create URL -> Others: create a tag, initialize the SRC attribute of the tag, trigger the click event,ie11: window.navigator.msSaveOrOpenBlob.
- 2, compatible with IE11, as mentioned above, the last step of export is not the same, the following is the entire export process code
Export default function downLoadExcel(data, type, config, ele) {console.log(config, 'Excel config '); var blob = IEsheet2blob(ws); if (IEVersion() ! == 11) { openDownloadXLSXDialog(blob, `${type}.xlsx`); } else { window.navigator.msSaveOrOpenBlob(blob, `${type}.xlsx`); } } function dislodgeLetter(str) { var result; var reg = /[a-zA-Z]+/; //[a-za-z] indicates bai matching letters, While (result = str.match(reg)) {STR = str.replace(result[0], ''); Result [0] = str.match(reg)} return STR; } function IEsheet2blob(sheet, sheetName) { try { new Uint8Array([1, 2]).slice(0, 2); } Catch (e) {// Internet Explorer or some browsers do not support the uint8array.slice () method. To use an Array. Slice () method Uint8Array. Prototype. Slice = Array. Prototype. Slice; } sheetName = sheetName || 'sheet1'; var workbook = { SheetNames: [sheetName], Sheets: {} }; workbook.Sheets[sheetName] = sheet; Var wopts = {bookType: 'XLSX ', // The file type to generate bookSST: Type: 'binary'} type: 'binary'} type: 'binary'} var wbout = XLSX.write(workbook, wopts); var blob = new Blob([s2ab(wbout)], { type: "application/octet-stream" }); Function s2ab(s) {var buf = new ArrayBuffer(s.length); var view = new Uint8Array(buf); for (var i = 0; i ! = s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF; return buf; } return blob; } function getDefaultStyle() { let defaultStyle = { fill: { fgColor: { rgb: '' } }, font: { name: "Meiryo UI", sz: 11, color: { rgb: '' }, bold: true }, border: { top: { style: 'thin', color: { auto: 1 } }, left: { style: 'thin', color: { auto: 1 } }, right: { style: 'thin', color: { auto: 1 } }, bottom: { style: 'thin', color: { auto: 1}}, alignment: {/// wrapText: 1, // Horizontal: "center", vertical: "center", indent: 0}}; return defaultStyle; } function IEVersion() { var userAgent = navigator.userAgent; Var isIE = userAgent.indexof ("compatible") > -1 && userAgent.indexof ("MSIE") > -1; Var isEdge = userAgent.indexof ("Edge") > -1 &&! isIE; Var isIE11 = userAgent.indexof ('Trident') > -1 && userAgent.indexof ("rv:11.0") > -1; if (isIE) { var reIE = new RegExp("MSIE (\\d+\\.\\d+);" ); reIE.test(userAgent); var fIEVersion = parseFloat(RegExp["$1"]); if (fIEVersion == 7) { return 7; } else if (fIEVersion == 8) { return 8; } else if (fIEVersion == 9) { return 9; } else if (fIEVersion == 10) { return 10; } else { return 6; <=7}} else if (isEdge) {return 'edge'; //edge } else if (isIE11) { return 11; //IE11 } else { return -1; }} function openDownloadXLSXDialog(url, saveName) { if (typeof url == 'object' && url instanceof Blob) { url = URL.createObjectURL(url); Var aLink = document.createElement('a'); aLink.href = url; aLink.download = saveName || ''; / / HTML 5 additional attributes, save the file name is specified, the can not suffixes, note that file:/// mode will not take effect var event; if (window.MouseEvent) event = new MouseEvent('click'); else { event = document.createEvent('MouseEvents'); event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null); } aLink.dispatchEvent(event); }Copy the codeV. Understanding and use of XLSX
- A worksheet object is an object that stores Excel data. Represents a cell, and we modify the style by doing something to that cell.
- 2. Cell objects, as mentioned above, each cell is a separate object stored in the Worksheet. The property of the Worksheet is called cell coordinates, that is, the position of the cell displayed in excel
{t: 'n' s: {}, v: 'values'}, where t is the type of the value, n is a number, so it’s easy to guess that the value of t s is a string; S is the style object, and there are many fields that you can style the cell, and v is the value of the cell, as we’ll see later. - 3,! Col: a special property in the worksheet object in the format of an array. For example:
[{wpx:100},{wpx:100},{wpx:80}], set up! The col value can change the column width, where WPX stands for the width in px units, as well as other types of writing such as WCH - 4,! 29. Merge: a configuration attribute used to merge cells in the form of an array of data such as:
[{s:{r:0,c:2},e:{r:0,c:6}},{s:{r:1,c:3},e:{r:8,c:3}}],s is the starting cell coordinates, R row, C column, and e is the ending cell coordinates. The example here represents merging the 2nd to 6th cells in row 0 and the 1st to 8th cells in column 3. - 5. Workbook: An object for storing worksheets in the format of
{sheetName:[],sheets:{}}SheetName stores worksheet names, sheets stores worksheet objects, and sheets stores worksheet names. - 6. Cell range transformation
Xlsx.utils.encode_row (2); Xlsx.utils.decode_row ("2"); Xlsx.utils.encode_col (2); Xlsx.utils. Decode_col ("A"); Xlsx.utils.encode_cell ({c: 1, r: 1}); //"B2" // decode cell xlsx.utils.decode_cell ("B1"); / / {c: 1, r: 0} / / encoding cell range XLSX. Utils. Encode_range ({s: {c: 1, r: 0}, e: {c: 2, r: 8}}); //"B1:C9" // decode_range("B1:C9"); //{s:{c: 1, r: 0},e: {c: 2, r: 8}}Copy the code- Cell style, which lists common style configurations
Color :{RGB: "}, // font color: true, // whether to bold}, fill: {// Background style bgColor: {RGB: "}, // background color, fill pattern background fgColor: {RGB: "}, // front background color, modify the color of the project with this}, border: [{// border related styles},{},{},{},{}], alignment: {// vertical: 'center', // horizontal: 'center', // horizontal alignment wrapText: true, // wrap}} // More detailed configuration parameters are shown belowCopy the code
- 8, other printing configuration or other functions are not involved in my project, we can understand by ourselves, as well as bloB related parameters, WPOT related parameters, we can find information according to the code and demand
Six, style modification and related packaging
- 1. Analyze the pattern of project modification: This part of the process is to make the code can have a reasonable format, we organize a particular style configuration data format, and then through the function processing catch with the style of the sheet to accept configuration information, so that the project code in the subsequent any project can be configured according to the data format, and to design data formats, We should consider all style changes is commonly used in project plan, first of all, the first point, the head and bottom of the table must be one of the most common modified object, and often to the background color, font color, font is bold and font size changes, so we enclosed a config object, the object with the merge property is used to set the cell to merge, The size attribute is used to set the line height and column width (row line height, col column width), then set up a field myStyle style information is used to store, we are going to custom, the second point, after processing is carried out for the head naturally want to deal with the bottom, the bottom of the handle and head the same, but should pay attention to the bottom of the need to indicate the number of rows, otherwise unable to position the bottom line; Third, processing for a particular line, sometimes an entire line or many lines need special processing, so you need a configuration that changes the style for a particular line; Fourth, as for the row modification, define an attribute for the modification of a specific column (colCells). Fifth, the style processing of a special table header, table header is not necessarily a uniform style, so there needs to be a configuration for the processing of the corresponding table header; Sixth, highlight processing, meet the conditions of the cell may need to highlight, here reserved a highlight configuration; Finally, for the time being, cells merged with the table_to_sheet operation may lose borders and require configuration information to fill in the borders.
- 2. The above is integrated as shown in the figure below
- 3. The encapsulation and processing I have done is still very rough, but it basically meets the needs of the project. If you have a better processing method, welcome to exchange and discuss.
if (config.merge.length ! = 0) { ws['!merges'] = config.merge; } ws['!cols'] = config.size.cols; If (config.mystyle.all) {// The style applied to all cells must be at the top, Keys (ws).foreach ((item, index) => {if (ws[item].t) {ws[item].s = config.mystyle.all; }}); } if (config.myStyle.headerColor) { if (config.myStyle.headerLine) { let line = config.myStyle.headerLine; let p = /^[A-Z]{1}[A-Z]$/; Object.keys(ws).forEach((item, index) => { for (let i = 1; i <= line; i++) { if (item.replace(i, '').length == 1 || (p.test(item.replace(i, '')))) { let myStyle = getDefaultStyle(); myStyle.fill.fgColor.rgb = config.myStyle.headerColor; myStyle.font.color.rgb = config.myStyle.headerFontColor; ws[item].s = myStyle; }}}); } } if (config.myStyle.specialCol) { config.myStyle.specialCol.forEach((item, index) => { item.col.forEach((item1, index1) => { Object.keys(ws).forEach((item2, index2) => { if (item.expect && item.s) { if (item2.includes(item1) && ! item.expect.includes(item2)) { ws[item2].s = item.s; } } if (item.t) { if (item2.includes(item1) && item2.t) { ws[item2].t = item.t; }}}); }); }); } if (config.myStyle.bottomColor) { if (config.myStyle.rowCount) { Object.keys(ws).forEach((item, index) => { if (item.indexOf(config.myStyle.rowCount) ! = -1) { let myStyle1 = getDefaultStyle(); myStyle1.fill.fgColor.rgb = config.myStyle.bottomColor; ws[item].s = myStyle1; } }) } } if (config.myStyle.colCells) { Object.keys(ws).forEach((item, index) => { if (item.split('')[0] === config.myStyle.colCells.col && item ! == 'C1' && item ! == 'C2') { ws[item].s = config.myStyle.colCells.s; }})} the if (config. MyStyle. MergeBorder) {/ / handling of export merge cells without borders let arr = [" A ", "B", "C", "D", "E", "F", "G", "H" and "I" and "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"] let range = config.myStyle.mergeBorder; Range.foreach ((item, index) => {if (item.s.c == item.e.c) {let star = item.s.r; let end = item.e.r; for (let i = star + 1; i <= end; i++) { ws[arr[i] + (Number(item.s.c) + 1)] = { s: Ws [arr[star] + (Number(item.s.c) + 1)].s}}} else {let star = item.s.c; let end = item.e.c; for (let i = star + 1; i <= end; i++) { ws[arr[item.s.r] + (i + 1)] = { s: ws[arr[item.s.r] + (star + 1)].s } } } }); } if (config.myStyle.specialHeader) { config.myStyle.specialHeader.forEach((item, index) => { Object.keys(ws).forEach((item1, index1) => { if (item.cells.includes(item1)) { ws[item1].s.fill = { fgColor: { rgb: item.rgb } }; if (item.color) { ws[item1].s.font.color = { rgb: item.color }; }}}); }); } if (config.myStyle.heightLightColor) { Object.keys(ws).forEach((item, index) => { if (ws[item].t === 's' && ws[item].v && ws[item].v.includes('%') && ! item.includes(config.myStyle.rowCount)) { if (Number(ws[item].v.replace('%', '')) < 100) { ws[item].s = { fill: { fgColor: { rgb: config.myStyle.heightLightColor } }, font: { name: "Meiryo UI", sz: 11, color: { auto: 1 } }, border: { top: { style: 'thin', color: { auto: 1 } }, left: { style: 'thin', color: { auto: 1 } }, right: { style: 'thin', color: {auto: 1}}, bottom: {style: 'thin', color: {auto: 1}}, alignment: {/// 1, // Horizontal: "center", vertical: "center", indent: 0}}}}}); } if (config.myStyle.rowCells) { config.myStyle.rowCells.row.forEach((item, index) => { Object.keys(ws).forEach((item1, index1) => { let num = Number(dislodgeLetter(item1)); if (num === item) { ws[item1].s = config.myStyle.rowCells.s; }}); }); } object.keys (ws).foreach ((item, index) => {// Null if (ws[item]. ws[item].v) { ws[item].v = '-'; }});Copy the code7. Difficulties in data processing
- 1. The exported Excel popup shows a file error, as shown below:
- 2. Merge cells: This is the point I’ve struggled with for a long time, because there is no small part form export meter, my project is derived under different departments within the company members information, also is the need to merge by department, at the same time the combined each department, there is a line below the classification of, is to cover, so how to achieve, think twice, Finally, I could only make a summary after obtaining the personnel data of the whole department and then add a row to the total data. Meanwhile, ANOTHER array was used to store the number of personnel of the department for subsequent cell combination calculation. The following is the calculation code of my subtotal:
Const countFormatedData = (formatedData, text) => {//formatedData is the entire department's data, Text is the name of the newly added summary row in the current project is subtotal or total if (formatedData.length! = 0) {let result1 = {' 1 month: 0, 'February: 0,' march: 0, 'April: 0,' may ': 0,' June ': 0,' July: 0, 'August' : 0, 'September' : 0, 'October: '0, 0,' November. December ': 0, [T] (' achievements of the previous period) : 0, [T (' next performance)] : 0, [T (' annual performance)] : 0, [T (' target of the previous period)] : 0, [T (' next target)] : 0, [T (' annual target)] : 0, [' common January] : 0, [' common February] : 0, [' march together] : 0, [' common April] : 0, [' common may] : 0, [' common June] : 0, [' common July] : 0, [' common August] : 0, [' common September] : 0, [' October ']: 0, [' November ']: 0, [' December ']: 0 0,} let result = formateddata.reduce ((init, next) => {if (next[' deploy '] === T(' subcount ')) {return init; } else {return {' 1 month: init [' January] + next [' January], 'February: init [' February] + next [' February],' march: init [' march] + next [' march], 'April: Init [' April] + next [' April], 'may' : init [' may '] + next [' may '], 'June' : init [' June '] + next [' June '], 'July: Init [' July] + next [' July], 'August' : init [' August '] + next [' August '], 'September' : init [' September '] + next [' September '], 'October: Init [' October] + next [' October], 'November: init [' November] + next [' November],' December: init [' December] + next [' December '], [T] (' achievements of the previous period) : Init [T (' achievements of the previous period)] + next [T (' achievements of the previous period)], [T (' next performance)] : init [T (' next performance)] + next [T (' next performance)], [T (' annual performance)] : Init [T (' annual performance)] + next [T (' annual performance)], [T (' target of the previous period)] : init [T (' target of the previous period)] + next [T (' target of the previous period)], [T (' next target)] : Init [T (' next target)] + next [T (' next target)], [T (' annual target)] : init [T (' annual target)] + next [T (' annual target)], [' common January] : Init [' common January] + next [' common January], [' common February] : init [' common February] + next [' common February], [' march together] : init [' march together] + next [' common march], [' common April] : Init [' common April] + next [' common April], [' common may] : init [' common may] + next [' common may], [' common June] : init [' common June] + next [' common June '], [' common July] : Init [' common July] + next [' common July], [' common August] : init [' common August] + next [' common August], [' common September] : init [' common September] + next [' common September], [' common October] : Init [' common October] + next [' common October], [' common November] : init [' common November] + next [' common November], [' common December] : Init [' common 12 '] + next[' common 12 '],}}}, result1); Result [T(' target ')] = result[T(' target ')]? ` ${(result [T (' achievements of the previous period)] / result [T (' target of the previous period)] * 100). The toFixed (1)} % ` : '0%'. Result [T(' target ')] = result[T(' target ')]? ` ${(result [T (' next performance)] / result [T (' next target)] * 100). The toFixed (1)} % ` : '0%'. Result [T(' year target ')] = result[T(' year target ')]? ` ${(result [T (' annual performance)] / result [T (' annual target)] * 100). The toFixed (1)} % ` : '0%'. result['NO.'] = text; Result [' deploy '] = text; Result [T(" name ")] = text; formatedData.push(result) } return formatedData; }Copy the codeThe best way I can think of is to use reduce to calculate the sum, as usual, welcome to explore a better way to write.
Here is my calculation logic code for merging the cell configuration:
ForEach ((item, index) => {exportData.foreach ((item, index) => { Reason please see cover the if (item [1] = = = T (' total ') | | item [1] = = = T (' subtotal)) {merge. Push ({s: {r: index, c: 0}, e: {r: index, c: 2}}); If (item [1] = = = T (' subtotal)) {/ / config is before we have introduced the configuration information for the config. MyStyle. RowCells. Row. Push (index + 1); }}}); If (index == 0) {merge. Push ({s: {r: 2, c: 1}, e: {r: 1}); item + 1, c: 1 } }); } else { let count = 0; for (let i = 0; i < index; i++) { count = count + deptCount[i]; } if (item > 1) { merge.push({ s: { r: count + index + 2, c: 1 }, e: { r: count + item + index + 1, c: 1 } }); }}})Copy the codeThere is also a table in the project that does not require subtotals, which is naturally simpler than subtotals, and will not be introduced here.
- 3, data to heavy: as we all know, once it comes to statistics, data to heavy will have to consider, because any redundant data, will cause the statistical summary data error, here do not do more details, when the error customer complaints can be criticized by the superior; And is the most simple to heavy in general array to heavy, but it can’t be that simple in practical projects, often involves an array of objects to heavy, and still is not completely equal to heavy conditions, may contain each other to get rid of the former one or the latter, also could not get rid of one, but will include all the elements of both between them, That’s a big nod at this point. Here are a few ways I use to heavy, welcome your reference, more welcome to point out the lack of.
First, cache the advance array that needs to be de-duplicated, and then filter out the elements in the array using filter:
Export function removeDuplicate(data,field) {let newData = data // let shouldDelete = []; For (let I = 0; i < newData.length; i++) { for (let j = i + 1; j < newData.length; j++) { if (newData[i][field] == newData[j][field]) { shouldDelete.push({[field]:newData[i][field],index:i}); } } } console.log(shouldDelete); if (shouldDelete.length ! = 0) { newData = newData.filter((item,index) => { let result = true; shouldDelete.forEach((item1) => { if (item[field] === item1[field]&&index==item1.index) { result = false; }}); return result; }); } return newData; }Copy the codeSelect * from userName where userName = userName; select * from userName where userName = userName;
Export function duplicateRemove(flowData) {let shouldDelete = []; For (let I = 0; i < newFlowInfo.length; i++) { for (let j = i + 1; j < newFlowInfo.length; J ++) {let name = newFlowInfo[I].username.split(', ').concat(newFlowInfo[j].username.split(', ')); let newName = Array.from(new Set(name)); if (name.length ! = newname. length // There is a duplicate entry && newFlowInfo[I].code && newFlowInfo[I].code! == 'Committee' && newFlowInfo[j].code && newFlowInfo[j].code ! == 'Committee') { for (let k = i; k < j; k++) { shouldDelete.push(newFlowInfo[k]); } } } } console.log(shouldDelete); if (shouldDelete.length ! = 0) { newFlowInfo = newFlowInfo.filter((item) => { let result = true; shouldDelete.forEach((item1) => { if (item.title === item1.title) { result = false; }}); return result; }); } return newFlowInfo; }Copy the codeThe third way, which removes the same UPN term, is the simplest:
Let data = {}; result.data.data = result.data.data.reduce((cur, next) => { data[next.UPN] ? "" : data[next.UPN] = true && cur.push(next); return cur; } []);Copy the codeThe above are the ways I used in the project, of course, there are other ways, here are a few separate ways to show.
Eight, summary
Function of excel export a lot of front-end project need to use, but rarely can meet the demand of most of the tools on the market, can’t we can only themselves, but for Monday to ultimately meet the requirements, the current processing while it is very rough, but this time we will continue to improve the trust project use what demand can also be used again in the future, I still have to spend more time processing and packaging a more convenient to use excel export function module, you have any suggestions or questions please feel free to see the comment section, later I will write my Excel export code and processed XLSX-style on Github for everyone to use and improve, that’s all for today (^_^)
Project information: github.com/wannigebang…
Update: Improved XLSX-style has been released to NPM! Install commandnpm install xlsx-style-medalsoft
Nine, update,
Recently, I have sorted out the functional module code, adding type judgment and multi-table export, but because of many changes, the code has become a bit complex, there must be room for optimization of the logic, if you have a chance to do it again in the future, let’s put the latest one up first
import XLSX from 'xlsx-style';
// npm install xlsx-style-medalsoft
interface IStyle {
font?: any;
fill?: any;
border?: any;
alignment?: any;
}
interface ICell {
r: number;
c: number;
}
interface IMerge {
s: ICell;
e: ICell
}
interface ICol {
wpx?: number;
wch?: number;
}
interface ISize {
cols: ICol[];
rows: any[];
}
interface ISpecialHeader {
cells: string[];
rgb: string;
color?: string;
}
interface ISpecialCol {
col: string[];
rgb: string;
expect: string[];
s: IStyle;
t?: string;
}
interface IRowCells {
row: string[];
s: IStyle;
}
interface IMyStyle {
all?: IStyle;
headerColor?: string;
headerFontColor?: string;
headerLine?: number;
bottomColor?: string;
rowCount?: number;
heightLightColor?: string;
rowCells?: IRowCells;
specialHeader?: ISpecialHeader[];
specialCol?: ISpecialCol[];
mergeBorder?: any[];
}
interface IConfig {
merge?: IMerge[];
size?: ISize;
myStyle?: IMyStyle;
}
/*
* @function 导出excel的方法
* @param data table节点||二维数组
* @param type 导出excel文件名
* @param config 样式配置参数 { all:{all样式的基本格式请参考xlsx-style内xlsx.js文件内的defaultCellStyle}
* merge:[s:{r:开始单元格行坐标,c:开始单元格列坐标},e:{r:结束单元格行坐标,c:结束单元格列坐标}]
* size:{col:[{wpx:800}],row:[{hpx:800}]},
* headerColor: 八位表格头部背景颜色, headerFontColor: 八位表格头部字体颜色,bottomColor:八位表格底部背景色,
* rowCount:表格底部行数, specialHeader: [{cells:[特殊单元格坐标],rgb:特殊单元格背景色}],
* sepcialCol:[{col:[特殊列的列坐标],rgb:特殊列的单元格背景色,expect:[特殊列中不需要修改的单元格坐标],s:{特殊列的单元格样式}}
* }]
*
* 导出流程 table节点|二维数组->worksheet工作表对象->workboo工作簿对象->bolb对象->uri资源链接->a标签下载或者调用navigator API下载
* 每个worksheet都是由!ref、!merge、!col以及属性名为单元格坐标（A1,B2等）值为{v:单元格值,s:单元格样式,t:单元格值类型}的属性组成
*/
export function downLoadExcel(exportElement: [][] | any, fileName: string, config: IConfig|IConfig[], multiSheet?: boolean, sheetNames?: string[]) {
let ws;
let wb = [];
if (multiSheet) {
exportElement.forEach((item, index) => {
wb.push(getSheetWithMyStyle(item, config[index]));
});
} else {
if(!Array.isArray(config)){
ws = getSheetWithMyStyle(exportElement, config);
}
}
console.log(ws, 'worksheet数据');
if (ws) {
downLoad([ws], fileName,sheetNames);
} else {
downLoad(wb, fileName, sheetNames);
}
}
export function downLoad(ws, fileName: string, sheetNames?: string[]) {
var blob = IEsheet2blob(ws, sheetNames);
if (IEVersion() !== 11) { //判断ie版本
openDownloadXLSXDialog(blob, `${fileName}.xlsx`);
} else {
window.navigator.msSaveOrOpenBlob(blob, `${fileName}.xlsx`);
}
}
export function getWorkSheetElement(exportElement: [][] | any) {
let ifIsArray = Array.isArray(exportElement);
let ws = ifIsArray ? XLSX.utils.aoa_to_sheet(exportElement) : XLSX.utils.table_to_sheet(exportElement);
return ws;
}
export function getSheetWithMyStyle(exportElement: [][] | any, config: IConfig, callback?: Function) {
//样式处理函数,返回ws对象，如果要对ws对象进行自定义的修改，可以单独调用此函数获得ws对象进行修改
try {
let ws = getWorkSheetElement(exportElement);
console.log(config, 'excel配置参数');
//根据data类型选择worksheet对象生成方式
if (config.merge) {
ws['!merges'] = config.merge;
}
ws['!cols'] = config.size.cols;
//all样式的基本格式请参考xlsx-style内xlsx.js文件内的defaultCellStyle
if (config.myStyle) {
if (config.myStyle.all) { //作用在所有单元格的样式，必须在最顶层，然后某些特殊样式在后面的操作中覆盖基本样式
Object.keys(ws).forEach((item, index) => {
if (ws[item].t) {
ws[item].s = config.myStyle.all;
}
});
}
if (config.myStyle.headerColor) {
if (config.myStyle.headerLine) {
let line = config.myStyle.headerLine;
let p = /^[A-Z]{1}[A-Z]$/;
Object.keys(ws).forEach((item, index) => {
for (let i = 1; i <= line; i++) {
if (item.replace(i.toString(), '').length == 1 || (p.test(item.replace(i.toString(), '')))) {
let headerStyle = getDefaultStyle();
headerStyle.fill.fgColor.rgb = config.myStyle.headerColor;
headerStyle.font.color.rgb = config.myStyle.headerFontColor;
ws[item].s = headerStyle;
}
}
});
}
}
if (config.myStyle.specialCol) {
config.myStyle.specialCol.forEach((item, index) => {
item.col.forEach((item1, index1) => {
Object.keys(ws).forEach((item2, index2) => {
if (item.expect && item.s) {
if (item2.includes(item1) && !item.expect.includes(item2)) {
ws[item2].s = item.s;
}
}
if (item.t) {
if (item2.includes(item1) && ws[item2].t) {
ws[item2].t = item.t;
}
}
});
});
});
}
if (config.myStyle.bottomColor) {
if (config.myStyle.rowCount) {
Object.keys(ws).forEach((item, index) => {
if (item.indexOf((config.myStyle.rowCount).toString()) != -1) {
let bottomStyle = getDefaultStyle();
bottomStyle.fill.fgColor.rgb = config.myStyle.bottomColor;
ws[item].s = bottomStyle;
}
})
}
}
config.myStyle?.specialHeader?.forEach((item, index) => {
Object.keys(ws).forEach((item1, index1) => {
if (item.cells.includes(item1)) {
ws[item1].s.fill = {
fgColor: {
rgb: item.rgb
}
};
if (item.color) {
ws[item1].s.font.color = {
rgb: item.color
};
}
}
});
});
if (config.myStyle.heightLightColor) {
Object.keys(ws).forEach((item, index) => {
if (ws[item].t === 's' && ws[item].v && ws[item].v.includes('%') && !item.includes((config.myStyle.rowCount).toString())) {
if (Number(ws[item].v.replace('%', '')) < 100) {
ws[item].s = {
fill: {
fgColor: {
rgb: config.myStyle.heightLightColor
}
},
font: {
name: "Meiryo UI",
sz: 11,
color: {
auto: 1
}
},
border: {
top: {
style: 'thin',
color: {
auto: 1
}
},
left: {
style: 'thin',
color: {
auto: 1
}
},
right: {
style: 'thin',
color: {
auto: 1
}
},
bottom: {
style: 'thin',
color: {
auto: 1
}
}
},
alignment: {
/// 自动换行
wrapText: 1,
// 居中
horizontal: "center",
vertical: "center",
indent: 0
}
}
}
}
});
}
config.myStyle?.rowCells?.row.forEach((item, index) => {
Object.keys(ws).forEach((item1, index1) => {
let num = Number(dislodgeLetter(item1));
if (num == Number(item)) {
ws[item1].s = config.myStyle.rowCells.s;
}
});
});
if (!Array.isArray(exportElement) && config.myStyle.mergeBorder) { //对导出合并单元格无边框的处理，只针对dom导出，因为只有dom导出会出现合并无边框的情况
let arr = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"]
let range = config.myStyle.mergeBorder;
range.forEach((item, index) => {
if (item.s.c == item.e.c) { //行相等,横向合并
let star = item.s.r;
let end = item.e.r;
for (let i = star + 1; i <= end; i++) {
ws[arr[i] + (Number(item.s.c) + 1)] = {
s: ws[arr[star] + (Number(item.s.c) + 1)].s
}
}
} else { //列相等，纵向合并
let star = item.s.c;
let end = item.e.c;
for (let i = star + 1; i <= end; i++) {
ws[arr[item.s.r] + (i + 1)] = {
s: ws[arr[item.s.r] + (star + 1)].s
}
}
}
});
}
}
callback && callback();
Object.keys(ws).forEach((item, index) => { //空数据处理，单元格值为空时不显示null
if (ws[item].t === 's' && !ws[item].v) {
ws[item].v = '-';
}
});
Object.keys(ws).forEach((item, index) => { //空数据处理，单元格值为空时不显示null
if (ws[item].t === 's' && ws[item].v.includes('%')) {
ws[item].v = ws[item].v.includes('.') ? (ws[item].v.replace('%', '').split('.')[1] === '0' ? `${ws[item].v.replace('%', '').split('.')[0]}%` : ws[item].v) : ws[item].v;
}
});
return ws;
} catch (e) {
throw (e);
}
}
function transIndexToLetter(num) { //数字转字母坐标，25->Z ,26-> AA
if (num < 26) {
return String.fromCharCode(num + 65);
} else {
return transIndexToLetter(Math.floor(num / 26) - 1) + transIndexToLetter(num % 26);
}
}
function dislodgeLetter(str) { //去掉字符串中的字母
var result;
var reg = /[a-zA-Z]+/; //[a-zA-Z]表示bai匹配字母，dug表示全局匹配
while (result = str.match(reg)) { //判断str.match(reg)是否没有字母了
str = str.replace(result[0], ''); //替换掉字母 result[0] 是 str.match(reg)匹配到的字母
}
return str;
}
export function sheetToJSON(wb,option?){
return XLSX.utils.sheet_to_json(wb,option);
}
function IEsheet2blob(sheet, sheetName?: string | string[]) {
console.log(sheet,'sheet');
console.log(sheetName,'sheetName');
try {
new Uint8Array([1, 2]).slice(0, 2);
} catch (e) {
//IE或有些浏览器不支持Uint8Array.slice()方法。改成使用Array.slice()方法
Uint8Array.prototype.slice = Array.prototype.slice;
}
sheetName = Array.isArray(sheetName)?(sheetName.length?sheetName:['sheet1']):'sheet1';
var workbook = {
SheetNames: Array.isArray(sheetName) ? sheetName : [sheetName],
Sheets: {}
};
if (Array.isArray(sheetName)) {
sheetName.forEach((item, index) => {
workbook.Sheets[item] = sheet[index];
});
} else { workbook.Sheets[sheetName] = sheet; }
console.log(workbook,'workbook');
// 生成excel的配置项
var wopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
type: 'binary'
};
var wbout = XLSX.write(workbook, wopts);
var blob = new Blob([s2ab(wbout)], {
type: "application/octet-stream"
});
// 字符串转ArrayBuffer
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
return blob;
}
export function sheetToWorkBook(sheet, sheetName?: string | string[]){
console.log(sheet,'sheet');
console.log(sheetName,'sheetName');
try {
new Uint8Array([1, 2]).slice(0, 2);
} catch (e) {
//IE或有些浏览器不支持Uint8Array.slice()方法。改成使用Array.slice()方法
Uint8Array.prototype.slice = Array.prototype.slice;
}
sheetName = sheetName || 'sheet1';
var workbook = {
SheetNames: Array.isArray(sheetName) ? sheetName : [sheetName],
Sheets: {}
};
if (Array.isArray(sheetName)) {
sheetName.forEach((item, index) => {
workbook.Sheets[item] = sheet[index];
});
} else { workbook.Sheets[sheetName] = sheet; }
console.log(workbook,'workbook');
// 生成excel的配置项
var wopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
type: 'binary'
};
var wbout = XLSX.write(workbook, wopts);
return wbout;
}
function getDefaultStyle() {
let defaultStyle = {
fill: {
fgColor: {
rgb: ''
}
},
font: {
name: "Meiryo UI",
sz: 11,
color: {
rgb: ''
},
bold: true
},
border: {
top: {
style: 'thin',
color: {
auto: 1
}
},
left: {
style: 'thin',
color: {
auto: 1
}
},
right: {
style: 'thin',
color: {
auto: 1
}
},
bottom: {
style: 'thin',
color: {
auto: 1
}
}
},
alignment: {
/// 自动换行
wrapText: 1,
// 居中
horizontal: "center",
vertical: "center",
indent: 0
}
};
return defaultStyle;
}
function IEVersion() {
var userAgent = navigator.userAgent; //取得浏览器的userAgent字符串
var isIE = userAgent.indexOf("compatible") > -1 && userAgent.indexOf("MSIE") > -1; //判断是否IE<11浏览器
var isEdge = userAgent.indexOf("Edge") > -1 && !isIE; //判断是否IE的Edge浏览器
var isIE11 = userAgent.indexOf('Trident') > -1 && userAgent.indexOf("rv:11.0") > -1;
if (isIE) {
var reIE = new RegExp("MSIE (\\d+\\.\\d+);");
reIE.test(userAgent);
var fIEVersion = parseFloat(RegExp["$1"]);
if (fIEVersion == 7) {
return 7;
} else if (fIEVersion == 8) {
return 8;
} else if (fIEVersion == 9) {
return 9;
} else if (fIEVersion == 10) {
return 10;
} else {
return 6; //IE版本<=7
}
} else if (isEdge) {
return 'edge'; //edge
} else if (isIE11) {
return 11; //IE11
} else {
return -1; //不是ie浏览器
}
}
function openDownloadXLSXDialog(url, saveName: string) {
try {
if (typeof url == 'object' && url instanceof Blob) {
url = URL.createObjectURL(url); // 创建blob地址
}
var aLink = document.createElement('a');
aLink.href = url;
aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
var event;
if (window.MouseEvent) event = new MouseEvent('click');
else {
event = document.createEvent('MouseEvents');
event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
}
aLink.dispatchEvent(event);
} catch (e) {
throw (e);
}
}
export function uploadExcel(exportElement: [][] | any, fileName: string, config: IConfig) {
let ws = getSheetWithMyStyle(exportElement, config);
console.log(ws, 'worksheet数据');
downLoad(ws, fileName);
}
export default {
downLoad,
downLoadExcel,
getWorkSheetElement,
getSheetWithMyStyle,
transIndexToLetter,
getDefaultStyle,
sheetToWorkBook,
sheetToJSON,
};
Copy the code