0

I am in the process of working on an internal website that will need to pull data exclusively from static XLSX spreadsheets (updated through automation daily). On this site, I the data will need to be posted in a table and sortable/searchable. I can work on the special features on my own (search/sort options) after I have the content actually displaying correctly.

My Problem: I have no idea how to get the content to display after successfully querying through AlaSQL. I can see the content in JSON format in the console, but I am no JS programmer and have little to no idea how to pass the information along to get it to display correctly.

I am working on a server with IIS 8, working in HTML/C# for the base website code, but also incorporating jQuery, the W3.CSS framework, and the JS library AlaSQL and dependencies (for Excel file reading).

As stated above, I have the ground work in place - Alasql is operating and I am getting results from the Excel file as I should, but only to the console. I know from the base code below that it is by design posting it to the console, but I am so very green at JS work, I do not know how to store the data so that it can be pulled and displayed in a table in HTML.

I also do have jQuery in place to hopefully assist with formatting the file table, if I can get that far [jquery-3.4.1 and jquery.tablesorter (https://mottie.github.io/tablesorter/docs/)].

 <div class="w3-panel" id="OhYeah">
 </div>
 <script>
      var ImportData = (alasql.promise('select * from xlsx("File.xlsx",{sheetid:"Sheet1"})')
           .then(function(data){
            console.log(data);
       }).catch(function(err){
        console.log('There was an error reading the source file.:', err);
      }));
      document.getElementById("OhYeah").innerHTML = ImportData
 </script>

So, just through bumbling around, I found a resource that said the above "document.getElementByID" line should display the data in a raw string (so I would know I'm at least making headway) - instead, I get the output "[object Promise]".

I have looked at other resources on this site that have similar requests, but none exactly helped - This is the closest question I've found, but attempting their code did not work. (Excel Table to Html table)

End result I am aiming for is to have the content loop through and create a table with the desired content. I cannot find any resources that specifically deal with AlaSQL output that explain this even in part. As with most things, I can figure it out if I have at least some code to "reverse engineer" into what I am looking to do.

Any thoughts?

---EDIT 6/18---

So, just trying to work through this logically, I am getting a little further, but still need guidance. I modified the code to be the following and now I am getting different output.

 <div class="w3-panel" id="OhYeah">
 </div>
 <script>
      alasql.promise('select * from xlsx("File.xlsx",{sheetid:"Sheet1"})')
           .then(function(data){
            document.getElementById("OhYeah").innerHTML = (data)
            //console.log(data);
       }).catch(function(err){
        console.log('There was an error reading the source file.:', err);
      });
 </script>

I now receive 755 (the number of rows within my spreadsheet) "[object Object]" print outs on the display page.

[object Object],[object Object],[object Object],[object Object],
[object Object],[object Object],[object Object],[object Object],
[object Object],[object Object],[object Object],[object Object],
[object Object],[object Object],[object Object],[object Object], [...]
James
  • 21
  • 4

1 Answers1

0

So, after much waiting and web (and soul) searching, I found an my own answer. I wanted to post it here in the off chance someone else is looking for the same thing.

I ended up with the following JS (and CSS) files to get this accomplished:

<script type="text/javascript" src="\js\jquery-3.4.1.slim.min.js"></script>
<script type="text/javascript" src="\js\xlsx.full.min.js"></script>
<script type="text/javascript" src="\js\jquery.miranda.min.js"></script>    
<script type="text/javascript" src="\js\alasql.min.js"></script>
<link href="\css\w3.css" rel="stylesheet" type="text/css">

jQuery Miranda Source: https://github.com/fabianomiranda/miranda-js

SheetJS (XLSX) Source: https://github.com/SheetJS/js-xlsx

AlaSQL Source: https://github.com/agershun/alasql

W3 CSS Source: https://www.w3schools.com/w3css/w3css_downloads.asp

Final Script Code:

    function getData(){
        let query = alasql.promise('select * from xlsx("File.xlsx")')
            .then(function(data){
            $("#tbodyid").mirandajs(data)
        }).catch(function(err){
             console.log('There was an error reading the source file.:', err);
        })
     };
     getData();

Final HTML Code:

<div class="w3-container" id="w00t" style="padding-bottom:100px;">
    <table class="w3-table w3-bordered w3-striped" id="1337" style="overflow: auto;">
        <thead>
            <tr>
                <th>Object1</th> //Any title/name you want to provide your data for viewing.
                <th>Object2</th>
            </tr>
        </thead>
        <tbody id="tbodyid">
            <tr>
                <td>[[Object_1]]</td> //This is the "title" of the JSON data pulled from the AlaSQL query against the spreasheet - this must match any header you want to pull content to display in a table format (with spaces or underscores or however the header is formatted). This will only pull the content for the table for each iteration of <td> that you do.
                <td>[[Object_2]]</td>
            </tr>
       </tbody>
    </table>
</div>

The above code, if implemented correctly, shows the data from a spreadsheet in a table on the site you desire.

More work can be done (and I have done it) to do conditional formatting depending on the content of the table after the fact (I would be happy to share, let me know if you're interested).

James
  • 21
  • 4