luckysheet控件实现excel文件导入导出

luckysheet控件是一款国产免费的在线表格编辑器，(https://gitee.com/mengshukeji/Luckysheet)
另外，项目中还推荐了一款搭配luckysheet的JS库：Luckyexcel
用以实现excel文件的导入导出：https://gitee.com/mengshukeji/Luckyexcel

然而，实际研究Luckyexcel代码发现，它只实现了excel到luckysheet的导入，导出函数是空的，且项目很久没有更新了。实际开发中，将页面文件保存的本地是常有的场景。因此，试着来自己补充一下。这里使用了exceljs与FileSaver两个JS库
直接上代码：

<!DOCTYPE html>
<html>
<head>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/luckysheet/dist/css/luckysheet.css" />
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/luckysheet/dist/assets/iconfont/iconfont.css" />    
    <script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/luckysheet.umd.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/luckyexcel/dist/luckyexcel.umd.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/exceljs/4.4.0/exceljs.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.5/FileSaver.min.js"></script>
</head>
<body>

<div class="form-group">
    <button>导入excel文件</button>
    <input type="file" id="excelFileInput" name="excelFile" accept=".xlsx" class="file-loading">
</div>

<div class="form-group" style="margin-right: 5px;">
    <button onclick="exportToExcel()">导出为Excel</button>
</div>

<script>

//导入Excel到luckysheet
$('#excelFileInput').on('change', function(event){
    event.preventDefault(); // 阻止默认提交行为
    var file = this.files[0]; // 获取选中的文件
    if (!file) {
        alert('未选择文件！');
        return;
    }

    let name = file.name;
    let suffixArr = name.split("."), fileName = suffixArr[0];
    var templateData ;


    LuckyExcel.transformExcelToLucky(file, function(exportJson, luckysheetfile){
        if(exportJson.sheets==null || exportJson.sheets.length==0){
            alert("Failed to read the content of the excel file, currently does not support xls files!");
            return;
        }
        console.log("exportJson.sheets is:" ,exportJson.sheets);
        window.luckysheet.destroy();
        window.luckysheet.create({
            container: 'luckysheet', //luckysheet is the container id
            showinfobar:false,
            data:exportJson.sheets,
            title:exportJson.info.name,
            userInfo:exportJson.info.name.creator
        });         

    });
});

//从luckysheet导出为Excel
async function exportToExcel() {

    const sheetsData = luckysheet.getAllSheets();
    // 创建新工作簿
    const workbook = new ExcelJS.Workbook();

    // 遍历每个工作表
    sheetsData.forEach(sheetData => {
        const worksheet = workbook.addWorksheet(sheetData.name);

        // 设置列宽
        if (sheetData.config.columnlen) {
            Object.entries(sheetData.config.columnlen).forEach(([col, width]) => {
                worksheet.getColumn(parseInt(col) + 1).width = width / 8; // 转换为Excel的字符单位
            });
        }

        // 设置行高
        if (sheetData.config.rowlen) {
            Object.entries(sheetData.config.rowlen).forEach(([row, height]) => {
                worksheet.getRow(parseInt(row) + 1).height = height / 1.5; // 转换为磅值
            });
        }

        // 处理单元格数据和样式
        sheetData.celldata.forEach(cell => {
            if (cell.v) {
                const row = cell.r + 1;
                const col = cell.c + 1;
                const excelCell = worksheet.getRow(row).getCell(col);

                // 设置值
                excelCell.value = cell.v.v || cell.v.m;

                // 设置样式
                const style = {};
                if (cell.v.fs) style.font = {
                    name: cell.v.ff || '等线',
                    size: cell.v.fs,
                    bold: cell.v.bl === 1,
                    color: { argb: cell.v.fc?.replace('#', '') || 'FF000000' }
                };

                if (cell.v.tb === 1) style.alignment = {
                    vertical: 'middle',
                    horizontal: 'center'
                };

                excelCell.style = style;
            }
        });

        // 处理合并单元格
        if (sheetData.config.merge) {
            Object.values(sheetData.config.merge).forEach(merge => {
                worksheet.mergeCells(
                    merge.r + 1,
                    merge.c + 1,
                    merge.r + merge.rs,
                    merge.c + merge.cs
                );
            });
        }

        // 处理边框
        sheetData.config.borderInfo.forEach(border => {
            if (border.rangeType === 'cell') {
                const row = border.value.row_index + 1;
                const col = border.value.col_index + 1;
                const cell = worksheet.getRow(row).getCell(col);

                const borderStyle = {
                    style: 'thin',
                    color: { argb: border.value.b?.color?.replace('#', '') || 'FF000000' }
                };

                cell.border = {
                    top: border.value.t ? borderStyle : undefined,
                    left: border.value.l ? borderStyle : undefined,
                    bottom: border.value.b ? borderStyle : undefined,
                    right: border.value.r ? borderStyle : undefined
                };
            }
        });
    });

    // 生成文件
    const buffer = await workbook.xlsx.writeBuffer();
    const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    saveAs(blob, 'export.xlsx');
}

</script>
</body>
</html>

说明：exportToExcel方法可以完整还原：表格基础结构、合并单元格、字体样式、文本对齐、边框样式、行列尺寸、基础数据内容等信息，对于更复杂的场景（如图片、条件格式、数据验证等）这里未作处理。实际的luckysheet表数据结构，可以在控制台查看日志（ console.log("exportJson.sheets is:" ,exportJson.sheets)）输出，自己慢慢分析