js操作excel表格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <title>导出excel</title>
</head>

<body>
  <input type="file" name="files-list" id="files-list">

  <table id="TableToExport">
    <tbody>
      <tr>
        <td colspan="3">SheetJS Table Export</td>
      </tr>
      <tr>
        <td>Author</td>
        <td>ID</td>
        <td>Note</td>
      </tr>
      <tr>
        <td>SheetJS</td>
        <td>7262</td>
        <td>Hi!</td>
      </tr>
      <tr>
        <td colspan="3"><a href="//sheetjs.com">Powered by SheetJS</a></td>
      </tr>
    </tbody>
  </table>
  <button id="sheetjsexport"><b>Export as XLSX</b></button>

  <script src="https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js"></script>
  <script>
    const tbody = document.getElementById("TableToExport").firstElementChild
    tbody.innerHTML = ''

    // 读取xlsx文件内容
    let filesList = document.getElementById("files-list")
    filesList.onchange = (e) => {
      const files = e.target.files;
      const reader = new FileReader();
      reader.onload = (ev) => {
        const data = ev.target.result;
        const workbook = XLSX.read(data, {
          type: 'binary'
        })

        const sheet = workbook.Sheets[workbook.SheetNames[0]]
        const json_data = XLSX.utils.sheet_to_json(sheet)
        const key_arr = []  // 用于保存有哪些字段
        const mykey_arr = ['经度（度分秒）', '纬度（度分秒）']  // 人为添加的字段
        
        // 首先获取表头，即excel表格内容的第一行
        let head_html = ''
        for(const key in json_data[0]) {
          head_html += `<td>${key}</td>`
          key_arr.push(key)
        }
        mykey_arr.forEach((key) => {
          head_html += `<td>${key}</td>`
        })
        tbody.insertAdjacentHTML('beforeend', `<tr>${head_html}</tr>`)

        // 然后获取表数据
        let data_html = ''
        for(let i=0; i<json_data.length; i++) {
          let tr = ''
          let td = ''
          key_arr.forEach((key) => {
            td += `<td>${json_data[i][key] || ''}</td>`
          })
          mykey_arr.forEach((key) => {
            if(key === '经度（度分秒）') {
              td += `<td>${ToDegrees(json_data[i]['经度'].toString())}</td>`
            } else if(key === '纬度（度分秒）') {
              td += `<td>${ToDegrees(json_data[i]['纬度'].toString())}</td>`
            }
          })
          tr = `<tr>${td}</tr>`
          data_html += tr
        }

        tbody.insertAdjacentHTML('beforeend', data_html)
      }
      reader.readAsBinaryString(files[0]);
    }

    // 导出xlsx文件
    document.getElementById("sheetjsexport").addEventListener('click', function () {
      /* Create worksheet from HTML DOM TABLE */
      var wb = XLSX.utils.table_to_book(document.getElementById("TableToExport"));
      /* Export to file (start a download) */
      XLSX.writeFile(wb, "SheetJSTable.xlsx");
    });

    // 经纬度转换
    //度转度°分′秒″
    function ToDegrees(val) {
        if (typeof (val) == "undefined" || val == "") {
            return "";
        }
        var i = val.indexOf('.');
        var strDu = i < 0 ? val : val.substring(0, i);//获取度
        var strFen = 0;
        var strMiao = 0;
        if (i > 0) {
            var strFen = "0" + val.substring(i);
            strFen = strFen * 60 + "";
            i = strFen.indexOf('.');
            if (i > 0) {
                strMiao = "0" + strFen.substring(i);
                strFen = strFen.substring(0, i);//获取分
                strMiao = strMiao * 60 + "";
                i = strMiao.indexOf('.');
                strMiao = strMiao.substring(0, i + 4);//取到小数点后面三位
                strMiao = parseFloat(strMiao).toFixed(4);//精确小数点后面两位
            }
        }
        
        // 111°26′38.00039999999982″

        // return strDu + "," + strFen + "," + strMiao;    // 这里可以修改成你想要的格式例如你可以改成这样的：
        return strDu + "°" + strFen + "′" + strMiao + "″";    // 这里可以修改成你想要的格式例如你可以改成这样的：
          //  return strDu + "°" + strFen + "'" + strMiao + "\"N";
        // return strDu + "°" + strFen + "'" + strMiao + "N";
    }
    
    //度°分′秒″转度
    function ToDigital(strDu, strFen, strMiao, len) {
        len = (len > 6 || typeof (len) == "undefined") ? 6 : len;//精确到小数点后最多六位   
        strDu = (typeof (strDu) == "undefined" || strDu == "") ? 0 : parseFloat(strDu);
        strFen = (typeof (strFen) == "undefined" || strFen == "") ? 0 : parseFloat(strFen) / 60;
        strMiao = (typeof (strMiao) == "undefined" || strMiao == "") ? 0 : parseFloat(strMiao) / 3600;
        var digital = strDu + strFen + strMiao;
        if (digital == 0) {
            return "";
        } else {
            return digital.toFixed(len);
        }
    }
  </script>
</body>

</html>