jquery - 具有 250,000 行的 HTML 表格 - 更好的方法吗？

Question

我有一个包含 250,000 行(日期、公司、帐户、金额)的 csv 文件 - 从 Excel 导出。此内容每月更新一次。

我希望根据搜索/过滤器(最好是自动完成)将数据加载到 html 表中进行显示，搜索“公司”字段。这将仅显示该公司的数据。

250k 似乎太多了，无法加载到表中然后过滤输出 - 那么...解决这个问题的最佳方法是什么？数据表不断出现 - 但我不知道从哪里开始从数据文件获取数据。

Answer 1

给定一个像这样的 CSV 文件:

Date,Company,Account,Amount
2017-01-16,Jast-Bergnaum,Sales,94.31
2017-02-08,Upton and Sons,Research and Development,11.01
2016-12-21,Medhurst-Erdman,Business Development,74.67
2016-10-10,"Skiles, Hermiston and Goodwin",Marketing,77.0
2016-10-04,"Moore, Deckow and King",Legal,13.86
2017-02-12,Volkman-Jerde,Business Development,32.79
2017-03-21,Purdy Group,Accounting,33.18
2016-12-27,"Bernier, Rice and Toy",Support,4.73
2016-09-28,Corkery-Orn,Legal,41.73
2017-04-05,Zemlak-Fisher,Product Management,28.03

那么这段代码应该可以满足您的需要:

let inputElement = document.getElementById("input");
let tableData = null;
let example = null;
let handleFiles = event => {
    if ($.fn.DataTable.isDataTable("#example")) {
        example.destroy();
        $('#example').empty();
    }
    alasql('SELECT * FROM FILE(?,{headers:true})', [event], (data) => {
        tableData = data;
        createTable();
    });
};
let createTable = () => {
    example = $("#example").DataTable({
        "columns": [{
            "title": "Date",
            "data": "Date"
        }, {
            "title": "Company",
            "data": "Company"
        }, {
            "title": "Account",
            "data": "Account"
        }, {
            "title": "Amount",
            "data": "Amount"
        }],
        "serverSide": true,
        "ajax": (data, callback, settings) => {
            let fieldNames = [];
            let whereClause = [];
            let filterQuery = "SELECT count(*) AS recordsFiltered FROM ?";
            let orderBy = [];
            for (let i of data.columns) {
                fieldNames.push(i.data);
            }
            let query = `SELECT ${fieldNames.join(", ")} FROM ?`;
            if (data.search.value !== "") {
                query += " WHERE ";
                filterQuery += " WHERE ";
                for (let i of data.columns) {
                    whereClause.push(i.data + " LIKE '%" + data.search.value + "%'");
                }
                filterQuery += whereClause.join(' OR ');
                query += whereClause.join(' OR ');
            }
            query += " ORDER BY ";
            for (let i of data.order) {
                orderBy.push(fieldNames[i.column] + " " + i.dir.toUpperCase());
            }
            query += orderBy.join(', ');
            query += " LIMIT " + data.length + " OFFSET " + data.start;
            let returnData = {
                "draw": data.draw,
            };
            alasql('SELECT count(*) AS recordsTotal FROM ?', [tableData], (data, err) => {
                returnData.recordsTotal = data[0].recordsTotal;
                alasql(filterQuery, [tableData], (data, err) => {
                    returnData.recordsFiltered = data[0].recordsFiltered;
                    alasql(query, [tableData], (data, err) => {
                        returnData.data = data;
                        callback(
                            returnData
                        )
                    });
                });
            });
        }
    })
};
inputElement.addEventListener("change", handleFiles, false);

感谢您提出问题，制定解决方案很有趣。我已经尝试过使用 300000 行的文件，一切似乎工作正常，有一点延迟，但并不大，并且在上传文件时加载数据非常快。

希望有帮助。工作 JSFiddle here 。只需使用上面的 CSV 文件来测试即可。

对于同一目录中的静态文件，这应该有效:

let tableData = null;
let example = null;
$.blockUI();
let timer = {
    start: moment()
};
alasql(`
    SELECT
      *
    FROM FILE("MOCK_DATA.csv", {
      headers:true
    })`, [event], data => {
    tableData = data;
    $.unblockUI();
    timer.end = moment();
    let duration = moment.duration(timer.end.diff(timer.start));
    console.log("timer", timer);
    console.log("duration", duration.asSeconds());
    createTable();
});
let createTable = () => {
    example = $("#example").DataTable({
        "columns": [
            {
                "title": "Date",
                "data": "Date"
            }, {
                "title": "Company",
                "data": "Company"
            }, {
                "title": "Account",
                "data": "Account"
            }, {
                "title": "Amount",
                "data": "Amount"
            }
        ],
        "serverSide": true,
        "ajax": (data, callback, settings) => {
            let filterQuery = `
                SELECT
                  count(*) AS recordsFiltered
                FROM
                  ?${data.search.value !== "" ? `
                WHERE
                  ${data.columns.map(i => `${i.data}
                LIKE
                  '%${data.search.value}%'`).join(`
                OR
                  `)};` : `;`}`;
            let query = `
                SELECT
                  ${data.columns.map(i => i.data).join(`,
                  `)}
                FROM
                  ?${data.search.value !== "" ? `
                WHERE
                  ${data.columns.map(i => `${i.data}
                LIKE
                  '%${data.search.value}%'`).join(`
                OR
                  `)}` : ``}
                ORDER BY
                  ${data.order.map(i => data.columns.map(i => i.data)[i.column] + " " + i.dir.toUpperCase()).join(`,
                  `)}
                LIMIT
                  ${data.length}
                OFFSET
                  ${data.start};`;
            let returnData = {
                "draw": data.draw,
            };
            alasql(`
                SELECT
                  count(*) AS recordsTotal
                FROM
                  ?`, [tableData], (data, err) => {
                returnData.recordsTotal = data[0].recordsTotal;
                alasql(filterQuery, [tableData], (data, err) => {
                    returnData.recordsFiltered = data[0].recordsFiltered;
                    alasql(query, [tableData], (data, err) => {
                        returnData.data = data;
                        callback(
                            returnData
                        )
                    });
                });
            });
        }
    })
};