JS实现导出Excel和CSV文件操作

一、js导出Excel

<html>
<head>

</head>
<body>
<script type="text/javascript" src="./xlsx.core.min.js"></script>
<script type="text/javascript">
/**
 * 通用的打开下载对话框方法，没有测试过具体兼容性
 * @param url 下载地址，也可以是一个blob对象，必选
 * @param saveName 保存文件名，可选
 */
function openDownloadDialog(url, saveName) {
    if (typeof url == 'object' && url instanceof Blob) {
        url = URL.createObjectURL(url); // 创建blob地址
    }
    var aLink = document.createElement('a');
    aLink.href = url;
    aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
    var event;
    if (window.MouseEvent) event = new MouseEvent('click');
    else {
        event = document.createEvent('MouseEvents');
        event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
    }
    aLink.dispatchEvent(event);
}
// 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
function sheet2blob(sheet, sheetName) {
    sheetName = sheetName || 'sheet1';
    var workbook = {
        SheetNames: [sheetName],
        Sheets: {}
    };
    workbook.Sheets[sheetName] = sheet;
    // 生成excel的配置项
    var wopts = {
        bookType: 'xlsx', // 要生成的文件类型
        bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
        type: 'binary'
    };
    var wbout = XLSX.write(workbook, wopts);
    var blob = new Blob([s2ab(wbout)], { type: "application/octet-stream" });
    // 字符串转ArrayBuffer
    function s2ab(s) {
        var buf = new ArrayBuffer(s.length);
        var view = new Uint8Array(buf);
        for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
        return buf;
    }
    return blob;
}
</script>

<script type="text/javascript">
var exportDataSource = [{
                    name: '路人甲',
                    phone: '123456789',
                    email: '000@123456.com'
                }, {
                    name: '炮灰乙',
                    phone: '123456789',
                    email: '000@123456.com'
                }, {
                    name: '土匪丙',
                    phone: '123456789',
                    email: '000@123456.com'
                }, {
                    name: '流氓丁',
                    phone: '123456789',
                    email: '000@123456.com'
                }, ]; //导出json数据源
function exportExcel() {
            var excelItems = [];
            for (let i = 0; i < exportDataSource.length; i++) {
                if (exportDataSource[i].name != "") {
                    excelItems.push({
                        "姓名": exportDataSource[i].name,
                        "电话": exportDataSource[i].phone,
                        "邮箱": exportDataSource[i].email
                    }); //属性
                }
            }
            var sheet = XLSX.utils.json_to_sheet(excelItems);
            openDownloadDialog(sheet2blob(sheet), 'exportdata.xlsx');
        }
</script>

<a style="float: right;" onclick="exportExcel()">导出下载</a>
</body>
</html>

二、js实现json导出csv

<html>
<head>
  <p style="font-size: 20px;color: red;">使用a标签方式将json导出csv文件</p>
  <button onclick='tableToExcel()'>导出</button>
</head>
<body>
  <script>

    function tableToExcel(){
      //要导出的json数据
      const jsonData = [
        {
          name:'路人甲',
          phone:'123456789',
          email:'000@123456.com'
        },
        {
          name:'炮灰乙',
          phone:'123456789',
          email:'000@123456.com'
        },
        {
          name:'土匪丙',
          phone:'123456789',
          email:'000@123456.com'
        },
        {
          name:'流氓丁',
          phone:'123456789',
          email:'000@123456.com'
        },
      ]
      //列标题，逗号隔开，每一个逗号就是隔开一个单元格
      let str = `姓名,电话,邮箱\n`;
      //增加\t为了不让表格显示科学计数法或者其他格式
      for(let i = 0 ; i < jsonData.length ; i++ ){
        for(let item in jsonData[i]){
            str+=`${jsonData[i][item] + '\t'},`;
        }
        str+='\n';
      }
      //encodeURIComponent解决中文乱码
      let uri = 'data:text/csv;charset=utf-8,\ufeff' + encodeURIComponent(str);
      //通过创建a标签实现
      let link = document.createElement("a");
      link.href = uri;
      //对下载的文件命名
      link.download =  "json数据表.csv";
      document.body.appendChild(link);
      link.click();
      document.body.removeChild(link);
    }

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

三、js实现json导出csv

<html>
<head>
  <p style="font-size: 20px;color: red;">使用table标签方式将json导出xls文件</p>
  <button onclick='tableToExcel()'>导出</button>
</head>
<body>
  <script>

    function tableToExcel(){
      //要导出的json数据
      const jsonData = [
        {
          name:'路人甲',
          phone:'123456',
          email:'123@123456.com'
        },
        {
          name:'炮灰乙',
          phone:'123456',
          email:'123@123456.com'
        },
        {
          name:'土匪丙',
          phone:'123456',
          email:'123@123456.com'
        },
        {
          name:'流氓丁',
          phone:'123456',
          email:'123@123456.com'
        },
      ]
      //列标题
      let str = '<tr><td>姓名</td><td>电话</td><td>邮箱</td></tr>';
      //循环遍历，每行加入tr标签，每个单元格加td标签
      for(let i = 0 ; i < jsonData.length ; i++ ){
        str+='<tr>';
        for(let item in jsonData[i]){
            //增加\t为了不让表格显示科学计数法或者其他格式
            str+=`<td>${ jsonData[i][item] + '\t'}</td>`;
        }
        str+='</tr>';
      }
      //Worksheet名
      let worksheet = 'Sheet1'
      let uri = 'data:application/vnd.ms-excel;base64,';

      //下载的表格模板数据
      let template = `<html xmlns:o="urn:schemas-microsoft-com:office:office"
      xmlns:x="urn:schemas-microsoft-com:office:excel"
      xmlns="http://www.w3.org/TR/REC-html40">
      <head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>
        <x:Name>${worksheet}</x:Name>
        <x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>
        </x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
        </head><body><table>${str}</table></body></html>`;
      //下载模板
      window.location.href = uri + base64(template)
    }
    //输出base64编码
    function base64 (s) { return window.btoa(unescape(encodeURIComponent(s))) }
</script>
</body>
</html>

四、js实现Tab转为Excel

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

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>excel 导出测试</title>
    <style>
        table {
            border-collapse: collapse;
            text-align: center;
            vertical-align: middle;
            width: 800px;
            font-size: 20px;
        }

        button {
            height: 30px;
            width: 100px;
            margin: 20px 20px;
            background: yellowgreen;
            border-radius: 10px;
            outline: none;
        }

        input {
            height: 30px;
            padding-left: 10px;
            margin: 10px;
        }
    </style>
</head>

<body>
    <div id="wrap" style="width:900px;margin:20px auto;">
        <h3>js脚本 导出excel测试</h3>
        <table id="tb" border="1" cellspacing="0" cellpadding="0">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>姓名</td>
                        <th>年龄</td>
                            <th>座右铭</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>1</td>
                    <td>张三</td>
                    <td>18</td>
                    <td>走的人多了，变成了路。</td>
                </tr>
                <tr>
                    <td>2</td>
                    <td>李四</td>
                    <td>88</td>
                    <td>人人都有自己走的路，哪条属于自己呢？</td>
                </tr>
                <tr>
                    <td>3</td>
                    <td>王五</td>
                    <td>81</td>
                    <td>走别人的路，让他没道可走！</td>
                </tr>
            </tbody>
        </table>

        <label>
            姓名：<input type="text" autocomplete id="name" placeholder="请输入您的姓名..." >
        </label>

        <label>
            年龄：<input type="text" autocomplete id="age" placeholder="请输入您的年龄...">
        </label>

        <label>
            座右铭：<input type="text" autocomplete id="sex" placeholder="请输入您的座右铭..." >
        </label>

        <button id="add">添加信息</button>
        <button id="out" onclick="btn_export()">导出文件</button>
    </div>
    <script src="./xlsx.core.min.js"></script>
    <script src="./jquery.min.js"></script>
    <script src="./excel.js"></script>

    <script>
        function btn_export() {
            var table = document.querySelector("#tb");
            var sheet = XLSX.utils.table_to_sheet(table); //将一个table对象转换成一个sheet对象
            openDownloadDialog(sheet2blob(sheet), 'excel.xlsx');
        }

        var id = $("input").length + 1;
        $(function() {
            $("#add").click(function() {
                var name = $("#name").val();
                var age = $("#age").val();
                var sex = $("#sex").val();
                $("tbody").append("<tr><td>" + id + "</td><td>" + name + "</td> <td>" + age + "</td> <td>" + sex + "</td></tr>");
                id++;
                $("input").val('');
            });
        })
    </script>
</body>

</html>

到此这篇关于JS实现导出Excel和CSV文件的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持我们。