Read XLS into a web datagrid

Question

I'm looking for a good solution to:

Select items in checkboxes on a web form (the checkbox would dynamically read the csv)
Read from a XLS file
Set which columns to be selected in options
Put the data into a datagrid with multiple columns and headers
Total the data on the final row.

Is there a simple solution to achieve this with jquery or some sort of web based spreadsheet? Any suggestions is appreciated.

<!DOCTYPE html>
<html>
<head>
<script src="media/js/jquery.js" type="text/javascript"></script> 
<script src="media/js/jquery.dataTables.js" type="text/javascript"></script> 

... js table code provided in answer ...

<link href="media/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <div id="demo"></div> 
</body>
</html>

Have a look at this http://stackoverflow.com/questions/7431268/how-read-data-from-csv-file-using-javascript. — Tariqulazam, Oct 16 '12 at 23:49
Thanks for the suggestion. I'm still looking for all-in-one solution. — rrrfusco, Oct 17 '12 at 00:01

score 0 · Answer 1 · edited May 23 '17 at 12:18

I am not sure if there is any all-in-one solution for this. But that is what I would do. I have modified the answer from How to read data From *.CSV file using javascript? and added jquery datatable support for this. I have hard-coded the headers in the datatable but that can be easily made dynamic.

SAMPLE CSV FILE (save it as data.txt file)

heading1,heading2,heading3,heading4,heading5
value1_1,value2_1,value3_1,value4_1,value5_1
value1_2,value2_2,value3_2,value4_2,value5_2

HTML

<div id="demo"></div>    
<script type="text/javascript">
    $(document).ready(function () {
        $.ajax({
            type: "GET",
            url: "data.txt",
            dataType: "text",
            success: function (data) {
                processData(data);
            }
        });


        function processData(allText) {
            var allTextLines = allText.split(/\r\n|\n/);
            var headers = allTextLines[0].split(',');
            var lines = [];

            for (var i = 1; i < allTextLines.length; i++) {
                var data = allTextLines[i].split(',');
                if (data.length == headers.length) {

                    var tarr = [];
                    for (var j = 0; j < headers.length; j++) {
                        tarr.push(data[j]);
                    }
                    lines.push(tarr);
                }
            }


            $('#demo').html('<table cellpadding="0" cellspacing="0" border="0" class="display" id="example"></table>');
            $('#example').dataTable({
                "aaData": lines,
                "aoColumns": [
                    { "sTitle": "heading1" },
                    { "sTitle": "heading2" },
                    { "sTitle": "heading3" },
                    { "sTitle": "heading4" },
                    { "sTitle": "heading5" }
                ]
            });


        }
    });



</script>

I'm having some trouble executing this. Is it simply the html and text file? — rrrfusco, Oct 17 '12 at 03:04
You have to include the DataTable jquery plugin and required css as well. Include into the page. — Tariqulazam, Oct 17 '12 at 03:58
I'm using this path with your code Why isn't it styling the text? — rrrfusco, Oct 18 '12 at 02:03
The images and color seem to render. The text does not. I'll have to look at it closer. — rrrfusco, Oct 18 '12 at 02:23
Could you please make sure you are browsing your sample html through http? — Tariqulazam, Oct 18 '12 at 04:30

Read XLS into a web datagrid

1 Answers1