WPS加载项——将多个单元格的值合并至一个单元格

最新推荐文章于 2025-02-13 12:54:24 发布

程不住气

最新推荐文章于 2025-02-13 12:54:24 发布

阅读量2.9k

点赞数 5

文章标签： wps javascript excel

本文链接：https://blog.csdn.net/weixin_42329250/article/details/143020905

版权

使用过Excel表格的朋友们应该都避不开一个功能——合并单元格

目前WPS中的合并单元格有以下几种合并模式（如图）：

其中第三种“合并内容”就是本篇文章讲述的合并模式。该合并模式只能将多行合并为一行，同时将各行的值都汇总到合并后的单元格中。

但以下这种情况就无法实现了：左表为原数据，需要根据姓名将科目合并到一个单元格中。结果如右表。此时只能采用WPS的加载项了，VS启动~~~

（1）我的WPS菜单栏中已经写了一个工具箱的窗口页，先在工具箱中加入“多行并一行”的功能入口按钮（图1），已经执行该功能的详情页（图2）

多行并一行的html

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>合并Excel文件</title>
    <link rel="stylesheet" href="../css/x_to_one.css">
</head>
<body>
    <div class="container">
        <div class="top">
            <button class="home" onclick="onbuttonclick('home')">主页</button>
            <button class="close-button" onclick="window.close()">×</button>
        </div>
        <div class="middle">
            <div class="biaoti"><h3>多行并一行</h3></div>

            <div class="table-container">
                <div class="table">
                    <label>合并前：</label>
                    <table>
                        <thead>
                            <tr>
                                <th>参考列A</th>
                                <th>合并列B</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td>A1</td>
                                <td>B1</td>
                            </tr>
                            <tr>
                                <td>A1</td>
                                <td>B2</td>
                            </tr>
                        </tbody>
                    </table>
                </div>
            
                <div class="table">
                    <label>合并后：</label>
                    <table>
                        <thead>
                            <tr>
                                <th>参考列A</th>
                                <th>合并列B</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td>A1</td>
                                <td>B1<br>B2</td>
                            </tr>
                            <tr>
                                <td>A1</td>
                                <td></td>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </div>
            

            <div class="zhong1">
                <input class="cankao" type="text" id="cankao" placeholder="参考" >
                <input class="hebing" type="text" id="hebing" placeholder="合并" >
            </div>
            <div class="tixing">
                <label id="status" for="">输入参考列、合并列</label>
            </div>
            <div class="xia">
                <button onclick="onbuttonclick('hebin')">开始合并</button>
            </div>
        </div>

    </div>
    <script src="../js/x_to_one.js"></script>
</body>
</html>

多行并一行的CSS

body, html {
    height: 100%;
    margin: 0;
    display: flex;
    align-items: center;
    justify-content: center;
    background-color: #f5f5f5;
    font-family: 'Arial', sans-serif;
}

.container {
    background-color: #fff;
    border: 1px solid #ddd;
    border-radius: 20px;
    padding: 20px;
    width: 320px;
    height: 450px;
    box-shadow: 0px 4px 8px rgba(0, 0, 0, 0.1);
    animation: fadeIn 0.5s ease-in-out;
    margin-top: 35px;
}

.close-button, .home {
    position: absolute;
    top: 10px;
    color: #000000;
    border: none;
    border-radius: 15px;
    line-height: 25px;
    text-align: center;
    cursor: pointer;
    transition: background-color 0.3s ease;
}

.close-button {
    width: 35px;
    height: 35px;
    right: 10px;
    font-size: 30px;
    background-color: #f5f5f5;
}

.close-button:hover {
    background-color: #ff0000;
}


.home {
    color: #ffffff;
    left: 10px; /* Adjusted to be next to the close button */
    width: 50px;
    height: 35px;
    font-size: 15px;
    background-color: #f0a9a7;
}

.home:hover {
    background-color: #f37a76;
}


.biaoti  {
    margin: 0;
    padding-bottom: 10px;
    font-size: 24px;
    border-bottom: 1px solid #ddd;
    color: #333;
}

.zhong1{
    display: flex;
    align-items: center;
    justify-content: space-around;
    margin: 10px;
}

input{
    height: 80px;
    width: 80px;
    font-size: 40px;
    text-align: center;
}


.xia{
    display: flex;
    align-items: center;
    justify-content: center;
}

.xia button {
    margin: 20px;
    padding: 10px 15px;
    border: none;
    width: 240px;
    height: 45px;
    border-radius: 15px;
    background-color: #f0a9a7;
    color: #fff;
    cursor: pointer;
    transition: background-color 0.3s ease;
}

.xia button:hover {
    background-color: #f37a76;
}




.table-container {
    display: flex;
    justify-content: space-between;
    margin: 5px;
}

.table-container label{
    font-size: 10px;
}

table {
    margin-top: 5px;
    margin-bottom: 5px; 
    width: 140px;
    border-collapse: collapse;
    font-size: 10px; /* 字体大小调整为14px，2px太小了 */
}

th, td {
    border: 1px solid #ccc;
    padding: 1px;
    text-align: center; /* 单元格内容居中 */
}

th {
    background-color: #f2f2f2;
}

tr:nth-child(even) {
    background-color: #f9f9f9;
}


#status {

    background-color: #ffffff;
    margin-top: 30px;
    display: block;
    width: 100%;
    height: 30px;
    border: 0px solid #ddd;
    border-radius: 5px;
    color: #ff0000;
    text-align: center;
}

（2）JS代码实现主要逻辑

function onbuttonclick(idStr) {
    if (typeof (wps.Enum) != "object") { // 如果没有内置枚举值
        wps.Enum = WPS_Enum;
    }
    switch (idStr) {
        case "hebin": {
            var cankaoValue = document.getElementById('cankao').value;
            var hebingValue = document.getElementById('hebing').value;

            if (cankaoValue == "" || hebingValue == "") {
                document.getElementById('status').textContent = "请输入列！";
            } else {
                if (/^[a-zA-Z]$/.test(cankaoValue) && /^[a-zA-Z]$/.test(hebingValue)) {
                    // 转换为大写
                    var uppercaseCankaoValue = cankaoValue.toUpperCase();
                    var uppercaseHebingValue = hebingValue.toUpperCase();
                    document.getElementById('cankao').value = uppercaseCankaoValue;
                    document.getElementById('hebing').value = uppercaseHebingValue;

                    const app = wps.EtApplication();
                    const wb = app.ActiveWorkbook;
                    const wbsht1 = wb.ActiveSheet;

                    let hangshu = wbsht1.Range(uppercaseCankaoValue + '65536').End(-4162).Row;

                    app.ActiveWindow.SelectedSheets.Copy(undefined, wbsht1);
                    app.ActiveSheet.Name = "多行并一行";
                    const wbsht = wb.ActiveSheet;

                    let i = 2;
                    function processRows() {
                        if (i > hangshu + 300) {
                            // 处理完成，执行后续代码
                            postProcessing();
                            return; // 结束处理
                        }

                        if (wbsht.Range(uppercaseCankaoValue + i).Value2 == null) {
                            postProcessing(); // 如果遇到空值也执行后续代码
                            return; // 结束处理
                        } else {
                            wbsht.Range(uppercaseHebingValue + i).Value2 = '/"' + wbsht.Range(uppercaseHebingValue + i).Value2 + '"';

                            for (let j = 1; j <= 2000; j++) {
                                if (wbsht.Range(uppercaseCankaoValue + i).Value2 == wbsht.Range(uppercaseCankaoValue + (i + j)).Value2) {
                                    wbsht.Range(uppercaseHebingValue + i).Value2 += "&CHAR(10)&" + '"' + wbsht.Range(uppercaseHebingValue + (i + j)).Value2 + '"';
                                    wbsht.Range(uppercaseHebingValue + (i + j)).Value2 = null;
                                    // app.Rows.Item((i + j) + ":" + (i + j)).Delete(-4162);
                                } else {
                                    i = i + j; // 更新行号
                                    break;
                                }
                            }
                        }
                        document.getElementById('status').textContent = (i / hangshu * 100).toFixed(0) + '%';

                        // 使用 setTimeout 继续处理下一行
                        setTimeout(processRows, 0);
                    }

                    // 后处理函数
                    function postProcessing() {
                        document.getElementById('status').textContent = "格式化中";
                        wbsht.Range(uppercaseHebingValue + "2:" + uppercaseHebingValue + hangshu).Select();
                        app.Selection.Replace("/", "=", 2, 1, false, false, true, true);
                        app.Selection.WrapText = true;
                        // wbsht.Range("A1").select();

                        for (let i = hangshu; i >= 1; i--) {
                            if (wbsht.Range(uppercaseHebingValue + i).Value2 == null || wbsht.Range(uppercaseHebingValue + i).Value2 === "") {
                                app.Rows.Item(i + ":" + i).Delete(-4162);
                            }
                        }
                        document.getElementById('status').textContent = "SUCCESS！";
                    }

                    // 开始处理行
                    processRows();

                } else {
                    document.getElementById('status').textContent = "请输入一个字母！";
                }
            }
        }
        break;
        case "home": {
            window.location.href = "./home.html";
        }
        break;
    }
}

最终代码实现效果：

WPS加载项——多行并一行演示效果