• Luckysheet + Exceljs：H5实现Excel在线编辑、导入、导出及上传服务器的示例代码（完整版demo）

     

    创建xeditor.html 

    1. <!DOCTYPE html>
    2. <html>
    3.  
    4. <head>
    5.     <meta charset="UTF-8" />
    6.     <title>Hello World!</title>
    7.     <!-- <link rel='stylesheet' href='./luckysheet/plugins/css/pluginsCss.css' />
    8.     <link rel='stylesheet' href='./luckysheet/plugins/plugins.css' />
    9.     <link rel='stylesheet' href='./luckysheet/css/luckysheet.css' />
    10.     <link rel='stylesheet' href='./luckysheet/assets/iconfont/iconfont.css' />
    11.     <script src="./luckysheet/plugins/js/plugin.js"></script>
    12.     <script src="./luckysheet/luckysheet.umd.js"></script> -->
    13.  
    14.     <!-- 引入luckysheet，用于渲染表格 -->
    15.     <link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/plugins/css/pluginsCss.css' />
    16.     <link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/plugins/plugins.css' />
    17.     <link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/css/luckysheet.css' />
    18.     <link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/assets/iconfont/iconfont.css' />
    19.     <script src="https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/plugins/js/plugin.js"></script>
    20.     <script src="https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/luckysheet.umd.js"></script>
    21.  
    22.     <!-- 引入exceljs、FileSaver，用于luckysheet表格转xlsx文件 -->
    23.     <script src="https://cdn.bootcdn.net/ajax/libs/FileSaver.js/2.0.5/FileSaver.min.js"></script>
    24.     <script src="https://cdn.bootcdn.net/ajax/libs/exceljs/4.3.0/exceljs.js"></script>
    25.     <script>
    26.         $(function () {
    27.             //Configuration item
    28.             var options = {
    29.                 container: 'luckysheet', //luckysheet is the container id
    30.                 showinfobar: false,
    31.                 title: '在线表格', // 设定表格名称
    32.                 lang: 'zh' // 设定表格语言
    33.             }
    34.             luckysheet.create(options)
    35.         });
    36.     </script>
    37. </head>
    38.  
    39. <body>
    40.     <div id="lucky-mask-demo"
    41.         style="position: absolute;z-index: 1000000;left: 0px;top: 0px;bottom: 0px;right: 0px; background: rgba(255, 255, 255, 0.8); text-align: center;font-size: 40px;align-items:center;justify-content: center;display: none;">
    42.         download...</div>
    43.     <p style="text-align:center;"> <input style="font-size:16px;" type="file" id="Luckyexcel-demo-file"
    44.             name="Luckyexcel-demo-file" change="demoHandler" /> 或加载远程 xlsx 文件：
    45.         <select style="height: 27px;top: -2px;position: relative;" id="Luckyexcel-select-demo">
    46.             <option value="">选择网络文件</option>
    47.             <option value="https://minio.cnbabylon.com/public/luckysheet/money-manager-2.xlsx">Money Manager.xlsx</option>
    48.             <option value="https://minio.cnbabylon.com/public/luckysheet/Activity%20costs%20tracker.xlsx">Activity costs tracker.xlsx</option>
    49.             <option value="https://minio.cnbabylon.com/public/luckysheet/House%20cleaning%20checklist.xlsx">House cleaning checklist.xlsx</option>
    50.             <option value="https://minio.cnbabylon.com/public/luckysheet/Student%20assignment%20planner.xlsx">Student assignment planner.xlsx</option>
    51.             <option value="https://minio.cnbabylon.com/public/luckysheet/Credit%20card%20tracker.xlsx">Credit card tracker.xlsx</option>
    52.             <option value="https://minio.cnbabylon.com/public/luckysheet/Blue%20timesheet.xlsx">Blue timesheet.xlsx</option>
    53.             <option value="https://minio.cnbabylon.com/public/luckysheet/Student%20calendar%20%28Mon%29.xlsx">Student calendar (Mon).xlsx</option>
    54.             <option value="https://minio.cnbabylon.com/public/luckysheet/Blue%20mileage%20and%20expense%20report.xlsx">Blue mileage and expense report.xlsx</option>
    55.         </select>
    56.         <a href="javascript:void(0)" id="Luckyexcel-downlod-file">下载 xlsx 文件</a>
    57.         <a href="javascript:void(0)" id="Luckyexcel-upload-file">上传到服务器</a>
    58.     </p>
    59.     <div id="luckysheet"
    60.         style="margin:0px;padding:0px;position:absolute;width:100%;left: 0px;top: 50px;bottom: 0px;outline: none;">
    61.     </div>
    62.     <!-- <script src="luckyexcel.umd.js"></script> -->
    63.     <script src="https://cdn.jsdelivr.net/npm/luckyexcel/dist/luckyexcel.umd.js"></script>
    64.  
    65.     <!-- <script type="module">
    66.             import l from './luckyexcel.js';
    67.             console.info('=====',l)
    68.             // window.onload = () => {
    69.             //     let upload = document.getElementById("file");
    70.             //     upload.addEventListener("change", function(evt){
    71.             //         var files = evt.target.files;   
    72.             //         importFile(files[0]);
    73.             //     });
    74.             // }
    75.         </script> -->
    76.     <script>
    77.         let fullName=''; // 正在编辑的Excel文件名，包含后缀名
    78.         function demoHandler() {
    79.             let upload = document.getElementById("Luckyexcel-demo-file");
    80.             let selectADemo = document.getElementById("Luckyexcel-select-demo");
    81.             let downlodDemo = document.getElementById("Luckyexcel-downlod-file");
    82.             let uploadDemo = document.getElementById("Luckyexcel-upload-file");
    83.             let mask = document.getElementById("lucky-mask-demo");
    84.             if (upload) {
    85.  
    86.                 window.onload = () => {
    87.  
    88.                     upload.addEventListener("change", function (evt) {
    89.                         var files = evt.target.files;
    90.                         if (files == null || files.length == 0) {
    91.                             alert("No files wait for import");
    92.                             return;
    93.                         }
    94.  
    95.                         let name = files[0].name;
    96.                         let suffixArr = name.split("."), suffix = suffixArr[suffixArr.length - 1];
    97.                         if (suffix != "xlsx") {
    98.                             alert("Currently only supports the import of xlsx files");
    99.                             return;
    100.                         }
    101.                         fullName=name;
    102.                         LuckyExcel.transformExcelToLucky(files[0], function (exportJson, luckysheetfile) {
    103.  
    104.                             if (exportJson.sheets == null || exportJson.sheets.length == 0) {
    105.                                 alert("Failed to read the content of the excel file, currently does not support xls files!");
    106.                                 return;
    107.                             }
    108.                             window.luckysheet.destroy();
    109.  
    110.                             window.luckysheet.create({
    111.                                 container: 'luckysheet', //luckysheet is the container id
    112.                                 showinfobar: false,
    113.                                 data: exportJson.sheets,
    114.                                 title: '在线表格',
    115.                                 userInfo: exportJson.info.name.creator,
    116.                                 lang: 'zh'
    117.                             });
    118.                         });
    119.                     });
    120.  
    121.                     selectADemo.addEventListener("change", function (evt) {
    122.                         var obj = selectADemo;
    123.                         var index = obj.selectedIndex;
    124.                         var value = obj.options[index].value;
    125.                         var name = obj.options[index].innerHTML;
    126.                         if (value == "") {
    127.                             return;
    128.                         }
    129.                         fullName=name;
    130.                         mask.style.display = "flex";
    131.                         LuckyExcel.transformExcelToLuckyByUrl(value, name, function (exportJson, luckysheetfile) {
    132.  
    133.                             if (exportJson.sheets == null || exportJson.sheets.length == 0) {
    134.                                 alert("Failed to read the content of the excel file, currently does not support xls files!");
    135.                                 return;
    136.                             }
    137.                             console.log(exportJson, luckysheetfile);
    138.                             mask.style.display = "none";
    139.                             window.luckysheet.destroy();
    140.  
    141.                             window.luckysheet.create({
    142.                                 container: 'luckysheet', //luckysheet is the container id
    143.                                 showinfobar: false,
    144.                                 data: exportJson.sheets,
    145.                                 title: '在线表格',
    146.                                 userInfo: exportJson.info.name.creator,
    147.                                 lang: 'zh'
    148.                             });
    149.                         });
    150.                     });
    151.                     
    152.                     uploadDemo.addEventListener("click", function (evt) {
    153.                         uploadExcel(window.luckysheet.getAllSheets(), fullName)  // 上传到服务器
    154.                     });
    155.  
    156.                     downlodDemo.addEventListener("click", function (evt) {
    157.                         exportExcelFront(window.luckysheet.getAllSheets(), fullName) // 下载Excel
    158.  
    159.                         // var obj = selectADemo;
    160.                         // var index = obj.selectedIndex;
    161.                         // var value = obj.options[index].value;
    162.                         // if (value.length == 0) {
    163.                         //     alert("Please select a demo file");
    164.                         //     return;
    165.                         // }
    166.                         // var elemIF = document.getElementById("Lucky-download-frame");
    167.                         // if (elemIF == null) {
    168.                         //     elemIF = document.createElement("iframe");
    169.                         //     elemIF.style.display = "none";
    170.                         //     elemIF.id = "Lucky-download-frame";
    171.                         //     document.body.appendChild(elemIF);
    172.                         // }
    173.                         // elemIF.src = value;
    174.  
    175.                     });
    176.                 }
    177.             }
    178.         }
    179.         demoHandler();
    180.  
    181.         /**
    182.          * 上传到服务器
    183.          * @param luckysheet    -> luckysheet的所有sheet
    184.          * @param name          -> 保存文件名（如：a.xlsx）
    185.          * @param excelType     -> office/wps
    186.          */
    187.         var uploadExcel = function(luckysheet, name, excelType) {
    188.             // 1.创建工作簿，可以为工作簿添加属性
    189.             const workbook = new ExcelJS.Workbook()
    190.             // 2.创建表格，第二个参数可以配置创建什么样的工作表
    191.             luckysheet.forEach(function (table) {
    192.                 // debugger
    193.                 if (table.data.length === 0) return true
    194.                 const worksheet = workbook.addWorksheet(table.name)
    195.                 const merge = (table.config && table.config.merge) || {}        //合并单元格
    196.                 const borderInfo = (table.config && table.config.borderInfo) || {}      //边框
    197.                 const columnWidth = (table.config && table.config.columnlen) || {}    //列宽
    198.                 const rowHeight = (table.config && table.config.rowlen) || {}      //行高
    199.                 const frozen = table.frozen || {}       //冻结
    200.                 const rowhidden = (table.config && table.config.rowhidden) || {}    //行隐藏
    201.                 const colhidden = (table.config && table.config.colhidden) || {}    //列隐藏
    202.                 const filterSelect = table.filter_select || {}    //筛选
    203.                 const images = table.images || {}   //图片
    204.                 // console.log(table)
    205.                 const hide = table.hide;    //工作表 sheet 1隐藏
    206.                 if (hide === 1) {
    207.                     // 隐藏工作表
    208.                     worksheet.state = 'hidden';
    209.                 }
    210.                 setStyleAndValue(table.data, worksheet)
    211.                 setMerge(merge, worksheet)
    212.                 setBorder(borderInfo, worksheet)
    213.                 setImages(images, worksheet, workbook)
    214.                 setColumnWidth(columnWidth, worksheet)
    215.                 //行高设置50导出后在ms-excel中打开显示25，在wps-excel中打开显示50这个bug不会修复
    216.                 setRowHeight(rowHeight, worksheet, excelType)
    217.                 setFrozen(frozen, worksheet)
    218.                 setRowHidden(rowhidden, worksheet)
    219.                 setColHidden(colhidden, worksheet)
    220.                 setFilter(filterSelect, worksheet)
    221.                 return true
    222.             })
    223.  
    224.             // 4.写入 buffer
    225.             const buffer = workbook.xlsx.writeBuffer().then(data => {
    226.                 const blob = new Blob([data], {
    227.                     type: 'application/vnd.ms-excel;charset=utf-8'
    228.                 })
    229.  
    230.                 // 创建FormData对象
    231.                 const formData = new FormData();
    232.                 formData.append('file', blob, `${name}`);
    233.                 // 创建XMLHttpRequest对象
    234.                 const xhr = new XMLHttpRequest();
    235.                 // 配置请求
    236.                 xhr.open('POST', 'http://127.0.0.1:3000/upload', true);
    237.                 // 设置请求完成的回调函数
    238.                 xhr.onload = function () {
    239.                     if (xhr.status === 200) {
    240.                         alert('已上传成功！')
    241.                         console.log('Success:', xhr.responseText);
    242.                     } else {
    243.                         alert('上传失败！'+xhr.statusText)
    244.                         console.error('Error:', xhr.statusText);
    245.                     }
    246.                 };
    247.                 // 设置请求失败的回调函数
    248.                 xhr.onerror = function () {
    249.                     console.error('Network error occurred');
    250.                 };
    251.                 // 设置请求超时的回调函数（可选）
    252.                 xhr.ontimeout = function (e) {
    253.                     console.error('Request timed out');
    254.                 };
    255.                 // 设置请求超时时间（可选）
    256.                 xhr.timeout = 5000; // 5秒
    257.                 // 发送FormData对象
    258.                 xhr.send(formData);
    259.                 // 设置请求头（可选，某些浏览器可能不需要）
    260.                 xhr.setRequestHeader('X-Requested-With', 'XMLHttpRequest');
    261.             })
    262.  
    263.             return buffer
    264.         }
    265.  
    266.         /**
    267.          * 下载Excel
    268.          * @param luckysheet    -> luckysheet的所有sheet
    269.          * @param name          -> 保存文件名（如：a.xlsx）
    270.          * @param excelType     -> office/wps
    271.          */
    272.         var exportExcelFront = function(luckysheet, name, excelType) {
    273.             // 1.创建工作簿，可以为工作簿添加属性
    274.             const workbook = new ExcelJS.Workbook()
    275.             // 2.创建表格，第二个参数可以配置创建什么样的工作表
    276.             luckysheet.forEach(function (table) {
    277.                 // debugger
    278.                 if (table.data.length === 0) return true
    279.                 const worksheet = workbook.addWorksheet(table.name)
    280.                 const merge = (table.config && table.config.merge) || {}        //合并单元格
    281.                 const borderInfo = (table.config && table.config.borderInfo) || {}      //边框
    282.                 const columnWidth = (table.config && table.config.columnlen) || {}    //列宽
    283.                 const rowHeight = (table.config && table.config.rowlen) || {}      //行高
    284.                 const frozen = table.frozen || {}       //冻结
    285.                 const rowhidden = (table.config && table.config.rowhidden) || {}    //行隐藏
    286.                 const colhidden = (table.config && table.config.colhidden) || {}    //列隐藏
    287.                 const filterSelect = table.filter_select || {}    //筛选
    288.                 const images = table.images || {}   //图片
    289.                 // console.log(table)
    290.                 const hide = table.hide;    //工作表 sheet 1隐藏
    291.                 if (hide === 1) {
    292.                     // 隐藏工作表
    293.                     worksheet.state = 'hidden';
    294.                 }
    295.                 setStyleAndValue(table.data, worksheet)
    296.                 setMerge(merge, worksheet)
    297.                 setBorder(borderInfo, worksheet)
    298.                 setImages(images, worksheet, workbook)
    299.                 setColumnWidth(columnWidth, worksheet)
    300.                 //行高设置50导出后在ms-excel中打开显示25，在wps-excel中打开显示50这个bug不会修复
    301.                 setRowHeight(rowHeight, worksheet, excelType)
    302.                 setFrozen(frozen, worksheet)
    303.                 setRowHidden(rowhidden, worksheet)
    304.                 setColHidden(colhidden, worksheet)
    305.                 setFilter(filterSelect, worksheet)
    306.                 return true
    307.             })
    308.  
    309.             // 4.写入 buffer
    310.             const buffer = workbook.xlsx.writeBuffer().then(data => {
    311.                 const blob = new Blob([data], {
    312.                     type: 'application/vnd.ms-excel;charset=utf-8'
    313.                 })
    314.  
    315.                 // 浏览器下载文件的示例代码
    316.                 console.log("导出成功！")
    317.                 saveAs(blob, `${name}`)
    318.             })
    319.  
    320.             return buffer
    321.         }
    322.  
    323.  
    324.         /**
    325.          * 列宽
    326.          * @param columnWidth
    327.          * @param worksheet
    328.          */
    329.         var setColumnWidth = function (columnWidth, worksheet) {
    330.             for (let key in columnWidth) {
    331.                 worksheet.getColumn(parseInt(key) + 1).width = columnWidth[key] / 7.5
    332.             }
    333.         }
    334.  
    335.         /**
    336.          * 行高
    337.          * @param rowHeight
    338.          * @param worksheet
    339.          * @param excelType
    340.          */
    341.         var setRowHeight = function (rowHeight, worksheet, excelType) {
    342.             //导出的文件用wps打开和用excel打开显示的行高大一倍
    343.             if (excelType == "wps") {
    344.                 for (let key in rowHeight) {
    345.                     worksheet.getRow(parseInt(key) + 1).height = rowHeight[key] * 0.75
    346.                 }
    347.             }
    348.             if (excelType == "office" || excelType == undefined) {
    349.                 for (let key in rowHeight) {
    350.                     worksheet.getRow(parseInt(key) + 1).height = rowHeight[key] * 1.5
    351.                 }
    352.             }
    353.         }
    354.  
    355.         /**
    356.          * 合并单元格
    357.          * @param luckyMerge
    358.          * @param worksheet
    359.          */
    360.         var setMerge = function (luckyMerge = {}, worksheet) {
    361.             const mergearr = Object.values(luckyMerge)
    362.             mergearr.forEach(function (elem) {
    363.                 // elem格式：{r: 0, c: 0, rs: 1, cs: 2}
    364.                 // 按开始行，开始列，结束行，结束列合并（相当于 K10:M12）
    365.                 worksheet.mergeCells(
    366.                     elem.r + 1,
    367.                     elem.c + 1,
    368.                     elem.r + elem.rs,
    369.                     elem.c + elem.cs
    370.                 )
    371.             })
    372.         }
    373.  
    374.         /**
    375.          * 设置边框
    376.          * @param luckyBorderInfo
    377.          * @param worksheet
    378.          */
    379.         var setBorder = function (luckyBorderInfo, worksheet) {
    380.             if (!Array.isArray(luckyBorderInfo)) return
    381.  
    382.             //合并边框信息
    383.             var mergeCellBorder = function (border1, border2) {
    384.                 if (undefined === border1 || Object.keys(border1).length === 0) return border2;
    385.                 return Object.assign({}, border1, border2)
    386.             }
    387.  
    388.             // console.log('luckyBorderInfo', luckyBorderInfo)
    389.             luckyBorderInfo.forEach(function (elem) {
    390.                 // 现在只兼容到borderType 为range的情况
    391.                 // console.log('ele', elem)
    392.                 if (elem.rangeType === 'range') {
    393.                     let border = borderConvert(elem.borderType, elem.style, elem.color)
    394.                     let rang = elem.range[0]
    395.                     let row = rang.row
    396.                     let column = rang.column
    397.  
    398.                     let rowBegin = row[0]
    399.                     let rowEnd = row[1]
    400.                     let colBegin = column[0]
    401.                     let colEnd = column[1]
    402.                     //处理外边框的情况 没有直接对应的外边框 需要转换成上下左右
    403.                     if (border.all) {//全部边框
    404.                         let b = border.all
    405.                         for (let i = row[0] + 1; i <= row[1] + 1; i++) {
    406.                             for (let y = column[0] + 1; y <= column[1] + 1; y++) {
    407.                                 let border = {}
    408.                                 border['top'] = b;
    409.                                 border['bottom'] = b;
    410.                                 border['left'] = b;
    411.                                 border['right'] = b;
    412.                                 worksheet.getCell(i, y).border = border
    413.                                 // console.log(i, y, worksheet.getCell(i, y).border)
    414.                             }
    415.                         }
    416.                     } else if (border.top) {//上边框
    417.                         let b = border.top
    418.                         let i = row[0] + 1;
    419.                         for (let y = column[0] + 1; y <= column[1] + 1; y++) {
    420.                             let border = {}
    421.                             border['top'] = b;
    422.                             worksheet.getCell(i, y).border = border
    423.                             // console.log(i, y, worksheet.getCell(i, y).border)
    424.                         }
    425.                     } else if (border.right) {//右边框
    426.                         let b = border.right
    427.                         for (let i = row[0] + 1; i <= row[1] + 1; i++) {
    428.                             let y = column[1] + 1;
    429.                             let border = {}
    430.                             border['right'] = b;
    431.                             worksheet.getCell(i, y).border = border
    432.                             // console.log(i, y, worksheet.getCell(i, y).border)
    433.                         }
    434.                     } else if (border.bottom) {//下边框
    435.                         let b = border.bottom
    436.                         let i = row[1] + 1;
    437.                         for (let y = column[0] + 1; y <= column[1] + 1; y++) {
    438.                             let border = {}
    439.  
    440.                             border['bottom'] = b;
    441.                             worksheet.getCell(i, y).border = border
    442.                             // console.log(i, y, worksheet.getCell(i, y).border)
    443.                         }
    444.                     } else if (border.left) {//左边框
    445.                         let b = border.left
    446.                         for (let i = row[0] + 1; i <= row[1] + 1; i++) {
    447.                             let y = column[0] + 1;
    448.                             let border = {}
    449.                             border['left'] = b;
    450.                             worksheet.getCell(i, y).border = border
    451.                             // console.log(i, y, worksheet.getCell(i, y).border)
    452.                         }
    453.                     } else if (border.outside) {//外边框
    454.                         let b = border.outside
    455.                         for (let i = row[0] + 1; i <= row[1] + 1; i++) {
    456.                             for (let y = column[0] + 1; y <= column[1] + 1; y++) {
    457.                                 let border = {}
    458.                                 if (i === rowBegin + 1) {
    459.                                     border['top'] = b
    460.                                 }
    461.                                 if (i === rowEnd + 1) {
    462.                                     border['bottom'] = b
    463.                                 }
    464.                                 if (y === colBegin + 1) {
    465.                                     border['left'] = b
    466.                                 }
    467.                                 if (y === colEnd + 1) {
    468.                                     border['right'] = b
    469.                                 }
    470.                                 let border1 = worksheet.getCell(i, y).border
    471.                                 worksheet.getCell(i, y).border = mergeCellBorder(border1, border)
    472.                                 // console.log(i, y, worksheet.getCell(i, y).border)
    473.                             }
    474.                         }
    475.                     } else if (border.inside) {//内边框
    476.                         let b = border.inside
    477.                         for (let i = row[0] + 1; i <= row[1] + 1; i++) {
    478.                             for (let y = column[0] + 1; y <= column[1] + 1; y++) {
    479.                                 let border = {}
    480.                                 if (i !== rowBegin + 1) {
    481.                                     border['top'] = b
    482.                                 }
    483.                                 if (i !== rowEnd + 1) {
    484.                                     border['bottom'] = b
    485.                                 }
    486.                                 if (y !== colBegin + 1) {
    487.                                     border['left'] = b
    488.                                 }
    489.                                 if (y !== colEnd + 1) {
    490.                                     border['right'] = b
    491.                                 }
    492.                                 let border1 = worksheet.getCell(i, y).border
    493.                                 worksheet.getCell(i, y).border = mergeCellBorder(border1, border)
    494.                                 // console.log(i, y, worksheet.getCell(i, y).border)
    495.                             }
    496.                         }
    497.                     } else if (border.horizontal) {//内侧水平边框
    498.                         let b = border.horizontal
    499.                         for (let i = row[0] + 1; i <= row[1] + 1; i++) {
    500.                             for (let y = column[0] + 1; y <= column[1] + 1; y++) {
    501.                                 let border = {}
    502.                                 if (i === rowBegin + 1) {
    503.                                     border['bottom'] = b
    504.                                 } else if (i === rowEnd + 1) {
    505.                                     border['top'] = b
    506.                                 } else {
    507.                                     border['top'] = b
    508.                                     border['bottom'] = b
    509.                                 }
    510.                                 let border1 = worksheet.getCell(i, y).border
    511.                                 worksheet.getCell(i, y).border = mergeCellBorder(border1, border)
    512.                                 // console.log(i, y, worksheet.getCell(i, y).border)
    513.                             }
    514.                         }
    515.                     } else if (border.vertical) {//内侧垂直边框
    516.                         let b = border.vertical
    517.                         for (let i = row[0] + 1; i <= row[1] + 1; i++) {
    518.                             for (let y = column[0] + 1; y <= column[1] + 1; y++) {
    519.                                 let border = {}
    520.                                 if (y === colBegin + 1) {
    521.                                     border['right'] = b
    522.                                 } else if (y === colEnd + 1) {
    523.                                     border['left'] = b
    524.                                 } else {
    525.                                     border['left'] = b
    526.                                     border['right'] = b
    527.                                 }
    528.                                 let border1 = worksheet.getCell(i, y).border
    529.                                 worksheet.getCell(i, y).border = mergeCellBorder(border1, border)
    530.                                 // console.log(i, y, worksheet.getCell(i, y).border)
    531.                             }
    532.                         }
    533.                     } else if (border.none) {//当luckysheet边框为border-none的时候表示没有边框 则将对应的单元格border清空
    534.                         for (let i = row[0] + 1; i <= row[1] + 1; i++) {
    535.                             for (let y = column[0] + 1; y <= column[1] + 1; y++) {
    536.                                 worksheet.getCell(i, y).border = {}
    537.                                 // console.log(i, y, worksheet.getCell(i, y).border)
    538.                             }
    539.                         }
    540.                     }
    541.                 }
    542.                 if (elem.rangeType === 'cell') {
    543.                     // col_index: 2
    544.                     // row_index: 1
    545.                     // b: {
    546.                     //   color: '#d0d4e3'
    547.                     //   style: 1
    548.                     // }
    549.                     const { col_index, row_index } = elem.value
    550.                     const borderData = Object.assign({}, elem.value)
    551.                     delete borderData.col_index
    552.                     delete borderData.row_index
    553.                     let border = addborderToCell(borderData, row_index, col_index)
    554.                     let border1 = worksheet.getCell(row_index + 1, col_index + 1).border;
    555.                     worksheet.getCell(row_index + 1, col_index + 1).border = mergeCellBorder(border1, border)
    556.                     // console.log(row_index + 1, col_index + 1, worksheet.getCell(row_index + 1, col_index + 1).border)
    557.                 }
    558.             })
    559.         }
    560.  
    561.  
    562.         /**
    563.          * 设置带样式的值
    564.          * @param cellArr
    565.          * @param worksheet
    566.          */
    567.         var setStyleAndValue = function (cellArr, worksheet) {
    568.             if (!Array.isArray(cellArr)) return
    569.             cellArr.forEach(function (row, rowid) {
    570.                 row.every(function (cell, columnid) {
    571.                     if (!cell) return true
    572.                     let fill = fillConvert(cell.bg)
    573.  
    574.                     let font = fontConvert(
    575.                         cell.ff,
    576.                         cell.fc,
    577.                         cell.bl,
    578.                         cell.it,
    579.                         cell.fs,
    580.                         cell.cl,
    581.                         cell.un
    582.                     )
    583.                     let alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr)
    584.                     let value = ''
    585.  
    586.                     if (cell.f) {
    587.                         value = { formula: cell.f, result: cell.v }
    588.                     } else if (!cell.v && cell.ct && cell.ct.s) {
    589.                         // xls转为xlsx之后，内部存在不同的格式，都会进到富文本里，即值不存在与cell.v，而是存在于cell.ct.s之后
    590.                         let richText = [];
    591.                         let cts = cell.ct.s
    592.                         for (let i = 0; i < cts.length; i++) {
    593.                             let rt = {
    594.                                 text: cts[i].v,
    595.                                 font: fontConvert(cts[i].ff, cts[i].fc, cts[i].bl, cts[i].it, cts[i].fs, cts[i].cl, cts[i].un)
    596.                             }
    597.                             richText.push(rt)
    598.                         }
    599.                         value = {
    600.                             richText: richText
    601.                         };
    602.  
    603.                     } else {
    604.                         //设置值为数字格式
    605.                         if (cell.v !== undefined && cell.v !== '') {
    606.                             var v = +cell.v;
    607.                             if (isNaN(v)) v = cell.v
    608.                             value = v
    609.                         }
    610.                     }
    611.                     //  style 填入到_value中可以实现填充色
    612.                     let letter = createCellPos(columnid)
    613.                     let target = worksheet.getCell(letter + (rowid + 1))
    614.                     // console.log('1233', letter + (rowid + 1))
    615.                     for (const key in fill) {
    616.                         target.fill = fill
    617.                         break
    618.                     }
    619.                     target.font = font
    620.                     target.alignment = alignment
    621.                     target.value = value
    622.  
    623.                     try {
    624.                         //设置单元格格式
    625.                         target.numFmt = cell.ct.fa;
    626.                     } catch (e) {
    627.                         console.warn(e)
    628.                     }
    629.  
    630.                     return true
    631.                 })
    632.             })
    633.         }
    634.  
    635.         /**
    636.          * 设置图片
    637.          * @param images
    638.          * @param worksheet
    639.          * @param workbook
    640.          */
    641.         var setImages = function (images, worksheet, workbook) {
    642.             if (typeof images != "object") return;
    643.             for (let key in images) {
    644.                 // console.log(images[key]);
    645.                 // "data:image/png;base64,iVBORw0KG..."
    646.                 // 通过 base64  将图像添加到工作簿
    647.                 const myBase64Image = images[key].src;
    648.                 //位置
    649.                 const tl = { col: images[key].default.left / 72, row: images[key].default.top / 19 }
    650.                 // 大小
    651.                 const ext = { width: images[key].default.width, height: images[key].default.height }
    652.                 const imageId = workbook.addImage({
    653.                     base64: myBase64Image,
    654.                     //extension: 'png',
    655.                 });
    656.                 worksheet.addImage(imageId, {
    657.                     tl: tl,
    658.                     ext: ext
    659.                 });
    660.             }
    661.         }
    662.  
    663.         /**
    664.          * 冻结行列
    665.          * @param frozen
    666.          * @param worksheet
    667.          */
    668.         var setFrozen = function (frozen = {}, worksheet) {
    669.             switch (frozen.type) {
    670.                 // 冻结首行
    671.                 case 'row': {
    672.                     worksheet.views = [
    673.                         { state: 'frozen', xSplit: 0, ySplit: 1 }
    674.                     ];
    675.                     break
    676.                 }
    677.                 // 冻结首列
    678.                 case 'column': {
    679.                     worksheet.views = [
    680.                         { state: 'frozen', xSplit: 1, ySplit: 0 }
    681.                     ];
    682.                     break
    683.                 }
    684.                 // 冻结行列
    685.                 case 'both': {
    686.                     worksheet.views = [
    687.                         { state: 'frozen', xSplit: 1, ySplit: 1 }
    688.                     ];
    689.                     break
    690.                 }
    691.                 // 冻结行到选区
    692.                 case 'rangeRow': {
    693.                     let row = frozen.range.row_focus + 1
    694.                     worksheet.views = [
    695.                         { state: 'frozen', xSplit: 0, ySplit: row }
    696.                     ];
    697.                     break
    698.                 }
    699.                 // 冻结列到选区
    700.                 case 'rangeColumn': {
    701.                     let column = frozen.range.column_focus + 1
    702.                     worksheet.views = [
    703.                         { state: 'frozen', xSplit: column, ySplit: 0 }
    704.                     ];
    705.                     break
    706.                 }
    707.                 // 冻结行列到选区
    708.                 case 'rangeBoth': {
    709.                     let row = frozen.range.row_focus + 1
    710.                     let column = frozen.range.column_focus + 1
    711.                     worksheet.views = [
    712.                         { state: 'frozen', xSplit: column, ySplit: row }
    713.                     ];
    714.                 }
    715.  
    716.             }
    717.  
    718.         }
    719.  
    720.         /**
    721.          * 行隐藏
    722.          * @param rowhidden
    723.          * @param worksheet
    724.          */
    725.         var setRowHidden = function (rowhidden = {}, worksheet) {
    726.             for (const key in rowhidden) {
    727.                 //如果当前行没有内容则隐藏不生效
    728.                 const row = worksheet.getRow(parseInt(key) + 1)
    729.                 row.hidden = true;
    730.             }
    731.         }
    732.  
    733.         /**
    734.          * 列隐藏
    735.          * @param colhidden
    736.          * @param worksheet
    737.          */
    738.         var setColHidden = function (colhidden = {}, worksheet) {
    739.             for (const key in colhidden) {
    740.                 const column = worksheet.getColumn(parseInt(key) + 1)
    741.                 column.hidden = true;
    742.             }
    743.         }
    744.  
    745.         /**
    746.          * 自动筛选器
    747.          * @param filter
    748.          * @param worksheet
    749.          */
    750.         var setFilter = function (filter = {}, worksheet) {
    751.             if (Object.keys(filter).length === 0) return
    752.             const from = {
    753.                 row: filter.row[0] + 1,
    754.                 column: filter.column[0] + 1
    755.             }
    756.  
    757.             const to = {
    758.                 row: filter.row[1] + 1,
    759.                 column: filter.column[1] + 1
    760.             }
    761.  
    762.             worksheet.autoFilter = {
    763.                 from: from,
    764.                 to: to
    765.             }
    766.  
    767.         }
    768.  
    769.         var fillConvert = function (bg) {
    770.             if (!bg) {
    771.                 return {}
    772.             }
    773.             // const bgc = bg.replace('#', '')
    774.             let fill = {
    775.                 type: 'pattern',
    776.                 pattern: 'solid',
    777.                 fgColor: { argb: bg.startsWith("#") ? bg.replace('#', '') : colorRGBtoHex(bg).replace("#", "") },
    778.             }
    779.             return fill;
    780.         }
    781.  
    782.         var fontConvert = function (
    783.             ff = 0,
    784.             fc = '#000000',
    785.             bl = 0,
    786.             it = 0,
    787.             fs = 10,
    788.             cl = 0,
    789.             ul = 0
    790.         ) {
    791.             // luckysheet：ff(样式), fc(颜色), bl(粗体), it(斜体), fs(大小), cl(删除线), ul(下划线)
    792.             const luckyToExcel = {
    793.                 0: '微软雅黑',
    794.                 1: '宋体（Song）',
    795.                 2: '黑体（ST Heiti）',
    796.                 3: '楷体（ST Kaiti）',
    797.                 4: '仿宋（ST FangSong）',
    798.                 5: '新宋体（ST Song）',
    799.                 6: '华文新魏',
    800.                 7: '华文行楷',
    801.                 8: '华文隶书',
    802.                 9: 'Arial',
    803.                 10: 'Times New Roman ',
    804.                 11: 'Tahoma ',
    805.                 12: 'Verdana',
    806.                 num2bl: function (num) {
    807.                     return num !== 0
    808.                 }
    809.             }
    810.             // 出现Bug，导入的时候ff为luckyToExcel的val
    811.  
    812.             let font = {
    813.                 name: typeof ff === 'number' ? luckyToExcel[ff] : ff,
    814.                 family: 1,
    815.                 size: fs,
    816.                 color: { argb: fc.startsWith("#") ? fc.replace('#', '') : colorRGBtoHex(fc).replace("#", "") },
    817.                 bold: luckyToExcel.num2bl(bl),
    818.                 italic: luckyToExcel.num2bl(it),
    819.                 underline: luckyToExcel.num2bl(ul),
    820.                 strike: luckyToExcel.num2bl(cl)
    821.             }
    822.  
    823.             return font
    824.         }
    825.  
    826.         var alignmentConvert = function (
    827.             vt = 'default',
    828.             ht = 'default',
    829.             tb = 'default',
    830.             tr = 'default'
    831.         ) {
    832.             // luckysheet:vt(垂直), ht(水平), tb(换行), tr(旋转)
    833.             const luckyToExcel = {
    834.                 vertical: {
    835.                     0: 'middle',
    836.                     1: 'top',
    837.                     2: 'bottom',
    838.                     default: 'middle'
    839.                 },
    840.                 horizontal: {
    841.                     0: 'center',
    842.                     1: 'left',
    843.                     2: 'right',
    844.                     default: 'center'
    845.                 },
    846.                 wrapText: {
    847.                     0: false,
    848.                     1: false,
    849.                     2: true,
    850.                     default: false
    851.                 },
    852.                 textRotation: {
    853.                     0: 0,
    854.                     1: 45,
    855.                     2: -45,
    856.                     3: 'vertical',
    857.                     4: 90,
    858.                     5: -90,
    859.                     default: 0
    860.                 }
    861.             }
    862.  
    863.             let alignment = {
    864.                 vertical: luckyToExcel.vertical[vt],
    865.                 horizontal: luckyToExcel.horizontal[ht],
    866.                 wrapText: luckyToExcel.wrapText[tb],
    867.                 textRotation: luckyToExcel.textRotation[tr]
    868.             }
    869.             return alignment
    870.         }
    871.  
    872.         var borderConvert = function (borderType, style = 1, color = '#000') {
    873.             // 对应luckysheet的config中borderinfo的的参数
    874.             if (!borderType) {
    875.                 return {}
    876.             }
    877.             const luckyToExcel = {
    878.                 type: {
    879.                     'border-all': 'all',
    880.                     'border-top': 'top',
    881.                     'border-right': 'right',
    882.                     'border-bottom': 'bottom',
    883.                     'border-left': 'left',
    884.                     'border-outside': 'outside',
    885.                     'border-inside': 'inside',
    886.                     'border-horizontal': 'horizontal',
    887.                     'border-vertical': 'vertical',
    888.                     'border-none': 'none',
    889.                 },
    890.                 style: {
    891.                     0: 'none',
    892.                     1: 'thin',
    893.                     2: 'hair',
    894.                     3: 'dotted',
    895.                     4: 'dashDot', // 'Dashed',
    896.                     5: 'dashDot',
    897.                     6: 'dashDotDot',
    898.                     7: 'double',
    899.                     8: 'medium',
    900.                     9: 'mediumDashed',
    901.                     10: 'mediumDashDot',
    902.                     11: 'mediumDashDotDot',
    903.                     12: 'slantDashDot',
    904.                     13: 'thick'
    905.                 }
    906.             }
    907.             let border = {}
    908.             border[luckyToExcel.type[borderType]] = {
    909.                 style: luckyToExcel.style[style],
    910.                 color: { argb: color.replace('#', '') }
    911.             }
    912.             return border
    913.         }
    914.  
    915.         function addborderToCell(borders, row_index, col_index) {
    916.             let border = {}
    917.             const luckyExcel = {
    918.                 type: {
    919.                     l: 'left',
    920.                     r: 'right',
    921.                     b: 'bottom',
    922.                     t: 'top'
    923.                 },
    924.                 style: {
    925.                     0: 'none',
    926.                     1: 'thin',
    927.                     2: 'hair',
    928.                     3: 'dotted',
    929.                     4: 'dashDot', // 'Dashed',
    930.                     5: 'dashDot',
    931.                     6: 'dashDotDot',
    932.                     7: 'double',
    933.                     8: 'medium',
    934.                     9: 'mediumDashed',
    935.                     10: 'mediumDashDot',
    936.                     11: 'mediumDashDotDot',
    937.                     12: 'slantDashDot',
    938.                     13: 'thick'
    939.                 }
    940.             }
    941.             // console.log('borders', borders)
    942.             for (const bor in borders) {
    943.                 // console.log(bor)
    944.                 if (borders[bor].color.indexOf('rgb') === -1) {
    945.                     border[luckyExcel.type[bor]] = {
    946.                         style: luckyExcel.style[borders[bor].style],
    947.                         color: { argb: borders[bor].color.replace('#', '') }
    948.                     }
    949.                 } else {
    950.                     border[luckyExcel.type[bor]] = {
    951.                         style: luckyExcel.style[borders[bor].style],
    952.                         color: { argb: borders[bor].color }
    953.                     }
    954.                 }
    955.             }
    956.  
    957.             return border
    958.         }
    959.  
    960.         function createCellPos(n) {
    961.             let ordA = 'A'.charCodeAt(0)
    962.  
    963.             let ordZ = 'Z'.charCodeAt(0)
    964.             let len = ordZ - ordA + 1
    965.             let s = ''
    966.             while (n >= 0) {
    967.                 s = String.fromCharCode((n % len) + ordA) + s
    968.  
    969.                 n = Math.floor(n / len) - 1
    970.             }
    971.             return s
    972.         }
    973.  
    974.         //rgb(255,255,255)转16进制 #ffffff
    975.         function colorRGBtoHex(color) {
    976.             color = color.replace("rgb", "").replace("(", "").replace(")", "")
    977.             var rgb = color.split(',');
    978.             var r = parseInt(rgb[0]);
    979.             var g = parseInt(rgb[1]);
    980.             var b = parseInt(rgb[2]);
    981.             return "#" + ((1 << 24) + (r << 16) + (g << 8) + b).toString(16).slice(1);
    982.         }
    983.     </script>
    984. </body>
    985.  
    986. </html>

    Node.js 接收前端上传文件的示例代码

    首先，确保您已经安装了multer和express：

    npm install multer express


    然后，您可以使用以下代码来设置一个Express服务器，该服务器能够接收Blob格式的文件上传：

    在项目一级目录，创建uploads文件夹和server.js

    1. const express = require('express');
    2. const multer = require('multer');
    3. const app = express();
    4.  
    5. // 配置存储选项
    6. const storage = multer.diskStorage({
    7.   destination: function (req, file, cb) {
    8.     cb(null, 'uploads/'); // 保存的路径，确保这个目录存在
    9.   },
    10.   filename: function (req, file, cb) {
    11.     cb(null, file.originalname); // 使用原始文件名作为保存的文件名
    12.   }
    13. });
    14.  
    15. // 创建multer实例
    16. const upload = multer({ storage: storage });
    17.  
    18. // 设置允许跨域请求，如果需要的话
    19. app.use((req, res, next) => {
    20.   res.header('Access-Control-Allow-Origin', '*');
    21.   res.header('Access-Control-Allow-Methods', 'GET, POST');
    22.   next();
    23. });
    24.  
    25. // 定义上传路由
    26. app.post('/upload', upload.single('file'), (req, res) => {
    27.   // req.file 包含了上传文件的信息
    28.   if (!req.file) {
    29.     return res.status(400).send('No file uploaded');
    30.   }
    31.  
    32.   // 文件上传成功
    33.   res.send(`File uploaded successfully. ${req.file.filename}`);
    34. });
    35.  
    36. // 启动服务器
    37. const port = 3000;
    38. app.listen(port, () => {
    39.   console.log(`Server running on port ${port}`);
    40. });

    启动服务

    node server.js

    参考内容：

    1. Luckyexcel/README-zh.md

    2.使用exceljs导出luckysheet表格

  • 相关阅读:
    挑战52天背完小猪佩奇(第02天)
    使用Bigemap计算挖填土石方量
    mindspore两日集训营202209-金箍棒应用
    猿创征文｜date-fns 秒助手函数
    JavaSE入门篇——类和对象（实例理解）
    Python数学计算工具1、海伦公式计算三角形面积
    【云原生 | 拓展02】在单台宿主机上管理Docker容器
    EXSI 实用指南 2024 -编译环境 Mac OS 安装篇（一）
    最新idea把run放到services中_超简单不坑人
    为什么我们不支持手工上传镜像
  • 原文地址：https://blog.csdn.net/qq285679784/article/details/136753327