js中对excel里面的字母和数字的转换处理

153 阅读1分钟

对应关系: 1 - A, 26 - Z, 27 - AA

实现效果: image.png

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>

<body>
    <div>
        <p>第一种方法实现:</p>
        输入大于0的整数:<input type="number" class="num" />
        <button onclick="change()">转换</button>
        转换后的字母:<span id="resString"></span>

        <p>第二种方法实现:</p>
        输入大于0的整数:<input type="number" class="num2" />
        <button onclick="change2()">转换</button>
        转换后的字母:<span id="resString2"></span>

        <p>字母转数字:</p>
        输入大写字母:<input type="text" id="txt" onblur="handleBlur()">
        转换后的数字: <span id="numString"></span>
    </div>
    <script>
        const handleBlur = () => {
            const val = document.getElementById('txt').value
            let num = 0
            const getLetterNum = (letter) => {
                return letter.charCodeAt() - 64 // 大写减64,小写减96
            }
            for (let i = 0, len = val.length; i < len; i++) {
                var letterNum = getLetterNum(val[i])
                num += letterNum * Math.pow(26, len - 1 - i)
            }
            document.getElementById('numString').innerText = num
        }

        const change2 = () => {
            const strArray = []
            const numToString = (nnum) => {
                const num = nnum - 1
                const integer = parseInt(num / 26)
                const remainder = num % 26
                const str = String.fromCharCode(64 + parseInt(remainder + 1))
                strArray.push(str)
                if (integer > 0) {
                    numToString(integer)
                }
            }
            const value = document.getElementsByClassName('num2')[0].value
            numToString(value)
            document.getElementById('resString2').innerText = strArray.reverse().join('')
        }
        // 实现数字转字母
        const change = () => {
            // 先获取输入的数字
            let num = document.getElementsByClassName('num')[0].value
            let str = ''
            if (parseInt(num) > 0) {
                if (num <= 26) {
                    str = String.fromCharCode(64 + parseInt(num))
                } else {
                    while (num > 26) {
                        let count = parseInt(num / 26)
                        let remainder = num % 26
                        if (remainder === 0) {
                            remainder = 26
                            count--
                        }
                        str = String.fromCharCode(64 + parseInt(remainder)) + str
                        num = count
                    }
                    str = String.fromCharCode(64 + num) + str
                }
            }
            document.getElementById('resString').innerText = str
        }
    </script>
</body>